FreeTDS Development Group

["Paul Steward" <Paul.Steward AT infospace.com>]

Can a stored procedure's return value be retrieved?  

I am attempting to invoke a MS SQL Server 2000 stored procedure via DBI
over ODBC over FreeTDS.  The sproc's return value, which should be 20,
always comes back undefined.  I can prepare()/execute() the procedure
successfully, bind the return value to a scalar, and even see the
correct return value in the TDS dump file.  But the perl script never
gets the return value.  What code change do I need to make to pick up
the return value?  Enviromental particulars below.




---- Environment -------
Linux 2.4.20 (Debian)
Perl:  5.6.1
DBI: 1.35
DBD::ODBC: 1.05
unixODBC: 2.2.5
FreeTDS: 0.62 (cvs version)
TDS Protocol Version: 7.0
MSSQL Server: 2000 (Service Pack 2)

---- Stored Proc -------
tsql -S dolap1a -U user -P pass <<EOF
Create proc dbo.my_proc as return 20
go
EOF

---- Script -------
#!/usr/local/bin/perl -w

use DBI qw(:sql_types);

$rv  = 0;
$dbh = DBI->connect("dbi:ODBC:dolap1a", 'user', 'pass', {PrintError =>
1, RaiseError => 1, AutoCommit => 1});

$dbh->trace(1);
my $sth = $dbh->prepare('{? = call dbo.my_proc}');
$sth->bind_param_inout(1, \$rv, 100, DBI::SQL_INTEGER);
$sth->execute();
$sth->finish();
print "return: [$rv]\n";

$dbh->disconnect;

__END__
=== Output of above program
    DBI::db=HASH(0x8100c14) trace level set to 1 in DBI 1.35-nothread
    <- prepare('{? = call dbo.my_proc}')= DBI::st=HASH(0x8205938) at
tds_base.pl line 9
    <- bind_param_inout(1 SCALAR(0x80fcd90) ...)= 1 at tds_base.pl line
10
    <- execute= -1 at tds_base.pl line 11
    <- finish= 1 at tds_base.pl line 12
return: []
    <- disconnect= 1 at tds_base.pl line 15
    <- DESTROY= undef
    <- DESTROY= undef during global destruction


---- TDS dump log -------
Starting log file for FreeTDS 0.62.dev.20030714
        on 2003-07-17 14:42:13 with debug level 99.
names for ISO-8859-1: ISO-8859-1
names for US-ASCII: US-ASCII
names for UCS-2LE: UCS-2LE
names for UCS-2BE: UCS-2BE
iconv to convert client-side data to the "ISO-8859-1" character set
14:42:13.887017 tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
14:42:13.888459 tds_iconv_info_init: converting "US-ASCII"->"UCS-2LE"
14:42:13.888492 Connecting to 10.111.2.21 port 1433, TDS 7.0.
14:42:13.889701 tds_put_string converting 9 bytes of "pimpernel"
14:42:13.889766 tds_put_string wrote 18 bytes
14:42:13.889780 tds_put_string converting 9 bytes of "interface"
14:42:13.889793 tds_put_string wrote 18 bytes
14:42:13.889811 tds_put_string wrote 0 bytes
14:42:13.889824 tds_put_string converting 6 bytes of "SYBASE"
14:42:13.889838 tds_put_string wrote 12 bytes
14:42:13.889849 tds_put_string wrote 0 bytes
14:42:13.889860 tds_put_string converting 10 bytes of "us_english"
14:42:13.889873 tds_put_string wrote 20 bytes
14:42:13.889884 tds_put_string converting 9 bytes of "interface"
14:42:13.889897 tds_put_string wrote 18 bytes
14:42:13.890010 tds_process_login_tokens()
Received header @ 14:42:13.892465
0000 04 01 01 a6 00 3f 01 00-                        |.....?..|


