Thread: MySQL2 Question

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10

    MySQL2 Question


    This might not be the right place to put this, if it's not I do
    apologize, but I hope maybe someone here can at least point me in the
    right direction.

    I'm currently creating a module to download sports box scores from
    websites (in HTML) that will insert the data into databases using regex
    and loops so that the data can be further analyzed.

    I ran into a problem with the names of some players having non letter
    punctuation (commas, periods, apostrophes, dashes, etc...), which I
    hadn't fully expected. I solved the issue of 'getting' the names with
    the regex, but mysql rejects the inserts into a varchar field with this
    non text characters. Right now my solution is to remove the non text
    characters on the insert of the athletes name, but then the name is
    stored improperly. I know PHP has an 'escape string' function that
    handles these things automatically. Is there something built in to ruby
    to handle something like this. If there isn't is there a way to write a
    gsub that will replace these non text characters with a \ before them,
    all at once?

    i.e if it's a ",", ".", "-" replace it with \, , \. , \- or do i have to
    do each one individually?
    Last edited by jemagee; October 16th, 2012 at 01:04 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    12
    Hi,

    This sounds like a MySQL problem. What happens when you execute the query directly from the command line?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    I haven't really tried that yet, when typing things out i realized that since my query is written in ruby with the text inserts in 'single quotes' ('), the ' in a name like o'neal might throw it off.

    Tonight I'll rewrite it with " instead of ' and see if it works, that might be it.

    The bigger part was making sure I got all the information (and the regex for that required some tweaking)

    This is my first major undertaking in ruby, and it's a 'side project' in that I work 50 hours a week already at smething else.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    12
    If you're just looking to replace characters for the MySQL query, use the "escape_string" method as described in the manual.

    But you cannot "escape" all non-alphanumeric characters like the dot or the comma, because those don't have a special meaning in SQL. If that's actually your problem, it's definitely a problem of the database.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    I know how to escape them in ruby but how do i escape them when i don't know for sure they are there.

    Is there a way to have a gsub written that says if you find a . or a , or a ' that you replace it with \. or \, or \'. I thought maybe with a backreference, but that wouldn't work if there are multiple characters in the name, which is possible

    Right now what I'm doing is just stripping out the 'non text' characters using the gsub ,but that' not ideal. I'd prefer to keep the last name as O'neal, as opposed to oneal.

    I will try the double quote " instead of single quote ' tonight, if that's the solution I'll be a bit bummed.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    12
    As I already said, you cannot escape "." or ",". You just can't. There seems to be a misunderstanding of what escaping even is. The purpose of escaping is to turn special characters that have a certain meaning in the particular language into literal characters. You can escape quotes (which would otherwise be interpreted as string delimiters) or the backslash. But neither the dot nor the comma have a special meaning in Ruby or SQL.

    So it might be a good idea to forget this approach and instead look for the actual problem. Not being able to insert a comma in a database is not normal. So either there's a general problem in how you do the queries in Ruby, or your MySQL/database is messed up.

    But like I said: Forget the "gsub". That doesn't work and won't solve any problem.

IMN logo majestic logo threadwatch logo seochat tools logo