$sth->execute(@$bindVars) is outputting an error

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.

Re: $sth->execute(@$bindVars) is outputting an error

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

Re: $sth->execute(@$bindVars) is outputting an error

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" 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.

Re: $sth->execute(@$bindVars) is outputting an error

------=_Part_7898_32585486.1143957495149
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 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" 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) =3D @_;
> > > $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
------------------------------------------------------------ ---------------=
-----------------------------------

------=_Part_7898_32585486.1143957495149--

Re: $sth->execute(@$bindVars) is outputting an error

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 () {
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 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 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" 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.

Re: $sth->execute(@$bindVars) is outputting an error

------=_Part_873_26633262.1144007334617
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

And what are the contents of the sql file?

Also, I know very little about Netezza....I'm going to assume that it
supports placeholders, but does the ODBC Driver for it?

On 4/2/06, Peter Loo wrote:
>
>
> 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) =3D sub_readSQLFile($s_SQL);
> $s_sth =3D sub_prepare($s_dbh, qq{$s_sqlString});
> $s_sth =3D sub_execute($s_dbh, $s_sth);
> $s_arrayref =3D sub_fetchall_arrayref($s_dbh, $s_sth);
>
> $d_sqlString =3D sub_getTblCols($d_dbh, $d_dbDriver, d_tblName, "
> ");
> $d_sth =3D sub_prepare($d_dbh, qq{$d_sqlString});
> $d_sth =3D sub_execute($d_dbh, $d_sth);
> $d_arrayref =3D sub_fetchall_arrayref($d_dbh, $d_sth);
> $d_sqlString =3D sub_insert($d_tblName, $d_arrayref);
> $d_sth =3D sub_prepare($d_dbh, qq{$d_sqlString});
> foreach $arrayref (@{$s_arrayref}) {
> if ($count >=3D $commitPoint) {
> $d_dbh->commit || die "$DBI::errstr\n";
> $count =3D 0;
> }
> $d_sth =3D 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) =3D @_;
> my $dbh =3D DBI->connect("dbi:$dbDriver:$dbName", "$dbUser",
> "$dbPass",
> { PrintError =3D> 1, RaiseError =3D> 1 }
> ) || die "$DBI::errstr\n";
> return($dbh);
> }
>
> sub sub_disconnect($) {
> my ($dbh) =3D @_;
> $dbh->disconnect || die "$dbh::errstr\n";
> }
>
> sub sub_execute($$) {
> my ($dbh, $sth) =3D @_;
> $sth->execute() || die "$dbh::errstr";
> return($sth);
> }
>
> sub sub_prepare($$) {
> my ($dbh, $sqlString) =3D @_;
> print "sqlString is: $sqlString\n";
> my $sth =3D $dbh->prepare(qq{$sqlString}) || die "$dbh::errstr\n";
> return($sth);
> }
>
> sub sub_readSqlFile($) {
> my ($sqlFile) =3D @_;
> my $stmtType =3D "";
> my $sqlString =3D "";
> open(FH, "$sqlFile") || die "$!\n";
> while () {
> 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 =3D "$sqlString" . "$_";
> }
> close(FH);
> $stmtType =3D "insert" if $sqlString =3D~
> /^(\s)+[Ii][Nn][Ss][Ee][Rr][Tt]/;
> $stmtType =3D "select" if $sqlString =3D~
> /^(\s)+[Ss][Ee][Ll][Ee][Cc][Tt]/;
> $stmtType =3D "delete" if $sqlString =3D~
> /^(\s)+[Dd][Ee][Ll][Ee][Tt][Ee]/;
> $stmtType =3D "update" if $sqlString =3D~
> /^(\s)+[Uu][Pp][Dd][Aa][Tt][Ee]/;
> $stmtType =3D "plsql" if $sqlString =3D~ /^(\s)+[Bb][Ee][Gg][Ii][Nn]/=
;
> $sqlString =3D~ s/(\s)*;(\s)*$//;
> return($stmtType, qq{$sqlString});
> }
>
> sub sub_getTblCols($$$$) {
> my ($dbh, $dbDriver, $tblName, $owner) =3D @_;
> my ($sqlString);
> if (ucfirst($dbDriver) eq "Oracle") {
> $sqlString =3D "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE";
> $sqlString =3D "$sqlString" . " TABLE_NAME =3D UPPER(\'$tblName\')"=
;
> $sqlString =3D "$sqlString" . " AND OWNER =3D UPPER(\'$owner\')";
> $sqlString =3D "$sqlString" . " ORDER BY COLUMN_ID";
> }
> elsif (uc($dbDriver) eq "ODBC") {
> $sqlString =3D "select column_name from all_tab_columns where";
> $sqlString =3D "$sqlString" . " table_name =3D lower(\'$tblName\')"=
;
> $sqlString =3D "$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) =3D @_;
> my ($sqlString, $delimCols, $bindVars);
> $delimCols =3D join(', ', @{$columns});
> $bindVars =3D join ', ', ('?') x $#{$columns};
> $sqlString =3D "insert into " . $tblName . " (" . $delimCols . ")
> values" . " (" . $bindVars . ")";
> return($sqlString);
> }
>
> sub sub_fetchallrows($$) {
> my ($dbh, $sth) =3D @_;
> my ($arrayref);
> $arrayref =3D $sth->fetchall_arrayref() || die "$dbh::errstr\n";
> return($arrayref);
> }
>
> sub sub_executeBind($$$) {
> local ($dbh, $sth, $bindVar) =3D @_;
> $sth->execute(@$bindVar) || die "$dbh::errstr";
> return($sth);
> }
>
> --- Jeffrey Seger 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 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" 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) =3D @_;
> > > > > $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.
>



--
------------------------------------------------------------ ---------------=
-----------------------------------
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
------------------------------------------------------------ ---------------=
-----------------------------------

------=_Part_873_26633262.1144007334617--

Re: $sth->execute(@$bindVars) is outputting an error

------=_Part_6627_14019355.1144119692038
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I'm posting this back to the list in case anyone else wants to see it.

First thing I see is that the number of ? placeholders doesn't appear to be
right. I whittled it down to a 2 column table and used your sub_insert to
build an insert statement:

sub sub_insert($$) {
my ($tblName, $columns) =3D @_;
my ($sqlString, $delimCols, $bindVars);
$delimCols =3D join(', ', @{$columns});
$bindVars =3D join ', ', ('?') x $#{$columns};
$sqlString =3D "insert into " . $tblName . " (" . $delimCols . ")values"=
.
" (" . $bindVars . ")";
return($sqlString);
}

yielded:
insert into table1 (COLUMN1, COLUMN2)values (?)

This change fixed that:

$bindVars =3D join ', ', ('?') x scalar(@$columns);
#$bindVars =3D join ', ', ('?') x $#{$columns};

However, this would not have explained why you got "38 found when 0
expected".
So I suspect that either Netezza doesn't support unnamed bind params, or th=
e
ODBC driver that you are using doesn't I have no way of knowing which.

So I suggest that you build your insert statement with named bind variables
instead:

sub sub_insert2($$) {
my ($tblName, $columns) =3D @_;
my ($sqlString, $delimCols, $bindVars);
$delimCols =3D join(', ', @{$columns});
my @bindlist =3D ();
foreach my $col(@$columns){
push @bindlist, ':' . $col;
}
$bindVars =3D join ', ', @bindlist;
$sqlString =3D "insert into " . $tblName . " (" . $delimCols . ")values"=
.
" (" . $bindVars . ")";
return($sqlString);
}

which yielded this:

insert into table1 (COLUMN1, COLUMN2)values (:COLUMN1, :COLUMN2)

Then you need to actually bind the values:

while (my @row =3D $sth_select->fetchrow_array){
for (0..$#row){
$sth_insert->bind_param(':' . $cols[$_], $row[$_]);
}
$sth_insert->execute;
}



A couple of other suggestions:

1) when someone offers help and asks you to send the minimum amount of code
to replicate the issue, please whittle it down as much as you can. It make=
s
people more inclined to help and you learn more (and possibly solve the
problem on your own).

