Dean Arnold wrote:
Column 3 is a BYTEA column in Pg and needs special peppering to work.
What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from
$sth->bind_param(3, $somelob, SQL_CLOB);
>>> SOME_DATE_COLUMN is the database native date type. On Oracle you'll
>>> need to convert the ? to a 'TO_DATE(?)'.
> Er, why ? I haven't used DBD::Oracle lately, but assuming you
> $sth->bind_param(1, '2005-07-13', SQL_DATE),
> I'd assume DBD::Oracle would be smart enough to communicate that
That bind_param peppering is precisely what I'm talking about, thanks
for demonstrating my point. This requirement to use "bind_param" to
explicitly tell the DBI which placeholders correspond to which types
is rarely mentioned on any introductions to DBI, and as a result, very
few people carry this out in practice or are prepared to do the
necessary re-work to code bases to perform it.
So, DBD drivers need to hope that the database driver is smart enough to
pull apart the query, match it against the schema and automatically
setup the type correctly. Perhaps many C database access libraries
provide enough information to do this, and pardon my ignorance for never
having written or worked on a DBD to this level - but I'm guessing that
such query introspection isn't always possible.
And, in a sense, requiring that the DBD is able to introspect the query
and DTRT is an "extra restriction" that DBD authors need to conform to,
setting the bar for conformance so high that it is practically impossible
to write portable database access code.
Please note that I'm not suggesting that we do away with the existing
interface, for people who don't care about writing portable database
code. But I'd like to be able to write, for instance;
use v6;
use DBI-2;
my $query = SQL {
SELECT
*
FROM
FOO
LEFT JOIN BAR
ON BAR.FOOID = FOO.ID
};
if ($one) {
$query &&= SQL::WhereClause {
ONE = $one
};
}
my $dbh = DBI.connect(:source("myapp"));
my $sth = $dbh.prepare($query);
my $resultset = $sth.execute();
for =$resultset -> @row {
...
}
So what's happening here?
Well, the SQL construct marks the beginning of a segment of code that
happens to be in an alternate grammar, corresponding to some level of
ANSI SQL. This builds an object which corresponds to that query. In
fact, this can happen at compile time! The SQL { } is actually a
closure that returns a SQL object when it is called.
The later SQL::WhereClause is the same; the variable isn't merely
interpolated, but is closed over, and included as if it were a
placeholder. The &&= assignment operator uses the overloaded &&
operator on the SQL object, which sees it is being given a query
fragment, and adds it into the appropriate point on the SQL AST.
This should all be quite transparent - of course, an optional (but
portable) method of writing database queries. And it's all deliciously
Perlish, yielding less fussing around with buggy code stitching together
queries, and more clean expression of queries using a standard language.
Of course it will be entirely possible to layer support for this sort of
thing atop any DBI interface; but this *is* a version 2, we do have
prototypes for the mechanics of all this stuff - and Done Right™, it
could actually fulfil the goal of DBI - being able to write
Database-driven applications that are truly independant of the database
product in use. DBI v1 cuts the porting time down to next to nothing;
but we can get it even closer!
Sorry to "bang on" about this for so long, I'm sure you're all getting
sick of it by now- I had hoped that the original suggestion was just
going to be acknowledged as a valid way to enhance portability and
flexibility and considered without too much rehashing of what to some
is an old topic.
Sam.
