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,leas 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|
>  ¨Âôè®åøåãõô娢±¢¬¢²¢¬¢³¢> }
>
> 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 `<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.)