FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Freddy,

> It seems that mssql try to use the same type used in input and fail on
> NULL if client pass not-nullable types. It returns also garbage !!! I
> don't think it's a different code but a bug (you can consider a client
> or a server problem... client should send correct type while server
> should validate more accurately data and not send garbage). So... all
we
> have to change it's tds_set_param_type in src/tds/data.c (and possibly
> add some tests too). 

Good analysis - you may be onto something there.

I have long thought we should use "sp_sproc_columns" - which exists in
both Sybase and SQL Server - to return to us the data types of the SP
parameters.
We could do this at dbrpcinit() or similar.

This sp exists for just this purpose AFAICT.

Bill 

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of ZIGLIO,
Frediano, VF-IT
Sent: 07 January 2005 11:06
To: FreeTDS Development Group
Subject: RE: [freetds] RE: output parameters and "unknown marker"


> 
> All,
> 
> I'm having a good look at our handling of AC (RETURNVALUE) packets.
> I'm pretty sure now that MS's version of this packet is different from
> Sybase's, and as yet it doesn't appear to make much sense.
> I'll keep you posted.
> 
> Bill
> 
> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of 
> Thompson, Bill D
> (London)
> Sent: 07 January 2005 10:21
> To: FreeTDS Development Group
> Subject: RE: [freetds] RE: output parameters and "unknown marker"
> 
> 
> Hi Alan,
> 
> I've recently noticed how well jTDS is doing - congratulations on API
> cursors!
> 
> I'd noticed the length thing too - there seem to be issues 
> with the SQL
> server AC (RETURNVALUE) packet.
> 
> I've just run a test on a RPC call to Sybase and SQL server where the
> RPC returns a NULL in an INT OUTPUT param.
> 
> Here's the two AC (RETURNVALUE) Packets...
> 
> Sybase:
> 
> token packet  name    name                                     max
> actual
>       length  length  @nrows)        status  user type   type  length
> length
> 
> ac    000f    06      406e726f7773   01      00000000    26    04
> 00
> 
> 
> Notice several things about this:
> 
> 1) the packet length is correct
> 2) the data type is SYBINTN (showing it is a variable length datatype)
> 
> SQL Server
> 
> token packet  name    name
> 
>       length  length  @nrows)                  status  user 
> type   type
> ??
> 
> ac    1300    06      40006e0072006f0077007300 01      38000000    38
> 00
> 
> Notice several things about this:
> 
> 1) packet length seems to make no sense
> 2) user type and type are both SYBINT4 (fixed length datatype)
> 3) The following 00 could be a single byte "length of data" or a NULL
> Indicator.
>    but how are we supposed to know , given that we expect fixed length
> data to follow.
> 
> Have you fixed this in jTDS ?
> 
> Bill
> 
> 
> 
> 
> 
> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Alin Sinpalean
> Sent: 07 January 2005 10:02
> To: freetds AT lists.ibiblio.org
> Subject: [freetds] RE: output parameters and "unknown marker"
> 
> 
> Has anyone else noticed that the packet length is incorrect? 
> According 
> to the header it's supposed to be 17 bytes but it's 18. This makes me 
> think it's an SQL Server problem.
> 
> Alin Sinpalean,
> The jTDS Project.

Yes and not... Why do ODBC tests work correctly ??

See some log snipped

./params

11:39:03.4292981059 sql2tds.c:120 type=4
11:39:03.4292981070 sql2tds.c:126
11:39:03.4292981089 sql2tds.c:268
11:39:03.4292981099 sql2tds.c:120 type=4
11:39:03.4292981108 sql2tds.c:126
11:39:03.4292981118 setting column 1 NULL bit
11:39:03.4292981128 sql2tds.c:120 type=12
11:39:03.4292981137 sql2tds.c:126
11:39:03.4292981146 setting column 2 NULL bit
11:39:03.4292981155 tds_free_all_results()
11:39:03.4292981172 tds_put_data_info putting param_name
11:39:03.4292981184 tds_put_data_info putting status
11:39:03.4292981193 tds_put_data: is_null = 0, colsize = 4
11:39:03.4292981202 tds_put_data: not null param varint_size = 1
11:39:03.4292981210 tds_put_data_info putting param_name
11:39:03.4292981219 tds_put_data_info putting status
11:39:03.4292981227 tds_put_data: is_null = 1, colsize = 0
11:39:03.4292981235 tds_put_data: null param
11:39:03.4292981243 tds_put_data_info putting param_name
11:39:03.4292981252 tds_put_data_info putting status
11:39:03.4292981260 tds_put_data: is_null = 1, colsize = 0
11:39:03.4292981269 tds_put_data: null param
11:39:03.4292981278 Sending packet
0000 03 01 00 3a 00 00 01 00-0a 00 73 00 70 00 54 00 |...:.... ..s.p.T.|
0010 65 00 73 00 74 00 50 00-72 00 6f 00 63 00 00 00 |e.s.t.P. r.o.c...|
0020 00 00 26 04 04 05 00 00-00 00 01 26 04 00 00 01 |..&..... ...&....|
0030 a7 14 00 09 04 d0 00 34-ff ff                   |.......4 ..|

