Please don't do anything until you've studied preparse() and t/preparse.t in the DBI.
It is intended that this become a common 'pre-parser' for all drivers and, through it, the DBI can offer important new functionality for all drivers like supporting ODBC escape sequences. I'm quite sure I've mentioned this several times on dbi-dev etc. I was planning to work on adding an interface to hold placeholder info when I next get to work on DBD::Oracle (so I could see what DBD::Oracle needed). Scott Hildreth has done much of the leg-work on preparse() for me so I've copied this to him as well. I'm close to a new release of the DBI (tidying up loose ends) so maybe between you Jeff, Scott, and myself we can push preparse() along a little further in time for that release. On Sat, Mar 09, 2002 at 08:41:28PM -0800, Jeffrey W. Baker wrote: > I have considered the DBD::Pg parser tonight, and have identified many > cases where it simply will not work. The parser simply fails on many > different cases, so I plan to write a new one. This may show up in > DBD::Pg 2.0. > > The existing parser scans the statement during prepare, looking for > occurences of '?', ':1', or ':foo'. These placeholders are renamed > ':pn' for values of n from zero to one less than the number of > placeholders, except for ':foo' style which are not changed. A hash is > made with placeholders as keys and values to be determined. Sounds like it was copied from DBD::Oracle's which does similar stuff (because DBD::Oracle used named binding so numeric placeholders need to be made into non-numeric first). > At binding time, the ordinal identifier is upgraded to ':pn' style, and > the bound value is stored in the hash. > > During execute, DBD::Pg again parses the statement. The exact code has > been copied from dbd_preparse into dbd_st_execute. This wastes CPU > cycles of course and also means both parsers must be patched to make an > effective change. That's certainly daft. > I believe a much more efficient parser can be made from this mess. The > parser should scan the statement -- without the current parser's edge > case bugs -- making a list of statement components. The components > would either be SQL taken literally from the statement or placeholders > to be filled in. During bind, the placeholders components will take a > copy of the bound value. The statement can be assembled during > execution by walking this list and sprintf()ing the values into a final > buffer. > > For examle, 'SELECT * FROM emp WHERE fname = :fname and lname = :lname > ORDER BY id' would become: > > Item 1: 'SELECT * FROM emp WHERE fname = ' > Item 2: Placeholder for :fname > Item 3: ' and lname = ' > Item 4: Placeholder for :lname > Item 5: ' ORDER BY id' > > Binding is simplified: simply scan for the desired placeholder name and > copy the value and data type. Execution is simplified because we need > not reparse the statement. The bound values need to be quoted and > escaped, then all the parts of the statement copied into the output > buffer and forwarded to the database library. Using preparse() you can ask it to return the statement to you as a sprintf string. Placeholders are rewritten as %s and any other percents are doubled up. Tim.
