Re: [Firebird-odbc-devel] selecting from Oracle 11g to firebird

[Alexander P. <ale...@re...>]

Hauser, Karsten wrote:
> Here's the one without join:
>
> dg4odbcFORMICA  f08-d50	ENTER SQLPrepare 
> 		HSTMT               01B51960
> 		UCHAR *             0x01E9C698 [      40] "SELECT "NR" FROM "PERSONAL" WHERE ?="NR""
> 		SDWORD                    40
>
> dg4odbcFORMICA  f08-d50	EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
> 		HSTMT               01B51960
> 		UCHAR *             0x01E9C698 [      40] "SELECT "NR" FROM "PERSONAL" WHERE ?="NR""
> 		SDWORD                    40
>
> dg4odbcFORMICA  f08-d50	ENTER SQLBindParameter 
> 		HSTMT               01B51960
> 		UWORD                        1 
> 		SWORD                        1 <SQL_PARAM_INPUT>
> 		SWORD                        1 <SQL_C_CHAR>
> 		SWORD                        3 <SQL_DECIMAL>
> 		SQLULEN                    1
> 		SWORD                        0 
> 		PTR                0x01EAE5F4
> 		SQLLEN                    65
> 		SQLLEN *            0x01E1D5E4
> dg4odbcFORMICA  f08-d50	ENTER SQLExecute 
> 		HSTMT               01B51960
>
> dg4odbcFORMICA  f08-d50	EXIT  SQLExecute  with return code -1 (SQL_ERROR)
> 		HSTMT               01B51960
>
> 		DIAG [01004] [ODBC Firebird Driver]Data truncated (0) 
>
> 		DIAG [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
> SQL error code = -303
> conversion error from string "           " (-303)
> ----------------------------
>   
I have considered this problem.
SQLBindParameter is called with wrong parameters, if NR is numeric then 
fourth parameter must be SQL_INTEGER but not SQL_C_CHAR. (Oracle bug?)
In this case if ? (the parameter) is not equal number (" " in your case) 
then Firebird engine can not execute this query without specific 
conversion to char type (TRIM converts to char type).
I can recommend to use the following:
SELECT "NR" FROM "PERSONAL" WHERE ?= cast("NR" as varchar(10)).

Regards
Alexander
-- 
Alexander Potapchenko
Senior developer