Beginner's problem with sqlite3

Ruby
1

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::Database.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

2

Tom C. wrote:

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

3

Hi,
It seems you’re having problems with the SQL part not the Ruby part.
Check out Query Language Understood by SQLite for documentation on the
syntax.

On Feb 21, 2008, at 9:03 AM, Tom C. wrote:

correct, but there are no errors produced by it:
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.

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.

4

Serg K. wrote:

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.

5

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

6

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)

Peter H. wrote:

Pretty sure that’s the error

–

Tom C., MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< [email protected] >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
7

Serg K. wrote:

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 :frowning:

Thats just soooo much fun to debug.

Caffine good.

8

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.

9

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

10

And I add the config file for database :d

Sorry :d

11

On Fri, 22 Feb 2008 05:14:56 +0900, Stéphane Wirtel wrote:

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

Example:

That’s an excellent example :slight_smile:

-Thufir

12

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

Thufir wrote:

That’s an excellent example :slight_smile:

-Thufir

–

Tom C., MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< [email protected] >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
13

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 C., MS MA, LMHC
Private practice Psychotherapist
Bellingham, Washington, U.S.A: (360) 920-1226
<< [email protected] >> (email)
<< TomCloyd.com >> (website & psychotherapy weblog)
<< sleightmind.wordpress.com >> (mental health issues weblog)
<< directpathdesign.com >> (web site design & consultation)
14

On Fri, Feb 22, 2008 at 6:30 AM, Tom C. [email protected] wrote:

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

15

Tom C. 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 :slight_smile:

And tomorrow it’s FOSDEM :d