ZeosLib 8.0.0, Firebird and a Query parameters

[korecek]

Hi,
I have a problem with ZeosLib and its parameters.
I use Firebird 2.5, Embarcadero C++ Builder XE6, XE10 and XE10.3 Rio.
I can see difference between ZeosLib 7.2.4-stable and v8.0.0 Beta.

Code I use in my application is same for both above named versions of Zeos. The query which I use is
also same but difference is in INTEGER parameters which these Zeos version use for the query evaluating.

I activated ZSQLMonitor to see how is query evaluated.

In Zeos 7.2.4-stable I can see:

Code: Select all

2021-03-31 22:54:50 cat: Prepare, proto: firebirdd-2.5, msg: Statement 10 : SELECT ID_PLACE_POINT,PP_PLACE_CATEGORY_ID, PP_POINT_NAME, PP_POSITION_JTSKX, PP_POSITION_JTSKY, PP_STRPOSITION_JTSKX, PP_STRPOSITION_JTSKY, PP_REGION, PP_DISTRICT, PP_MUNICIPALITY_EXTACT, PP_ZIPCODE, PP_ZIPCODE_STR, PP_MUNICIPALITY, PP_STREET, PP_HOUSE_NUM, PP_HOUSE_NUMSTR, PP_ORIENT_HOUSENUM, PP_PHONE, PP_CATEGORY_TMP FROM PLACE_POINT_HEALTH WHERE ( (PP_POINT_NAME CONTAINING ?) OR (PP_REGION CONTAINING ?) OR (PP_DISTRICT CONTAINING ?) OR (PP_MUNICIPALITY_EXTACT CONTAINING ?) OR (PP_ZIPCODE CONTAINING ?) OR (PP_MUNICIPALITY CONTAINING ?) OR (PP_STREET CONTAINING ?) OR (PP_HOUSE_NUM CONTAINING ?) OR (PP_ORIENT_HOUSENUM CONTAINING ?) OR (PP_PHONE CONTAINING ?) )
2021-03-31 22:54:50 cat: Bind prepared, proto: firebirdd-2.5, msg: Statement 10 : '549','549','549','549',549,'549','549',549,'549','549',
2021-03-31 22:54:50 cat: Execute prepared, proto: firebirdd-2.5, msg: Statement 10

In Zeos 8.0.0 beta I can see:

Code: Select all

'2021-04-14 23:04:44.652' cat: Prepare, proto: firebird, msg: Statement 12 : SELECT ID_PLACE_POINT,PP_PLACE_CATEGORY_ID, PP_POINT_NAME, PP_POSITION_JTSKX, PP_POSITION_JTSKY, PP_STRPOSITION_JTSKX, PP_STRPOSITION_JTSKY, PP_REGION, PP_DISTRICT, PP_MUNICIPALITY_EXTACT, PP_ZIPCODE, PP_ZIPCODE_STR, PP_MUNICIPALITY, PP_STREET, PP_HOUSE_NUM, PP_HOUSE_NUMSTR, PP_ORIENT_HOUSENUM, PP_PHONE, PP_CATEGORY_TMP FROM PLACE_POINT_HOSPITAL WHERE ( (PP_POINT_NAME CONTAINING ?) OR (PP_REGION CONTAINING ?) OR (PP_DISTRICT CONTAINING ?) OR (PP_MUNICIPALITY_EXTACT CONTAINING ?) OR (PP_ZIPCODE CONTAINING ?) OR (PP_MUNICIPALITY CONTAINING ?) OR (PP_STREET CONTAINING ?) OR (PP_HOUSE_NUM CONTAINING ?) OR (PP_ORIENT_HOUSENUM CONTAINING ?) OR (PP_PHONE CONTAINING ?) )
'2021-04-14 23:04:44.652' cat: Bind prepared, proto: firebird, msg: Statement 12 : '549','549','549','549','','549','549','','549','549'
'2021-04-14 23:04:44.663' cat: Execute prepared, proto: firebird, msg: Statement 12, affected row(s): 0, elapsed time: '00:00:00.006'
'2021-04-14 23:04:44.669' cat: Fetch complete, proto: firebird, msg: Statement 12, fetched row(s): 283
'2021-04-14 23:04:49.294' cat: Unprepare prepared, proto: firebird, msg: Statement 12

