Apache OpenOffice (AOO) Bugzilla – Issue 49043
JDBC returns DATE as TEXT[varchar] on IBM UniVerse
Last modified: 2013-02-07 21:58:30 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
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.
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.
*** Issue 49043 has been confirmed by votes. ***
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
Created attachment 73872