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
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
···
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.
[snip: code setup]
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
Mat Schaffer 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.
> [snip: code setup]
>
> 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
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
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
···
On Jan 2, 2007, at 6:45 AM, rajibsukanta wrote:
Mat Schaffer 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.
[snip: code setup]
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
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
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"
I'm not sure what you mean. That string looks fine to me. irb agrees.
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 ?
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
···
On Jan 2, 2007, at 8:50 PM, rajibsukanta wrote:
On Jan 2, 2007, at 6:45 AM, rajibsukanta wrote:
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
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
···
On Jan 3, 2007, at 2:10 AM, rajibsukanta wrote:
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 ...