PerlScript, ADODB, SQL Server updates not working

[pfaut]

I have been helping a friend develop a website. He uses IIS, VBScript, and SQL Server via ADODB. In order to make database updates easier, I wrote some PerlScript to handle imports. This is where the problems lie.

We have several export routines that provide data from various tables in CSV format. The person that does the updates loads this data into a spreadsheet, massages it as necessary, exports to a new CSV and imports (uploads) it to the site. Sometimes the updates don't take but no errors are generated.

SQL Server seems to have some odd ideas on how to signal success or failure back to the application. Generally, in stored procedures we need to include 'SET NOCOUNT ON' or else the VBScript code recieves errors when calling them. When executing an SQL statement, however, 'SET NOCOUNT OFF' needs to be in effect or else it thinks there were no results. I believe this has something to do with our problems.

In some of the imports, I need to first update a record, then call a stored procedure to perform some other updates based on the new data. I don't wish to do the updates from a stored procedure because I'm trying to be flexible about the format of the CSV file (one of the reasons for coding the imports in perl instead of VBScript). I only want the CSV file to contain the columns that have changed data. This reduces the size of the import and also speeds up the process. It's easier to build a dynamic query using perl than SQL Server stored procedures.

I think part of the problem here is that the stored procedure does a 'SET NOCOUNT ON' but that screws up the next query. The result would be that the first record got updated correctly but each additional record would complain about not being able to locate the record. Checking by other means would prove that the additional records were indeed on file.

So I added a 'SET NOCOUNT OFF;' to the beginning of the query SQL statement. This seemed to help in one import but not another and recently the problem appears to have returned on the import that had started working. When it fails, it goes through all the motions and does not return any error status but the updates don't take.

Right now, the problem appears to be present on the more important import. No errors are being reported but the updates aren't being applied to the database. There appears to be some wierd interaction between perl and ADODB that's not signalling errors but not working, either, and I am unable to figure out what the problem is.

Another problem I've seen is when doing inserts. It seems each field's length is set by the first set of data provided to the statement. Data provided for parameters in subsequent executions of the statement with different data get truncated to the length of the first set of data. I eventually got around this by switching from ADODB to ODBC but it ran a lot slower.

If anyone has experienced problems like these or has hints for working with ADODB/SQL Server from perl, I'd appreciate your input.

In general, the import routines look something like this:

sub _open_db {
    my $conn = $Server->CreateObject("adodb.connection");
    $conn->Open($ScriptingNamespace->GetConnectionString);
    return $conn;
}

sub _db_command {
    my ($conn,$sql) = @_;
    my $cmd = $Server->CreateObject("adodb.command");
    $cmd->{ActiveConnection} = $conn;
    $cmd->{CommandText} = $sql;
    $cmd->{Prepared} = 1;
    $cmd->Parameters->Refresh;
    return $cmd;
}

sub _import_products {
    my ($fnm) = @_;
    chomp $fnm;
    return unless my $fh = _open_file($fnm);
    my $csv = Text::CSV_XS->new;
    my $line = <$fh>;
    $csv->parse($line);
    my @fields = $csv->fields;
    my %map;
    return if _check_fields([qw(PRODUCTID)], \@fields, \%map);
    my $conn = _open_db;
    my $cmd = _db_command($conn,"set nocount off;select * from product
+s where productid=?");
    my $cmd2 = _db_command($conn,"exec fixup_generic_names ?");
    while (defined($line = <$fh>)) {
        if (!defined($csv->parse($line))) {
            _append_log("<b>Malformed CSV file at line $.</b>");
            return;
        }
        @fields = $csv->fields;
        $cmd->Parameters(0)->{Value} = $fields[$map{PRODUCTID}];
        my $rs = $Server->CreateObject("adodb.recordset");
        $rs->Open($cmd,undef,adOpenStatic,adLockPessimistic);
        if ($rs->{EOF}) {
            _append_log("Product $fields[$map{PRODUCTID}] not found<br
+>");
        } else {
            my $pid = $rs->Fields('ProductID')->{Value};
            for (qw(list of product table column names)) {
                $rs->Fields($_)->{Value} = $fields[$map{$_}] if exists
+ $map{$_};
            }
            $rs->Update;
            if (_report_errors($conn)) {
                _append_log("Current product id $pid (update)<br>");
                $conn->Errors->Reset;
                next;
            }
            undef $rs;
            $cmd2->Parameters(0)->{Value} = $pid;
            my $cnt = Variant(VT_R8|VT_BYREF, 0);
            $cmd2->Execute($cnt);
            if (_report_errors($conn)) {
                _append_log("Current product id $pid (fixup)<br>");
                $conn->Errors->Reset;
                next;
            }
        }
    }
    _append_log("<b>Done</b>");
}

[olivierp]

$rs->Open($cmd,undef,adOpenStatic,adLockPessimistic);
        if ($rs->{EOF}) {
            # ....
        } else {
         #...
                         $rs->Update;
         #...
        }

I'm wondering if using a cursor that is defined as read only and scrollable, and then trying to update it, could be causing you problems.

[pfaut]

Those are the same parameters we use in the VBScript code to get an updateable recordset. That code works fine.