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

Garrett Goebel garrett at scriptpro.com
Wed Apr 23 11:55:49 EDT 2003


Frediano Ziglio wrote:
> Garrett Goebel wrote: 
> > Frediano Ziglio wrote:
> > > Garrett Goebel wrote: 
> > > > 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
> > > >
> > > ...
> > > 
> > > I'll look deeper..
> > 
> > I should have been more specific. The segfault goes away if 
> > I do either of the two things I mentioned. I.e., that's an
> > OR not an AND. So the reproduction case for the segfault is
> > to use the odbc call escape sequence on a stored procedure
> > that returns no result set.
> 
> Garrett, I'm not able to reproduce the core...
> Can you enable trace in ODBC and send me the log?

Sorry for the delayed response. The ODBC trace is down below...

I found another nugget of info.

I ran the same perl script on a WinNT box using same versions of Perl, DBI,
and DBD::ODBC but with native MDAC ODBC and SQL Server driver. I did not
encounter the segmentation fault, but my test case still failed. So, I may
have uncovered a bug in DBD::ODBC as well... though the segfault remains an
issue in the linux/unixODBC/FreeTDS environment.

DBD::ODBC has a test case of odbc call syntax which calls a procedure taking
1 integer input param  and returning output. If that test case is modified
not to take an input var... it produces the same error as I show below.
I'll take this bug over to the DBI mailing list and Jeff Urlwin to see what
they make of it. 

If I modify my stored procedure to match what works under DBD::ODBC (add an
input param to the stored proc), I finally have a success under WinNT w/
native ODBC and driver. However, I still get the same segfault under
linux/unixODBC/FreeTDS. Perhaps the differing error details under WinNT, etc
will assist in isolating the segfault...


In the interest of completeness...

[- Environment -]
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

[Stored procedure]
CREATE procedure odbc_call as return 1

[- Perl Script - odbc_call.pl -]
#!/usr/bin/perl -w

use DBI;

my $rv  = 0;
my $dbh = DBI->connect(
 
'dbi:ODBC:ServerName=RD_Q;Driver=TDS;Database=SPCentral;TDS_Version=7.0',
    '???',
    '???',
    {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 "return value: $rv\n";

$dbh->disconnect;

1;
__END__

[- STDOUT/STDERR -]
# ./odbc_call.pl
    DBI::db=HASH(0x8240a30) trace level set to 1 in DBI 1.35-nothread
    <- prepare('{?=call odbc_call()}')= DBI::st=HASH(0x8240f2c) 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


[- STDOUT/STDERR under WinNT 4.0sp6a and MDAC ODBC/driver w/ DSN-only change
-]
    DBI::db=HASH(0x1e9fe24) trace level set to 1 in DBI 1.35-ithread
    <- prepare('{?=call odbc_call()}')= DBI::st=HASH(0x1e9ff14) at
odbc_call.pl
line 14
    <- bind_param_inout(1 SCALAR(0x1bd24e0) ...)= 1 at odbc_call.pl line 15
    !! ERROR: -1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
odbc_
call has no parameters and arguments were supplied. (SQL-42000)(DBD:
st_execute/
SQLExecute err=-1)'
    <- execute= undef at odbc_call.pl line 16
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Pro
cedure odbc_call has no parameters and arguments were supplied.
(SQL-42000)(DBD:
 st_execute/SQLExecute err=-1) at C:\TEMP\odbc_call.pl line 16.
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Pro
cedure odbc_call has no parameters and arguments were supplied.
(SQL-42000)(DBD:
 st_execute/SQLExecute err=-1) at C:\TEMP\odbc_call.pl line 16.
       error: -1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
odbc_
call has no parameters and arguments were supplied. (SQL-42000)(DBD:
st_execute/
SQLExecute err=-1)'
    <- DESTROY= undef
       error: -1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
odbc_
call has no parameters and arguments were supplied. (SQL-42000)(DBD:
st_execute/
SQLExecute err=-1)'
    <- DESTROY= undef

[- ODBC trace -]
[ODBC][27027][__handles.c][345]
		Exit:[SQL_SUCCESS]
			Environment = 0x8241240
[ODBC][27027][SQLSetEnvAttr.c][142]
		Entry:            
			Environment = 0x8241240            
			Attribute = SQL_ATTR_ODBC_VERSION            
			Value = 0x3            
			StrLen = 0xfffffffa
[ODBC][27027][SQLSetEnvAttr.c][286]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLAllocHandle.c][284]
		Entry:
			Handle Type = 2
			Input Handle = 0x8241240
[ODBC][27027][SQLAllocHandle.c][373]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x82416c8
[ODBC][27027][SQLDriverConnect.c][470]
		Entry:            
			Connection = 0x82416c8            
			Window Hdl = (nil)            
			Str In =
[ServerName=RD_Q;Driver=TDS;Database=SPCentral;TDS_Version=7.0;UI...][length
= 82]            
			Str Out = 0xbfffe9d0            
			Str Out Max = 2048            
			Str Out Ptr = 0xbfffe9ce            
			Completion = 0
[ODBC][27027][SQLDriverConnect.c][1108]
		Exit:[SQL_SUCCESS]                    
			Connection Out [[
£
@%][length = 5 (SQL_NTS)]]
[ODBC][27027][SQLSetConnectOption.c][195]
		Entry:            
			Connection = 0x82416c8            
			Option = SQL_ATTR_AUTOCOMMIT            
			Value = 1
[ODBC][27027][SQLSetConnectOption.c][442]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLGetInfo.c][157]
		Entry:            
			Connection = 0x82416c8            
			Info Type = SQL_DRIVER_ODBC_VER            
			Info Value = 0x823ef8c            
			Buffer Length = 20            
			StrLen = 0xbffff3de
