Find_by_sql fails with undefined method

Ruby JRuby
1

I’m in rails console/IRB and if I try the following:

id = ‘CA-KNF-5659’

f = Fire.find_by_sql(“SELECT * FROM fires WHERE incident_id = ?”, id)

I get:

NoMethodError: undefined method `shift’ for “CA-KNF-5659”:String

Am I missing something here?

I’m using jruby 1.6.5.1 (ruby-1.8.7-p330) (2011-12-27 1bf37c2) (Java
HotSpot™ 64-Bit Server VM 1.7.0_02) [linux-amd64-java]

Thanks,
-Jim

2

Jim -

Can you provide more information? Such as:

  • more of the exception text, to show where the problem occurred
  • Rails version
  • does this happen with other sql statements that use parameter
    substitution (“?”)
  • does this happen with other sql statements that don’t use parameter
    substitution (“?”)
  • if it’s possible to run this in MRI Ruby, does the problem still occur
    in MRI Ruby?
  • Keith

–
Keith R. Bennett

3

Keith B. wrote in post #1075497:

Jim -

Can you provide more information? Such as:

  • more of the exception text, to show where the problem occurred

Loading development environment (Rails 3.2.1)
jruby-1.6.5.1 :001 > f = Fire.find_by_sql(“SELECT * FROM fires WHERE
incident_id = ?”, “CA-KNF-5659”)
NoMethodError: undefined method shift' for "CA-KNF-5659":String from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-jdbc-adapter-1.2.2/lib/arjdbc/jdbc/adapter.rb:202:insubstitute_binds’
from org/jruby/RubyString.java:2764:in gsub' from org/jruby/RubyString.java:2744:ingsub’
from
/home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-jdbc-adapter-1.2.2/lib/arjdbc/jdbc/adapter.rb:202:in
substitute_binds' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-jdbc-adapter-1.2.2/lib/arjdbc/jdbc/adapter.rb:207:inexecute’
from
/home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-jdbc-adapter-1.2.2/lib/arjdbc/jdbc/adapter.rb:337:in
select' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:16:inselect_all’
from
/home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in
select_all' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:38:infind_by_sql’
from
/home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-3.2.1/lib/active_record/explain.rb:33:in
logging_query_plan' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:37:infind_by_sql’
from (irb):1:in evaluate' from org/jruby/RubyKernel.java:1088:ineval’
from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:158:in
eval_input' from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:271:insignal_status’
from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:155:in
eval_input' from org/jruby/RubyKernel.java:1420:inloop’
from org/jruby/RubyKernel.java:1192:in catch' from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:154:ineval_input’
from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:71:in
start' from org/jruby/RubyKernel.java:1192:incatch’
from /home/jim/.rvm/rubies/jruby-1.6.5.1/lib/ruby/1.8/irb.rb:70:in
start' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/railties-3.2.1/lib/rails/commands/console.rb:47:instart’
from
/home/jim/.rvm/gems/jruby-1.6.5.1/gems/railties-3.2.1/lib/rails/commands/console.rb:8:in
start' from /home/jim/.rvm/gems/jruby-1.6.5.1/gems/railties-3.2.1/lib/rails/commands.rb:41:in(root)’
from org/jruby/RubyKernel.java:1038:in `require’

  • Rails version
    Rails 3.2.1
  • does this happen with other sql statements that use parameter
    substitution ("?")

Yes.

  • does this happen with other sql statements that don’t use parameter
    substitution ("?")

No, those work just fine.

  • if it’s possible to run this in MRI Ruby, does the problem still occur
    in MRI Ruby?

Unfortunately, not at the moment. This app is Torquebox enabled so a few
things would have to be changed, the bundle reloaded, mysql gem
compiled, etc. I use JRuby for everything and rarely, if ever RVM into
MRI.

Thank you,
-Jim

4

The long and the short of it is, the method substitute_binds appears to
expect an array. I’m passing a string. Obviously the shift method
doesn’t exist for a string. This is in adapter.rb.

-Jim

5

I realize this shouldn’t be necessary, but it would only take a minute
to try…what if you put the id in an array?:

f = Fire.find_by_sql(“SELECT * FROM fires WHERE incident_id = ?”,
[“CA-KNF-5659”])

  • Keith

–
Keith R. Bennett

6

Just found this at

You have to use the same string replacement as with AR find

|Listing.find_by_sql([“SELECT * FROM listings WHERE industry = ?”,
@user_industry])
|

Looks like the SQL command too is supposed to be in the array.

  • Keith

–
Keith R. Bennett

7

Keith B. wrote in post #1075501:

I realize this shouldn’t be necessary, but it would only take a minute
to try…what if you put the id in an array?:

f = Fire.find_by_sql(“SELECT * FROM fires WHERE incident_id = ?”,
[“CA-KNF-5659”])

  • Keith

–
Keith R. Bennett
Keith R. Bennett - Reston, Virginia, Bennett Business Solutions Inc | about.me

Ha, I tried that actually. It reverses the string:

Fire Load (1.0ms) SELECT * FROM fires WHERE incident_id = ‘9565-FNK-AC’
=> []

Really odd behavior. I just updated to Rails 3.2.8 just in case it was a
bug in Active Record.

The deeper I dig, I can’t be sure this is JRuby’s fault.

It’s odd that after googling quite a bit, no one else has had this
issue.

-Jim

8

The API link doesn’t include a version, so maybe there was something
about it there back in July 2011 when he wrote it. It may have been
true in a previous version, or an error even then, or an error on his
part.

Sorry I couldn’t be more helpful.

By the way, you’re not doing anything special with that SQL. Can you
use the regular Rails-ish style, find_by_user_industry, instead, or was
that just a simplified example?

  • Keith

Keith R. Bennett

9

Keith B. wrote in post #1075503:

Just found this at

You have to use the same string replacement as with AR find

|Listing.find_by_sql([“SELECT * FROM listings WHERE industry = ?”,
@user_industry])
|

Looks like the SQL command too is supposed to be in the array.

  • Keith

–
Keith R. Bennett
Keith R. Bennett - Reston, Virginia, Bennett Business Solutions Inc | about.me

Interesting… that’s not at all what APIDock says:

http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class

The link in that article doesn’t actually link to anything about
find_by_sql.

But, I’m glad Stack Overflow exists.

Thanks for your help.

-Jim

10

The real SQL statement basically does ORDER BY created_at DESC LIMIT 1
so I’m sure that could be refactored into something nicer.

-Jim

11

Missing ‘[’ & ‘]’. Try the query

f = Fire.find_by_sql([“SELECT * FROM fires WHERE incident_id = ?”, id])

Ajit