SQLite3 and ruby / shoes gui

Ruby
1

I am feeling constricted and frustrated!

I have had I believe a very good sniff on google trying to find some
help with my problem (namely using SQLite).

got and printed off the FAQ from
sqlite-ruby.rubyforge.org/sqlite3/faq.html and it gives me enough to
hang myself with!

this is the code i have that directly relates to sqlite.

db.execute("select * from customers where '#{@cust_no}'") do |row|
  debug row
end

Now the debug is a shoes call to put output to it’s debug window so this
could easily be changed to a puts for ruby.

all i am wanting to do is to enter a customer number and have the data
printed to the screen (or to the debug screen within shoes to help me
prove i can do it).

the error i get is /usr/local/lib/shoes/ruby/lib/sqlite3/errors.rb line
62
library routine called out of sequence.

Now the code I’m trying to copy is this…

db = SQLite3::database.new(“test.db”)
db.execute(“select * from test”) do |row|
… <I’ve just put the debug row in here!!!>
end

now i’m only shown you the db.execute part as i think we can safely
assume that the database is open and my db variable is a valid handle or
i get more errors talking about the that database isn’t open or some
such messages.

Soooooo what am i doing wrong?

also what is the correct way to do things with sqlite - 1 table per
database file or can you have multiple tables within 1 database file?

e.g.

Customer.db has X tables all relating to one another just like you might
have with postgesql or mysql?

anyone point me to some more ruby & sqlite3 sample code?

currently i have 3 files each with 1 table in them and will be wanting
to extract data from them depending on the customer number being viewed.

so …

Customer number is a UNIQUE numeric value to the customer and it has a
field in jobs table that has that customers number in it.
Jobs has a UNIQUE field called job_nos and the 3rd table parts has a
field for the job_nos that’s held in the job table.

cheers,

dave

2

Found some errors after i posted this !!

they are…

  1. didn’t have a table field name after the where clause!
  2. the ‘#{@cust_no}’"’ should have been cust_nos - grrrr

the output now give me what i didn’t check for namely this…
select * from customers where cust_nos = 1 <if that’s been entered

I currently have 2 customers and their account codes are 1 & 2.
my error message hasn’t changed tho - drat!

dave.

Dave L. wrote:

I am feeling constricted and frustrated!

I have had I believe a very good sniff on google trying to find some
help with my problem (namely using SQLite).

got and printed off the FAQ from
sqlite-ruby.rubyforge.org/sqlite3/faq.html and it gives me enough to
hang myself with!

this is the code i have that directly relates to sqlite.

db.execute("select * from customers where '#{@cust_no}'") do |row|
  debug row
end

Now the debug is a shoes call to put output to it’s debug window so this
could easily be changed to a puts for ruby.

all i am wanting to do is to enter a customer number and have the data
printed to the screen (or to the debug screen within shoes to help me
prove i can do it).

the error i get is /usr/local/lib/shoes/ruby/lib/sqlite3/errors.rb line
62
library routine called out of sequence.

Now the code I’m trying to copy is this…

db = SQLite3::database.new(“test.db”)
db.execute(“select * from test”) do |row|
… <I’ve just put the debug row in here!!!>
end

now i’m only shown you the db.execute part as i think we can safely
assume that the database is open and my db variable is a valid handle or
i get more errors talking about the that database isn’t open or some
such messages.

Soooooo what am i doing wrong?

also what is the correct way to do things with sqlite - 1 table per
database file or can you have multiple tables within 1 database file?

e.g.

Customer.db has X tables all relating to one another just like you might
have with postgesql or mysql?

anyone point me to some more ruby & sqlite3 sample code?

currently i have 3 files each with 1 table in them and will be wanting
to extract data from them depending on the customer number being viewed.

so …

Customer number is a UNIQUE numeric value to the customer and it has a
field in jobs table that has that customers number in it.
Jobs has a UNIQUE field called job_nos and the 3rd table parts has a
field for the job_nos that’s held in the job table.

cheers,

dave

3

Please try to post a complete program which demonstrates the problem.
Try to make it not depend on shoes (e.g. use puts for output)

Then there’s a good chance that someone will be able to run it, see the
error in detail, and help you to fix it.

At the moment the only error you’ve shown is this:

the error i get is /usr/local/lib/shoes/ruby/lib/sqlite3/errors.rb line
62
library routine called out of sequence.

which isn’t very much detail.

now i’m only shown you the db.execute part as i think we can safely
assume that the database is open and my db variable is a valid handle or
i get more errors talking about the that database isn’t open or some
such messages.

“routine called out of sequence” sounds like you might have forgotten
to open the database properly. That’s why it’s important to show a
complete, minimal program that replicates the problem.

also what is the correct way to do things with sqlite - 1 table per
database file or can you have multiple tables within 1 database file?

You can have multiple tables within one file - this is how Rails sets
things up anyway. If you didn’t have them in one file, I’d be surprised
if you could join between them.

Brian.

4

Brian C. wrote:

Please try to post a complete program which demonstrates the problem.
Try to make it not depend on shoes (e.g. use puts for output)

Then there’s a good chance that someone will be able to run it, see the
error in detail, and help you to fix it.

At the moment the only error you’ve shown is this:

the error i get is /usr/local/lib/shoes/ruby/lib/sqlite3/errors.rb line
62
library routine called out of sequence.

which isn’t very much detail.

now i’m only shown you the db.execute part as i think we can safely
assume that the database is open and my db variable is a valid handle or
i get more errors talking about the that database isn’t open or some
such messages.

“routine called out of sequence” sounds like you might have forgotten
to open the database properly. That’s why it’s important to show a
complete, minimal program that replicates the problem.

also what is the correct way to do things with sqlite - 1 table per
database file or can you have multiple tables within 1 database file?

You can have multiple tables within one file - this is how Rails sets
things up anyway. If you didn’t have them in one file, I’d be surprised
if you could join between them.

Brian.

Thanks for the comments Brian,

below is a Ruby script that does what i wanted with shoes!

simple as asking for a customer nos t ofind user enters it and the data
returned.

The only thing i had to change was where i had the SQL.
tried to have it as this…

row = db.execute(“select * from customers where cust_nos =
‘@{cust_nos}’”)

but nothing would display in the iteration so I am happy with how i’ve
got the thing to work.

require “sqlite3”

db = SQLite3::Database.new( “customer” )
puts ‘customer Nos to find’
cust_nos = gets.chomp
stmt = "select * from customers where cust_nos = " + cust_nos
row = db.execute(stmt)
row.each {|t| puts t}

regarding the 1 table = 1 file rule i either read or saw in a video/cast
online
I think i shall put it to the SQLite forum.

dave.