Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
Don't ask to ask, just ask
 
PerlMonks  

PerlScript, ADODB, SQL Server updates not working

by pfaut (Priest)
on Nov 17, 2004 at 01:37 UTC ( #408300=perlquestion: print w/ replies, xml ) Need Help??
pfaut has asked for the wisdom of the Perl Monks concerning the following question:

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>"); }
90% of every Perl application is already written.
dragonchild

Comment on PerlScript, ADODB, SQL Server updates not working
Download Code
Re: PerlScript, ADODB, SQL Server updates not working
by olivierp (Hermit) on Nov 17, 2004 at 12:47 UTC
    $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.
    HTH
    --
    Olivier

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

      90% of every Perl application is already written.
      dragonchild

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://408300]
Approved by neilh
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (14)
As of 2014-04-24 12:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (565 votes), past polls