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=-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
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/SQLExecute err=-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 memorynot 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 <[EMAIL PROTECTED]> 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 <[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. > -- -- 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 --
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 [EMAIL PROTECTED] 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. Peter Loo -Original Message- From: Ron Savage [mailto:[EMAIL PROTECTED] 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=-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 [EMAIL PROTECTED] 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. Peter Loo -Original Message- From: Jeffrey Seger [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 19, 2006 9:37 PM To: Peter Loo Cc: [EMAIL PROTECTED]; List - DBI users Subject: Re: Bad int8 external representation "" (SQL-HY000)(DBD: st_execute/SQLExecute err=-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 memorynot 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 <[EMAIL PROTECTED]> 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 <[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. > -- -- 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 = 'insert into table_x (column_y, column_z) values (?, 99)'; $sth = $dbh -> prepare($sql); $sth -> execute(undef); -- Cheers Ron Savage, [EMAIL PROTECTED] 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. Peter Loo Wolters Kluwer Health (602) 381-9553 -Original Message- From: Ron Savage [mailto:[EMAIL PROTECTED] 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=-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 = 'insert into table_x (column_y, column_z) values (?, 99)'; $sth = $dbh -> prepare($sql); $sth -> execute(undef); -- Cheers Ron Savage, [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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 = 'insert into table_x (column_y, column_z) values (?, 99)'; >> $sth = $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, [EMAIL PROTECTED] 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, [EMAIL PROTECTED] on 21/04/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company