[unixODBC-support] Oracle -- Informix boolean error

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


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?


Regards,

tamas


2015-04-14 15:16 keltezéssel, Nick Gorham írta:
> On 14/04/15 13:56, Michael König wrote:
>> Hi there!
>>
>> Sorry, I've never worked with Informix before, and I have never used 
>> Oracle's ODBC import. From your pastebin stuff, I note the following 
>> lines:
>>
>> DTY     NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
>>  -7 BIT Y          1          1   0/  0    0   0  20 stat
>> Performing delayed open.
>>  SQLBindCol: column 1, cdatatype: -28, bflsz: 1
>> Entered hgopoer at 2015/04/14-12:22:03
>> hgopoer, line 233: got native error -11013 and sqlstate 07006; 
>> message follows...
>> [Informix][Informix ODBC Driver]Restricted data type attribute 
>> violation. {07006,NativeErr = -11013}
>>
>> -7 is the type code for bit fields in the database (SQL_BIT). Oracle 
>> tries to bin this field to the C data type with identifier -28 
>> (cdatatype). In unixodbc's sqlext.h file, this identifier matches 
>> SQL_C_UTINYINT (unsigned tiny integer). So Oracle tries to bind a 
>> boolean field to a buffer of unsigned tiny integers. This requires 
>> data conversion. If this conversion is not supported, errors like the 
>> one you encountered might happen. You could check Informix's ODBC 
>> documentation to get some insight in what conversions are supported.
>>
>> iusql probably uses a more suitable (read: supported) C data type, 
>> for example SQL_C_BIT. This would explain that you don't see an error 
>> there.
>>
>> Concerning a potential workaround: Some databases (such as PostgreSQL 
>> offer flags which handle how SQL_BIT fields are presented to ODBC 
>> applications. If Informix had such a flag, you could use it to get 
>> boolean fields as strings by manipulating your odbc.ini file. Oracle 
>> would bind the field differently, then, thus avoiding the problem you 
>> see. Just a shot in the dark, though.
>>
>> Hope this helps!
>>
>> Cheers
>>
>> Michael
>
> Yep, another workaround would be to create a view where the bitfield 
> column is mapped into a integer or varchar usig cast or convert, and 
> query this view from oracle.
>
> isql/iusql gets data back as SQL_CHAR/SQL_WCHAR because this is most 
> likely to work in all situations (and its simpler for the application).
>



More information about the unixODBC-support mailing list