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

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(at)wheeler(dot)net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org