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