[RESOLVED] binding identifiers possible?

johanafm

Is it possible to bind arguments to identifiers? The particular case I'm insterested in is being able to take external input for INTERVALs

-- 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?

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.

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 ":period_type" are the ones listed in the table here?

NogDog

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?)

Weedpacket

bradgrafelman wrote:
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.

bradgrafelman

Weedpacket;11033161 wrote:
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.)

Weedpacket

Well, a little clarification might be needed from johanafm; the things in MySQL that read [font=monospace]INTERVAL n DAYS[/font] aren't the same thing as those in standard SQL. There, [font=monospace]INTERVAL[/font] is a distinct data type, of which [font=monospace]INTERVAL • DAYS[/font] 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.

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.

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.

Weedpacket

johanafm wrote:
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 🙂