issue with stored proc parameters of type varchar

[email protected]
Newsgroups gmane.comp.db.tds.freetds
Message-ID <OF40F9B9E8.BFD95FB7-ONC1257528.005D0983-C1257528.005F173A@asset-control.com>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.