TClientdataSet.locate on TUniQuery fail

[Jeweller]

Hello,
Im migrating my application from ADO (D7, TADOquery -> TDatasetProvider -> TClientdataset) to UNIDAC component (both Oracle or SQLServer Provider).

I make a simple locate() on the clientDataset. This one worked fine before, but not now with UniDac. I do not understand at all what I've done!! This is a very simple test case!

Here is the code:

Code: Select all

   uniquery1.close;
   uniquery1.ParamByName('NumFol').Value := 17;
   uniquery1.ParamByName('Codpro').Value := 'AD';
   uniquery1.ParamByName('Coduti').Value := 'MR';
   uniquery1.open; //just to prepare and get the data (->single row)
   ClientDataSet1.open;
   ClientDataSet1.Locate('ID_NUMFOL;NU_TYPE;ST_DES',varArrayOF(['17',1,'BUS - BT + Détails et Coûts']),[loCaseInsensitive]);  //EDataBaseError on DB.pas - "the constant is not of type 17 correct"

Code: Select all

   ADOQuery1.close;
   ADOQuery1.Parameters.ParamByName('NumFol').Value := 17;
   ADOQuery1.Parameters.ParamByName('Codpro').Value := 'AD';
   ADOQuery1.Parameters.ParamByName('Coduti').Value := 'MR';
   ADOQuery1.open; //same query, same database (oracle)
   ClientDataSet2.open;
   ClientDataSet2.Locate('ID_NUMFOL;NU_TYPE;ST_DES',varArrayOF(['17',1,'BUS - BT + Détails et Coûts']),[loCaseInsensitive]);  //works perfectly

Does someone have experience something like this or can point me on the way to find a solution?
I cannot continue my migration to Unidac instead of making this behavior out.
Regards

[AlexP]

Hello,

We cannot reproduce the problem. Please provide the table creating script and the SQL query you are using.

[Jeweller]

Ok
Here is the SQL to create the table used:

Code: Select all

CREATE TABLE ETAITEM (
  ID_NUMITEM NUMBER(10, 0) NOT NULL,
  ID_CODPRO  VARCHAR2(3 BYTE),
  ID_CODUTI  VARCHAR2(20 BYTE),
  ST_DES     VARCHAR2(60 BYTE),
  NU_SIZE    NUMBER(10, 0),
  NU_TYPE    NUMBER(10, 0),
  DT_MAJ     DATE,
  DT_DEL     DATE,
  OL_DOC     LONG RAW,
  ID_NUMFOL  NUMBER(10, 0),
  ST_STD     VARCHAR2(1 BYTE),
  NU_DATA    NUMBER(3, 0)
);

CREATE UNIQUE INDEX ID_NUMITEM_AK ON ETAITEM (ID_NUMITEM);

INSERT INTO ETAITEM(ID_NUMITEM, ID_CODPRO, ID_CODUTI, ST_DES, NU_SIZE, NU_TYPE, DT_MAJ, DT_DEL, OL_DOC, ID_NUMFOL, ST_STD, NU_DATA) VALUES
(849, NULL, NULL, 'BUS - BT + Détails et Coûts', 86267, 1, '12/20/2010 12:04:06', NULL, TMP_LONGRAW_8, 17, 'N', NULL);
END;
/

And Here is the query used Inside TUniQuery/TADoQuery:

Code: Select all

SELECT * FROM ETAITEM
WHERE ID_NUMFOL = :NumFol
AND (ID_CODPRO = :CodPro OR ID_CODPRO = '' OR ID_CODPRO IS NULL)
AND (ID_CODUTI = :CodUti OR ID_CODUTI = '' OR ID_CODUTI IS NULL)

Complete script is here (with full longraw data):
http://mainta.apavealsacienne.com/etaitem.zip

[AlexP]

This problem is due to the bug in Delphi: http://qc.embarcadero.com/wc/qcmain.aspx?d=6142 . As a workaround, you can use DataTypeMapping: http://www.devart.com/unidac/docs/data_type_mapping.htm .

Code: Select all

uses ..., OraDataTypeMapUni;
...
  uniquery1.close;
  uniquery1.DataTypeMap.AddDBTypeRule(oraNumber, 0, 10, ftInteger);
...

In this case NUMBER(10,0) fields will always have the ftInteger type as set in the mapping rules.

[Jeweller]

Yes! Great, it solve my problem.
Can I apply the same fix for both cases: SQL Server and Oracle ? Or should I care before on which provider Im running ?
Thanks a lot
Regards

[AlexP]

Yes, you can set mapping rules for both providers simultaneously, specifying the original field types and size for each server (e.g, for the SQL Server BigInt type - msBigint should be specified).

[Jeweller]

Ok, I haven't test yet under mssql. So I need to add this code, right ? :

Code: Select all

DataTypeMap.AddDBTypeRule(msBigint, ftInteger);

Thanks a lot for your so quick and usefull advise. You have great components!
Regards

[AlexP]

You are welcome.

Yes, this is correct code. Moreover, you should also add the MSDataTypeMapUni module to the Uses section.