Forum: Ruby SQLite3 and ruby / shoes gui

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Dave L. (Guest)
on 2009-06-08 15:19
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
Dave L. (Guest)
on 2009-06-08 15:49
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
Brian C. (Guest)
on 2009-06-08 16:35
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.
Dave L. (Guest)
on 2009-06-09 11:34
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.
This topic is locked and can not be replied to.