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