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 `<main>'
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 
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.)
···
On Tue, Nov 30, 2010 at 12:43 PM, Tianshuo Deng <dengtianshuo@gmail.com> wrote:
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).
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.....