From: Tianshuo Deng <dengtianshuo@...>
Date: 2010-12-01T14:00:46+09:00
Subject: Re: dbi doesnt work well with sqlite3!?!?

Thanks brabuhr :)

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

On Nov 30, 2010, at 10:49 PM, brabuhr@gmail.com wrote:

> 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.....
> 
> 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.)
>