database - DBLib 错误让我抓狂

Question

我正在处理具有多个数据库调用的 perl 脚本，但我不断收到以下错误:

数据库库错误: 尝试启动一个新的 SQL Server 操作，结果未决。

在我的代码中有以下内容:

对数据库的第一次调用执行了一些从散列构建的插入语句:

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){}
        }
    }
}

完成后我立即关闭连接，并取消它以确保没有留下要处理的结果:

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

从那里我调用一个单独的子例程来执行一个存储过程，它将产生三行表示行号的输出:

子程序调用

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

子程序:

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();
}

我确实有第三个 SQL block ，它位于存储过程之后，无论有没有这个子例程都可以正常工作。

发生的事情是我在存储过程打印结果之前收到上述错误。我已经尝试了我能想到的一切，以确保所有结果都得到处理。日志输出示例如下:

[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

如果我能提供任何帮助，我将不胜感激，因为我已经尝试了所有我能找到的东西，进行互联网搜索和阅读文档。

谢谢

Answer 1

我认为您的问题是您的 run_proc 方法试图执行此 SQL:

my $sql1 = "upd_holiday_data";

我怀疑这是一个有效的 SQL 命令