Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > how to concanate this mysql query?

Reply
Thread Tools

how to concanate this mysql query?

 
 
rajibsukanta
Guest
Posts: n/a
 
      01-02-2007
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

 
Reply With Quote
 
 
 
 
Mat Schaffer
Guest
Posts: n/a
 
      01-02-2007

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


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




 
Reply With Quote
 
 
 
 
rajibsukanta
Guest
Posts: n/a
 
      01-03-2007

Mat Schaffer wrote:
> 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

>
> 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

 
Reply With Quote
 
Mat Schaffer
Guest
Posts: n/a
 
      01-03-2007

On Jan 2, 2007, at 8:50 PM, rajibsukanta wrote:

>
> Mat Schaffer wrote:
>> 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

>>
>> 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

 
Reply With Quote
 
rajibsukanta
Guest
Posts: n/a
 
      01-03-2007
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

 
Reply With Quote
 
Mat Schaffer
Guest
Posts: n/a
 
      01-03-2007

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 ...


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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
MySQL-python-1.2.2 install with no mysql washakie Python 4 01-15-2008 08:15 PM
"mysql.h: No such file or directory" when building MySQL-python francescomoi@europe.com Python 2 05-11-2005 03:12 PM
DBD:mysql doesn't read mysql option file /etc/my.cnf file JL Perl 0 01-28-2005 03:19 AM
"Pure Python" MySQL module like Net::MySQL Ravi Python 6 07-21-2003 06:53 PM



Advertisments