FreeTDS Development Group

[Garrett Goebel <garrett AT scriptpro.com>]

I've got a problem using ODBC CALL syntax with inout bound parameters to get
at a stored procedure's return value.  

I'm using...

Linux:     Gentoo 1.4
MSSQL:     7.0sp2
unixODBC:  2.0.6
FreeTDS:   0.61
Perl:      5.8.0
DBI:       1.35
DBD::ODBC: 1.05

I've got 3 different variations on the same thing that have different
outcomes:
o  syntax error on valid syntax
o  segmentation fault on valid syntax
o  no error, but unexpected result

1) If I create a stored procedure:

  create procedure odbc_call as return 1

and a perl script:

#!usr/bin/perl -w
use DBI;
my $rv = 0;
my $dbh = DBI->connect(
    'dbi:ODBC:ServerName=RD_Q;Driver=TDS;Database=SPCentral',
    'sa', 'mypasswd',
    {RaiseError => 1, AutoCommit => 1}
) or die DBI->errstr;
$dbh->trace(1);
my $sth = $dbh->prepare('{ ? = call odbc_call() }');
$sth->bind_param_inout(1, \$rv, 4);
$sth->execute();
$sth->finish();
print "rv: $rv\n";
$dbh->disconnect;
1;
__END__


I get an error during execute:

[BEGIN: DBI trace level 1]
    DBI::db=HASH(0x8240a10) trace level set to 1 in DBI 1.35-nothread
    <- prepare('{ ? = call odbc_call() }')= DBI::st=HASH(0x8240f0c) at
odbc_call.pl line 14
    <- bind_param_inout(1 SCALAR(0x812cf10) ...)= 1 at odbc_call.pl line 15
    !! ERROR: -1 '[unixODBC] Msg 170, Level 15, State 1, Server RD_Q, Line 1
Line 1: Incorrect syntax near '0'.
 (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)'
    <- execute= undef at odbc_call.pl line 16
DBD::ODBC::st execute failed: [unixODBC] Msg 170, Level 15, State 1, Server
RD_Q, Line 1
[END: DBI trace level 1]

[BEGIN: hopefully relevant portion of FreeTDS dump file]
Starting log file with debug level 99.
2003-04-15 07:11:11.344391 iconv will convert client-side data to the ""
character set
2003-04-15 07:11:11.344804 Connecting addr 192.168.197.29 port 1433 with TDS
version 7.0
2003-04-15 07:11:11.345677 inside tds_process_login_tokens()

[...]

2003-04-15 07:11:11.355554 processing result tokens.  marker is  fd(DONE)
2003-04-15 07:11:11.355604 inside tds_process_end() more_results = 0,
was_cancelled = 0
2003-04-15 07:11:11.355663 inside tds_process_result_tokens() state is
COMPLETED
Sending packet @ 2003-04-15 07:11:11.356692
0000  01 01 00 36 00 00 01 00  27 00 30 00 27 00 20 00   |...6.... '.0.'. .|
0010  3d 00 20 00 63 00 61 00  6c 00 6c 00 20 00 6f 00   |=. .c.a. l.l. .o.|
0020  64 00 62 00 63 00 5f 00  63 00 61 00 6c 00 6c 00   |d.b.c._. c.a.l.l.|
0030  28 00 29 00 20 00                                  |(.). .|

Received header @ 2003-04-15 07:11:11.357944
0000  04 01 00 6c 00 00 00 00                            |...l....|

Received packet @ 2003-04-15 07:11:11.358016
0000  aa 58 00 aa 00 00 00 01  0f 22 00 4c 00 69 00 6e   |.X...... .".L.i.n|
0010  00 65 00 20 00 31 00 3a  00 20 00 49 00 6e 00 63   |.e. .1.: . .I.n.c|
0020  00 6f 00 72 00 72 00 65  00 63 00 74 00 20 00 73   |.o.r.r.e .c.t. .s|
0030  00 79 00 6e 00 74 00 61  00 78 00 20 00 6e 00 65   |.y.n.t.a .x. .n.e|
0040  00 61 00 72 00 20 00 27  00 30 00 27 00 2e 00 04   |.a.r. .' .0.'....|
0050  52 00 44 00 5f 00 51 00  00 01 00 fd 02 00 fd 00   |R.D._.Q. ........|
0060  00 00 00 00                                        |....|


