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 <martin.ev...@easysoft.com> > *To:* Sumit Sharma <sumit_1...@yahoo.com> > *Cc:* dbi-users@perl.org > *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 >