There are 10 parameters where two are INTEGER (PP_ZIPCODE and PP_HOUSE_NUM) and
the others are VARCHAR parameters.

In 7.2.4 are Integer's parameters set as integer (549) while in 8.0.0 beta version I can see there
only empty apostrophes ('') which is unexpected 'value' of parameter I think.

Above mentioned "wrong" parameter ('') I have got in all versions C++ Builder where I installed
ZeosLib 8.0.0 beta (XE6 and XE10.3).
I have also disabled the TZParams (see DISABLE_ZPARAM) and I used TParams instead.
Result was same -> ('').

Question:
What should I do or change in my code to force Zeoslib v8.0.0 use the correct integer parameter, i.e. 549,
instead of an empty apostrophes ('')?

Thank you in advance for any help.

Vaclav

[aehimself]

I think an important question is if the query is executing correctly or not? Is it a functional or display issue?

[korecek]

A nice question. Thank you. I have to say to your question that the query works very well in
IBExpert (SQL Editor) tool or in the Squirrel-SQL client. No problem.
Zeos component works well but with wrong (different) parameters.
However you made me to more investigate in differences of Zeos v7.2.4 vs. v8.0.0.

Now I can more detail specify my question although I am not sure if it is not already documented.
1. PP_ZIPCODE column is defined this way:

Code: Select all

 
 CREATE TABLE  PLACE_POINT_HOSPITAL (
    ....
    PP_POINT_NAME  VARCHAR(200) ,
   ...
    PP_ZIPCODE   INTEGER CHECK ((VALUE >= 10000) AND (VALUE < 80000) OR (VALUE IS NULL)),
    ....
);

2. Code in my application:

Code: Select all

 UnicodeString stSearch = "549";
 int siSearch = 549; 
...
   ZQueryPlaces->Params->Clear();
...
   ZQueryPlaces->Params->CreateParam(ftString,"xPOINT_NAME",ptInput);
   ZQueryPlaces->ParamByName("xPOINT_NAME")->Value = stSearch;
  .....
   ZQueryPlaces->Params->CreateParam(ftInteger,"xZIPCODE",ptInput);
   ZQueryPlaces->ParamByName("xZIPCODE")->Value = siSearch;
  ....

Note: The xZIPCODE parameter use ftInteger.
This code provides the query parameters I have already written in previous text as:
'549', .... 549, .... in Zeos v7.2.4 while in Zeos v8.0.0 it generates parameters following way -> '549', ..... '', ... .

3. ...but, I found out that if I use for parameter xZIPCODE ftString in Zeos 8.0.0 it works same as in Zeos v7.2.4 for ftInteger.

Code: Select all

   ZQueryPlaces->Params->CreateParam(ftString,"xPOINT_NAME",ptInput);
   ZQueryPlaces->ParamByName("xPOINT_NAME")->Value = stSearch;
  .....
    ZQueryPlaces->Params->CreateParam(ftString,"xZIPCODE",ptInput);
    ZQueryPlaces->ParamByName("xZIPCODE")->Value = stSearch;
....

In Zeos v8.0.0 works above code (part 3.) exactly same as in example 2. (above) for Zeos v7.2.4. Provided parameters are: '549', .... '549', ... .

Question is:
Is it really true that all input parameters are used as ftSting in Zeos v8.0.0? Can I find somewhere rules for parameter's using in Zeos v8.0.0?
I thought that to the Integer parameter should by assigned integer value and not string.

TIA

[aehimself]

Can you please check if the issue will be solved if you use Param.AsInteger instead of Param.Value?
Value directly sets the variant behind so the type might not be detected correctly, while the .Asxxx properties make sure that the type is also changed.

[korecek]

I know about possibility to use AsInteger but I have tried another solution.
Shortly: Using of "AsInteger" does not help either.

Now I tried following code:

