in reply to how to use DBI connect to >1 database

Your query as written looks fine to use with DBI so long as the user you have connected as has the requisite privileges on both DBs. Connect to either and just use that one handle.

All of this assumes that both DBs are on the same host, of course.

🦛

  • Comment on Re: how to use DBI connect to >1 database

Replies are listed 'Best First'.
Re^2: how to use DBI connect to >1 database
by misterperl (Friar) on Sep 28, 2023 at 17:10 UTC
    Well done sir hippo- surprisingly, it WORKED! I used the $db1 handle which was connected to db1, and, remarkably, I can also read from tables in db2.. It seems counterintuitive since a DB handle connects to a specific database, yet it ALSO appears connected to others. It makes me wonder why I have to specify a database when making a connection?

    TY

[reply]

      yet it ALSO appears connected to others.

      No, it doesn't. You just connect to one server, and pass it the SQL to execute.

      It makes me wonder why I have to specify a database when making a connection?

      If you don't specify the server, how would it know to which server to connect and to send the SQL?

[reply]
      In some database engines you don't actually need to specify a database, since there's usually a default. But, if you did that you would need to specify fully qualified table names on every query, which would be annoying. Also, if you are a limited-access user sometimes the database wants you to choose so that it can make permission checks before allowing you to do anything.

      For a real mind-bender, Postgres has a feature where it can import views of foreign databases from other servers (or even types of server, like mysql!) and then you can run a single query across multiple database servers! It's a bit awkward to set up, though.

[reply]
Re^2: how to use DBI connect to >1 database
by misterperl (Friar) on Sep 28, 2023 at 16:47 UTC
    privs are fine- I can select from either DB. BUT, if I use say $db1->prepare( $query ), how does that know what $db1 and $db2 are?

    But I like your idea, I'll try it

[reply]
[d/l]

In Section Seekers of Perl Wisdom