dbi doesnt work well with sqlite3!?!?

T

Tianshuo Deng

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=3Ddbh.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=3DSQLite3::Database.new("shit.db")
db.execute(%q{
CREATE TABLE PEOPLE(
NAME CHAR(20),
ADDRESS CHAR(29) );})
st=3Ddb.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!!!!!!!!
 
B

brabuhr

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=3Ddbh.prepare("INSERT INTO WORDS VALUES(?,?,?)")

(0..1).each{|i|
=A0 =A0 =A0 =A0sth.execute("1","2","3")
}

it will give you a /sqlite3-ruby-1.3.2/lib/sqlite3/statement.rb:41:in `bi=
Click to expand...
nd_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 ca=
Click to expand...
n 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 =3D 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 =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

sth =3D 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/stateme=
nt.rb:71:in
`bind_params'
from /Library/Ruby/Gems/1.8/gems/dbi-0.4.5/lib/dbi/handles/statement.rb:11=
5: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/li=
b/dbi/handles/statement.rb:115:in
`execute'
from i.rb:10:in `<main>'

Making a change:

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

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

Tianshuo Deng

Thanks brabuhr :)

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

`bind_param': library routine called out of sequence =
(SQLite3::MisuseException).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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

simple SQLITE3 error!!??library routine called out of sequence 0
DBI and SQLite3 1
Windows XP problem with Ruby, gem sqlite3-ruby, and SQLite3 4
Odd Issue With DBD::SQLite3 - Inserts Don't Work 1
Help on SQLITE3-ruby (1.2.3) Windows problem 3
errors when using sqlite3's query and prepare methods 2
[SQLite3] CREATE TABLE ... doesn't work sometimes 1
SQLite3 passing row data from 1 class to another 6

Members online

Total: 95 (members: 1, guests: 94)
Robots: 227

Forum statistics

Threads
473,766
Messages
2,569,569
Members
45,042
Latest member
icassiem

Latest Threads

Top