Recent

Author Topic: [SOLVED] IBX for Lazarus, parameter AsDateTime problem  (Read 2971 times)

patyi

  • Full Member
  • ***
  • Posts: 152
[SOLVED] IBX for Lazarus, parameter AsDateTime problem
« 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)
« Last Edit: March 13, 2018, 08:51:26 pm by patyi »

GetMem

  • Hero Member
  • *****
  • Posts: 3256
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #1 on: March 12, 2018, 09:21:00 am »
frmdate <> fromdate

patyi

  • Full Member
  • ***
  • Posts: 152
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #2 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.

rvk

  • Hero Member
  • *****
  • Posts: 3508
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #3 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.

patyi

  • Full Member
  • ***
  • Posts: 152
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #4 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 ...
« Last Edit: March 12, 2018, 09:41:14 am by patyi »

rvk

  • Hero Member
  • *****
  • Posts: 3508
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #5 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
« Last Edit: March 12, 2018, 10:30:24 am by rvk »

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #6 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.

patyi

  • Full Member
  • ***
  • Posts: 152
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #7 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'.
« Last Edit: March 12, 2018, 06:45:51 pm by patyi »

rvk

  • Hero Member
  • *****
  • Posts: 3508
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #8 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
« Last Edit: March 12, 2018, 06:12:35 pm by rvk »

patyi

  • Full Member
  • ***
  • Posts: 152
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #9 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 ...

rvk

  • Hero Member
  • *****
  • Posts: 3508
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #10 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.
« Last Edit: March 12, 2018, 07:33:56 pm by rvk »

rvk

  • Hero Member
  • *****
  • Posts: 3508
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #11 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)

dsiders

  • Jr. Member
  • **
  • Posts: 62
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #12 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.
Lazarus 1.8.2 / FPC 3.0.4 / Windows 8.1 64-bit

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #13 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

patyi

  • Full Member
  • ***
  • Posts: 152
Re: IBX for Lazarus, parameter AsDateTime problem
« Reply #14 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 ...