On 8/22/05, John Siracusa <[EMAIL PROTECTED]> wrote:
>
> On 8/21/05 10:38 PM, [EMAIL PROTECTED] wrote:
> > I'm using DBD:DB2.
> > Maybe quatation (') rwapping data must be dropped, and
> > "CURRENT TIMESTAMP" keyword will be treated.
> >
> > my @new_values = ();
> > my @place_holders =
> > map {
> > if (/^CURRENT TIMESTAMP$/) {
> > $_;
> > } else {
> > push @new_values => $_;
> > "?";
> > }
> > } @args
> > ;
>
> I'm using Informix, which is apparently (and perhaps not surprisingly)
> similar to DB2 in this regard. I had to do something similar to what you
> describe above in my RDBMS-OO mapper module:
>
>
> http://search.cpan.org/~jsiracusa/Rose-DB-Object-0.0692/lib/Rose/DB/Object/M
> etadata.pm#allow_inline_column_values
>
> It's a bit of a pain, but until DBD::Informix becomes "smart" enough to
> detect this on its own (if that's even possible) it's something that has
> to
> be handled by the programmer (or, in my case, by the programmer's library
> :)
Patches are welcome - with test suite, etc.
The DBD::Informix code already has a function sqltoken() - in sqltoken.c and
sqltoken.h - which parses SQL and counts placeholders; see also
dbd_ix_preparse() in dbdimp.ec <http://dbdimp.ec> (which is an alternative
ad hoc solution based in part on the preparse() from either DBI or
DBD::Oracle of about 8 years ago).
You'd have to do a little work - it doesn't identify what type of token it
has found. There's also a 'cover function' called iustoken() available (with
SQLCMD, which can be downloaded from the Software Archive at the IIUG
http://www.iiug.org/software/, or from my private web site - contact me if
you need the code and can't get hold of it). This deals with a particular
nasty related to SET, MULTISET and LIST values in IUS (9.x or 10.x) Informix
database servers.
Using those, you can relatively easily identify the sequence of symbols that
make up the SQL statement. You'd have to worry about compound symbols, such
as DATETIME(2005-08-22 21:40:22) YEAR TO SECOND, and decide when those can
be replaced by placeholders in the SQL -- hint, they can't easily be
replaced by placeholders in the select-list of a SELECT statement such as
SELECT DATETIME(2005-08-22 21:40:22)) YEAR TO SECOND AS about_now, CURRENT
YEAR TO SECOND AS exactly_now FROM 'informix'.systables WHERE tabid = 1.
You can then concatenate the non-replacable portions of the SQL with the
placeholders for the replacable portions of the SQL, and isolate ther
replaced values. The code is deliberately and carefully non-destructive; the
tokens are identified by start position and one character after last.
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."