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, [e-mail] wrote:

> On Tue, Nov 30, 2010 at 12:43 PM, Tianshuo Deng =
<[e-mail]> wrote:
>> Hi, guys, I want to share my discovery with you. If you found I am =
wrong, pleas point it out:)
>>=20
>> if you use dbi to generate a prepared statement by doing:
>>=20
>> sth=3Ddbh.prepare("INSERT INTO WORDS VALUES(?,?,?)")
>>=20
>> (0..1).each{|i|
>>        sth.execute("1","2","3")
>> }
>>=20
>> 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).
>>=20
>> 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.....
>=20
> I'm not using DBI, but based on an online tutorial for DBI:Mysql I
> made this test program:
>=20
> require "dbi"
>=20
> dbh =3D DBI.connect("DBI:SQLite3:memory:")
>=20
> 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 )")
>=20
> sth =3D 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
>=20
> sth =3D dbh.prepare("SELECT * FROM EMPLOYEE WHERE INCOME > ?")
> sth.execute(1000)
>=20
> 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
>=20
> dbh.disconnect
>=20
> And, testing it:
>=20
> 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
>=20
> And:
>=20
> 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>'
>=20
> Making a change:
>=20
> sth =3D 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
>=20
> ruby 1.8.7 (2009-06-12 patchlevel 174) [universal-darwin10.0]
> First Name: John, Last Name : Poul
> Age: 25, Sex : M
> Salary :2300
>=20
> 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.
>=20
> (I'd like to try this against another database, but I don't have the
> time right now.)
>=20