David -- I'll pipe in where I can...
>
> * In several of the functions, DBD::Pg starts with the statement
> "dTHR;". DBD::mysql, meanwhile, starts with this:
>
> #ifdef dTHR
> dTHR;
> #endif
>
> Which is correct, and what is this thing (variable) for?
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).
>
> * 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 :)
>
> These next questions relate to the dbd_preparse() function in DBD::Pg.
>
> * DBD::Pg is doing a fair amount of work to work around SQL-style,
> C-style, and C++-style comments in SQL statements. Is this necessary?
> Are comments officially supported by the DBI spec? I notice that
> DBD::ODBC, for example, doesn't appear to do anything special with
> regard to comments. And if comments *are* supported by DBI, and DBD::Pg
> is doing the right thing by watching out for them, wouldn't it be
> smarter for DBD::Pg to cut them *out* of its internal copy of the SQL
> statement so that dbd_st_execute() doesn't also have to deal with them?
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.
>
> * Dumb question: Can someone explain to me what's happening with the
> literal stuff? What is the parse doing, exactly? I have a general idea,
> but some of it is a bit opaque to my Perl-trained brain. :-)
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...
>
> These questions related to the dbd_st_execute() function in DBD::Pg.
>
> * If the answer to the last question is "no", then in dbd_st_execute(),
> where DBD::Pg sets the number of fields, (sh|c)ouldn't this be done
> only once per prepared statement? I mean, no matter how many times a
> prepared statement executes, its number of fields won't change, will it?
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. SQLServer
can handle:
select * from sometable_a; select * from sometable_b;
Which returns 2 results sets, one for each query and each can have different
columns and types of columns. So, DBD::ODBC has more logic in the execute()
to handle/detect that and also, in multiple-result set queries. (I'm not
even going to get into stored procedures which can change the types of
result sets they can return based upon input parameters, which is
problematic).
>
> * With regard to the svp and phs variables, which get their values like
> this:
>
> svp = hv_fetch(imp_sth->all_params_hv, namebuf, i, 0);
> phs = (phs_t*)(void*)SvPVX(*svp);
>
> What are these variables, and where do they come from? I realize I'm
> showing my ignorance of Perl internals here, but I'm also trying to
> understand whether these variables are retrieving metadata from the
> PostgreSQL server. I understand that phs->ftype is checked to see if
> the value needs to be quoted, but I'm not sure where phs->ftype comes
> from.
>
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...
Hope this helps...
Regards,
Jeff
