Page 1 of 1
TClientdataSet.locate on TUniQuery fail
Posted: Thu 18 Jun 2015 16:54
by 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
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 05:10
by AlexP
Hello,
We cannot reproduce the problem. Please provide the table creating script and the SQL query you are using.
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 06:33
by 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
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 07:35
by 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.
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 08:36
by 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
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 09:18
by 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).
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 09:51
by 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
Re: TClientdataSet.locate on TUniQuery fail
Posted: Fri 19 Jun 2015 10:14
by AlexP
You are welcome.
Yes, this is correct code. Moreover, you should also add the MSDataTypeMapUni module to the Uses section.