SQL statement w/ Ruby variables

Hello All,
I am trying to attach a small ruby program (see below) to an MS-Access
database. I need to update the database with the values of Ruby
variables and cannot seem to get the formatting right. The variables get
their value based on a txt file that changes every couple of minutes.

require ‘rubygems’
require ‘win32ole’

sid = "12345678’
lname = “Data”
fname = “Test”
rn = “123456789012”
fac = “TRW”

connection = WIN32OLE.new(‘ADODB.Connection’)
connection.Open(‘Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\patients\BarCodes.mdb’)
Connection.Execute(“INSERT INTO UserInfo(UserLast, USerFirst, SID, FAC,
MRN, BarCode) VALUES(lname, fname, sid, fac, rn);”)

When I run this code I get:
Invalid char ‘\223’ in expression
syntax error, unexpected tCONSTANT, expecting ‘)’

Note, this is not a rails project and I do not want to install rails on
the workstation where this will run.

Thanks,
Jeff

\223 ocatal = 147 decimal = ‘o’ with circumflex in extended ASCII

It sounds like something isn’t happy that it is being fed a non-ascii
character.

Thanks for the quick response.

I agree with your assessment, but I do not have that character in my
command.

sid = "12345678’

You started the string with a quote and ended it with an apostrophe.

Connection.Execute(“

The last character of this snippet is not a quote. This is a nice
Unicode quote, the one Word will insert into your documents. Same with
the one at the end of this line.

– Matma R.

2011/8/16 7stud – [email protected]:

Connection.Execute

“Connection” is uppercase and I think it shouldn’t.

Also, you do realize you did not insert values of the variables into
the SQL string, only their names? (Unless the library you’re using
does some really heavy magic.)

– Matma R.

2011/8/16 Jeff S. [email protected]:

How do I pass the value of the variable?

2011/8/16 Bartosz Dziewoński [email protected]

2011/8/16 Bartosz Dziewoński [email protected]:

Connection.Execute

“Connection” is uppercase and I think it shouldn’t.

Also, you do realize you did not insert values of the variables into
the SQL string, only their names? (Unless the library you’re using
does some really heavy magic.)

Also, the INSERT statement tries to insert five values, while “INTO
UserInfo(UserLast, USerFirst, SID, FAC, MRN, BarCode)” lists six
columns.

You are absolutely correct, I completely missed that.

Getting a new error that references a method_messing (execute function),
so
the search continues.

Thanks.

2011/8/16 Bartosz Dziewoński [email protected]