CODEWITHSUNDEEP
javasqldatasource
Lucas
Lucas

Reputation: 14959

Can you determine which database vendor is used from a SQLException?

I'm assuming the simple answer is no, but wondering if one of the Java ninja's out there might know of a clever hack... More specifically my problem is I am trying to create automation that can extract/restore a database from some other data format (xml, another database vendor, json...). I am trying to limit the scope of vendor specific code to an *Adapter class. The interface is:

public String escapeName( String name );

public ColumnMetaData columnMetaDataFromResultSet( ResultSet resultSet ) throws SQLException;

public SqlQuery getColumnMetaDataSql( String schema, String table );

public String getCreateTableSql( TableMetaData tableMetaData );

public String getDropTableSql( TableMetaData tableMetaData );

public String getRowInsertSql( TableMetaData tableMetaData );

public String getRowSelectSql( TableMetaData tableMetaData );

public SqlQuery getTableMetaDataSql();

public TableMetaData tableMetaDataFromResultSet( ResultSet resultSet ) throws SQLException;

The important method to this question is getDropTableSql( TableMetaData tableMetaData ). That method would theoretically return a SQL statement that would drop the table indicated by the metadata so that it could be recreated (this is used to publish full datasets on fixed term basis so, out with the old, in with the new). The adapters for both MySQL and SQLServer support some flavor of safe DROP (DROP table IF EXISTS or similar), but Oracle and DB2 don't. So my next thought was to just drop it and catch the appropriate exception. But the exception that gets thrown is java.sql.SQLSyntaxErrorException(or org.springframework.jdbc.BadSqlGrammarException since I am using springs JdbcTemplate). That family of exception seems far to broad to catch safely (and it doesn't really seem like the grammar to me...). Each vendor seems to return a different SQLState and ErrorCode, so those are not much help unless I can also determine the vendor. So, can I? I know that spring does it somehow, most likely by digging it out of the DataSource that issued the query. Just haven't figured it out yet.

---------------------- UPDATE ---------------------------

I was able to achieve my goal with this code: 

private void dropTable( JdbcTemplate jdbcTemplate, String dropSql ) {
    try {
        jdbcTemplate.execute( dropSql );
    }
    catch ( BadSqlGrammarException sqle ) {
        try {
            throw sqle.getRootCause();
        }
        catch ( SQLException rootCause ) {
            int errorCode = rootCause.getErrorCode();
            String sqlState = rootCause.getSQLState();

            try {
                String vendor = jdbcTemplate.getDataSource().getConnection().getMetaData().getDatabaseProductName();

                if ( vendor.equalsIgnoreCase( "mysql" ) && errorCode == 1051 && sqlState.equals( "42S02" ) ) {
                    logger.info( "mysql table does not exist, unable to drop ({})", dropSql );
                }
                else if ( vendor.equalsIgnoreCase( "oracle" ) && errorCode == 942 && sqlState.equals( "42000" ) ) {
                    logger.info( "oracle table does not exist, unable to drop ({})", dropSql );
                }
                else {
                    throw( sqle );
                }
            }
            catch ( Exception e ) {
                throw( sqle );
            }
        }
        catch ( Throwable e ) {
            throw sqle;
        }
    }
}

Upvotes: 0

Views: 3307

Answers (1)

user330315
user330315

Reputation:

You can check for the name of the database vendor using DatabaseMetaData.getDatabaseProductName().

That way you don't need to "analyze" the exception

Upvotes: 3

Related Questions