[unixODBC][Driver Manager]Function sequence error (SQL-HY010)
"Bower, Wayne (Wayne)" <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
Found what looks like a bug and I did my best to reduce the code that reproduces to a small test case. The code I reduced it to has no practical use other than to reveal what appears to be the bug and how I am able to work around it. uname -a Linux palltc04 2.6.9-89.0.20.EL.bz501565.11largesmp #1 SMP Tue Mar 23 04:20:50 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux perl v5.14.1 DBI 1.616 DBD::ODBC 1.33 freeTDS 0.91 and unixODBC The 1st attachment (debugio14.pl) is the script that demonstrates the problem and how I'm attempting to work around it. The 2nd attachment (debugio14.log) is from DBI_TRACE=15=debugio14_dbi.log ./debugio14 >debugio14.log 2>&1 I also have debugio14_dbi.log (the DBI_TRACE output) if that helps, but it will exceed the mail message size limit. Essentially the issue appears to be related to using 'SET NOCOUNT ON' in a stored procedure. The script creates a procedure and executes it. The cases that fail issue the message '[unixODBC][Driver Manager]Function sequence error (SQL-HY010)'. To avoid the failure I have to either remove 'SET NOCOUNT ON' or move later in the procedure. Keep in mind that this is just a test case to reveal the issue. In our actual code we always start each procedure with 'SET NOCOUNT ON' and do much more than shown in this test case. We don't see this problem using our old install of perl 5.8.7, DBI 1.616, DBD::ODBC 1.16, freeTDS 0.64 with iodbc. The DBD::ODBC developer already reviewed this and believes the issue to be with freeTDS 0.91 since he doesn't see the problem using both of the following: Easysoft SQL Server ODBC Driver and unixODBC driver manager MS ODBC Driver manager and MS SQL Server ODBC Driver _______________________________________________ FreeTDS mailing list [email protected] http://lists.ibiblio.org/mailman/listinfo/freetds
debugio14.log
(application/octet-stream, 3.8 KB)
0x2a00 DBI::db::get_info 0x10000 DBI::db::take_imp_data 0x10c00 DBI::db::disconnect 0x2000 DBI::db::selectrow_array 0x2200 DBI::db::tables 0x0430 DBI::db::quote_identifier 0x0000 DBI::db::clone 0x0430 DBI::db::quote 0x2200 DBI::db::type_info 0xaa00 DBI::db::statistics_info 0x2000 DBI::db::selectrow_arrayref 0x0400 DBI::db::begin_work 0x2800 DBI::db::last_insert_id 0xaa00 DBI::db::foreign_key_info 0x2200 DBI::db::primary_key 0x0c80 DBI::db::commit 0x0404 DBI::db::ping 0x2000 DBI::db::selectall_arrayref 0x2a00 DBI::db::type_info_all 0x3200 DBI::db::do 0x2000 DBI::db::selectcol_arrayref 0xa200 DBI::db::prepare_cached 0x0004 DBI::db::rows 0x0c80 DBI::db::rollback 0xaa00 DBI::db::column_info 0xaa00 DBI::db::table_info 0xaa00 DBI::db::primary_key_info 0xa200 DBI::db::prepare 0x0000 DBI::db::preparse 0x0004 DBI::db::connected 0x0200 DBI::db::data_sources 0x2000 DBI::db::selectall_hashref 0x2000 DBI::db::selectrow_hashref 0x0000 DBI::dr::default_user 0x0800 DBI::dr::data_sources 0x0004 DBI::dr::dbixs_revision 0x0800 DBI::dr::disconnect_all 0x8000 DBI::dr::connect_cached 0x8000 DBI::dr::connect 0x0000 DBI::st::more_results 0x0000 DBI::st::blob_read 0x0000 DBI::st::fetchall_hashref 0x0000 DBI::st::bind_param_inout_array 0x0000 DBI::st::finish 0x0004 DBI::st::rows 0x5040 DBI::st::execute_for_fetch 0x0000 DBI::st::fetchrow_hashref 0x0000 DBI::st::_get_fbav 0x0000 DBI::st::_set_fbav 0x0000 DBI::st::fetchrow 0x0000 DBI::st::fetch 0x5040 DBI::st::execute_array 0x0000 DBI::st::dump_results 0x0000 DBI::st::bind_param_array 0x0000 DBI::st::fetchrow_array 0x1040 DBI::st::execute 0x0000 DBI::st::bind_col 0x0000 DBI::st::fetchall_arrayref 0x0000 DBI::st::fetchrow_arrayref 0x0000 DBI::st::bind_param_inout 0x0000 DBI::st::bind_columns 0x0800 DBI::st::cancel 0x0000 DBI::st::blob_copy_to_file 0x0000 DBI::st::bind_param 0x0404 DBI::common::parse_trace_flag 0x0004 DBI::common::errstr 0x0004 DBI::common::trace_msg 0x0004 DBI::common::err 0x0004 DBI::common::CLEAR 0x0004 DBI::common::state 0x0000 DBI::common::_not_impl 0x0004 DBI::common::NEXTKEY 0x0004 DBI::common::trace 0x0004 DBI::common::debug 0x0404 DBI::common::parse_trace_flags 0x0000 DBI::common::private_attribute_info 0x0000 DBI::common::swap_inner_handle 0x0004 DBI::common::EXISTS 0x0404 DBI::common::visit_child_handles 0x0404 DBI::common::FETCH 0x0004 DBI::common::FIRSTKEY 0x0010 DBI::common::set_err 0x10004 DBI::common::DESTROY 0x0004 DBI::common::dump_handle 0x0404 DBI::common::FETCH_many 0x0100 DBI::common::can 0x041c DBI::common::STORE 0x0004 DBI::common::private_data 0x0006 DBI::common::func Create TMP_SP_Test_ODBC with input and no output exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output exec_sp_io TMP_SP_Test_ODBC Error: exec_sp_io TMP_SP_Test_ODBC failed ERROR_CODE=1 ERROR_STRING=[unixODBC][Driver Manager]Function sequence error (SQL-HY010) TMP_SP_Test_ODBC returned: rv=<> o=<> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output without SET NOCOUNT ON exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Selected values: I 10 Output values: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Error: exec_sp_io TMP_SP_Test_ODBC failed ERROR_CODE=1 ERROR_STRING=[unixODBC][Driver Manager]Function sequence error (SQL-HY010) Output values: rv=<> o=<> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, later SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Output values: rv=<5> o=<55> Drop TMP_SP_Test_ODBC
debugio14.pl
(application/octet-stream, 7 KB)
#!/tools/cfr/perl/5.14.1/bin/perl
use strict;
use DBI;
select STDERR; $| = 1;
select STDOUT; $| = 1;
our ($ERROR_CODE, $ERROR_STRING, $WARNING_STRING);
my $db = 'Test_Perl_DBI';
my $host = 'pausql02.lsi.com\\dev';
my $driv = '/tools/cfr/tdsodbc/0.91/lib/libtdsodbc.so';
my $src = "DBI:ODBC:Database=$db;Server=$host;Driver=$driv;TDS_Version=7.2";
my $user = 'test_perl_adm';
my $pass = 'Tp3rl%DB!@';
my $dbh;
for (my $i = 0 ; $i < 3 ; $i++) {
$dbh =
DBI->connect ($src, $user, $pass,
{
PrintError => 0,
RaiseError => 0,
AutoCommit => 0,
}
) and last;
warn "Attempt #$i failed to connect - $DBI::errstr\n";
sleep 1;
}
if ($dbh) {
$dbh->{RaiseError} = 1;
}
else {
exit 1;
}
sub exec_sp_io {
my ($dbh, $sp_spec, $iparams, $oparams) = @_;
my $sth;
undef $ERROR_CODE;
undef $ERROR_STRING;
undef $WARNING_STRING;
if ($sth = $dbh->prepare ("{ $sp_spec }")) {
if (defined $iparams) {
while (my ($n, $p) = each (%$iparams)) {
$sth->bind_param ($n, $p);
}
}
if (defined $oparams) {
while (my ($n, $p) = each (%$oparams)) {
$sth->bind_param_inout ($n, @$p);
}
}
eval { $sth->execute; };
if ($@) {
$ERROR_CODE = $sth->err;
$ERROR_STRING = $sth->errstr;
}
else {
$WARNING_STRING = $sth->errstr;
}
}
else {
$ERROR_CODE = $DBI::err;
$ERROR_STRING = "Can't prepare statement '{ $sp_spec }'\n"
. $DBI::errstr;
}
return if ($ERROR_CODE);
return $sth;
} # exec_sp_io()
my ($sth, $rv, $o);
my $proc = 'TMP_SP_Test_ODBC';
print "\nCreate $proc with input and no output\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT
AS
SET NOCOUNT ON
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
exec_sp_io (
$dbh,
":RV = call $proc (:I)",
{
I => 10,
},
{
RV => [ \$rv, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "$proc returned:\n";
print "rv=<$rv>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
print "\nCreate $proc with input and output\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT,
@O INT OUTPUT
AS
SET NOCOUNT ON
SELECT @O = 55
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
exec_sp_io (
$dbh,
":RV = call $proc (:I, :O)",
{
I => 10,
},
{
RV => [ \$rv, 4 ],
O => [ \$o, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "$proc returned:\n";
print "rv=<$rv>\n";
print "o=<$o>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
print "\nCreate $proc with input and output without SET NOCOUNT ON\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT,
@O INT OUTPUT
AS
SELECT @O = 55
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
exec_sp_io (
$dbh,
":RV = call $proc (:I, :O)",
{
I => 10,
},
{
RV => [ \$rv, 4 ],
O => [ \$o, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "$proc returned:\n";
print "rv=<$rv>\n";
print "o=<$o>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
print "\nCreate $proc with input, output, select, init SET NOCOUNT\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT,
@O INT OUTPUT
AS
SET NOCOUNT ON
SET @O = 55
IF @I = 0
RETURN 5
SELECT ' . "'I'" . ', CAST (@I AS VARCHAR)
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
$sth = exec_sp_io (
$dbh,
":RV = call $proc (:I, :O)",
{
I => 10,
},
{
RV => [ \$rv, 4 ],
O => [ \$o, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "Selected values:\n";
while (my @a = $sth->fetchrow_array) {
print "@a\n";
}
print "Output values:\n";
print "rv=<$rv>\n";
print "o=<$o>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
print "\nCreate $proc with input, output, select disabled, init SET NOCOUNT\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT,
@O INT OUTPUT
AS
SET NOCOUNT ON
SET @O = 55
IF @I = 0
RETURN 5
SELECT ' . "'I'" . ', CAST (@I AS VARCHAR)
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
exec_sp_io (
$dbh,
":RV = call $proc (:I, :O)",
{
I => 0,
},
{
RV => [ \$rv, 4 ],
O => [ \$o, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "Output values:\n";
print "rv=<$rv>\n";
print "o=<$o>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
print "\nCreate $proc with input, output, select disabled, later SET NOCOUNT\n";
eval {
$dbh->do (
"CREATE PROCEDURE [$proc]" . '
@I INT,
@O INT OUTPUT
AS
SET @O = 55
IF @I = 0
RETURN 5
SET NOCOUNT ON
SELECT ' . "'I'" . ', CAST (@I AS VARCHAR)
RETURN 9
'
)
};
if ($@ || $dbh->err) {
die "Failed to create $proc\n",
"$@\n",
"$DBI::errstr\n";
}
print "exec_sp_io $proc\n";
($rv, $o) = ();
exec_sp_io (
$dbh,
":RV = call $proc (:I, :O)",
{
I => 0,
},
{
RV => [ \$rv, 4 ],
O => [ \$o, 4 ],
}
)
or
warn "Error: exec_sp_io $proc failed\n",
"ERROR_CODE=$ERROR_CODE\n",
"ERROR_STRING=$ERROR_STRING\n";
$WARNING_STRING and warn "WARNING_STRING=$WARNING_STRING\n";
print "Output values:\n";
print "rv=<$rv>\n";
print "o=<$o>\n";
print "Drop $proc\n";
eval { $dbh->do ("DROP PROCEDURE $proc") };
eval { $dbh->commit; };
if ($@) {
die "Commit failed - $DBI::errstr\n";
exit 1;
}
$dbh->disconnect;