Perl DBI without accessing the database

Question

I'm creating a set of SQL INSERT statements for a database that doesn't exist yet, and I'm saving them to file.

How can I use Perl's powerful DBI module to create those INSERT statements without accessing a specific database. In particular, it looks like using the $dbh->quote() function requires that I instantiate $dbh with a connection to a database.

Ostracize

SQL is just text... create the query strings and save them. No need to involve DBI. DBI's quote() is usually just a passthrough to the database's own quoting function, so it can't be done without a DB connection, otherwise DBI won't know which drive's quote function to call. – Celaeno

Answer 1

Unfortunately, the actual quote() behaviour isn't always a portable characteristic, so each driver will do them differently. Unless you connect to a driver, you don't know which quoting format to use in practice. (There is one module that might do this without a connection, DBIx::Abstract, but it is not especially current.).

The quote() method is actually implemented by the corresponding driver class, in the DBD::* namespace. You might attempt to load the driver you need and call the function directly (see http://search.cpan.org/~timb/DBI-1.616/lib/DBI/DBD.pm#Writing_DBD::Driver::db::quote) but this feels grubby.

I'd still make a DBI connection, if only so that you get the right format of quoting. You don't need to actually send it any statements, but then you do know that the quoting format will be correct for the database you will use.

Shuster

Answer 2

From DBI::quote:

For most database types, at least those that conform to SQL standards, quote would return 'Don''t' (including the outer quotation marks). For others it may return something like 'Don\'t'

That is, the behavior of DBI::quote varies from database to database, and it doesn't make sense to call it in a database-independent way.

Make a trivial connection to a database of the same type you are writing for, or learn your database's quoting conventions and implement a quote method yourself. See the DBI source for a reference implementation.

Uvea

Answer 3

Usually you would use DBI by specifying a database like so:

my $dbh = DBI->connect("DBI:mysql:database=$db_name;host=127.0.0.1");

However, your database does not yet exist so you cannot connect to it. You can use DBI without specifying a database like so:

my $dbh = DBI->connect("DBI:mysql:;host=127.0.0.1");

Hers

Answer 4

You could use DBD::CSV or DBD::AnyData as a dummy database. SQLite is good for this purpose, too.

A hidden advantage of using SQLite here is that it's a semi-real database, and will tend to make you write code in a way that's decoupled from any specific database.

Surplus

Answer 5

According to perl -MDBI -E 'say join(q{,},DBI->available_drivers);' in clean Debian chroot with only DBI (package "libdbi-perl") installed the following drivers are available right away:

DBM,ExampleP,File,Gofer,Proxy,Sponge

The minimum DBI connect statement that works for me is

my $dbh=DBI->connect("DBI:DRIVER:");  # DRIVER is one of [DBM,File,ExampleP,Sponge,mysql,SQLite]

That is enough to use $dbh->quote() with no database whatsoever.

DBM and File escape q{'} as q{\'} ("mysql" style);
ExampleP and Sponge escape: q{'} as q{''} ("SQLite" style).

Mousebird

Answer 6

You can also use:

DBD::_::db->quote()

To access the quote function without setting up a database handle. I don't believe it is specific to MySQL though.

Paulo