DBI MySQL Use Another DB Without Disconnecting

A

afrinspray

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
 
B

Brian Wakem

afrinspray said:
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
 
X

xhoster

afrinspray said:
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
 
A

afrinspray

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
 
X

xhoster

afrinspray said:
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
 
A

afrinspray

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top