Summary: I suspect the code just needs recompiling for a 64-bit architecture.
Detail: I have a Firebird 2.1 database that works perfectly under Windows, but has been problematic under a migration to Kubuntu 9.10 with ODBC driver libOdbcFb.so.
After initial problems I finally got ODBC working. But I have repeated problems with the date format (and quite possibly the integer format too). The database was created in a UK locale and Kubuntu is set to a UK locale.
I have a row containing a date field which I read once with the Perl application and I get '30/07/2009'. I read it again later and get, say, '32231-08-2\x00'. Sometimes the read works and sometimes not. Sometimes I get a valid date and sometimes I get a strange string, but always with the format nnnnn-nn-n\x00.
If I use isql-fb, it works perfectly, but if I use just isql (the ODBC product), it fails. So this leads me to believe it's an ODBC issue Here is an example, if I don't use any code but just use isql and I run the same query (select att_examination_dte from tbl_exam) three times, I get record 1 = 31893-11-15, twice and record 1 = 2009-07-24 (the correct value) the third time. There are 21 records in this table, here is the whole output (see below).
steve@steve-desktop:~$ isql end_db
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select att_examination_dte from tbl_exam
+----------------------------+
| ATT_EXAMINATION_DTE|
+----------------------------+
| 31893-11-15 |
| 31893-11-15 |
| 31893-11-15 |
| 31893-11-22 |
| 31893-12-05 |
| 31893-11-24 |
| 31893-11-18 |
| 31893-11-16 |
| 31893-11-23 |
| 31893-11-19 |
| 31893-11-21 |
| 31893-11-17 |
| 31893-12-11 |
| 31893-11-20 |
| 31893-12-11 |
| 31893-12-11 |
| 31893-12-11 |
| 31893-12-12 |
| 31893-12-16 |
| 31893-12-17 |
| 31894-02-05 |
+----------------------------+
SQLRowCount returns 21
21 rows fetched
SQL> select att_examination_dte from tbl_exam
+----------------------------+
| ATT_EXAMINATION_DTE|
+----------------------------+
| 31893-11-15 |
| 31893-11-15 |
| 31893-11-15 |
| 31893-11-22 |
| 31893-12-05 |
| 31893-11-24 |
| 31893-11-18 |
| 31893-11-16 |
| 31893-11-23 |
| 31893-11-19 |
| 31893-11-21 |
| 31893-11-17 |
| 31893-12-11 |
| 31893-11-20 |
| 31893-12-11 |
| 31893-12-11 |
| 31893-12-11 |
| 31893-12-12 |
| 31893-12-16 |
| 31893-12-17 |
| 31894-02-05 |
+----------------------------+
SQLRowCount returns 21
21 rows fetched
SQL> select att_examination_dte from tbl_exam
+----------------------------+
| ATT_EXAMINATION_DTE|
+----------------------------+
| 2009-07-24 |
| 2009-07-24 |
| 2009-07-24 |
| 2009-07-31 |
| 2009-08-13 |
| 2009-08-02 |
| 2009-07-27 |
| 2009-07-25 |
| 2009-08-01 |
| 2009-07-28 |
| 2009-07-30 |
| 2009-07-26 |
| 2009-08-19 |
| 2009-07-29 |
| 2009-08-19 |
| 2009-08-19 |
| 2009-08-19 |
| 2009-08-20 |
| 2009-08-24 |
| 2009-08-25 |
| 2009-10-14 |
+----------------------------+
SQLRowCount returns 21
21 rows fetched
SQL>
Bug
Closed
Major
64 bit ODBC driver does not work in Kubuntu
In the ODBC driver the 64-bit support is more or less broken. The main problem is the assumption that sizeof (long) == 4. The solution would be to use the types int32_t or uint32_t.
This particular issue is caused by the definition of LO_LONG in OdbcConvert.cpp:
#define LO_LONG(l) ((long)(l))
This assumes that casting a value to long will get rid of the upper 32 bits, which is not true with 64bit long. Possible solutions:
#define LO_LONG(l) ((int32_t)(l))
#define LO_LONG(l) ((long)((l) & 0xFFFFFFFF))
There are many more places in OdbcConvert.cpp where long should be replaced with int32_t, or even with int, as long as nobody tries to compile it on a 16 bit system.