Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

------_=_NextPart_001_01C66318.2CDD810B
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello All,
=20
I am attempting to INSERT into a table while reading in from a piped
delimited file and am getting the following error:
=20
[unixODBC]ERROR: copy: line 1, Bad int8 external representation ""
(SQL-HY000)(DBD: st_execute/SQLExecute err=3D-1)
=20
The destination table (Netezza) layout is (38 columns):
=20
Table "p_dlvrb_study_attributes"
Attribute | Type | Modifier | Default
Value=20
------------------------+------------------------+---------- +-----------
----
dlvrb_gid | bigint | |=20
study_gid | bigint | |=20
client_gid | bigint | |=20
slsfc_gid | bigint | |=20
mkt_def_gid | bigint | |=20
mkt_def_desc | character varying(40) | |=20
store_panl_gid | bigint | |=20
study_anlys_typ_cde | character varying(5) | |=20
extnd_lkbck_strt_dte | date | |=20
cohrt_strt_dte | date | |=20
cohrt_end_dte | date | |=20
study_end_dte | date | |=20
lkbck_prd_days_nbr | numeric(3,0) | |=20
study_prd_days_nbr | numeric(3,0) | |=20
dlvry_freq_typ_cde | character(1) | |=20
dlvrb_error_flg_desc | character varying(100) | |=20
std_err_thrhld_nbr | numeric(8,4) | |=20
std_err_ind | character varying(1) | |=20
actl_dlvry_dte | date | |=20
dlvry_frmt_typ_desc | character varying(40) | |=20
study_nam | character varying(100) | |=20
study_anlys_desc | character varying(100) | |=20
slsfc_nam | character varying(250) | |=20
client_long_nam | character varying(100) | |=20
std_err_rsn_desc | character varying(100) | |=20
sob_clsfy_row_cnt | bigint | |=20
sob_prctr_demo_row_cnt | bigint | |=20
sob_pay_typ_row_cnt | bigint | |=20
sob_sw_dtl_row_cnt | bigint | |=20
sob_prctr_row_cnt | bigint | |=20
sob_unq_ptnt_row_cnt | bigint | |=20
pc_clsfy_row_cnt | bigint | |=20
pc_ptnt_demo_row_cnt | bigint | |=20
pc_prctr_row_cnt | bigint | |=20
mkt_cnfgr_gid | bigint | |=20
itime_prjct_id | character varying(25) | |=20
client_cntct_nam | character varying(40) | |=20
dlvrb_nbr | smallint | |=20
Distributed on random: (round-robin)
=20
and the file record I am trying to INSERT is:
=20
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
=20
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.
=20
The INSERT statement that is dynamically created as shown below and the
column count and the bind variables count appears to be correct.
=20
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 (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)
=20
I have been at it for awhile and hope that someone can assist. =20
=20
Here is my code:
=20
sub sub_executeBindFromInputFile($$$$) {
print STDERR "\n***** sub_executeBindFromInputFile() *****\n\n";
local ($dbDriver, $dbh, $sth, $fh) =3D @_;
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 =3D 0;
$COMMITPOINT ||=3D 10000;
while (<$fh>) {
chomp;
@row =3D split(/\|/, $_);
unless ($sth->execute(@row)) {
$MESSAGE =3D $DBI::errstr;
$STATUS =3D $FAILURE;
sub_exit("Y", $dbDriver, $dbh);
}
if ($destIsOracle) {
$count++;
if ($count >=3D $COMMITPOINT) {
$s_dbh->commit || die "$DBI::errstr\n";
$count =3D 0;
}
}
}
print STDERR "RETURN STATEMENT HANDLE IS: $sth\n\n";
return($sth);
} #<===3D (End of sub_executeBindFromInputFile)
=20
Thanks everyone in advance.
=20
Sincerely,
=20
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.

------_=_NextPart_001_01C66318.2CDD810B--

Re: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

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=3D-1)

Line 1, eh? First attempt to insert data? Did you try chomp-ing the line?

--
Cheers
Ron Savage, ron@savage.net.au on 19/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

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 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, ron@savage.net.au 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/SQLExecute err=-1)

------=_Part_2562_22450084.1145507820570
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

It's a numeric field and you are trying to insert an empty string rather
than undef (equivalent to null).

When you queried the other database and had the values in memory....not
written to a file... they stayed undef. When you split the row from the
file, you have empty strings rather than undef or null.

You also need to be chomping.

On 4/19/06, Peter Loo wrote:
>
> 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 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=3D-1)
> >
> > Line 1, eh? First attempt to insert data? Did you try chomp-ing the
> > line?
> >
> > --
> > Cheers
> > Ron Savage, ron@savage.net.au 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.
>



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

------=_Part_2562_22450084.1145507820570--

Re: Bad int8 external representation "" (SQL-HY000)(DBD:st_execute/SQLExecute err=-1)

On Wed, 19 Apr 2006 20:56:39 -0700 (PDT), Peter Loo wrote:

Hi Peter

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

OK.

You do realize a blank field (empty string) in Perl is /not/ a database null,
right?

To get a null use Perl's undef.

> Yes, I am only testing with one record.

I guessed chomp may be relevant as soon as I thought it died on the first line.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Hi Ron,

Do you mean plug in the work "undef" before doing the INSERT?

Thanks.
=20
Peter Loo

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Wednesday, April 19, 2006 9:39 PM
To: List - DBI users
Subject: Re: Bad int8 external representation "" (SQL-HY000)(DBD:
st_execute/SQLExecute err=3D-1)

