Create database 1

[audiopro]

How do I create a database from code in PERL?
I can do it from the MySQL prompt but not within a perl script.

Keith

http://www.studiosoft.co.uk

 

[BrianAtWork]

When you say "but not within a perl script", how are you trying to accomplish this? System call? DBI library?

 

[audiopro]

Not sure how to implement the call.
I have created a database at the MySQL prompt and can create tables and populate them from within a perl script. This is done of course by connecting to an existing database and referencing the returned handle.
The simple term of

CREATE DATABASE DATABASE_NAME

does the job but how do I issue that command from within PERL?


Keith

http://www.studiosoft.co.uk

 

[BrianAtWork]

Sorry, I thought "but not within a perl script" meant it wouldn't work in your perl script... Probably the best way to do it, would be to use the DBI libraries for perl. Run this from the command line:

perl -e 'use DBI;'

If you don't get an error, you probably have DBI installed correctly. If you get an error that says it wasn't found in your @INC, then you need to install the DBI modules from search.cpan.org, as well as the MySQL DBD.

Once you have DBI setup correctly, creating a database in perl (or any mysql functions) is pretty straight forward.

Example:

#!/usr/local/bin/perl
use DBI;
my $dbh = DBI->connect("database","user","password");
if ($DBI::errstr ne "" ){
        print "$DBI::errstr";
        exit;
}
my $sth = $dbh->prepare ("CREATE DATABASE DATABASE_NAME")
$sth->execute();

Basically, you can enter any query into the prepare statement that you can enter at the command line, plus, if you are inserting or selecting you have the ablility to bind variables into your statements.

 

[TrojanWarBlade]

Why bother with "prepare" and "execute" when yuo can simply "do" the create?



Trojan.

 

[BrianAtWork]

In this case, yep - "do" works just fine... I usually use the prepare and executes because I am inserting data into tables, and I prepare statements that have "VALUES (?,?,?)" in them.

But you are right, the "do" would be the best option in this case.

 

[audiopro]

Databases
I can create the database on my local server but how do I do it on a remote, hosted server?

Prepare
I am creating the actual query on the fly so the prepare method seemed a better option.

Keith

http://www.studiosoft.co.uk

 

[BrianAtWork]

Check out this link on CPAN:

http://search.cpan.org/~jwied/Msql-Mysql-modules-1.2219/mysql/lib/DBD/mysql.pm

Here's what you are looking for:

$dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
                      $user, $password, {RaiseError => 1});

or

    $dsn = "DBI:mysql:$database"; #on local host
    $dsn = "DBI:mysql:database=$database;host=$hostname"; #remote host
    $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; #remote host with non-default port

    $dbh = DBI->connect($dsn, $user, $password); #your connect statement

(Lots of ways to do it)

Checkout CPAN - it can answer a lot more questions than I can.

Brian

 

[audiopro]

This code looks like the same code I connect to the database any way, although I must check before I am sure.
If so, a database is created by default, the same way a text file is created when you save to a non existent file.

$dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
                      $user, $password, {RaiseError => 1});

Keith

http://www.studiosoft.co.uk

 

[BrianAtWork]

Ooops - My mistake, sorry to mislead you. Nope, databases aren't created by default.

Instead, when you connect to a MySQL server, just leave the database portion of the DSN out of your connect statement.

my $dbh = DBI->connect("DBI:mysql:;host=$host",
                      $user, $password, {RaiseError => 1});

I just leave out the database portion after "DBI:mysql:"
After you connect, THEN do your CREATE NEW_DATABASE statement.

Then you can "use NEW_DATABASE" and create tables, etc.

Sorry if I confused you!

 

[audiopro]

Thanks Brian - Yes I was a little confused at first but it soon passed.
Could you please explain the

{RaiseError => 1}

part of your previous statement.



Keith

http://www.studiosoft.co.uk

 

[BrianAtWork]

Sure - Here's an clip from CPAN:

The RaiseError attribute can be used to force errors to raise exceptions rather than simply return error codes in the normal way. It is "off" by default. When set "on", any method which results in an error will cause the DBI to effectively do a die("$class $method failed: $DBI::errstr"), where $class is the driver class and $method is the name of the method that failed.

Basically, you can leave that part out, if you wanted, but I usually leave it in. By turning RaiseError on, the program will die if there is a problem with DBI. If you leave it out, then DBI will only warn you of problems.