Re: DBD::PgSQL: More Queestions

On Tue, Nov 19, 2002 at 03:13:54PM -0800, David Wheeler wrote:
>
> * What are the guidelines for handling trace? I note that DBD::Pg
> checks dbis->debug far more often than, say, DBD::mysql, and generally
> sets a lower threshold (1). What sorts of things should a driver be
> printing at what debug/trace levels?

Drivers should rarely if ever print anything below trace level 3.
Quite a few drivers get this wrong and it can be quite annoying to
the users to have to wade through lots of driver output when all
they want is the basic DBI level 1 or 2 trace. Use levels around
4 through 7 to add more (obscure) detail.

> * 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?

I believe dTHR is only needed for the old "5.005 threads", not the
new iThreads, and the DBI will no longer support the old 5.005 threads
so you can delete them all for your new driver.

> * In dbd_st_prepare(), is there a way to determine NUM_OF_FIELDS, NAME,
> etc. -- that is, before executing the statement?

Some databases, like Oracle, have what you could call a "server-side
prepare" so DBD::Oracle's prepare() method sends the statement to
the server and then asks the server to 'describe' it.

(Without that you'd need a full sql parser available on the client
side. Even then you wouldn't know the TYPE of the database fields
without much more work.)

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

That's the wrong question. The DBI spec actually makes no assumptions
about the syntax of the statements. But it does say that question marks
should be used for placeholders.

Thing is, users tend to get upset (quite reasonably) when a driver
interprets question marks that are inside comments as real placeholders.

Personally I'd agree with them that it's a driver, er, limitation
(so I'm not over-happy with DBD::ODBC ducking the issue, but I
understand that DBD::ODBC faces a much tougher issue here than most drivers).

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

Why should dbd_st_execute have to "deal" with them? The whole string
should just be sent off to the server.

> * 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. :-)

Same issue as comments. Consider this statement:

SELECT * FROM table WHERE a = ? AND b = '?' /* is this right? */

How many question marks are there? Three. And how many placeholders? One.

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

If it's possible for you to _realiably_ determine the number of
field at prepare() time, then you should do it. If not, then do it
on just the *first* execute().

> * 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?

Your dbd_preparse function set them up as it recognized the placeholders.

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

phs stands for placeholder structure. The structure (typedef) is declared
in one of the driver *.h files. There's one per placeholder and they're
store in the hash pointed to by imp_sth->all_params_hv. All that's done
by dbd_preparse() as it scans the statement string.

Tim.