11:39:03.4292998633 Received header
0000 04 01 00 6a 00 35 01 00-                        |...j.5..|

11:39:03.4292998681 Received packet
0000 ff 11 00 c1 00 01 00 00-00 ff 11 00 c1 00 01 00 |........ ........|
0010 00 00 ff 01 00 c0 00 00-00 00 00 ff 11 00 c1 00 |........ ........|
0020 01 00 00 00 79 00 00 00-00 ac 0d 00 00 01 38 00 |....y... ......8.|
0030 00 00 26 04 04 05 00 00-00 ac 1d 00 00 01 a7 00 |..&..... ........|
0040 00 00 a7 14 00 09 04 d0-00 34 0d 00 54 68 69 73 |........ .4..This|
0050 20 69 73 20 63 6f 6f 6c-21 fe 00 00 e0 00 01 00 | is cool !.......|
0060 00 00                  -                        |..|

11:39:03.4292998781 processing result tokens.  marker is  ff(DONEINPROC)
11:39:03.4292998809 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
11:39:03.4292998819                 rows_affected = 1
11:39:03.4292998829 processing result tokens.  marker is  ff(DONEINPROC)
11:39:03.4292998838 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
11:39:03.4292998847                 rows_affected = 1
11:39:03.4292998856 processing result tokens.  marker is  ff(DONEINPROC)
11:39:03.4292998864 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 0
11:39:03.4292998874 processing result tokens.  marker is  ff(DONEINPROC)
11:39:03.4292998898 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
11:39:03.4292998908                 rows_affected = 1
11:39:03.4292998917 processing result tokens.  marker is
79(RETURNSTATUS)
11:39:03.4292998926 tds_process_result_tokens: return status is 0
11:39:03.4292998935 convert_tds2sql: src is 56 dest = -15
11:39:03.4292998951 processing result tokens.  marker is  ac(PARAM)
11:39:03.4292998970 processing result. type = 38(integer-null),
varint_size 1
11:39:03.4292998980 processing result. column_size 4
11:39:03.4292998990 processing row.  column is 0 varint size = 1
11:39:03.4292998999 processing row.  column size is 4
11:39:03.4292999008 clearing column 0 NULL bit
11:39:03.4292999019 processing result. type = 39(varchar), varint_size 2
11:39:03.4292999029 processing result. column_size 20
11:39:03.4292999038 adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 20
        Client charset: ISO-8859-1
        Client column_size: 20
11:39:03.4292999048 processing row.  column is 1 varint size = 2
11:39:03.4292999057 processing row.  column size is 13
11:39:03.4292999065 clearing column 1 NULL bit
11:39:03.4292999087 convert_tds2sql: src is 56 dest = -15
11:39:03.4292999097 convert_tds2sql: src is 39 dest = 1
11:39:03.4292999107 convert_tds2sql: outputting character data destlen =
20
11:39:03.4292999117 processing result tokens.  marker is  fe(DONEPROC)
11:39:03.4292999126 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
11:39:03.4292999135 tds_process_end() state set to TDS_IDLE
11:39:03.4292999143 tds_process_result_tokens() state is COMPLETED
11:39:03.4292999178 tds_free_all_results()

./genparams

