query parsing (was: Re: [PEAR-DEV] Re: DB::oci8 question)

"Tomas V.V.Cox" wrote: > > "Stig S. Bakken" wrote: > > > > yavo wrote: > > > > > Ok this was silly you wouldn't have a select when calling a stored proc. But > > > the question remains. > > > > > > > > > yavo > > > > > > PS I'm like talking to myself here. :) > > > > I agree there is a need for such a placeholder. I'm not aware of other > > dbms'es than Oracle who can retrn refcursors, does for instance Sybase, > > Informix or MS-SQL have it or a similar concept? If so, we should try > > finding a model that fits all. > > > > One question is, is it possible to have a lob placeholder in the same > > query as a refcursor one? I guess the answer is yes, which means we > > need yet another placeholder char for refcursors. # is used in SQL > > syntax in some dbms, what about @? > > > > Just an idea is to use the same "!" (DB_PARM_MISC), provide a system to > detect the type and redirect to the properly method if any. Hm, how would you go about detecting whether a placeholder is for example a refcursor? I think you will end up parsing (rather than splitting) the entire query, and sometimes even that will not be enough (what if you don't know the type of the prpc param that returns a refcursor?). Full query parsing would be a cool feature, because it lets us do more compatibility stunts at the SQL level, but this is a big design issue that has to be done consistently in all of DB, not as a side-effect of a specific feature. I'm open to discussing query parsing though. Some ideas: * implement a real SQL parser (in C) based on a standardized syntax (SQL92 or newer) * take the rewriteQuery method further and make this layer cusomizable, so users can opt to disable the "standard SQL" syntax and do native, unintercepted queries, or have special compatibility plugins for example for creating tables with metabase's xml format * make a common way of groping around in table structures, ala "DESCRIBE" or selecting from data dictionary tables, be it a set of "views" that are implemented in the query parsing layer, or separate SQL commands * views can be emulated for databases not supporting them These are some of the problems I can think of off the top of my head: * dbms-specific query keywords such as MySQL's "REPLACE" or Oracle's "INSERT RETURNING". we can choose to ignore them or identify each case and try to find workarounds * a query parser needs to have information about tables and such, which obviously needs caching, which makes the system vulnerable to changes not coming through PEAR DB. this can be fixed or at least remedied with configuration options though. * types need to be well "unified", I'm not sure it's 100% doable. we can learn from metabase here - Stig