beginner's problem with sqlite3

[Tom Cloyd]

It would be helpful if the sqlite3-ruby documentation offered one or two
complete examples, as without these I am unable to figure out several
syntax questions, so I'm having to guess, and I'm not guessing right.

I'm inputing a simple text file and trying to use it to generate an
table containing 4 fields (columns). I'm tracking the program with logger.

Here's the table creation code, which I'm not entirely sure is correct,
but there are no errors produced by it:

db = SQLite3:atabase.new( "sqlite-demo.sqlite" )
db.execute( 'CREATE TABLE newsheap (datecol titlecol urlcol summarycol)' )

I then read four physical records into, respectively, variables date,
title, url, and summary. Then I try to output them to the table with
this statement, about which I feel even less certain, and it's here that
the error is produced:

db.execute( 'INSERT INTO newsheap(datecol titlecol urlcol summarycol)
VALUES(date title url summary)' )

The logger output this produces:
E, [2008-02-21T05:52:45.358561 #11133] ERROR -- : near "titlecol":
syntax error

I've spent hours trying many variations to probe the syntax, and I keep
getting the same error.

I keep thinking that I'm somehow not being clear to ruby, in the
transition between ruby variables and variables in the sql statement,
but I cannot find the problem. For now, I'm stumped. Any help would be
appreciated.

Tom

 

[Serg Koren]

Hi,
It seems you're having problems with the SQL part not the Ruby part.
Check out http://www.sqlite.org/lang.html for documentation on the
syntax.

It would be helpful if the sqlite3-ruby documentation offered one or
two complete examples, as without these I am unable to figure out
several syntax questions, so I'm having to guess, and I'm not
guessing right.

I'm inputing a simple text file and trying to use it to generate an
table containing 4 fields (columns). I'm tracking the program with
logger.

Here's the table creation code, which I'm not entirely sure is
correct, but there are no errors produced by it:

db = SQLite3:atabase.new( "sqlite-demo.sqlite" )
db.execute( 'CREATE TABLE newsheap (datecol titlecol urlcol
summarycol)' )

I then read four physical records into, respectively, variables
date, title, url, and summary. Then I try to output them to the
table with this statement, about which I feel even less certain, and
it's here that the error is produced:

db.execute( 'INSERT INTO newsheap(datecol titlecol urlcol
summarycol) VALUES(date title url summary)' )

Youre SQL is incorrect, the syntax of an INSERT statement is: INSERT
INTO x VALUES (). You're missing the VALUES keyword.

The logger output this produces:
E, [2008-02-21T05:52:45.358561 #11133] ERROR -- : near "titlecol":
syntax error

I've spent hours trying many variations to probe the syntax, and I
keep getting the same error.

I keep thinking that I'm somehow not being clear to ruby, in the
transition between ruby variables and variables in the sql
statement, but I cannot find the problem. For now, I'm stumped. Any
help would be appreciated.

Tom

Hope that helps.

 

[Peter Hickman]

db.execute( 'INSERT INTO newsheap(datecol titlecol urlcol summarycol)
VALUES(date title url summary)' )

I would have written the sql with a few commas and a semi colon.

INSERT INTO newsheap (datecol, titlecol, urlcol, summarycol) VALUES
(date, title, url, summary);

Pretty sure that's the error

 

[Peter Hickman]

Hope that helps.

Probably wont. The "INSERT INTO tablename (a,b,c) VALUES (x, y, z);"
syntax is valid sql. He was just missing the commas.

 

[Serg Koren]

That's what I get trying to help when I'm not awake. I misread his
code and missed the VALUES keyword. My apologies.

 

[Tom Cloyd]

Serg, Peter - thank you very much. This is such an amazing list. One
gets back so much help. To an amateur like me, that's very encouraging.
Onward!...

Tom
(Bellingham, Washington, USA)

I would have written the sql with a few commas and a semi colon.

INSERT INTO newsheap (datecol, titlecol, urlcol, summarycol) VALUES
(date, title, url, summary);

Pretty sure that's the error

--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tom Cloyd, MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< (e-mail address removed) >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Peter Hickman]

That's what I get trying to help when I'm not awake. I misread his
code and missed the VALUES keyword. My apologies.

Actually the syntax you proposed is also valid but was not what was
wrong with his code. However I would never recommend the "INSERT INTO
tablename VALUES (x, y, z);" format as the it can appear to be correct
but be stuffing the values into the wrong columns

Thats just soooo much fun to debug.

Caffine good.

 

[Serg Koren]

Yup that's bad style and lazy coding. I always specify the column
list just to avoid assumptions about the column order in the
underlying table--which could change and existing code would then be
wrong.

 

[Stéphane Wirtel]

Hi,

Can I give you a piece of advice ?

Check about ActiveRecord without Rails, because if you are novice with SQL, it can help you.

Example:

require "rubygems"
require "active_record"
require "logger"
require "yaml"

ActiveRecord::Base.establish_connection( YAML::load_file( 'database.yml' ) )
ActiveRecord::Base.logger = Logger.new( File.open( 'database.log', 'w' ) )
ActiveRecord::Base.colorize_logging = false

ActiveRecord::Schema.define do
create_table :tests, :force => true do |t|
t.column :text, :string, :limit => 32
end
end

class Test < ActiveRecord::Base
end

newTest = Test.new
newTest.text = "first test :d"
newTest.save!

count = Test.count
if count == 0
puts "The table 'Test' is empty"
elsif count == 1
puts "There is one element"
else
puts "There are #{count} elements"
end


Good luck

Stephane

 

[Stéphane Wirtel]

And I add the config file for database :d

Sorry :d

adapter: sqlite3
database: development.sqlite3

 

[Thufir]

Check about ActiveRecord without Rails, because if you are novice with
SQL, it can help you.

Example:

That's an excellent example


-Thufir

 

[Tom Cloyd]

Stephanie,
Oh this is certainly exciting, because I've been vaguely aware of
ActiveRecord, and have wanted to dig into it, but wasn't sure WHEN. At
your suggestion, I'm going to do it right away. And your inclusion of
some code to study, adapt, etc., is very very appreciated. My gratitude
is doubled. This is going to be a great week for my ruby-learning.

Thanks!

Tom

That's an excellent example


-Thufir

--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tom Cloyd, MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< (e-mail address removed) >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Tom Cloyd]

Stephane,

I apologize for being a a too-rapid reader, and a bit culturally
ignorant as well - I inadvertently feminized your name in my reply,
writing what I thought I saw. I think that to some of us native English
speakers, your name looks feminine, at first glance. Clearly, it's not.
I'll try to be more attentive!

Tom

--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tom Cloyd, MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< (e-mail address removed) >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Todd Benson]

Stephane,

I apologize for being a a too-rapid reader, and a bit culturally
ignorant as well - I inadvertently feminized your name in my reply,
writing what I thought I saw. I think that to some of us native English
speakers, your name looks feminine, at first glance. Clearly, it's not.
I'll try to be more attentive!



Tom

Tom, you should check out the Sequel library as well (gem install
sequel). I've played with it a little, and it seems pretty solid.
ActiveRecord is good too, though. Both rubyize the SQL, but they both
allow you to do direct SQL queries also.

Todd

 

[Stéphane Wirtel]

Tom Cloyd a écrit :

Stephane,

I apologize for being a a too-rapid reader, and a bit culturally
ignorant as well - I inadvertently feminized your name in my reply,
writing what I thought I saw. I think that to some of us native English
speakers, your name looks feminine, at first glance. Clearly, it's not.
I'll try to be more attentive!

Thanks


And tomorrow it's FOSDEM :d