Re: Bug with date conversion

Kevin I've found the answer to my problem. It's not actually a bug of
CLSQL but a possible improvement.

DATE field are stored in DB2 using an internal format but are rendered
using a string representation that depends on the configuration.

We are running the following environment:

1) OS/2 machine: running IBM DB2 V5.2 for OS/2 hosting the production
database.

2) Linux machine: running IBM DB2 V6.1 for Linux hosting a test
database,
CMUCL, CLSQL, and acting also as a client via unixODBC of the OS/2
machine.

It happens that the dbms running on the OS/2 machine is configured to
return date in the EUR format (dd/mm/yyyy) so every query
selecting a date column is returned using a string representation in
this format regardless the
access method (native / JDBC / ODBC / CLI).

On the contrary, on the Linux machine, the test database was configured
to
return date in ISO format (yyyy-mm-dd) and in fact CLSQL does not
complain.

Configuring the OS/2 server to return date in a given format is just a
matter of rebinding the ODBC/CLI package against the database with
the chosen format (see https://aurora.vcu.edu/db2help/db2s0/frame3.htm#dattyps
) , but doing so will break compatibility with our existing C++
applications that expect date to be in EUR format and not in ISO format.

So one possible solution could be to make CLSQL "smarter" than our C++
applications and able to handle date fields returned by DB2 using
different string representations.

I will follow your suggestion and look in db-odbc/odbc-api.lisp.

Kevin Rosenberg wrote:

Massimiliano Campagnoli wrote: (shortened)

Instead, query 1) using CLSQL gives out an error:
* (CLSQL:QUERY "SELECT DATALANCIO FROM LOTTI WHERE CODICELOTTO=8100")
Type-error in KERNEL::OBJECT-NOT-TYPE-ERROR-HANDLER:
98 is not of type (INTEGER 1 31)
[Condition of type TYPE-ERROR]

Interesting. It seems that DB2/ODBC driver returns dates differently
than Postgresql/ODBC, Oracle/ODBC, and MySQL/ODBC. It's also rather
unusual that field value submitted to the day of the month "(INTEGER 1
31)" is 98 rather than the 1998 value you have in your database. I
haven't seen any ODBC drivers return a 2-digit date field. Are you
using a ODBC v3 compatible driver with DB2? Are using unixODBC as your
ODBC driver manager or Microsoft Windows?

But query 2) that cast the date field to ISO format works ok:
[...]
(("1998-07-31"))

I think the issue here is that the CHAR operator converted the
result to string field so that CLSQL handled it fine, not that the
data was in an ISO format.

I don't have a DB2 installation available to work on adding DB2
compatibility to CLSQL. Would you have a server available that I can
connect use for working on adding DB2/ODBC compatibility?

Alternately, if you wish to work on adding such compatibility, I
believe you'll find the issues are confined to the
db-odbc/odbc-api.lisp file in CLSQL. As long as submitted patches
don't affect proper ODBC functioning for Postgresql, Mysql, and
Oracle, I'll be glad to incorporate them in CLSQL.

_______________________________________________
CLSQL-Devel mailing list
[email protected]
http://lists.b9.com/mailman/listinfo/clsql-devel