Skip to Content.
Sympa Menu

freetds - [freetds] issue with stored proc parameters of type varchar

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: ocfs2 AT asset-control.com
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] issue with stored proc parameters of type varchar
  • Date: Tue, 23 Dec 2008 18:18:38 +0100

I am breaking my head over an issue that we ran into using freetds ct-lib
to connect to a Sybase ASE backend.
In a nutshell: we are calling a stored procedure that has a single
parameter of type varchar, and it is an "input-output" parameter.
However, the value that we get back is padded with spaces.
So I enabled freetds logging, and sure enough it shows that in fact the
tds packet that freetds receives from ASE
already contains those spaces.
So I put Ribo in between, and this shows me an interesting difference
between freetds and Sybase ct-lib:
With Sybase ct-lib I see:

***********************************************
DBRPC Token (0xE6); variable length.
Length [2]: 21
Name Length [1]: 18
Name [18]: "ac_init_connection"
Options [2]: RPC_PARAMS (0x0002)

PARAMFMT Token (0xEC); variable length.
Length [2]: 11
Number of Params [2]: 1
Param 1
Name Length [1]: 0
Status [1]: PARAM_RETURN (0x01)
User Type [4]: 0
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0

PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 6
Param data [6]: "_1_1_0" (0x5F315F315F30)
***********************************************

but with freetds I see:

***********************************************
DBRPC Token (0xE6); variable length.
Length [2]: 21
Name Length [1]: 18
Name [18]: "ac_init_connection"
Options [2]: RPC_PARAMS (0x0002)

PARAMFMT Token (0xEC); variable length.
Length [2]: 11
Number of Params [2]: 1
Param 1
Name Length [1]: 0
Status [1]: PARAM_RETURN (0x01)
User Type [4]: 0
Data Type [1]: CHAR
Length [1]: 255
Locale Length [1]: 0

PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 6
Param data [6]: "_1_0_0" (0x5F315F305F30)
***********************************************

now at least the space padding makes sense as apparently freetds is
indicating a char
rather than a varchar datatype here, which probably causes ASE to pad with
spaces.
My question, are stored procedure parameters always treated as CHAR?
Any idea why the behaviour here differs from Sybase ct-lib?

By the way, with Sybase ct-lib, Ribo also shows me information about the
packets returned by ASE:

Capture Record Header
Source [4]: RESPONSE (0x00000002)
Length [4]: 173

PDU Header
TDS Packet Type [1]: BUF_RESPONSE (0x04)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 173
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0

DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT + DONE_EVENT
(0x0051)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count [4]: 1

DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT + DONE_EVENT
(0x0051)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count [4]: 1

EED Token (0xE5); variable length.
Length [2]: 94
Message Number [4]: 2714
Message State [1]: 1
Message Class [1]: 16
SQL State Length [1]: 5
SQL State [5]: "ZZZZZ"
Status [1]: NO_EED (0x00)
Transaction State [2]: TDS_NOT_IN_TRAN (0x0000)
Message Length [2]: 64
Message Text [64]: "There is already an object named
'blkld_1_1_0' in the database.
"
Server Name Length [1]: 9
Server Name [9]: "beethoven"
Stored Proc. Name Length [1]: 0
Line Number [2]: 1

DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_ERROR + DONE_EVENT
(0x0043)
TranState [2]: TDS_STMT_ABORT (0x0003)
Count (unused) [4]: 0

DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_EVENT (0x0041)
TranState [2]: TDS_STMT_ABORT (0x0003)
Count (unused) [4]: 0

RETURNSTATUS Token (0x79); fixed length.
Length [0]: [4]
Status [4]: 0x00000000

RETURN_VALUE Token (0xAC);
Length [2]: 15
Name Length [1]: 0
Name [0]: [""]
Status [1]: <unrecognized> (0x01)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Maximum Length [1]: 255
Returned Data
Length [1]: 6
Data [6]: "tempdb" (0x74656D706462)

DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL (0x0000)
TranState [2]: TDS_STMT_ABORT (0x0003)
Count (unused) [4]: 0

I don't get this information when connecting with freetds, but this could
be a flaw in Ribo.

tia,
Tim Knigge





  • [freetds] issue with stored proc parameters of type varchar, ocfs2, 12/23/2008

Archive powered by MHonArc 2.6.24.

Top of Page