How do you create prepared statements with the mysql2 gem?

Question

I've tried using google to answer this seemingly simple question, but to my surprise, it didn't help.

I have code in my rails application currently using the 'prepare' method with the mysql gem. On switching to mysql2, this breaks with the error:

undefined method `prepare' for #<Mysql2::Client::0.......

So I tried looking for a version of the 'prepare' method but this search has been unsuccessful so far. Can anyone help me out with this?

Edit: If this isn't possible, could anyone let me know if there's a way to simply parameterize my queries with something in the mysql2 library?

Answer 1

UPDATE

As Ryan Rapp pointed out correctly, mysql2 now supports prepared statements. Following snippet is extracted from the readme:

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

Thanks Ryan!

Original Post

I found no such function either; neither in source nor in the documentation. Maybe the following snippet is a helpful replacement for your needs? (found in the documentation of mysql2 gem):

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

Answer 2

The mysql2 gem now supports prepared statements according to the documentation.

The syntax is as follows:

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

This was added with a merged pull request in June 2015.

Answer 3

I swapped out to use https://github.com/tmtm/ruby-mysql instead of mysql2. I'm surprised this isn't a bigger deal-breaker for people using the mysql2 gem. I guess people who dig this deep into writing SQL have swapped to Postgresql?

In case others are having trouble with gem install ruby-mysql followed by require "mysql" where you get a Ruby error like 'read_eof_packet': packet is not EOF (Mysql::ProtocolError) the trick is to gem uninstall ruby-mysql and instead gem install ruby-mysql-ext (or use gem 'ruby-mysql-ext' in your Gemfile) which will swap out the Ruby implementation which isn't yet Ruby 2.0 compatible (or at least, didn't work for me) for simple C bindings.

To be clear, if you do require 'mysql' while both ruby-mysql-ext and ruby-mysql are installed, it will load the Ruby version. There may be a way to require within a specific gem, but I didn't have time to look it up.

Answer 4

Yes, mysql2 adapter doesn't support binding till the current Rails 4.0. I'm being surprised! You could tell this by the code snip from ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/mysql2_adapter.rb

      def exec_query(sql, name = 'SQL', binds = [])
        result = execute(sql, name)
        ActiveRecord::Result.new(result.fields, result.to_a)
      end

      alias exec_without_stmt exec_query

      # Returns an ActiveRecord::Result instance.
      def select(sql, name = nil, binds = [])                                                                                                                      
        exec_query(sql, name)
      end

also this is helpful for you understanding:

(in ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb)

      # Returns an ActiveRecord::Result instance.
      def select_all(arel, name = nil, binds = [])
        if arel.is_a?(Relation)
          relation = arel
          arel = relation.arel                                                                                                                                     
          if !binds || binds.empty?
            binds = relation.bind_values
          end
        end

        select(to_sql(arel, binds), name, binds)
      end

That's it! And I guess I may turn to Postgres!!