2003-04-15 07:11:11.358211 processing result tokens.  marker is  aa(ERROR)
2003-04-15 07:11:11.358261 inside tds_process_default_tokens() marker is
aa(ERROR)
2003-04-15 07:11:11.358338 processing result tokens.  marker is  fd(DONE)
2003-04-15 07:11:11.358389 inside tds_process_end() more_results = 0,
was_cancelled = 0
SQLFreeHandle(3, 0x8244150)
SQLFreeHandle(2, 0x8139358)
SQLFreeHandle(1, 0x81b4b10)
[END: hopefully relevant portion of FreeTDS dump file]


2) If I alter the prepared statement to remove all unnecessary whitespace:

  {?=call odbc_call()}

I get a segmentation fault. The segfault goes away if I:
o  place a space between the opening '{' and '?'
   { ?=call odbc_call()}
o  alter the stored proc to return a result set
   create procedure odbc_call as select 1 return 1

[BEGIN: DBI Trace level 1]
    DBI::db=HASH(0x8240a10) trace level set to 1 in DBI 1.35-nothread
    <- prepare('{?=call odbc_call()}')= DBI::st=HASH(0x8240f0c) at
odbc_call.pl line 14
    <- bind_param_inout(1 SCALAR(0x812cf10) ...)= 1 at odbc_call.pl line 15
    <- execute= '0E0' at odbc_call.pl line 16
    <- finish= 1 at odbc_call.pl line 17
Segmentation fault
[END: DBI Trace level 1]

[BEGIN: hopefully relevant portion of FreeTDS dump file]
Starting log file with debug level 99.
2003-04-15 07:00:15.650551 iconv will convert client-side data to the ""
character set
2003-04-15 07:00:15.650961 Connecting addr 192.168.197.29 port 1433 with TDS
version 7.0
2003-04-15 07:00:15.651584 inside tds_process_login_tokens()

[...]

2003-04-15 07:00:15.661354 processing result tokens.  marker is  fd(DONE)
2003-04-15 07:00:15.661405 inside tds_process_end() more_results = 0,
was_cancelled = 0
2003-04-15 07:00:15.661464 inside tds_process_result_tokens() state is
COMPLETED
Sending packet @ 2003-04-15 07:00:15.662462
0000  01 01 00 28 00 00 01 00  65 00 78 00 65 00 63 00   |...(.... e.x.e.c.|
0010  20 00 6f 00 64 00 62 00  63 00 5f 00 63 00 61 00   | .o.d.b. c._.c.a.|
0020  6c 00 6c 00 20 00 20 00                            |l.l. . .|

Received header @ 2003-04-15 07:00:15.664235
0000  04 01 00 1f 00 00 00 00                            |........|

Received packet @ 2003-04-15 07:00:15.664307
0000  ff 11 00 c1 00 01 00 00  00 79 01 00 00 00 fe 00   |........ .y......|
0010  00 e0 00 01 00 00 00                               |.......|

2003-04-15 07:00:15.664384 processing result tokens.  marker is
ff(DONEINPROC)
2003-04-15 07:00:15.664436 inside tds_process_end() more_results = 1,
was_cancelled = 0
2003-04-15 07:00:15.664495 processing result tokens.  marker is
79(RETURNSTATUS)
convert_tds2sql: src is 56 dest = 12
2003-04-15 07:00:15.664597 processing result tokens.  marker is
fe(DONEPROC)
2003-04-15 07:00:15.664649 inside tds_process_end() more_results = 0,
was_cancelled = 0
2003-04-15 07:00:15.664708 inside tds_process_result_tokens() state is
COMPLETED
2003-04-15 07:00:15.664798 inside tds_process_result_tokens() state is
COMPLETED
[END: hopefully relevant portion of FreeTDS dump file]


3) If I then alter the stored procedure to return something:

  create procedure odbc_call as select 1 return 1 

