How To connect to server root with jdbc driver
Posted by: Mx Dog
Date: February 18, 2022 01:30AM

I have fighting this for years with c#, java, pentaho and a few others.

using the com.mysql.jdbc.Driver from mysql-connector-java-8.0.27.jar , how do you connect to a server's "ROOT" so that you can browse and/or select different schemas ? as of now I have to make a separate connection to each and every data base I want to use and that just doesn't seem right to me.

It should work something similar to workbench when you make a connection and see all the different databases in the left pane and can use;/select; on that connection and other DB's that connect you to the root if no database is selected

the closest I have come is connecting to the information_schema but that has problems too trying to use a "Real" table from the information you can get from it.

is there some hidden table to connect to because it seems short sighted to have to connect like this. I feel like I am missing the obvious here either with the driver or the server set up not to mention having to know the data structure in advance to do anything.

so assuming a basic connection string is ...
jdbc:mysql://localhost:3306/WHAT-GOES-HERE? other then a table name to get the top level of the server if i do not put a table name in then any query's fail with cannot find schema.....even though there is a good active connection.

i.e. jdbc:mysql://localhost:3306/
then depending on software ...
use table1; select * from table1 ;
ERROR cannot find schema information null or similar message...


what i do now the head-ache it is:

jdbc:mysql://localhost:3306/information_schema
... query get table names
then
jdbc:mysql://localhost:3306/table1 i need data from
jdbc:mysql://localhost:3306/table2..3..4..etc i need data from

is just a pain when i know there should be a better way to do this with out making a ton of separate connections. Some of this canned software i use, I literally have to make a separate connection for each and every database on the server i need to use.

do i have to structure the queries in some way to actually select a certain schema after a generic connection ( no schema in connection string ) or am i missing something obvious in the connector string to get the "ROOT" and access to all the databases on the server that the user/me has rights too ....

like i said it works in workbench and and mysql client so shouldn't it should work in the jdbc driver as well ? what magic am i missing ?

Options: ReplyQuote


Subject
Written By
Posted
How To connect to server root with jdbc driver
February 18, 2022 01:30AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.