DBI UPDATE into ODBC fails... 1

[netbumbler]

I'm fairly new to PERL and DBI, but from the standard examples I've been able to follow, this should work:

$dbh=DBI->connect($dbName, $dbUsername, $dbPassword);

$sql="UPDATE vs SET dc2='$fcriteria2' where dc2='$fcriterian'";

$Updaterecord=$dbh->do($sql)

# note: $fcriteria2 and $fcriteria are passed on CLI.

I get a failure code with $Updaterecord - and accessing the table on the application shows no update of the records. Any ideas?
Thanks much,
Chris

 

[netbumbler]

Forgot to mention, I'm using ActivePerl on Windows 2000.

 

[TrojanWarBlade]

Does that user have update permissions on that table?
Hve you tried the "errstr" method to see what the error message is?


Trojan.

 

[netbumbler]

Actually, I figured it out. The where clause was using = and comparing the exact length of the field. When I changed the where comparison to use LIKE it started working.
Thanks!
Chris

 

[fishiface]

some advice from a seasoned DBI hacker:

Replace

$sql="UPDATE vs SET dc2='$fcriteria2' where dc2='$fcriterian'";

$Updaterecord=$dbh->do($sql)

with

$sql="UPDATE vs SET dc2=? where dc2=?";
$Updaterecord=$dbh->do($sql,{},$fcriteria2,$fcriterian)

if your database supports it.

This does two things for you: it means you can forget about whether a particular field needs quoting or not and it means that you never need worry about metacharacters in your data. OK - it might never happen with this application but it's a good habit to get into because you will get tripped up one day without it.

Consider the case where $fcriteria2 =

   '';delete from vs;UPDATE vs SET dc2='

including the quotes. It's a mild example of an "SQL injection attack", to which the use of placeholders is a useful defense.

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur

 

[netbumbler]

Thanks fishi - I can see how that would be a problem. I tried them originally, but I think I was not completing it right or something. Anyway, I'll probably go back to it!
Thanks,
Chris

BTW - I love your Maurice quote. Dang if that doesn't personify ALL of IT.

 

[fishiface]

Here's some typical DBI usage:

my $dbh = DBI->connect($dbName, $dbUsername, $dbPassword)
  or die "$0: Can't connect to database: $DBI::errstr";

# unparameterised query
$num_deleted = $dbh->do( q{
       DELETE FROM temptab
  } );

# parameterised...
$num_deleted = $dbh->do( q{
       DELETE FROM temptab WHERE id < ?
  }, {}, $max_id );

# lets get some data
my $fullname = $dbh->selectrow_array( q{
       SELECT fullname FROM users WHERE login = ?
  }, {}, $me );

# hang on - data are a plural!
my( $fullname, $auth ) = $dbh->selectrow_array( q{
       SELECT fullname, auth FROM users WHERE login = ?
  }, {}, $me );

# more than one record?
my $q = $dbh->prepare( q{
       SELECT fullname, auth FROM users WHERE status = ?
  } ) or die "$0: can't prep: $DBI::errstr";
$q->execute( $stat ) or die "$0: can't exec: $DBI::errstr";
$q->bind_columns( undef, \my( $full, $auth ) );
while( $q->fetch() ) {
   # do something interesting with $full and $auth
}

These techniques cover 95% of the day-to-day DBI tasks that cross my desk and I hope that they prove useful templates for your own code.

Yours,

fish

["]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.["]
--Maur