From: Junkone <junkone1@...>
Date: 2008-06-16T08:39:10+09:00
Subject: Re: ruby string format kicks in when doign wild card in activerecord

On Jun 15, 6:24 pm, Sam Smoot <ssm...@gmail.com> wrote:
> On Jun 15, 4:29 pm, Junkone <junko...@gmail.com> wrote:
>
>
>
>
>
> > when i was doing a LIKE statement in my wild card query. Ruby thinks
> > that the % is a String Format charactor and throws exception. what are
> > my options.
>
> > >> Rawdata.find(:all , :conditions=>["processed_indicator like 'LF%'"])
>
> > ArgumentError: malformed format string - %'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:1421:in `%'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:1421:in `sanitize_sql_array'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:1390:in `sanitize_sql'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:1163:in `add_conditions!'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:1096:in `construct_finder_sql'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:997:in `find_every'
> >         from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
> > active_record/base.rb:418:in `find'
> >         from (irb):20
>
> Try it without the Array wrapper? Or with a ? place-holder and 'LF%'
> as the second element in the Array? Or monkey-patch the
> sanitize_sql_array method not to use String#%.- Hide quoted text -
>
> - Show quoted text -

when i use the place holder ? the acticerecord creates a bad sql.
Rawdata.find(:all, :conditions=>["processed_indicator is NULL and
symbol not in (select  distinct r.symbol  from rawdatas r
WHERE  r.executionecn  like ?  or   r.executionecn  like ?",'%IDEAL
%','%FX%'])
is the actual definition. it produces an sql like this where it opens
a bracket for the inner select but fails to close it correctly.

>> a=Rawdata.new
=> #<Rawdata:0x45ee094 @attributes={"updated_at"=>nil, "size"=>nil,
"execution_id"=>nil, "executionecn"=>"", "account"=>"", "
tradedatetime"=>nil, "price"=>nil, "symbol"=>"",
"processing_date"=>nil, "action"=>"", "processed_indicator"=>nil,
"created_a
t"=>nil}, @new_record=true>
>> a.unprocessedRawData()
ActiveRecord::StatementInvalid: Mysql::Error: You have an error in
your SQL syntax; check the manual that corresponds to your
 MySQL server version for the right syntax to use near '' at line 2:
SELECT * FROM rawdatas WHERE (processed_indicator is NUL
L and symbol not in (select  distinct r.symbol  from rawdatas r
WHERE  r.executionecn  like '%IDEAL%'  or   r.executionecn  like '%FX
%')
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/connection_adapters/abstract_adapter.rb:128
:in `log'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/connection_adapters/mysql_adapter.rb:243:in
 `execute'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/connection_adapters/mysql_adapter.rb:399:in
 `select'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/connection_adapters/abstract/database_state
ments.rb:7:in `select_all'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/base.rb:427:in `find_by_sql'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/base.rb:997:in `find_every'
        from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/
active_record/base.rb:418:in `find'
        from E:/TradingTools/Development/app/models/rawdata.rb:14:in
`unprocessedRawData'
        from (irb):42
SELECT * FROM rawdatas WHERE (processed_indicator is NULL and symbol
not in (select  distinct r.symbol  from rawdatas r
WHERE  r.executionecn  like '%IDEAL%'  or   r.executionecn  like '%FX
%')

It created the opening bracket after the FIRST WHERE STATEMETN but it
does not produce a matching closing bracket.
some bug in activerecord. dunno how to fix it up.
seede