MySQL Query with variable in Perl

Question

My Perl is fairly rusty so please forgive. Trying to write a query using a variable. Have tried reformatting, just can't seem to get it correctly. Here is my code, not sure what I'm doing wrong.

my $d_var = "$3\n";
my $query="SELECT id FROM `accounts` WHERE (`accounts`.`named` = ?) LIMIT 1";
my $st_h = $db_h->prepare($query);
$st_h->bind_param(1, '$d_var');
  $st_h->execute;
    my $row = $st_h->fetchrow_array();

When you say you can't get it correctly, do you mean it doesn't parse? Does the query run but not return your results? What happens? (Welcome to SO!) Also, have you tried removing the single-quotes from around $d_var in bind_param()? — Dan Fego
– Dan Fego, Commented Jan 4, 2012 at 16:36
Yea, I tried $d_var in different notations. Errors have been changing as I've tried to get it working. Latest error is: Can't call method "bind_param" on an undefined value at new.pl line 31, <$log> line 2. — Dangeruss
– Dangeruss, Commented Jan 4, 2012 at 16:41
Under the first line put in a die "\$d_var not defined" unless defined $d_var;. — user554546
– user554546, Commented Jan 4, 2012 at 17:11
Added, however d_var is getting a value. I updated my original post with the current code, new error is: Use of uninitialized value $row in print at new.pl line 35, <$log> line 14. — Dangeruss
– Dangeruss, Commented Jan 4, 2012 at 17:15
remove the single quote on this line $st_h->bind_param(1, '$d_var') — jchips12
– jchips12, Commented Jan 4, 2012 at 17:18

Ekkehard.Horner · Accepted Answer · 2012-01-04 17:19:24Z

1

Please double check:

$3 contains something reasonable
concatenating $3's value and "\n" (by interpolation) is correct ("\n" in field?)
as ' doesn't interpolate => my $st_ht->bind_param(1, $d_var);

(I don't understand the DBI Docs as Chris Ledet does.)

On 2nd thought:

This code snippet:

  my $v = "nix nix 1001";
  print "$v\n";
  print '$v\n', "\n";
  if ($v =~ m/(nix) (nix) (\d+)/) {
    print 'found: ', $3, "\n";
    $sth = $dbh->prepare('SELECT * FROM sample01.csv WHERE GRUPPE=?');
    $sth->bind_param(1, $3);
    $sth->execute;
    while(my @row = $sth->fetchrow_array()) {
      print '|', join( '|', @row ), "|\n";
    }
  } else {
    print "no match\n";
  }

and the output:

DBI: 1.616 DBD::CSV: 0.33
|00000089-6d83-486d-9ddf-30bbbf722583|2011-09-17 16:25:09|1001|
|000004c9-92c6-4764-b320-b1403276321e|2011-11-09 13:52:30|2000|


nix nix 1001
$v\n
found: 1001
|00000089-6d83-486d-9ddf-30bbbf722583|2011-09-17 16:25:09|1001|

should illustrate:

' does not interpolate, your '$d_var' will pass this variable name literally to DBI
a valid match needs no "\n" to 'work'
the param sequence for bind_param is number, value

edited Jan 4, 2012 at 17:19

answered Jan 4, 2012 at 16:46

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dangeruss Over a year ago

You ARE, the MAN. This is now fixed. Replacing d_var with $3 in the lookup fixed the issue. I don't have enough points to give you an upvote, but certainly deserving.

Ekkehard.Horner Over a year ago

@user1130364: Thanks for the feedback; I'm glad we solved the problem.

frezik Over a year ago

Funny how four other replies missed the interpolation problem. Upvote for noticing the actual problem rather than merely pointing out stylistic concerns.

Dave Cross · Accepted Answer · 2012-01-04 17:22:14Z

1

Not sure why you're even using bind_param. In my opinion, it's far simpler to just pass extra values into execute.

my $d_var = "$3\n";
my $query = 'SELECT id FROM accounts` WHERE (`accounts`.`named` = ?) LIMIT 1';
my $st_h = $db_h->prepare($query);
$st_h->execute($d_var);
my $row = $st_h->fetchrow_array();

Have you considered switching to DBIx::Class?

answered Jan 4, 2012 at 17:22

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

Comments

choroba · Accepted Answer · 2012-01-04 16:50:41Z

0

What does this mean?

my $st_ht->bind_param(1, '$d_var');

There is no variable being introduced, so why the my?

answered Jan 4, 2012 at 16:50

choroba

245k27 gold badges221 silver badges304 bronze badges

1 Comment

Dangeruss Over a year ago

I've modified the code (what you say makes sense), getting another error now. Error: Use of uninitialized value $row in print at new.pl line 35, <$log> line 9.

viGeek · Accepted Answer · 2012-01-04 16:51:18Z

0

I have $3 printing out prior to execution and there is data contained within the string.

I have d_var="$3\n" as the variable $3 is being generated by a Regex string and doesn't seem to work without \n.

Tried what Chris suggested above, however did not work.

answered Jan 4, 2012 at 16:51

viGeek

4174 silver badges8 bronze badges

Comments

jchips12 · Accepted Answer · 2012-01-04 16:51:25Z

0

Aside from what Dan said (removing the single quote), you binded your param to a possibly none Statement handle object $st_ht->bind_param(...) it should be $st_h->bind_param(...).

answered Jan 4, 2012 at 16:51

jchips12

1,2271 gold badge13 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL Query with variable in Perl

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related