FreeTDS Development Group

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

Hi Josh,

If @label can validly be passed as a NULL parameter, then you really should 
recode the SP.

in this case though, would 

where d.label = @label

be functionally equivalent to 

where d.label = NULL

?


the  [!]= NULL syntax is, as you know, non standard. 
Sybase accepts it, and MSSQL will accept it if you set the right switch, but 
I'd get rid if I were you...

"IS [NOT] NULL" should be used instead.

Bill


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Paparo, Joshua
Sent: 03 December 2004 12:01
To: FreeTDS Development Group
Subject: RE: [freetds] stored proc parameters


Thanks Bill.

Here's the text.

create procedure pdtMatchAlphas
        @date datetime,
        @label varchar(32)
as
begin

        select p.tenor, p.value, d.pricingdate, d.label, p.sequence
        from AlphaMatrix d, AlphaMatrixPoint p
        where d.label = @label
        and d.pricingdate = @date
        and d.id = p.matrixid
        order by p.sequence

end

GO

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Thompson, Bill D 
(London)
Sent: 03 December 2004 11:59
To: FreeTDS Development Group
Subject: RE: [freetds] stored proc parameters

let me take a look at the rpc code.
I'll get back to you.

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Paparo, Joshua
Sent: 03 December 2004 11:54
To: FreeTDS Development Group
Subject: RE: [freetds] stored proc parameters


Thanks for that Bill. 

An empty string will indeed cause that proc to return zero rows.

In that case my problem becomes how do I pass parameters to the function 
dbrpcparam() for say a null varchar. I think my problem is the last parameter 
(BYTE* value). Using my old Sybase API I can pass a null pointer here to do 
the job and things work ok. The FreeTDS code here checks 'value' and will not 
add the parameter to the proc if it's null. 

I was just passing an empty string as default but this clearly is not the way 
to go.

Josh

11:33:52.850738 dbsqlok() marker is 81
11:33:52.850752 dbsqlok() found result token
11:34:29.384963 processing row tokens.  marker is  81(TDS7_RESULT)
11:34:29.385018 dbresults()
11:34:29.385030 processing result tokens.  marker is  81(TDS7_RESULT)
11:34:29.385040 tds_free_all_results()
11:34:29.385063 processing TDS7 result. set current_results to tds->res_info
11:34:29.385076 adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 8
        Client charset: ISO-8859-1
        Client column_size: 8
11:34:29.385085 tds_get_string: reading 10 from wire to give 5 to client.
11:34:29.385102 tds7_get_data_info:1554: 
        colname = tenor (5 bytes)
        type = 39 (varchar)
        server's type = 167 (xvarchar)
        column_varint_size = 2
        column_size = 8 (8 on server)
11:34:29.385124 tds_get_string: reading 10 from wire to give 5 to client.
11:34:29.385134 tds7_get_data_info:1554: 
        colname = value (5 bytes)
        type = 62 (float)
        server's type = 62 (float)
        column_varint_size = 0
        column_size = 8 (8 on server)
11:34:29.385144 tds_get_string: reading 22 from wire to give 11 to client.
11:34:29.385152 tds7_get_data_info:1554: 
        colname = pricingdate (11 bytes)
        type = 61 (datetime)
        server's type = 61 (datetime)
        column_varint_size = 0
        column_size = 8 (8 on server)
11:34:29.385162 adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 16
        Client charset: ISO-8859-1
        Client column_size: 16
11:34:29.385171 tds_get_string: reading 10 from wire to give 5 to client.
11:34:29.385178 tds7_get_data_info:1554: 
        colname = label (5 bytes)
        type = 39 (varchar)
        server's type = 167 (xvarchar)
        column_varint_size = 2
        column_size = 16 (16 on server)
11:34:29.385188 tds_get_string: reading 16 from wire to give 8 to client.
11:34:29.385195 tds7_get_data_info:1554: 
        colname = sequence (8 bytes)
        type = 52 (smallint)
        server's type = 52 (smallint)
        column_varint_size = 0
        column_size = 2 (2 on server)
11:34:29.385205 dbresults() process_result_tokens returned result_type = 4049 
retcode = 1
11:34:29.385215 processing result tokens.  marker is  a9(ORDERBY)
11:34:29.385222 tds_process_default_tokens() marker is a9(ORDERBY)
11:34:29.385229 token.c:217: Eating ORDERBY token
11:34:29.385237 processing result tokens.  marker is  ff(DONEINPROC)
11:34:29.385246 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 1
11:34:29.385254                 rows_affected = 0
11:34:29.385260 dbresults() process_result_tokens returned result_type = 4054 
retcode = 1

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Thompson, Bill D 
(London)
Sent: 03 December 2004 11:40
To: FreeTDS Development Group
Subject: RE: [freetds] stored proc parameters

Josh,

your date parameter is getting through OK, but the varchar parameter is going 
through with zero length, i.e an empty string.

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org 
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Paparo, Joshua
Sent: 03 December 2004 11:17
To: freetds AT lists.ibiblio.org
Subject: [freetds] stored proc parameters


Hi,

 

I am using FreeTDS 0.63RC2 on GNU Linux 2.4.20 to connect to SQLServer2000 
and am having trouble with stored procedures.

 

I'm attempting a port of RogueWave's dbtools++ (dblib) from Sybase to FreeTDS.

 

Procedures I run with just null parameters seem okay but when I try to run 
one with say a date (non-null) and a string (null) as below I get no rows 
returned when I know there should be. I have verified this in both tsql and 
sqsh.

 

Is it possible to tell from the hex code below what values for @date and 
@label I'm actually sending to the server?

 

