On Wed, 4 Jun 2003 11:16:23 -0700 (PDT) Robert <[EMAIL PROTECTED]> wrote:
> #!C:\perl\ActivePerl\Perl\bin\perl -w
> use DBI;
> use strict;
> use DBI qw(:sql_types);
> DBI->trace( 2, 'dbitrace.log' );
# If RaiseError is set, connect() won't return on errors so the
# 'or die()' part is irrelevant.
# I frequently don't set RaiseError in the connect() so I can
# tailor my error message on connect. Then I set $dbh->{RaiseError}
# to 1 immediately after.
> my $dbh1 = DBI->connect( "dbi:ODBC:db2data", "test", "test" ) or die "
> Can't make 1st database connect: $DBI::errstr\n";
> my $dbh2 = DBI->connect("dbi:Oracle:Oradb", "test", "test", { AutoComm
> it => 0, RaiseError => 1,} ) or die "Can't make 2nd database connect:
> $DBI::errstr\n";
>
> my $sel1 = $dbh1->prepare("Select company,comp_number from test2");
> $sel1->execute();
# Bind variables are much faster than array assignment
my ( $comp_name, $comp_num );
$sel1 -> bind_columns( \( $comp_name, $comp_num ) );
# Do all your prepare()s outside the loop and use placeholders
# for values that might change.
my $sel2 = $dbh2 -> prepare(
"SELECT COUNT(*) FROM cover_tab WHERE comp_number = ?" );
# Listing the columns explicitly protects against schema changes.
my $ins = $dbh2 -> prepare(
"INSERT INTO cover_tab " .
"( col1, col7, col9, col10, col11, col14, col17 )" .
"VALUES ( cover_tab_seq.nextval, 'Outstanding', 100, ?, 3, 42, 2003 )" );
#> my @db2data;
#> while ( @db2data = $sel1->fetchrow_array() ) {
#> print "Company is $db2data[0] with Company_number $db2data[1]\n";
#> my $sel2 = $dbh2->prepare("Select comp_number from cover_tab where com
#> p_number='$db2data[1]'");
#> $sel2->execute;
#> if ($sel2->rows == 0) {
# Note that $sel2->rows can not be depended on to know the number
# of rows in this context.
my $comp_rows;
while ( $sel1 -> fetch ) {
print "Company is $comp_name with Company_number $comp_num\n";
$comp_rows = $dbh2 -> selectrow_array( $sel2, {}, $comp_num );
if ( ! $comp_rows ) {
> print "inserting Company_number $db2data[1] for company $db2data[0]\n"
> ;
#> my $ins = $dbh2->prepare("insert into cover_tab
#> values(cover_tab_seq.nextval,null,null,null,null,null,'Outstanding',null,
#> 100,'$db2data[1]',3,null,null,42,null,null,2003,null");
#> $ins->execute;
# You might want to use placeholders for some other values,
# the year for example.
$ins -> execute( $comp_num );
# Why commit every row? I normally commit every thousand or so.
# If you only expect a couple hundred, just commit after the loop.
> $dbh2->commit;
> }
> else { print "$db2data[1] for Company $db2data[0] already exists\n" }
> }
>
> $dbh1->disconnect();
> $dbh2->disconnect();
--
Mac :})
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
