Oracle -- Informix boolean error

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle -- Informix boolean error

Rébeli-Szabó Tamás
Hello,

I'm connecting to an Informix database from an Oracle database using
unixODBC:

Informix Database: Enterprise Edition 12.10 for Linux x86_64
Informix ODBC driver: Informix Client SDK Developer Edition 4.10 for
Linux 64-bit
Oracle Database: Enterprise Edition 11.2.0.1.0 64-bit (Oracle Linux 6.6
64-bit)
unixODBC: 2.3.2 x86_64


When I select a boolean type column from a table in the Informix
database, I get the following error:

[Informix][Informix ODBC Driver] Restricted data type attribute
violation. {07006,NativeErr = -11013}

When I do the same using iusql, it works fine.  Other column types work
fine from Oracle.

Could you help me resolve this issue?


Please find my configuration details and traces here:

http://pastebin.com/3Gkn5yeB


Kind regards,

tamas

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Oracle -- Informix boolean error

Michael König
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

> Please find my configuration details and traces here:
>
> http://pastebin.com/3Gkn5yeB
>
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Oracle -- Informix boolean error

Nick Gorham-2
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).

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Oracle -- Informix boolean error

Rébeli-Szabó Tamás
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).
>

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Oracle -- Informix boolean error

Nick Gorham-2
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.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Oracle -- Informix boolean error

Rébeli-Szabó Tamás
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
   1  DECLARE
   2    val  VARCHAR2(1);
   3    c    INTEGER;
   4    nr   INTEGER;
   5  BEGIN
   6    c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@informix;
   7    DBMS_HS_PASSTHROUGH.PARSE@informix(c,'select cast (stat as
character) from bt');
   8    LOOP
   9     nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@informix(c);
  10     EXIT WHEN nr = 0;
  11     DBMS_HS_PASSTHROUGH.GET_VALUE@informix(c, 1, val);
  12     DBMS_OUTPUT.PUT_LINE(val);
  13    END LOOP;
  14    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@informix(c);
  15  END;
  16*
t
f

PL/SQL procedure successfully completed


Thanks again!

tamas


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.
>

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support