PERL Beginners - insert and query a db

Author

insert and query a db

bbrecht56@gmail.com

2008-01-07, 7:01 pm

Hi
I'm trying to write a script that reads the data below, parses it, and
then inserts it into the "info" table.

1:Karl Heiz:1:444-555-6666:441-551-6661:5:1:1:1
2:Helmut Schmidt:1:222-333-1234:222-555-4321:2:1:1:1
3:Udo Lindenberg:3:111-555-1234:111-556-4321:3:0:0:1
4:Peter Frech:2:333-111-1234:3330-555-4321:2:0:1:0
5:Peter Schneider:6:333-234-1212:333-321-2323:0:0:1:1

I created the table "info" with the following columns:

COLUMN TYPE ATTRIBUTES
=========== =========== ===========
id int PRIMARY KEY
name varchar(64)
grade int
phone varchar(16)
cell varchar(16)
house int
car int

then inserted the data to the table. When I try to read the data from
the table and display the fields on the screen, it displays only the
first 2 elements of each record (id and name) and the following error
message for all other fields:
ID: 2
Name: Helmut Schmidt
Use of uninitialized value in printf at ./bonnerRunde.pl line 124.
Grade: 0
Use of uninitialized value in printf at ./bonnerRunde.pl line 125.
Phone:
Use of uninitialized value in printf at ./bonnerRunde.pl line 126.
Cell:
Use of uninitialized value in printf at ./bonnerRunde.pl line 127.
house: 0
Use of uninitialized value in printf at ./bonnerRunde.pl line 128.
car: 0

Help is needed and highly appreciated . I would like also to know if
this is a good way to read and parse data then insert them in a table?
or there is a better way?

Thanks for your help

Berti

The script:
========
use warnings;
use DBI;

$driver = "DBI:DBM:TEST_DB";
$user = "user";
$password = "user1234";

#----------------------------------#
# Create the table "info" #
#----------------------------------#
$table = "info";
$dbh = DBI->connect($driver, $user, $password) || die "Couldn't
connect to database: " . DBI->errstr();

$sqlstmt = "create TABLE $table (id int PRIMARY KEY, name varchar(64),
grade int, phone varchar(16), cell varchar(16), house int, car int)";

$dbh->do($sqlstmt);

#-----------------------------------------------------------------#
# read, parse then insert data into "info" table #
#-----------------------------------------------------------------#

$stp = 1;
while (<DATA> ){

#---------------------------------------#
# Some control statements #
#---------------------------------------#
print "STEP-$stp\n";
($id, $name, $grade, $phone, $cell, $house, $car) = split(":");
print "\$id = $id \n";
print "\$name = $name \n";
print "\$grade = $grade\n";
print "\$phone = $phone\n";
print "\$cell = $cell \n";
print "\$house = $house\n";
print "\$car = $car \n";
print "\n";

$sqlInsertStmt = "insert into $table
(id, name, grade, phone, cell, house, car)
values ($id, $name, $grade, $phone, $cell, $house,
$car)";
$dbh -> do ($sqlInsertStmt);
$stp++;
}

#----------------------#
# run the query #
#----------------------#
$queryStmt = "select * from $table";
$sth = $dbh -> prepare ($queryStmt);
$sth -> execute() || die "ERROR: " . DBI -> errstr();

#-----------------------------------------------#
# get and display number of rows #
#-----------------------------------------------#

$rn = $sth->rows;
print "\n";
print "#------------------------------------------------#\n";
print "# Number of rows found in the table \"$table\" ( $rn ) #\n";
print "#------------------------------------------------#\n";

#----------------------------------------------#
# Format and display the fields #
#----------------------------------------------#
while (my @result = $sth -> fetchrow_array()){
#print "@result\n";
printf "%s %2d\n", "ID: ", $result[0];
printf "%s %s \n", "Name: ", $result[1];
printf "%s %d \n", "Grade: ", $result[2];
printf "%s %s \n", "Phone: ", $result[3];
printf "%s %s \n", "Cell: ", $result[4];
printf "%s %d \n", "house: ", $result[5];
printf "%s %d \n", "car: ", $result[6];
print "\n";
}

$sth -> finish();
$dbh -> disconnect();

John Moon

2008-01-07, 7:01 pm

[>>] ...

Please try (not tested). Placeholders help me a lot:

my $sth_insert =3D $dbh->prepare('Insert into info (id, name, grade,
phone, cell, house, car)
values (?,?,?,?,?,?,?)')
or die DBI->errstr;
while (<DATA> ){

#---------------------------------------#
# Some control statements #
#---------------------------------------#
print "STEP-$stp\n";
($id, $name, $grade, $phone, $cell, $house, $car) =3D split(":");
print "\$id =3D $id \n";
[>>] ...
print "\$car =3D $car \n";
print "\n";

[>>]die DBI->errstr unless $sth_insert->execute($id, $name,
$grade, $phone, $cell, $house, $car);

$stp++;
}
[>>] ...
=20

