Hi Jeffrey,
Here is the calling code:
Source database is Oracle using Oracle driver while the destination
database is Netezza using ODBC driver. When all fields are populated,
the same code appears to work.
($s_stmtType, $s_sqlString) = sub_readSQLFile($s_SQL);
$s_sth = sub_prepare($s_dbh, qq{$s_sqlString});
$s_sth = sub_execute($s_dbh, $s_sth);
$s_arrayref = sub_fetchall_arrayref($s_dbh, $s_sth);
$d_sqlString = sub_getTblCols($d_dbh, $d_dbDriver, d_tblName, "
");
$d_sth = sub_prepare($d_dbh, qq{$d_sqlString});
$d_sth = sub_execute($d_dbh, $d_sth);
$d_arrayref = sub_fetchall_arrayref($d_dbh, $d_sth);
$d_sqlString = sub_insert($d_tblName, $d_arrayref);
$d_sth = sub_prepare($d_dbh, qq{$d_sqlString});
foreach $arrayref (@{$s_arrayref}) {
if ($count >= $commitPoint) {
$d_dbh->commit || die "$DBI::errstr\n";
$count = 0;
}
$d_sth = sub_executeBind($d_dbh, $d_sth, $arrayref);
$count++;
}
}
sub_disconnect($s_dbh);
sub_disconnect($d_dbh);
The sub-routines that the above code call are:
sub sub_connect($$$$) {
my ($dbDriver, $dbName, $dbUser, $dbPass) = @_;
my $dbh = DBI->connect("dbi:$dbDriver:$dbName", "$dbUser",
"$dbPass",
{ PrintError => 1, RaiseError => 1 }
) || die "$DBI::errstr\n";
return($dbh);
}
sub sub_disconnect($) {
my ($dbh) = @_;
$dbh->disconnect || die "$dbh::errstr\n";
}
sub sub_execute($$) {
my ($dbh, $sth) = @_;
$sth->execute() || die "$dbh::errstr";
return($sth);
}
sub sub_prepare($$) {
my ($dbh, $sqlString) = @_;
print "sqlString is: $sqlString\n";
my $sth = $dbh->prepare(qq{$sqlString}) || die "$dbh::errstr\n";
return($sth);
}
sub sub_readSqlFile($) {
my ($sqlFile) = @_;
my $stmtType = "";
my $sqlString = "";
open(FH, "$sqlFile") || die "$!\n";
while (<FH>) {
chomp;
s/--.*$//;
next if /^\//;
next if /^(\s)*[Ss][Pp][Oo][Oo][Ll]/;
next if /^(\s)*--/;
next if /^(\s)*$/;
next if /^(\s)*[Ss][Ee][Tt]/;
next if /^(\s)*[Ee][Xx][Ii][Tt]/;
next if /^(\s)*[Qq][Uu][Ii][Tt]/;
print "$_\n";
$sqlString = "$sqlString" . "$_";
}
close(FH);
$stmtType = "insert" if $sqlString =~
/^(\s)+[Ii][Nn][Ss][Ee][Rr][Tt]/;
$stmtType = "select" if $sqlString =~
/^(\s)+[Ss][Ee][Ll][Ee][Cc][Tt]/;
$stmtType = "delete" if $sqlString =~
/^(\s)+[Dd][Ee][Ll][Ee][Tt][Ee]/;
$stmtType = "update" if $sqlString =~
/^(\s)+[Uu][Pp][Dd][Aa][Tt][Ee]/;
$stmtType = "plsql" if $sqlString =~ /^(\s)+[Bb][Ee][Gg][Ii][Nn]/;
$sqlString =~ s/(\s)*;(\s)*$//;
return($stmtType, qq{$sqlString});
}
sub sub_getTblCols($$$$) {
my ($dbh, $dbDriver, $tblName, $owner) = @_;
my ($sqlString);
if (ucfirst($dbDriver) eq "Oracle") {
$sqlString = "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE";
$sqlString = "$sqlString" . " TABLE_NAME = UPPER(\'$tblName\')";
$sqlString = "$sqlString" . " AND OWNER = UPPER(\'$owner\')";
$sqlString = "$sqlString" . " ORDER BY COLUMN_ID";
}
elsif (uc($dbDriver) eq "ODBC") {
$sqlString = "select column_name from all_tab_columns where";
$sqlString = "$sqlString" . " table_name = lower(\'$tblName\')";
$sqlString = "$sqlString" . " order by column_sequence_nbr";
}
else {
print "This database driver $dbDriver is not supported at this
time.\n";
exit(666);
}
return($sqlString);
}
sub sub_insert($$) {
local ($tblName, $columns) = @_;
my ($sqlString, $delimCols, $bindVars);
$delimCols = join(', ', @{$columns});
$bindVars = join ', ', ('?') x $#{$columns};
$sqlString = "insert into " . $tblName . " (" . $delimCols . ")
values" . " (" . $bindVars . ")";
return($sqlString);
}
sub sub_fetchallrows($$) {
my ($dbh, $sth) = @_;
my ($arrayref);
$arrayref = $sth->fetchall_arrayref() || die "$dbh::errstr\n";
return($arrayref);
}
sub sub_executeBind($$$) {
local ($dbh, $sth, $bindVar) = @_;
$sth->execute(@$bindVar) || die "$dbh::errstr";
return($sth);
}
--- Jeffrey Seger <[EMAIL PROTECTED]> wrote:
> In order to figure out what's going awry, I'd need to see some code.
> Preferably the minimum amount necessary to replicate the error.
>
> Also, do you have multiple statement handles attached to this dbh?
> Try
> printing $sth->{Statement} rather than $dbh->{Statement}. Are you
> checking
> for errors at the prepare?
>
>
>
> On 4/1/06, Peter Loo <[EMAIL PROTECTED]> wrote:
> >
> > Hi Martin,
> >
> > The value for $dbh->{Statemet} is as follows:
> >
> > insert into p_dlvrb_study_attributes (dlvrb_gid, study_gid,
> client_gid,
> > slsfc_gid, mkt_def_gid, mkt_def_desc, store_panl_gid,
> > study_anlys_typ_cde, extnd_lkbck_strt_dte, cohrt_strt_dte,
> > cohrt_end_dte, study_end_dte, lkbck_prd_days_nbr,
> study_prd_days_nbr,
> > dlvry_freq_typ_cde, dlvrb_error_flg_desc, std_err_thrhld_nbr,
> > std_err_ind, actl_dlvry_dte, dlvry_frmt_typ_desc, study_nam,
> > study_anlys_desc, slsfc_nam, client_long_nam, std_err_rsn_desc,
> > sob_clsfy_row_cnt, sob_prctr_demo_row_cnt, sob_pay_typ_row_cnt,
> > sob_sw_dtl_row_cnt, sob_prctr_row_cnt, sob_unq_ptnt_row_cnt,
> > pc_clsfy_row_cnt, pc_ptnt_demo_row_cnt, pc_prctr_row_cnt,
> > mkt_cnfgr_gid, itime_prjct_id, client_cntct_nam, dlvrb_nbr) values
> (?,
> > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?,
> > ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> >
> > Thanks.
> >
> > Peter
> >
> >
> > --- "Martin J. Evans" <[EMAIL PROTECTED]> wrote:
> >
> > > Peter Loo wrote:
> > >
> > > >Hi,
> > > >
> > > >I am trying to pass an array reference to $sth->execute and I am
> > > >getting the following error:
> > > >
> > > >DBD::ODBC::st execute failed: called with 38 bind variables when
> 0
> > > are
> > > >needed at /usr/local/apps/common/devl/bin/GlobalRoutines.pm line
> 42.
> > > >
> > > >The code I am using is as follows:
> > > >
> > > > sub sub_executeBind($$$) {
> > > > local ($dbh, $sth, $bindVar) = @_;
> > > > $sth->execute(@$bindVar) || die "$dbh::errstr";
> > > > return($sth);
> > > > }
> > > >
> > > >Will someone please help me understand this error message?
> > > >
> > > >Thanks in advance.
> > > >
> > > >Peter
> > > >
> > > >Peter Loo
> > > >Worldwide Consulting, Inc.
> > > >Phoenix, Arizona
> > > >U.S.A.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > This would suggest there are no parameters in the SQL (i.e. no ?
> as
> > > in "insert into table values (?)").
> > >
> > > What does $dbh->{Statement}return - it should be the SQL you
> > > are executuing.
> > >
> > > Martin
> > >
> > >
> > >
> >
> >
> > Peter Loo
> > Worldwide Consulting, Inc.
> > Phoenix, Arizona
> > U.S.A.
> >
>
>
>
> --
>
--------------------------------------------------------------------------------------------------------------
> The darkest places in hell are reserved for those who maintain their
> neutrality in times of moral crisis.
> Dante Alighieri (1265 - 1321)
>
> They who would give up an essential liberty for temporary security,
> deserve
> neither liberty or security.
> Benjamin Franklin
>
--------------------------------------------------------------------------------------------------------------
>
Peter Loo
Worldwide Consulting, Inc.
Phoenix, Arizona
U.S.A.
