[unixODBC-support] Oracle -- Informix boolean error

Rébeli-Szabó Tamás tamas.rebeli.szabo at webvalto.hu
Tue Apr 14 17:56:59 BST 2015

Hello again Michael,

thanks for your tip.

However cumbersome it is, it actually works with a passthrough query and 
a cast.

I am pasting it here for the record:

SQL> set serveroutput on
SQL> r
   2    val  VARCHAR2(1);
   3    c    INTEGER;
   4    nr   INTEGER;
   5  BEGIN
   6    c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR at informix;
   7    DBMS_HS_PASSTHROUGH.PARSE at informix(c,'select cast (stat as 
character) from bt');
   8    LOOP
   9     nr := DBMS_HS_PASSTHROUGH.FETCH_ROW at informix(c);
  10     EXIT WHEN nr = 0;
  11     DBMS_HS_PASSTHROUGH.GET_VALUE at informix(c, 1, val);
  12     DBMS_OUTPUT.PUT_LINE(val);
  13    END LOOP;
  14    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR at informix(c);
  15  END;

PL/SQL procedure successfully completed

Thanks again!


2015-04-14 18:12 keltezéssel, Nick Gorham írta:
> On 14/04/15 17:04, Rébeli-Szabó Tamás wrote:
>> Hello Michael and Nick,
>> thank you both for your valuable input.
>> As Michael says, the target type is SQL_C_UTINTYINT, as also 
>> evidenced by the ODBC trace:
>> ODBC][11041][1429005970.975326][SQLBindCol.c][236]
>>                 Entry:
>>                         Statement = 0x276ecb0
>>                         Column Number = 1
>>                         Target Type = -28 SQL_C_UTINYINT
>>                         Target Value = 0x27f5af8
>>                         Buffer Length = 1
>>                         StrLen Or Ind = 0x27f5eb8
>> [ODBC][11041][1429005970.975349][SQLBindCol.c][341]
>> As per my understanding, there are at least three data type leves at 
>> play here: Native Informix SQL types, ODBC driver SQL types and ODBC 
>> driver C types.
>> **According to the documentation, the ODBC Driver SQL type for the 
>> native Informix boolean type is SQL_BIT, and the Informix ODBC Driver 
>> can convert the SQL_BIT ODBC SQL type into SQL_C_BINARY, SQL_C_CHAR 
>> and SQL_C_BIT ODBC C types.
>> I assume this means that the native boolean type cannot be presented 
>> as an SQL_CHAR ODBC SQL type for instance, and Oracle is requesting 
>> the ODBC Driver to convert SQL_BIT ODBC SQL type into SQL_C_UTINYINT 
>> ODBC C type, which the ODBC Driver cannot do?
>> As for creating a view in Informix, that is unfortunately not 
>> possible because the source system cannot be touched. Thank you for 
>> your idea though, Nick.
>> So does this all mean I will have no luck with ODBC?
> Depends. If you can change what you are asking Oracle to do then it 
> may be possible to get around the problem. Maybe with a passthrough 
> query to take some of the choices out of Oracle's hands.

More information about the unixODBC-support mailing list