Re: :PgSQL: More Queestions

>
>
> On Tuesday, November 19, 2002, at 03:42 PM, Jeff Urlwin wrote:
>
> > You probably only need dTHR to support older, pre-threading perls. I
> > don't
> > believe you need the #ifdef, but it can't hurt (except visually in your
> > code).
>
> Okay. What is it?

See the other posts. They did a better job of describing it.

>
> > In the preparse(), we're looking for placeholders to notify DBI that
> > we need
> > specific parameters to execute the query and, in the case of DBD::ODBC,
> > later notify the ODBC Driver that we are binding parameters (and what
> > type
> > they are, VARCHAR, etc). Then the Driver does the binding in whatever
> > DBMS
> > specific way it needs to. You may have to do more, as you *are* the
> > driver.
> > Note that there is also a way in DBD::ODBC to ignore :foo style
> > parameters
> > because some databases use that for syntax in stored procedures or
> > triggers.
> > For example, with Oracle a trigger can access :old.column_name or
> > :new.column_name and DBD::ODBC allows you to turn off treating
> > :anything as
> > a bind variable to support that. You may not need that...
>
> I understand that the goal is to convert the placeholders from '?' or
> ':nn' to the PostgreSQL internal version (':pn'). What I'm referring to
> specifically, however, is this snippet from DBD::Pg:
>
> if (in_literal) {
> /* Check if literal ends but keep quotes in literal */
> if (*src == in_literal) {
> int bs = 0;
> char *str;
> str = src-1; /* Back a character. */
> while (*(str - bs) == '\\')
> bs++;
> if (!(bs & 1))
> /* bs is an even number? */
> in_literal = 0;
> }
> *dest++ = *src++;
> continue;
> }
>
> in_literal is set when the last character wasn't a placeholder
> character ('?' or ':') and is either a single or a double quotation
> mark. So while I understand that one might want to ignore placeholder
> characters, I don't really understand what the above code is doing.
> Probably'll be easier for me after I've been looking a C for a while...

I'm not sure what it's really trying to do, either, really...

>
> Maybe it's just too complex, because, looking at DBD::ODBC's
> dbd_preparse(), the handling of literals in the query seems a good deal
> more straight-forward (though it doesn't appear to handle '\'' or "\""
> -- am I reading that right?

Nope, it handles " or '.

if (*src == '"' || *src == '\'') {
etc...
}
>
> > It's going to depend upon what you need to handle. For the most part,
> > it
> > shouldn't change after the prepare, but in DBD::ODBC, for example,
> > it's more
> > complex because some statements can return multiple result sets.
>
> Ah, that makes sense. Not sure if it's an issue for PostgreSQL, but I
> doesn't appear to be much of an overhead to set it on a per-execute
> basis...

Actually, if you can get away with doing it only once, the first execute, go
with it. DBD::ODBC tries to do that, but rechecks under two conditions:
1) we "know" there are multiple result sets in this query via already
experiencing it
2) the user sets a DBD::ODBC private attributed to recheck the result set
types (this is to support nasty things like stored procedures returning only
one result set per call, but a different result set based upon the input
(yes, I've seen this!).

My advice: if you don't have to support multiple result sets, do it once per
execute. If you setup that "flag" to avoid re-doing work and find that you
need to support multiple-result sets, you can always clear the flag...

>
> > svp is a temporary reference to obtain a pointer to a scalar value
> > (scalar
> > value pointer). You are then casting it to a pointer to a phs_t, which
> > holds your parameter information. You'll "create" the phs_t instances
> > when
> > you preparse the query. In DBD::ODBC, the ftype is queried from the
> > driver
> > itself (may go back to the database for information) to determine if
> > it's
> > numeric, varchar, etc. The phs_t instance can hold whatever you need
> > to
> > track the parameter (type, scale, etc). Some drivers assume
> > everything is a
> > varchar and the database itself performs the conversion.
> >
> > So: all_params_hv is in your statement handle and you actually put the
> > information in the all_params_hv hash when you preparse the statement
> > (and
> > update it during execute/bind_param). Each "phs_t" represents a
> > parameter
> > in the query. The information contained in the phs_t instance comes
> > from
> > your parsing the query and, possibly querying the database to
> > determine the
> > type of the parameter.
> >
> > Also, the all_params_hv is handy in handling the relatively new DBI
> > attribute ParamValues...
>
> Yes, thank you. I'm spending a lot of time right now just studying the
> code in dbd_preparse() and dbd_st_execute() in both DBD::Pg and
> DBD::ODBC. I think I'll keep at it until I feel I understand it as well
> as I can, and then post my thoughts on what to do next.

I would make the statement that DBD::Oracle may provide a better reference
on the pre-parse stuff. DBD::ODBC's is probably a bit watered down from
DBD::Oracle -- especially because I'm avoiding comments.

Regards,

Jeff