I recently got saddled with a project involving mSQL and DBI and it
needs to run cleanly under mod_perl at some point, but right now I'm
just cleaning the code up so I can maintain it properly. This means
changing a lot of "hackish" perl over to something a little more sane
for long term development.
Each "screen" presented to the user touches the mSQL database "test"
(rather scramble my own data first then the clients :))
I have a perl module that looks like so:
database.pm
-----------
use strict;
use DBI;
BEGIN {
use Exporter ();
@database::ISA = qw(Exporter);
@database::EXPORT = qw();
@database::EXPORT_OK = qw($dbh);
}
use vars qw($dbh);
$dbh = DBI->connect('dbi:mSQL:test', '') ||
die "DBI ERROR: $DBI::errstr";
# prototypes
sub put_sqlbind (@);
# todo: transactions
sub put_sqlbind (@) {
my ($query, @binds) = @_;
my $sth = $dbh->prepare($query);
my $rows = $sth->execute(@binds);
return($rows);
}
1;
Then my main script:
script.pl
---------
use strict;
use Time::Local;
use vars qw($dbh);
use database qw($dbh);
my $foo = 'bar';
my $bar = 'foo';
my $key_date = '1-Jan-1990';
my $key_location = 'Somewhere';
my $query = 'UPDATE table SET foo = ?, bar = ? WHERE key_date = ? AND
key_location = ?';
my @binds = ( $foo, $bar, $key_date, $key_location );
my $rows = &database::put_sqlbind($query, @binds);
print "rows = $rows\n";
$rows return from database.pm is always an undefined value so I'm
guessing it wasn't successful.
However, in mSQL's update.log, I see:
UPDATE table SET foo = 'bar', bar = 'foo' WHERE key_date = '01-Jan-1990'
AND location = 'Somewhere'
But the information is not being committed, I go back to the record and
it's still the old data I had before any changes.
The table is populated and the field being updated does exist. mSQL
logs the update, but doesn't seem to want to commit it. Am I doing
something wrong?
I should note that the original code did indeed work, and used the do()
method with concatenated strings:
$update = "UPDATE table SET foo = '$foo', bar = '$bar' ..." .
"WHERE key_date = '$key_date' AND location = '$location'";
$dbh->do($update);
DBI is imported in theirs as well, so I'm guessing it's a programming
error on my behalf, theirs looks like:
script.pl
---------
use Time::Local;
require 'database.pl';
$foo = "bar";
..
..
$update = "UPDATE table ...";
$dbh->do($update);
database.pl
-----------
use DBI;
$drh = DBI->install_driver('mSQL');
$dbh = $drh->connect('test');
die "DBI error: $DBI::errstr\n" unless $dbh;
1;
I'm using:
perl 5.0053
Time::Local
CGI (that came with my perl distribution)
mSQL v3
DBI v1.37
Any replies appreciated.
