Re: Help with selectdb in dbi module

mani kandan wrote:
> Hi,
> 
>    As Alex asked me why I not giving the name of the database in the
> connection string, the script I writing is basically a testing scripting.It
> will connect to the database engine then iterate over all the database
> available.If the database of my interest is available I will connect to
> it.Else I will thrown an error.
> 
>        Simple solution what I thought is giving the database name in
> connection string.If the connection is not successful, then it is clear the
> database is not there.But What if there is ten or fifteen db I have to check
> for.I dont want to hardcode for all the database.I want to have a
> generalised solution.

Well you've got a problem then since using DBI you will need to connect
to a database in order to find out the names of the other databases.

As it seems you are using MS SQL Server and perhaps DBD::ODBC (you
didn't say) you could do this:

connect to one know data source

select name from master..sysdatabases

save all the found databases, disconnect and try them each one at a time.

e.g., the following outputs all the databases I can see:

perl -le 'use DBI;my $h =
DBI->connect("dbi:ODBC:test","user","pass");use Data::Dumper;print
Dumper($h->selectall_arrayref(q/select name from master..sysdatabases/));'
$VAR1 = [
          [
            'master'
          ],
          [
            'tempdb'
          ],
          [
            'model'
          ],
          [
            'msdb'
          ],
          [
            'foo'
          ]
        ];

If you want to find out what database you are connected to you can use:

select db_name(dbid) from master..sysprocesses where spid=@@SPID

>      John, the code provided by you is for Mysql.I actually struck with sql
> server.When I slightly modified your code for sql server.I got the same
> error "selectdb()" cannot be found.

You would do as selectdb is part of the Mysql module and nothing to do
with DBI.

>     Please help me with this.


> Thanks & Regards,
> Manikandan.G

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


> On Fri, May 14, 2010 at 8:50 PM, John Scoles <[email protected]> wrote:
> 
>> Alexander Foken wrote:
>>
>>> On 13.05.2010 16:58, mani kandan wrote:
>>>
>>>> Hi Tim,
>>>>
>>>>  I wrote a small script that connect to SQL Server engine.I don't want to
>>>> give the name of the databse in the connection string.
>>>>
>>> Why not?
>>>
>>>
>>>> I want to select a db
>>>> on the fly only if it present.
>>>>
>>> When WHAT is present?
>>>
>>>  I tried $dbh->selectdb($databasename) but this
>>>> failed throwing error can't able to locate object method "selectdb" in
>>>> package dbi::db.
>>>>
>>>  I think the selectdb is part of the Mysql  demo mod
>> haven't  used it in years but here is a quick use of it
>>
>> #!/usr/bin/perl
>> # PERL MODULE
>> use Mysql;
>> # MYSQL CONFIG VARIABLES
>> $host = "localhost";
>> $database = "store";
>> $tablename = "inventory";
>> $user = "username";
>> $pw = "password";
>> # PERL CONNECT()
>> $connect = Mysql->connect($host, $database, $user, $pw);
>> # SELECT DB
>> $connect->selectdb($database);
>>
>>
>> Mysql I think is a nice wrapper for DBD::mysql but don't quote me on that;)
>>
>> cheers
>> John Scoles
>>
>>
>>  There is no such method in DBI, and as far as I know, there was never one.
>>> The only way to specify a database is to use the connect() method of the
>>> DBI class.
>>>
>>>  Please help me on this.I work with microsoft sql server
>>>> 2005.
>>>>
>>>>
>>> What DBD do you use? DBD::ODBC?
>>>
>>> Alexander
>>>
>>>
>>>
>