Tim Bunce <[EMAIL PROTECTED]> wrote:
> PostgreSQL is non-standard (and inconvenient) in this respect.
I chatted with Mischa (my work's resident DB guru) about this, and
according to him, the error behaviour when you attempt to SELECT from a
table that does not exist is "undetermined" in the SQL standard, so it
really is the individual DBMS' choice. I think that's actually worse; all of
these DBMs are behaving completely differently but still "correctly" on such
a basic SQL operation due to a lack of standard!
> There isn't, as far as I know, except to accept the 'lowest common
> denominator'. In this case that means forcing a rollback if any
> statement fails.
> execute() is sufficient if the driver doesn't also supply it's own do()
> because DBI's default do() calls execute(). But some drivers do supply
> their own do() method (for good reasons).
Fair enough. So what I've done, is modified DBIx::Transaction to
mark a transaction error if any query in the transaction returns false;
--snip--
sub execute {
my $self = shift;
my $rv = eval { DBI::st::execute($self, @_); };
if($@) {
$self->{Database}->inc_transaction_error;
die "[EMAIL PROTECTED]";
}
if(!$rv) {
$self->{Database}->inc_transaction_error;
}
return $rv;
}
--snip--
(and similar logic for db::do()).
The package I'm working on that uses DBIx::Transaction now also
checks for the existance of a table before attempting to manipulate it.
I was considering using the "table_info" method for this, but
there's a problem there; I don't know how to ask DBI what database/catalog
name I am currently working in, and "undef" is documented as returning
tables in *every* database, not just the current one.
So what I've done instead is defaulted to this query to check for a
table:
SELECT 1 FROM information_schema.tables WHERE table_name = ?
... then for MySQL,
SHOW TABLES LIKE ?
... and SQLite2,
SELECT 1 FROM sqlite_master WHERE type ='table' AND name = ?
The default query does work for postgres, and I'm told I can expect
it to work with M$SQL, Sybase, and Oracle as well. Can you think of any DBI
drivers I should expect it to *not* work with?
DB::Introspector claims to help you do this, so I might just use it.
Although it does a lot more stuff that I don't need, and I only see MySQL,
Oracle, and Postgres subclasses for it. Is there a better module or method
out there?
- Tyler
