CODEWITHSUNDEEP
mysqlruby
user605947
user605947

Reputation: 119

Failed to insert value into tables in Ruby Mysql

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,

Upvotes: 1

Views: 3958

Answers (2)

tjmw
tjmw

Reputation: 309

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)}')")

Upvotes: 2

Andrew France
Andrew France

Reputation: 4879

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)})")

Upvotes: 1

Related Questions