sth->execute returns undef when successful

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: sth->execute returns undef when successful by Corion (Patriarch) on Aug 08, 2015 at 19:58 UTC
On larger longer running queries (about 40 min) is when it gets the return value of undf I have no idea if this is the case in your situation, but maybe the network connection gets cancelled by some appliance (load balancer or firewall) between your client and your server. A network sniffer like wireshark on the client side or the server log could maybe tell you for the long-running queries if there was an unexpected disconnection while the query was executing. One approach for a workaround could be to partition up your statements into smaller statements that execute quicker, for example by looping over limited clauses like `where id between 0 and 1000000` etc., but that way you might lose transaction safety. Maybe the Oracle client library has a way to send keep-alive packets during query execution so that the network path remains open?	[reply] [d/l]
Re^2: sth->execute returns undef when successful by BrowserUk (Patriarch) on Aug 08, 2015 at 21:09 UTC
That crossed my mind too; but it looks like the query is returning successfully internally, but then the driver is returning false: `dbd_st_execute CREATE returned (SUCCESS, rpc2211750915, fn1, +out0) 09 <- execute= undef at ./test_query.pl line 80` [download] Another thought that crossed my mind was that if the statement didn't modify any lines, and the driver was returning 0 instead of "0E0", that would appear false even though the statement had succeeded. With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :) In the absence of evidence, opinion is indistinguishable from prejudice. I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!	[reply] [d/l]
Re: sth->execute returns undef when successful by 1nickt (Canon) on Aug 08, 2015 at 16:43 UTC
Disclaimer: I don't use Oracle. Just throwing out a couple of questions since you haven't received an answer yet. It looks like you are using threads. Could that be the reason why there are problems with long queries and not with simple ones? From the DBI docs: "Using DBI with perl threads is not yet recommended for production environments. " Is your statement really `CREATE (out0, lob0) ...`? Don't you need to say `CREATE TABLE tbl_name (col_name ... )`? Doesn't seem like it would make a difference here, but why not use `RaiseError` ? Hope this helps a little. The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re^2: sth->execute returns undef when successful by BrowserUk (Patriarch) on Aug 08, 2015 at 18:10 UTC
"Using DBI with perl threads is not yet recommended for production environments. " Do you have any first hand experience of use DBI with threads? Because citing decade old scaremongering quotes from docs that never get updated -- when there are thousands of people using DBI in conjunction with threads -- isn't useful. Could that be the reason why there are problems with long queries and not with simple ones? Care to explain the logic behind that piece of deduction? It looks like you are using threads The fact that the version of perl (and thus the version of DBI) was built with threading enables, does not mean "he's using threads". Neither do the presence of thread handles in the traces. That simply means that he could (safely) use threads if his application required it. If you look closely at those traced thread handles, you'll see they are all the same! So, at least the bit traced, is single threaded. Bottom line: speculations on subjects you appear to know little about and have personal bias towards doesn't help anyone. With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :) In the absence of evidence, opinion is indistinguishable from prejudice. I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!	[reply] [d/l]
Re^3: sth->execute returns undef when successful by 1nickt (Canon) on Aug 08, 2015 at 19:29 UTC
Ouch. No personal bias, I am not one of those anti-thread boogeymen, and I tried to preface my comment appropriately, asking questions, not making statements. What do you think the problem is? The way forward always starts with a minimal test.	[reply] [d/l]
Re^4: sth->execute returns undef when successful by BrowserUk (Patriarch) on Aug 08, 2015 at 19:50 UTC
Re: sth->execute returns undef when successful by pfay (Initiate) on Aug 12, 2015 at 16:59 UTC
Still have no clue why it returns a undef after a successful table creation, but to get around it I have set the RaiseError => 1 and put an eval around the prepare and execute. This seems to be working and still capturing any errors that might occur. `# Prepare the SQL query eval { $sth = $dbh->prepare($$sql); }; if ($@) { confess("ERROR:$0: Error in SQL prepare \n $@ "); } # Execute the SQL Query eval { $sth->execute(); }; if ($@) { $dbh->rollback(); confess("ERROR:$0: Error in SQL execute \n $@ "); }` [download]	[reply] [d/l]
Re: sth->execute returns undef when successful by 1nickt (Canon) on Aug 08, 2015 at 00:47 UTC
Hi, Please put your trace output inside `<code></code>` tags. The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re^2: sth->execute returns undef when successful by Anonymous Monk on Aug 10, 2015 at 14:17 UTC
Below is the trace: Fyi: the perl script in question reads in metadata and builds out the create table statement then calls a procedure that does the prepare, execute and finish. For the trace I copied the CTAS it was executing and put it in a simple perl script with RaiseError. Just to see how that would work. With RaiseError it does not get an error, but you can see it is returning the undef. Thank you for the repsoneses so far. DBI::st=HASH(0x15783d0) trace level set to 0x0/14 (DBI @ 0x0/0) in DB +I 1.609-ithread (pid 26383) -> execute for DBD::Oracle::st (DBI::st=HASH(0x16e9b28)~0x15783d0) + thr#154e010 dbd_st_execute CREATE (out0, lob0)... Statement Execute Mode is 0 (DEFAULT) OCIStmtExecute(19c18d8,19cd328,19b9f68,1,0,0,0,mode=DEFAULT,0) +=SUCCESS OCIAttrGet(19cd328,OCI_HTYPE_STMT,7fffb492eec8,0,OCI_ATTR_ROW_ +COUNT,19b9f68)=SUCCESS OCIAttrGet(19cd328,OCI_HTYPE_STMT,7fffb492eece,0,OCI_ATTR_SQLF +NCODE,19b9f68)=SUCCESS dbd_st_execute CREATE returned (SUCCESS, rpc2211750915, fn1, o +ut0) <- execute= undef at ./test_query.pl line 80 -> finish for DBD::Oracle::st (DBI::st=HASH(0x16e9b28)~0x15783d0) +thr#154e010 <- finish= 1 at ./test_query.pl line 98 -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x15783d0)~INNER) thr +#154e010 dbd_st_destroy OCIHandleFree(19cd328,OCI_HTYPE_STMT)=SUCCESS <- DESTROY= undef [download]	[reply] [d/l]
Re^3: sth->execute returns undef when successful by poj (Abbot) on Aug 10, 2015 at 15:40 UTC
The table creates successfully (I can query the table after program exits with error). I don't understand how this happens with the rollback statement. What do you have `AutoCommit` set to ? `$sth->execute() or do { $msg = "ERROR:$0: Error in SQL execute:\n$DBI::errstr"; $dbh->rollback(); confess "$msg"; };` [download] poj	[reply] [d/l] [select]
Re^4: sth->execute returns undef when successful by Anonymous Monk on Aug 10, 2015 at 17:11 UTC
Re^5: sth->execute returns undef when successful by poj (Abbot) on Aug 10, 2015 at 17:46 UTC
Some notes below your chosen depth have not been shown here