[ODBC][27027][SQLGetInfo.c][428]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLGetInfo.c][157]
		Entry:            
			Connection = 0x82416c8            
			Info Type = SQL_DBMS_NAME            
			Info Value = 0x823efa0            
			Buffer Length = 64            
			StrLen = 0xbffff3de
[ODBC][27027][SQLGetInfo.c][428]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLGetFunctions.c][121]
		Entry:            
			Connection = 0x82416c8            
			Id = SQLMoreResults            
			Supported = 0xbffff3dc
[ODBC][27027][SQLGetFunctions.c][158]
		Exit:[SQL_SUCCESS]                
			Supported = 0xbffff3dc -> 1
[ODBC][27027][SQLGetFunctions.c][121]
		Entry:            
			Connection = 0x82416c8            
			Id = SQLDescribeParam            
			Supported = 0xbffff3dc
[ODBC][27027][SQLGetFunctions.c][158]
		Exit:[SQL_SUCCESS]                
			Supported = 0xbffff3dc -> 0
[ODBC][27027][SQLSetConnectOption.c][195]
		Entry:            
			Connection = 0x82416c8            
			Option = SQL_ATTR_AUTOCOMMIT            
			Value = 1
[ODBC][27027][SQLSetConnectOption.c][442]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLAllocHandle.c][418]
		Entry:
			Handle Type = 3
			Input Handle = 0x82416c8
[ODBC][27027][SQLAllocHandle.c][775]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x8250d10
[ODBC][27027][SQLPrepare.c][153]
		Entry:            
			Statement = 0x8250d10            
			SQL = [{?=call odbc_call()}][length = 20]
[ODBC][27027][SQLPrepare.c][354]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLBindParameter.c][167]
		Entry:            
			Statement = 0x8250d10            
			Param Number = 1            
			Param Type = 2            
			C Type = 1             
			SQL Type = 12             
			Col Def = 80            
			Scale = 0            
			Rgb Value = 0x8244270            
			Value Max = 27            
			StrLen Or Ind = 0x8244210
[ODBC][27027][SQLBindParameter.c][320]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLExecute.c][145]
		Entry:            
			Statement = 0x8250d10
[ODBC][27027][SQLExecute.c][296]
		Exit:[SQL_SUCCESS]
[ODBC][27027][SQLRowCount.c][122]
		Entry:            
			Statement = 0x8250d10            
			Row Count = 0x8244154
[ODBC][27027][SQLRowCount.c][188]
		Exit:[SQL_SUCCESS]                
			Row Count = 0x8244154 -> 0
[ODBC][27027][SQLNumResultCols.c][129]
		Entry:            
			Statement = 0x8250d10            
			Column Count = 0xbffff56e
[ODBC][27027][SQLNumResultCols.c][220]
		Exit:[SQL_SUCCESS]                
			Count = 0xbffff56e -> 0
[ODBC][27027][SQLMoreResults.c][127]
		Entry:            
			Statement = 0x8250d10
[ODBC][27027][SQLMoreResults.c][277]
		Exit:[SQL_NO_DATA]


[FreeTDS Trace]
Starting log file with debug level 99.
2003-04-23 08:42:42.909186 iconv will convert client-side data to the ""
character set
2003-04-23 08:42:42.909608 Connecting addr 192.168.197.29 port 1433 with TDS
version 7.0
2003-04-23 08:42:42.910491 inside tds_process_login_tokens()
Received header @ 2003-04-23 08:42:42.913697
0000  04 01 01 9c 00 00 00 00                            |........|


