FreeTDS Development Group

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

Josh, Freddy,

The following change to src/dblib/rpc.c
seems to fix the problem with NULL output parameters...

in function param_info-alloc():

 tdsdump_log(TDS_DBG_INFO1, "parm_info_alloc(): parameter null-ness =
%d\n", param_is_null);

- if (param_is_null) {
+ if (param_is_null || (p->status & DBRPCRETURN)) {

Would this be OK do you think freddy ?
Josh, could you test it out ?

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 11:30
To: FreeTDS Development Group
Subject: RE: [freetds] RE: output parameters and "unknown marker"


Freddy,

There is code in dblib/rpc.c - function param_info_alloc() 
that changes the parameter data type from e.g. SYBINT4 to SYBINTN
if the INPUT data is NULL

We could just add some code that does the same if the parameter 
is a RETURN parameter. It seems that these are always potentially 
NULLable...

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/ 
--------------------------------------------------------
 
_______________________________________________
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/ 
--------------------------------------------------------