Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

DBI Quoting question

by perlcgi (Hermit)
on May 10, 2000 at 20:41 UTC ( #11000=perlquestion: print w/replies, xml ) Need Help??

perlcgi has asked for the wisdom of the Perl Monks concerning the following question:

What's the neatest/coolest way to quote the elements of @line below. AFAIK dbh->quote() only quotes a scalar.
Much obliged for any pointers.
my $sql_fmt = "INSERT INTO whatever VALUES( %s, %s, %s, %s, %s, %s, %s +, %s, %s, %s, %s, %s, %s, %s)"; while (<INFILE>) { my @line = (parse_csv ($_)); my $sql = sprintf ($sql_fmt, @line); #Elements of @line need to b +e in quotes $dbh->do($sql); }

Replies are listed 'Best First'.
Re: DBI Quoting question
by btrott (Parson) on May 10, 2000 at 20:46 UTC
    Use placeholders--then the values will automatically be quoted.
    my $NUM_FIELDS = 14; my $sql = "INSERT INTO whatever VALUES (" . join(', ', ('?') x $NU +M_FIELDS) . ")"; my $sth = $dbh->prepare_cached($sql); while (<FILE>) { my @line = (parse_cvs($_)); $sth->execute(@line); } $sth->finish;
    If you want to be more flexible about the number of fields in your file, do the prepare inside the loop (but make sure you use prepare_cached so that you're not preparing the same statement over and over):
    while (<FILE>) { my @line = (parse_cvs($_)); my $sql = "INSERT INTO whatever VALUES (" . join(', ', ('?') x @line) . ")"; my $sth = $dbh->prepare_cached($sql); $sth->execute(@line); $sth->finish; }
    In fact, I like this last better. :) I'd recommend using this, because it's nice to be input-independent.
Re: DBI Quoting question
by plaid (Chaplain) on May 10, 2000 at 20:46 UTC
    The best is to use the prepare method with placeholders:
    my $sth = $dbh->prepare("INSERT INTO whatever VALUES(?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?)") while (<INFILE>) { my @line = (parse_csv ($_)); $sth->execute(@line); }
    This has the benefit of taking care of the quoting for you, but also of adding a performance increase, as just having to prepare the query once will save some time, as opposed to the DBI 'do' subroutine, which has to prepare it each time.
Re: DBI Quoting question
by httptech (Chaplain) on May 10, 2000 at 20:47 UTC
    You'll want to use placeholder variables instead, that will take care of the quoting for you.
    my $sql_fmt = "INSERT INTO whatever VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,? +)"; while (<INFILE>) { my @line = (parse_csv ($_)); $dbh->do($sql_fmt,undef,@line); }
    You just need to make sure you always have the same number of array elements in @line that you have '?' marks in $sql_fmt or DBI will give you an error.

    Also it's a good idea to format your SQL statements like: INSERT INTO whatever (column_names) VALUES(?...) That way all your insert statements won't break if you decide to add to your database structure.

RE: DBI Quoting question
by lhoward (Vicar) on May 10, 2000 at 21:03 UTC
    Placeholders are definitely the way to go. However there is another approach that can be useful for situations where you can't use palceholders. The only example that comes to mind of a time where you can't use placeholders is "selet * from foo where bar in (?)" where you want to specify a list with an unknown number of elements for ?.

    The DBH quote function will quote a variable apropriately for the DB that your handle is connected to, escaping all necessary metacharacters, etc....

    my $s=$dbh->quote($a);
Re: DBI Quoting question
by BBQ (Deacon) on May 10, 2000 at 20:47 UTC
    Maybe I'm looking at this the wrong way, but would you need to quote non-scalars if you were sticking to the prepare + execute method?


    Doesn't the $dbh->do() method skip all DBI internal SQL checking and send it "as-is" for the database to figure out? I remember reading this somehere, and it occured to me that by using the method you had stated above, you could be running into SQL syntax problems if the quoting part didn't work out. Just a thought.

    # Trust no1!
      The DBI::do() method replaces both prepare() and execute(), though it has to go through those steps anyway. If you're going to do any sort of looping, it's inefficient and you'll get much better performance with a little more typing.

      btrott's last suggestion is right on top of things.

      Whoa! Thanks guys - almost as fast as perldoc. Was the answer that obvious? Everyone gets a vote tomorrow.
Re: DBI Quoting question
by princepawn (Parson) on May 10, 2000 at 22:20 UTC
    If you can get away with it, use DBIx::Recordset instead of DBI... DBI was designed to be an interface to databases, not the interface for an application programmer to databases Solves MANY of the irksome issues associated with DBI. Check it out on CPAN
RE: DBI Quoting question
by Anonymous Monk on May 10, 2000 at 23:04 UTC
    my $sql_fmt = "INSERT INTO whatever VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"; while (<INFILE>) { my @line = (parse_csv ($_)); my $sql = sprintf ($sql_fmt, map {$dbh->quote($_)} @line); #Elements of @line need to be in quotes $dbh->do($sql); }

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2022-06-27 20:36 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (88 votes). Check out past polls.