jms53 has asked for the wisdom of the Perl Monks concerning the following question:
I have a small DB module and I'm not sure wether or not I'm using DBI->do() correctly.
This is the code I have:
my ($first, $last) = split (' ', $user);
my $id = $dbh->do(q{
INSERT INTO person SET id=?, first_name=?, las
+t_name=?;
}, values {id=>'NULL', first_name=> $first, last_
+name=> $last});
$dbh->disconnect;
When I test this, perl complains that I am not using a hash for the values. Should I be using a different DBI function? I have tried many combinations, and the documentation doesn't show an example.
Thank you!
Re: Replacing DBI->do()
by Corion (Patriarch) on Apr 23, 2014 at 13:52 UTC
|
We must be looking at different documentations. When I look at the documentation of v1.631 of DBI for ->do, I see examples.
The examples do not match your code though, and I really wonder how your code compiles if it is supposed to be normal DBI code.
Maybe you want to learn about how DBI uses placeholders first?
I'm also unfamiliar with the SQL statement you seem to be using. I have never seen INSERT INTO ... SET foo=bar.
Also, you might want to read up on how DBI maps undef and NULL. A string NULL seems highly unlikely as a value for the column id.
Personally, I usually end up using the sequence of ->prepare, ->execute, ->finish:
my $dbh= DBI->connect('dbi:...', $user, $pass, { RaiseError => 1, Prin
+tError => 0 });
# RaiseError is important so I can leave out all error handling
my $sth= $dbh->prepare(<<'SQL');
INSERT INTO person SET id=?, first_name=?, last_name=?;
SQL
$sth->execute( undef, $first, $last );
$sth->finish;
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
"INSERT INTO ... SET foo=bar"
See INSERT:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Surprise! I didn't know it too.
Best regards, Karl
«The Crux of the Biscuit is the Apostrophe»
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: Replacing DBI->do()
by MidLifeXis (Monsignor) on Apr 23, 2014 at 13:56 UTC
|
$rv = $dbh->do($statement, \%attr, @bind_values);
I am surprised that your snippet even compiles or passes the SQL parser, but in any case, I don't think that you are doing what you think you are doing.
To expand on the synopsis example, do:
my $statement = q{INSERT INTO....};
my %attr = ();
my @bind_values = ( 'NULL', $first, $last );
my $rv = $dbh->do( $statement, \%attr, @bind_values );
and see what happens.
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: Replacing DBI->do()
by rnewsham (Curate) on Apr 23, 2014 at 13:58 UTC
|
Here is an example of how I might have written that. Note the use of placeholders and that null is null and not the string 'null'.
my $id = $dbh->do("INSERT INTO person (id, first_name, last_name ) val
+ues ( null, ?, ? )", undef, $first, $last);
| [reply] [Watch: Dir/Any] [d/l] |
Re: Replacing DBI->do()
by mje (Curate) on Apr 23, 2014 at 14:02 UTC
|
my $id = $dbh->do(q{INSERT INTO person (id, first_name, last_name) val
+ues(?,?,?}, undef, undef, $first, $last});
that is, do($sql, undef, @bind_values). Also, notice I removed the trailing ';'. Also, I'm guessing your id field is actually auto generated for you by the database and a sequence or something in which case you can leave out the insert of the id entirely.
Also, your assignment to $id worries me as it will not be the id auto generated by the database. | [reply] [Watch: Dir/Any] [d/l] |
Re: Replacing DBI->do()
by trippledubs (Deacon) on Apr 23, 2014 at 14:40 UTC
|
#!/usr/bin/env perl
use strict;
use warnings;
use SQL::Abstract;
use Data::Dump;
my $first = 'john';
my $last = 'smith';
my $sql = SQL::Abstract->new();
my ($stmt,@bind) = $sql->insert(
'person',
{
id => undef,
first_name => $first,
last_name => $last
}
);
dd $stmt;
dd @bind;
Output:
"INSERT INTO person ( first_name, id, last_name) VALUES ( ?, ?, ? )"
("john", undef, "smith")
| [reply] [Watch: Dir/Any] [d/l] |
|
|