Friday, November 16, 2012

DB2 Weirdness

In the year 2012, what serious database might require code like this:
private ResultSet getColumns(DatabaseMetaData pMetaData,
                             String pCat,
                             String pSchema,
                             String pTableName)
    throws SQLException {
 if (pMetaData.getDatabaseProductName().startsWith("DB2")) {
   final String q = "SELECT null, TABSCHEMA, TABNAME, COLNAME," 
  + " CASE TYPENAME"
  + " WHEN 'BIGINT' THEN -5"
  + " WHEN 'BLOB' THEN 2004"
  + " WHEN 'CHARACTER' THEN 1"
  + " WHEN 'DATE' THEN 91"
  + " WHEN 'INTEGER' THEN 5"
  + " WHEN 'SMALLINT' THEN 4"
  + " WHEN 'TIMESTAMP' THEN 93"
  + " WHEN 'VARCHAR' THEN 12"
  + " WHEN 'XML' THEN -1"
  + " ELSE NULL"
  + " END, TYPENAME, LENGTH FROM SYSCAT.COLUMNS"
  + " WHERE TABSCHEMA=? AND TABNAME=?";
   final PreparedStatement stmt =
     pMetaData.getConnection().prepareStatement(q);
   stmt.setString(1, pSchema);
   stmt.setString(2, pTableName);
   return stmt.executeQuery();
 } else {
   return pMetaData.getColumns(pCat, pSchema, pTableName, null);
 }
}
or this:
  private ResultSet getExportedKeys(DatabaseMetaData pMetaData)
     throws SQLException {
    if (pMetaData.getDatabaseProductName().startsWith("DB2")) {
      final String q = "SELECT null, TABSCHEMA, TABNAME,"
      +  " PK_COLNAMES, null, REFTABSCHEMA, REFTABNAME,"
      +  " FK_COLNAMES, COLCOUNT FROM SYSCAT.REFERENCES"
      +  " WHERE TABSCHEMA=? OR REFTABSCHEMA=?";
      final PreparedStatement stmt =
        pMetaData.getConnection().prepareStatement(q);
      stmt.setString(1, "EKFADM");
      stmt.setString(2, "EKFADM");
      return stmt.executeQuery();   
    } else {
      return pMetaData.getExportedKeys(null, "EKFADM", null);
    }
}

No comments: