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