Issue 49043

Summary: JDBC returns DATE as TEXT[varchar] on IBM UniVerse
Product: Base Reporter: ianst <ian>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 2.0 Beta   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description ianst 2005-05-10 22:15:21 UTC
On OpenOffice.org 1.9.100 Windows 2000 connecting to IBM UniVerse database via JDBC.

When creating a table with a date column OOo creates the date column correctly.
 If the table is accessed immediately after creating the database table,  the
date column correctly displays as a date and the definition of the field also
correctly shows as DATE.

However, on reconnecting to the database the date column is picked up as
TEXT[VARCHAR] when connecting using the JDBC.

Using MS Office and ODBC to connect to the database,  the column appears as a
date and the database manager also recognises the column as date when running
SQL queries directly against the database without JDBC or ODBC.

Any suggestions ?

Tks
Ian Stuart
Comment 1 ianst 2005-05-25 18:41:00 UTC
Have installed 1.9.104 and tested date columns with UniVerse JDBC in query tool.
Although date columns are still incorrectly shown as text[varchar] when editing 
table definitions, a query can be created (with limitations) that does display 
date columns correctly and I wonder if this might shed some light on why 
OOo "loses" the date definition once a table is created and one reconnects to 
the database as previously reported.

For example:

If a query is created in design view, date columns are displayed as an integer 
which does not appear to have a relationship to the actual date.

EG.
SELECT  "NAME1", "DATE.OPENED" FROM "CM" ORDER BY "DATE.OPENED" ASC
NAME1   |DATE.OPENED|
SHOPRITE|25567      |

On switching Design Mode off and running the SQL "directly" the date columns 
display as the correct date eg.

SHOPRITE|01/01/70

However by switching to "Run SQL command directly" mode one cannot then use "?" 
to parameterise the criteria for selection.

So although the table definition seems to remain incorrectly interpreted as 
text for date columns, the query tool can interpret date columns correctly.

Could the fact that running a query in "direct" mode correctly handles dates, 
albeit without the ability to prompt for criteria, throw some light on the 
reason for OOo and UniVerse not understanding/interpreting  certain data types.
Comment 2 ianst 2005-12-08 11:02:11 UTC
Datatype and formatting of date and non-date number columns in queries also
appears to be a related problem to the original description of this issue,
depending on how a query is submitted.

For example, while in design mode, date columns are returned as number formatted
values but numeric,2 columns correctly return data as 9999.99.

However, if a query is submitted directly through SQL, date columns are shown
correctly as date format, but numeric,2 columns are displayed with 16 decimal
places.

By changing the format of date columns when in design mode (right-click, Column
Format in query with data displayed in the grid) dates are then correctly
displayed, but on saving and re-executing the query the date columns revert to
number.

 
Comment 3 wendi 2005-12-12 09:20:36 UTC
*** Issue 49043 has been confirmed by votes. ***
Comment 4 ianst 2006-06-15 12:23:19 UTC
Have loaded OOo_2.0.3RC4 on Windows testing against JDBC datasource on UniVerse
database on SUSE 9.2.  

Date column format behaves differently depending on whether the "Use Schema Name
in  Select Statements" is checked or not in Advanced Properties of the datasource.

If checked then date columns are returned correctly in the query builder and
into spreadsheets using the data pilot.

If not checked the date is returned as an integer.

As reported before however, formatting the column to date format results in the
correct date being displayed.

Regards
Ian Stuart
Comment 5 ubanmidna 2010-11-10 23:50:10 UTC
Created attachment 73872