0001: /*
0002:
0003: Derby - Class org.apache.derby.catalog.SystemProcedures
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derby.catalog;
0023:
0024: import org.apache.derby.iapi.services.sanity.SanityManager;
0025:
0026: import org.apache.derby.iapi.services.i18n.MessageService;
0027: import org.apache.derby.iapi.error.PublicAPI;
0028: import org.apache.derby.iapi.error.StandardException;
0029: import org.apache.derby.iapi.reference.SQLState;
0030: import java.sql.ResultSet;
0031: import java.sql.Connection;
0032: import java.sql.PreparedStatement;
0033: import java.sql.SQLException;
0034: import java.sql.DatabaseMetaData;
0035: import java.util.StringTokenizer;
0036: import java.util.NoSuchElementException;
0037:
0038: import org.apache.derby.jdbc.InternalDriver;
0039: import org.apache.derby.iapi.db.Factory;
0040: import org.apache.derby.iapi.db.PropertyInfo;
0041: import org.apache.derby.impl.jdbc.Util;
0042: import org.apache.derby.impl.load.Export;
0043: import org.apache.derby.impl.load.Import;
0044: import org.apache.derby.impl.jdbc.EmbedDatabaseMetaData;
0045:
0046: import org.apache.derby.impl.sql.execute.JarDDL;
0047: import org.apache.derby.iapi.util.IdUtil;
0048: import org.apache.derby.iapi.error.PublicAPI;
0049: import org.apache.derby.iapi.error.StandardException;
0050: import org.apache.derby.iapi.sql.conn.ConnectionUtil;
0051:
0052: /**
0053: Some system built-in procedures, and help routines. Now used for network server.
0054: These procedures are built-in to the SYSIBM schema which match the DB2 SYSIBM procedures.
0055: Currently information on those can be found at url:
0056: ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2l2e80.pdf
0057:
0058: <P>
0059: Also used for builtin-routines, such as SYSFUN functions, when direct calls
0060: into Java libraries cannot be made.
0061: */
0062: public class SystemProcedures {
0063:
0064: private final static int SQL_BEST_ROWID = 1;
0065: private final static int SQL_ROWVER = 2;
0066: private final static String DRIVER_TYPE_OPTION = "DATATYPE";
0067: private final static String ODBC_DRIVER_OPTION = "'ODBC'";
0068:
0069: // This token delimiter value is used to separate the tokens for multiple
0070: // error messages. This is used in DRDAConnThread
0071: /**
0072: * <code>SQLERRMC_MESSAGE_DELIMITER</code> When message argument tokes are sent,
0073: * this value separates the tokens for mulitiple error messages
0074: */
0075: public static String SQLERRMC_MESSAGE_DELIMITER = new String(
0076: new char[] { (char) 20, (char) 20, (char) 20 });
0077:
0078: /**
0079: Method used by Cloudscape Network Server to get localized message (original call
0080: from jcc.
0081:
0082: @param sqlcode sqlcode, not used.
0083: @param errmcLen sqlerrmc length
0084: @param sqlerrmc sql error message tokens, variable part of error message (ie.,
0085: arguments) plus messageId, separated by separator.
0086: @param sqlerrp not used
0087: @param errd0 not used
0088: @param errd1 not used
0089: @param errd2 not used
0090: @param errd3 not used
0091: @param errd4 not used
0092: @param errd5 not used
0093: @param warn not used
0094: @param sqlState 5-char sql state
0095: @param file not used
0096: @param localeStr client locale in string
0097: @param msg OUTPUT parameter, localized error message
0098: @param rc OUTPUT parameter, return code -- 0 for success
0099: */
0100: public static void SQLCAMESSAGE(int sqlcode, short errmcLen,
0101: String sqlerrmc, String sqlerrp, int errd0, int errd1,
0102: int errd2, int errd3, int errd4, int errd5, String warn,
0103: String sqlState, String file, String localeStr,
0104: String[] msg, int[] rc) {
0105: int numMessages = 1;
0106:
0107: // Figure out if there are multiple exceptions in sqlerrmc. If so get each one
0108: // translated and append to make the final result.
0109: for (int index = 0;; numMessages++) {
0110: if (sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER, index) == -1)
0111: break;
0112: index = sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER, index)
0113: + SQLERRMC_MESSAGE_DELIMITER.length();
0114: }
0115:
0116: // Putting it here instead of prepareCall it directly is because inter-jar reference tool
0117: // cannot detect/resolve this otherwise
0118: if (numMessages == 1)
0119: MessageService.getLocalizedMessage(sqlcode, errmcLen,
0120: sqlerrmc, sqlerrp, errd0, errd1, errd2, errd3,
0121: errd4, errd5, warn, sqlState, file, localeStr, msg,
0122: rc);
0123: else {
0124: int startIdx = 0, endIdx;
0125: String sqlError;
0126: String[] errMsg = new String[2];
0127: for (int i = 0; i < numMessages; i++) {
0128: endIdx = sqlerrmc.indexOf(SQLERRMC_MESSAGE_DELIMITER,
0129: startIdx);
0130: if (i == numMessages - 1) // last error message
0131: sqlError = sqlerrmc.substring(startIdx);
0132: else
0133: sqlError = sqlerrmc.substring(startIdx, endIdx);
0134:
0135: if (i > 0) {
0136: /* Strip out the SQLState */
0137: sqlState = sqlError.substring(0, 5);
0138: sqlError = sqlError.substring(6);
0139: msg[0] += " SQLSTATE: " + sqlState + ": ";
0140: }
0141:
0142: MessageService.getLocalizedMessage(sqlcode,
0143: (short) sqlError.length(), sqlError, sqlerrp,
0144: errd0, errd1, errd2, errd3, errd4, errd5, warn,
0145: sqlState, file, localeStr, errMsg, rc);
0146:
0147: if (rc[0] == 0) // success
0148: {
0149: if (i == 0)
0150: msg[0] = errMsg[0];
0151: else
0152: msg[0] += errMsg[0]; // append the new message
0153: }
0154: startIdx = endIdx + SQLERRMC_MESSAGE_DELIMITER.length();
0155: }
0156: }
0157: }
0158:
0159: /**
0160: * Get the default or nested connection corresponding to the URL
0161: * jdbc:default:connection. We do not use DriverManager here
0162: * as it is not supported in JSR 169. IN addition we need to perform
0163: * more checks for null drivers or the driver returing null from connect
0164: * as that logic is in DriverManager.
0165: * @return The nested connection
0166: * @throws SQLException Not running in a SQL statement
0167: */
0168: private static Connection getDefaultConn() throws SQLException {
0169: InternalDriver id = InternalDriver.activeDriver();
0170: if (id != null) {
0171: Connection conn = id.connect("jdbc:default:connection",
0172: null);
0173: if (conn != null)
0174: return conn;
0175: }
0176: throw Util.noCurrentConnection();
0177: }
0178:
0179: /**
0180: * Get the DatabaseMetaData for the current connection for use in
0181: * mapping the jcc SYSIBM.* calls to the Cloudscape DatabaseMetaData methods
0182: *
0183: * @return The DatabaseMetaData object of the current connection
0184: */
0185: private static DatabaseMetaData getDMD() throws SQLException {
0186: Connection conn = getDefaultConn();
0187: return conn.getMetaData();
0188: }
0189:
0190: /**
0191: * Map SQLProcedures to EmbedDatabaseMetaData.getProcedures
0192: *
0193: * @param catalogName SYSIBM.SQLProcedures CatalogName varchar(128),
0194: * @param schemaName SYSIBM.SQLProcedures SchemaName varchar(128),
0195: * @param procName SYSIBM.SQLProcedures ProcName varchar(128),
0196: * @param options SYSIBM.SQLProcedures Options varchar(4000))
0197: * @param rs output parameter, the resultset object containing
0198: * the result of getProcedures
0199: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0200: * version of this procedure.
0201: */
0202: public static void SQLPROCEDURES(String catalogName,
0203: String schemaName, String procName, String options,
0204: ResultSet[] rs) throws SQLException {
0205: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0206: .getProceduresForODBC(catalogName, schemaName, procName)
0207: : getDMD().getProcedures(catalogName, schemaName,
0208: procName);
0209: }
0210:
0211: /**
0212: * Map SQLFunctions to EmbedDatabaseMetaData.getFunctions
0213: *
0214: * @param catalogName SYSIBM.SQLFunctions CatalogName varchar(128),
0215: * @param schemaName SYSIBM.SQLFunctions SchemaName varchar(128),
0216: * @param funcName SYSIBM.SQLFunctions ProcName varchar(128),
0217: * @param options SYSIBM.SQLFunctions Options varchar(4000))
0218: * (not used)
0219: * @param rs output parameter, the resultset object containing
0220: * the result of getFunctions
0221: */
0222: public static void SQLFUNCTIONS(String catalogName,
0223: String schemaName, String funcName, String options,
0224: ResultSet[] rs) throws SQLException {
0225: rs[0] = ((EmbedDatabaseMetaData) getDMD()).getFunctions(
0226: catalogName, schemaName, funcName);
0227: }
0228:
0229: /**
0230: * Map SQLTables to EmbedDatabaseMetaData.getSchemas, getCatalogs,
0231: * getTableTypes and getTables, and return the result of the
0232: * DatabaseMetaData calls.
0233: *
0234: * <p>JCC and DNC overload this method:
0235: * <ul>
0236: * <li>If options contains the string 'GETSCHEMAS=1',
0237: * call getSchemas()</li>
0238: * <li>If options contains the string 'GETSCHEMAS=2',
0239: * call getSchemas(String, String)</li>
0240: * <li>If options contains the string 'GETCATALOGS=1',
0241: * call getCatalogs()</li>
0242: * <li>If options contains the string 'GETTABLETYPES=1',
0243: * call getTableTypes()</li>
0244: * <li>otherwise, call getTables()</li>
0245: * </ul>
0246: *
0247: * @param catalogName SYSIBM.SQLTables CatalogName varchar(128),
0248: * @param schemaName SYSIBM.SQLTables SchemaName varchar(128),
0249: * @param tableName SYSIBM.SQLTables TableName varchar(128),
0250: * @param tableType SYSIBM.SQLTables TableType varchar(4000))
0251: * @param options SYSIBM.SQLTables Options varchar(4000))
0252: * @param rs output parameter, the resultset object
0253: */
0254: public static void SQLTABLES(String catalogName, String schemaName,
0255: String tableName, String tableType, String options,
0256: ResultSet[] rs) throws SQLException {
0257:
0258: String optionValue = getOption("GETCATALOGS", options);
0259: if (optionValue != null && optionValue.trim().equals("1")) {
0260: rs[0] = getDMD().getCatalogs();
0261: return;
0262: }
0263: optionValue = getOption("GETTABLETYPES", options);
0264: if (optionValue != null && optionValue.trim().equals("1")) {
0265: rs[0] = getDMD().getTableTypes();
0266: return;
0267: }
0268: optionValue = getOption("GETSCHEMAS", options);
0269: if (optionValue != null) {
0270: optionValue = optionValue.trim();
0271: if (optionValue.equals("1")) {
0272: rs[0] = getDMD().getSchemas();
0273: return;
0274: }
0275: if (optionValue.equals("2")) {
0276: EmbedDatabaseMetaData edmd = (EmbedDatabaseMetaData) getDMD();
0277: rs[0] = edmd.getSchemas(catalogName, schemaName);
0278: return;
0279: }
0280: }
0281:
0282: String[] typeArray = null;
0283: if (tableType != null) {
0284: StringTokenizer st = new StringTokenizer(tableType, "',");
0285: typeArray = new String[st.countTokens()];
0286: int i = 0;
0287:
0288: while (st.hasMoreTokens()) {
0289: typeArray[i] = st.nextToken();
0290: i++;
0291: }
0292: }
0293: rs[0] = getDMD().getTables(catalogName, schemaName, tableName,
0294: typeArray);
0295: }
0296:
0297: /**
0298: * Map SQLForeignKeys to EmbedDatabaseMetaData.getImportedKeys, getExportedKeys, and getCrossReference
0299: *
0300: * @param pkCatalogName SYSIBM.SQLForeignKeys PKCatalogName varchar(128),
0301: * @param pkSchemaName SYSIBM.SQLForeignKeys PKSchemaName varchar(128),
0302: * @param pkTableName SYSIBM.SQLForeignKeys PKTableName varchar(128),
0303: * @param fkCatalogName SYSIBM.SQLForeignKeys FKCatalogName varchar(128),
0304: * @param fkSchemaName SYSIBM.SQLForeignKeys FKSchemaName varchar(128),
0305: * @param fkTableName SYSIBM.SQLForeignKeys FKTableName varchar(128),
0306: * @param options SYSIBM.SQLForeignKeys Options varchar(4000))
0307: * @param rs output parameter, the resultset object
0308: * containing the result of the DatabaseMetaData calls
0309: * JCC overloads this method:
0310: * If options contains the string 'EXPORTEDKEY=1', call getImportedKeys
0311: * If options contains the string 'IMPORTEDKEY=1', call getExportedKeys
0312: * otherwise, call getCrossReference
0313: */
0314: public static void SQLFOREIGNKEYS(String pkCatalogName,
0315: String pkSchemaName, String pkTableName,
0316: String fkCatalogName, String fkSchemaName,
0317: String fkTableName, String options, ResultSet[] rs)
0318: throws SQLException {
0319:
0320: String exportedKeyProp = getOption("EXPORTEDKEY", options);
0321: String importedKeyProp = getOption("IMPORTEDKEY", options);
0322:
0323: if (importedKeyProp != null
0324: && importedKeyProp.trim().equals("1"))
0325: rs[0] = getDMD().getImportedKeys(fkCatalogName,
0326: fkSchemaName, fkTableName);
0327: else if (exportedKeyProp != null
0328: && exportedKeyProp.trim().equals("1"))
0329: rs[0] = getDMD().getExportedKeys(pkCatalogName,
0330: pkSchemaName, pkTableName);
0331: else
0332: rs[0] = getDMD().getCrossReference(pkCatalogName,
0333: pkSchemaName, pkTableName, fkCatalogName,
0334: fkSchemaName, fkTableName);
0335: }
0336:
0337: /**
0338: * Helper for SQLForeignKeys and SQLTables
0339: *
0340: * @return option String containing the value for a given option
0341: * @param pattern String containing the option to search for
0342: * @param options String containing the options to search through
0343: */
0344: private static String getOption(String pattern, String options) {
0345: if (options == null)
0346: return null;
0347: int start = options.lastIndexOf(pattern);
0348: if (start < 0) // not there
0349: return null;
0350: int valueStart = options.indexOf('=', start);
0351: if (valueStart < 0) // invalid options string
0352: return null;
0353: int valueEnd = options.indexOf(';', valueStart);
0354: if (valueEnd < 0) // last option
0355: return options.substring(valueStart + 1);
0356: else
0357: return options.substring(valueStart + 1, valueEnd);
0358: }
0359:
0360: /**
0361: * Map SQLProcedureCols to EmbedDatabaseMetaData.getProcedureColumns
0362: *
0363: * @param catalogName SYSIBM.SQLProcedureCols CatalogName varchar(128),
0364: * @param schemaName SYSIBM.SQLProcedureCols SchemaName varchar(128),
0365: * @param procName SYSIBM.SQLProcedureCols ProcName varchar(128),
0366: * @param paramName SYSIBM.SQLProcedureCols ParamName varchar(128),
0367: * @param options SYSIBM.SQLProcedureCols Options varchar(4000))
0368: * @param rs output parameter, the resultset object containing
0369: * the result of getProcedureColumns
0370: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0371: * version of this procedure.
0372: */
0373: public static void SQLPROCEDURECOLS(String catalogName,
0374: String schemaName, String procName, String paramName,
0375: String options, ResultSet[] rs) throws SQLException {
0376: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0377: .getProcedureColumnsForODBC(catalogName, schemaName,
0378: procName, paramName) : getDMD()
0379: .getProcedureColumns(catalogName, schemaName, procName,
0380: paramName);
0381: }
0382:
0383: /**
0384: * Map SQLFunctionParameters to
0385: * EmbedDatabaseMetaData.getFunctionColumns()
0386: *
0387: * @param catalogName SYSIBM.SQLFunctionParameters CatalogName
0388: * varchar(128),
0389: * @param schemaName SYSIBM.SQLFunctionParameters SchemaName
0390: * varchar(128),
0391: * @param funcName SYSIBM.SQLFunctionParameters FuncName
0392: * varchar(128),
0393: * @param paramName SYSIBM.SQLFunctionParameters ParamName
0394: * varchar(128),
0395: * @param options SYSIBM.SQLFunctionParameters Options
0396: * varchar(4000))
0397: * @param rs output parameter, the resultset object containing the
0398: * result of getFunctionColumns().
0399: */
0400: public static void SQLFUNCTIONPARAMS(String catalogName,
0401: String schemaName, String funcName, String paramName,
0402: String options, ResultSet[] rs) throws SQLException {
0403: rs[0] = ((EmbedDatabaseMetaData) getDMD()).getFunctionColumns(
0404: catalogName, schemaName, funcName, paramName);
0405: }
0406:
0407: /**
0408: * Map SQLColumns to EmbedDatabaseMetaData.getColumns
0409: *
0410: * @param catalogName SYSIBM.SQLColumns CatalogName varchar(128),
0411: * @param schemaName SYSIBM.SQLColumns SchemaName varchar(128),
0412: * @param tableName SYSIBM.SQLColumns TableName varchar(128),
0413: * @param columnName SYSIBM.SQLColumns ColumnName varchar(128),
0414: * @param options SYSIBM.SQLColumns Options varchar(4000))
0415: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0416: * version of this procedure.
0417: * @param rs output parameter, the resultset object containing
0418: * the result of getProcedures
0419: */
0420: public static void SQLCOLUMNS(String catalogName,
0421: String schemaName, String tableName, String columnName,
0422: String options, ResultSet[] rs) throws SQLException {
0423: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0424: .getColumnsForODBC(catalogName, schemaName, tableName,
0425: columnName) : getDMD().getColumns(catalogName,
0426: schemaName, tableName, columnName);
0427: }
0428:
0429: /**
0430: * Map SQLColPrivileges to EmbedDatabaseMetaData.getColumnPrivileges
0431: *
0432: * @param catalogName SYSIBM.SQLColPrivileges CatalogName varchar(128),
0433: * @param schemaName SYSIBM.SQLColPrivileges SchemaName varchar(128),
0434: * @param tableName SYSIBM.SQLColPrivileges ProcName varchar(128),
0435: * @param columnName SYSIBM.SQLColPrivileges ColumnName varchar(128),
0436: * @param options SYSIBM.SQLColPrivileges Options varchar(4000))
0437: * @param rs output parameter, the resultset object containing
0438: * the result of getColumnPrivileges
0439: */
0440: public static void SQLCOLPRIVILEGES(String catalogName,
0441: String schemaName, String tableName, String columnName,
0442: String options, ResultSet[] rs) throws SQLException {
0443: rs[0] = getDMD().getColumnPrivileges(catalogName, schemaName,
0444: tableName, columnName);
0445: }
0446:
0447: /**
0448: * Map SQLTablePrivileges to EmbedDatabaseMetaData.getTablePrivileges
0449: *
0450: * @param catalogName SYSIBM.SQLTablePrivileges CatalogName varchar(128),
0451: * @param schemaName SYSIBM.SQLTablePrivileges SchemaName varchar(128),
0452: * @param tableName SYSIBM.SQLTablePrivileges ProcName varchar(128),
0453: * @param options SYSIBM.SQLTablePrivileges Options varchar(4000))
0454: * @param rs output parameter, the resultset object containing
0455: * the result of getTablePrivileges
0456: */
0457: public static void SQLTABLEPRIVILEGES(String catalogName,
0458: String schemaName, String tableName, String options,
0459: ResultSet[] rs) throws SQLException {
0460: rs[0] = getDMD().getTablePrivileges(catalogName, schemaName,
0461: tableName);
0462: }
0463:
0464: /**
0465: * Map SQLPrimaryKeys to EmbedDatabaseMetaData.getPrimaryKeys
0466: *
0467: * @param catalogName SYSIBM.SQLPrimaryKeys CatalogName varchar(128),
0468: * @param schemaName SYSIBM.SQLPrimaryKeys SchemaName varchar(128),
0469: * @param tableName SYSIBM.SQLPrimaryKeys TableName varchar(128),
0470: * @param options SYSIBM.SQLPrimaryKeys Options varchar(4000))
0471: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0472: * version of this procedure.
0473: * @param rs output parameter, the resultset object containing
0474: * the result of getPrimaryKeys
0475: */
0476: public static void SQLPRIMARYKEYS(String catalogName,
0477: String schemaName, String tableName, String options,
0478: ResultSet[] rs) throws SQLException {
0479: rs[0] = getDMD().getPrimaryKeys(catalogName, schemaName,
0480: tableName);
0481: }
0482:
0483: /**
0484: * Map SQLGetTypeInfo to EmbedDatabaseMetaData.getTypeInfo
0485: *
0486: * @param dataType SYSIBM.SQLGetTypeInfo DataType smallint,
0487: * @param options SYSIBM.SQLGetTypeInfo Options varchar(4000))
0488: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0489: * version of this procedure.
0490: * @param rs output parameter, the resultset object containing the
0491: * result of getTypeInfo
0492: */
0493: public static void SQLGETTYPEINFO(short dataType, String options,
0494: ResultSet[] rs) throws SQLException {
0495: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0496: .getTypeInfoForODBC() : getDMD().getTypeInfo();
0497: }
0498:
0499: /**
0500: * Map SQLStatistics to EmbedDatabaseMetaData.getIndexInfo
0501: *
0502: * @param catalogName SYSIBM.SQLStatistics CatalogName varchar(128),
0503: * @param schemaName SYSIBM.SQLStatistics SchemaName varchar(128),
0504: * @param tableName SYSIBM.SQLStatistics TableName varchar(128),
0505: * @param unique SYSIBM.SQLStatistics Unique smallint; 0=SQL_INDEX_UNIQUE(0); 1=SQL_INDEX_ALL(1),
0506: * @param approximate SYSIBM.SQLStatistics Approximate smallint; 1=true; 0=false,
0507: * @param options SYSIBM.SQLStatistics Options varchar(4000))
0508: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0509: * version of this procedure.
0510: * @param rs output parameter, the resultset object containing
0511: * the result of getIndexInfo
0512: */
0513: public static void SQLSTATISTICS(String catalogName,
0514: String schemaName, String tableName, short unique,
0515: short approximate, String options, ResultSet[] rs)
0516: throws SQLException {
0517: boolean boolUnique = (unique == 0) ? true : false;
0518: boolean boolApproximate = (approximate == 1) ? true : false;
0519:
0520: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0521: .getIndexInfoForODBC(catalogName, schemaName,
0522: tableName, boolUnique, boolApproximate)
0523: : getDMD().getIndexInfo(catalogName, schemaName,
0524: tableName, boolUnique, boolApproximate);
0525: }
0526:
0527: /**
0528: * Map SQLSpecialColumns to EmbedDatabaseMetaData.getBestRowIdentifier and getVersionColumns
0529: *
0530: * @param colType SYSIBM.SQLSpecialColumns ColType smallint,
0531: * where 1 means getBestRowIdentifier and 2 getVersionColumns was called.
0532: * @param catalogName SYSIBM.SQLSpecialColumns CatalogName varchar(128),
0533: * @param schemaName SYSIBM.SQLSpecialColumns SchemaName varchar(128),
0534: * @param tableName SYSIBM.SQLSpecialColumns TableName varchar(128),
0535: * @param scope SYSIBM.SQLSpecialColumns Scope smallint,
0536: * @param nullable SYSIBM.SQLSpecialColumns Nullable smallint; 0=false, 1=true,
0537: * @param options SYSIBM.SQLSpecialColumns Options varchar(4000))
0538: * If options contains the string 'DATATYPE='ODBC'', call the ODBC
0539: * version of this procedure.
0540: * @param rs output parameter, the resultset object containing
0541: * the result of the DatabaseMetaData call
0542: */
0543: public static void SQLSPECIALCOLUMNS(short colType,
0544: String catalogName, String schemaName, String tableName,
0545: short scope, short nullable, String options, ResultSet[] rs)
0546: throws SQLException {
0547:
0548: boolean boolNullable = (nullable == 1) ? true : false;
0549: if (colType == SQL_BEST_ROWID) {
0550: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0551: .getBestRowIdentifierForODBC(catalogName,
0552: schemaName, tableName, scope, boolNullable)
0553: : getDMD().getBestRowIdentifier(catalogName,
0554: schemaName, tableName, scope, boolNullable);
0555: } else // colType must be SQL_ROWVER
0556: {
0557: rs[0] = isForODBC(options) ? ((EmbedDatabaseMetaData) getDMD())
0558: .getVersionColumnsForODBC(catalogName, schemaName,
0559: tableName)
0560: : getDMD().getVersionColumns(catalogName,
0561: schemaName, tableName);
0562: }
0563: }
0564:
0565: /**
0566: * Map SQLUDTS to EmbedDatabaseMetaData.getUDTs
0567: *
0568: * @param catalogName SYSIBM.SQLUDTS CatalogName varchar(128),
0569: * @param schemaPattern SYSIBM.SQLUDTS Schema_Name_Pattern varchar(128),
0570: * @param typeNamePattern SYSIBM.SQLUDTS Type_Name_Pattern varchar(128),
0571: * @param udtTypes SYSIBM.SQLUDTS UDTTypes varchar(128),
0572: * @param options SYSIBM.SQLUDTS Options varchar(4000))
0573: * @param rs output parameter, the resultset object containing
0574: * the result of getUDTs, which will be empty
0575: */
0576: public static void SQLUDTS(String catalogName,
0577: String schemaPattern, String typeNamePattern,
0578: String udtTypes, String options, ResultSet[] rs)
0579: throws SQLException {
0580:
0581: int[] types = null;
0582:
0583: if (udtTypes != null && udtTypes.length() > 0) {
0584: StringTokenizer tokenizer = new StringTokenizer(udtTypes,
0585: " \t\n\t,");
0586: int udtTypeCount = tokenizer.countTokens();
0587: types = new int[udtTypeCount];
0588: String udtType = "";
0589: try {
0590: for (int i = 0; i < udtTypeCount; i++) {
0591: udtType = tokenizer.nextToken();
0592: types[i] = Integer.parseInt(udtType);
0593: }
0594: } catch (NumberFormatException nfe) {
0595: throw new SQLException("Invalid type, " + udtType
0596: + ", passed to getUDTs.");
0597: } catch (NoSuchElementException nsee) {
0598: throw new SQLException(
0599: "Internal failure: NoSuchElementException in getUDTs.");
0600: }
0601: }
0602: rs[0] = getDMD().getUDTs(catalogName, schemaPattern,
0603: typeNamePattern, types);
0604: }
0605:
0606: /*
0607: * Map SYSIBM.METADATA to appropriate EmbedDatabaseMetaData methods
0608: * for now, using the sps in org.apache.derby.iapi.db.jdbc.datadictionary.metadata_net.properties
0609: *
0610: */
0611: public static void METADATA(ResultSet[] rs) throws SQLException {
0612: rs[0] = ((EmbedDatabaseMetaData) getDMD())
0613: .getClientCachedMetaData();
0614: }
0615:
0616: /**
0617: * Helper for ODBC metadata calls.
0618: * @param options String containig the options to search through.
0619: * @return True if options contain ODBC indicator; false otherwise.
0620: */
0621: private static boolean isForODBC(String options) {
0622:
0623: String optionValue = getOption(DRIVER_TYPE_OPTION, options);
0624: return ((optionValue != null) && optionValue.toUpperCase()
0625: .equals(ODBC_DRIVER_OPTION));
0626:
0627: }
0628:
0629: /**
0630: * Set/delete the value of a property of the database in current connection.
0631: * <p>
0632: * Will be called as SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY.
0633: *
0634: * @param key The property key.
0635: * @param value The new value, if null the property is deleted.
0636: *
0637: * @exception StandardException Standard exception policy.
0638: **/
0639: public static void SYSCS_SET_DATABASE_PROPERTY(String key,
0640: String value) throws SQLException {
0641: PropertyInfo.setDatabaseProperty(key, value);
0642: }
0643:
0644: /**
0645: * Get the value of a property of the database in current connection.
0646: * <p>
0647: * Will be called as SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY.
0648: *
0649: * @param key The property key.
0650: *
0651: * @exception StandardException Standard exception policy.
0652: **/
0653: public static String SYSCS_GET_DATABASE_PROPERTY(String key)
0654: throws SQLException {
0655: return (PropertyInfo.getDatabaseProperty(key));
0656: }
0657:
0658: /**
0659: * Compress the table.
0660: * <p>
0661: * Calls the "alter table compress {sequential}" sql. This syntax
0662: * is not db2 compatible so it mapped by a system routine. This
0663: * routine will be called when an application calls:
0664: *
0665: * SYSCS_UTIL.SYSCS_COMPRESS_TABLE
0666: * <p>
0667: *
0668: * @param schema schema name of the table to compress. Must be
0669: * non-null, no default is used.
0670: * @param tablename table name of the table to compress. Must be
0671: * non-null.
0672: * @param sequential if non-zero then rebuild indexes sequentially,
0673: * if 0 then rebuild all indexes in parallel.
0674: *
0675: * @exception StandardException Standard exception policy.
0676: **/
0677: public static void SYSCS_COMPRESS_TABLE(String schema,
0678: String tablename, int sequential) throws SQLException {
0679:
0680: String query = "alter table " + "\"" + schema + "\"" + "."
0681: + "\"" + tablename + "\"" + " compress"
0682: + (sequential != 0 ? " sequential" : "");
0683:
0684: Connection conn = getDefaultConn();
0685:
0686: conn.prepareStatement(query).executeUpdate();
0687:
0688: conn.close();
0689: }
0690:
0691: /**
0692: * Freeze the database.
0693: * <p>
0694: * Call internal routine to freeze the database so that a backup
0695: * can be made.
0696: *
0697: * @exception StandardException Standard exception policy.
0698: **/
0699: public static void SYSCS_FREEZE_DATABASE() throws SQLException {
0700: Factory.getDatabaseOfConnection().freeze();
0701: }
0702:
0703: /**
0704: * Unfreeze the database.
0705: * <p>
0706: * Call internal routine to unfreeze the database, which was "freezed"
0707: * by calling SYSCS_FREEZE_DATABASE().
0708: * can be made.
0709: *
0710: * @exception StandardException Standard exception policy.
0711: **/
0712: public static void SYSCS_UNFREEZE_DATABASE() throws SQLException {
0713: Factory.getDatabaseOfConnection().unfreeze();
0714: }
0715:
0716: public static void SYSCS_CHECKPOINT_DATABASE() throws SQLException {
0717: Factory.getDatabaseOfConnection().checkpoint();
0718: }
0719:
0720: /**
0721: * Backup the database to a backup directory.
0722: *
0723: * This procedure will throw error, if there are any unlogged
0724: * operation executed in the same transaction backup is started.
0725: * If there any unlogged operations in progess in other transaction, it
0726: * will wait until those transactions are completed before starting the backup.
0727: *
0728: * Examples of unlogged operations include: create index and bulk insert.
0729: * Note that once the backup begins these operations will not block,
0730: * instead they are automatically converted into logged operations.
0731: *
0732: * @param backupDir the name of the directory where the backup should be
0733: * stored. This directory will be created if it
0734: * does not exist.
0735: * @exception StandardException thrown on error
0736: */
0737: public static void SYSCS_BACKUP_DATABASE(String backupDir)
0738: throws SQLException {
0739: Factory.getDatabaseOfConnection().backup(backupDir, true);
0740: }
0741:
0742: /**
0743: * Backup the database to a backup directory.
0744: *
0745: * This procedure will throw error, if there are any uncommitted unlogged
0746: * operation before stating the backup. It will not wait for the unlogged
0747: * operations to complete.
0748: *
0749: * Examples of unlogged operations include: create index and bulk insert.
0750: * Note that once the backup begins these operations will not block,
0751: * instead they are automatically converted into logged operations.
0752: *
0753: * @param backupDir the name of the directory where the backup should be
0754: * stored. This directory will be created if it
0755: * does not exist.
0756: * @exception StandardException thrown on error
0757: */
0758: public static void SYSCS_BACKUP_DATABASE_NOWAIT(String backupDir)
0759: throws SQLException {
0760: Factory.getDatabaseOfConnection().backup(backupDir, false);
0761: }
0762:
0763: /**
0764: * Backup the database to a backup directory and enable the log archive
0765: * mode that will keep the archived log files required for roll-forward
0766: * from this version of the backup.
0767: *
0768: * This procedure will throw error if there are any unlogged
0769: * operation executed in the same transaction backup is started.
0770: * If there any unlogged operations in progess in other transaction, it
0771: * will wait until those transactions are completed before starting the backup.
0772: *
0773: * Examples of unlogged operations include: create index and bulk insert.
0774: * Note that once the backup begins these operations will not block,
0775: * instead they are automatically converted into logged operations.
0776: *
0777: * @param backupDir the name of the directory where the backup should be
0778: * stored. This directory will be created if not it
0779: * does not exist.
0780: * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes online
0781: * archived log files that exist before this backup, delete
0782: * will occur only after the backup is complete.
0783: * @exception StandardException thrown on error.
0784: */
0785: public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
0786: String backupDir, int deleteOnlineArchivedLogFiles)
0787: throws SQLException {
0788:
0789: Factory.getDatabaseOfConnection()
0790: .backupAndEnableLogArchiveMode(backupDir,
0791: (deleteOnlineArchivedLogFiles != 0), true);
0792: }
0793:
0794: /**
0795: * Backup the database to a backup directory and enable the log archive
0796: * mode that will keep the archived log files required for roll-forward
0797: * from this version backup.
0798: *
0799: * This procedure will throw error, if there are any uncommitted unlogged
0800: * operation before stating the backup. It will not wait for the unlogged
0801: * operations to complete.
0802: *
0803:
0804: * Examples of unlogged operations include: create index and bulk insert.
0805: * Note that once the backup begins these operations will not block,
0806: * instead they are automatically converted into logged operations.
0807: *
0808: * @param backupDir the name of the directory where the backup should be
0809: * stored. This directory will be created if not it
0810: * does not exist.
0811: *
0812: * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes online
0813: * archived log files that exist before this backup, delete
0814: * will occur only after the backup is complete.
0815: *
0816: * @exception StandardException thrown on error.
0817: */
0818: public static void SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT(
0819: String backupDir, int deleteOnlineArchivedLogFiles)
0820: throws SQLException {
0821:
0822: Factory.getDatabaseOfConnection()
0823: .backupAndEnableLogArchiveMode(backupDir,
0824: (deleteOnlineArchivedLogFiles != 0), false);
0825: }
0826:
0827: /**
0828: * Disables the log archival process, i.e No old log files
0829: * will be kept around for a roll-forward recovery.
0830: *
0831: * @param deleteOnlineArchivedLogFiles If <tt>non-zero</tt> deletes all the
0832: * online archived log files that exist before this call immediately.
0833: *
0834: * @exception StandardException Thrown on error
0835: */
0836:
0837: public static void SYSCS_DISABLE_LOG_ARCHIVE_MODE(
0838: int deleteOnlineArchivedLogFiles) throws SQLException {
0839: Factory.getDatabaseOfConnection().disableLogArchiveMode(
0840: (deleteOnlineArchivedLogFiles != 0));
0841: }
0842:
0843: public static void SYSCS_SET_RUNTIMESTATISTICS(int enable)
0844: throws SQLException {
0845: ConnectionUtil.getCurrentLCC().setRunTimeStatisticsMode(
0846: enable != 0 ? true : false);
0847: }
0848:
0849: public static void SYSCS_SET_STATISTICS_TIMING(int enable)
0850: throws SQLException {
0851: ConnectionUtil.getCurrentLCC().setStatisticsTiming(
0852: enable != 0 ? true : false);
0853: }
0854:
0855: public static int SYSCS_CHECK_TABLE(String schema, String tablename)
0856: throws SQLException {
0857: boolean ret_val = org.apache.derby.iapi.db.ConsistencyChecker
0858: .checkTable(schema, tablename);
0859:
0860: return (ret_val ? 1 : 0);
0861: }
0862:
0863: public static void SYSCS_INPLACE_COMPRESS_TABLE(String schema,
0864: String tablename, int purgeRows, int defragementRows,
0865: int truncateEnd) throws SQLException {
0866: org.apache.derby.iapi.db.OnlineCompress.compressTable(schema,
0867: tablename, (purgeRows == 1), (defragementRows == 1),
0868: (truncateEnd == 1));
0869:
0870: return;
0871: }
0872:
0873: public static String SYSCS_GET_RUNTIMESTATISTICS()
0874: throws SQLException {
0875:
0876: Object rts = ConnectionUtil.getCurrentLCC()
0877: .getRunTimeStatisticsObject();
0878:
0879: if (rts == null)
0880: return null;
0881:
0882: return rts.toString();
0883:
0884: }
0885:
0886: /*
0887: ** SQLJ Procedures.
0888: */
0889:
0890: /**
0891: Install a jar file in the database.
0892:
0893: SQLJ.INSTALL_JAR
0894:
0895: @param url URL of the jar file to be installed in the database.
0896: @param jar SQL name jar will be installed as.
0897: @param deploy Ignored.
0898:
0899: @exception SQLException Error installing jar file.
0900: */
0901: public static void INSTALL_JAR(String url, String jar, int deploy)
0902: throws SQLException {
0903:
0904: try {
0905:
0906: String[] st = IdUtil.parseQualifiedName(jar.trim(), true);
0907:
0908: String schemaName = null;
0909: String sqlName = null;
0910:
0911: switch (st.length) {
0912: case 1:
0913: schemaName = null;
0914: sqlName = st[0];
0915: break;
0916: case 2:
0917: schemaName = st[0];
0918: sqlName = st[1];
0919: default:
0920: ; // RESOLVE
0921: }
0922:
0923: checkJarSQLName(sqlName);
0924: JarDDL.add(schemaName, sqlName, url);
0925: } catch (StandardException se) {
0926: throw PublicAPI.wrapStandardException(se);
0927: }
0928: }
0929:
0930: /**
0931: Replace a jar file in the database.
0932:
0933: SQLJ.REPLACE_JAR
0934:
0935: @param url URL of the jar file to be installed in the database.
0936: @param jar SQL name of jar to be replaced.
0937:
0938: @exception SQLException Error replacing jar file.
0939: */
0940: public static void REPLACE_JAR(String url, String jar)
0941: throws SQLException {
0942:
0943: try {
0944:
0945: String[] st = IdUtil.parseQualifiedName(jar.trim(), true);
0946:
0947: String schemaName = null;
0948: String sqlName = null;
0949:
0950: switch (st.length) {
0951: case 1:
0952: schemaName = null;
0953: sqlName = st[0];
0954: break;
0955: case 2:
0956: schemaName = st[0];
0957: sqlName = st[1];
0958: default:
0959: ; // RESOLVE
0960: }
0961:
0962: checkJarSQLName(sqlName);
0963: JarDDL.replace(schemaName, sqlName, url);
0964: } catch (StandardException se) {
0965: throw PublicAPI.wrapStandardException(se);
0966: }
0967: }
0968:
0969: /**
0970: Remove a jar file from the database.
0971:
0972: @param jar SQL name of jar to be replaced.
0973: @param undeploy Ignored.
0974:
0975: @exception SQLException Error removing jar file.
0976: */
0977: public static void REMOVE_JAR(String jar, int undeploy)
0978: throws SQLException {
0979:
0980: try {
0981:
0982: String[] st = IdUtil.parseQualifiedName(jar.trim(), true);
0983:
0984: String schemaName = null;
0985: String sqlName = null;
0986:
0987: switch (st.length) {
0988: case 1:
0989: schemaName = null;
0990: sqlName = st[0];
0991: break;
0992: case 2:
0993: schemaName = st[0];
0994: sqlName = st[1];
0995: default:
0996: ; // RESOLVE
0997: }
0998:
0999: checkJarSQLName(sqlName);
1000:
1001: JarDDL.drop(schemaName, sqlName);
1002: } catch (StandardException se) {
1003: throw PublicAPI.wrapStandardException(se);
1004: }
1005: }
1006:
1007: private static void checkJarSQLName(String sqlName)
1008: throws StandardException {
1009:
1010: // weed out a few special cases that cause problems.
1011: if ((sqlName.length() == 0) || (sqlName.indexOf(':') != -1)) {
1012:
1013: throw StandardException
1014: .newException(SQLState.ID_PARSE_ERROR);
1015: }
1016: }
1017:
1018: /**
1019: * Export data from a table to given file.
1020: * <p>
1021: * Will be called by system procedure:
1022: * SYSCS_EXPORT_TABLE(IN SCHEMANAME VARCHAR(128),
1023: * IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) ,
1024: * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1025: * IN CODESET VARCHAR(128))
1026: * @exception StandardException Standard exception policy.
1027: **/
1028: public static void SYSCS_EXPORT_TABLE(String schemaName,
1029: String tableName, String fileName, String columnDelimiter,
1030: String characterDelimiter, String codeset)
1031: throws SQLException {
1032: Connection conn = getDefaultConn();
1033: Export.exportTable(conn, schemaName, tableName, fileName,
1034: columnDelimiter, characterDelimiter, codeset);
1035: //export finished successfully, issue a commit
1036: conn.commit();
1037: }
1038:
1039: /**
1040: * Export data from a select statement to given file.
1041: * <p>
1042: * Will be called as
1043: * SYSCS_EXPORT_QUERY(IN SELECTSTATEMENT VARCHAR(32672),
1044: * IN FILENAME VARCHAR(32672) ,
1045: * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1046: * IN CODESET VARCHAR(128))
1047: *
1048: * @exception StandardException Standard exception policy.
1049: **/
1050: public static void SYSCS_EXPORT_QUERY(String selectStatement,
1051: String fileName, String columnDelimiter,
1052: String characterDelimiter, String codeset)
1053: throws SQLException {
1054: Connection conn = getDefaultConn();
1055: Export.exportQuery(conn, selectStatement, fileName,
1056: columnDelimiter, characterDelimiter, codeset);
1057:
1058: //export finished successfully, issue a commit
1059: conn.commit();
1060: }
1061:
1062: /**
1063: * Import data from a given file to a table.
1064: * <p>
1065: * Will be called by system procedure as
1066: * SYSCS_IMPORT_TABLE(IN SCHEMANAME VARCHAR(128),
1067: * IN TABLENAME VARCHAR(128), IN FILENAME VARCHAR(32672) ,
1068: * IN COLUMNDELIMITER CHAR(1), IN CHARACTERDELIMITER CHAR(1) ,
1069: * IN CODESET VARCHAR(128), IN REPLACE SMALLINT)
1070: * @exception StandardException Standard exception policy.
1071: **/
1072: public static void SYSCS_IMPORT_TABLE(String schemaName,
1073: String tableName, String fileName, String columnDelimiter,
1074: String characterDelimiter, String codeset, short replace)
1075: throws SQLException {
1076: Connection conn = getDefaultConn();
1077: try {
1078: Import.importTable(conn, schemaName, tableName, fileName,
1079: columnDelimiter, characterDelimiter, codeset,
1080: replace);
1081: } catch (SQLException se) {
1082: //issue a rollback on any errors
1083: conn.rollback();
1084: throw se;
1085: }
1086: //import finished successfull, commit it.
1087: conn.commit();
1088: }
1089:
1090: /**
1091: * Import data from a given file into the specified table columns from the
1092: * specified columns in the file.
1093: * <p>
1094: * Will be called as
1095: * SYSCS_IMPORT_DATA (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128),
1096: * IN INSERTCOLUMNLIST VARCHAR(32762), IN COLUMNINDEXES VARCHAR(32762),
1097: * IN FILENAME VARCHAR(32762), IN COLUMNDELIMITER CHAR(1),
1098: * IN CHARACTERDELIMITER CHAR(1), IN CODESET VARCHAR(128),
1099: * IN REPLACE SMALLINT)
1100: *
1101: * @exception StandardException Standard exception policy.
1102: **/
1103: public static void SYSCS_IMPORT_DATA(String schemaName,
1104: String tableName, String insertColumnList,
1105: String columnIndexes, String fileName,
1106: String columnDelimiter, String characterDelimiter,
1107: String codeset, short replace) throws SQLException {
1108: Connection conn = getDefaultConn();
1109: try {
1110: Import.importData(conn, schemaName, tableName,
1111: insertColumnList, columnIndexes, fileName,
1112: columnDelimiter, characterDelimiter, codeset,
1113: replace);
1114: } catch (SQLException se) {
1115: //issue a rollback on any errors
1116: conn.rollback();
1117: throw se;
1118: }
1119:
1120: //import finished successfull, commit it.
1121: conn.commit();
1122: }
1123:
1124: /**
1125: * Perform bulk insert using the specificed vti .
1126: * <p>
1127: * Will be called as
1128: * SYSCS_BULK_INSERT (IN SCHEMANAME VARCHAR(128), IN TABLENAME VARCHAR(128),
1129: * IN VTINAME VARCHAR(32762), IN VTIARG VARCHAR(32762))
1130: *
1131: * @exception StandardException Standard exception policy.
1132: **/
1133: public static void SYSCS_BULK_INSERT(String schemaName,
1134: String tableName, String vtiName, String vtiArg)
1135: throws SQLException {
1136: Connection conn = getDefaultConn();
1137:
1138: String entityName = (schemaName == null ? tableName
1139: : schemaName + "." + tableName);
1140: String binsertSql = "insert into " + entityName
1141: + " --DERBY-PROPERTIES insertMode=bulkInsert \n"
1142: + "select * from new " + vtiName + "(" + "'"
1143: + schemaName + "'" + ", " + "'" + tableName + "'"
1144: + ", " + "'" + vtiArg + "'" + ")" + " as t";
1145:
1146: PreparedStatement ps = conn.prepareStatement(binsertSql);
1147: ps.executeUpdate();
1148: ps.close();
1149: }
1150:
1151: /**
1152: * Method to return the constant PI.
1153: * SYSFUN.PI().
1154: * @return PI
1155: */
1156: public static double PI() {
1157: return StrictMath.PI;
1158: }
1159:
1160: /**
1161: * Constant for natural log(10).
1162: */
1163: private static final double LOG10 = StrictMath.log(10.0d);
1164:
1165: /**
1166: * Base 10 log function. SYSFUN.LOG10
1167: * Calculated by
1168: * <code>
1169: * log(value) / log(10)
1170: * </code>
1171: * where log is the natural log.
1172: */
1173: public static double LOG10(double value) {
1174: return StrictMath.log(value) / LOG10;
1175: }
1176: }
|