Perl DBI and Inserting

[savanm]

Hi all,

Here I want to insert a row into a table.

Am using Mysql.How to use insert query in PERLDBI

Here is my sample Code

use strict;
use DBI;
 
my $dbh = DBI->connect("DBI:mysql:database=perldbi;host=localhost","root","");
 
#$dbh->do("CREATE TABLE asdf (name VARCHAR(21),phone VARCHAR(21))");
#$dbh->do("DROP TABLE asdf");
 
my ($userid, $pass);
my $sth = $dbh->prepare("SELECT * FROM foo1");
$sth->execute();
$sth->bind_columns(\$userid, \$pass);
while ($sth->fetch) {
    print "$userid        $pass\n";
}
$sth->finish();
 
my $sthC = $dbh->prepare("insert TABLE (usrid,pass) values (navi,sava)");
# or die "Couldn't prepare: $DBI::errstr";
$sthC->execute('navi', 'sava');
# or die "Couldn't execute: $DBI::errstr";
$dbh->disconnect();
 
 

Thanks

[KevinADC]

It should be covered in the Mysql driver documentation:

cpan DBD::Mysql

[savanm]

Hi Kevin,

I got the solution,

print "Enter user id\n";
 my $usr = <STDIN>;    
 chomp($usr);
 print "Enter Password\n";
 my $pas = <STDIN>;
 chomp($pas);
 
my $d=$dbh->do("INSERT INTO foo1 values('$usr','$pas')");
print "Insert $d Rows\n";
 

Thaks for ur help

[KevinADC]

Very good, you're welcome.

[miller]

Hi savanm,

A better way to do that insert statement would be to use placeholders like this:

my $sthC = $dbh->prepare("INSERT INTO foo1 (usrid, pass) VALUES (?, ?)");
my $count = $sthC->execute('navi', 'sava') or die $dbh->errstr;
 

You were close to this method in your original solution. You simply hadn't put in the question marks.

- Miller