2) read a book on regexes.
$stmtType =3D "insert" if $sqlString =3D~ /^(\s)+[Ii][Nn][Ss][Ee][Rr][Tt]/=
;
would be better written and more readable as:
$stmtType =3D "insert" if $sqlString =3D~ /^\s+insert/i;


On 4/2/06, Peter Loo wrote:
>
> Hi Jeffrey,
>
> It is a simple SELECT statement with the following columns from one
> table in Oracle then I am using Perl DBI ODBC driver to do an INSERT
> into the same table in Netezza. Netezza is a SQL server runs on Linux
> and support 100% SQL standard. What I a trying to do is using
> all_tab_columns view in Netezza to dynamically create a list of columns
> for the INSERT statement. I have verified that it is in fact the
> correct list of columns.
>
> dlvrb_gid, study_gid, client_gid, slsfc_gid, mkt_def_gid, kt_def_desc,
> store_panl_gid, study_anlys_typ_cde, extnd_lkbck_strt_dte,
> ohrt_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
>
> --- Jeffrey Seger wrote:
>
> > And what are the contents of the sql file?
> >
> > Also, I know very little about Netezza....I'm going to assume that it
> > supports placeholders, but does the ODBC Driver for it?
> >
> > On 4/2/06, Peter Loo wrote:
> > >
> > >
> > > 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) =3D sub_readSQLFile($s_SQL);
> > > $s_sth =3D sub_prepare($s_dbh, qq{$s_sqlString});
> > > $s_sth =3D sub_execute($s_dbh, $s_sth);
> > > $s_arrayref =3D sub_fetchall_arrayref($s_dbh, $s_sth);
> > >
> > > $d_sqlString =3D sub_getTblCols($d_dbh, $d_dbDriver,
> > d_tblName, "
> > > ");
> > > $d_sth =3D sub_prepare($d_dbh, qq{$d_sqlString});
> > > $d_sth =3D sub_execute($d_dbh, $d_sth);
> > > $d_arrayref =3D sub_fetchall_arrayref($d_dbh, $d_sth);
> > > $d_sqlString =3D sub_insert($d_tblName, $d_arrayref);
> > > $d_sth =3D sub_prepare($d_dbh, qq{$d_sqlString});
> > > foreach $arrayref (@{$s_arrayref}) {
> > > if ($count >=3D $commitPoint) {
> > > $d_dbh->commit || die "$DBI::errstr\n";
> > > $count =3D 0;
> > > }
> > > $d_sth =3D 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) =3D @_;
> > > my $dbh =3D DBI->connect("dbi:$dbDriver:$dbName", "$dbUser",
> > > "$dbPass",
> > > { PrintError =3D> 1, RaiseError =3D> 1 }
> > > ) || die "$DBI::errstr\n";
> > > return($dbh);
> > > }
> > >
> > > sub sub_disconnect($) {
> > > my ($dbh) =3D @_;
> > > $dbh->disconnect || die "$dbh::errstr\n";
> > > }
> > >
> > > sub sub_execute($$) {
> > > my ($dbh, $sth) =3D @_;
> > > $sth->execute() || die "$dbh::errstr";
> > > return($sth);
> > > }
> > >
> > > sub sub_prepare($$) {
> > > my ($dbh, $sqlString) =3D @_;
> > > print "sqlString is: $sqlString\n";
> > > my $sth =3D $dbh->prepare(qq{$sqlString}) || die
> > "$dbh::errstr\n";
> > > return($sth);
> > > }
> > >
> > > sub sub_readSqlFile($) {
> > > my ($sqlFile) =3D @_;
> > > my $stmtType =3D "";
> > > my $sqlString =3D "";
> > > open(FH, "$sqlFile") || die "$!\n";
> > > while () {
> > > 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 =3D "$sqlString" . "$_";
> > > }
> > > close(FH);
> > > $stmtType =3D "insert" if $sqlString =3D~
> > > /^(\s)+[Ii][Nn][Ss][Ee][Rr][Tt]/;
> > > $stmtType =3D "select" if $sqlString =3D~
> > > /^(\s)+[Ss][Ee][Ll][Ee][Cc][Tt]/;
> > > $stmtType =3D "delete" if $sqlString =3D~
> > > /^(\s)+[Dd][Ee][Ll][Ee][Tt][Ee]/;
> > > $stmtType =3D "update" if $sqlString =3D~
> > > /^(\s)+[Uu][Pp][Dd][Aa][Tt][Ee]/;
> > > $stmtType =3D "plsql" if $sqlString =3D~
> > /^(\s)+[Bb][Ee][Gg][Ii][Nn]/;
> > > $sqlString =3D~ s/(\s)*;(\s)*$//;
> > > return($stmtType, qq{$sqlString});
> > > }
> > >
> > > sub sub_getTblCols($$$$) {
> > > my ($dbh, $dbDriver, $tblName, $owner) =3D @_;
> > > my ($sqlString);
> > > if (ucfirst($dbDriver) eq "Oracle") {
> > > $sqlString =3D "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE";
> > > $sqlString =3D "$sqlString" . " TABLE_NAME =3D
> > UPPER(\'$tblName\')";
> > > $sqlString =3D "$sqlString" . " AND OWNER =3D UPPER(\'$owner\')=
";
> > > $sqlString =3D "$sqlString" . " ORDER BY COLUMN_ID";
> > > }
> > > elsif (uc($dbDriver) eq "ODBC") {
> > > $sqlString =3D "select column_name from all_tab_columns where";
> > > $sqlString =3D "$sqlString" . " table_name =3D
> > lower(\'$tblName\')";
> > > $sqlString =3D "$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) =3D @_;
> > > my ($sqlString, $delimCols, $bindVars);
> > > $delimCols =3D join(', ', @{$columns});
> > > $bindVars =3D join ', ', ('?') x $#{$columns};
> > > $sqlString =3D "insert into " . $tblName . " (" . $delimCols . ")
> > > values" . " (" . $bindVars . ")";
> > > return($sqlString);
> > > }
> > >
> > > sub sub_fetchallrows($$) {
> > > my ($dbh, $sth) =3D @_;
> > > my ($arrayref);
> > > $arrayref =3D $sth->fetchall_arrayref() || die "$dbh::errstr\n";
> > > return($arrayref);
> > > }
> > >
> > > sub sub_executeBind($$$) {
> > > local ($dbh, $sth, $bindVar) =3D @_;
> > > $sth->execute(@$bindVar) || die "$dbh::errstr";
> > > return($sth);
> > > }
> > >
> > > --- Jeffrey Seger 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 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" 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) =3D @_;
> > > > > > > $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.
> > >
> >
> >
> >
> > --
> >
>
> ------------------------------------------------------------ -------------=
-------------------------------------
> > 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.
>



--
------------------------------------------------------------ ---------------=
-----------------------------------
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
------------------------------------------------------------ ---------------=
-----------------------------------

------=_Part_6627_14019355.1144119692038--

Re: $sth->execute(@$bindVars) is outputting an error

Hi Jeffrey,

I have found the culprit. It is in the calling program. Instead of
using $d_sth like the original program, someone made a change and
didn't tell me. I was looking at the copy instead of the one that was
having the problem.

$d_dbh = sub_prepare($d_dbh, $sqlString);

I have made some changes to the programs as you wonderful folks had
suggested.

Thanks everyone for your help.

Sincerely,

Peter


--- Jeffrey Seger wrote:

> I'm posting this back to the list in case anyone else wants to see
> it.
>
> First thing I see is that the number of ? placeholders doesn't appear
> to be
> right. I whittled it down to a 2 column table and used your
> sub_insert to
> build an insert statement:
>
> sub sub_insert($$) {
> my ($tblName, $columns) = @_;
> my ($sqlString, $delimCols, $bindVars);
> $delimCols = join(', ', @{$columns});
> $bindVars = join ', ', ('?') x $#{$columns};
> $sqlString = "insert into " . $tblName . " (" . $delimCols .
> ")values" .
> " (" . $bindVars . ")";
> return($sqlString);
> }
>
> yielded:
> insert into table1 (COLUMN1, COLUMN2)values (?)
>
> This change fixed that:
>
> $bindVars = join ', ', ('?') x scalar(@$columns);
> #$bindVars = join ', ', ('?') x $#{$columns};
>
> However, this would not have explained why you got "38 found when 0
> expected".
> So I suspect that either Netezza doesn't support unnamed bind params,
> or the
> ODBC driver that you are using doesn't I have no way of knowing
> which.
>
> So I suggest that you build your insert statement with named bind
> variables
> instead:
>
> sub sub_insert2($$) {
> my ($tblName, $columns) = @_;
> my ($sqlString, $delimCols, $bindVars);
> $delimCols = join(', ', @{$columns});
> my @bindlist = ();
> foreach my $col(@$columns){
> push @bindlist, ':' . $col;
> }
> $bindVars = join ', ', @bindlist;
> $sqlString = "insert into " . $tblName . " (" . $delimCols .
> ")values" .
> " (" . $bindVars . ")";
> return($sqlString);
> }
>
> which yielded this:
>
> insert into table1 (COLUMN1, COLUMN2)values (:COLUMN1, :COLUMN2)
>
> Then you need to actually bind the values:
>
> while (my @row = $sth_select->fetchrow_array){
> for (0..$#row){
> $sth_insert->bind_param(':' . $cols[$_], $row[$_]);
> }
> $sth_insert->execute;
> }
>
>
>
> A couple of other suggestions:
>
> 1) when someone offers help and asks you to send the minimum amount
> of code
> to replicate the issue, please whittle it down as much as you can.
> It makes
> people more inclined to help and you learn more (and possibly solve
> the
> problem on your own).
>
> 2) read a book on regexes.
> $stmtType = "insert" if $sqlString =~
> /^(\s)+[Ii][Nn][Ss][Ee][Rr][Tt]/;
> would be better written and more readable as:
> $stmtType = "insert" if $sqlString =~ /^\s+insert/i;
>
>
> On 4/2/06, Peter Loo wrote:
> >
> > Hi Jeffrey,
> >
> > It is a simple SELECT statement with the following columns from one
> > table in Oracle then I am using Perl DBI ODBC driver to do an
> INSERT
> > into the same table in Netezza. Netezza is a SQL server runs on
> Linux
> > and support 100% SQL standard. What I a trying to do is using
> > all_tab_columns view in Netezza to dynamically create a list of
> columns
> > for the INSERT statement. I have verified that it is in fact the
> > correct list of columns.
> >
> > dlvrb_gid, study_gid, client_gid, slsfc_gid, mkt_def_gid,
> kt_def_desc,
> > store_panl_gid, study_anlys_typ_cde, extnd_lkbck_strt_dte,
> > ohrt_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
> >
> > --- Jeffrey Seger wrote:
> >
> > > And what are the contents of the sql file?
> > >
> > > Also, I know very little about Netezza....I'm going to assume
> that it
> > > supports placeholders, but does the ODBC Driver for it?
> > >
> > > On 4/2/06, Peter Loo wrote:
> > > >
> > > >
> > > > 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 () {
> > > > 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 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 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" 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.
> > > >
> > >
> > >
> > >
> > > --
> > >
> >
> >
>
------------------------------------------------------------ --------------------------------------------------
> > > 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.
> >
>
>
>
> --
>
------------------------------------------------------------ --------------------------------------------------
> 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.