Howdy:
I have a script where I connect from Oracle 9 to
PostgreSQL 7.2.x and insert new records. It seems
to work for about 200 or so records, but, fails
(I was able to capture this message)
[error]
Uncaught exception from user code:
Placeholder :0 invalid, placeholders must be >= 1 at
/usr/lib/perl5/site_p
erl/5.6.0/i386-linux/DBD/Pg.pm line 129.
DBD::Pg::db::prepare('DBI::db=HASH(0x83d88a0)', 'insert into
sys_ed_inp (^
JMBR_ID,^JCODE,^JPROC_1,^JPROC_2,^JPROC_3,^JD...', undef) called at
/usr/lib/perl5
/site_perl/5.6.0/i386-linux/DBI.pm line 1212
DBD::_::db::do('DBI::db=HASH(0x83d88a0)', 'insert into sys_ed_inp
(^JMBR_I
D,^JCODE,^JPROC_1,^JPROC_2,^JPROC_3,^JD...') called at
./insert_sys_ed_inp.pl line
95
Database handle destroyed without explicit disconnect at
/usr/lib/perl5/site_perl/
5.6.0/i386-linux/DBD/Pg.pm line 129.
[/error]
What is this talking about? I'm not sure I understand why it
failed after so many records. I *thought* it could be a record
was off or a column had some odd character or whatever, but
perhaps I don't see that? The table should accept NULLs by
default and the table structure on both DBs are the same.
This is the code
[snip code]
#!/usr/bin/perl -w
# created 17 Sep 03
# script to connect from Oracle via DBI to
# PostgreSQL and insert records into a table
#
use POSIX 'strftime';
use strict;
use warnings;
use diagnostics;
use DBI;
my $host='local';
my $sid='jessupic';
my $dbname='o_testdb';
my $username='joe';
my $password='joe_passwd';
my $datestr=strftime '%d%m%Y',localtime;
# connection options either works
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $username,
$password, { R
aiseError => 1 }) or die "Can not connect: $!";
if (!defined($dbh)) {exit;}
# test the SQL
# don't use semi-colons? wonder why ...
#
print "\nthis is for SYS_ED_INP table\n\n";
my $sql = "
SELECT
MBR_ID,
CODE,
PROC_1,
PROC_2,
PROC_3,
DIAG_1,
DIAG_2,
DIAG_3,
DIAG_4,
DIAG_5,
FROM_DT,
THRU_DT,
BILLINGPROVIDERNUM,
BILLINGPROVIDERNAME,
DIAGNOSISDESCRIPTION,
ADDED,
ID,
PRODUCT
from
SYS_ED_INP
" ;
# test the sql and prepare to use
# for Oracle
my $sth = $dbh->prepare($sql) or die "Error = ", DBI::errstr;
unless ($sth->execute) {
print"\n\tExecute failed for stmt:\n\t$sql\nError = ", DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
print "\nThis is to be inserted into PostgreSQL ...\n\n";
print "\nThis is the build time $datestr ...\n\n";
while (my (
$MBR_ID,
$CODE,
$PROC_1,
$PROC_2,
$PROC_3,
$DIAG_1,
$DIAG_2,
$DIAG_3,
$DIAG_4,
$DIAG_5,
$FROM_DT,
$THRU_DT,
$BILLINGPROVIDERNUM,
$BILLINGPROVIDERNAME,
$DIAGNOSISDESCRIPTION,
$ADDED,
$ID,
$PRODUCT
) =$sth->fetchrow) {
my $dbh_p=DBI->connect('dbi:Pg:dbname=test_db', 'joe')
or die "Can not connect: $!";
$dbh_p->do("insert into sys_ed_inp (
MBR_ID,
CODE,
PROC_1,
PROC_2,
PROC_3,
DIAG_1,
DIAG_2,
DIAG_3,
DIAG_4,
DIAG_5,
FROM_DT,
THRU_DT,
BILLINGPROVIDERNUM,
BILLINGPROVIDERNAME,
DIAGNOSISDESCRIPTION,
ADDED,
ID,
PRODUCT
) values (
'$MBR_ID',
'$CODE',
'$PROC_1',
'$PROC_2',
'$PROC_3',
'$DIAG_1',
'$DIAG_2',
'$DIAG_3',
'$DIAG_4',
'$DIAG_5',
'$FROM_DT',
'$THRU_DT',
'$BILLINGPROVIDERNUM',
'$BILLINGPROVIDERNAME',
'$DIAGNOSISDESCRIPTION',
'$ADDED',
'$ID',
'$PRODUCT')" );
$dbh_p->disconnect;
}
print "done with the sys_ed_inp program.\n\n";
$dbh->disconnect;
[/snip code]
Suggestions? TIA!
-X
