Ruby SQL Insert using Mysql2 gem

Question

I'm trying to insert into a remote mysql database. I am able to connect correctly and can query 'select' no problem from it. However, I cannot perform inserts into the same table that I can select from. I suspect it has something to do with my binds, but this is nearly identical to what I was using to get sqlite3 working which I think uses the same Arel to insert.

@result = @db.query("insert into lead_to_processes (case_number, style_of_case) values (?,?)", [
      self.case_number.to_blob.force_encoding("UTF-8"),
      self.style_of_case.to_blob.force_encoding("UTF-8")
    ]
)

Ultimate goal is to be able query a remote database from inside of a model and insert data into it. I've tried using Octopus and that didn't quite work because the tables will be different from the databases.

I have full permissions with this user on the database.

So following guidance from comments i changed the syntax and am getting a different error

Mysql2::Error: You have an error in your SQL syntax;

However i'm doing the query like this now

@db = Mysql2::Client.new(connectionstring)

 @case_number            =  @db.escape(self.case_number)
 @style_of_case          =  @db.escape(self.style_of_case)

@db.query("insert into lead_to_processes (case_number, style_of_case) VALUES
(#{@case_number}, #{@style_of_case})

Any ideas or guidance? I've also tried this with '' encapsulating the variables that i'm inserting

Do you get any errors? Could you post them? Do you have write permissions for that database? Or only read? — Mischa
– Mischa, Commented Apr 9, 2013 at 13:24
I don't believe that the mysql2 gem provides binds for queries, you have to create valid SQL yourself to pass to the query. github.com/brianmario/mysql2 — mguymon
– mguymon, Commented Apr 9, 2013 at 13:35

joshweir · Accepted Answer · 2016-05-14 12:34:59Z

1

I guess there were some weird characters in my code so I had to force UTF-8 encoding and then removed the characters using gsub below, everything is flowing now.

Thanks for the advice

@db.escape(self.style_of_case.force_encoding("UTF-8"))
@db.escape(self.case_number.gsub(/[\xC2]/,'').gsub(/[\xA0]/,'').force_encoding("UTF-8"))

edited May 14, 2016 at 12:34

joshweir

5,6753 gold badges43 silver badges67 bronze badges

answered Apr 9, 2013 at 19:49

Austio

6,0951 gold badge23 silver badges35 bronze badges

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

Comments

Miguel-F · Accepted Answer · 2013-04-09 20:04:07Z

0

Is it possible that you are missing an end quote?

this

@db.query("insert into lead_to_processes (case_number, style_of_case) VALUES
(#{@case_number}, #{@style_of_case})

should be

@db.query("insert into lead_to_processes (case_number, style_of_case) VALUES
(#{@case_number}, #{@style_of_case}") <== notice the quote at the end.

edited Apr 9, 2013 at 20:04

Miguel-F

13.6k6 gold badges40 silver badges66 bronze badges

answered Apr 9, 2013 at 19:45

Karl

231 silver badge6 bronze badges

1 Comment

Austio Over a year ago

thanks for response, posted answer as you were posting this, there were garbage characters in the data that was causing the error. Thanks for the help though @Karl

Collectives™ on Stack Overflow

Ruby SQL Insert using Mysql2 gem

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related