Issue 49043

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