Re: Problem with DBI and MySQL

ikegami is right, checking for errors helps. Shortcut:

~~In your statement~~

$dbh->do ('INSERT into teach_info (pickup, pmonth, pday, pyear)
[download]

~~the tokens pickup, pmonth, pday, pyear are not symbols at the database level and thus have to be quoted.~~ Use placeholders.

If the values you are inserting are in the column order, you dont need the column enumeration at all - you can just do

my $sql = "INSERT into teach_info values ($pickup,$pmonth, $pday, $pye
+ar)";
[download]

But! Again, use placeholders. Remember Bobby Tables:

use DBI;
use strict;

my $dbh = DBI->connect(
    'DBI:mysql:gailbord_teachcoll',
    'gailbord_admin',
    'thotwp2',
    { RaiseError => 1 }
);
my $sth = $dbh->prepare("INSERT into teach_info values(?,?,?,?)");
$sth->execute($pickup, $pmonth, $pday, $pyear);
[download]

update: ~~incorrect statement~~ - thanks, ruzam. My DB fu is a bit rusty... ;-)

Comment on Re: Problem with DBI and MySQL Select or Download Code

Replies are listed 'Best First'.
Re^2: Problem with DBI and MySQL by ait (Hermit) on Aug 23, 2010 at 01:29 UTC
If the values you are inserting are in the column order, you dont need the column enumeration at all - you can just do That is not a good idea because it makes the insert very fragile to possible database changes: if the field order changes your insert breaks. It is always wise to use explicit columns and then the values. Regarding your recommendation to use placeholders (aka bind variables) you are absolutely correct. If your database supports it, DBI will take advantage and prepare the statements in the database, which can boost your transaction speed in great orders of magnitude depending on the underlying RDBMS. The use of do() should be avoided at all times, and every statement should be prepared using bind variables. Of course There's More Than One Way To Do It, but in general terms, it makes a lot of sense to prepare your queries beforehand, much like you would in database procedure languages such a PL/pgSQL and Oracle's PL/SQL	[reply]
Re^2: Problem with DBI and MySQL by ruzam (Curate) on Aug 23, 2010 at 00:41 UTC
Actually, the tokens '(pickup, pmonth, pday, pyear)' are field names for the following 'values ($pickup, $pmonth, $pday, $pyear)' part of the statement, which is perfectly valid, no quoting required. You can't assume that pickup, pmonth, pday and pyear are the only fields in the table either (others may be auto defaulted) or that the order of the fields in the table is the same table definition (they may have to be inserted in a different order). The real problem is that the entire query is contained in single quotes and the variables will never be filled in (in the first example). Checking errors as has been suggested would confirm this. The query would never execute successfully with literals like $pickup in them. The second example may be equally broken depending on the values of the variables. Numbers can go unquoted, but string values would definitely need quotes around them. Again checking errors would confirm this to. Maybe it's a simple case of table permission. The MySQL database user may be able to read the table but not insert values. Again with the checking errors. I can't agree more on the use of place holders (++). This also works: `my $affInfo = $dbh->do( 'INSERT into teach_info (pickup,pmonth,pday,pyear) values (?,?,?,?)', undef, $pickup, $pmonth, $pday, $pyear);` [download]	[reply] [d/l]