SQL statement w/ Ruby variables

ruby-talk
1

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

···

--
Posted via http://www.ruby-forum.com/.

2

\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.

···

--
Posted via http://www.ruby-forum.com/.

3

Thanks for the quick response.

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

···

On Tue, Aug 16, 2011 at 2:11 PM, 7stud -- <bbxx789_05ss@yahoo.com> wrote:

\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.

--
Posted via http://www.ruby-forum.com/\.

4

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 Rex

···

2011/8/16 7stud -- <bbxx789_05ss@yahoo.com>:

\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.

--
Posted via http://www.ruby-forum.com/\.

5

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 <matma.rex@gmail.com>

> 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 Rex

2011/8/16 7stud -- <bbxx789_05ss@yahoo.com>:
> \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.
>
> --
> Posted via http://www.ruby-forum.com/\.
>
>

6

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 Rex

···

2011/8/16 Jeff Smith <jesmith2095@gmail.com>:

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 <matma.rex@gmail.com>

> 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 Rex

2011/8/16 7stud -- <bbxx789_05ss@yahoo.com>:
> \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.
>
> --
> Posted via http://www.ruby-forum.com/\.
>
>

7

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

···

2011/8/16 Bartosz Dziewoński <matma.rex@gmail.com>:

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.)

8

How do I pass the value of the variable?

···

2011/8/16 Bartosz Dziewoński <matma.rex@gmail.com>

> 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 Rex

2011/8/16 Jeff Smith <jesmith2095@gmail.com>:
> 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 <matma.rex@gmail.com>
>
>> > 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 Rex
>>
>>
>>
>> 2011/8/16 7stud -- <bbxx789_05ss@yahoo.com>:
>> > \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.
>> >
>> > --
>> > Posted via http://www.ruby-forum.com/\.
>> >
>> >
>>
>>
>