/* ========== licence begin GPL Copyright (C) 2002-2003 SAP AG This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. ========== licence end */ package com.sap.dbtech.jdbc; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Hashtable; import com.sap.dbtech.jdbc.exceptions.DatabaseException; import com.sap.dbtech.jdbc.exceptions.InternalJDBCError; import com.sap.dbtech.util.MessageKey; import com.sap.dbtech.util.MessageTranslator; import com.sap.dbtech.util.VersionInfo; /** * DatabaseMetaData for MaxDB 7.6 and up. * * This honors the new system tables introduced with version 7.6. */ public class DatabaseMetaDataMaxDB extends DatabaseMetaDataBase { private VersionInfo dbVersionInfo; private String dbVersion; private Hashtable systeminfo; /** * Constructor. * @param connection The connection for the meta data. * @param dbVersionInfo Version info. */ DatabaseMetaDataMaxDB(ConnectionSapDB connection, VersionInfo dbVersionInfo) throws SQLException { super(connection); this.dbVersionInfo = dbVersionInfo; this.hasSchemaSupport = true; loadSystemInfo(); } public int getDatabaseMajorVersion() throws SQLException { return this.dbVersionInfo.getMajorVersion(); } public int getDatabaseMinorVersion() throws SQLException { return dbVersionInfo.getMinorVersion(); } public int getMaxConnections() throws SQLException { return getIntProperty("MAXCONNECTIONS"); } public int getMaxCursorNameLength() throws SQLException { return getIntProperty("MAXCURSORNAMELENGTH"); } public int getMaxIndexLength() throws SQLException { return getIntProperty("MAXINDEXLENGTH"); } public int getMaxProcedureNameLength() throws SQLException { return getIntProperty("MAXPROCEDURENAMELENGTH"); } public int getMaxRowSize() throws SQLException { return getIntProperty("MAXROWSIZE"); } public int getMaxSchemaNameLength() throws SQLException { return getIntProperty("MAXSCHEMANAMELENGTH"); } public int getMaxTableNameLength() throws SQLException { return getIntProperty("MAXTABLENAMELENGTH"); } public int getMaxTablesInSelect() throws SQLException { return getIntProperty("MAXTABLESINSELECT"); } public int getMaxUserNameLength() throws SQLException { return getIntProperty("MAXUSERNAMELENGTH"); } public String getDatabaseProductVersion() throws SQLException { if(this.dbVersion == null) { ResultSet rs = this.internalQuery("SELECT \"VALUE\" FROM SYSJDBC.SYSTEMINFO " + "WHERE PROPERTY='KERNELVERSION'", "getDatabaseProductVersion"); rs.next(); this.dbVersion = rs.getString(1); rs.close(); } return this.dbVersion; } public String getDriverName() throws SQLException { return DriverSapDB.singleton ().getName (); } public String getDriverVersion() throws SQLException { return DriverSapDB.singleton ().getVersionString (); } public ResultSet getCatalogs() throws SQLException { return internalQuery("SELECT TABLE_CAT FROM SYSJDBC.CATALOGS", "getCatalogs"); } public ResultSet getSchemas() throws SQLException { return internalQuery("SELECT TABLE_SCHEM, TABLE_CATALOG FROM " + "SYSJDBC.SCHEMAS ORDER BY TABLE_SCHEM", "getSchemas"); } public ResultSet getTableTypes() throws SQLException { return internalQuery("SELECT TABLE_TYPE FROM SYSJDBC.TABLETYPES ORDER BY TABLE_TYPE", "getTableTypes"); } public ResultSet getTypeInfo() throws SQLException { return internalQuery("SELECT TYPE_NAME, DATA_TYPE, PRECISION, " + "LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, NULLABLE," + "CASE_SENSITIVE, SEARCHABLE, UNSIGNED_ATTRIBUTE, FIXED_PREC_SCALE," + "AUTO_INCREMENT, LOCAL_TYPE_NAME, MINIMUM_SCALE, MAXIMUM_SCALE," + "SQL_DATA_TYPE, SQL_DATETIME_SUB, NUM_PREC_RADIX " + "FROM SYSJDBC.SESSIONTYPEINFO ORDER BY DATA_TYPE, MATCHORDER", "getTypeInfo"); } public ResultSet getExportedKeys( String catalog, String schema, String table) throws SQLException { PreparedStatement ps = this.internalPreparedStatement( "SELECT PKTABLE_CAT," + "PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, " + "FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE," + "DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY " + "FROM SYSJDBC.CROSSREFERENCES " + "WHERE (1 = ? OR PKTABLE_SCHEM = ?) " + " AND PKTABLE_NAME = ? " + "ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ", "getExportedKeys"); if (schema == null) { ps.setInt(1, 1); ps.setNull(2, java.sql.Types.CHAR); } else { ps.setInt(1, 0); ps.setString(2, schema); } ps.setString(3, table); return setFromMetaData(ps.executeQuery()); } public ResultSet getImportedKeys( String catalog, String schema, String table) throws SQLException { PreparedStatement ps = this.internalPreparedStatement( "SELECT PKTABLE_CAT," + "PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, " + "FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE," + "DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, COMMENT " + "FROM SYSJDBC.CROSSREFERENCES " + "WHERE (1 = ? OR FKTABLE_SCHEM = ?) " + " AND FKTABLE_NAME = ? " + "ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ", "getImportedKeys"); if (schema == null) { ps.setInt(1, 1); ps.setNull(2, java.sql.Types.CHAR); } else { ps.setInt(1, 0); ps.setString(2, schema); } ps.setString(3, table); return setFromMetaData(ps.executeQuery()); } public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT " + "TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME " + "FROM SYSJDBC.PRIMARYKEYS " + "WHERE (1 = ? OR TABLE_SCHEM = ?)" + " AND TABLE_NAME = ?", "getPrimaryKeys"); if(schema == null) { ps.setInt(1, 1); ps.setNull(2, Types.CHAR); } else { ps.setInt(1, 0); ps.setString(2, schema); } ps.setString(3, table); return setFromMetaData(ps.executeQuery()); } public ResultSet getProcedures(String catalog, String schemapattern, String procedurepattern) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT " + "PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, RESERVED4, " + "RESERVED5, RESERVED6, REMARKS, PROCEDURE_TYPE " + "FROM SYSJDBC.PROCEDURES " + "WHERE PROCEDURE_SCHEM LIKE ? ESCAPE '\\'" + " AND PROCEDURE_NAME LIKE ? ESCAPE '\\' " + "ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME", "getProcedures"); ps.setString(1, schemapattern==null?"%":schemapattern); ps.setString(2, procedurepattern==null?"%":procedurepattern); return setFromMetaData(ps.executeQuery()); } public ResultSet getSuperTables(String catalog, String schema, String tablename) throws SQLException { return internalQuery("SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, " + "SUPERTABLE_NAME FROM SYSJDBC.SUPERTABLES", "getSuperTables"); } public ResultSet getSuperTypes(String arg0, String arg1, String arg2) throws SQLException { return internalQuery("SELECT TYPE_CAT, TYPE_SCHEM, TYPE_NAME," + "SUPERTYPE_CAT, SUPERTYPE_SCHEM, SUPERTYPE_NAME " + "FROM SYSJDBC.SUPERTYPES", "getSuperTypes"); } public ResultSet getTablePrivileges( String catalog, String schema, String table) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT TABLE_CAT, " + "TABLE_SCHEM, TABLE_NAME, GRANTOR, GRANTEE, PRIVILEGE, " + "IS_GRANTABLE " + "FROM SYSJDBC.TABLEPRIVILEGES " + "WHERE TABLE_SCHEM LIKE ? ESCAPE '\\' AND TABLE_NAME LIKE ? ESCAPE '\\' " + "ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE", "getTablePrivileges"); ps.setString(1, schema==null?"%":schema); ps.setString(2, table==null?"%":table); return setFromMetaData(ps.executeQuery()); } public ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException { return internalQuery("SELECT SCOPE, COLUMN_NAME, DATA_TYPE, " + "TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, " + "PSEUDO_COLUMN FROM SYSJDBC.VERSIONCOLUMNS", "getVersionColumns"); } public ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException { PreparedStatement ps = null; if(schema != null) { ps = this.internalPreparedStatement("SELECT SCOPE, COLUMN_NAME, " + "DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, " + "PSEUDO_COLUMN FROM SYSJDBC.BESTROWIDENTIFIER " + "WHERE TABLE_SCHEM=? AND TABLE_NAME=? " + "ORDER BY SCOPE, COLUMN_NAME", "getBestRowIdentifier"); ps.setString(1, schema); ps.setString(2, table); } else { ps = this.internalPreparedStatement("SELECT SCOPE, COLUMN_NAME, " + "DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, " + "PSEUDO_COLUMN FROM SYSJDBC.BESTROWIDENTIFIER " + "WHERE TABLE_NAME=? " + "ORDER BY SCOPE, COLUMN_NAME", "getBestRowIdentifier"); ps.setString(1, table); } return setFromMetaData(ps.executeQuery()); } private String getIndexInfoTable(boolean unique, boolean approximate) { if(approximate) { return "SYSJDBC.APPROXINDEXINFO"; } else if(unique) { return "SYSJDBC.UNIQUEINDEXINFO"; } else { return "SYSJDBC.INDEXINFO"; } } /* (non-Javadoc) * @see java.sql.DatabaseMetaData#getIndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean, boolean) */ public ResultSet getIndexInfo( String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT" + " TABLE_CAT, TABLE_SCHEM, TABLE_NAME, NON_UNIQUE, INDEX_QUALIFIER, " + " INDEX_NAME, TYPE, ORDINAL_POSITION, COLUMN_NAME, ASC_OR_DESC, " + " CARDINALITY, PAGES, FILTER_CONDITION, COMMENT FROM " + getIndexInfoTable(unique, approximate) + " WHERE ( 1 = ? OR TABLE_SCHEM = ?) " + " AND TABLE_NAME = ? " + " AND ( 1= ? OR NON_UNIQUE = ? ) " + "ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION", "getIndexInfo"); if(schema == null) { ps.setInt(1, 1); ps.setNull(2, Types.CHAR); } else { ps.setInt(1, 0); ps.setString(2, schema); } ps.setString(3, table); if(unique) { ps.setInt(4, 0); ps.setBoolean(5, false); } else { ps.setInt(4, 1); ps.setBoolean(5, true); } return setFromMetaData(ps.executeQuery()); } public ResultSet getUDTs(String catalog, String schemapatterm, String typeNamePattern, int[] types) throws SQLException { return internalQuery("SELECT TYPE_CAT, TYPE_SCHEM, TYPE_NAME, " + "CLASS_NAME, DATA_TYPE, REMARKS, BASE_TYPE " + "FROM SYSJDBC.UDTS", "getUDTs"); } public ResultSet getAttributes( String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern) throws SQLException { return this.internalQuery("SELECT TYPE_CAT, " + " TYPE_SCHEM," + " TYPE_NAME," + " ATTR_NAME," + " DATA_TYPE," + " ATTR_TYPE_NAME," + " ATTR_SIZE," + " DECIMAL_DIGITS," + " NUM_PREC_RADIX," + " NULLABLE," + " REMARKS," + " ATTR_DEF," + " SQL_DATA_TYPE," + " SQL_DATETIME_SUB," + " CHAR_OCTET_LENGTH," + " ORDINAL_POSITION," + " IS_NULLABLE," + " SCOPE_CATALOG," + " SCOPE_SCHEMA," + " SCOPE_TABLE," + " SOURCE_DATA_TYPE " + " FROM SYSJDBC.ATTRIBUTES", "getAttributes"); } public ResultSet getColumnPrivileges( String catalog, String schema, String tablename, String columnnamepattern) throws SQLException { PreparedStatement ps = null; if(schema != null) { ps = this.internalPreparedStatement("SELECT TABLE_CAT, " + "TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, GRANTOR, GRANTEE, " + "PRIVILEGE, IS_GRANTABLE " + "FROM SYSJDBC.COLUMNPRIVILEGES " + "WHERE TABLE_SCHEM=? AND TABLE_NAME=? AND COLUMN_NAME LIKE ? ESCAPE '\\'", "getColumnPrivileges"); ps.setString(1, schema); ps.setString(2, tablename); ps.setString(3, columnnamepattern); } else { ps = this.internalPreparedStatement("SELECT TABLE_CAT, " + "TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, GRANTOR, GRANTEE, " + "PRIVILEGE, IS_GRANTABLE " + "FROM SYSJDBC.COLUMNPRIVILEGES " + "WHERE TABLE_NAME=? AND COLUMN_NAME LIKE ? ESCAPE '\\' ", "getColumnPrivileges"); ps.setString(1, tablename); ps.setString(2, columnnamepattern); } return setFromMetaData(ps.executeQuery()); } public ResultSet getColumns( String catalog, String schemaPattern, String tablePattern, String columnPattern) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT TABLE_CAT, " + "TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, " + "COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE," + "REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, " + "CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE, SCOPE_CATLOG, " + "SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, COLUMN_DEF_FUNC " + "FROM SYSJDBC.COLUMNS " + "WHERE TABLE_SCHEM LIKE ? ESCAPE '\\' AND TABLE_NAME LIKE ? ESCAPE '\\' AND COLUMN_NAME LIKE ? ESCAPE '\\'" + "ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION", "getColumns"); ps.setString(1, schemaPattern==null?"%":schemaPattern); ps.setString(2, tablePattern==null?"%":tablePattern); ps.setString(3, columnPattern==null?"%":columnPattern); return setFromMetaData(ps.executeQuery()); } public ResultSet getProcedureColumns( String catalog, String schemaPattern, String procedurePattern, String columnPattern) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT " + "PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_NAME, " + "COLUMN_TYPE, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE, " + "RADIX, NULLABLE, REMARKS " + "FROM SYSJDBC.PROCEDURECOLUMNS " + "WHERE (PROCEDURE_SCHEM LIKE ? ESCAPE '\\' ) " + " AND (PROCEDURE_NAME LIKE ? ESCAPE '\\' ) " + " AND (COLUMN_NAME LIKE ? ESCAPE '\\' ) " + "ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, ORDINAL_POSITION", "getProcedureColumns"); ps.setString(1, schemaPattern==null?"%":schemaPattern); ps.setString(2, procedurePattern==null?"%":procedurePattern); ps.setString(3, columnPattern==null?"%":columnPattern); return setFromMetaData(ps.executeQuery()); } /* (non-Javadoc) * @see java.sql.DatabaseMetaData#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[]) */ public ResultSet getTables( String catalog, String schemapattern, String tablepattern, String[] types) throws SQLException { boolean whereadded = false; String s = "SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, " + "TABLE_TYPE, REMARKS, SAMPLE_PERCENT, SAMPLE_ROWS FROM SYSJDBC.TABLES"; if(schemapattern != null) { s += " WHERE TABLE_SCHEM LIKE ? ESCAPE '\\' "; whereadded = true; } if(tablepattern != null) { if(whereadded) { s += " AND TABLE_NAME LIKE ? ESCAPE '\\' "; } else { s += " WHERE TABLE_NAME LIKE ? ESCAPE '\\'"; whereadded = true; } } if(types != null && types.length > 0) { if(whereadded) { s += " AND ("; } else { s += " WHERE ("; whereadded = true; } for(int i=0; i pmd.getPrecision(pcount)) { ps.setNull(pcount, Types.CHAR); } else { ps.setString(pcount, types[k]); } ++pcount; } } return setFromMetaData(ps.executeQuery()); } public ResultSet getCrossReference( String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException { PreparedStatement ps = this.internalPreparedStatement("SELECT PKTABLE_CAT," + "PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, " + "FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE," + "DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY " + "FROM SYSJDBC.CROSSREFERENCES " + "WHERE (1 = ? OR PKTABLE_SCHEM = ?) " + " AND (1 = ? OR FKTABLE_SCHEM = ?) " + " AND PKTABLE_NAME = ? AND FKTABLE_NAME = ? " + "ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ", "getCrossReference"); if(primarySchema == null) { ps.setInt(1, 1); ps.setNull(2, java.sql.Types.CHAR); } else { ps.setInt(1, 0); ps.setString(2, primarySchema); } if(foreignSchema == null) { ps.setInt(3, 1); ps.setNull(4, java.sql.Types.CHAR); } else { ps.setInt(3,0); ps.setString(4, foreignSchema); } ps.setString(5, primaryTable); ps.setString(6, foreignTable); return setFromMetaData(ps.executeQuery()); } private PreparedStatement internalPreparedStatement(String cmd, String routine) throws SQLException{ PreparedStatement result; com.sap.dbtech.util.Tracer.println("\n"+cmd+"\n\n"); try { result = this.connection.prepareStatement(cmd, dbmdResultSetType, ResultSet.CONCUR_READ_ONLY); try { ((ResultSetSapDB) result).setFromMetaData(true); } catch (ClassCastException ccx) { } } catch (DatabaseException dbExc) { throw new InternalJDBCError(routine, dbExc); } return result; } private ResultSet setFromMetaData(ResultSet rs) { try { ((ResultSetSapDB) rs).setFromMetaData(true); } catch (ClassCastException ccx) { } return rs; } private void loadSystemInfo() throws SQLException { this.systeminfo=new Hashtable(); ResultSet rs=internalQuery("SELECT \"PROPERTY\", \"VALUE\" " + "FROM SYSJDBC.SYSTEMINFO", "loadSystemInfo"); while(rs.next()) { this.systeminfo.put(rs.getString(1), rs.getString(2)); } rs.close(); } public int getMaxBinaryLiteralLength() throws SQLException { return getIntProperty("MAXBINARYLITERALLENGTH"); } public int getMaxCatalogNameLength() throws SQLException { return getIntProperty("MAXCATALOGNAMELENGTH"); } public int getMaxCharLiteralLength() throws SQLException { return getIntProperty("MAXCHARLITERALLENGTH"); } public int getMaxColumnNameLength() throws SQLException { return getIntProperty("MAXCOLUMNNAMELENGTH"); } public int getMaxColumnsInGroupBy() throws SQLException { return getIntProperty("MAXCOLUMNSINGROUPBY"); } public int getMaxColumnsInIndex() throws SQLException { return getIntProperty("MAXCOLUMNSININDEX"); } public int getMaxColumnsInOrderBy() throws SQLException { return getIntProperty("MAXCOLUMNSINORDERBY"); } public int getMaxColumnsInSelect() throws SQLException { return getIntProperty("MAXCOLUMNSINSELECT"); } public int getMaxColumnsInTable() throws SQLException { return getIntProperty("MAXCOLUMNSINTABLE"); } private int getIntProperty(String property) throws SQLException { try { return Integer.parseInt((String)systeminfo.get(property)); } catch(Exception ex) { throw new InternalJDBCError(MessageTranslator.translate( MessageKey.ERROR_NOMETADATA, property, ex.getMessage())); } } public ResultSet getConstraints( String catalog, String schemapattern, String tablepattern) throws SQLException { boolean whereadded = false; String s = "SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, " + "CONSTRAINT_NAME, CONSTRAINT_DEFINITION FROM SYSJDBC.CONSTRAINTS"; if(schemapattern != null) { s += " WHERE TABLE_SCHEM LIKE ? ESCAPE '\\'"; whereadded = true; } if(tablepattern != null) { if(whereadded) { s += " AND TABLE_NAME LIKE ? ESCAPE '\\'"; } else { s += " WHERE TABLE_NAME LIKE ? ESCAPE '\\'"; whereadded = true; } } PreparedStatement ps = this.internalPreparedStatement(s, "getConstraints"); int pcount = 1; if(schemapattern != null) { ps.setString(pcount, schemapattern); ++pcount; } if(tablepattern != null) { ps.setString(pcount, tablepattern); ++pcount; } return setFromMetaData(ps.executeQuery()); } }