Issue 94543

Summary: Error on importing a timestamp field from AS-400 via ODBC
Product: Base Reporter: danros66 <rossetti.danilo>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, mechtilde, rainerbielefeld_ooo_qa, tenger
Version: OOo 3.0 RC1Keywords: oooqa
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Example error on importing a timestamp field from AS-400 via ODBC
none
Basic timestamp value for better understand the problem
none
I found that the difference is always a multiple of a constant value and this multiple is between 0 and 63
none
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
none
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
none
similar results from OS/400 V5R2 and DEV300_m75
none
patch for review
none
test data and results none

Description danros66 2008-10-01 16:51:50 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
Comment 1 danros66 2008-10-01 16:53:13 UTC
Created attachment 56959 [details]
Example error on importing a timestamp field from AS-400 via ODBC
Comment 2 danros66 2008-10-01 20:45:05 UTC
Created attachment 56962 [details]
Basic timestamp value for better understand the problem
Comment 3 danros66 2009-05-14 14:30:56 UTC
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
Comment 4 danros66 2009-05-14 14:34:37 UTC
Please look this issue.
Now I added a new information

Thanks
Best Regard
Comment 5 Rainer Bielefeld 2009-05-22 17:24:54 UTC
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.
Comment 6 Mechtilde 2009-08-21 09:09:27 UTC
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.



Comment 7 danros66 2009-08-24 09:45:15 UTC
What do you need?
How can I help you?
Comment 8 Frank Schönheit 2009-08-24 10:17:18 UTC
a virtual machine, set up to show the problem, would *tremendously* help :)
Comment 9 danros66 2009-08-24 17:29:13 UTC
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
Comment 10 danros66 2009-09-13 18:15:32 UTC
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
Comment 11 danros66 2009-09-13 18:16:41 UTC
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
Comment 12 400guy 2010-03-19 14:44:43 UTC
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.

Comment 13 400guy 2010-03-19 14:47:25 UTC
Created attachment 68434 [details]
similar results from OS/400 V5R2 and DEV300_m75
Comment 14 400guy 2010-03-26 17:36:48 UTC
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.
Comment 15 400guy 2010-03-26 17:39:35 UTC
Created attachment 68563 [details]
patch for review
Comment 16 400guy 2010-03-26 17:42:03 UTC
Created attachment 68564 [details]
test data and results