Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: DBI variable argument count

by ig (Vicar)
on Sep 13, 2012 at 22:58 UTC ( #993596=note: print w/ replies, xml ) Need Help??


in reply to DBI variable argument count

I tend to assemble my arguments to execute as I assemble my SQL statement. There are many ways to do it. The following might give you some ideas:

use strict; use warnings; use Data::Dumper; update(10, { name => 'test', value => 'success', }); sub update { my ($key, $params) = @_; my $sql = "update my_table set "; my @args; $sql .= join( ', ', map { push(@args, $params->{$_}); "$_ = ?" } keys %$params ); $sql .= " where key = ?"; push(@args, $key); die Dumper([$sql, \@args]); }

which gives a coordinated SQL statement and array of arguments for execute:

$VAR1 = [ 'update my_table set value = ?, name = ? where key = ?', [ 'success', 'test', 10 ] ];

update: you should check the field names to avoid SQL injection. I typically qualify them against a list of known field names one way or another (grep a list, lookup in a hash, match a regular expression, etc.)


Comment on Re: DBI variable argument count
Select or Download Code
Replies are listed 'Best First'.
Re^2: DBI variable argument count
by anothersmurf (Novice) on Sep 14, 2012 at 01:39 UTC
    oh my. I don't get it. I think I'm doing this right but I'm obviously not.
    $request = "SELECT * FROM nsmw WHERE unit = ? AND name1 REGEXP ? AND e +mail REGEXP ? ORDER BY unit" @values = ("001", "duckman", "google.com"); # Do database transaction my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost", "user", + "user",{'RaiseError' => 1}); my $sth = $dbh->prepare($request); $sth->execute(@values); while (my $ref = $sth->fetchrow_hashref()) { # here is where the magic happens h3("Unit $ref->{'unit'}"); }
    There is a matching row in that DB but for some reason it just doesn't print anything in the while loop. :(

    I think maybe I'm just too tired. I've been coding for 9 hours without a break =)

      Take a break - it's amazing what a fresh mind can see.

      I'm not familiar with REGEXP, so can't comment on that.

      I would strip down the query then build it back up one argument at a time, to see where the problem is. Do you get some records from the query select * from nsmw? If so, then add your where clauses one at a time. This will, at least, narrow your focus a bit.

      update: a quick look at REGEXP turns up this for MySQL 5.1:

      The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

      Are your strings plain ASCII or are they multi-byte?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://993596]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (14)
As of 2015-07-28 11:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (254 votes), past polls