Skip to Content.
Sympa Menu

freetds - [freetds] ODBC: { ? = CALL myproc() }

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Garrett Goebel <garrett AT scriptpro.com>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: [freetds] ODBC: { ? = CALL myproc() }
  • Date: Tue, 15 Apr 2003 13:45:21 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page