What Is the Vendor Name Factor in Database Metadata? in JDBC

Sometimes, for a given problem, there are different solutions based on the database vendor. For example, the code that gets the table names for an Oracle database is different from the code that gets the tables names for a MySQL database. When you develop an application or framework for a relational database, be sure that your connection pool manager takes the vendor name as a parameter. Depending on the vendor name, you might be calling different methods, or you might be issuing a different set of SQL queries. For example, when you’re using the BLOB data type, the vendor name makes a difference in reading or writing BLOB data. For instance, Oracle requires an empty_blob() function use for setting empty BLOBs, but MySQL does not(empty BLOBs are denoted by NULL in MySQL).

The vendor name also plays an important role in connection pool management and database metadata. Suppose you have a pool of connections that you use in a production environment. If for some reason the database server goes down, then all of the connections in the pool will be obsolete or defunct—that is, they become dead connections. Using a dead connection will throw an exception. One of the important tasks a pool manager must do is that, before handing a connection to the client, it must make sure that the connection is valid. For this reason, a pool manager must issue a minimal SQL query against that database to make sure that the connection is valid. If it is valid, then it can be given to a client; otherwise, you need to obtain another available connection or throw an exception. This minimal SQL query is called a validity check statement, which can differ from vendor to vendor. A validity check statement is a SQL statement that will return at least one row. For Oracle, this validity check statement is "select 1 from dual" and for MySQL and Sybase Adaptive Server, it is "select 1". Without knowing the vendor parameter, it is impossible to check for the validity of database connections. Also, note that without a valid database connection, you cannot get a DatabaseMetaData object. Therefore, you have to make sure that you have a valid database connection before attempting to create a DatabaseMetaData object.

Some JDBC metadata methods require knowledge of the database vendor. For example, getting the name of database tables is not the same in every case. For an Oracle database, you need to select the names from Oracle’s user_objects table, while for other databases, the DatabaseMetaData.getTables() method will be sufficient.

Therefore, when you write a Java or JDBC application program or framework, you have to keep in mind that the same program will run against many relational databases (MySQL, Oracle, Sybase, and others). For example, if your application or framework runs on Oracle, you should be able to run the same program, with minimal changes to parameters and configurations, using MySQL. This means that you need to create database-dependent parameters(such as vendor code specifying the vendor of the database) for your database URLs, SQL queries, and connection properties; avoid hard-coding any values that depend on a specific database vendor.

Here is an example of a vendor name in a configuration file. Based on the <db-name>.vendor key, you will be able to make smart decisions.

Here is another example of a vendor name in an XML document. In this example, the <db-name>.vendor key helps you make smart decisions.

Here is another example of a vendor name in an XML document. In this example, the <db-name>.vendor key helps you make smart decisions.

Now, using this configuration file, depending on the name of the vendor, you may select the appropriate database connection’s validity check statement. Also, based on the name of the vendor, you might issue different JDBC methods for getting the database’s table or view names.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics