CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Insert syntax question

 



earachefl
Novice

Aug 9, 2009, 1:54 PM

Post #1 of 6 (6593 views)
Insert syntax question Can't Post

I'm trying to complete a tutorial on inserting files to a database. In a previous tutorial, I had been able to successfully connect to this database and print the contents. Now I get an error message:

Quote
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3. (SQL-07002) at C:\perlscripts\lab11-2.pl line 61, <STDIN> line 5.

Line 61 is:

Code
$sql = qq{INSERT INTO cartable (year, make, model, color, price) VALUES($year, $make, $model, $color, $price);};

The textbook includes a completed lab in which that line reads:

Code
$sql = qq{INSERT INTO cartable (year, make, model, color, price) VALUES ($year, '$make', '$model', '$color', '$price');};

which compiles and runs fine. As far as I can see, the only difference is the addition of single quotes to the last 4 values. I am puzzled as to why this would work.

For what it's worth, my original code for the offending line was:

Code
$sql = "INSERT INTO cartable ('year', 'make', 'model', 'color', 'price') VALUES($year, $make, $model, $color, $price)";

which gives the same error message as above but with "Expected 4" instead of "Expected 3".


FishMonger
Veteran / Moderator

Aug 10, 2009, 9:37 AM

Post #2 of 6 (6563 views)
Re: [earachefl] Insert syntax question [In reply to] Can't Post

Please post a more complete sample of your code. I need to see how/where you're assigning those vars.


earachefl
Novice

Aug 10, 2009, 12:50 PM

Post #3 of 6 (6558 views)
Re: [FishMonger] Insert syntax question [In reply to] Can't Post

OK, here's more of the code:

Code
#Variables have previously been read from <STDIN> and chomped 

if(($year != '') && ($make ne '') && ($model ne '') && ($color ne '') && ($price ne ''))
{
UpdateDB();
}
else
{
print("Unable to complete insert.\n");
print("Incomplete data was received.\n");
}

sub UpdateDB
{

$sql = qq{INSERT INTO cartable (year, make, model, color, price) VALUES($year, $make, $model, $color, $price);};
#$sql = "INSERT INTO cartable ('year', 'make', 'model', 'color', 'price') VALUES($year, $make, $model, $color, $price)";

$dbh = DBI->connect("dbi:ODBC:cars", '', '') or die "Couldn't connect: $dbi::errstr";

$sth = $dbh->prepare($sql);
$sth->execute;

$dbh->disconnect;
}



FishMonger
Veteran / Moderator

Aug 10, 2009, 2:42 PM

Post #4 of 6 (6557 views)
Re: [earachefl] Insert syntax question [In reply to] Can't Post

Verifying the variables should have been done when they were read from STDIN.

Try this:

Code
sub UpdateDB   
{
my ($year, $make, $model, $color, $price) = @_; # pass those vars when you call the sub

my $dbh = DBI->connect("dbi:ODBC:cars", '', '', {RaiseError => 1})
or die "Couldn't connect: $DBI::errstr";

my $sql = qq{INSERT INTO cartable (year, make, model, color, price) VALUES(?,?,?,?,?)};

$sth = $dbh->prepare($sql);
$sth->execute($year, $make, $model, $color, $price);
$dbh->disconnect;
}



earachefl
Novice

Aug 10, 2009, 3:06 PM

Post #5 of 6 (6554 views)
Re: [FishMonger] Insert syntax question [In reply to] Can't Post

OK, the verification part of the code was directly from the book's example. Are you saying that checks of that type should be done while input is being read?
Perhaps it would make sense to read in the variables and repeat them to the operator for final verification before committing to the append operation?

And is the example you gave the preferred way to code the prepare/execute statements, i.e. using VALUES(?,?,?,?,?) in the prepare statement, then passing in the real values in the execute statement?


FishMonger
Veteran / Moderator

Aug 10, 2009, 3:32 PM

Post #6 of 6 (6549 views)
Re: [earachefl] Insert syntax question [In reply to] Can't Post


In Reply To
OK, the verification part of the code was directly from the book's example. Are you saying that checks of that type should be done while input is being read?
Perhaps it would make sense to read in the variables and repeat them to the operator for final verification before committing to the append operation?


The retrieval of the user input should be done in a loop such that when you read in the value, you apply whatever validation that is required and if that validation fails, then you ask them to reenter the data.


Quote
And is the example you gave the preferred way to code the prepare/execute statements, i.e. using VALUES(?,?,?,?,?) in the prepare statement, then passing in the real values in the execute statement?


That is the preferred method because it handles the quoting of sql special characters that may be in the vars, such as single quotes, and it helps to reduce sql injection. It's also an efficiency advantage when you're doing inserts within a loop, such as when importing data from a csv file. You only need to prepare the statement once outside of the loop and do the execute statement in the loop.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives