2

I use MySQL DB and MySQL2 as adapter.

I want to execute custom sql-statements in Rails.

I need a method that would allow to prepare sql-statements like in PHP, like this:

stmt = prepare("INSERT INTO tab (col1, col2) VALUES (?, ?)", ["foo", "bar"])

I found, though, that neither ActiveRecord nor MySQL2 have no such methods.

But I found that there are several exec_* methods in ActiveRecord::ConnectionAdapters::AbstractAdapter and I think they are exactly for my task but don't know how to use them, their API documentation has very little info:

exec_insert(sql, name, binds)

Executes insert sql statement in the context of this connection using binds as the bind substitutes. name is the logged along with the executed sql statement.

I've tired this

sql = "INSERT INTO tab (col1, col2) VALUES (?, ?)"
r = ActiveRecord::Base.connection().exec_insert(sql, "someName", [10, "foo"])
puts r

But got an error:

`query': Mysql2::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 '?, ?)'

So how to use these methods? What is a name attribute? What is a type of a bind parameter? How to write sql-statement, what to put instead of ??

UPD:

I've written prepare method to use in Rails. See in github.

Green
  • 28,742
  • 61
  • 158
  • 247

2 Answers2

0

Too late to answer and with all the rails upgrade but probably this what you were looking for -

ActiveRecord::Base.connection().exec_query('INSERT INTO table(column1, column2) VALUES($1, $2)', 'someName', [[nil, 10], [nil, 'column2Value']])

Please note that I have used and tested $1, $2 for Postgres only as it is the convention used to prepare query in postgres.

It maybe different for MySql & other databases. For Mysql I think it should be ? instead of $1, $2 etc

sahilbathla
  • 519
  • 3
  • 10
  • If using mysql2 gem, then this will not work since `exec_query` ignore binds. See: https://github.com/rails/rails/issues/20498. Though I think your solution would work in PG. – hirowatari Jan 12 '19 at 15:24
-1

I do not recommend you use exec_insert because it's an "internal" method, very prone to changes in the future.

Rails approach is as follows: either you use AR goodies (i.e. you bypass SQL) or you use plain SQL, mixing the two is not a practical option.

Bindings (3rd parameter of exec_inser) are great tools, they allow you to dynamically check for the values you pass to the DB against the DB columns, but their use is undocumented because you are not supposed to use them.

1) Build the SQL query by hand

sql = "INSERT INTO tab (col1, col2) VALUES (10, 'foo')"

2) Fire it into the DB

r = ActiveRecord::Base.connection.execute(sql)

3) Check the result. This is DB Adapter specific.

Cheers,

P.S. Do not forget created_at and updated_at columns, set them to 'DateTime.now'.

Idavod
  • 167
  • 5
  • But are bindings also not beneficial for addressing the need to otherwise quote values? I am looking to do something similar as the OP but have not found a way to do parameterized queries. – twelve17 Apr 23 '13 at 20:46
  • Can you make an example of what you are looking for? I reccomend you post a new question anyway. – Idavod Apr 23 '13 at 21:01