DBLib error makes me go crazy

Question

I am working on a perl script that has multiple database queries, and I constantly get the following error:

DB-Library Error: Try initiating a new SQL Server operation with pending results.

In my code, I have the following:

The first database call executes a series of insert statements constructed from a hash:

while (my ($key, $value) = each(%holidays)) { system("log4k", "DEBUG", "$0", "Staging holiday info data for: $cal_name: $key"); $sql = "INSERT INTO stg_holiday_data (hol_mnemonic, hol_date, hol_comment, dml_type) VALUES (\"$cal_name\", $key, \"$value\", \"N\")"; system("log4k", "TRACE", "$0", "SQL being executed: $sql"); if ($test == 0) { $dbh->dbcmd($sql); ($dbh->dbsqlexec() && $dbh->dbresults) or &fatalError("Database error in $sql", "DB_ERROR"); while($dbh->dbresults != NO_MORE_RESULTS) { while(my @dat = $dbh->dbnextrow){} } } } 

immediately after this ends, I close the connection and cancel it to make sure that the results will not be processed by releasing:

 $dbh->dbcancel(); $dbh->dbclose(); 

From there, I call a separate routine to execute the stored procedure, which will call three lines of output, indicating line numbers:

subroutine call

 &run_sproc() if ($test == 0); 

subroutine:

 sub run_sproc() { system("log4k", "DEBUG", "$0", "Loading staged holiday data"); my $sql1 = "upd_holiday_data"; system("log4k", "TRACE", "$0", "SQL being executed: $sql1"); my($dbh2) = new Sybase::DBlib $ENV{DATABASE_USER}, $ENV{DATABASE_PASSWORD}, $ENV{DATABASE_SERVER}, "GME_calendar_sync"; &fatalError("Failed to login imagine database", "DB_LOGIN_ERROR") unless ($dbh2); $dbh2->dbcmd($sql1); ($dbh2->dbsqlexec() && $dbh2->dbresults ) or &fatalError ("Database error in $sql", "DB_ERROR"); while ($dbh2->dbresults != NO_MORE_RESULTS) { while (my @d = $dbh2->dbnextrow) { system("log4k", "TRACE", "$0", "Next row being inserted @d"); } } $dbh2->dbclose(); } 

I have a third SQL block that comes after a stored procedure that works fine with or without this routine.

What happens, I get the above error right before the printout of the stored procedure. I tried everything I can imagine to make sure that all the results are being processed. The following is an example log output:

 [Tuesday, 23 October 2012 13:30:02 BST] [DEBUG] gme_process_calendars.pl: Staging holiday info data for: CA: 20251226 [Tuesday, 23 October 2012 13:30:03 BST] [TRACE] gme_process_calendars.pl: SQL being executed: INSERT INTO stg_holiday_data (hol_mnemonic, hol_date, hol_comment, dml_type) VALUES ("CA", 20251226, "upload", "N") [Tuesday, 23 October 2012 13:30:03 BST] [DEBUG] gme_process_calendars.pl: Staging holiday info data for: CA: 20220103 [Tuesday, 23 October 2012 13:30:03 BST] [TRACE] gme_process_calendars.pl: SQL being executed: INSERT INTO stg_holiday_data (hol_mnemonic, hol_date, hol_comment, dml_type) VALUES ("CA", 20220103, "upload", "N") [Tuesday, 23 October 2012 13:30:03 BST] [DEBUG] gme_process_calendars.pl: Loading staged holiday data [Tuesday, 23 October 2012 13:30:03 BST] [TRACE] gme_process_calendars.pl: SQL being executed: upd_holiday_data DB-Library error: Attempt to initiate a new SQL Server operation with results pending. [Tuesday, 23 October 2012 13:30:03 BST] [TRACE] gme_process_calendars.pl: Next row being inserted 310107230 

Any help with this would be greatly appreciated, as I already tried everything I could find using an Internet search and reading the documentation.

thanks

Answer 1

Solution found:

In the run_sproc subroutine check:

  ($dbh2->dbsqlexec() && $dbh2->dbresults ) or &fatalError ("Database error in $sql", "DB_ERROR"); 

the second condition $ dbh2-> dbresults is what causes this error condition, once it has been deleted, the error no longer occurs.

Answer 2

I think your problem is that your run_proc method run_proc trying to execute this SQL:

 my $sql1 = "upd_holiday_data"; 

which, I doubt, is a valid SQL command