Received packet @ 2003-04-23 08:42:42.913794
0000  e3 21 00 01 09 53 00 50  00 43 00 65 00 6e 00 74   |.!...S.P .C.e.n.t|
0010  00 72 00 61 00 6c 00 06  6d 00 61 00 73 00 74 00   |.r.a.l.. m.a.s.t.|
0020  65 00 72 00 ab 64 00 45  16 00 00 02 00 28 00 43   |e.r..d.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 53 00 50 00 43 00 65   |.t.o. .' .S.P.C.e|
0070  00 6e 00 74 00 72 00 61  00 6c 00 27 00 2e 00 04   |.n.t.r.a .l.'....|
0080  52 00 44 00 5f 00 51 00  00 00 00 e3 17 00 02 0a   |R.D._.Q. ........|
0090  75 00 73 00 5f 00 65 00  6e 00 67 00 6c 00 69 00   |u.s._.e. n.g.l.i.|
00a0  73 00 68 00 00 ab 62 00  47 16 00 00 01 00 27 00   |s.h...b. G.....'.|
00b0  43 00 68 00 61 00 6e 00  67 00 65 00 64 00 20 00   |C.h.a.n. g.e.d. .|
00c0  6c 00 61 00 6e 00 67 00  75 00 61 00 67 00 65 00   |l.a.n.g. u.a.g.e.|
00d0  20 00 73 00 65 00 74 00  74 00 69 00 6e 00 67 00   | .s.e.t. t.i.n.g.|
00e0  20 00 74 00 6f 00 20 00  75 00 73 00 5f 00 65 00   | .t.o. . u.s._.e.|
00f0  6e 00 67 00 6c 00 69 00  73 00 68 00 2e 00 04 52   |n.g.l.i. s.h....R|
0100  00 44 00 5f 00 51 00 00  00 00 e3 0f 00 03 05 69   |.D._.Q.. .......i|
0110  00 73 00 6f 00 5f 00 31  00 01 00 00 e3 0b 00 05   |.s.o._.1 ........|
0120  04 31 00 30 00 33 00 33  00 00 e3 0f 00 06 06 31   |.1.0.3.3 .......1|
0130  00 39 00 36 00 36 00 30  00 39 00 00 ad 36 00 01   |.9.6.6.0 .9...6..|
0140  07 00 00 00 16 4d 00 69  00 63 00 72 00 6f 00 73   |.....M.i .c.r.o.s|
0150  00 6f 00 66 00 74 00 20  00 53 00 51 00 4c 00 20   |.o.f.t.  .S.Q.L. |
0160  00 53 00 65 00 72 00 76  00 65 00 72 00 00 00 00   |.S.e.r.v .e.r....|
0170  00 07 00 03 4a e3 13 00  04 04 34 00 30 00 39 00   |....J... ..4.0.9.|
0180  36 00 04 34 00 30 00 39  00 36 00 fd 00 00 00 00   |6..4.0.9 .6......|
0190  00 00 00 00                                        |....|


2003-04-23 08:42:42.914460 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.914558 inside tds_process_default_tokens() marker is
ab(INFO)
2003-04-23 08:42:42.914658 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.914719 inside tds_process_default_tokens() marker is
ab(INFO)
2003-04-23 08:42:42.914804 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.914866 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.914925 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.914987 inside tds_process_default_tokens() marker is
e3(ENVCHANGE)
2003-04-23 08:42:42.915048 increasing block size from 4096 to 4096
2003-04-23 08:42:42.915114 inside tds_process_default_tokens() marker is
fd(DONE)
2003-04-23 08:42:42.915175 inside tds_process_end() more_results = 0,
was_cancelled = 0 
2003-04-23 08:42:42.915234 inside tds_process_default_tokens() setting state
to COMPLETED
Sending packet @ 2003-04-23 08:42:42.915332
0000  01 01 00 22 00 00 01 00  73 00 65 00 6c 00 65 00   |...".... s.e.l.e.|
0010  63 00 74 00 20 00 40 00  40 00 73 00 70 00 69 00   |c.t. . at . @.s.p.i.|
0020  64 00                                              |d.|


Received header @ 2003-04-23 08:42:42.916012
0000  04 01 00 1d 00 00 00 00                            |........|


Received packet @ 2003-04-23 08:42:42.916087
0000  81 01 00 00 00 00 00 34  00 d1 14 00 fd 10 00 c1   |.......4 ........|
0010  00 01 00 00 00                                     |.....|


2003-04-23 08:42:42.916163 processing result tokens.  marker is
81(TDS7_RESULT)
2003-04-23 08:42:42.916238 tds7_get_data_info:1083: 
	type = 52 (smallint)
	column_varint_size = 0
	colname = 
	colnamelen = 0
