DBI Oracle error

ruby-talk
1

My (possibly wrong) initial guess would be a precision error of some
sort in conjunction with Ruby's .to_f. Double check the the column
types (presumably a NUMBER) and see what the precision is set to. The
column precision *is* set, right? You may need to tinker a bit.
Stepping through the debugger and manually inspecting the float values
might be in order as well.

HTH.

Dan

···

-----Original Message-----
From: Graham [mailto:gandalfmeister@gmail.com]
Sent: Tuesday, June 07, 2005 8:25 AM
To: ruby-talk ML
Subject: DBI Oracle error

I've written a small upload utility to take a 10k+ line log
file and upload it to a database. It works fine (on the
whole) except that I get

Error code: 1722
Error message: ORA-01722: invalid number

every so often. The data rows around the offending line are
01/06/05,00:43:39,0,6.67,30.3,-99.9,-99.9,-99.9,
01/06/05,00:43:49,0,6.67,30.3,-99.9,-99.9,-99.9, <- this line
fails 01/06/05,00:43:59,0,6.67,30.3,-99.9,-99.9,-99.9,
(i.e. virtually identical data)

I'm using a prepared statement and my only thoughts are that
there is an issue with the driver somehow. e.g. after a
certain number of inserts or memory usage the driver fails.

Any ideas how I debug this, as inserting "broken" rows
manually via SQL*Plus succeeds every time (so it isn't the
data). Anyone have problems with the DBI functionality?

require 'dbi'
dbh = DBI.connect('DBI:Oracle:tpdev', 'username',
'password'); sth = dbh.prepare("INSERT INTO EFFLUENT_LOG
(datetime, suspect, flow, temp, ph) VALUES(to_date(?,
'DD/MM/YY HH24:MI:SS'), ?, ?, ?, ?)"); .... some loop stuff..
sth.execute((date + " " + time), val, flow.to_f, temp.to_f,
ph.to_f) ... more loop stuff

Not rocket science.. but I'm confused. Is it a driver issue? Graham

2

Berger, Daniel wrote:

My (possibly wrong) initial guess would be a precision error of some
sort in conjunction with Ruby's .to_f. Double check the the column
types (presumably a NUMBER) and see what the precision is set to. The
column precision *is* set, right?

Nope - not explicitly. The column is just defined as NUMBER. If this was the problem - how come
a) almost identical data lines either side work correctly
b) running the data thorough again will correctly insert the offending row into the database (other rows fail to insert due to filing a uniqueness constraint on the datetime column)

I'm perplexed. I don't have a real debugger for Ruby.. just the built in one (which is a bit painful to use).
Graham