Apache OpenOffice (AOO) Bugzilla – Issue 94543
Error on importing a timestamp field from AS-400 via ODBC
Last modified: 2013-01-29 21:47:11 UTC
Strange conversion on a time-stamp field imported from an AS/400 Database via ODBC. The same wrong conversion is in Calc and in Base. Other program that use the same ODBC (pe Excel) perform the correct conversion. I attached a file in calc with an example
Created attachment 56959 [details] Example error on importing a timestamp field from AS-400 via ODBC
Created attachment 56962 [details] Basic timestamp value for better understand the problem
Created attachment 62266 [details] I found that the difference is always a multiple of a constant value and this multiple is between 0 and 63
Please look this issue. Now I added a new information Thanks Best Regard
Not P1 (we would have heard some more noise here ...) @danros66: Please - do not modify version, where problem has been observed first time - read our guidelines on <http://qa.openoffice.org/issue_handling/pre_submission.html> and <http://www.openoffice.org/bugs/bug_writing_guidelines.html>, then contribute a clear step by step instruction containing all observations (error messages ...), _every_key_press_and_every_mouse_click_ how to reproduce the problem, and explain why you believe that your results are unexpected.
I see no chance to be able ro reproduce this problem. As far as I know there are no further people working with this combination AS/400 Database via ODBC and OpenOffice.org. So we need your special knowledge to analyse and to solve the problem.
What do you need? How can I help you?
a virtual machine, set up to show the problem, would *tremendously* help :)
A virtual machine is impossible for me I tried to put on internet an old AS400 but its raid-card battery is dead and AS400 doesn't work. For security consideration is impossible to put our production machine on internet.... So, I could extract through calc and excel specific data that you suggest me.... or I could test a special version of OOo... I know it's a small thing but I haven't other idea at the moment
Created attachment 64741 [details] I discovered that the same problem in finding the data from DB2 on the PC. I enclose an example of the differences between Calc and Excel. This can simplify your test
Created attachment 64742 [details] I discovered that the same problem in finding the data from DB2 on the PC. I enclose an example of the differences between Calc and Excel. This can simplify your test
Conclusion: I recommend that this issue be confirmed. I have managed to create situations very much like what I have noticed in danros66's attachments. This happens with OS/400 V5R2 using database and spreadsheet interfaces of a lightly hacked non-production local build of DEV300_m75. As the results are consistent across OOo interfaces, I am not providing quite the step-by-step instructions that rainerbielefeld asked for above. Database setup on AS/400 ------------------------ I defined my test file with an SQL statement in member QALLSRC(AA6411) create table aa6411 ( keyfield int not null primary key, ts timestamp not null, comment char(30) ); invoked with CL statement RUNSQLSTM SRCFILE(QALLSRC) SRCMBR(AA6411) COMMIT(*NONE) I populated the thest file with SQL statments in member QALLSRC(AA6412) delete from aa6411; insert into aa6411 values( 1, '1990-03-02-08.30.00.100000', 'copied from V5R2 manual' ); insert into aa6411 values( 2, '2008-10-01-10.41.54.090000', 'danros66 got time 10:42' ); insert into aa6411 values( 3, '2008-10-01-10.41.52.305824', 'danros66 got time 10:51' ); insert into aa6411 values( 4, '2008-10-01-10.41.54.306208', 'danros66 got time 10:44' ); invoked with CL statment RUNSQLSTM SRCFILE(QALLSRC) SRCMBR(AA6412) COMMIT(*NONE) Using isql, part of DB2 on Linux, I retrieved the table, showing the expected values SQL> select * from aa6411 +------------+---------------------------+-------------------------------+ | KEYFIELD | TS | COMMENT | +------------+---------------------------+-------------------------------+ | 1 | 1990-03-02 08:30:00.100000| copied from V5R2 manual | | 2 | 2008-10-01 10:41:54.090000| danros66 got time 10:42 | | 3 | 2008-10-01 10:41:52.305824| danros66 got time 10:51 | | 4 | 2008-10-01 10:41:54.306208| danros66 got time 10:44 | +------------+---------------------------+-------------------------------+ SQLRowCount returns -1 4 rows fetched I have a prexisting ODBC connection defined to the AS/400. Observations in OpenOffice -------------------------- I created an OOo database using a previously defined ODBC connection. In OOo Base SQL View, I created query "simplest" from SQL statement select * from AA6411 which returns, give-or-take my typing mistakes, the incorrect values that danros66 reported ... KEYFIELD TS COMMENT -------- -------------- ------------------------------ 1 02/03/90 08:39 copied from V5R2 manual 2 01/10/08 10:42 danros66 got time 10:42 3 01/10/08 10:51 danras66 got time 10:51 4 01/10/08 10:44 danros66 got time 10:44 In Calc, I opened a new workbook, pressed F4, drilled down, and dragged the query into the worksheet, getting the same incorrect result. I shall attach the resulting workbook. My local hacks to OpenOffice ---------------------------- (*) I have removed "typedef int BOOL;" from iodbcunix.h as it is in my Ubunty Hardy installation and copied the result to connectivity/inc/iodbcunix.h and dbaccess/inc/iodbcunix.h (*) svl/source/misc/inettype.cxx incorporates sb's patch for issue 109146 and the unreferenced routine tje_20100316() quoted in <http://www.openoffice.org/servlets/ReadMsg?list=dev&msgNo=26782>. (*) From sw/source/filter/asc/parasc.cxx I have deleted lines 259 and 260, to avoid the assertion described in issue 109179.
Created attachment 68434 [details] similar results from OS/400 V5R2 and DEV300_m75
Attachments coming ... (*) A patch for consideration (*) Package of test data and results - AA6411.MBR: DDL for table and a view. View AA6411V1 derives date and time from the timestamp and char() of the timestamp, date, and time. I think that the char() columns represent the right answers, albeit sometimes to more precision than OOo displays. The char() columns deliver good results from V5R3 of target system but not from V5R2. I have received one opinion <http://dba.openoffice.org/servlets/ReadMsg?list=dev&msgNo=4334> that we do not care about V5R2. - AA6412.MBR: DML creating some test data. Nineteen rows. - use_OTools_DateTimeToTimestamp.ods: Result of importing AA6411V1 into Calc. I manually increased the precision of column TS in the spreadsheet.
Created attachment 68563 [details] patch for review
Created attachment 68564 [details] test data and results