Perl Guru Forums: Perl Programming Help: DBI: Insert syntax question

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
Insert syntax question

earachefl
Novice

Aug 9, 2009, 1:54 PM

Post #1 of 6 (4936 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

Aug 10, 2009, 9:37 AM

Post #2 of 6 (4906 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 (4901 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

Aug 10, 2009, 2:42 PM

Post #4 of 6 (4900 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 (4897 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

Aug 10, 2009, 3:32 PM

Post #6 of 6 (4892 views)

Re: [earachefl] Insert syntax question [In reply to]	Can't Post

In Reply To

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)

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