Problem with TDateTime field x Oracle 10 x Lazarus 1.0.12 lazarus / 16-Databases ---------------------------------------------------------------------- dailton · 2014-06-06 00:28 Hello Guys, I have 3 basic questions related to access to Oracle 10 using Lazarus 1.0.12 for fields of type TDateTime using TOracleConnection, TSQLQuery components: Doubt 1: When I do an INSERT of a TDateTime field, the value stored in Oracle is  truncated losing time value. Even using the following command, the field is truncated and stored in Oracle: SQLQueryIns.Params.ParamByName ('LoginTime') AsDataTime: = now; I got around this problem by modifying the INSERT as follows: insert into sg.FTP_Account (LoginTime) Values ​​(to_date (: LoginTime, 'DD / MM / YYYY HH24: MI: SS')) next I used the following command: SQLQueryIns.Params.ParamByName ('LoginTime') AsString: = FormatDateTime('dd/mm/yyyy hh:nn:ss',now); It worked but  I think it' s not the natural way. Doubt 2: When I retrieve a TDateTime Oracle field in a grid, the value is truncated (only the part of the date) Although the field is correct in Oracle. I got around this problem by  modifying the event onGetText of the field as follows: procedure TfrmSimpleBrowser.SQLQueryGetText(Sender: TField; var aText: string;   DisplayText: Boolean); begin   aText:=UTF8Encode(sender.AsWideString); end;  It worked but again  I think it' s not the natural way. Doubt 3: When I retrieve fields with accented characters of the Portuguese character then char ? is shown. I tried to set the CharSet = utf8 property in TOracleConnection but it did not work. Could someone give me a hand. Grateful Dailton Menezes ---------------------------------------------------------------------- BigChimp · 2014-06-06 06:20 1. Have you checked the bugtracker for those bugs? What FPC version is Lazarus 1.0.12 compiled with? There may be fixed bugs in Lazarus 1.2.2/FPC 2.6.4 2. FPC does not yet have explicit unicode support in its libraries - it just passes along strings without converting them in sqldb. Make sure the db server, client library all use UTF8 encoding. Setting the connection property by itself obviously is not enough. ---------------------------------------------------------------------- LacaK · 2014-06-06 06:33 Re Doubt1: Yes it seems, that time portion is not set. I can try patch it and send you patched oracleconnection.pp for testing. I am not Oracle user so I can not test it myself. Can you try with attached file? ---------------------------------------------------------------------- dailton · 2014-06-06 12:55 Thanks for your reply, I tried to compile the new Oracleconnection.pp but i've got an error: oracleconnection.pp(106,20) Error: There is no method in an ancestor class to be overridden: "class TOracleConnectionDef.LoadedLibraryName:AnsiString;" oracleconnection.pp(109,1) Fatal: There were 1 errors compiling module, stopping it seems that there are dependencies. Tks. ---------------------------------------------------------------------- BigChimp · 2014-06-06 13:03 Please upgrade; e.g. to Laz 1.2.2/FPC 2.6.4; the LoadedLibraryName stuff is definitely in there, see e.g. http://svn.freepascal.org/cgi- bin/viewvc.cgi/tags/release_2_6_4/packages/fcl- db/src/sqldb/oracle/oracleconnection.pp?revision=26871&view=markup ---------------------------------------------------------------------- dailton · 2014-06-06 14:22 thanks for your reply. Ok, I'll do what you suggested and the following week I will answer. Regards. Dailton Menezes ---------------------------------------------------------------------- dailton · 2014-06-07 13:05 Hi, I installed a secondary instalation of Lazarus 1.2.2/fpc 2.6.4 and tried to compile my project using the new oraconnection.pp sent by you. New errors raised: oracleconnection.pp(121,22) Hint: Parameter "ictxp" not used oracleconnection.pp(121,36) Hint: Parameter "bindp" not used oracleconnection.pp(121,52) Hint: Parameter "iter" not used oracleconnection.pp(121,62) Hint: Parameter "index" not used oracleconnection.pp(133,37) Hint: Parameter "bindp" not used oracleconnection.pp(133,53) Hint: Parameter "iter" not used oracleconnection.pp(133,63) Hint: Parameter "index" not used oracleconnection.pp(315,38) Hint: Local variable "errcode" does not seem to be initialized oracleconnection.pp(346,66) Hint: Local variable "i" does not seem to be initialized oracleconnection.pp(350,66) Hint: Local variable "f" does not seem to be initialized oracleconnection.pp(332,5) Note: Local variable "db" not used oracleconnection.pp(405,55) Hint: Local variable "TempServiceContext" does not seem to be initialized oracleconnection.pp(433,55) Hint: Local variable "TempServiceContext" does not seem to be initialized oracleconnection.pp(597,5) Note: Local variable "db" not used oracleconnection.pp(865,95) Error: Wrong number of parameters specified for call to "Add" oracleconnection.pp(1120) Fatal: There were 1 errors compiling module, stopping Before compiling with the new oraconnection.pp, I tried the old one and the time portion of tdatetime field was not set yet. Thanks Dailton Menezes ---------------------------------------------------------------------- LacaK · 2014-06-09 05:00 Sorry, original "oraconnection.pp" was taken from TRUNK version of FPC. Here is attached version taken from FPC 2.6.4. It should compile without errors. Can you please re-try? (may be with both attached versions) ---------------------------------------------------------------------- dailton · 2014-06-10 00:28 Hi, Well done, it worked. Congratulation. I made two tests: 1) select sysdate from dual => and time portion was retrieved successfully 2) Next, I tried an insert => and time portion was stored successfully too. thank you very  much Dailton Menezes ---------------------------------------------------------------------- LacaK · 2014-06-10 06:15 Thanks for testing. I have reported it in bug tracker with attached patch: http://bugs.freepascal.org/view.php?id=26321 ---------------------------------------------------------------------- BigChimp · 2014-06-10 06:51 Unfortunately we're struggling with the db tests failing on at least my machine; see bug http://bugs.freepascal.org/view.php?id=26078 @Dailton: Perhaps you could run the dbtestframework test suite against a test Oracle database and post the results as an attachment? 1. cd \packages\fcl-db\tests 2. copy database.ini.txt to database.ini 3. edit database.ini, set [Database] type=oracle and in the Oracle section set up your connection details e.g. [oracle] ; Oracle database: connector=sql connectorparams=oracle ;name means instance here name=xe user=system password=oracle hostname=192.168.1.2 4. Compile dbtestframework: fpc dbtestframework.pas 5. Run dbtestframework: dbtestframework --format=plain --skiptiming -all > Oracleoutput.txt 6. Zip and attach OracleOutput.txt Thanks a lot! ---------------------------------------------------------------------- dailton · 2014-06-11 20:40 Hi, Sorry for the long delay. I'll be trying tonight and I'll give an answer tomorrow. Regards Dailton Menezes ---------------------------------------------------------------------- dailton · 2014-06-11 20:59 Hi, Here is the output from the compile process: C:\>cd\lazarus1-22\fpc\2.6.4\source\packages\fcl-db\tests C:\lazarus1-22\fpc\2.6.4\source\packages\fcl-db\tests>Compile dbtestframework: f pc dbtestframework.pas 'Compile' não é reconhecido como um comando interno ou externo, um programa operável ou um arquivo em lotes. C:\lazarus1-22\fpc\2.6.4\source\packages\fcl- db\tests>C:\lazarus1-22\fpc\2.6.4\b in\i386-win32\fpc.exe dbtestframework.pas Free Pascal Compiler version 2.6.4 [2014/04/18] for i386 Copyright (c) 1993-2014 by Florian Klaempfl and others Target OS: Win32 for i386 Compiling dbtestframework.pas Compiling toolsunit.pas Compiling sqldbtoolsunit.pas sqldbtoolsunit.pas(168,76) Warning: Constructing a class "TOracleConnection" wit h abstract method "LoadBlobIntoBuffer" Compiling dbftoolsunit.pas Compiling bufdatasettoolsunit.pas bufdatasettoolsunit.pas(90,50) Warning: Constructing a class "TPersistentBufData Set" with abstract method "InternalInitFieldDefs" bufdatasettoolsunit.pas(90,50) Warning: Constructing a class "TPersistentBufData Set" with abstract method "LoadBlobIntoBuffer" bufdatasettoolsunit.pas(124,50) Warning: Constructing a class "TPersistentBufDat aSet" with abstract method "InternalInitFieldDefs" bufdatasettoolsunit.pas(124,50) Warning: Constructing a class "TPersistentBufDat aSet" with abstract method "LoadBlobIntoBuffer" Compiling memdstoolsunit.pas Compiling sdfdstoolsunit.pas Compiling tcsdfdata.pp Compiling testbasics.pas testbasics.pas(146,29) Warning: Constructing a class "TDataSet" with abstract me thod "GetRecord" testbasics.pas(146,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalClose" testbasics.pas(146,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalOpen" testbasics.pas(146,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalInitFieldDefs" testbasics.pas(146,29) Warning: Constructing a class "TDataSet" with abstract me thod "IsCursorOpen" testbasics.pas(188,29) Warning: Constructing a class "TDataSet" with abstract me thod "GetRecord" testbasics.pas(188,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalClose" testbasics.pas(188,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalOpen" testbasics.pas(188,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalInitFieldDefs" testbasics.pas(188,29) Warning: Constructing a class "TDataSet" with abstract me thod "IsCursorOpen" testbasics.pas(221,29) Warning: Constructing a class "TDataSet" with abstract me thod "GetRecord" testbasics.pas(221,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalClose" testbasics.pas(221,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalOpen" testbasics.pas(221,29) Warning: Constructing a class "TDataSet" with abstract me thod "InternalInitFieldDefs" testbasics.pas(221,29) Warning: Constructing a class "TDataSet" with abstract me thod "IsCursorOpen" Compiling testfieldtypes.pas testfieldtypes.pas(2114,14) Warning: Class types "TSQLQuery" and "HackedDataset"  are not related testfieldtypes.pas(2120,14) Warning: Class types "TSQLQuery" and "HackedDataset"  are not related testfieldtypes.pas(2126,14) Warning: Class types "TSQLQuery" and "HackedDataset"  are not related Compiling testdatasources.pas testdatasources.pas(97,5) Note: Local variable "i" not used testdatasources.pas(97,7) Note: Local variable "count" not used Compiling testdbbasics.pas testdbbasics.pas(226,5) Note: Local variable "b" not used testdbbasics.pas(314,5) Note: Local variable "i" not used testdbbasics.pas(314,7) Note: Local variable "count" not used testdbbasics.pas(399,5) Note: Local variable "i" not used testdbbasics.pas(399,7) Note: Local variable "count" not used testdbbasics.pas(458,5) Note: Local variable "i" is assigned but never used testdbbasics.pas(607,5) Note: Local variable "v" is assigned but never used testdbbasics.pas(1223,5) Note: Local variable "a" not used testdbbasics.pas(1290,5) Note: Local variable "tel" not used testdbbasics.pas(1366,5) Note: Local variable "s" is assigned but never used testdbbasics.pas(1385,42) Warning: Constructing a class "TCustomBufDataset" with  abstract method "InternalInitFieldDefs" testdbbasics.pas(1385,42) Warning: Constructing a class "TCustomBufDataset" with  abstract method "LoadBlobIntoBuffer" testdbbasics.pas(1396,5) Note: Local variable "LoadDs" not used testdbbasics.pas(1420,38) Warning: Constructing a class "TCustomBufDataset" with  abstract method "InternalInitFieldDefs" testdbbasics.pas(1420,38) Warning: Constructing a class "TCustomBufDataset" with  abstract method "LoadBlobIntoBuffer" testdbbasics.pas(1649,5) Note: Local variable "FList" not used testdbbasics.pas(1651,5) Note: Local variable "StrValue" not used testdbbasics.pas(1919,5) Note: Local variable "ds" not used testdbbasics.pas(1920,5) Note: Local variable "I" not used testdbbasics.pas(1927,5) Note: Local variable "I" not used testdbbasics.pas(2133,5) Note: Local variable "i" not used testdbbasics.pas(2134,5) Note: Local variable "OldID" not used testdbbasics.pas(2336,5) Note: Local variable "i" not used Compiling testbufdatasetstreams.pas testbufdatasetstreams.pas(160,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "InternalInitFieldDefs" testbufdatasetstreams.pas(160,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "LoadBlobIntoBuffer" testbufdatasetstreams.pas(415,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "InternalInitFieldDefs" testbufdatasetstreams.pas(415,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "LoadBlobIntoBuffer" testbufdatasetstreams.pas(469,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "InternalInitFieldDefs" testbufdatasetstreams.pas(469,42) Warning: Constructing a class "TCustomBufDatas et" with abstract method "LoadBlobIntoBuffer" testbufdatasetstreams.pas(522,44) Warning: Constructing a class "TCustomBufDatas et" with abstract method "InternalInitFieldDefs" testbufdatasetstreams.pas(522,44) Warning: Constructing a class "TCustomBufDatas et" with abstract method "LoadBlobIntoBuffer" Compiling testsqldb.pas Compiling testspecifictbufdataset.pas testspecifictbufdataset.pas(84,32) Warning: Constructing a class "TBufDataset" w ith abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(84,32) Warning: Constructing a class "TBufDataset" w ith abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(112,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(112,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(138,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(138,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(152,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(152,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(172,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(172,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(217,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(217,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" testspecifictbufdataset.pas(225,32) Warning: Constructing a class "TBufDataset" with abstract method "InternalInitFieldDefs" testspecifictbufdataset.pas(225,32) Warning: Constructing a class "TBufDataset" with abstract method "LoadBlobIntoBuffer" Compiling testspecifictdbf.pas Compiling testdbexport.pas Linking dbtestframework.exe 11029 lines compiled, 6.4 sec , 1194048 bytes code, 383388 bytes data 49 warning(s) issued 21 note(s) issued Running: C:\lazarus1-22\fpc\2.6.4\source\packages\fcl-db\tests>dbtestframework --format=p lain --skiptiming -all > Oracleoutput.txt Please  let me know if ok. Regards Dailton Menezes ---------------------------------------------------------------------- LacaK · 2014-06-12 06:14 it is not OK, as far as tests was not ran at all  It seems, that you have any problem in set up connection to server. Look please into database.ini to [oracle] section ... as Reinier wrote ... if there is all correct. ---------------------------------------------------------------------- dailton · 2014-06-12 23:06 Hi, Ok! Let's  try again. I don't know what's wrong. Here is my TNSNAMES.ORA entry from Oracle client: DBDES =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = dbdes)(PORT = 1521))     )     (CONNECT_DATA =       (SID = desenv)     )   ) Here is fragmented database.ini : [Database] ;type=bufdataset type=oracle . . . ; Oracle database: [oracle] connector=sql connectorparams=oracle name=desenv user=system password= hostname=dbdes What did I do wrong? Regards Dailton Menezes ---------------------------------------------------------------------- BigChimp · 2014-06-13 08:31 Perhaps you need to set name to empty (no string), leave hostname as dbdes (guessin that's the tnsnames.ora service name) See here for instructions on how to use tnsnames.ora with the connector: http://wiki.lazarus.freepascal.org/oracle Thread were we found out what to specify http://forum.lazarus.freepascal.org/index.php/topic,23905.msg144187.ht ml#msg144187 (Sorry, haven't touched tnsnames.ora for yours and I don't have fond memories of it) ---------------------------------------------------------------------- BigChimp · 2014-06-16 12:39 LacaK's patch was committed to FPC trunk. @Dailton: if you haven't gotten the test working, it's ok... ---------------------------------------------------------------------- dailton · 2014-06-17 12:33 Hi, Test completed! I changed name=dbdes (dbname not instant name). Attached is the file Oracleoutput.txt. Good luck. Regards. Dailton Menezes