Re^3: Variable interpolation in a file to be read in

Replies are listed 'Best First'.
Re^4: Variable interpolation in a file to be read in by runrig (Abbot) on Oct 06, 2011 at 18:33 UTC
Always use placeholders... Beware of absolutes. Placeholders are not always the way to go.	[reply]
Re^5: Variable interpolation in a file to be read in by afoken (Chancellor) on Oct 06, 2011 at 20:21 UTC
Re^4: DBI, place holders and CGI forms (quote++) Problem 1: "`limit ?`" with DBD::mysql. Seems to be a bug introduced in some version of DBD::mysql. Maybe the placeholder support for `limit` was an accidental feature removed by a "bugfix", but as this "bugfix" breaks existing code, it is a bug, not a bugfix. Maybe it is related to changed placeholder handling code in 4.014 / 4.015, and maybe it could be fixed using the `mysql_bind_comment_placeholders` attribute. Or it was broken in 3.0008_1, when the LIMIT parsing was changed. I don't know, I don't like MySQL, and so I don't use it. Problem 2: Datetime literals with DBD::ODBC. Seems to be caused by using an incompatible datetime format in the application, according to the reply by mje, the maintainer of DBD::ODBC. Where the advice to use DBI bind parameters can go wrong (long) Problem 3: "`col like ?`" instead of "`col like 'prefix%'`" forces Oracle's query optimizer into a full table scan, because it can no longer see the special case that only col values starting with `prefix` are relevant and thus an index on `col` can be used. When I learned to use Oracle, I was told to avoid `like` when `substr` can be used. I have no Oracle instance available, but I think the query optimizer should be able to use the index with "`substr(col,1,length(:p1))=:p1`". Can a DBI Placeholder accept multiple values? Problem 4: "`col in (`list`)`". Yeah. SQL sucks here. This is not a DBI problem. You have to construct a different SQL statement for each different list length. And in parallel, you can/have to construct a list of parameters to be passed to `$sth->bind_param()` or `$sth->execute()`. Or, you use a wrapper to do so: igelkott's reply proposes to use the special "??" placeholder of DBIx::Simple. I still see no reason to use `quote()` in all but the first case. And even this "limit" problem would better be fixed by reconfiguring or reparing DBD::mysql, or by downgrading it to the latest "known good" version. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^6: Variable interpolation in a file to be read in by runrig (Abbot) on Oct 06, 2011 at 22:02 UTC
I've run into ODBC drivers that did not cope very well with placeholders and executing statements multiple times, substr() doesn't fix everything when you do sometimes want wildcards at the beginning of a string, and if I might have wildly varying numbers of "?" in "IN (?,?,?)" type clauses, I usually just go with quote().	[reply]