Re: Bad int8 external representation (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
No Ron. I did not chomp the record, but I am quite certain that it is not the issue. The field it is complaining about is blank. The odd thing is that the column it is going into is allowed for NULL values. I don't know what else to do, but will certainly give chomp a try. Yes, I am only testing with one record. Thanks. Peter --- Ron Savage [EMAIL PROTECTED] wrote: On Tue, 18 Apr 2006 11:44:50 -0700, Loo, Peter # PHX wrote: Hi Peter [unixODBC]ERROR: copy: line 1, Bad int8 external representation (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) Line 1, eh? First attempt to insert data? Did you try chomp-ing the line? -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/04/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company Peter Loo Worldwide Consulting, Inc. Phoenix, Arizona U.S.A.
Re: Bad int8 external representation (SQL-HY000) (DBD: st_execute/SQL_Execute err=-1
Hi Ron, Yes, I should have sent more details, but I was just hoping that someone has seen this and have a quick answer. Peter --- Ron Savage [EMAIL PROTECTED] wrote: On Fri, 14 Apr 2006 12:13:40 -0700 (PDT), Peter Loo wrote: Hi Peter Bad int8 external representation (SQL-HY000) (DBD: st_execute/SQL_Execute err=-1 You need better diagnostic skills. Print the data before you insert it. Print a message saying that it has been inserted. That way, we know what data failed to be inserted, /and we can see it/! -- Cheers Ron Savage, [EMAIL PROTECTED] on 15/04/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company Peter Loo Worldwide Consulting, Inc. Phoenix, Arizona U.S.A.
Bad int8 external representation (SQL-HY000) (DBD: st_execute/SQL_Execute err=-1
Hi All,
I am reading in a pipe delimited file and using the while loop and
$sth-execute, I am trying to insert into a table, but am getting the
following error:
Bad int8 external representation (SQL-HY000) (DBD:
st_execute/SQL_Execute err=-1
Here is my sub-routine:
sub sub_executeBindFromInputFile() {
local ($dbDriver, $dbh, $sth, $fh) = @_;
my ($bindVar, @row);
my $count = 0;
$delimiter ||= |;
$COMMITPOINT ||= 1;
while ($fh) {
chomp;
@row = split(/\|/, $_);
unless ($sth-execute(@row)) {
$MESSAGE = $DBI::errstr;
$STATUS = $FAILURE;
sub_exit(Y, $dbDriver, $dbh);
}
if ($destIsOracle) {
$count++;
if ($count = $COMMITPOINT) {
$s_dbh-commit || die $DBI::errstr\n;
$count = 0;
}
}
}
print STDERR RETURN STATEMENT HANDLE IS: $sth\n\n;
return($sth);
} #=== (End of sub_executeBindFromInputFile)
When I handle the insert directly out of a select, it appears to work.
Can you please tell me what I am doing wrong?
Thanks.
Peter
Peter Loo
Worldwide Consulting, Inc.
Phoenix, Arizona
U.S.A.
Re: dump_results
Hi Jeffrey,
But why does this work when I select the rows into memory and insert
them to the same table? I must be missing something.
Thanks.
Peter
--- Jeffrey Seger [EMAIL PROTECTED] wrote:
Single quoted strings do not do backslash substitution (except for
\\
and \'). You meant $lsep = \n;
Also, how can I force it so that the output does not have the
quotes
for every field?
DBI::neat() won't quote the value if it knows it's a number.
Unfortunately, I'm not sure how to let it know it's a number in
this
case. Sorry.
I'm guessing that this is happening when you hit a an empty string
that you
are trying to insert into a numeric field. Convert empty strings in
@row
into undef and it should work.
foreach my $i (0..$#row){$row[$i] = undef if $row[$i] eq ''}
The only drawback to this is if you actually want empty strings in
your
character fields instead of nulls.
--
--
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
Our lives begin to end the day we become silent about things that
matter.
Martin Luther King
Our government can't be bought. The oil companies will never give it
up at
any price.
My opinion
--
Peter Loo
Worldwide Consulting, Inc.
Phoenix, Arizona
U.S.A.
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
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.
$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: Stored Procedure (arguments)
Hi Michael,
1) $DICEpackage is a store procedure we use internally.
2) The reason that $dbh-prepare is in loop to dynamically handle multiple
tables.
3) I have tried bind variables with commas and yet it didn't work.
4) {} is a style I had adopted to identify that a variable was previously
declared.
6) I will give DBI-trace a try after I have figured out how it works.
Thanks.
Peter
-Original Message-
From: Michael A. Chase [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 12, 2001 8:38 PM
To: Loo, Peter # PHX; [EMAIL PROTECTED]
Subject: Re: Stored Procedure (arguments)
1. What is the value in $DICEpackage?
2. You should call $dbh-prepare() outside the loop. Keep both prepared
handles in separate variables.
3. The bind variables (:1, :2, :3) definitely need to be separated by commas
(,) in both SQL statements.
4. You don't need either the quotes or braces in the first argument to
either $sth-execute(). Just use $tableName in that position.
5. The braces are also not needed in ${tableName} in the print statement.
6. Try adding DBI-trace(2,"file_name"); before this section to see what's
really happening.
--
Mac :})
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
- Original Message -
From: "Loo, Peter # PHX" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 12, 2001 3:15 PM
Subject: Stored Procedure (arguments)
Can someone please tell me what this is all about?
DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number
(DBD
ERROR: OCIBindByName) at sma_run_aggs.pl line 172.
Here is my syntax:
foreach $tableName (@tableList) {
print STDERR "\n\nDropping indexes (${tableName}).\n";
$sth = $dbh-prepare("BEGIN $DICEpackage.DROP_INDEXES(:1:2:3);
END;");
$sth-execute("${tableName}", "ALL", "FALSE"); === Line 172
sub_dbms_output_errors($SearchString, $dbh);
print STDERR "Truncating table (${tableName}).\n";
$sth = $dbh-prepare("BEGIN $DICEpackage.TRUNCATE_TABLE(:1:2);
END;");
$sth-execute("${tableName}", "ALL");
sub_dbms_output_errors($SearchString, $dbh);
}
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