On Wed, 19 Apr 2006 20:56:39 -0700 (PDT), Peter Loo wrote:

Hi Peter

> 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=20
> thing is that the column it is going into is allowed for NULL values.=20
> I don't know what else to do, but will certainly give chomp a try.

OK.

You do realize a blank field (empty string) in Perl is /not/ a database
null, right?

To get a null use Perl's undef.

> Yes, I am only testing with one record.

I guessed chomp may be relevant as soon as I thought it died on the
first line.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html




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.


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.

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Hi Jeffrey,

Thanks for the clear explanation. Now it makes sense. I will try
various things with chomp and undef.

Thanks again.
=20
Peter Loo

-----Original Message-----
From: Jeffrey Seger [mailto:jseger@gmail.com]=20
Sent: Wednesday, April 19, 2006 9:37 PM
To: Peter Loo
Cc: ron@savage.net.au; List - DBI users
Subject: Re: Bad int8 external representation "" (SQL-HY000)(DBD:
st_execute/SQLExecute err=3D-1)

It's a numeric field and you are trying to insert an empty string rather
than undef (equivalent to null).

When you queried the other database and had the values in memory....not
written to a file... they stayed undef. When you split the row from the
file, you have empty strings rather than undef or null.

You also need to be chomping.

On 4/19/06, Peter Loo wrote:
>
> 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=20
> 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 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=3D-1)
> >
> > Line 1, eh? First attempt to insert data? Did you try chomp-ing the=20
> > line?
> >
> > --
> > Cheers
> > Ron Savage, ron@savage.net.au on 19/04/2006=20
> > http://savage.net.au/index.html Let the record show: Microsoft is=20
> > not an Australian company
> >
> >
> >
>
>
> 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

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


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.


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.

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

On Thu, 20 Apr 2006 07:51:24 -0700, Loo, Peter # PHX wrote:

Hi Peter

> Do you mean plug in the work "undef" before doing the INSERT?

Yes. Off the top of my head (add error checks!):

$sql =3D 'insert into table_x (column_y, column_z) values (?, 99)';
$sth =3D $dbh -> prepare($sql);
$sth -> execute(undef);
--
Cheers
Ron Savage, ron@savage.net.au on 21/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Hi Ron,

Yes, thanks. I have it working now. Now I am working on the UPDATE
from a SELECT. I am having problem with char columns. I am working on
a routine that will identify the columns that require quotes and
dynamically plug them in before doing the update.

Thanks for all your help.
=20
Peter Loo
Wolters Kluwer Health
(602) 381-9553

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Thursday, April 20, 2006 4:14 PM
To: List - DBI users
Subject: RE: Bad int8 external representation "" (SQL-HY000)(DBD:
st_execute/SQLExecute err=3D-1)

On Thu, 20 Apr 2006 07:51:24 -0700, Loo, Peter # PHX wrote:

Hi Peter

> Do you mean plug in the work "undef" before doing the INSERT?

Yes. Off the top of my head (add error checks!):

$sql =3D 'insert into table_x (column_y, column_z) values (?, 99)'; $sth =
=3D
$dbh -> prepare($sql); $sth -> execute(undef);
--
Cheers
Ron Savage, ron@savage.net.au on 21/04/2006
http://savage.net.au/index.html Let the record show: Microsoft is not an
Australian company




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.


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.

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Ron Savage said:
> On Thu, 20 Apr 2006 07:51:24 -0700, Loo, Peter # PHX wrote:
>
> Hi Peter
>
>> Do you mean plug in the work "undef" before doing the INSERT?
>
> Yes. Off the top of my head (add error checks!):
>
> $sql = 'insert into table_x (column_y, column_z) values (?, 99)';
> $sth = $dbh -> prepare($sql);
> $sth -> execute(undef);
> --
> Cheers
> Ron Savage, ron@savage.net.au on 21/04/2006
> http://savage.net.au/index.html
> Let the record show: Microsoft is not an Australian company
Minior change: should it not be:
$sth -> execute((undef));


-------

William R. Mussatto, Senior Systems Engineer
http://www.csz.com
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

On Thu, 20 Apr 2006 16:33:14 -0700 (PDT), William R. Mussatto wrote:

Hi William

>> $sql =3D 'insert into table_x (column_y, column_z) values (?, 99)';
>> $sth =3D $dbh -> prepare($sql); $sth -> execute(undef); -- Cheers

> Minior change: should it not be:
> $sth -> execute((undef));

Well, no. Firstly I believe the original code is correct, and secondly that=

won't make any difference :-)!

Perhaps you were thinking of trying to force DBI to quote the value (just
guessing)? But I used a placeholder (the ?) to get DBI to do this /without=
me
having to hold DBI's hand/ as to what needs quoting.

Long Live Placeholders!

--
Cheers
Ron Savage, ron@savage.net.au on 21/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

On Thu, 20 Apr 2006 16:21:29 -0700, Loo, Peter # PHX wrote:

Hi Peter

> Yes, thanks. I have it working now. Now I am working on the
> UPDATE from a SELECT. I am having problem with char columns. I am
> working on a routine that will identify the columns that require
> quotes and dynamically plug them in before doing the update.

You're wasting you time, and unless you're infallible, will probably write=
code
which fails one day.

Use placeholders, as I said in my other post a moment ago.

--
Cheers
Ron Savage, ron@savage.net.au on 21/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company