DBI MySQL Use Another DB Without Disconnecting

Discussion in 'Perl Misc' started by afrinspray, Jul 28, 2005.

  1. afrinspray

    afrinspray Guest

    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, Jul 28, 2005
    #1
    1. Advertising

  2. afrinspray

    Brian Wakem Guest

    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
     
    Brian Wakem, Jul 28, 2005
    #2
    1. Advertising

  3. afrinspray

    afrinspray Guest

    Oh totally. Thanks!
     
    afrinspray, Jul 29, 2005
    #3
  4. afrinspray

    Guest

    "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
     
    , Jul 29, 2005
    #4
  5. afrinspray

    afrinspray Guest

    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, Aug 1, 2005
    #5
  6. afrinspray

    Guest

    "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
     
    , Aug 2, 2005
    #6
  7. afrinspray

    afrinspray Guest

    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
     
    afrinspray, Aug 5, 2005
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. sc0ri0n

    disconnecting rsh session

    sc0ri0n, Feb 16, 2005, in forum: Perl
    Replies:
    1
    Views:
    554
    Jim Gibson
    Feb 16, 2005
  2. Daniel Albisser
    Replies:
    2
    Views:
    1,413
    Daniel Albisser
    Feb 24, 2004
  3. John

    Disconnecting a SQL Connection from a WebService

    John, Jun 17, 2004, in forum: ASP .Net Web Services
    Replies:
    3
    Views:
    227
    Ian Jones
    Jun 22, 2004
  4. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    118
    Paul Vudmaska
    Apr 21, 2004
  5. Jerome Hauss
    Replies:
    0
    Views:
    181
    Jerome Hauss
    Oct 13, 2004
Loading...

Share This Page