Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

My MSSQL query fails the second time.

by kyle (Abbot)
on Apr 30, 2013 at 17:44 UTC ( #1031438=perlquestion: print w/ replies, xml ) Need Help??
kyle has asked for the wisdom of the Perl Monks concerning the following question:

Hello most gracious monks,

I was happily writing a Catalyst app with DBIx::Class when I tripped over some database problem. I haven't been able to pin it down, but I'm pretty sure it has to do with DBD::Sybase used with our MSSQL server.

My original problem is I tried to call ->first on a DBIx::Class::ResultSet twice. That produced the error "DBIx::Class::ResultSet::first(): Unknown error: execute() returned false, but error flags were not set...", which you can find in DBIx::Class::Storage::DBI in sub _dbh_execute.

# Can this fail without throwing an exception anyways??? my $rv = $sth->execute(); $self->throw_exception( $sth->errstr || $sth->err || 'Unknown error: execute() returned fa +lse, but error flags were not set...' ) if !$rv;

I have a minimal test using only DBI that demonstrates the problem.

use 5.014; use strict; use warnings; use DBI; use Test::More; my @connect_info = ( "dbi:Sybase:server=xxx;database=xxx", 'xxx', ':)', { RaiseError => 1 }, ); # @connect_info = ("dbi:SQLite2:dbname=dbfile","",""); my $dbh = DBI->connect( @connect_info ); isa_ok $dbh, 'DBI::db'; my $sql = 'SELECT 1'; test_query( $sql ); test_query( $sql ); sub test_query { my ($sql) = @_; my $sth = $dbh->prepare_cached( $sql ); isa_ok $sth, 'DBI::st'; my $r = $sth->execute(); { no warnings 'uninitialized'; ok defined( $r ), "defined: '$r'"; } ok $sth->finish(), '$sth->finish'; return; } done_testing;

I've obscured the actual connect info. This is what the output looks like:

ok 1 - The object isa DBI::db ok 2 - The object isa DBI::st ok 3 - defined: '-1' ok 4 - $sth->finish ok 5 - The object isa DBI::st not ok 6 - defined: '' # Failed test 'defined: ''' # at t/dbi-bug-pm.t line 35. ok 7 - $sth->finish 1..7 # Looks like you failed 1 test of 7.

Note that if you uncomment the SQLite connect info, it works fine. That's why I think this is a problem with my database or driver. You can see a run with SQLite below.

ok 1 - The object isa DBI::db ok 2 - The object isa DBI::st ok 3 - defined: '0E0' ok 4 - $sth->finish ok 5 - The object isa DBI::st ok 6 - defined: '0E0' ok 7 - $sth->finish 1..7

Note also that if I disconnect from the database and reconnect between the queries, they work, but I don't think disconnecting from the database after every query is a very good workaround.

I'd be happy with a workaround for this issue. If I could stick some code in every ResultSet so that it avoids the problem until it's fixed elsewhere, that would be fine with me, but I don't even understand what's going wrong. I'm asking here to see if someone is familiar with it and what it means.

My searching for the issue online turned up a thread from about a year ago (http://lists.scsys.co.uk/pipermail/dbix-class/2012-June/010553.html). If there's an answer in there, I failed to comprehend it.

Any help would be appreciated. Thank you.

Comment on My MSSQL query fails the second time.
Select or Download Code
Re: My MSSQL query fails the second time.
by moritz (Cardinal) on Apr 30, 2013 at 17:52 UTC

      Here's some delicious DBI_TRACE=3

      ok 2 - The object isa DBI::st -> execute for DBD::Sybase::st (DBI::st=HASH(0x8bc9234)~0x8bc9284) + thr#897b008 syb_alloc_cmd() -> CS_COMMAND 8bcb778 for CS_CONNECTION 8b85e60 cmd_execute() -> ct_command() OK cmd_execute() -> ct_send() OK cmd_execute() -> set inUse flag st_next_result() -> ct_results(4040) == 1 ct_res_info() returns 1 columns STORE DBI::st=HASH(0x8bc9284) 'NUM_OF_FIELDS' => 1 ct_describe(0): type = 8, maxlen = 4 describe() -> col 0, type 8, realtype 8 describe() retcode = 1 st_next_result() -> lasterr = 0, lastsev = 0 <- execute= ( -1 ) [1 items] at t/dbi-bug-pm.t line 36 via at t/d +bi-bug-pm.t line 23 ok 3 - defined: '-1' -> finish for DBD::Sybase::st (DBI::st=HASH(0x8bc9234)~0x8bc9284) +thr#897b008 syb_st_finish() -> ct_cancel(CS_CANCEL_ALL) clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, ex +ec_done clear_sth_flags() -> reset inUse flag <- finish= ( 1 ) [1 items] at t/dbi-bug-pm.t line 42 via at t/dbi +-bug-pm.t line 23 ok 4 - $sth->finish -> prepare_cached in DBD::_::db for DBD::Sybase::db (DBI::db=HASH( +0x8b98148)~0x8b98094 'SELECT 1') thr#897b008 -> FETCH for DBD::Sybase::st (DBI::st=HASH(0x8bc9234)~0x8bc9284 'A +ctive') thr#897b008 .. FETCH DBI::st=HASH(0x8bc9284) 'Active' = '' <- FETCH= ( '' ) [1 items] at /usr/lib/perl5/DBI.pm line 1699 via + at t/dbi-bug-pm.t line 33 <- prepare_cached= ( DBI::st=HASH(0x8bc9234) ) [1 items] at t/dbi- +bug-pm.t line 33 via at t/dbi-bug-pm.t line 28 -> execute for DBD::Sybase::st (DBI::st=HASH(0x8bc9234)~0x8bc9284) + thr#897b008 cmd_execute() -> ct_command() OK cmd_execute() -> ct_send() failed <- execute= ( undef ) [1 items] at t/dbi-bug-pm.t line 36 via at +t/dbi-bug-pm.t line 28 not ok 6 - defined: '' # Failed test 'defined: ''' # at t/dbi-bug-pm.t line 39. -> finish for DBD::Sybase::st (DBI::st=HASH(0x8bc9234)~0x8bc9284) +thr#897b008 <- finish= ( 1 ) [1 items] at t/dbi-bug-pm.t line 42 via at t/dbi +-bug-pm.t line 28 ok 7 - $sth->finish

      What stands out to me is cmd_execute() -> ct_send() failed, so I Googled ct_send. It says, among other things, that it fails if there are results pending. That sounds like my problem, but $sth->finish seems too simple to fail.

      Anyway, that was fun, but I'm still stumped.

        I ran the test against Sybase with the Sybase client libs, and this appeared to work as expected - so I would assume that you've hit some sort of bug in the FreeTDS client libs.

        Michael

Re: My MSSQL query fails the second time.
by karlgoethebier (Curate) on Apr 30, 2013 at 19:25 UTC

    Mmh, the only idea i have about this is: try to contact Michael Peppler, the author.

    He is very cooperative and helped me very much some years ago with some issues about building DBD::Sybase.

    Btw, naive question: if MSSQL, why not ODBC?

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

      Once I got ODBC working, that fixed it. Thanks!

      Edited to add the DSN I'm using (dbi:ODBC:DRIVER=FreeTDS;Server=xxx;Port=1433;DATABASE=xxx) and the output of the test using that connection.

      ok 1 - The object isa DBI::db ok 2 - The object isa DBI::st ok 3 - defined: '-1' ok 4 - $sth->finish ok 5 - The object isa DBI::st ok 6 - defined: '-1' ok 7 - $sth->finish 1..7
        I was receiving a very similar error message. In my case, it was because I had a FOREIGN KEY that was not satisfied (i.e. I was trying to add a child record for which I had no parent). Check your table structure, and be sure your record is complete.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1031438]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (11)
As of 2014-11-20 21:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (102 votes), past polls