Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Replacing DBI->do()

by jms53 (Monk)
on Apr 23, 2014 at 13:44 UTC ( [id://1083357]=perlquestion: print w/replies, xml ) Need Help??

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!
J -

Replies are listed 'Best First'.
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;
      "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»

Re: Replacing DBI->do()
by MidLifeXis (Monsignor) on Apr 23, 2014 at 13:56 UTC

    The documentation lists this format for using bind values:

    $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.

    --MidLifeXis

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);
Re: Replacing DBI->do()
by mje (Curate) on Apr 23, 2014 at 14:02 UTC

    Not ignoring Corion's comments the do should be something like

    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.

Re: Replacing DBI->do()
by trippledubs (Deacon) on Apr 23, 2014 at 14:40 UTC

    You could also use SQL::Abstract

    #!/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")

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1083357]
Approved by rnewsham
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (3)
As of 2024-03-29 05:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found