# How to concanate this mysql query? **URL:** \ **Category:** Ruby\ **Created:** [January 2, 2007, 12:45pm UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943 "2007-01-02T12:45:23Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 2, 2007, 12:45pm UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/1 "2007-01-02T12:45:23Z")
could any pl suggest me how to create this mysql query string in ruby? I have a directory c:\mooney.com\NSEDATA\ inside i have a couple of csv files that i need to update onto database of nsehistory. # so i used the following code require ‘rubygems’ require ‘mysql’ ``` def with_db dbh = Mysql.real_connect('localhost', 'root', ' ******', 'historicalstockprice') begin yield dbh ensure dbh.close end end with_db do |db| db.query('drop table if exists nsehistory') db.query('create table nsehistory( id INT NOT NULL PRIMARY KEY ``` AUTO\_INCREMENT, symbol TINYTEXT,series VARCHAR(4),open DEC,high DEC,low DEC,close DEC,last DEC,prevclose DEC ,totrdqty INT,totrdval INT,date DATE)') ``` end ``` # we will loop through the directory NSEDATA and load the nsehistory database Dir.foreach(“c:\mooney.com\NSEDATA”) do |f| ``` if f.include? "csv" with_db do |db| csv_name = "c:\\mooney.com\\NSEDATA\\#{f}" res = db.query("load data infile #{csv_name} into table ``` nsehistory fields terminated by ‘,’ lines terminated by ‘\n’ ignore 1 lines") res.free end ``` end end ``` But this returns me saying MYSQL syntax error ================================================= 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 ‘c:\mooney.com\NSEDATA\cm01APR2002bhav.csv into table nsehistory fields terminate’ at line 1 c:/ruby/lib/ruby/1.8/mysql.rb:453:in `read' c:/ruby/lib/ruby/1.8/mysql.rb:345:in `read\_query\_result’ c:/ruby/lib/ruby/1.8/mysql.rb:160:in `real_query' c:/ruby/lib/ruby/1.8/mysql.rb:275:in `query’ C:/mooney.com/historicalqoute/historicalentry.rb:27 C:/mooney.com/historicalqoute/historicalentry.rb:9:in `with_db' C:/mooney.com/historicalqoute/historicalentry.rb:25 C:/mooney.com/historicalqoute/historicalentry.rb:22 c:/ruby/lib/ruby/1.8/mysql.rb:453:in `read’: 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 ‘c:\mooney.com\NSEDATA\cm01APR2002bhav.csv into table nsehistory fields terminate’ at line 1 (Mysql::Error) from c:/ruby/lib/ruby/1.8/mysql.rb:345:in `read_query_result' from c:/ruby/lib/ruby/1.8/mysql.rb:160:in `real\_query’ from c:/ruby/lib/ruby/1.8/mysql.rb:275:in `query' from C:/mooney.com/historicalqoute/historicalentry.rb:27 from C:/mooney.com/historicalqoute/historicalentry.rb:9:in `with\_db’ from C:/mooney.com/historicalqoute/historicalentry.rb:25 from C:/mooney.com/historicalqoute/historicalentry.rb:22 =============== cheers ---
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 2, 2007, 4:31pm UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/2 "2007-01-02T16:31:59Z")
On Jan 2, 2007, at 6:45 AM, rajibsukanta wrote: > could any pl suggest me how to create this mysql query string in ruby? > > I have a directory c:\mooney.com\NSEDATA\ > > inside i have a couple of csv files that i need to update onto > database > of nsehistory. > ``` > end > end > > ``` > > But this returns me saying MYSQL syntax error This looks like more a of a mysql problem. But I think you need to put ‘’ around your file name like this: res = db.query(“load data infile ‘#{csv\_name}’ into table nsehistory fields terminated by ‘,’ lines terminated by ‘\n’ ignore 1 lines”) -Mat ---
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 3, 2007, 2:50am UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/3 "2007-01-03T02:50:53Z")
Mat S. wrote: > > [snip: code setup] > > end > > -Mat > > Thanks I also tried to put ’ around the csv\_name variable but … when i modify my query as res = db.query(“load data infile ‘#{csv\_name}’ into table nsehistory fields terminated by ‘,’ lines terminated by ‘\n’ ignore 1 lines”) all the forward slashes in the csv\_name variable gets ignored and i get back an error saying c:mooney.comNSEDATAthecsvbhav.csv file not found. So it seems the ’ around the csv\_name variable is causing tthe \ to be ignored. so my question is how can i make a string like this abc= “xyz ‘p:\q\r.x’ m” btw… when i place the csv file in the default position of the mysql server( c:\programfiles\mysql\data\nsehistory) and run my ruby code it is correctly updating the database. also using ACTIVERECORD is it possible to issue load infile SQL query ? cheers ---
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 3, 2007, 5:03am UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/4 "2007-01-03T05:03:08Z")
On Jan 2, 2007, at 8:50 PM, rajibsukanta wrote: > > > database > > > res.free > > > nsehistory fields terminated by ‘,’ > > > 1 lines") > > so my question is how can i make a string like this > > abc= “xyz ‘p:\q\r.x’ m” I’m not sure what you mean. That string looks fine to me. irb agrees. > query ? > ActiveRecord wouldn’t buy you much in this case. It’s mainly meant > for CRUD operations. Keep playing with it some. Actually now that I > look more closely, I would make more use of ’ ’ to quote strings > rather than " " . If you use “” things like \n will get interpreted > by ruby, but you actually want them to be preserved until the mysql > level. Try something like: > > > csv\_name = ‘c:\mooney.com\NSEDATA\’+f > > > res = db.query(‘load data infile '’+csv\_name+‘' into table > > > nsehistory fields terminated by ',' > > > lines terminated by '\n' ignore 1 lines’) Also look around for any functions on the mysql connection that might allow for bound queries or otherwise properly escaping data. I don’t generally go this low level from within ruby so I can’t help you much more than that. Good luck! -Mat ---
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 3, 2007, 8:11am UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/5 "2007-01-03T08:11:29Z")
Thanks for the lead it is resolved Try something like: > > > > csv\_name = ‘c:\mooney.com\NSEDATA\’+f > > > > res = db.query(‘load data infile '’+csv\_name+‘' into table > > > > nsehistory fields terminated by ',' > > > > lines terminated by '\n' ignore 1 lines’) i had to do it this way csv\_name = ‘c:\’+‘\mooney.com\’+‘\NSEDATA\’+‘\’ +f and now it is fine … cheers ---
**Author:** ![rajibsukanta](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea666f/32.png) [@rajibsukanta](https://www.ruby-forum.com/u/rajibsukanta)\ **Post date:** [January 3, 2007, 3:24pm UTC](https://www.ruby-forum.com/t/how-to-concanate-this-mysql-query/80943/6 "2007-01-03T15:24:29Z")
On Jan 3, 2007, at 2:10 AM, rajibsukanta wrote: > csv\_name = ‘c:\’+‘\mooney.com\’+‘\NSEDATA\’+‘\’ +f > and now it is fine … Nice work. You should be able to safely remove those +'s, but do what works best for your situation. Good luck with the rest of the project. -Mat