Dbi doesnt work well with sqlite3?

Ruby
1

Hi, guys, I want to share my discovery with you. If you found I am
wrong, pleas point it out:)

if you use dbi to generate a prepared statement by doing:

sth=dbh.prepare(“INSERT INTO WORDS VALUES(?,?,?)”)

(0…1).each{|i|
sth.execute(“1”,“2”,“3”)
}

it will give you a /sqlite3-ruby-1.3.2/lib/sqlite3/statement.rb:41:in
`bind_param’: library routine called out of sequence
(SQLite3::MisuseException).
I have no idea what the hell is that…

My Guess is it’s a compatible issue… So I bypassed the ‘dbi’, and use
sqlite3-ruby driver directly, and it works fine:

require ‘rubygems’
require ‘sqlite3’
db=SQLite3::Database.new(“shit.db”)
db.execute(%q{
CREATE TABLE PEOPLE(
NAME CHAR(20),
ADDRESS CHAR(29) );})
st=db.prepare(“INSERT INTO PEOPLE VALUES(?,?)”)
(0…100).each do |i|
st.execute(i.to_s,i.to_s)
end

I’m using MacOSX(10.6.5),

Is there anybody using dbi???
Is that very useful???I know it should be useful…but the only way I
can make my program work is to bypass dbi…

Help!!!

2

Thanks brabuhr :slight_smile:

Just like what you said. It will happen when you have more than one
record to insert.

3

On Tue, Nov 30, 2010 at 12:43 PM, Tianshuo D. [email protected]
wrote:

it will give you a /sqlite3-ruby-1.3.2/lib/sqlite3/statement.rb:41:in
`bind_param’: library routine called out of sequence (SQLite3::MisuseException).

Is there anybody using dbi???
Is that very useful???I know it should be useful…but the only way I can make
my program work is to bypass dbi…

I’m not using DBI, but based on an online tutorial for DBI:Mysql I
made this test program:

require “dbi”

dbh = DBI.connect(“DBI:SQLite3:memory:”)

dbh.do(“DROP TABLE IF EXISTS EMPLOYEE”)
dbh.do(“CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )”)

sth = dbh.prepare( “INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE,
SEX, INCOME) VALUES (?, ?, ?, ?, ?)”)
sth.execute(‘John’, ‘Poul’, 25, ‘M’, 2300)
sth.execute(‘Zara’, ‘Ali’, 17, ‘F’, 1000)
sth.finish

sth = dbh.prepare(“SELECT * FROM EMPLOYEE WHERE INCOME > ?”)
sth.execute(1000)

sth.fetch do |row|
printf “First Name: %s, Last Name : %s\n”, row[0], row[1]
printf “Age: %d, Sex : %s\n”, row[2], row[3]
printf “Salary :%d \n\n”, row[4]
end
sth.finish

dbh.disconnect

And, testing it:

ruby 1.8.7 (2009-06-12 patchlevel 174) [universal-darwin10.0]
/Library/Ruby/Gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in
bind_param': library routine called out of sequence (SQLite3::MisuseException) from /Library/Ruby/Gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in bind_params’
from
/Library/Ruby/Gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in
each' from /Library/Ruby/Gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in bind_params’
from
/Library/Ruby/Gems/1.8/gems/dbd-sqlite3-1.2.5/lib/dbd/sqlite3/statement.rb:71:in
bind_params' from /Library/Ruby/Gems/1.8/gems/dbi-0.4.5/lib/dbi/handles/statement.rb:115:in execute’
from i.rb:10

And:

ruby 1.9.2p0 (2010-08-18 revision 29036) [x86_64-darwin10.4.0]
lib/rational.rb is deprecated
/usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles.rb:12:
warning: optional boolean argument is obsoleted
/usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in
bind_param': library routine called out of sequence (SQLite3::MisuseException) from /usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in block in bind_params’
from
/usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in
each' from /usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in bind_params’
from
/usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/dbd-sqlite3-1.2.5/lib/dbd/sqlite3/statement.rb:71:in
bind_params' from /usr/local/Cellar/ruby/1.9.2-p0/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles/statement.rb:115:in execute’
from i.rb:10:in `’

Making a change:

sth = dbh.prepare( “INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE,
SEX, INCOME) VALUES (?, ?, ?, ?, ?)”)
sth.execute(‘John’, ‘Poul’, 25, ‘M’, 2300)
#sth.execute(‘Zara’, ‘Ali’, 17, ‘F’, 1000)
sth.finish

ruby 1.8.7 (2009-06-12 patchlevel 174) [universal-darwin10.0]
First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

The exception occurs the second time we try to execute the prepared
statement. (I don’t really know what that means :slight_smile: but, hopefully
someone will find that information useful.

(I’d like to try this against another database, but I don’t have the
time right now.)

4

On Wed, Dec 1, 2010 at 6:00 AM, Tianshuo D. [email protected]
wrote:

Thanks brabuhr :slight_smile:

Just like what you said. It will happen when you have more than one record to
insert.

Try a transaction:
http://www.kitebird.com/articles/ruby-dbi.html#TOC_13

–
Phillip G.

Though the folk I have met,
(Ah, how soon!) they forget
When I’ve moved on to some other place,
There may be one or two,
When I’ve played and passed through,
Who’ll remember my song or my face.