Conference orarep::nomahs::sql_services

2190.0. "arith_except, open cursor, parameter datatypes" by M5::JHAYTER () Tue Apr 15 1997 11:40

Hi,

Rdb 7.0 SQS 7.0, ODBC 2.10.11

Question on parameter marker datatypes.  Got a customer doing an MS Access
query of:

Select a,b,c,d from table where a = ? and b = ? and c = ? and d = ?

Column C is a TINYINT datatype and if they specify a value greater than the
range of of a tinyint, the open cursor statment errors with:

RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime

My take is the datatype in the sqlda for the tinyint parameter is also
tinyint in size.  From the ODBC side, they have no control over the size
allocation.

And somewhere in the RDB side things just get nasty - but I got no clue how
a value of say "768" gets set up in the cursor declare/open so the error
occurs on the open cursor.  Can someone explain that piece?


If it help to explain, following is a client log I got by using the
sqlsrv$dynamic program doing:

select * from ti where ti = ?
where:  table ti contains 1 column named ti of datatype tinyint

Thanks,
Jerry

PROTOCOL LEVEL LOG CLIENT: write (logonly) at 14:10:56 on 11-Apr-1997
----PACKET ID: 5, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE
------------SQL STATEMENT
----------------SQLSRV_ASCII_STRING, len: 29
--------------------len: 29, value: select * from ti where ti = ?
--------END OF MESSAGE

PROTOCOL LEVEL LOG CLIENT: read at 14:10:56 on 11-Apr-1997
----PACKET ID: 5, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE ACK
------------STATEMENT ID
----------------len: 4, value: 1000001
------------SQLERRD[1]
----------------len: 4, value: 1
------------PARAMETER MARKER SQLDA
----------------len: 2, value: 1
------------SQLVAR
----------------len: 2, value: 0
------------SQLTYPE
----------------len: 2, value: 130
------------SQLLEN
----------------len: 2, value: 4
------------SQLNAME
----------------SQLSRV_ASCII_STRING, len: 2
--------------------len: 2, value: TI
------------SELECT LIST SQLDA
----------------len: 2, value: 3
------------SQLVAR
----------------len: 2, value: 0
------------SQLTYPE
----------------len: 2, value: 130
------------SQLLEN
----------------len: 2, value: 4
------------SQLNAME
----------------SQLSRV_ASCII_STRING, len: 2
--------------------len: 2, value: TI
------------SQLVAR
----------------len: 2, value: 1
------------SQLTYPE
----------------len: 2, value: 129
------------SQLLEN
----------------len: 2, value: 10
------------SQLNAME
----------------SQLSRV_ASCII_STRING, len: 3
--------------------len: 3, value: TIC
------------SQLVAR
----------------len: 2, value: 2
------------SQLTYPE
----------------len: 2, value: 130
------------SQLLEN
----------------len: 2, value: 11
------------SQLNAME
----------------SQLSRV_ASCII_STRING, len: 1
--------------------len: 1, value: I
--------END OF MESSAGE

ROUTINE LEVEL LOG at 14:10:56 on 11-Apr-1997
----SELECT LIST SQLDA
--------SQLDA: SQLD 3
--------[0].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SIZE 4, SCALE 0
------------SQLNAME: TI
--------[1].SQLTYPE: SQLSRV_ASCII_STRING, SQLLEN: 10
------------SQLNAME: TIC
--------[2].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SIZE 11, SCALE 0
------------SQLNAME: I

ROUTINE LEVEL LOG at 14:10:56 on 11-Apr-1997
----PARAMETER MARKER SQLDA
--------SQLDA: SQLD 1
--------[0].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SIZE 4, SCALE 0
------------SQLNAME: TI

ROUTINE LEVEL LOG at 14:10:56 on 11-Apr-1997
----SQLSRV_ALLOCATE_SQLDA_DATA

ROUTINE LEVEL LOG at 14:11:06 on 11-Apr-1997
----SQLSRV_ALLOCATE_SQLDA_DATA

ROUTINE LEVEL LOG at 14:11:06 on 11-Apr-1997
----SQLSRV_OPEN_CURSOR
--------CURSOR NAME
------------SEL
--------STATEMENT ID
------------1000001

PROTOCOL LEVEL LOG CLIENT: write (logonly) at 14:11:06 on 11-Apr-1997
----PACKET ID: 6, PACKET SEQUENCE: 0
--------SQLSRV_OPEN_CURSOR
------------CURSOR NAME
----------------SQLSRV_ASCII_STRING, len: 3
--------------------len: 3, value: SEL
------------STATEMENT ID
----------------len: 4, value: 1000001
------------PARAMETER MARKER SQLDA
----------------len: 2, value: 1
------------SQLVAR INDEX SQLDATA SQLIND
----------------SQLSRV_SQLVAR_INDEX
--------------------len: 2, value: 0
----------------SQLSRV_SQLVAR_SQLIND1
--------------------len: 2, value: 0
----------------SQLSRV_SQLVAR_SQLDATA1, len: 3
--------------------len: 3, value: 768
--------END OF MESSAGE

PROTOCOL LEVEL LOG CLIENT: read at 14:11:06 on 11-Apr-1997
----PACKET ID: 6, PACKET SEQUENCE: 0
--------ERROR ACK
------------ERROR_VALUE_TAG
----------------len: 4, value: -304
------------SPECIFIC_ERROR_TAG
----------------len: 4, value: 0
------------SPECIFIC_ERROR_TEXT_TAG
----------------SQLSRV_ASCII_STRING, len: 95
--------------------len: 95, value: %RDB-E-ARITH_EXCEPT, truncation of a numeric
--------------------Non-printable data
--------END OF MESSAGE

Hi Jerry,

This is known and expected behavior.  

>And somewhere in the RDB side things just get nasty - but I got no clue how
>a value of say "768" gets set up in the cursor declare/open so the error
>occurs on the open cursor.  Can someone explain that piece?

When you run the SQL/Services sample application and ask it to do a 
    select * from ti where ti = ?
    
it will prompt you for the value of ti to use for the select.  You entered 
"768".  SQL/Services uses generalized numbers (ascii) to pass numeric 
data between client and server.  We give Rdb the string "768" for the open 
cursor.  The range of a tinyint is -127 to +127.  When Rdb attempts to 
convert the string "768" to a tinyint, it gets a truncation, thus the 
ARITH_EXCEPT.

Sue