Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > DBI MySQL Use Another DB Without Disconnecting

Reply
Thread Tools

DBI MySQL Use Another DB Without Disconnecting

 
 
afrinspray
Guest
Posts: n/a
 
      07-28-2005
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

 
Reply With Quote
 
 
 
 
Brian Wakem
Guest
Posts: n/a
 
      07-28-2005
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

 
Reply With Quote
 
 
 
 
afrinspray
Guest
Posts: n/a
 
      07-28-2005
Oh totally. Thanks!

 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      07-28-2005
"afrinspray" <(E-Mail Removed)> 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
 
Reply With Quote
 
afrinspray
Guest
Posts: n/a
 
      08-01-2005
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

 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      08-02-2005
"afrinspray" <(E-Mail Removed)> 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
 
Reply With Quote
 
afrinspray
Guest
Posts: n/a
 
      08-05-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> "afrinspray" <(E-Mail Removed)> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
use dBI function look for Primary key for MYSQL db table. sam Perl Misc 2 01-02-2005 07:35 AM
DBI Mysql storing DBI:binary, trouble with character 26 Jerome Hauss Ruby 0 10-13-2004 03:04 PM
DBI and DBI::Oracle packages configuration ulloa Perl 1 07-22-2004 05:52 PM
dbi:mysql mysql has gone away Paul Vudmaska Ruby 0 04-21-2004 06:19 PM
Mason, DBI, and DBI::Pg Asby Perl Misc 0 07-24-2003 09:04 PM



Advertisments