On Jan 7, 12:31 am, [EMAIL PROTECTED] 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
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
