how to create a database in perl

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

How do you create a database in perl? Normally when i use the DBI i specify the database name when i connect

    my $db = DBI->connect("DBI:mysql:database=$sql_database;host=$sql_
+host;port=$sql_port;mysql_socket=$sql_socket", $username, $password,{
+'RaiseError' => 1});
[download]

but i haven't made my database yet to give its name here. Simiarly code for using a dsn needs a database name


$dsn = ‘dbi:mysql:dbname=NameOfDatabase’;
$user = ‘mysqlusername’;
$password = ‘mysqlpassword’;
$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });
[download]

How do i connect without passing the database name?

Once i've connected i could create the database using this command

$dbh->do( qq(CREATE DATABASE NAME) );
[download]

but if i then have subsequent queries on the database how will it know which database to use. I don't really want to have to use databasename.tablename in all my queries. Is there a way to issue the 'use database command' to the dbh handle.

Many thanks

Comment on how to create a database in perl Select or Download Code

Replies are listed 'Best First'.
Re: how to create a database in perl by Your Mother (Archbishop) on Jan 28, 2011 at 15:17 UTC
I didn't realize it was this easy... Just don't give a DB name. `use DBI; my $dbh = DBI->connect("dbi:mysql:", "user","pass"); print 1 == $dbh->do("create database MooCow") ? "YAY\n":"BOO\n";` [download] I'm guessing that's driver specific behavior.	[reply] [d/l]
Re^2: how to create a database in perl by Anonymous Monk on Jan 28, 2011 at 15:20 UTC
ok, thanks, but if you don't give a name, how can i get the database handle to perform queries on my database once i have created it. thanks	[reply]
Re^3: how to create a database in perl by erix (Prior) on Jan 28, 2011 at 15:53 UTC
You can just run "use mynewdbname" as a sql statement in do: `$dbh->do( "use mynewdbname" )` [download] I don't use mysql, but I find plenty of usage of that when searching in google code with `$dbh->do("use`	[reply] [d/l] [select]
Re^3: how to create a database in perl by Jeppe (Monk) on Jan 28, 2011 at 15:39 UTC
Simply disconnect(), and connect again with the name of the newly created database.	[reply]
Re: how to create a database in perl by erix (Prior) on Jan 28, 2011 at 14:30 UTC
You control that via the dsn; see the DBD::mysql docs (second line of the synopsis): `$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";` [download] Specifically: the 'database=$database;' part. The part of the dsn after 'dbi:<driver>:' is given as a whole to the specific database driver; what the driver expects is specific to that driver. For example, in the case of postgresql database driver DBD::Pg, the database can be given with either 'database', 'dbname', or 'db'.	[reply] [d/l]
Re^2: how to create a database in perl by Your Mother (Archbishop) on Jan 28, 2011 at 14:41 UTC
the database can be given with either 'database', 'dbname', or 'db' I think the question is "how can the database name not be given?"	[reply]
Re: how to create a database in perl by sundialsvc4 (Abbot) on Jan 28, 2011 at 17:36 UTC
While “creating an entire database from scratch, procedurally,” is a very interesting (and achievable) concept, most of the time I think it will work better to use other mechanisms, like a “setup” program or an install-script of some kind, to actually create the necessary environment. “Yes, of course, you can build such a program in Perl.” I’m just suggesting that you survey the entire field to see if this approach is the cleanest and quickest option that is available to you. A great many things can be done, when there exists an easier way to do it, and “laziness is a virtue, too.” JM2CW.
Re^2: how to create a database in perl by Anonymous Monk on Jan 28, 2011 at 20:20 UTC
you're right. i'll create a set up script to make the database and then import the data after in perl	[reply]
Re^2: how to create a database in perl by girarde (Hermit) on Jan 28, 2011 at 21:22 UTC
I don't use mysql, but I suspect if there isn't something named `master` there's an analog, and that could be used while creating.	[reply] [d/l]


Just another Perl shrine
	PerlMonks