[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
Or is sprintf the only available solution?
-- 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
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.
High Energy Magic Dept.
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?)
What it boils down to is that the DBMS can't prepare the statement without knowing which tables/columns/indexes are involved.
Originally Posted by bradgrafelman
For MySQL, http://dev.mysql.com/doc/refman/5.6/en/prepare.html paragraph 2
(but am failing to come up with a solid manual reference to support),
Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.
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.)
Originally Posted by Weedpacket
Last edited by bradgrafelman; 09-14-2013 at 01:59 AM.
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.
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.
I guess you'd have to take that up with the SQL standards boards and/or MySQL's developers, then
Originally Posted by johanafm
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)