Forum: JRuby find_by_sql fails with undefined method

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(TM) 64-Bit Server VM 1.7.0_02) [linux-amd64-java]

Thanks,
-Jim

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
http://about.me/keithrbennett

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:in 
`substitute_binds'
  from org/jruby/RubyString.java:2764:in `gsub'
  from org/jruby/RubyString.java:2744:in `gsub'
  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:in 
`execute'
  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:in 
`select_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:in 
`find_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:in 
`find_by_sql'
  from (irb):1:in `evaluate'
  from org/jruby/RubyKernel.java:1088:in `eval'
  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:in 
`signal_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:in `loop'
  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:in 
`eval_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:in `catch'
  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:in 
`start'
  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

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

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
http://about.me/keithrbennett

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
> http://about.me/keithrbennett

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

Just found this at
http://stackoverflow.com/questions/6833389/rails-f...

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
http://about.me/keithrbennett

Keith B. wrote in post #1075503:
> Just found this at
> 
http://stackoverflow.com/questions/6833389/rails-f...
>
> 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
> http://about.me/keithrbennett

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

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
http://about.me/keithrbennett

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

Missing '[' & ']'. Try the query

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


Ajit