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
