Ruby - Passing binding variables in SQL execution

Question

I'm new to Ruby and I want to try to access a MySQL database:

require 'rubygems'
require "dbi"

class DBConnection
  attr_accessor :dbh
  #Connect to db 
  def connect?(driver_url,user,pass)
    begin
      @dbh = DBI.connect(driver_url, user,pass);
      return true
    rescue DBI::DatabaseError => e
        puts "Error message: #{e.errstr}"
        @dbh.rollback
        return false
    ensure
      @dbh.disconnect if !dbh
    end
  end

  def execute_customize(query,params)
    stm = @dbh.prepare(query)
    if( (params != nil) && !(params.empty?) )
       stm.execute(params)
    else
      stm.execute
    end
    header = false
    stm.fetch do |row|
      if (!header)
          puts("ID   Name")
          header = true
      end
       puts("#{row[0]}   #{row[1]}")
    end
  end
end

db = DBConnection.new
db.connect?("DBI:Mysql:test:localhost", "root", "123456")
db.execute_customize("SELECT * FROM test.employee WHERE name = ? OR name = ? ",*["John","Terry"])

But the above returns the following error:

in `execute_customize': wrong number of arguments (3 for 2) (ArgumentError)

But the execution is successful with:

dbh.execute_customize("SELECT * FROM test.employee WHERE name = ?",*["John"])

What am I doing wrong?

Demo data from employee table :
+------+-------+
| id   | name  |
+------+-------+
|    1 | John  |
|    2 | Terry |
|    3 | Vidal |
|    4 | CR7   |
|    5 | M10   |
|    6 | R10   |
|    7 | F4    |
+------+-------+

// Update : Your comment almost told me using IN in query, but if with other query like :

SELECT * FROM test.employee WHERE name = ? and id > ?

I still need a way to passing seperate paramer to every "?" character

What is dbh? How did you create it? The mysql2 gm doesn't seem to have an execute at all and the old mysql gem's execute doesn't work like that. — mu is too short
– mu is too short, Commented Jan 7, 2014 at 6:10
I've post full of my example ,i rewrite execute() function to execute_customize() — Ryo
– Ryo, Commented Jan 7, 2014 at 7:41

inertialgradient · Accepted Answer · 2014-01-07 07:07:26Z

3

You're passing three arguments instead of two.

The splat operator * expands the array, so its elements are treated as separate arguments.

Try

dbh.execute("SELECT * FROM test.employee WHERE name IN (?)", names)

where names is a comma-separated list of strings.

That should work, but you may not need to use execute for this.

If you're using Rails, you can just use

Employee.where(name: ["John","Terry"])

and ActiveRecord will understand what you mean.

See http://guides.rubyonrails.org/active_record_querying.html

edited Jan 7, 2014 at 7:07

answered Jan 7, 2014 at 5:12

inertialgradient

2,2351 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

sevenseacat Over a year ago

while the question is tagged with rails, there's nothing rails-specific in his code and you shouldn't assume he's using ActiveRecord.

mu is too short Over a year ago

Are you sure that WHERE name IN ? ends up like where name in ('John', 'Terry') rather than where name in 'John'?

Ryo Over a year ago

Thanks,but i get a mysql syntax 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 'OF 'JohnTerry')...") And i wondering why my array parameter contain 2 elements but Ruby flatten it to 3 elements ??

inertialgradient Over a year ago

Revised. Should work with MySQL now: dev.mysql.com/doc/refman/5.0/en/…

Paul Richter Over a year ago

@Novice The fundamental problem is still the same. jmromer is correct that the splat operator has the effect of separating out the components of the array in to individual elements when it is passed to your custom method. It ends up being the same as if you called execute_customize(query, "John", "Terry"), which would give you the same error. Either remove the asterisks from the method invocation, or alter the method to accept a variable number of params, by doing execute_customize(query, *params).

|

Collectives™ on Stack Overflow

Ruby - Passing binding variables in SQL execution

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related