Thanks,

 

Josh Paparo

 

10:57:12.317001 tds_process_end() state set to TDS_IDLE

10:59:18.640678 dbrpcinit() added rpcname "dbo.pdtMatchAlphas"

11:00:22.296402 dbrpcparam() added parameter "@date"

11:02:25.675630 dbrpcparam() added parameter "@label"

11:02:39.413132 dbrpcsend()

11:02:39.413228 tds_free_all_results()

11:02:39.413275 tds_put_data_info putting param_name 

11:02:39.413292 tds_put_data_info putting status 

11:02:39.413305 tds_put_data: is_null = 0, colsize = 8

11:02:39.413316 tds_put_data: not null param varint_size = 0

11:02:39.413329 tds_put_data_info putting param_name 

11:02:39.413343 tds_put_data_info putting status 

11:02:39.413354 tds_put_data: is_null = 0, colsize = 0

11:02:39.413364 tds_put_data: not null param varint_size = 2

11:02:39.413378 Sending packet

0000 03 01 00 5d 00 00 01 00-12 00 64 00 62 00 6f 00 |...].... ..d.b.o.|

0010 2e 00 70 00 64 00 74 00-4d 00 61 00 74 00 63 00 |..p.d.t. M.a.t.c.|

0020 68 00 41 00 6c 00 70 00-68 00 61 00 73 00 00 00 |h.A.l.p. h.a.s...|

0030 05 40 00 64 00 61 00 74-00 65 00 00 3d 0e 8c 00 |.@.d.a.t e..=...|

0040 00 00 00 00 00 06 40 00-6c 00 61 00 62 00 65 00 |......@. l.a.b.e.|

0050 6c 00 00 a7 00 00 09 04-c0 00 33 00 00          |l..§.... À.3..|

 

11:02:40.175909 in dbsqlok() 

11:02:40.175987 Received header

0000 04 01 00 97 00 33 01 00-                        |.....3..|

 

11:02:40.176015 Received packet

0000 81 05 00 28 01 0a 00 a7-08 00 09 04 c0 00 33 05 |...(...§ .À.3.|

0010 74 00 65 00 6e 00 6f 00-72 00 00 00 08 00 3e 05 |t.e.n.o. r.....>.|

0020 76 00 61 00 6c 00 75 00-65 00 0c 01 08 00 3d 0b |v.a.l.u. e.....=.|

0030 70 00 72 00 69 00 63 00-69 00 6e 00 67 00 64 00 |p.r.i.c. i.n.g.d.|

0040 61 00 74 00 65 00 14 01-0b 00 a7 10 00 09 04 c0 |a.t.e... §....À|

0050 00 33 05 6c 00 61 00 62-00 65 00 6c 00 00 00 08 |.3.l.a.b .e.l....|

0060 00 34 08 73 00 65 00 71-00 75 00 65 00 6e 00 63 |.4.s.e.q .u.e.n.c|

0070 00 65 00 a9 02 00 05 00-ff 11 00 c1 00 00 00 00 |.e.©.... ÿ..Á....|

0080 00 79 00 00 00 00 fe 00-00 e0 00 00 00 00 00    |.y....þ. à.....|

 

11:02:40.176080 dbsqlok() marker is 81

11:02:40.176088 dbsqlok() found result token




Notice to recipient:
The information in this internet e-mail and any attachments is confidential 
and may be privileged. It is intended solely for the addressee. If you are 
not the intended addressee please notify the sender immediately by telephone. 
If you are not the intended recipient, any disclosure, copying, distribution 
or any action taken or omitted to be taken in reliance on it, is prohibited 
and may be unlawful.

When addressed to external clients any opinions or advice contained in this 
internet e-mail are subject to the terms and conditions expressed in any 
applicable governing terms of business or client engagement letter issued by 
the pertinent Bank of America group entity.

If this email originates from the U.K. please note that Bank of America, 
N.A., London Branch, Banc of America Securities Limited and Banc of America 
Futures Incorporated are authorised and regulated by the Financial Services 
Authority.

_______________________________________________
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



Notice to recipient:
The information in this internet e-mail and any attachments is confidential 
and may be privileged. It is intended solely for the addressee. If you are 
not the intended addressee please notify the sender immediately by telephone. 
If you are not the intended recipient, any disclosure, copying, distribution 
or any action taken or omitted to be taken in reliance on it, is prohibited 
and may be unlawful.

When addressed to external clients any opinions or advice contained in this 
internet e-mail are subject to the terms and conditions expressed in any 
applicable governing terms of business or client engagement letter issued by 
the pertinent Bank of America group entity.

If this email originates from the U.K. please note that Bank of America, 
N.A., London Branch, Banc of America Securities Limited and Banc of America 
Futures Incorporated are authorised and regulated by the Financial Services 
Authority.

_______________________________________________
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



Notice to recipient:
The information in this internet e-mail and any attachments is confidential 
and may be privileged. It is intended solely for the addressee. If you are 
not the intended addressee please notify the sender immediately by telephone. 
If you are not the intended recipient, any disclosure, copying, distribution 
or any action taken or omitted to be taken in reliance on it, is prohibited 
and may be unlawful.

When addressed to external clients any opinions or advice contained in this 
internet e-mail are subject to the terms and conditions expressed in any 
applicable governing terms of business or client engagement letter issued by 
the pertinent Bank of America group entity.

If this email originates from the U.K. please note that Bank of America, 
N.A., London Branch, Banc of America Securities Limited and Banc of America 
Futures Incorporated are authorised and regulated by the Financial Services 
Authority.

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