FirebirdSQL/firebird-odbc-driver

SQLGetTypeInfo() bug [ODBC210]

firebird-automations opened this issue · 3 comments

Submitted by: Greg Wittmeyer (gammad)

SQLGetTypeInfo() returns incorrect information for date, time, and timestamp columns. The incorrect information is in columns 4 and 5, the literal prefix and literal suffix. For example, it says for dates, the literal prefix is "{d'" and the literal suffix is "'}". In fact, both should be a single tick mark. If a program obeyed this information, it would produce a date literal like this:

UPDATE foo SET my_date={d'2018-08-28'} WHERE tid=1

which results in a syntax error. The correct syntax is:

UPDATE foo SET my_date='2018-08-28' WHERE tid=1

Commented by: @alexpotapchenko

If SQL_ATTR_NOSCAN = SQL_NOSCAN_OFF then UPDATE foo SET my_date={d'2018-08-28'} WHERE tid=1is the correct syntax.

Example for Employee.fdb with ODBC Log:

IBExpert 184-99c ENTER SQLPrepare
HSTMT 0x02A13470
UCHAR * 0x042ADD68 [ -3] "UPDATE SALES SET date_needed ={d'2019-08-28'} WHERE PO_NUMBER = 'V91E0210'\ d\ a\ 0"
SDWORD -3

IBExpert 184-99c EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 0x02A13470
UCHAR * 0x042ADD68 [ -3] "UPDATE SALES SET date_needed ={d'2019-08-28'} WHERE PO_NUMBER = 'V91E0210'\ d\ a\ 0"
SDWORD -3

Modified by: @alexpotapchenko

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

Fix Version: 2.0.6 [ 10851 ]

Commented by: Greg Wittmeyer (gammad)

{d'2018-08-28'} works with SQL_NOSCAN_OFF because that is the escape format, and the ODBC driver translates it. SQLGetTypeInfo() should advertise what firebird expects natively, not the escape format.