Hi all,

I suspect that this is more of a database issue than a perl problem, but I have been struggling with it for weeks and so I am now turning to you folks. It would be just great if anybody had *any* information on this, as I'm just plain stumped and out of ideas.

I am using dbi/dbd on Windows, using the odbc dbd and SQL Server. I am updating text fields, and occasionally I will get this bizarre error. I catch the error, disconnect/reconnect, and try again up to 10 times. (that was my last attempt at trying to "fix" this.)

When it wants to fail, it fails. However at other times (and usually) the same command will work fine. The error is happening on a customer's remote server - so I haven't had direct access to it myself. I keep sending patches back and forth trying to get to the bottom of this. Here is the error I am generating :

Slept 3s... Trying again after reconnect.
Slept 2s... Trying again after reconnect.
Slept 1s... Trying again after reconnect.
Slept 1s... Trying again after reconnect.
Slept 3s... Trying again after reconnect.
Slept 1s... Trying again after reconnect.
Slept 1s... Trying again after reconnect.
Slept 3s... Trying again after reconnect.
Slept 2s... Trying again after reconnect.
DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverSQL ServerNULL textptr (text, ntext, or image pointer) passed to UpdateText function. (SQL-42000) MicrosoftODBC SQL Server DriverSQL ServerThe statement has been terminated. (SQL-01000)(DBD: st_execute/SQLExecute err=-1) at ...

UPDATE MASTER5 SET Promote__bFile__bList=?,Post__bPromote__bRequirements=? WHERE mrID=300806


It is basically coming from this code:

# the two sql statements must function as one single, atomic transacti
+on
# we check for errors after the eval block.
    
LABEL_START_TRANSACTION:

    eval
    {
        # AutoCommit must be off for transaction handling, it is put b
+ack on
        # after the transaction.
        
        $dbh->{AutoCommit} = 0;
        $dbh->{RaiseError} = 1;
        
        # if nothing is being updated, then we don't want to fail
        if ($setFields)
        {
            $sql = "UPDATE $table SET $setFields WHERE mrID=$mrNumber"
+;
            
            # prepare the sql statement
            $sth = $dbh->prepare($sql);
            
            # bind the parameters (description and any multi-line fiel
+ds)
            for (my $i=0; $i<=$#projValuesToBind; $i++)
            {
                $sth->bind_param($i+1, $projValuesToBind[$i], DBI::SQL
+_LONGVARCHAR);
            }
            
            # execute the sql statement to insert row into MASTERx tab
+le
            $sth->execute();
        }
        
        # get the new address book fields
        if($ENV{'ABMASTER'} && $setABFields) 
        {
            $sql = "UPDATE ${table}_ABDATA SET $setABFields WHERE mrID
+=$mrNumber";
            
            # prepare the sql statement
            $sth = $dbh->prepare($sql);
            
            # bind the parameters (any multi-line fields) 
            for (my $i=0; $i<=$#ABValuesToBind; $i++)
            {
                $sth->bind_param($i+1, $ABValuesToBind[$i], DBI::SQL_L
+ONGVARCHAR);
            }
            
            # execute the sql statement to update row into MASTERx_ABD
+ATA table
            $sth->execute();
        }
    
        # there are sql statements that need to be comitted
        if (($setABFields && $ENV{'ABMASTER'}) || $setFields)
        {
            # everything worked, so commit the database changes.
            $dbh->commit();      
        }
    };
    
    
    if ($@) # error during transaction, rollback and report informatio
+n
    {   
        $errorCount++;
        my $originalError = $@;
        
        # retry for up to five errors
        if ($errorCount <= $DBI_RETRY_COUNT)
        {
            # roll back the transaction
            #  (catch errors from rolling back.  otherwise the script 
+will just die.)
            eval {
                $dbh->rollback();
            };
            if ($@)  # error rolling back!
            {
                $errStr .= "$originalError <BR><BR>\n";
                $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
                &errorExit(203, "mrChange transaction error: ", $errSt
+r, "<BR>\nalso failed while rolling back: $@<BR><BR>\n");
            }
            
            # sleep a random max of $DBI_RETRY_SECONDS
            my $retrySeconds = int(rand($DBI_RETRY_SECONDS)+1);
            
            $errStr .= "Slept ${retrySeconds}s... ";
            sleep $retrySeconds; 
            
            $errStr .= "Trying again after reconnect.<BR>\n";

            # reconnect
            &disconnectFromDBD;
            &connectToDBD;
            
            goto LABEL_START_TRANSACTION;
        }
        
        # roll back the transaction
        #  (catch errors from rolling back.  otherwise the script will
+ just die.)
        eval {
            $dbh->rollback();
        };
        if ($@)  # error rolling back!
        {
            $errStr .= "$originalError <BR><BR>\n";
            $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
            &errorExit(203, "mrChange transaction error: ", $errStr, "
+<BR>\nalso failed while rolling back: $@<BR><BR>\n");
        }
        
        # rollback was successful:
        # append warning from dbi to error string
        $errStr .= "$originalError <BR><BR>\n";
        
        $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
        
        &errorExit(203, "mrChange transaction error: ", $errStr);
    }
    
    # AutoCommit MUST get turned back on, as the rest of FootPrints do
+es not
    # use transactions.
    
    $dbh->{AutoCommit} = 1;
    $dbh->{RaiseError} = 0;
[download]

It's failing on the execute, always when binding a parameter to a text field.

Thanks for ANY help!

Matt