DBI and cursors?

ruby-talk
1

I'm writing a program to run through large numbers of records in a
database, classify them using a machine learning algorithm (rubysvm), and
store the results in the database by updating a single column on the
record.

Most of my database work thus far has been using DBI. Is it possible to
update each row through the cursor (similar to how JDBC can be used) in
DBI? Is there some other way of accessing the database that can do this?

--Ken

···

--
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/

2

Do you mean reusing a SQL statement to increase performance?

select_sql = 'select id, class_desc from products'
update_sql = 'update products SET category = ? where idn = ?'

dbh = DBI.connect('dbi:oci8:dbname', 'user','passwd');
select_sth = dbh.prepare(select_sql)
update_sth = dbh.prepare(update_sql)

select_sth.execute
select_sth.each do |row|

···

On 11/20/06, Ken Bloom <kbloom@gmail.com> wrote:

I'm writing a program to run through large numbers of records in a
database, classify them using a machine learning algorithm (rubysvm), and
store the results in the database by updating a single column on the
record.

Most of my database work thus far has been using DBI. Is it possible to
update each row through the cursor (similar to how JDBC can be used) in
DBI? Is there some other way of accessing the database that can do this?

--Ken

--
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/

--
Neil Kohl
nakohl@gmail.com

3

(Sorry, hit send accidentally before finishing ...)

Do you mean reusing a SQL statement/statement handle to increase
performance?

require 'dbi'

select_sql = 'select id, class_desc from products'
update_sql = 'update products SET category = ? where idn = ?'

dbh = DBI.connect('dbi:oci8:dbname', 'user','passwd');
select_sth = dbh.prepare(select_sql)
update_sth = dbh.prepare(update_sql)

select_sth.execute
select_sth.each do |row|
   category = calculate_category(row[1])
   update_sth.execute(row[0], category)
end

···

On 11/20/06, Ken Bloom <kbloom@gmail.com> wrote:
>
> I'm writing a program to run through large numbers of records in a
> database, classify them using a machine learning algorithm (rubysvm),
> and
> store the results in the database by updating a single column on the
> record.
>
> Most of my database work thus far has been using DBI. Is it possible to
> update each row through the cursor (similar to how JDBC can be used) in
> DBI? Is there some other way of accessing the database that can do this?
>
> --Ken
>
> --
> Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
> Department of Computer Science. Illinois Institute of Technology.
> http://www.iit.edu/~kbloom1/ <http://www.iit.edu/~kbloom1/&gt;
>

--
Neil Kohl
nakohl@gmail.com