Lazarus

Programming => Databases => Topic started by: patyi on March 12, 2018, 09:18:32 am

Title: [SOLVED] IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 12, 2018, 09:18:32 am
Hi !

I have a problem with the following query: (the fileld date is type of date, Firebird 3.0.3 server dialect 3)

Query.Close;
Query.SQL.Clear;
Query.SQL.Add ('SELECT datum, some_fields FROM some_table WHERE datum BETWEEN :fromdate AND :todate ORDER BY datum');
Query.ParamByName('fromdate').AsDate := DateTimPicker_From.Date;
Query.ParamByName('todate').AsDate := DateTimPicker_To.Date;
Query.Open;

Query.Eof is always True, but there are certain entries in the SQL table for that period.

when I replaced the SQL parameters to  "2018-01-01" and "2018-03-01", I got the correct result.

I would like to use the Parameterized Query as the date range varies, depending on how the user chooses.

Previously I used ZeosDBO, I have never had problems with the DateTime parameters. I'm doing something wrong or the ParamByName ('dat_param').AsDate (or AsDateTime)  works differently ?

XUbuntu 17.10 i386, Lazarus 1.8.3 i386 gtk, Firebird 3.0.3, IBX 2.2 (applicatin also cross compiled to Win32)
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: balazsszekely on March 12, 2018, 09:21:00 am
frmdate <> fromdate
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 12, 2018, 09:26:11 am
Yes, but this is just a spell at writing the question  :-[, I fixed it, the point remains.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: rvk on March 12, 2018, 09:31:17 am
What is the Query.Recordcount after .Open.

Also don't use date as fieldname. It's a reserved word.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 12, 2018, 09:38:09 am
Record count is 0 (EOF = True), actually my real field name is datum , date is just illustration name for data type in example code ...
I fixed it in the original post, to avoid confusion ...
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: rvk on March 12, 2018, 10:28:21 am
Query.Eof is always True, but there are certain entries in the SQL table for that period.
when I replaced the SQL parameters to  "2018-01-01" and "2018-03-01", I got the correct result.
And what are those certain dates?

How did you determine there where records between those dates? If you are looking at them with a SQL-manager, make sure you interpret the dates on screen correctly. If they are in the form of xx-xx-xxxx they can be either mm-dd-yyyy or dd-mm-yyyy.

So show us all the entries of
Code: SQL  [Select][+][-]
  1. SELECT datum, some_fields FROM some_table WHERE datum BETWEEN '2018-01-01' AND '2018-12-31' ORDER BY datum
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: tonyw on March 12, 2018, 10:37:39 am
Hi !

I have a problem with the following query: (the fileld date is type of date, Firebird 3.0.3 server dialect 3)

Query.Close;
Query.SQL.Clear;
Query.SQL.Add ('SELECT datum, some_fields FROM some_table WHERE datum BETWEEN :fromdate AND :todate ORDER BY datum');
Query.ParamByName('fromdate').AsDate := DateTimPicker_From.Date;
Query.ParamByName('todate').AsDate := DateTimPicker_To.Date;
Query.Open;

Query.Eof is always True, but there are certain entries in the SQL table for that period.

when I replaced the SQL parameters to  "2018-01-01" and "2018-03-01", I got the correct result.

I would like to use the Parameterized Query as the date range varies, depending on how the user chooses.

Previously I used ZeosDBO, I have never had problems with the DateTime parameters. I'm doing something wrong or the ParamByName ('dat_param').AsDate (or AsDateTime)  works differently ?

XUbuntu 17.10 i386, Lazarus 1.8.3 i386 gtk, Firebird 3.0.3, IBX 2.2 (applicatin also cross compiled to Win32)

In the above, it would be useful if you could also print out  the values of DateTimPicker_From.Date and DateTimPicker_To.Date to make sure that they are what you think they are.

You can see an example of how IBX works with date ranges in the ibx/examples/employee example - see the method TForm1.EmployeesBeforeOpen in Unit1.pas. This is intended to illustrate dynamic changes to queries in order to select employees by HIRE_DATE ranges but also illustrates what you are trying to do. Playing around with this example may help you diagnose what is happening in your program.

The different between AsDate and AsDateTime is the way they map on to Firebird Data Types. AsDate sets the parameter type to a Firebird DATE type while AsDateTime sets the parameter type to a Firebird TIMESTAMP type. As your example does not have a time part, this should not make a difference.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 12, 2018, 05:28:25 pm
The complete code for Query:

Code: Pascal  [Select][+][-]
  1.  EnableMonitoring;
  2.   QFinans.Close;
  3.   QFinans.SQL.Clear;
  4.   QFinans.SQL.Add('SELECT D.red, D.ino, D.roba, D.datum, D.predracun, D.racunbr,'+
  5.                          'D.dobavljac, D.majstor, D.namena, D.napomena, D.opis,'+
  6.                          'D.mestotr, D.firma, D.rokpl, D.osnovica, D.pdv,'+
  7.                          'D.valuta, D.vrsta, D.potvrda, D.nacinpl, D.sporno,'+
  8.                          'D.korisnik, D.dat_izm, D.porez, D.iznospdv,'+
  9.                          'D.zatvoren, D.bojana1, D.bojana2,'+
  10.                          '(SELECT COALESCE(SUM(U.uplata),0.0) FROM dobavupl U '+
  11.                           'WHERE U.dobred = D.red) placeno,'+
  12.                          '(SELECT MAX(U.datum) FROM dobavupl U '+
  13.                           'WHERE U.dobred = D.red) datumpl,'+
  14.                          '(SELECT COALESCE(SUM(K.kompenz),0.0) FROM dobavkom K '+
  15.                           'WHERE K.dobred = D.red) kompenz,'+
  16.                          '(SELECT MAX(K.datum) FROM dobavkom K '+
  17.                           'WHERE K.dobred = D.red) kompdat,'+
  18.                          'D.iznospdv-'+
  19.                          '(SELECT COALESCE(SUM(U.uplata),0.0) FROM dobavupl U '+
  20.                           'WHERE U.dobred = D.red)-'+
  21.                          '(SELECT COALESCE(SUM(K.kompenz),0.0) FROM dobavkom K '+
  22.                           'WHERE K.dobred = D.red) dug '+
  23.                   'FROM dobavljac D '+
  24.                   'WHERE D.datum BETWEEN :odd AND :dod '+
  25.                   'ORDER BY D.datum');
  26.   QFinans.ParamByName('odd').AsDateTime := DTOdd.DateTime;
  27.   QFinans.ParamByName('dod').AsDateTime := DTDod.DateTime;
  28.  
  29.   ShowMessage(DateTimeToStr(QFinans.ParamByName('odd').AsDateTime)+' - '+
  30.                           DateTimeToStr(QFinans.ParamByName('dod').AsDateTime));  // ---> show  '2010-01-01 - 2017-12-22'
  31.  
  32.   QFinans.Open;
  33.   DisableMonitoring;  
  34.  

where DTOdd and DTDod is TDateTimePicker component, witch I use widely.

The IBSQLMonitor trace for execute query:

Code: MySQL  [Select][+][-]
  1. 2018-03-12 17:04:52
  2.  
  3. [Application: VzbFinans]
  4. QFinans: [Execute]
  5. SELECT  D.red, D.ino, D.roba, D.datum, D.predracun, D.racunbr,D.dobavljac, D.majstor,
  6.                D.namena, D.napomena, D.opis,D.mestotr, D.firma, D.rokpl, D.osnovica, D.pdv,
  7.                D.valuta, D.vrsta, D.potvrda, D.nacinpl, D.sporno,D.korisnik, D.dat_izm, D.porez,
  8.                D.iznospdv,D.zatvoren, D.bojana1, D.bojana2,
  9.               (SELECT COALESCE(SUM(U.uplata),0.0) FROM dobavupl U WHERE U.dobred = D.red) placeno,
  10.               (SELECT MAX(U.datum) FROM dobavupl U WHERE U.dobred = D.red) datumpl,
  11.               (SELECT COALESCE(SUM(K.kompenz),0.0) FROM dobavkom K WHERE K.dobred = D.red) kompenz,
  12.               (SELECT MAX(K.datum) FROM dobavkom K WHERE K.dobred = D.red) kompdat,
  13.               D.iznospdv-(SELECT COALESCE(SUM(U.uplata),0.0) FROM dobavupl U WHERE U.dobred = D.red)-
  14.                                  (SELECT COALESCE(SUM(K.kompenz),0.0) FROM dobavkom K WHERE K.dobred = D.red) dug
  15.  FROM  dobavljac D
  16. Where  D.datum BETWEEN :odd AND :dod
  17.  
  18.   ODD = <NULL>
  19.   DOD = <NULL>
  20.  

As you can see ODD and DOD parameters is NULL, wherry strange ! The actual value should be '2010-01-01' and '2017-12-22'.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: rvk on March 12, 2018, 05:47:48 pm
As you can see ODD and DOD parameters is NULL, wherry strange ! The actual value should be '2010-01-01' ans '2017-12-22'.
And you say these values were correct for "2018-01-01" and "2018-03-01" ?

In my testprogram (with the latest IBX) it works fine.
(and it states values other than NULL for odd and dod)
Code: [Select]
  ODD = 01-01-2018 18:02:08.147
  DOD = 06-02-2018 18:02:11.666
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 12, 2018, 07:27:53 pm
Yes I'm sure that interval is Ok, this Query is running in an old application with ZeosDBO with same parametrization without a problem ...
Personally I think it is a bug in IBX. With simple queries everything is working as expected but with relatively complex queries is something went wrong ... this is just my opinion, maybe I'm wrong, needs more testing ...
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: rvk on March 12, 2018, 07:31:36 pm
So, I take it for "2018-01-01" and "2018-03-01" you also got NULL with this query.

If so, could you strip the sql until it works. Maybe just removing those select-fields.

Otherwise you could maybe post a small sample database and query to reproduce this.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: rvk on March 12, 2018, 08:26:23 pm
Personally I think it is a bug in IBX. With simple queries everything is working as expected but with relatively complex queries is something went wrong ... this is just my opinion, maybe I'm wrong, needs more testing ...
You could be right about IBX misinterpreting the parameters (or not finding them correctly).

This could be a bug and needs to be determined but could you try setting ParamCheck to false and see if that helps. If it helps than it could indeed be the "complexity" of the SQL (although it is not very complex so it should be resolved).

(In that case the bug could probably be in TParams.ParseSQL() somewhere)
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: dsiders on March 12, 2018, 09:21:11 pm

Query.SQL.Add ('SELECT datum, some_fields FROM some_table WHERE datum BETWEEN :fromdate AND :todate ORDER BY datum');
Query.ParamByName('fromdate').AsDate := DateTimPicker_From.Date;
Query.ParamByName('todate').AsDate := DateTimPicker_To.Date;
Query.Open;

Query.Eof is always True, but there are certain entries in the SQL table for that period.

when I replaced the SQL parameters to  "2018-01-01" and "2018-03-01", I got the correct result.

I would like to use the Parameterized Query as the date range varies, depending on how the user chooses.

Perhaps you could cast the parameters in the select statement, like:

Code: [Select]
SELECT datum, some_fields FROM some_table
WHERE datum BETWEEN DATE :fromdate AND DATE :todate ORDER BY datum

Not sure this will actually help, but it's something else to try.
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: tonyw on March 13, 2018, 01:19:33 am
Yes I'm sure that interval is Ok, this Query is running in an old application with ZeosDBO with same parametrization without a problem ...
Personally I think it is a bug in IBX. With simple queries everything is working as expected but with relatively complex queries is something went wrong ... this is just my opinion, maybe I'm wrong, needs more testing ...

Attached is a simple example using the Firebird employee database and which I have adapted from ibx/examples/employee. It should illustrate how to use IBX with date ranges and parameterised queries. It works for me.

If you look on the main form at the Employees (TIBDataSet) the SelectSQL has  "Where HIRE_DATE Between :FromDate and :Todate " as does the TotalsQuery (TIBQuery). In both cases, the parameter values are set in the BeforeOpen event handler from TDateEdit Source. Employees is a very complex query using a recursive expression, while the totals query is a very simple select query. Perhaps you can see where your example differs from this one.

Tested on Linux Mint 18, fpc 3.0.4 and Lazarus 1.8.2  with IBX 2.2.0
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 13, 2018, 02:29:05 pm
Hi Tony !

Here is a test program based on the Employee database, tested ...
At first it seems to have worked for me, but when I started it again, it did not work after the date change. The monitor tab can look at the execution SQL result ...
Title: Re: IBX for Lazarus, parameter AsDateTime problem
Post by: tonyw on March 13, 2018, 03:21:46 pm
Hi Tony !

Here is a test program based on the Employee database, tested ...
At first it seems to have worked for me, but when I started it again, it did not work after the date change. The monitor tab can look at the execution SQL result ...
I can see now where the problem lies. You are using a TIBDynamicGrid and that only works if the parameter values are set in the BeforeOpen event handler. Although that is how all the examples work, I would agree that this should be clearer in the documentation. The same constraint applies to the other IBX dynamic controls.

The reason for this is that they are designed to manipulate the SQL Query behind the scenes - closing and re-opening the dataset. TIBDynamicGrid does this in order to modify the SQL order by clause so that you can re-order the grid by simply clicking on the column heading. However, when the SQL is changed, behind the scenes or otherwise, TIBQuery clears its parameters and re-parses the SQL losing any settings made before you call TIBQuery.Open. If you set the parameters in the BeforeOpen handler then this is again called after the TIBDynamicGrid has updated the SQL and are hence always applied.

In theory it would be possible to save the parameter settings while TIBDynamicGrid does its work and then re-apply them if the number of parameters, their name and type have not changed. However, I personally prefer to update the documentation and make this constraint clearer.

On the other hand, you could always use a standard TDBGrid as this should work with your code - but then you lose dynamic column sizing and sorting.

Also worth noting that by turning off monitoring as soon as the dataset is opened, you missed all the dynamic SQL management that goes on. It you comment out the Disable Monitoring then you actually start to see what is happening e.g. when you click on a column header.
Title: Re: [SOLVED] IBX for Lazarus, parameter AsDateTime problem
Post by: patyi on March 13, 2018, 08:52:17 pm
Ok, thanks to clarifying the things, honestly I don't expect that DynamicGrid is working in that way. Now I know what to expect and on my own is to decide where and when to use this special controls. Yes you are right about standard DBGrid, it's working as expect in my sample code.

Thanks, I learned again something useful  !
TinyPortal © 2005-2018