«

Is there a way to change the database in a DBI mysql connection without
disconnecting? For example, in PHP you'd just say

mysql_select_db("db_name_here", $dbh)

I looked through the DBI code on cpan and I didn't see anything, but
I'm falling asleep here at work, so maybe I missed it.

Thanks,
Mike

afrinspray wrote:

> Is there a way to change the database in a DBI mysql connection without
> disconnecting? For example, in PHP you'd just say
>
> mysql_select_db("db_name_here", $dbh)
>
> I looked through the DBI code on cpan and I didn't see anything, but
> I'm falling asleep here at work, so maybe I missed it.
>
> Thanks,
> Mike


You can do it quite easily, but the user needs permission to do stuff on the
other database. Personally I have a different user for each DB so I can't
do this myself (if somebody gets with access to the machine gets hold of a
user/pass they can only access 1 DB).

You change it exactly as you would if you were logged in at the command
line:-

use db_name


--
Brian Wakem

Oh totally. Thanks!

"afrinspray" <> wrote:
> Is there a way to change the database in a DBI mysql connection without
> disconnecting? For example, in PHP you'd just say
>
> mysql_select_db("db_name_here", $dbh)
>
> I looked through the DBI code on cpan and I didn't see anything, but
> I'm falling asleep here at work, so maybe I missed it.

$dbh->do("use $db_name_here");

Unfortunately, placeholders don't work in this context.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

Yeah exactly. That's why I'm using a prepare with "use ?"

In short, w/o error checking:
$sth = $dbh->prepare("use ?");
loop dbs into $dbname {
$sth->execute($dbname);
}
$sth->finish();

Can you see any problems with this?

Mike

"afrinspray" <> wrote:
> Yeah exactly.

Exactly what? Please quote some context.

> That's why I'm using a prepare with "use ?"

*What* is why you are using a prepare with "use ?"?

>
> In short, w/o error checking:
> $sth = $dbh->prepare("use ?");
> loop dbs into $dbname {

Er, that is not Perl. Looks kind of like PL/SQL

> $sth->execute($dbname);
> }
> $sth->finish();
>
> Can you see any problems with this?

Yes. Placeholders do not work in this context. A placeholder signifies
data, and database names are not considered data, they are considered
metadata.

This is the exception to the rule to almost always use placeholders.
Plug the database name directly into the string without using placeholders.
If the database name is funky, use the DBI method "quoteidentifier" and
pray that it does what it is supposed to under MySQL.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

wrote:
> "afrinspray" <> wrote:
> > In short, w/o error checking:
> > $sth = $dbh->prepare("use ?");
> > loop dbs into $dbname {
>
> Er, that is not Perl. Looks kind of like PL/SQL

In short, as in pseudocode. I should have said

foreach my $dbname (@dbs) {

so you perl masters would understand.


> Yes. Placeholders do not work in this context. A placeholder signifies
> data, and database names are not considered data, they are considered
> metadata.
>
> This is the exception to the rule to almost always use placeholders.
> Plug the database name directly into the string without using placeholders.
> If the database name is funky, use the DBI method "quoteidentifier" and
> pray that it does what it is supposed to under MySQL.

The advantage of using a placeholder is that the statement only needs
to be prepared once, increasing speed. But as you said, it doesn't
work, so I'll just redefine the statement everytime.

Thanks.
Mike