From: Tim Bunce <Tim.Bunce@pobox.com>
To: David Wheeler <david@wheeler.net>
Cc: Rudy Lippan <rlippan@remotelinux.com>
Cc: dbi-dev@perl.org
Cc: pgsql-interfaces@postgresql.org
Subject: Re: DBD::PostgreSQL
Date: Mon, 18 Nov 2002 17:44:08 +0000
Message-ID: <20021118174408.GF1444@dansat.data-plan.com> (raw)
In-Reply-To: <A97148BA-FB14-11D6-93B3-0003931A964A@wheeler.net>
References: <Pine.LNX.4.44.0211172220380.19332-100000@elfride.ineffable.net>
<A97148BA-FB14-11D6-93B3-0003931A964A@wheeler.net>
On Mon, Nov 18, 2002 at 08:42:01AM -0800, David Wheeler wrote:
> On Sunday, November 17, 2002, at 08:26 PM, Rudy Lippan wrote:
>
> >I would guess this is along the lines of std PostgeSQL behaviour; when
> >you
> >begin_work you tell the db to start a transaction (BEGIN) up until the
> >next commit/rollback. So instead of turning autocommit off you can
> >just
> >begin work around the blocks of code that need transactions. (cf.
> >local
> >($dbh->{AutoCommit}) = 0)
>
> Okay, so if I understand correctly, it's an alternative to AutoCommit
> for handling transactions. That explains why they *both* need to be checked.
Just to be sure this is clear, begin_work is *just* an alternative
way to set AutoCommit off till the next commit or rollback. The
application is free to just set AutoCommit explicitly as needed.
The *only* time a driver needs to consider the BegunWork attribute
is immediately after a commit or rollback.
> >dbd_preparse scans and rewrites the query for placeholders, so if you
> >want to use placeholders with prepare, you will need to walk the string
> >looking for placeholders. How do you think DBD::Pg knows that when you
> >say $sth = $x->prepare("SELECT * FROM thing WHERE 1=? and 2 =?) that
> >$sth
> >is going to need two placeholders when execute() is called?
>
> Right, okay, that's *kind of* what I thought. It just seems a shame
> that each query has to be parsed twice (once by the DBI driver, once by
> PostgreSQL).
Should need to "parse" in the formal sense, no full grammar is
needed, it's just a very quick skim through the string. If done in
C it should be too cheap to worry about. Especially as it's only
done at prepare() time, not execute().
> >>* One more thing: I was looking at the PostgreSQL documents for the
> >>new
> >>support for prepared statements in version 7.3. They look like this:
> >>
> >>PREPARE q3(text, int, float, boolean, oid, smallint) AS
> >> SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
> >> ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
> >>
> >From my rough scanning of the docs a few weeks ago, I think that the
> >types are optional (I hope that thy are, in any event), & you are
> >missing the plan_name.
>
> Unfortunately, according to Tom Lane, the data types are required. :-(
> FWIW with the above example, I swiped it right out of PostgreSQL's
> tests. the plan_name is "q3".
>
> >You do not want to go there (trying to magically get the types for the
> >placeholders (unless PostgreSQL will give them to you)).
>
> Not easily, I think. A shame, really, that the data types are required,
> as it means that dynamic database clients like DBI (and, I expect,
> JDBC) won't really be able to take advantage of prepared statements.
> Only custom code that uses the PostgreSQL API directly (that is, C
> applications) will be able to do it.
Would binding a string type to an integer (etc) placeholder work?
If so, just ignore the types and bind everything as strings.
That's exactly what DBD::Oracle does.
Tim.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: Tim.Bunce@pobox.com, david@wheeler.net, rlippan@remotelinux.com, dbi-dev@perl.org
Subject: Re: DBD::PostgreSQL
In-Reply-To: <20021118174408.GF1444@dansat.data-plan.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox