ParamByName in a method lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2023-03-13 19:01 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // erzeugt SLQ queries nach den Radioboxen, die der Nutzer ausgewählt hatprocedure TFrame_bearbeiteDB.SQL_Adaptieren(Var dataset_: TIBDataset);Var s: string; begin// für Tabelle tbkalender   If (RadioGroup_bearbeiten.ItemIndex = 0) then begin    s:='select * from tbkalender';    if CheckBox_DatumBegrenzen.Checked then    // Auswahl mit Datum       s:=s + ' where (jdatum < :anf) and (jdatum > :ende)';  // Sortierung: AUFsteigen!     if CheckBox_nurEintraege.Checked then begin         if CheckBox_DatumBegrenzen.Checked   // Auswahl nur Feiertage             then s:=s + ' and (feiertagsmark = 1)'           else s:=s + ' where feiertagsmark = 1';                                       end;     s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!     DataSet_.SelectSQL.Text:=s;     if CheckBox_DatumBegrenzen.Checked then begin        dataset_.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;      dataset_.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;     end;   end; .... I call it by Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SQL_Adaptieren(IBDataSet_bearbeite);   IBDataSet_bearbeite.Active:=true; and result in an error-code of Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Projekt ....Exception-Klasse »EIBClientError« .... saying:SQL Param No. 0 (ANF) is uninitialised  In Datei 'FBMessages.pas' in Zeile 410 And yes, the same without generating ModifySQL in a method works fine. ---------------------------------------------------------------------- egsuh · 2023-03-14 06:09 -Try string type instead of TDateTime type. ---------------------------------------------------------------------- af0815 · 2023-03-14 07:48 Quote from: Nicole on March 13, 2023, 08:01:59 pmCode: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Projekt ....Exception-Klasse »EIBClientError« .... saying:SQL Param No. 0 (ANF) is uninitialised  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 Quote        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 · 2023-03-14 07:58 And to clean up your mess with the If-Clauses requires SysUtils in "Uses" Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFrame_bearbeiteDB.SQL_Adaptieren(Var dataset_: TIBDataset);Var s: string;    a: Array Of String;   //Added!begin  If (RadioGroup_bearbeiten.ItemIndex = 0) then    begin      s:='select * from tbkalender';      if CheckBox_DatumBegrenzen.Checked then    // Auswahl mit Datum        Begin          SetLength(a, Length(a)+1);        a[High(a)]:='(jdatum < :anf) and (jdatum > :ende)';   // Sortierung: AUFsteigen!        End;      if CheckBox_nurEintraege.Checked then        begin          SetLength(a,Length(a)+1);          a[High(a)]:='(feiertagsmark = 1)';        End;      //We need a WHERE-Clause      If Length(a)>0 Then s:=s + ' where '+String.Join(' and ',a);      s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!    DataSet_.SelectSQL.Text:=s;      if CheckBox_DatumBegrenzen.Checked then        begin        //No idea about this          dataset_.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;        dataset_.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;      end;                                   end;end. ---------------------------------------------------------------------- Zvoni · 2023-03-14 10:44 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 · 2023-03-14 11:16 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 · 2023-03-14 11:24 Try calling Prepare after assigning SQL to DataSet: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i       s:=s + ' order by jdatum desc';    // die größer- und kleine-Zeichen müssen umgedreht sein!      DataSet_.SelectSQL.Text:=s;      DataSet_.Prepare;       //   <-- Add      if CheckBox_DatumBegrenzen.Checked then  Quote from: Zvoni on March 14, 2023, 11:44:01 amis 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 · 2023-03-14 17:47 Quote from: Zvoni on March 14, 2023, 11:44:01 amis 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/fblangr ef40/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 · 2023-03-15 02:52 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.