It would be ful 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 would be
appreciated.

Tom

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.

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

> It would be ful 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
> would be appreciated.
>
> Tom
>
>

Hope that s.

Tom Cloyd 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

Serg Koren wrote:
> Hope that s.
>


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

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


On Feb 21, 2008, at 9:18 AM, Peter Hickman wrote:

> Serg Koren wrote:
>> Hope that s.
>>
>
>
> Probably wont. The "INSERT INTO tablename (a,b,c) VALUES (x, y, z);"
> syntax is valid sql. He was just missing the commas.
>
>
>

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

Tom
(Bellingham, Washington, USA)

Peter Hickman wrote:
> Tom Cloyd 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
>
>


--

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

Serg Koren wrote:
> That's what I get trying to 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.

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.

On Feb 21, 2008, at 10:05 AM, Peter Hickman wrote:

> Serg Koren wrote:
>> That's what I get trying to 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.
>
>