# DBI and cursors?

**URL:** <https://www.ruby-forum.com/t/dbi-and-cursors/76707>\
**Category:** Ruby\
**Created:** [November 20, 2006, 4:36pm UTC](https://www.ruby-forum.com/t/dbi-and-cursors/76707 "2006-11-20T16:36:45Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [November 20, 2006, 4:36pm UTC](https://www.ruby-forum.com/t/dbi-and-cursors/76707/1 "2006-11-20T16:36:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [November 20, 2006, 5:04pm UTC](https://www.ruby-forum.com/t/dbi-and-cursors/76707/2 "2006-11-20T17:04:58Z")

</div>

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|

---

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [November 20, 2006, 5:09pm UTC](https://www.ruby-forum.com/t/dbi-and-cursors/76707/3 "2006-11-20T17:09:00Z")

</div>

(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