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.

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.

Answer 2

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");

Answer 3

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.