Failed to insert value into tables in Ruby Mysql

Question

I am trying to use Ruby to insert values into MySQL on localhost. The value i want to insert is the result from Twitter search. My program can successfully write the result to a file, so now i want to insert the result to MySQL. Here is part of my code:

results = @search.perform("yahoo", 100)
client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "123", :database => "db1")
results.map do |status|
insert = client.query ("INSERT INTO table1 (fromuser, tweet) VALUES (#{status.from_user},#{status.text})")
end

The error is "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 " tweet " at line 1 (Mysql2::Error).
What caused the error?

Another problem i have found is that when i used following code to insert value to MySQL ,i got another error: "Unknown column 'a' in 'field list' (Mysql::ServerError::BadFieldError)"

require 'mysql'
require 'rubygems'
sql = Mysql.real_connect("localhost", "root", "123", "db1")
st1 = "a"
st2 = "b"
user_string = "(#{st1},#{st2})"
query="INSERT INTO table1 (fromuser, tweet) VALUES" + user_string
sql.query(query)

I want to insert "a" and "b" into table. How to solve this?

Thanks in advance,

tjmw · Accepted Answer · 2013-04-13 21:56:30Z

2

Like Andrew said, you definitely want to escape your data.

I think you also need to quote the values:

insert = client.query("INSERT INTO tweets (from_user, tweet_text)
                       VALUES ('#{client.escape(status.from_user)}',
                               '#{client.escape(status.text)}')")

answered Apr 13, 2013 at 21:56

tjmw

3092 silver badges4 bronze badges

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

2 Comments

user605947 Over a year ago

Thank you, tjmw. I really appreciate it. It works now. I need the quote and the escape.

Andrew France Over a year ago

Ah, well done. I don't use MySQL and I assumed the adaptor would add the quotes when escaping.

Andrew France · Accepted Answer · 2013-04-13 21:18:50Z

1

You need to use CREATE TABLE to create a table in your database to insert the data into. At the moment you are saying you want to insert the data into the database name ("db1") itself.

Also, you must escape your data first:

insert = client.query("INSERT INTO tweets (from_user, tweet_text)
                       VALUES (#{client.escape(status.from_user)},
                               #{client.escape(status.text)})")

answered Apr 13, 2013 at 21:18

Andrew France

4,8991 gold badge27 silver badges27 bronze badges

1 Comment

user605947 Over a year ago

The name of my table is actually db1. I think i should name it a new one. But i still got the same error after i escape my data.

Collectives™ on Stack Overflow

Failed to insert value into tables in Ruby Mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related