how to concanate this mysql query?

Discussion in 'Ruby' started by rajibsukanta, Jan 2, 2007.

  1. rajibsukanta

    rajibsukanta Guest

    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
    rajibsukanta, Jan 2, 2007
    #1
    1. Advertising

  2. rajibsukanta

    Mat Schaffer Guest

    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
    Mat Schaffer, Jan 2, 2007
    #2
    1. Advertising

  3. rajibsukanta

    rajibsukanta Guest

    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
    rajibsukanta, Jan 3, 2007
    #3
  4. rajibsukanta

    Mat Schaffer Guest

    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
    Mat Schaffer, Jan 3, 2007
    #4
  5. rajibsukanta

    rajibsukanta Guest

    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
    rajibsukanta, Jan 3, 2007
    #5
  6. rajibsukanta

    Mat Schaffer Guest

    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
    Mat Schaffer, Jan 3, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,142
  2. Ravi
    Replies:
    6
    Views:
    1,407
    Suchandra Thapa
    Jul 21, 2003
  3. Replies:
    2
    Views:
    6,191
  4. washakie
    Replies:
    4
    Views:
    922
    washakie
    Jan 15, 2008
  5. Love4llamas
    Replies:
    0
    Views:
    799
    Love4llamas
    Oct 13, 2011
Loading...

Share This Page