How do you create prepared statements using mysql2 gem?

Question

I tried using Google to answer this seemingly simple question, but, to my surprise, this did not help.

I have code in my rails application currently using the prepare method with mysql stone. When switching to mysql2, this fails with an error:

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

So, I tried to find a version of the “prepare” method, but this search has not been successful so far. Can anyone help me with this?

Edit: if this is not possible, can someone tell me if there is a way to just parameterize my queries with something in the mysql2 library?

Answer 1

Brawl mysql2 now supports prepared statements as per 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 merge request in June 2015.

Answer 2

UPDATE

As Ryan Rapp pointed out correctly, mysql2 now supports prepared statements. The following snippet is extracted from 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 publication

I also did not find such a function; neither in the source nor in the documentation . Maybe the following snippet is a useful replacement for your needs? (found in the gem mysql2 documentation):

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

Answer 3

I swapped to use https://github.com/tmtm/ruby-mysql instead of mysql2. I am surprised that this is not a big robber for people using the mysql2 gem. I think the people who dig this deep into SQL writing have switched to Postgresql?

If others have problems 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 gem uninstall ruby-mysql and instead gem install ruby-mysql-ext (or use the gem 'ruby-mysql-ext' in the Gemfile) which will replace the Ruby implementation that is not yet compatible with Ruby 2.0 (or at least doesn't work for me) for simple bindings C.

To be clear, if you execute require 'mysql' while both ruby-mysql-ext and ruby-mysql are installed, it will download the Ruby version. There may be a way to demand in a certain gem, but I did not have time for this.

Answer 4

Yes, the mysql2 adapter does not support binding until the current Rails 4.0. I am surprised! You can tell this with snip code 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 

It is also useful for you:

(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 

What is it! And I think I can turn to Postgres!

Answer 5

I am also surprised that the preparation method is missing. Of course, in the general setup of ActiveRecord and Mysql2, ActiveRecord should avoid strings and then use libmysql, and I find this to be a bit of a bother.

At the same time, you can use https://github.com/brianmario/mysql2/tree/stmt

Answer 6

The rails and Active Record adapter for MySQL do not have support for prepared statements, as far as I know:

http://patshaughnessy.net/2011/10/22/show-some-love-for-prepared-statements-in-rails-3-1

This is due to the fact that they do not actually use operator acceleration and can actually slow down due to the lack of planning for MySQL queries.

Answer 7

You can also use mysql2-cs-bind gem: https://github.com/tagomoris/mysql2-cs-bind