bbrecht56@gmail.com

2008-01-11, 8:01 am

On Jan 7, 1:50 pm, John.M...@dms.myflorida.com (John Moon) wrote:
> [>>] ...
>
> Please try (not tested). Placeholders help me a lot:
>
> my $sth_insert = $dbh->prepare('Insert into info (id, name, grade,
> phone, cell, house, car)
> values (?,?,?,?,?,?,?)')
> or die DBI->errstr;
> while (<DATA> ){
>
> #---------------------------------------#
> # Some control statements #
> #---------------------------------------#
> print "STEP-$stp\n";
> ($id, $name, $grade, $phone, $cell, $house, $car) = split(":");
> print "\$id = $id \n";
> [>>] ...
> print "\$car = $car \n";
> print "\n";
>
> [>>]die DBI->errstr unless $sth_insert->execute($id, $name,
> $grade, $phone, $cell, $house, $car);
>
> $stp++;}
>
> [>>] ...

Hi John,

Thanks for your reply.
I tried your suggestion with the Placeholder:

my $sqlInsertStmt = "insert into $table
(id, name, grade, phone, cell, house, car)
values (?, ?, ?, ?, ?, ?, ?)";

$sth = $dbh -> prepare ($sqlInsertStmt) || die DBI -> errstr;
die DBI -> errstr unless $sth -> execute ($id, $name, $grade, $phone,
$cell, $house, $car);

unfortunately, I get the same error message when I run:

$queryStmt = "select * from $table";
$sth = $dbh -> prepare ($queryStmt);
$sth -> execute() || die "ERROR: " . DBI -> errstr();

then try to format and print the content of the fields:

