how to concanate this mysql query?

[rajibsukanta]

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

 

[Mat Schaffer]

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

 

[rajibsukanta]

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

 

[Mat Schaffer]

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:

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

 

[rajibsukanta]

Thanks for the lead it is resolved
Try something like:i had to do it this way
csv_name = 'c:\\'+'\\mooney.com\\'+'\\NSEDATA\\'+'\\' +f
and now it is fine ...




cheers

 

[Mat Schaffer]

Thanks for the lead it is resolved
Try something like:
i had to do it this way
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