[RESOLVED] binding identifiers possible?
Results 1 to 8 of 8

Thread: [RESOLVED] binding identifiers possible?

  1. #1
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,619

    resolved [RESOLVED] binding identifiers possible?

    Is it possible to bind arguments to identifiers? The particular case I'm insterested in is being able to take external input for INTERVALs
    Code:
    -- binding the "multiplier" part works
    INTERVAL :period DAY
    
    -- but is it possible to bind values for the period type identifier "DAY", "MONTH" etc?
    INERVAL :period :period_type
    Or is sprintf the only available solution?

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    From what I understand (but am failing to come up with a solid manual reference to support), parameters in a prepared statement can only be "simple values" - emphasis on the values. In other words, you can't use them to alter the syntax/structure of the SQL query itself - only data components.

    Then again... why would you want to use a bound parameter here anyway? What benefits would you gain versus the sprintf method?

    EDIT: Perhaps you're looking for the SQL driver to know that the only identifiers/keywords that would be valid for "eriod_type" are the ones listed in the table here?
    Last edited by bradgrafelman; 09-13-2013 at 11:08 AM.

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,820
    Yeah, I think I ran into that before, too, at least in the PostgreSQL world. Assuming you can't bind them, I might create a little function with an array of allowed values so you can validate them before injecting them into the SQL. (Or maybe stick it all into a stored procedure, which could do the validation itself?)
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  4. #4
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,773
    Quote Originally Posted by bradgrafelman
    From what I understand (but am failing to come up with a solid manual reference to support), parameters in a prepared statement can only be "simple values" - emphasis on the values. In other words, you can't use them to alter the syntax/structure of the SQL query itself - only data components.
    What it boils down to is that the DBMS can't prepare the statement without knowing which tables/columns/indexes are involved.


    (but am failing to come up with a solid manual reference to support),
    For MySQL, http://dev.mysql.com/doc/refman/5.6/en/prepare.html paragraph 2
    Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  5. #5
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    Quote Originally Posted by Weedpacket View Post
    What it boils down to is that the DBMS can't prepare the statement without knowing which tables/columns/indexes are involved.
    Seems like even that is too boiled down as to still be correct. After all, in johanafm's example above, not knowing whether :period_type is "DAY" or "MICROSECOND" doesn't affect anything that you mentioned. (In fact, I would say it doesn't even change any of the loose/ambiguous terms I mentioned above - the "syntax/structure of the SQL query" since, after all, it's just a unit describing a value.)
    Last edited by bradgrafelman; 09-14-2013 at 01:59 AM.

  6. #6
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,773
    Well, a little clarification might be needed from johanafm; the things in MySQL that read INTERVAL n DAYS aren't the same thing as those in standard SQL. There, INTERVAL is a distinct data type, of which INTERVAL • DAYS would be a subtype. I think the type of the value would affect how it's handled. In MySQL it's described as a keyword and - like a lot of the keywords in SQL - it's a noise word.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  7. #7
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,619
    I am talking about using it for arithmetic purposes only, and not as a data type. For data types I realize that you' probably consider year month interval and day second interval as separate subtypes. However, it would seem to me that when it comes to arithmetic second, day and month are nothing but qiantifiers, just like m, K, M are.

    Either way, the real reason I asked was that it'd be easier and cleaner if it was possible to bind both the numeric and quantifier parts. But lacking that option I'll go with an in_array comparison against the provided quantifiers and sprint_f it.

  8. #8
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,773
    Quote Originally Posted by johanafm
    I am talking about using it for arithmetic purposes only, and not as a data type. For data types I realize that you' probably consider year month interval and day second interval as separate subtypes. However, it would seem to me that when it comes to arithmetic second, day and month are nothing but qiantifiers, just like m, K, M are.
    I guess you'd have to take that up with the SQL standards boards and/or MySQL's developers, then
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •