?
Solved

creating a database table in perl

Posted on 2007-10-06
5
Medium Priority
?
1,363 Views
Last Modified: 2012-05-05
i'm writing a program in perl to populate a database.  the code seems to be the way it should, but when i run it, the script does not create a new table.  it drops the table but doesn't create it.  i get an error message saying table does not exist when it gets to the insert statement.

#!C:\Perl\bin\perl

 #use strict;
         use DBI;
         my $dbh = DBI->connect( 'dbi:Oracle:XE',
                                 'username',
                                 'password',
                               ) || die "Database connection not made:

$DBI::errstr";

my $sql = qq {DROP TABLE CUSTOMER};
$dbh->do ($sql);

my $sql = qq { CREATE TABLE CUSTOMER (
            id INTEGER PRIMARY KEY,
            fname vARCHAR2(30),
            lname VACHAR2(30),
            street VARCHAR2(50),
            city VARCHAR2(30),
            state VARCHAR2(20),
            zip VARCHAR2(10),
            gender VARCHAR2(10)
            )};
$dbh->do ($sql);

my $insert_handle = $dbh->prepare_cached('INSERT INTO CUSTOMER (id,

fname,lname, street, city, state, zip, gender) VALUES (?,?,?,?,?,?,?,?) ');
      die "Couldn't prepare queries; aborting" unless defined

$insert_handle;

my @tuple;
my $index=1;

open(TEST, "custdata_CSV.csv");
while (<TEST>){
 
      @tuple = split(/,/,$_);
      
      print "$tuple[0]\n$tuple[1]\n$tuple[2]\n$tuple[3]\n$tuple[4]

\n$tuple[5]\n$tuple[6]\n";            

      $insert_handle->execute($index,$tuple[0],$tuple[1],$tuple[2],$tuple

[3],$tuple[4],$tuple[5],$tuple[6]);
      $index++;
 
}
close(TEST);
$dbh->disconnect();
0
Comment
Question by:jpking72
  • 2
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
BioI earned 600 total points
ID: 20027805
You have a typing error near "lname"
=>  lname VACHAR2(30)
should be
=>  lname VARCHAR2(30)
(you forgot an R)
0
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 400 total points
ID: 20027840
You should be using the strict and warnings pragmas.

Also, you should enable RaiseError on the DB connection.

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:XE', 'username', 'password',
                       { RaiseError => 1, PrintError => 0 })
            || die "Database connection not made: $DBI::errstr";

....
....
....

Your id field should be auto incremented instead of manually updating the index number.

You should always check the return status of an open call.

open(TEST, '<', "custdata_CSV.csv") || die "Can't open custdata_CSV.csv <$!>";
0
 

Author Comment

by:jpking72
ID: 20028211
how do you do auto increment in oracle?
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 20028361
>> how do you do auto increment in oracle?

I work with mysql but I assume the DBD::Oracle module supports the same syntax as DBD::mysql.

my $sql = qq { CREATE TABLE CUSTOMER (
                                     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                     ....
                                     ....
                                     ....
0
 

Author Comment

by:jpking72
ID: 20028791
I've seen websites that say it can't be done that way in Oracle.  check this out:

http://jen.fluxcapacitor.net/geek/autoincr.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question