String expand problem

[Wild Karl-Heinz]

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

 

[Bill Atkins]

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.

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

 

[David A. Black]

Hi --

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.

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

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

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


David

 

[Wild Karl-Heinz]

In message "String expand problem"

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]

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

 

[David A. Black]

Hi --

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?


David

 

[James Edward Gray II]

Hi --



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]

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/ruby-dbi.html 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

 

[Charles Mills]

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]

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