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

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]
      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]

        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]
        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]
      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 locked_user 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.

      you're right. i'll create a set up script to make the database and then import the data after in perl
[reply]
      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]

Back to Seekers of Perl Wisdom