From: Nat Pryce <nat.pryce@...13media.com>
Date: 2002-07-09T02:19:26+09:00
Subject: Re: Ruby.DBI -- select where ...

From: "Mark Probert" <probertm@nortelnetworks.com>
> This is a Ruby-DBI question.  I am using 0.0.15 under ruby 1.6.6 (cygwin).
> I am using ODBC to connect to a SQL 7 database.  The connection and
> test programs work fine.
>
> However, when I do:
>
> require 'dbi'
>
> dbh = DBI.connect('DBI:ODBC:pubs', 'bob', 'bob')
> state = "CA"
> sth = dbh.prepare("select au_lname, au_fname from authors where state =
#{state}")
> sth.execute
>
> I get:
>
> /usr/local/lib/ruby/site_ruby/1.6/DBD/ODBC/ODBC.rb:129:in `prepare': S0022
(207)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'CA'.
> (DBI::DatabaseError)
>         from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:536:in `prepare'
>         from select_1.rb:8
>
> Is there a problem in using where clauses in DBI with ODBC?  Or am I
> doing something wrong?

You need to put quotes around the #{state}substitution to compare the value
of the state column to a string literal "CA".  Without quotes, the value of
the state column is compared to the value of the CA column, which I guess
doesn't exist and results in the error message you are seeing.  The
statement should be built like this:

sth = dbh.prepare("select au_lname, au_fname from authors where state =
\"#{state}\"")

You might be able to use single quotes in SQL (I can't remember; it's a
while since I've done any SQL coding), in which case the statement can be
built like this, which is more readable:

sth = dbh.prepare("select au_lname, au_fname from authors where state =
'#{state}'")

Cheers,
            Nat.

________________________________
Dr. Nathaniel Pryce
B13media Ltd.
Studio 3a, Aberdeen Business Centre, 22/24 Highbury Grove, London, N5 2EA
http://www.b13media.com