Hello All,
I am attempting to INSERT into a table while reading in from a piped
delimited file and am getting the following error:
[unixODBC]ERROR: copy: line 1, Bad int8 external representation ""
(SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
The destination table (Netezza) layout is (38 columns):
Table "p_dlvrb_study_attributes"
Attribute | Type | Modifier | Default
Value
------------------------+------------------------+----------+-----------
----
dlvrb_gid | bigint | |
study_gid | bigint | |
client_gid | bigint | |
slsfc_gid | bigint | |
mkt_def_gid | bigint | |
mkt_def_desc | character varying(40) | |
store_panl_gid | bigint | |
study_anlys_typ_cde | character varying(5) | |
extnd_lkbck_strt_dte | date | |
cohrt_strt_dte | date | |
cohrt_end_dte | date | |
study_end_dte | date | |
lkbck_prd_days_nbr | numeric(3,0) | |
study_prd_days_nbr | numeric(3,0) | |
dlvry_freq_typ_cde | character(1) | |
dlvrb_error_flg_desc | character varying(100) | |
std_err_thrhld_nbr | numeric(8,4) | |
std_err_ind | character varying(1) | |
actl_dlvry_dte | date | |
dlvry_frmt_typ_desc | character varying(40) | |
study_nam | character varying(100) | |
study_anlys_desc | character varying(100) | |
slsfc_nam | character varying(250) | |
client_long_nam | character varying(100) | |
std_err_rsn_desc | character varying(100) | |
sob_clsfy_row_cnt | bigint | |
sob_prctr_demo_row_cnt | bigint | |
sob_pay_typ_row_cnt | bigint | |
sob_sw_dtl_row_cnt | bigint | |
sob_prctr_row_cnt | bigint | |
sob_unq_ptnt_row_cnt | bigint | |
pc_clsfy_row_cnt | bigint | |
pc_ptnt_demo_row_cnt | bigint | |
pc_prctr_row_cnt | bigint | |
mkt_cnfgr_gid | bigint | |
itime_prjct_id | character varying(25) | |
client_cntct_nam | character varying(40) | |
dlvrb_nbr | smallint | |
Distributed on random: (round-robin)
and the file record I am trying to INSERT is:
28526|205|12|1581|1027|Int 2
P2||PC|2002-10-04|2005-01-01|2005-02-28|2005-03-03|730|30|M|||1||F|Int 2
P2|PC|XXXXXX XXXXXXXXXX XXXXX|XXXXXX, XXX.|||||||||||1530|100005|UI
Integration Test 205|1
This is very odd because I have another process where I get the above
data directly from a table in another database using "fetchall_arrayref"
and dynamically creating an INSERT statement using the table definition
of the detination table and doing the INSERT and it works. But when I
read in the same data from a piped delimited file and doing the INSERT,
I get an error.
The INSERT statement that is dynamically created as shown below and the
column count and the bind variables count appears to be correct.
insert into pl_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 (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)
I have been at it for awhile and hope that someone can assist.
Here is my code:
sub sub_executeBindFromInputFile($$$$) {
print STDERR "\n***** sub_executeBindFromInputFile() *****\n\n";
local ($dbDriver, $dbh, $sth, $fh) = @_;
print STDERR "DATABASE DRIVER IS: $dbDriver\n";
print STDERR "DATABASE HANDLE IS: $dbh\n";
print STDERR "STATEMENT HANDLE IS: $sth\n";
print STDERR "FILE HANDLE IS: $fh\n";
my ($bindVar, @row);
my $count = 0;
$COMMITPOINT ||= 10000;
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)
Thanks everyone in advance.
Sincerely,
Peter
This E-mail message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply E-mail, and destroy all copies of
the original message.
