DBI giving user input by place holders

valavanp has asked for the wisdom of the Perl Monks concerning the following question:

In DBI, I want to get the user input and fetch the rows given by user. I am getting only the last row of my table. But when i view it in MYSQL driver it shows all the rows. Can anyone tell where i am wrong. This is the coding which i executed.

use DBI;
use strict;
$a=<STDIN>;
chomp $a;
$b=<STDIN>;
chomp $b;

my $dbname="test";
my $dbh=DBI->connect("DBI:mysql:$dbname","root","", {RaiseError=>1, Au
+toCommit=>0});
$sth=$dbh->do("create table rec(name varchar(10), id int(10)");
$sth=$dbh->prepare("insert into rec(name, id) values(?, ?)");
$sth->execute($a, $b);
$sth->prepare("select * from rec");
my @row;
while (@row=$sth->fetchrow_array)
{
print "@row\n";
}
$sth->finish();
$dbh->disconnect();
exit;
[download]

Thanks for the help. Apologize if any errors.

20060711 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Comment on DBI giving user input by place holders Download Code

Replies are listed 'Best First'.
Re: DBI giving user input by place holders by gellyfish (Monsignor) on Jul 11, 2006 at 08:45 UTC
Firstly you probably don't want to use the variables $a and $b in your code like that, they have special meaning when used in a sort subroutine (which is why they don't provoke an error from 'strict'). The code above appears to only insert one row into the table anyway, I'm not actually sure why you think there should be more than one row. /J\	[reply]
Re^2: DBI giving user input by place holders by valavanp (Curate) on Jul 11, 2006 at 12:05 UTC
i want to insert more rows in my table. i am not able to do with the above coding. Can you please suggest how to do that.	[reply]
Re^3: DBI giving user input by place holders by john_oshea (Priest) on Jul 12, 2006 at 15:43 UTC
Assuming you want the same SQL executed, but with different values, you'll need to issue the `$sth->execute(...)` multiple times. You can do this either by manually repeating that line in your code, or put the line inside a loop of some sort. Hope that helps.	[reply] [d/l]
Re: DBI giving user input by place holders by Herkum (Parson) on Jul 11, 2006 at 13:12 UTC
You should create the table outside of your script, once the table is created, your done with it. Why try and create the table everytime you run the script? Without executing the code, I am also going to assume the script dies when attempt to create the table that already exists which causes problems. Create your table manually, and delete the do statement which creates the table from your code. Run that and see what happens.	[reply]
Re^2: DBI giving user input by place holders by ptum (Priest) on Jul 11, 2006 at 15:05 UTC
As Herkum has said, create your table outside your script (or drop it every time and recreate it). In order to add multiple rows to your table, you'll want the execute statement inside your while loop, passing it new arguments for each iteration of the execute. You'll probably want to check the success of the execute as well. No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply]