ParamByName in a method

[Nicole]

The story behind: I have ONE Form ans SEVERAL tables which shall be edited in the same DBGrid etc.
So I have to adjust the queries behind.

There is something which looks problematic at first sight.
And it does not work.
But how to put it correctly?

I want to generate for a dataset SQL queries, which works fine.
The problem occurs: Where and how to set the parameters?

This works as long the user does not ask for the parameters like "Anf" = beginning date

Code: Pascal  [Select][+][-]

1. // erzeugt SLQ queries nach den Radioboxen, die der Nutzer ausgewählt hat
2. procedure TFrame_bearbeiteDB.SQL_Adaptieren(Var dataset_: TIBDataset);
3. Var s: string;
4.  
5. begin
6. // für Tabelle tbkalender
7.    If (RadioGroup_bearbeiten.ItemIndex = 0) then begin
8.     s:='select * from tbkalender';
9.     if CheckBox_DatumBegrenzen.Checked then    // Auswahl mit Datum
10.        s:=s + ' where (jdatum < :anf) and (jdatum > :ende)';  // Sortierung: AUFsteigen!
11.      if CheckBox_nurEintraege.Checked then begin
12.          if CheckBox_DatumBegrenzen.Checked   // Auswahl nur Feiertage
13.              then s:=s + ' and (feiertagsmark = 1)'
14.              else s:=s + ' where feiertagsmark = 1';
15.                                          end;
16.      s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!
17.      DataSet_.SelectSQL.Text:=s;
18.      if CheckBox_DatumBegrenzen.Checked then begin
19.         dataset_.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;
20.         dataset_.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;
21.        end;
22.    end;
23. ....

I call it by

Code: Pascal  [Select][+][-]

1.   SQL_Adaptieren(IBDataSet_bearbeite);
2.   IBDataSet_bearbeite.Active:=true;  

and result in an error-code of

Code: Text  [Select][+][-]

1. Projekt ....Exception-Klasse »EIBClientError« .... saying:
2. SQL Param No. 0 (ANF) is uninitialised
3.  
4.  In Datei 'FBMessages.pas' in Zeile 410

And yes, the same without generating ModifySQL in a method works fine.

[egsuh]

-Try string type instead of TDateTime type.

[af0815]

Code: Text  [Select][+][-]

1. Projekt ....Exception-Klasse »EIBClientError« .... saying:
2. SQL Param No. 0 (ANF) is uninitialised
3.  
4.  In Datei 'FBMessages.pas' in Zeile 410

And yes, the same without generating ModifySQL in a method works fine.

It looks for me, that DataSet_.SelectSQL.Text:=s; does not parse the stament and autogenerate the params.
On easy test is to use this

        dataset_.Params.CreateParam(ftDateTime,'anf', ptInput);
        dataset_.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;
        dataset_.Params.CreateParam(ftDateTime,'ende', ptInput);
        dataset_.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;

I am always generate Params by myself. So it depends not on the automatic.

[Zvoni]

And to clean up your mess with the If-Clauses
requires SysUtils in "Uses"

Code: Pascal  [Select][+][-]

1. procedure TFrame_bearbeiteDB.SQL_Adaptieren(Var dataset_: TIBDataset);
2. Var s: string;
3.     a: Array Of String;   //Added!
4. begin
5.   If (RadioGroup_bearbeiten.ItemIndex = 0) then
6.     begin
7.       s:='select * from tbkalender';
8.       if CheckBox_DatumBegrenzen.Checked then    // Auswahl mit Datum
9.         Begin
10.           SetLength(a, Length(a)+1);
11.           a[High(a)]:='(jdatum < :anf) and (jdatum > :ende)';   // Sortierung: AUFsteigen!
12.         End;
13.       if CheckBox_nurEintraege.Checked then
14.         begin
15.           SetLength(a,Length(a)+1);
16.           a[High(a)]:='(feiertagsmark = 1)';
17.         End;
18.       //We need a WHERE-Clause
19.       If Length(a)>0 Then s:=s + ' where '+String.Join(' and ',a);
20.       s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!
21.       DataSet_.SelectSQL.Text:=s;
22.       if CheckBox_DatumBegrenzen.Checked then
23.         begin
24.         //No idea about this
25.           dataset_.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;
26.           dataset_.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;
27.         end;                              
28.     end;
29. end.

[Zvoni]

is it possible, that FireBird stores Dates/DateTimes as a String, similiar to SQLite?
see also egsuh's answer above
It certainly looks that way: https://firebirdsql.org/en/firebird-date-literals/

[Thaddy]

IF, but only IF, that is the case that would even raise my anger about that FB. But I do not believe that.
I first have to see the design of the fields and even if it were stored as string is it stored in proper format:
1. yyyy/mm/dd
2. In UTC
3. It may even be stored in UNIX format: that is something to be carefull with, since that is UINT64

But I do not believe that either way. FB should do it correct.

Maybe RVK can shed light on this?

(Btw: in Sqlite3 it is possible to store dates correctly, at least in an easily convertible format.)

[korba812]

Try calling Prepare after assigning SQL to DataSet:

Code: Pascal  [Select][+][-]

1.       s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!
2.       DataSet_.SelectSQL.Text:=s;
3.       DataSet_.Prepare;       //   <-- Add
4.       if CheckBox_DatumBegrenzen.Checked then
5.  

is it possible, that FireBird stores Dates/DateTimes as a String, similiar to SQLite?
see also egsuh's answer above
It certainly looks that way: https://firebirdsql.org/en/firebird-date-literals/

FireBirs stores date in the format declared as the column type (SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TIMESTAMP...). Date literals are used in pure SQL queries, not for parameters or specifying column format.

[Zoran]

is it possible, that FireBird stores Dates/DateTimes as a String, similiar to SQLite?
see also egsuh's answer above
It certainly looks that way: https://firebirdsql.org/en/firebird-date-literals/

No. https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-datatypes-datetime

The document you are referring to is about format used for writing date/time literals in sql queries, not about storage.

[egsuh]

Try setting FB’s field type as Timestamp.  I remember I had similar problem in the past, but do not remember exactly what it was. Now I linked the field directly to TDBDateEdit component via TDataSource and have no problem.