Code: Select all

     siSearch = 549;
     ZQuerPlaces->Params->CreateParam(ftInteger,"xZIPCODE",ptInput);
     ZQueryPlaces->Params->ParamByName("xZIPCODE")->AsInteger = siSearch;

     siSearch = 0;
     siSearch = ZQueryPlaces->ParamByName("xZIPCODE")->Value; /* here I got same value (549) which 
                                                                I entered through using of "AsInteger" above  */

Parameter used in query is still empty apostrophe ('').

[aehimself]

I'm not sure if Zeos works different when using it from C++ builder, but in Delphi ZQuerys parse the SQL and create their params when the text changes.

E.g.:

ZQuery.SQL.Text := '';
// ZQuery.Params.Count = 0
ZQuery.SQL.text := 'SELECT * FROM MyTable WHERE MyField = :pMyFieldValue OR MyField = :pMyFieldValue2';
// ZQuery.Params.Count = 2
// Now we simply can write ZQuery.ParamByName('pMyFieldValue').AsInteger := 42;
// without creating anything else

Are you sure that creating a new parameter is required under C++ builder? Maybe we can try without clearing and recreating the params, only resetting their values?

[aehimself]

There is a strange thing I found though.

Code: Select all

 ZQuery1.SQL.Text := ':param';

 var p := ZQuery1.ParamByName('param');
 p.Value := 549;
 // p.DataType = ftUnknown
 p.AsInteger := 549;
 // p.DataType := ftInteger;

The issue is with TZParam.SetAsVariant (ZDataSetParam : 3794)

Code: Select all

  if SQLType <> FSQLType then //mimic the TParam behavior
    SetSQLType(SQLType);

FSQLType (and therefore p.DataType) is correctly set to ftWord at this level, but as original SQLType is ftUnknown, it resets it back. Maybe it was meant to be SetSQLType(FSQLType)...?

I'll see what the query does once I try to execute it...

Edit: it seems parameter binding is RDBMS-specific, testing it in MySQL seems to bind the value correctly even if it is ftUnknown:

'2021-04-16 15:04:30.067' cat: Bind prepared, proto: mysql, msg: Statement 5 : 549
'2021-04-16 16:04:54.506' cat: Execute, proto: mysql, msg: Statement 5 : SELECT * FROM Clients WHERE ClientID = ?, elapsed time: '00:00:00.005'

Unfortunately I don't have access to any Firebird instances so I can not investigate further :(

Alas, setting parameter values with the .Asxxx should fix this behavior.

[korecek]

I am sorry for my late answer because of another project.

To your question:

Are you sure that creating a new parameter is required under C++ builder? Maybe we can try without clearing and recreating the params, only resetting their values?

What you asked me for was used in original project with Zeos v7.2.4 where I have used such solution. It works quite well in original version with Zeos v7.2.4.
Now I also tried same with Zeos v8.0.0 beta and I have had same problem as if I have cleared and recreated the params. It means that if I use integer values for integer item then query does not work properly but if I use string value for integer item then query works quite well.

Unfortunately I don't have access to any Firebird instances so I can not investigate further

Could I help with some code with Firebird and with C++?

Alas, setting parameter values with the .Asxxx should fix this behavior.

As I written before using .ASxxx does not help with using of right parameter like is integer in my case but using String provides "workaround".

I have only a question. My apps with Zeos v7.2.4 in XE10 Seattle works well but in XE6 and XE10.3 Rio with Zeos v8.0.0.beta needs above mentioned "workaround". Is it possible to find difference in Zeos versions and what part of code could it be?

[marsupilami]

Now I also tried same with Zeos v8.0.0 beta and I have had same problem as if I have cleared and recreated the params.

Please don't clear the parameters. It simply isn't necessary. Zeos will recreate parameters as soon as you change the SQL but try to preserve the data. If you want to be sure about the data in the parameters, just assign all parameters and use the .AsXXX properties and never the .Value property.

Could I help with some code with Firebird and with C++?

Yes - please try to create a minimum working example that produces the error. We run tests on Zeos on a regular basis and this problem doesn't show up. Please include a table definition in your example as well as a sample project that produces the error.