2003-04-23 08:42:42.916363 processing row tokens.  marker is  d1(ROW)
2003-04-23 08:42:42.916415 processing row.  column is 0 varint size = 0
2003-04-23 08:42:42.916482 processing row.  column size is 2 
2003-04-23 08:42:42.916532 clearing column 0 NULL bit
2003-04-23 08:42:42.916577 processing row tokens.  marker is  fd(DONE)
2003-04-23 08:42:42.916626 inside tds_process_end() more_results = 0,
was_cancelled = 0 
2003-04-23 08:42:42.916685 inside tds_process_result_tokens() state is
COMPLETED
2003-04-23 08:42:42.916738 leaving tds_process_login_tokens() returning 1
SQLGetFunctions: fFunction is 1
SQLGetFunctions: fFunction is 2
SQLGetFunctions: fFunction is 1001
SQLGetFunctions: fFunction is 3
SQLGetFunctions: fFunction is 4
SQLGetFunctions: fFunction is 72
SQLGetFunctions: fFunction is 5
SQLGetFunctions: fFunction is 6
SQLGetFunctions: fFunction is 40
SQLGetFunctions: fFunction is 7
SQLGetFunctions: fFunction is 8
SQLGetFunctions: fFunction is 9
SQLGetFunctions: fFunction is 41
SQLGetFunctions: fFunction is 1005
SQLGetFunctions: fFunction is 10
SQLGetFunctions: fFunction is 11
SQLGetFunctions: fFunction is 12
SQLGetFunctions: fFunction is 13
SQLGetFunctions: fFunction is 15
SQLGetFunctions: fFunction is 1006
SQLGetFunctions: fFunction is 16
SQLGetFunctions: fFunction is 14
SQLGetFunctions: fFunction is 42
SQLGetFunctions: fFunction is 43
SQLGetFunctions: fFunction is 1010
SQLGetFunctions: fFunction is 44
SQLGetFunctions: fFunction is 45
SQLGetFunctions: fFunction is 1014
SQLGetFunctions: fFunction is 46
SQLGetFunctions: fFunction is 47
SQLGetFunctions: fFunction is 61
SQLGetFunctions: fFunction is 63
SQLGetFunctions: fFunction is 18
SQLGetFunctions: fFunction is 48
SQLGetFunctions: fFunction is 19
SQLGetFunctions: fFunction is 49
SQLGetFunctions: fFunction is 20
SQLGetFunctions: fFunction is 1016
SQLGetFunctions: fFunction is 50
SQLGetFunctions: fFunction is 1019
SQLGetFunctions: fFunction is 51
SQLGetFunctions: fFunction is 54
SQLGetFunctions: fFunction is 23
SQLGetFunctions: fFunction is 1011
change_autocommit: executing set implicit_transactions off
Sending packet @ 2003-04-23 08:42:42.918368
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 @ 2003-04-23 08:42:42.919151
0000  04 01 00 11 00 00 00 00                            |........|


Received packet @ 2003-04-23 08:42:42.919227
0000  fd 00 00 ba 00 00 00 00  00                        |........ .|


2003-04-23 08:42:42.919281 processing result tokens.  marker is  fd(DONE)
2003-04-23 08:42:42.919332 inside tds_process_end() more_results = 0,
was_cancelled = 0 
2003-04-23 08:42:42.919390 inside tds_process_result_tokens() state is
COMPLETED
change_autocommit: executing set implicit_transactions off
Sending packet @ 2003-04-23 08:42:42.920575
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 @ 2003-04-23 08:42:42.921499
0000  04 01 00 11 00 00 00 00                            |........|


Received packet @ 2003-04-23 08:42:42.921574
0000  fd 00 00 ba 00 00 00 00  00                        |........ .|


2003-04-23 08:42:42.921628 processing result tokens.  marker is  fd(DONE)
2003-04-23 08:42:42.921678 inside tds_process_end() more_results = 0,
was_cancelled = 0 
2003-04-23 08:42:42.921736 inside tds_process_result_tokens() state is
COMPLETED
Sending packet @ 2003-04-23 08:42:42.923786
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-23 08:42:42.924628
0000  04 01 00 1f 00 00 00 00                            |........|


Received packet @ 2003-04-23 08:42:42.924704
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-23 08:42:42.924780 processing result tokens.  marker is
ff(DONEINPROC)
2003-04-23 08:42:42.924831 inside tds_process_end() more_results = 1,
was_cancelled = 0 
2003-04-23 08:42:42.924890 processing result tokens.  marker is
79(RETURNSTATUS)
convert_tds2sql: src is 56 dest = 12
2003-04-23 08:42:42.924992 processing result tokens.  marker is
fe(DONEPROC)
2003-04-23 08:42:42.925043 inside tds_process_end() more_results = 0,
was_cancelled = 0 
2003-04-23 08:42:42.925101 inside tds_process_result_tokens() state is
COMPLETED
2003-04-23 08:42:42.925673 inside tds_process_result_tokens() state is
COMPLETED

--
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.com
 



More information about the FreeTDS mailing list