How Do You Get Database Catalogs? - JDBC

If you want to provide catalog and schema services to database applications, then you might need to provide catalog and schema values to client applications. The words “catalog” and “schema” have different meanings, depending on the database vendor. Again, the vendor parameter is very important in understanding the semantics of catalogs and schemas. Oracle treats “schema” as a database name, while MySQL treats “catalog” as a database name. So, in order to get the names of databases from Oracle, you must use getSchemas(); to get the names of databases from MySQL, you must use getCatalogs(). If you use getCatalogs() for an Oracle database, or getSchemas() for MySQL, it returns nothing (as null objects). In the JDBC API, getSchemas() claims that it returns a set of two columns (table schema and table catalog), but in reality it just returns table schema, as a first column of the result set. Once again, this proves at least two points:

  • You have to test your code against different databases; that is, databases can have different semantics by using the same JDBC API.
  • When you define connections, make sure that the vendor parameter is defined. By knowing who the database vendor is, you can invoke the correct methods.

MySQL Database Setup

$ mysql -u root -proot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 36 to server version: 5.0.12-beta-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Oracle Database Setup

In Oracle, the DBA_USERS table describes all users (in Oracle, “user” is semantically equivalent to a “schema”) of the database. To save space, I have edited the output.

The Solution

Invoking GetCatalogs for MySQL

Figure : shows how to run the solution for the MySQL database.

Invoking GetCatalogs for MySQL

Invoking GetCatalogs for Oracle

Figure : shows how to run the solution for the Oracle database.

Invoking GetCatalogs for Oracle

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

JDBC Topics