Received packet @ 14:42:13.892517
0000 e3 21 00 01 09 69 00 6e-00 74 00 65 00 72 00 66 |.!...i.n .t.e.r.f|
0010 00 61 00 63 00 65 00 06-6d 00 61 00 73 00 74 00 |.a.c.e.. m.a.s.t.|
0020 65 00 72 00 ab 6a 00 45-16 00 00 02 00 28 00 43 |e.r..j.E .....(.C|
0030 00 68 00 61 00 6e 00 67-00 65 00 64 00 20 00 64 |.h.a.n.g .e.d. .d|
0040 00 61 00 74 00 61 00 62-00 61 00 73 00 65 00 20 |.a.t.a.b .a.s.e. |
0050 00 63 00 6f 00 6e 00 74-00 65 00 78 00 74 00 20 |.c.o.n.t .e.x.t. |
0060 00 74 00 6f 00 20 00 27-00 69 00 6e 00 74 00 65 |.t.o. .' .i.n.t.e|
0070 00 72 00 66 00 61 00 63-00 65 00 27 00 2e 00 07 |.r.f.a.c .e.'....|
0080 44 00 4f 00 4c 00 41 00-50 00 31 00 41 00 00 00 |D.O.L.A. P.1.A...|
0090 00 e3 17 00 02 0a 75 00-73 00 5f 00 65 00 6e 00 |......u. s._.e.n.|
00a0 67 00 6c 00 69 00 73 00-68 00 00 ab 68 00 47 16 |g.l.i.s. h...h.G.|
00b0 00 00 01 00 27 00 43 00-68 00 61 00 6e 00 67 00 |....'.C. h.a.n.g.|
00c0 65 00 64 00 20 00 6c 00-61 00 6e 00 67 00 75 00 |e.d. .l. a.n.g.u.|
00d0 61 00 67 00 65 00 20 00-73 00 65 00 74 00 74 00 |a.g.e. . s.e.t.t.|
00e0 69 00 6e 00 67 00 20 00-74 00 6f 00 20 00 75 00 |i.n.g. . t.o. .u.|
00f0 73 00 5f 00 65 00 6e 00-67 00 6c 00 69 00 73 00 |s._.e.n. g.l.i.s.|
0100 68 00 2e 00 07 44 00 4f-00 4c 00 41 00 50 00 31 |h....D.O .L.A.P.1|
0110 00 41 00 00 00 00 e3 0f-00 03 05 69 00 73 00 6f |.A...... ...i.s.o|
0120 00 5f 00 31 00 01 00 00-e3 0b 00 05 04 31 00 30 |._.1.... .....1.0|
0130 00 33 00 33 00 00 e3 0f-00 06 06 31 00 39 00 36 |.3.3.... ...1.9.6|
0140 00 36 00 30 00 39 00 00-ad 36 00 01 07 00 00 00 |.6.0.9.. .6......|
0150 16 4d 00 69 00 63 00 72-00 6f 00 73 00 6f 00 66 |.M.i.c.r .o.s.o.f|
0160 00 74 00 20 00 53 00 51-00 4c 00 20 00 53 00 65 |.t. .S.Q .L. .S.e|
0170 00 72 00 76 00 65 00 72-00 00 00 00 00 08 00 02 |.r.v.e.r ........|
0180 f8 e3 11 00 04 03 35 00-31 00 32 00 04 34 00 30 |......5. 1.2..4.0|
0190 00 39 00 36 00 fd 00 00-00 00 00 00 00 00       |.9.6.... ......|


14:42:13.892889 looking for login token, got  e3(ENVCHANGE)
14:42:13.892907 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 18 from wire to give 9 to client.
tds_get_string: reading 12 from wire to give 6 to client.
14:42:13.892985 looking for login token, got  ab(INFO)
14:42:13.893000 tds_process_default_tokens() marker is ab(INFO)
tds_get_string: reading 80 from wire to give 40 to client.
tds_get_string: reading 14 from wire to give 7 to client.
14:42:13.893065 looking for login token, got  e3(ENVCHANGE)
14:42:13.893081 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 20 from wire to give 10 to client.
14:42:13.893107 looking for login token, got  ab(INFO)
14:42:13.893121 tds_process_default_tokens() marker is ab(INFO)
tds_get_string: reading 78 from wire to give 39 to client.
tds_get_string: reading 14 from wire to give 7 to client.
14:42:13.893163 looking for login token, got  e3(ENVCHANGE)
14:42:13.893177 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 10 from wire to give 5 to client.
tds_get_string: reading 2 from wire to give 1 to client.
14:42:13.893208 looking for login token, got  e3(ENVCHANGE)
14:42:13.893221 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 8 from wire to give 4 to client.
14:42:13.893245 looking for login token, got  e3(ENVCHANGE)
14:42:13.893257 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 12 from wire to give 6 to client.
14:42:13.893281 looking for login token, got  ad(LOGINACK)
14:42:13.893294 looking for login token, got  e3(ENVCHANGE)
14:42:13.893306 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 6 from wire to give 3 to client.
tds_get_string: reading 8 from wire to give 4 to client.
14:42:13.893339 looking for login token, got  fd(DONE)
14:42:13.893352 tds_process_default_tokens() marker is fd(DONE)
14:42:13.893364 tds_process_end() more_results = 0, was_cancelled = 0 
14:42:13.893380 leaving tds_process_login_tokens() returning 1
14:42:13.893437 tds_put_string converting 19 bytes of "set textsize
64512 "
14:42:13.893456 tds_put_string wrote 38 bytes
Sending packet @ 14:42:13.893474
0000 01 01 00 2e 00 00 01 00-73 00 65 00 74 00 20 00 |........ s.e.t. .|
0010 74 00 65 00 78 00 74 00-73 00 69 00 7a 00 65 00 |t.e.x.t. s.i.z.e.|
0020 20 00 36 00 34 00 35 00-31 00 32 00 20 00       | .6.4.5. 1.2. .|


Received header @ 14:42:13.894289
0000 04 01 00 11 00 3f 01 00-                        |.....?..|


Received packet @ 14:42:13.894327
0000 fd 00 00 be 00 00 00 00-00                      |........ .|


14:42:13.894349 processing result tokens.  marker is  fd(DONE)
14:42:13.894363 tds_process_end() more_results = 0, was_cancelled = 0 
14:42:13.894378 tds_process_result_tokens() state is COMPLETED
SQLGetFunctions: fFunction is 999
change_autocommit: executing set implicit_transactions off
14:42:13.894597 tds_put_string converting 29 bytes of "set
implicit_transactions off"
14:42:13.894620 tds_put_string wrote 58 bytes
Sending packet @ 14:42:13.894634
0000 01 01 00 42 00 00 01 00-73 00 65 00 74 00 20 00 |...B.... s.e.t. .|
0010 69 00 6d 00 70 00 6c 00-69 00 63 00 69 00 74 00 |i.m.p.l. i.c.i.t.|
0020 5f 00 74 00 72 00 61 00-6e 00 73 00 61 00 63 00 |_.t.r.a. n.s.a.c.|
0030 74 00 69 00 6f 00 6e 00-73 00 20 00 6f 00 66 00 |t.i.o.n. s. .o.f.|
0040 66 00                  -                        |f.|


Received header @ 14:42:13.895372
0000 04 01 00 11 00 3f 01 00-                        |.....?..|


Received packet @ 14:42:13.895407
0000 fd 00 00 ba 00 00 00 00-00                      |........ .|


14:42:13.895429 processing result tokens.  marker is  fd(DONE)
14:42:13.895443 tds_process_end() more_results = 0, was_cancelled = 0 
14:42:13.895456 tds_process_result_tokens() state is COMPLETED
change_autocommit: executing set implicit_transactions off
14:42:13.895699 tds_put_string converting 29 bytes of "set
implicit_transactions off"
14:42:13.895726 tds_put_string wrote 58 bytes
Sending packet @ 14:42:13.895739
0000 01 01 00 42 00 00 01 00-73 00 65 00 74 00 20 00 |...B.... s.e.t. .|
0010 69 00 6d 00 70 00 6c 00-69 00 63 00 69 00 74 00 |i.m.p.l. i.c.i.t.|
0020 5f 00 74 00 72 00 61 00-6e 00 73 00 61 00 63 00 |_.t.r.a. n.s.a.c.|
0030 74 00 69 00 6f 00 6e 00-73 00 20 00 6f 00 66 00 |t.i.o.n. s. .o.f.|
0040 66 00                  -                        |f.|


Received header @ 14:42:13.897664
0000 04 01 00 11 00 3f 01 00-                        |.....?..|


Received packet @ 14:42:13.897700
0000 fd 00 00 ba 00 00 00 00-00                      |........ .|


14:42:13.897722 processing result tokens.  marker is  fd(DONE)
14:42:13.897736 tds_process_end() more_results = 0, was_cancelled = 0 
14:42:13.897749 tds_process_result_tokens() state is COMPLETED
14:42:13.900070 tds_put_string converting 11 bytes of "dbo.my_proc"
14:42:13.900125 tds_put_string wrote 22 bytes
Sending packet @ 14:42:13.900140
0000 01 01 00 1e 00 00 01 00-64 00 62 00 6f 00 2e 00 |........ d.b.o...|
0010 6d 00 79 00 5f 00 70 00-72 00 6f 00 63 00       |m.y._.p. r.o.c.|


Received header @ 14:42:13.901040
0000 04 01 00 1f 00 3f 01 00-                        |.....?..|


Received packet @ 14:42:13.901313
0000 ff 11 00 c1 00 01 00 00-00 79 14 00 00 00 fe 00 |........ .y......|
0010 00 e0 00 01 00 00 00   -                        |.......|


14:42:13.901356 processing result tokens.  marker is  ff(DONEINPROC)
14:42:13.901371 tds_process_end() more_results = 1, was_cancelled = 0 
14:42:13.901386 processing result tokens.  marker is  79(RETURNSTATUS)
convert_tds2sql: src is 56 dest = 4
14:42:13.901451 processing result tokens.  marker is  fe(DONEPROC)
14:42:13.901468 tds_process_end() more_results = 0, was_cancelled = 0 
14:42:13.901482 tds_process_result_tokens() state is COMPLETED
14:42:13.901637 tds_process_result_tokens() state is COMPLETED
SQLFreeHandle(2, 0x822cee0)
SQLFreeHandle(1, 0x822c460)