I get no DBI error, however the bound inout parameter $rv does not receive
the return value.

[BEGIN: Hopefully relevant portion of FreeTDS dump file]
2003-04-15 07:14:19.265838 inside tds_process_result_tokens() state is
COMPLETED
Sending packet @ 2003-04-15 07:14:19.266850
0000  01 01 00 28 00 00 01 00  65 00 78 00 65 00 63 00   |...(.... e.x.e.c.|
0010  20 00 6f 00 64 00 62 00  63 00 5f 00 63 00 61 00   | .o.d.b. c._.c.a.|
0020  6c 00 6c 00 20 00 20 00                            |l.l. . .|

Received header @ 2003-04-15 07:14:19.269302
0000  04 01 00 36 00 00 00 00                            |...6....|

Received packet @ 2003-04-15 07:14:19.269375
0000  81 01 00 00 00 00 00 38  00 d1 01 00 00 00 ff 11   |.......8 ........|
0010  00 c1 00 01 00 00 00 ff  11 00 c1 00 01 00 00 00   |........ ........|
0020  79 01 00 00 00 fe 00 00  e0 00 01 00 00 00         |y....... ......|

2003-04-15 07:14:19.269483 processing result tokens.  marker is
81(TDS7_RESULT)
2003-04-15 07:14:19.269539 tds7_get_data_info:1083:
        type = 56 (int)
        column_varint_size = 0
        colname =
        colnamelen = 0
odbc:SQLColAttributes: fDescType is 6
odbc:SQLColAttributes: fDescType is 3
Sending packet @ 2003-04-15 07:14:19.269886
0000  06 01 00 08 00 00 01 00                            |........|

2003-04-15 07:14:19.269978 inside tds_process_default_tokens() marker is
d1(ROW)
2003-04-15 07:14:19.270036 processing row.  column is 0 varint size = 0
2003-04-15 07:14:19.270087 processing row.  column size is 4
2003-04-15 07:14:19.270133 clearing column 0 NULL bit
2003-04-15 07:14:19.270177 inside tds_process_default_tokens() marker is
ff(DONEINPROC)
2003-04-15 07:14:19.270231 inside tds_process_end() more_results = 1,
was_cancelled = 0
2003-04-15 07:14:19.270290 inside tds_process_default_tokens() marker is
ff(DONEINPROC)
2003-04-15 07:14:19.270344 inside tds_process_end() more_results = 1,
was_cancelled = 0
2003-04-15 07:14:19.270402 inside tds_process_default_tokens() marker is
79(RETURNSTATUS)
2003-04-15 07:14:19.270457 inside tds_process_default_tokens() marker is
fe(DONEPROC)
2003-04-15 07:14:19.270510 inside tds_process_end() more_results = 0,
was_cancelled = 0
2003-04-15 07:14:19.270568 inside tds_process_default_tokens() setting state
to COMPLETED
Received header @ 2003-04-15 07:14:19.270643
0000  04 01 00 11 00 00 00 00                            |........|

Received packet @ 2003-04-15 07:14:19.270713
0000  fd 20 00 00 00 00 00 00  00                        |. ...... .|

2003-04-15 07:14:19.270767 inside tds_process_end() more_results = 0,
was_cancelled = 1
SQLFreeHandle(2, 0x8139358)
SQLFreeHandle(1, 0x81b4b10)
[END: Hopefully relevant portion of FreeTDS dump file]

--
Garrett Goebel
IS Development Specialist

ScriptPro                  Direct: 913.403.5261
5828 Reeds Road            Main:   913.384.1008
Mission, KS 66202          Fax:    913.384.2180
www.scriptpro.com          garrett at scriptpro dot com