Trying to insert a row into a oracle table 1

[jcarrott]

I am using the insert command and I get the error,

Undefined subroutine &main::X12LINE called at ghxEDM_sh line 112, <X12> line 1.

The code is,

elsif ($temp[0] eq "PID") {
$description = $temp[5];
## save the line record
insert into jcarrott.X12LINE($company, $invoice,
$invoice_dte, $po_number, $line_nbr, $item,
$ven_item, $description, $qty, $uom, $price_cd,
$price);
commit;
}

The table was created with a SQL statement and it is there.

Is there a special way to do inserts in Perl?

 

[travs69]

Using DBI is the easiest.. it is a general wrapper around all your SQL interactions, you will also need DBD::Oracle (or what ever DB you will access).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[jcarrott]

how do I change the code to use this wrapper?

 

[travs69]

Go read this

http://search.cpan.org/~timb/DBI-1.609/DBI.pm

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[jcarrott]

I am feeling dumb and I think I am getting dumber.

I added,

DBD::Oracle;

To the top of the code, then I changed my code to,

elsif ($temp[0] eq "PID") {
$description = $temp[5];
## save the line record
my $sth = $dbh->prepare(q{
INSERT INTO jcarrott.X12LINE(company, invoice, invoice_dte,
po_number, line_nbr, item, ven_item, description,
qty, uom, price_cd, price) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
}) or die $dbh->errstr;
while (<>) {
chomp;
my ($company, $invoice, $invoice_dte,
$po_number, $line_nbr, $item, $ven_item, $description,
$qty, $uom, $price_cd, $price) = split /,/;
$sth->execute($company, $invoice, $invoice_dte,
$po_number, $line_nbr, $item, $ven_item, $description,
$qty, $uom, $price_cd, $price) or die $dbh->errstr;
}
$dbh->commit or die $dbh->errstr;
}

to try and insert the data into the table.

The error I got was,

Can't call method "prepare" on an undefined value at ghxEDM_sh line 113, <X12> line 1.

Is it really this hard to write a line to a table?

 

[travs69]

No... it's really simple but you have to get into the correct syntax.

You have to open your connection to the DB, specifing the host, username, and password to begin with.

You should really read the perldoc on DBI. It has everything you need.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[jcarrott]

I developed this code in Windows, and then moved it into UNIX where it runs for real. I don't have to log into the database once I am in UNIX, because I have already logged in and I am the owner of the instance that the table is under.
I did go to the document you sent me the link for and it does have a ton of information about everything.
They even had examples and a chart that related the examples to different databases. They just forgot to state "this is example 1". There are examples, and then there are details. I couldn't tell one from the other.
I work in Perl from time to time, but not enough to get comfortable with it.
I know you said this is simple to do, and I guess if you work with all the time it is.
I am sorry, but I don't see it. Can you help me?

 

[travs69]

I know you say that you don't have to log in but you still need to create the connection.

You need something like, if you don't need a username,password you can leave that out.

use DBI;
my $dbh = DBI->connect('DBI:Oracle:host=servername;sid=DBName', 'username', 'password') or die "Can't connect to db:$!\n";
my $query = qq~put your insert query here~;
$dbh->do($query) or die "Can't do $query: $!\n";
$dbh->disconnect();

if you are doing a lot of the same statement you can prepare it, then do a bunch of executes on it. "do" does a prepare and execute all in one.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[jcarrott]

This has been extremely helpful. I have one question, I don't see a commit. Is the commit part of the DO?

 

[travs69]

AutoCommit is on by default, if you want transactions you will have to turn it off and then do a commit.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;