while (my @result = $sth -> fetchrow_array()){
#print "@result\n";
printf "%s %2d\n", "ID: ", $result[0];
printf "%s %s \n", "Name: ", $result[1];
printf "%s %d \n", "Grade: ", $result[2];
printf "%s %s \n", "Phone: ", $result[3];
printf "%s %s \n", "Cell: ", $result[4];
printf "%s %d \n", "house: ", $result[5];
printf "%s %d \n", "car: ", $result[6];
print "\n";

Only the content of the first 2 fields is displayed:
ID: 2
Name: Helmut Schmidt
Use of uninitialized value in printf at ./parseDataIntoDBTable.pl line
75.
Grade: 0
Use of uninitialized value in printf at ./parseDataIntoDBTable.pl line
76.
Phone:
Use of uninitialized value in printf at ./parseDataIntoDBTable.pl line
77.
Cell:
Use of uninitialized value in printf at ./parseDataIntoDBTable.pl line
78.
house: 0
Use of uninitialized value in printf at ./parseDataIntoDBTable.pl line
79.
car: 0

Any other idea?
Is there any other way to display the content of the table?

Thanks for your help

Berti

Tom Phoenix

2008-01-11, 7:01 pm

On Jan 10, 2008 10:50 PM, <bbrecht56@gmail.com> wrote:

> Only the content of the first 2 fields is displayed:

In your shoes, I would check that 'use warnings' and 'use strict' are
in place, and other basics, then I'd start debugging at the database.
Check out the tracing facility, documented in the DBI manpage. Trace
level 2 is probably enough for you to see what's happening from the
db's point of view. Is the db getting the right data? Is it giving the
answers you think it should?

http://search.cpan.org/~timb/DBI/DBI.pm#TRACING

It can also be helpful to step through your code in the Perl debugger.
You can stop at any statement to see what values your variables really
have. That's documented in the perldebug manpage.

http://perldoc.perl.org/perldebug.html

Good luck with it!

--Tom Phoenix
Stonehenge Perl Training

Tri Trinh

2008-01-16, 7:02 pm

On Jan 7, 12:31 am, bbrech...@gmail.com wrote:
> Hi
> I'm trying to write a script that reads the data below, parses it, and
> then inserts it into the "info" table.
>
> 1:Karl Heiz:1:444-555-6666:441-551-6661:5:1:1:1
> 2:Helmut Schmidt:1:222-333-1234:222-555-4321:2:1:1:1
> 3:Udo Lindenberg:3:111-555-1234:111-556-4321:3:0:0:1
> 4:Peter Frech:2:333-111-1234:3330-555-4321:2:0:1:0
> 5:Peter Schneider:6:333-234-1212:333-321-2323:0:0:1:1
>
> I created the table "info" with the following columns:
>
> COLUMN TYPE ATTRIBUTES
> =========== =========== ===========
> id int PRIMARY KEY
> name varchar(64)
> grade int
> phone varchar(16)
> cell varchar(16)
> house int
> car int
>
> then inserted the data to the table. When I try to read the data from
> the table and display the fields on the screen, it displays only the
> first 2 elements of each record (id and name) and the following error
> message for all other fields:
> ID: 2
> Name: Helmut Schmidt
> Use of uninitialized value in printf at ./bonnerRunde.pl line 124.
> Grade: 0
> Use of uninitialized value in printf at ./bonnerRunde.pl line 125.
> Phone:
> Use of uninitialized value in printf at ./bonnerRunde.pl line 126.
> Cell:
> Use of uninitialized value in printf at ./bonnerRunde.pl line 127.
> house: 0
> Use of uninitialized value in printf at ./bonnerRunde.pl line 128.
> car: 0
>
> Help is needed and highly appreciated . I would like also to know if
> this is a good way to read and parse data then insert them in a table?
> or there is a better way?
>
> Thanks for your help
>
> Berti
>
> The script:
> ========
> use warnings;
> use DBI;
>
> $driver = "DBI:DBM:TEST_DB";
> $user = "user";
> $password = "user1234";
>
> #----------------------------------#
> # Create the table "info" #
> #----------------------------------#
> $table = "info";
> $dbh = DBI->connect($driver, $user, $password) || die "Couldn't
> connect to database: " . DBI->errstr();
>
> $sqlstmt = "create TABLE $table (id int PRIMARY KEY, name varchar(64),
> grade int, phone varchar(16), cell varchar(16), house int, car int)";
>
> $dbh->do($sqlstmt);
>
> #-----------------------------------------------------------------#
> # read, parse then insert data into "info" table #
> #-----------------------------------------------------------------#
>
> $stp = 1;
> while (<DATA> ){
>
> #---------------------------------------#
> # Some control statements #
> #---------------------------------------#
> print "STEP-$stp\n";
> ($id, $name, $grade, $phone, $cell, $house, $car) = split(":");
> print "\$id = $id \n";
> print "\$name = $name \n";
> print "\$grade = $grade\n";
> print "\$phone = $phone\n";
> print "\$cell = $cell \n";
> print "\$house = $house\n";
> print "\$car = $car \n";
> print "\n";
>
> $sqlInsertStmt = "insert into $table
> (id, name, grade, phone, cell, house, car)
> values ($id, $name, $grade, $phone, $cell, $house,
> $car)";
> $dbh -> do ($sqlInsertStmt);
> $stp++;
>
> }
>
> #----------------------#
> # run the query #
> #----------------------#
> $queryStmt = "select * from $table";
> $sth = $dbh -> prepare ($queryStmt);
> $sth -> execute() || die "ERROR: " . DBI -> errstr();
>
> #-----------------------------------------------#
> # get and display number of rows #
> #-----------------------------------------------#
>
> $rn = $sth->rows;
> print "\n";
> print "#------------------------------------------------#\n";
> print "# Number of rows found in the table \"$table\" ( $rn ) #\n";
> print "#------------------------------------------------#\n";
>
> #----------------------------------------------#
> # Format and display the fields #
> #----------------------------------------------#
> while (my @result = $sth -> fetchrow_array()){
> #print "@result\n";
> printf "%s %2d\n", "ID: ", $result[0];
> printf "%s %s \n", "Name: ", $result[1];
> printf "%s %d \n", "Grade: ", $result[2];
> printf "%s %s \n", "Phone: ", $result[3];
> printf "%s %s \n", "Cell: ", $result[4];
> printf "%s %d \n", "house: ", $result[5];
> printf "%s %d \n", "car: ", $result[6];
> print "\n";
>
> }
>
> $sth -> finish();
> $dbh -> disconnect();

Those unitialized values just means that the data aren't inserted
correctly into the database. When you try to fetch the data, values
comes out as null and perl throws out an error. The first thing is
trying to see whether you inserted the value correctly by dumping your
tablespace. Here is an updated code to what you have earlier.

$queryStmt = "select * from $table";
$sth = $dbh -> prepare ($queryStmt);
$sth -> execute() || die "ERROR: " . $sth->errstr();
$sth->dump_results if $sth->{NUM_OF_FIELDS};

The statement handle that calls the dump_results function should tell
you whether you inserted the data correctly. Out put as follows:

'1', 'Karl Heiz', undef, undef, undef, undef, undef
'2', 'Helmut Schmidt', undef, undef, undef, undef, undef
'3', 'Udo Lindenberg', undef, undef, undef, undef, undef
'4', 'Peter Frech', undef, undef, undef, undef, undef
'5', 'Peter Schneider', undef, undef, undef, undef, undef
5 rows

Tri

Sponsored Links