I am working on a perl script that has multiple database calls and I continuously get the following error: DB-Library error: Attempt to initiate a new SQL Server operation with results pending. In my code I have the following: The first call to the database performs a number of insert statements built from a hash: 
while (my ($key, $value) = each(%holidays)) {

    system("log4k", "DEBUG", "$0", "Staging holiday info data for: $ca
+l_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){}
        }
    }
}

[download]
immediately after that finishes I close off the connection, and cancel it to make sure that there are no results left to be processed by issuing: 
$dbh->dbcancel();
$dbh->dbclose();

[download]
From there I call a separate subroutine to execute a stored procedure which will produce three lines of output signifying row 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_P
+ASSWORD}, $ENV{DATABASE_SERVER}, "GME_calendar_sync";
    &fatalError("Failed to login to database", "DB_LOGIN_ERROR") unles
+s ($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();
}

[download]
I do have a third SQL block that comes after the stored procedure that works fine with or without this subroutine. What is happening is I am receiving the error mentioned above right before the results from the stored procedure print. I have tried everything I can imagine to make sure that all the results are being processed. A sample of the log output is below: 
[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, h
+ol_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, h
+ol_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 pendin
+g.
[Tuesday, 23 October 2012 13:30:03 BST] [TRACE] 
gme_process_calendars.pl: Next row being inserted 310107230

[download]
Any help with this would be greatly appreciated as I have already tried everything I can find doing an internet search and reading the documentation. Thanks Solution Found: Solution Found: In the run_sproc sub routine, the check: 
    ($dbh2->dbsqlexec() && $dbh2->dbresults ) 
            or &fatalError ("Database error in $sql", "DB_ERROR");

[download]
the second condition  $dbh2->dbresults is what is causing this error condition, once this was removed the error did not come up anymore. Thank you everyone that looked into this for me

In reply to DBLib error driving me crazy by sandygallant

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.