Menu
▾
▴
firebird-odbc-devel
|
From: Hauser, K. <Kar...@ib...> - 2009-01-13 13:49:32
|
Hi, I have the following Problem with the combination of Firebird 1.5.x or 2.1, Oracle 11g and Firebird ODBC 2.00.00.148 on a winXP system: the command: SELECT pe.NR, us.USER_LOGIN FROM IBKPVS_SYS_USER us LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR where us.USER_LOGIN = 'ADMIN' ends up in this error msg: ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht zurück: [ODBC Firebird Driver]Data truncated[ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code = -303 conversion error from string " " ORA-02063: vorherige 4 lines von FORMICA Other, simple statement are working like: SELECT pe.NR FROM PERSONAL@formica pe where pe.VORNAME = 'Nicole' The Problem appears when joining one table of Firebird with one of Oracle and restricting the result set. I've created a thread in the oracle forum (http://forums.oracle.com/forums/thread.jspa?threadID=845777). Here you can find the trace-information of oracle and windows. How can I solve this problem? Is there a tracing capability in Firebird? I've tried to log the failure with Dr. Watson and the debug-Version of Firebird as written in an Post on formicasql.org, but I didn't get it working. Thank you for helping Karsten IBYKUS AG für Informationstechnologie Herman-Hollerith-Str. 1 99099 Erfurt Fon +49 (361) 4410-310 Fax +49 (361) 4410-410 E-Mail mailto:kar...@ib... <blocked::mailto:kar...@ib...> Internet http://www.ibykus.com <blocked::http://www.ibykus.com/> ------------------------------------------------------------------------------------------------------------ IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter / Vorsitzender des Aufsichtsrates: Dr. Frieder Schäuble |
|
From: Alexander P. <ale...@re...> - 2009-01-13 14:53:17
|
Hauser, Karsten wrote: > > Hi, > > I have the following Problem with the combination of Firebird 1.5.x or > 2.1, Oracle 11g and Firebird ODBC 2.00.00.148 on a winXP system: > > the command: > SELECT pe.NR, us.USER_LOGIN > FROM IBKPVS_SYS_USER us > LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR > where us.USER_LOGIN = 'ADMIN' > > ends up in this error msg: > > ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht > zurück: > [ODBC Firebird Driver]Data truncated[ODBC Firebird > Driver][Firebird]Dynamic SQL Error > SQL error code = -303 > conversion error from string " " > ORA-02063: vorherige 4 lines von FORMICA > > Other, simple statement are working like: > > SELECT pe.NR FROM PERSONAL@formica pe where pe.VORNAME = 'Nicole' > > The Problem appears when joining one table of Firebird with one of > Oracle and restricting the result set. I’ve created a thread in the > oracle forum > (http://forums.oracle.com/forums/thread.jspa?threadID=845777). Here > you can find the trace-information of oracle and windows. > > How can I solve this problem? Is there a tracing capability in > Firebird? I’ve tried to log the failure with Dr. Watson and the > debug-Version of Firebird as written in an Post on formicasql.org, but > I didn’t get it working. > > Hi, This is like a bug in the Firebird ODBC driver, but I am not sure completely. Please add it into the bug tracker here: http://tracker.firebirdsql.org/browse/ODBC Next we are resuming an analysis of this problem there soon. Now I need only a part of a trace file where is an query (SQLPrepare) which are generating the error in the SQLExecute after that. Regards, Alexander -- Alexander Potapchenko Senior developer |
|
From: Hauser, K. <Kar...@ib...> - 2009-01-13 15:34:04
|
I've created the issue. I don't know how to trace the execution of the statement in Firebird. Maybe you can give me an manual for doing this? I have only one further statement which works: SELECT pe.NR, us.USER_LOGIN FROM IBKPVS_SYS_USER us LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR where TRIM(us.USER_LOGIN) = 'ADMIN' But inserting a trim should not be the solution.. -----Ursprüngliche Nachricht----- Von: Alexander Potapchenko [mailto:ale...@re...] Gesendet: Dienstag, 13. Januar 2009 15:53 An: fir...@li... Betreff: Re: [Firebird-odbc-devel] selecting from Oracle 11g to firebird Hauser, Karsten wrote: > > Hi, > > I have the following Problem with the combination of Firebird 1.5.x or > 2.1, Oracle 11g and Firebird ODBC 2.00.00.148 on a winXP system: > > the command: > SELECT pe.NR, us.USER_LOGIN > FROM IBKPVS_SYS_USER us > LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR > where us.USER_LOGIN = 'ADMIN' > > ends up in this error msg: > > ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht > zurück: > [ODBC Firebird Driver]Data truncated[ODBC Firebird > Driver][Firebird]Dynamic SQL Error > SQL error code = -303 > conversion error from string " " > ORA-02063: vorherige 4 lines von FORMICA > > Other, simple statement are working like: > > SELECT pe.NR FROM PERSONAL@formica pe where pe.VORNAME = 'Nicole' > > The Problem appears when joining one table of Firebird with one of > Oracle and restricting the result set. I've created a thread in the > oracle forum > (http://forums.oracle.com/forums/thread.jspa?threadID=845777). Here > you can find the trace-information of oracle and windows. > > How can I solve this problem? Is there a tracing capability in > Firebird? I've tried to log the failure with Dr. Watson and the > debug-Version of Firebird as written in an Post on formicasql.org, but > I didn't get it working. > > Hi, This is like a bug in the Firebird ODBC driver, but I am not sure completely. Please add it into the bug tracker here: http://tracker.firebirdsql.org/browse/ODBC Next we are resuming an analysis of this problem there soon. Now I need only a part of a trace file where is an query (SQLPrepare) which are generating the error in the SQLExecute after that. Regards, Alexander -- Alexander Potapchenko Senior developer ------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ Firebird-odbc-devel mailing list Fir...@li... https://lists.sourceforge.net/lists/listinfo/firebird-odbc-devel ------------------------------------------------------------------------------------------------------------ IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter / Vorsitzender des Aufsichtsrates: Dr. Frieder Schäuble |
|
From: Alexander P. <ale...@re...> - 2009-01-13 15:54:24
|
Hauser, Karsten wrote: > I've created the issue. I don't know how to trace the execution of the statement in Firebird. Maybe you can give me an manual for doing this? > > I have only one further statement which works: > SELECT pe.NR, us.USER_LOGIN > FROM IBKPVS_SYS_USER us > LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR > where TRIM(us.USER_LOGIN) = 'ADMIN' > > But inserting a trim should not be the solution.. > > No, I have said about ODBC trace file. I want to look at the first query (without TRIM in the SQLPrepare) and the second query with TRIM at ODBC level. Regards, Alexander -- Alexander Potapchenko Senior developer |
|
From: Hauser, K. <Kar...@ib...> - 2009-01-14 08:06:50
|
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 SQLNumResultCols HSTMT 01B51960 SWORD * 0x01E11638 dg4odbcFORMICA f08-d50 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS) HSTMT 01B51960 SWORD * 0x01E11638 (1) 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 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) 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 (65) 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) ---------------------------- And here with trim: dg4odbcFORMICA e44-194 ENTER SQLPrepare HSTMT 01B51960 UCHAR * 0x01E9C6B4 [ 27] "SELECT "NR" FROM "PERSONAL"" SDWORD 27 dg4odbcFORMICA e44-194 EXIT SQLPrepare with return code 0 (SQL_SUCCESS) HSTMT 01B51960 UCHAR * 0x01E9C6B4 [ 27] "SELECT "NR" FROM "PERSONAL"" SDWORD 27 dg4odbcFORMICA e44-194 ENTER SQLNumResultCols HSTMT 01B51960 SWORD * 0x01E11638 dg4odbcFORMICA e44-194 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS) HSTMT 01B51960 SWORD * 0x01E11638 (1) dg4odbcFORMICA e44-194 ENTER SQLExecute HSTMT 01B51960 dg4odbcFORMICA e44-194 EXIT SQLExecute with return code 0 (SQL_SUCCESS) HSTMT 01B51960 So it seems that the problem is related to the binding during execution. -----Ursprüngliche Nachricht----- Von: Alexander Potapchenko [mailto:ale...@re...] Gesendet: Dienstag, 13. Januar 2009 16:54 An: fir...@li... Betreff: Re: [Firebird-odbc-devel] selecting from Oracle 11g to firebird Hauser, Karsten wrote: > I've created the issue. I don't know how to trace the execution of the statement in Firebird. Maybe you can give me an manual for doing this? > > I have only one further statement which works: > SELECT pe.NR, us.USER_LOGIN > FROM IBKPVS_SYS_USER us > LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR > where TRIM(us.USER_LOGIN) = 'ADMIN' > > But inserting a trim should not be the solution.. > > No, I have said about ODBC trace file. I want to look at the first query (without TRIM in the SQLPrepare) and the second query with TRIM at ODBC level. Regards, Alexander -- Alexander Potapchenko Senior developer ------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ Firebird-odbc-devel mailing list Fir...@li... https://lists.sourceforge.net/lists/listinfo/firebird-odbc-devel ------------------------------------------------------------------------------------------------------------ IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter / Vorsitzender des Aufsichtsrates: Dr. Frieder Schäuble |
|
From: Alexander P. <ale...@re...> - 2009-01-14 21:23:26
|
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
|
|
From: Nikolay S. <nik...@re...> - 2009-01-14 22:29:25
|
Alexander,
Oracle is not quite easy when it comes to numeric data types. Native
numeric in Oracle has 38 decimal digits, and as such can not always be
represented as SQL_INTEGER.
Can you take an Oracle instance, and see what is going on when using
Firebird ODBC driver in the way described? This way we may let the users
from bank's IT department use our Firebird data warehousing databases
from their Oracle environment.
Alexander Potapchenko wrote:
> Hauser, Karsten wrote:
>
>> Here's the one without join:
>> 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
>
--
Nikolay Samofatov, MBA
Red Soft International
+1 416 710 6854
|
|
From: Alexander P. <ale...@re...> - 2009-01-15 00:11:45
|
Nikolay Samofatov wrote: > Alexander, > > Oracle is not quite easy when it comes to numeric data types. Native > numeric in Oracle has 38 decimal digits, and as such can not always be > represented as SQL_INTEGER. > Can you take an Oracle instance, and see what is going on when using > Firebird ODBC driver in the way described? This way we may let the users > from bank's IT department use our Firebird data warehousing databases > from their Oracle environment. > Yes, SQL_DOUBLE is a correctly data type in a general case for numeric. The main question is how Oracle detects parameters type for ODBC functions such as SQLBindParameter, SQLBindCol... I think it must call SQLColAttribute for this. I did not try to use Firebird ODBC driver in Oracle as yet. Alexander -- Alexander Potapchenko Senior developer |
|
From: Frank Schlottmann-G. <fs...@us...> - 2009-01-13 15:05:12
|
Hauser, Karsten wrote:
>
>
> Hi,
>
>
>
> I have the following Problem with the combination of Firebird 1.5.x or
> 2.1, Oracle 11g and Firebird ODBC 2.00.00.148 on a winXP system:
>
>
>
> the command:
> SELECT pe.NR, us.USER_LOGIN
> FROM IBKPVS_SYS_USER us
> LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR
> where us.USER_LOGIN = 'ADMIN'
> ends up in this error msg:
> conversion error from string " "
Seems that either IBKPVS_SYS_USER.USER_PERS_ID_FK is declared as numeric
type and PER...@fo... is declared as char typ and contains ' '
(or null ?) or vice versa.
What does
Select SELECT us.USER_PERS_ID_FK,us.USER_LOGIN
FROM IBKPVS_SYS_USER us
where us.USER_LOGIN = 'ADMIN'
return?
mit freundlichen Grüßen
Frank Schlottmann-Gödde
--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling
|
|
From: Hauser, K. <Kar...@ib...> - 2009-01-13 15:18:35
|
Both fields are numeric.. Your statement delivers the result set (1->Number, ADMIN->String) and is the base for following statement 'SELECT "NR" FROM "PERSONAL" WHERE ?="NR"' send to firebird while during excecution the sign '?' should be replace by the number 1 (from result set). There somewhere is the failure.
-----Ursprüngliche Nachricht-----
Von: Frank Schlottmann-Gödde [mailto:fs...@us...]
Gesendet: Dienstag, 13. Januar 2009 16:00
An: fir...@li...
Betreff: Re: [Firebird-odbc-devel] selecting from Oracle 11g to firebird
Hauser, Karsten wrote:
>
>
> Hi,
>
>
>
> I have the following Problem with the combination of Firebird 1.5.x or
> 2.1, Oracle 11g and Firebird ODBC 2.00.00.148 on a winXP system:
>
>
>
> the command:
> SELECT pe.NR, us.USER_LOGIN
> FROM IBKPVS_SYS_USER us
> LEFT JOIN PERSONAL@formica pe ON us.USER_PERS_ID_FK = pe.NR
> where us.USER_LOGIN = 'ADMIN'
> ends up in this error msg:
> conversion error from string " "
Seems that either IBKPVS_SYS_USER.USER_PERS_ID_FK is declared as numeric
type and PER...@fo... is declared as char typ and contains ' '
(or null ?) or vice versa.
What does
Select SELECT us.USER_PERS_ID_FK,us.USER_LOGIN
FROM IBKPVS_SYS_USER us
where us.USER_LOGIN = 'ADMIN'
return?
mit freundlichen Grüßen
Frank Schlottmann-Gödde
--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Firebird-odbc-devel mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-odbc-devel
------------------------------------------------------------------------------------------------------------
IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter /
Vorsitzender des Aufsichtsrates: Dr. Frieder Schäuble
|
|
From: Hauser, K. <Kar...@ib...> - 2009-01-20 17:15:10
|
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..
I've tried several things with the sources of the ODBC Driver (static replacing SQL_C_CHAR with SQL_C_DOUBLE as a simple test-> not working.. firebird says: no data found). Is there any documentation over the code of the ODBC Driver? I would know where Oracle jumps into the code..
SQLColAttribute? I found that other functions like this are traced, so I think that Oracle use another function.
-----Ursprüngliche Nachricht-----
Von: Alexander Potapchenko [mailto:ale...@re...]
Gesendet: Donnerstag, 15. Januar 2009 01:12
An: fir...@li...
Betreff: Re: [Firebird-odbc-devel] selecting from Oracle 11g to firebird
Nikolay Samofatov wrote:
> Alexander,
>
> Oracle is not quite easy when it comes to numeric data types. Native
> numeric in Oracle has 38 decimal digits, and as such can not always be
> represented as SQL_INTEGER.
> Can you take an Oracle instance, and see what is going on when using
> Firebird ODBC driver in the way described? This way we may let the users
> from bank's IT department use our Firebird data warehousing databases
> from their Oracle environment.
>
Yes, SQL_DOUBLE is a correctly data type in a general case for numeric.
The main question is how Oracle detects parameters type for ODBC
functions such as SQLBindParameter, SQLBindCol...
I think it must call SQLColAttribute for this. I did not try to use
Firebird ODBC driver in Oracle as yet.
Alexander
--
Alexander Potapchenko
Senior developer
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Firebird-odbc-devel mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-odbc-devel
------------------------------------------------------------------------------------------------------------
IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter /
Vorsitzender des Aufsichtsrates: Dr. Frieder Schäuble
|
|
From: Alexander P. <ale...@re...> - 2009-01-20 19:21:29
|
Hauser, Karsten wrote: > I've tried several things with the sources of the ODBC Driver (static replacing SQL_C_CHAR with SQL_C_DOUBLE as a simple test-> not working.. firebird says: no data found). Is there any documentation over the code of the ODBC Driver? MSDN - http://msdn.microsoft.com/en-us/library/ms714177.aspx > I would know where Oracle jumps into the code.. > SQLColAttribute? I found that other functions like this are traced, so I think that Oracle use another function. > SQLDescribeParam/SQLDescribeCol probably. Your error is happened on the Firebird server level (in conversion), but SQL_C_CHAR instead of SQL_C_DOUBLE in SQLBindParameter leads to such results. I have seen MySql, the query: select id from table where id = 'empty string or text' is executed without errors, but it is impossible in Firebird without CAST. Alexander -- Alexander Potapchenko http://www.red-soft.biz Senior developer |
|
From: bill l. <cbi...@gm...> - 2009-01-21 05:57:40
|
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 韋莊 金陵圖 江雨霏霏江草齊 六朝如夢鳥空啼 無情最是臺城柳 依舊煙籠十里堤 |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X