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?

>>
>> * In dbd_st_prepare(), is there a way to determine NUM_OF_FIELDS, 
>> NAME,
>> etc. -- that is, before executing the statement?
>
> Only if you want to fully parse the SQL :)

Okay, that makes sense. Thanks.

> DBD::ODBC, specifically doesn't handle comments.  Reasoning: comments 
> are
> (typically) DB vendor specific and I do not believe that ODBC itself
> declares a comment capability.  Therefore, it's really not safe for
> something like DBD::ODBC to look for them.  It may be for you.  I do 
> not see
> *much* benefit in adding comments to the queries themselves, within 
> perl,
> but then again, if you had perl read a file of SQL Statements and have 
> it
> generically prepare() and execute() them, then, there's probably value
> there.

Yeah. We have a Bricolage script that does that, but, as it happens, we 
were parsing out comments in Perl before passing them to DBD:Pg's 
prepare(). I didn't notice that there were any tests in DBD::Pg's test 
suite for comments, either. And it's not documented. So the way I look 
at it, if I leave the current parser, I'll leave the comment parsing 
part. But if I change it (as I'm seriously considering, in light of 
PostgreSQL 7.3's support for prepared statements), I'll probably do no 
parsing for comments.

> 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
isan 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...

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?

> 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...

> 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.

Thanks,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org