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

[bill l. <cbi...@gm...>]

On Tue, 20 Jan 2009, Hauser, Karsten wrote:
> I've tried to create the same scenario with a connection from oracle to mysql. By applying the statement:
> ------------
> DECLARE
>     l_nNR NUMBER := 1;
>     l_strPuffer VARCHAR2(2000);
> begin
>     SELECT "first_name" INTO l_strPuffer
> 	FROM EMPLOYEE@myodbc
> 	WHERE "employee_id" = l_nNR;
> end; 
> ----------
> which forces the ODBC-Driver (or Oracle.. I don't know) to use bindings I got the following trace-part:
> ----------
> dg4odbcMYODBC ( 12ac-f20	EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
> 		HSTMT               01B51960
> 		UCHAR *             0x01E169F4 [      66] "SELECT A1.`first_name` FROM `EMPLOYEE` A1 WHERE A1.`employee_id`=?"
> 		SDWORD                    66
> 
> dg4odbcMYODBC ( 12ac-f20	ENTER SQLNumResultCols 
> 		HSTMT               01B51960
> 		SWORD *             0x01E002E8
> 
> dg4odbcMYODBC ( 12ac-f20	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
> 		HSTMT               01B51960
> 		SWORD *             0x01E002E8 (1)
> 
> dg4odbcMYODBC ( 12ac-f20	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                0x01E16830
> 		SQLLEN                    65
> 		SQLLEN *            0x01E0F2E8
> ----------
> 
> You see, that oracle here also detects the col-type SQL_C_CHAR, whereas the original type of the column is numeric. The statement delivers the correct solution..
> 

IIRC odbc itself can not deduce data type for input
parameter. All calls to odbc api as what you got

> dg4odbcMYODBC ( 12ac-f20	EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
> dg4odbcMYODBC ( 12ac-f20	ENTER SQLNumResultCols 
> dg4odbcMYODBC ( 12ac-f20	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
> dg4odbcMYODBC ( 12ac-f20	ENTER SQLBindParameter 

were issued by another process such as your orcale databench ide or
application program. So it depends on the cleverness of the calling
party and not odbc to give the correct binding.  In the above trace,
you can see that the calling party actually never query the
characteristics of parameters, but instead issue the sqlbindparameter
directly.  I guess the calling program had parsed the sql statement
itself or otherwise, therefore knowing that it needed conversion.
I regard it as an issue in oracle (but I've never used orcale).

-- 
regards,
====================================================
GPG key 1024D/4434BAB3 2008-08-24
gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3
唐詩308 韋莊  金陵圖
    江雨霏霏江草齊  六朝如夢鳥空啼  無情最是臺城柳  依舊煙籠十里堤