Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

how to use DBI connect to >1 database

by misterperl (Pilgrim)
on Sep 28, 2023 at 13:58 UTC ( [id://11154723]=perlquestion: print w/replies, xml ) Need Help??

misterperl has asked for the wisdom of the Perl Monks concerning the following question:

O'Rielly cookbook suggests:

SELECT db1.artist.name, db2.painting.title FROM db1.artist INNER JOIN db2.painting ON db1.artist.a_id = db2.painting.a_id;

which is similar to my application requirements. I can use DBI of course to connect to db1. And make ANOTHER connection to db2. But then I need to "prepare" - which needs a database connection. How can I create a handle connected to TWO databases as this statement requires? Maybe:

$db1->$db2->prepare( $query );

Gracias

Replies are listed 'Best First'.
Re: how to use DBI connect to >1 database
by hippo (Bishop) on Sep 28, 2023 at 16:12 UTC

    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.


    🦛

      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

        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?

        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.

      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

Re: how to use DBI connect to >1 database
by ikegami (Patriarch) on Sep 28, 2023 at 17:17 UTC

    DBI does not evaluate SQL. It just passes it to the server. If DB2 support queries that make use of multiple databases, you will need to issue the appropriate SQL commands to do so. This has nothing to do with DBI, which just gives you allows you to communicate with a server.

      you're right I guess this was more of a mysql question. Sorry 'bout that, but we're gold now!

        Sorry, thought I saw "DB2" mentioned. Same applies to every other server, though. If MySQL support queries that make use of multiple databases, you will need to issue the appropriate SQL commands to do so.

Re: how to use DBI connect to >1 database
by marto (Cardinal) on Sep 28, 2023 at 14:50 UTC

    You don't say which database, if a database link has been created. Are you saying this query works from whatever client you're using?

      I dont understand the question about links. I have a mysql database , v 5.7.

      There are two databases. I have connections and handles to each one: $db1 , $db2.

      I didn't say the query worked for me at all- the cookbook example just stated the query- they didn't say how to invoke it. My question is , how can I do this in the Perl DBI?

      I did see some resources mention DBIx which I've never heard of, but those were not in reference to mysql.

Re: how to use DBI connect to >1 database
by Polyglot (Chaplain) on Sep 29, 2023 at 00:21 UTC
    To my understanding, the reason Perl's DBI must connect to a database, not just to a particular SQL host irrespective of a database, is because SQL privileges are granted on a database-specific level, and one must provide the appropriate credentials to connect to that database on the SQL server. In your case, the same credential allows you access to the second database within that same server, so you are able to include DB2 in the query for DB1.

    One is left to wonder, however, if the DBI would be able to accept the more complicated scenario involving two separate databases which had separate grant privileges, requiring two separate credentials. In such a case, your scenario might fail--and I can see why this question would come up. Nor do I know what the answer might be in such a case--particularly if one is not sufficiently privileged as to create new database users and grant them access privileges.

    Blessings,

    ~Polyglot~

      In retrospect, perhaps the question is moot, because the query involving two databases already assumes accessing them both through the same privileged user. Even the original query suggested by O'Reilly depends on having accessed the database server through a single user. It would simply be impossible, I think, to initiate a multi-user connection.

      Blessings,

      ~Polyglot~

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11154723]
Approved by marto
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2024-04-12 14:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found