perlquestion
pfaut
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>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.</p>
<p>If anyone has experienced problems like these or has hints for working
with ADODB/SQL Server from perl, I'd appreciate your input.</p>
<p>In general, the import routines look something like this:</p>
<readmore>
<code>
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 products 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>");
}
</code>
</readmore>
<div class="pmsig"><div class="pmsig-217641">
<center><table><tr><td><font size="-1">90% of every Perl application is already written. [id://246498|⇒]</font></td></tr><tr><td align="right"><font size="-1"><i>[dragonchild]</i></font></td></tr></table></center>
</div></div>