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 Netezza....I'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 $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.
> > > >
> > >
> > >
> > >
> > > --
> > >
> >
> >
>
--------------------------------------------------------------------------------------------------------------
> > > 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.
