Re: $sth-execute(@$bindVars) is outputting an error
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote:
And what are the contents of the sql file?
Also, I know very little about NetezzaI'm going to assume that it
supports placeholders, but does the ODBC Driver for it?
On 4/2/06, Peter Loo [EMAIL PROTECTED] 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) = @_;
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote:
And what are the contents of the sql file?
Also, I know very little about NetezzaI'm going to assume
that it
supports placeholders, but does the ODBC Driver for it?
On 4/2/06, Peter Loo [EMAIL PROTECTED] 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
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 (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,
Re: $sth-execute(@$bindVars) is outputting an error
And what are the contents of the sql file?
Also, I know very little about NetezzaI'm going to assume that it
supports placeholders, but does the ODBC Driver for it?
On 4/2/06, Peter Loo [EMAIL PROTECTED] 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 (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
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 [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.
Re: $sth-execute(@$bindVars) is outputting an error
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
--
$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.