11:56:29.4293032193 tds_put_string converting 97 bytes of "CREATE PROC
spTestProc @i DATETIME OUTPUT AS SELECT @i = CONVERT(DATETIME,
'2004-02-24 15:16:17')"
11:56:29.4293032209 tds_put_string wrote 194 bytes
11:56:29.4293032218 Sending packet
0000 01 01 00 ca 00 00 01 00-43 00 52 00 45 00 41 00 |........ C.R.E.A.|
0010 54 00 45 00 20 00 50 00-52 00 4f 00 43 00 20 00 |T.E. .P. R.O.C. .|
0020 73 00 70 00 54 00 65 00-73 00 74 00 50 00 72 00 |s.p.T.e. s.t.P.r.|
0030 6f 00 63 00 20 00 40 00-69 00 20 00 44 00 41 00 |o.c. .@. i. .D.A.|
0040 54 00 45 00 54 00 49 00-4d 00 45 00 20 00 4f 00 |T.E.T.I. M.E. .O.|
0050 55 00 54 00 50 00 55 00-54 00 20 00 41 00 53 00 |U.T.P.U. T. .A.S.|
0060 20 00 53 00 45 00 4c 00-45 00 43 00 54 00 20 00 | .S.E.L. E.C.T. .|
0070 40 00 69 00 20 00 3d 00-20 00 43 00 4f 00 4e 00 |@.i. .=.  .C.O.N.|
0080 56 00 45 00 52 00 54 00-28 00 44 00 41 00 54 00 |V.E.R.T. (.D.A.T.|
0090 45 00 54 00 49 00 4d 00-45 00 2c 00 20 00 27 00 |E.T.I.M. E.,. .'.|
00a0 32 00 30 00 30 00 34 00-2d 00 30 00 32 00 2d 00 |2.0.0.4. -.0.2.-.|
00b0 32 00 34 00 20 00 31 00-35 00 3a 00 31 00 36 00 |2.4. .1. 5.:.1.6.|
00c0 3a 00 31 00 37 00 27 00-29 00                   |:.1.7.'. ).|

11:56:29.4293056395 Received header
0000 04 01 00 11 00 35 01 00-                        |.....5..|

11:56:29.4293056440 Received packet
0000 fd 00 00 de 00 00 00 00-00                      |........ .|

11:56:29.4293056457 processing result tokens.  marker is  fd(DONE)
11:56:29.4293056486 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
11:56:29.4293056496 tds_process_end() state set to TDS_IDLE
11:56:29.4293056506 tds_process_result_tokens() state is COMPLETED
11:56:29.4293056540 sql2tds.c:120 type=93
11:56:29.4293056550 sql2tds.c:126
11:56:29.4293056570 setting column 0 NULL bit
11:56:29.4293056580 tds_free_all_results()
11:56:29.4293056595 tds_put_data_info putting param_name
11:56:29.4293056604 tds_put_data_info putting status
11:56:29.4293056614 tds_put_data: is_null = 1, colsize = 0
11:56:29.4293056622 tds_put_data: null param
11:56:29.4293056631 Sending packet
0000 03 01 00 25 00 00 01 00-0a 00 73 00 70 00 54 00 |...%.... ..s.p.T.|
0010 65 00 73 00 74 00 50 00-72 00 6f 00 63 00 00 00 |e.s.t.P. r.o.c...|
0020 00 01 6f 08 00         -                        |..o..|

11:56:29.4293079177 Received header
0000 04 01 00 33 00 35 01 00-                        |...3.5..|

11:56:29.4293079218 Received packet
0000 ff 11 00 c1 00 01 00 00-00 79 00 00 00 00 ac 11 |........ .y......|
0010 00 00 01 3d 00 00 00 6f-08 08 97 94 00 00 2c aa |...=...o ......,.|
0020 fb 00 fe 00 00 e0 00 01-00 00 00                |........ ...|

11:56:29.4293079263 processing result tokens.  marker is  ff(DONEINPROC)
11:56:29.4293079287 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
11:56:29.4293079297                 rows_affected = 1
11:56:29.4293079307 processing result tokens.  marker is
79(RETURNSTATUS)
11:56:29.4293079316 tds_process_result_tokens: return status is 0
11:56:29.4293079325 processing result tokens.  marker is  ac(PARAM)
11:56:29.4293079340 processing result. type = 111(datetime-null),
varint_size 1
11:56:29.4293079366 processing result. column_size 8
11:56:29.4293079377 processing row.  column is 0 varint size = 1
11:56:29.4293079386 processing row.  column size is 8
11:56:29.4293079395 clearing column 0 NULL bit
11:56:29.4293079405 convert_tds2sql: src is 61 dest = -2
11:56:29.4293079414 convert_tds2sql: outputting binary data destlen =
256
11:56:29.4293079422 processing result tokens.  marker is  fe(DONEPROC)
11:56:29.4293079432 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
11:56:29.4293079441 tds_process_end() state set to TDS_IDLE
11:56:29.4293079449 tds_process_result_tokens() state is COMPLETED


As you can see I use always nullable type and I receive correctly data.
It seems that mssql try to use the same type used in input and fail on
NULL if client pass not-nullable types. It returns also garbage !!! I
don't think it's a different code but a bug (you can consider a client
or a server problem... client should send correct type while server
should validate more accurately data and not send garbage). So... all we
have to change it's tds_set_param_type in src/tds/data.c (and possibly
add some tests too). 

freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds 
--------------------------------------------------------
 
If you are not an intended recipient of this e-mail, please notify the 
sender, delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/ 
--------------------------------------------------------