Asp Forum - String expand problem

Karl-Heinz Wild

12/17/2004 2:57:00 PM

hi.

I have a bit of a problem :-)
I'd like to do the following.

DATA = [
{ :fn => 'data.csv',
:sql => %q{ insert into table (id,name) values ('#{x[1]}','#{x[2]}' ); }
},
]

DATA.each { | d |
File.foreach( d[ :fn ] ) { | line |
x = line.chomp.split( /\t/ )
print( d[ :sql ] )
}
}

If I us %Q I get an name error x isn't defined and
if I us %q the x aren't expanded with the found values.

Some hits. Whould be great :-)

regards
Karl-Heinz

9 Answers

Bill Atkins

12/17/2004 3:10:00 PM

You're getting those errors because x isn't defined at that point.
Interpolation happens as soon as Ruby encounters the string, so when
it's parsing DATA, it doesn't know what x is supposed to refer to.

There are a few ways to make this work. For instance, you could use
%q and then put some placeholders in DATA[:sql], so the string would
become "... values ('@@X1@@'..." and then simply do DATA[:sql].gsub!
/@@X1@@/, x[1] when x actually has a value.

On Fri, 17 Dec 2004 23:57:24 +0900, Wild Karl-Heinz <kh.wild@wicom.li> wrote:
> hi.
>
> I have a bit of a problem :-)
> I'd like to do the following.
>
> DATA = [
> { :fn => 'data.csv',
> :sql => %q{ insert into table (id,name) values ('#{x[1]}','#{x[2]}' ); }
> },
> ]
>
> DATA.each { | d |
> File.foreach( d[ :fn ] ) { | line |
> x = line.chomp.split( /\t/ )
> print( d[ :sql ] )
> }
> }
>
> If I us %Q I get an name error x isn't defined and
> if I us %q the x aren't expanded with the found values.
>
> Some hits. Whould be great :-)
>
> regards
> Karl-Heinz
>
>

--
$stdout.sync = true
"Just another Ruby hacker.".each_byte do |b|
('a'..'z').step do|c|print c+"\b";sleep 0.007 end;print b.chr
end; print "\n"

dblack

12/17/2004 3:15:00 PM

Karl-Heinz Wild

12/17/2004 3:20:00 PM

In message "String expand problem"
on 17.12.2004, David A. Black <dblack@wobblini.net> writes:

DAB> See Bill's answer too. Here's another way, using the %-style value
DAB> interpolation in the output string:

DAB> DATA = [
DAB> { :fn => 'data.csv',
DAB> :sql => %q{ insert into table (id,name)
DAB> values ('%s','%s'); }
DAB> },
DAB> ]

DAB> DATA.each { |d|
DAB> File.foreach(d[:fn] ) { |line|
DAB> x = line.chomp.split( /\t/ )
DAB> puts(d[:sql] %x)
DAB> }
DAB> }

It's that I was looking for.

Thank you.

Karl-Heinz

Austin Ziegler

12/17/2004 3:40:00 PM

On Sat, 18 Dec 2004 00:20:00 +0900, Wild Karl-Heinz <kh.wild@wicom.li> wrote:

Note that you probably don't want to use %s expansion for reql SQL;
instead use the ? substitution mechanism -- you don't have to worry
about escaping single quotes or other values.

-austin
--
Austin Ziegler * halostatue@gmail.com
* Alternate: austin@halostatue.ca

dblack

12/17/2004 4:28:00 PM

James Gray

12/17/2004 4:32:00 PM

On Dec 17, 2004, at 10:28 AM, David A. Black wrote:

> Hi --
>
> On Sat, 18 Dec 2004, Austin Ziegler wrote:
>
>> On Sat, 18 Dec 2004 00:20:00 +0900, Wild Karl-Heinz
>> <kh.wild@wicom.li> wrote:
>>
>> Note that you probably don't want to use %s expansion for reql SQL;
>> instead use the ? substitution mechanism -- you don't have to worry
>> about escaping single quotes or other values.
>
> What's the ? substitution mechanism?

The DBI allows you to use ?s as placeholders in statements you prepare.
They are substituted automatically, by extra parameters to the execute
methods. As pointed out above, the DBI will handle escaping for you.

Hope the helps.

James Edward Gray II

Austin Ziegler

12/17/2004 4:38:00 PM

On Sat, 18 Dec 2004 01:28:26 +0900, David A. Black <dblack@wobblini.net> wrote:
> On Sat, 18 Dec 2004, Austin Ziegler wrote:
> > On Sat, 18 Dec 2004 00:20:00 +0900, Wild Karl-Heinz <kh.wild@wicom.li> wrote:
> > Note that you probably don't want to use %s expansion for reql SQL;
> > instead use the ? substitution mechanism -- you don't have to worry
> > about escaping single quotes or other values.
> What's the ? substitution mechanism?

You write your SQL so that it reads:

SELECT foo, bar
FROM baz
WHERE oingo = ?

Then, you use parameter binding (see
http://www.kitebird.com/articles/rub... for "Quoting,
Placeholders, and Parameter Binding"). The "?" in your SQL becomes a
parameter that you don't have to quote.

If you've ever done Oracle programming using Pro*C, this is equivalent to doing:

SELECT foo, bar
FROM baz
WHERE oingo = :boingo;

It results in far more efficient SQL most of the time, because the
optimizers in quality databases (e.g., most emphatically *not* MySQL)
will detect that all queries referring the ? or :boingo can be
optimized and compiled a particular way so that only the parameters
vary and the SQL doesn't have to be recompiled every time you provide
a new value for :boingo :)

-austin
--
Austin Ziegler * halostatue@gmail.com
* Alternate: austin@halostatue.ca

Charles Mills

12/17/2004 6:17:00 PM

Austin Ziegler wrote:
(snip)
> If you've ever done Oracle programming using Pro*C, this is
equivalent to doing:
>
> SELECT foo, bar
> FROM baz
> WHERE oingo = :boingo;
>
> It results in far more efficient SQL most of the time, because the
> optimizers in quality databases (e.g., most emphatically *not* MySQL)
> will detect that all queries referring the ? or :boingo can be
> optimized and compiled a particular way so that only the parameters
> vary and the SQL doesn't have to be recompiled every time you provide
> a new value for :boingo :)
>

SQLite allows this as well (using ':name' as a place holder). Since
the place holder looks like a symbol it seems natural to allow the
following (in Ruby DBI and other Ruby database drivers):

db.execute("select * from table where field = :yada", :yada =>
my_string)

Anyway, using symbols and the hash in the parameters list thing you end
up with some nice syntactic sugar. Clearer than using positional place
holders (IMO).

-Charlie

Charles Mills

12/17/2004 6:20:00 PM

comp.lang.ruby

String expand problem

Karl-Heinz Wild

Bill Atkins

dblack

Karl-Heinz Wild

Austin Ziegler

dblack

James Gray

Austin Ziegler

Charles Mills

Charles Mills

x Login to ForumsZone