Is it possible to find difference in Zeos versions and what part of code could it be?

No - unfortunately this is not possible. A lot of things have changed between Zeos 7.2 and 8.0. There is one more thing you could try to see if it makes a difference: use "interbase" as the protocol name instead of "firebird". With Zeos 7.2 there is no difference. With Zeos 8.0 "firebird" will use a completely new driver, if your firebird client library has version 2.5 or newer.

[korecek]

Hi, I re-created my app not to be depended on third party components to be as simple as possible.
I have also cut most of data tables from database and cut most of data of the table.
I also changed some data of the table.

There is provided definition DB as well as the database backup.

There is also included ZSQLMonitor which generates log file.
It seems to me it generates wrong time.

Best regards,
Vaclav

[korecek]

Hello,
after some time we returned to this because we are now converting project from XE5 to XE10.4 C++ Builder.
Here are some of our results for the issue:

just assign all parameters and use the .AsXXX properties and never the .Value property.

This doesn't work in either case because there seems to be probably some issue with ftInteger (BigInt in DB).

I have found out some workarounds for this problem:

1) Using recommended .AsXXX without clearing params:

• I get it working by replacing all .AsInteger to .AsString and convert integers to strings in C++ code before assigning to param.

2) Original use of .Value from XE5:

• This I got working too by first use Params->Clear() and then Params->CreateParam but with ftString not ftInteger. Then I simply assigned to .Value integer and it worked.

3) modification of SQL command:
- Original Query:

Code: Select all

SELECT ID_PLACE_POINT,  PP_POINT_NAME,  PP_ZIPCODE,  PP_MUNICIPALITY,  PP_STREET,  PP_HOUSE_NUM,  PP_ORIENT_HOUSENUM
FROM PLACE_POINT_HEALTHCENTRE
WHERE  
(
    (PP_POINT_NAME CONTAINING :xPOINT_NAME) 
     OR  (PP_ZIPCODE CONTAINING :xZIPCODE) 
     OR  (PP_MUNICIPALITY CONTAINING :xMUNICIPALITY) 
     OR  (PP_STREET CONTAINING :xSTREET)
     OR  (PP_HOUSE_NUM CONTAINING :xHOUSE_NUM ) 
     OR  (PP_ORIENT_HOUSENUM = :xORIENT_HOUSENUM)
)

- Modified Query:

Code: Select all

SELECT ID_PLACE_POINT,  PP_POINT_NAME,  PP_ZIPCODE,  PP_MUNICIPALITY,  PP_STREET,  PP_HOUSE_NUM,  PP_ORIENT_HOUSENUM
FROM PLACE_POINT_HEALTHCENTRE
WHERE  
(
    (PP_POINT_NAME CONTAINING :xPOINT_NAME)
    OR  (PP_ZIPCODE CONTAINING CAST(:xZIPCODE AS BIGINT))
    OR  (PP_MUNICIPALITY CONTAINING :xMUNICIPALITY)
    OR  (PP_STREET CONTAINING :xSTREET) 
    OR  (PP_HOUSE_NUM CONTAINING CAST(:xHOUSE_NUM  AS BIGINT)) 
    OR  (PP_ORIENT_HOUSENUM = :xORIENT_HOUSENUM)
)

With CAST(xxx AS BIGINT) all original code from XE5 starts working without needing to do modifications to code as noted in points 1 and 2.


Note: I would have given the example source code with translated *.exe file and with DB, but it seems to be too big for allowed attachment size.

[marsupilami]

I think, I fixed this. Please ckeck the current SVN version.

Note: You should always use .AsString for conditions like '(PP_ZIPCODE CONTAINING :xZIPCODE)'. Containing expects VARCHAR values and Firebird signals to Zeos that it expects :xZIPCODE to be a VARCHAR type. Zeos then converts yor integer value to a string in any case. The error surfaced because Zeos made a mistake during the conversion. That error got fixed by my changes.

Best regards, Jan

[korecek]

Thank you for your fix regarding this.
We tested the latest update and it works quite well.

Once more thanks.

Best regards,
Vaclav