SQL placeholder passed on command line in Perl

[macubergeek]

I'm having a problem with line 26. As you can see I'm trying to pass the value of Port as $ARGV[0] on the command line:

------------

      1 #!/usr/bin/perl
      2 ################
      3 ##
      4 ################
      5 use DBI;
      6 use strict;
      7
      8 ###############
      9 ## Variables ##
     10 ###############
     11 my ($dsn) = "DBI:mysql:lookup:localhost";
     12 my ($user) = "root";
     13 my ($password) = "mysql";
     14 my ($dbh,$sth);
     15 my (@ary);
     16
     17 #########################
     18 ## Connect to Database ##
     19 #########################
     20 $dbh = DBI->connect ($dsn, $user, $password, {RaiseError => 1});
     21 ################
     22 ## Make Query ##
     23 ################
     24 $sth = $dbh->prepare (qq{SELECT * FROM ports WHERE Port VALUES(?)});
     25
     26 $sth->execute("$ARGV[0]");
     27 #####################################
     28 ## Read and print results of query ##
     29 #####################################
     30 while (@ary = $sth->fetchrow_array ())
     31 {
     32   print join ("\t", @ary), "\n";
     33 }
     34 $sth->finish ();
     35 ##############################
     36 ## Disconnect from Database ##
     37 ##############################
     38 $dbh->disconnect ();
     39 exit (0);
------------

Perl is complaining:
C:\Documents and Settings\kellyji\Desktop>perl port.pl 23
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near 'VALUES('23')' at line 1 at port.pl line 26.
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near 'VALUES('23')' at line 1 at port.pl line 26.

I wonder if someone can point out how I can correct this...like I've said, line 26 seems to be the culprit but I'm also a little leery of line 24 as well.

 

[azzazzello]

mmm...it looks like you've confuseled INSERT and SELECT syntax. The VALUES() is only applicable in INSERT context. You are doing a SELECT. Try this

$sth = $dbh->prepare (qq{SELECT * FROM ports WHERE Port = ?});

 

[ericbrunson]

Which brings up an importand debugging maxim:

When you're having problems with a SQL statement, ALWAYS: print out the SQL you are using in your script and paste it into the mysql CLI.

This will tell you if your problem is with your API or your SQL.

 

[macubergeek]

erickbrunson
good point
thanks

 

[macubergeek]

From experimenting, I've found that (?) and ? both work.
Does anyone know if it's possible to pass more than one placeholder? Say I want to specify two or more terms in the SELECT statement can I use ? and ?1? or some other form of multiple placeholders inside the SQL query?

 

[ericbrunson]

Try typing: perldoc DBI

There are plenty of examples in the documentation.

 

[macubergeek]

**slapping forehead**
thanks