Re: how to create database without any handle

Sumit Sharma wrote:
> Hello Martin,
> thanks for your reply. Since, I am new to perl i would need your help in
> resolving my project problem that I am facing..
> Basically , I am using activePerl 5.8.8 along with mysql. I have a perl
> script which reads from txt file the name of a mysql database. The
> script first needs to check if the myswl dbase exists or not..if it
> does, it should drop the same and re-create the same..I can easily
> achive this by first using dbi->connect to connect to the base, this
> gives me a dbase handle and then I can drop the dbase using dbh->do 
> (DROP database <dbaseName>) followed by dbh->do (CREATE database
> <dbaseName>).
> If dbase doesnt exist, I am facing prob of how to create it w/o any
> dbase handle..I was browsing through driver Handle having createdb,
> dropdb functions which can be called,,but no luck..
> ANy help would be highly appreciated
>  
> sumit

Please keep all replies on the thread in dbi-users list.

I believe I answered this question in the last response I made on the
thread. There should be some way in mysql of listing databases. If you
cannot (and I don't know) create an connection to mysql without knowing
the database then you've got a problem I cannot answer but your original
posting was using ODBC and you normally can connect to an ODBC driver
without knowing the database.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> ------------------------------------------------------------------------
> *From:* Martin J. Evans <[email protected]>
> *To:* Sumit Sharma <[email protected]>
> *Cc:* [email protected]
> *Sent:* Fri, July 2, 2010 1:22:03 PM
> *Subject:* Re: how to create database without any handle
> 
> Sumit Sharma wrote:
>> Hello,
>> I am new to Perl programming. Below is piece of perl code I have
> written to browse through available drivers and list the databases
> available for each driver.
>> My question is if a specific database is present, I would like to drop
> and re-create it. One way of doing that is to connect to it which
> returns back a database handle and with the help of that handle, just
> execute a do with DROP DATABASE followed by CREATE DATABASE. However,
> what if database doesnt exist, how to create it without obtaining any
> kind of database handle?
>> Any help would be greatly appreciated!!!
>>
>> #!/usr/local/bin/perl
>> #
>> # ch04/listdsns: Enumerates all data sources and all
>> # installed drivers
>> #
>> use DBI;  # for database
>> use DB;
>> use DBD::ODBC;
>> my $databaseName = "july";
>> my $driverName = "ODBC";
>> ### Probe DBI for the installed drivers
>> my @drivers = DBI->available_drivers();
>> my $drh = DBI->install_driver('ODBC');
>> my $dbiPart;
>> my $odbcPart;
>> my $datasrcName;
>> die "No drivers found!\n" unless @drivers; # should never happen
>> 
>> ### Iterate through the drivers and list the data sources for
>> ### each one
>> foreach my $driver ( @drivers ) {
>>    print "Driver: $driver\n";
>>    my @dataSources = DBI->data_sources( $driver );
>>    foreach my $dataSource ( @dataSources ) {
>>      if($driver eq $driverName)
>>  {
>>      #Slice it to exactly find the database name.
> 
> this is not the database name, it is the datasource name.
> 
>>            ($dbiPart,$odbcPart,$datasrcName)=split(/:/,$dataSource);
>>            print "\tDatabase Name: $datasrcName\n";
>>    if($datasrcName eq $databaseName)
>>    {
>>        #database exists, so we should FIRST
>>        #  CONNECT to it
>>      # DROP and RE_CREATE it
>>                $dbh = DBI->connect ("DBI:mysql:database=$databaseName",
>>                                      '','');
>>            $dbh->do ( qq (DROP DATABASE  $databaseName) );
>>            $dbh->do ( qq (CREATE DATABASE $databaseName) );
>>    }
>>    else
>>    {
>>        How do I create database? Below statement
>>        gives an error ($dbh is undefined, so cannt call "do")
>>        #$dbh->do ( qq (CREATE DATABASE $databaseName) );
>>    }
>>  }
>>  else
>>  {
>>          print "\tData Source is $dataSource\n";
>>  }
>>    }
>>    print "\n";
>> }
>>
>>
>>
>>     
> 
> You seem to be mixing up datasource names with database names.
> In ODBC, you create named datasources which define a way to connect to
> database engines. The datasource /may/ contain an attribute which says
> which database to make the default database but it does not have to and
> some even default the database depending on who logged in. There is
> nothing which says the datasource name has to be the name of the
> database e.g., on Windows I create a datasource called FRED for MS SQL
> Server but select to use the DAVE database. Often you can even change
> the current database with a SQL statement and sometimes you can even
> query an alternative database to the default by prefixing the database
> name to the table name (see schema and catalog).
> 
> Your code is picking datasource names and splitting the datasource name
> out of the string that DBI's data_sources method returns so in my above
> example you'd get FRED but the default database in the FRED datasource
> is DAVE.
> 
> To locate the available database names you will need to find the meta
> table provided by the SQL Engine which you can query to find the
> databases. You will obviously need to be connected to the database
> engine to do that and depending on the engine you are using you might be
> able to find the databases and create/drop new ones as you like in ANY
> ODBC connection.
> 
> e.g., for MS SQL Server you would create a datasource that connects to
> SQL Server and do something like:
> 
> select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
> 
> which will list database names. If you have permission to do so you can
> create a new database or drop an existing one.
> 
> How you do this may depend on the DBMS you are connected to although the
> above SQL is ANSI I believe.
> 
> Martin
> -- 
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>