0001: /*
0002: * QuerySender.java
0003: *
0004: * Copyright (C) 2002, 2003, 2004, 2005, 2006 Takis Diakoumis
0005: *
0006: * This program is free software; you can redistribute it and/or
0007: * modify it under the terms of the GNU General Public License
0008: * as published by the Free Software Foundation; either version 2
0009: * of the License, or any later version.
0010: *
0011: * This program is distributed in the hope that it will be useful,
0012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0014: * GNU General Public License for more details.
0015: *
0016: * You should have received a copy of the GNU General Public License
0017: * along with this program; if not, write to the Free Software
0018: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
0019: *
0020: */
0021:
0022: package org.executequery.databasemediators;
0023:
0024: import java.math.BigDecimal;
0025:
0026: import java.sql.CallableStatement;
0027: import java.sql.Connection;
0028: import java.sql.DatabaseMetaData;
0029: import java.sql.ResultSet;
0030: import java.sql.SQLException;
0031: import java.sql.Statement;
0032: import java.sql.Types;
0033: import java.util.ArrayList;
0034: import java.util.Arrays;
0035: import java.util.Hashtable;
0036: import java.util.List;
0037: import java.util.StringTokenizer;
0038: import org.executequery.datasource.ConnectionManager;
0039: import org.executequery.util.Log;
0040: import org.underworldlabs.jdbc.DataSourceException;
0041: import org.underworldlabs.util.MiscUtils;
0042:
0043: /* ----------------------------------------------------------
0044: * CVS NOTE: Changes to the CVS repository prior to the
0045: * release of version 3.0.0beta1 has meant a
0046: * resetting of CVS revision numbers.
0047: * ----------------------------------------------------------
0048: */
0049:
0050: /**
0051: * This class handles all database query functions
0052: * such as the execution of SQL SELECT, INSERT, UPDATE
0053: * etc statements.
0054: *
0055: * <p>This class will typically be used by the Database
0056: * Browser or Query Editor where all SQL statements to be
0057: * executed will pass through here. In the case of a Query
0058: * Editor, a dedicated connection is maintained by this class
0059: * for the editor's use. This was shown to decrease some overhead
0060: * associated with constantly retrieving conenctions from the
0061: * pool. Also, when the commit mode is not set to auto-commit
0062: * within an editor, a dedicated connection is required
0063: * so as to maintain the correct rollback segment.
0064: *
0065: * @author Takis Diakoumis
0066: * @version $Revision: 1.7 $
0067: * @date $Date: 2006/09/24 14:03:56 $
0068: */
0069: public class QuerySender {
0070:
0071: /** Whether this object is owned by a QueryEditor instance */
0072: private boolean keepAlive;
0073:
0074: /** The connection's commit mode */
0075: private boolean commitMode;
0076:
0077: /** The database connection */
0078: private Connection conn;
0079:
0080: /** The database <code>Statement</code> object */
0081: private Statement stmnt;
0082:
0083: /** The database <code>CallableStatement</code> object */
0084: //private CallableStatement cstmnt;
0085: /** The connection use count */
0086: private int useCount = 0;
0087:
0088: /** The specified maximum connection use count */
0089: private int maxUseCount;
0090:
0091: /** the query result object */
0092: private SqlStatementResult statementResult;
0093:
0094: /** the database connection properties object */
0095: private DatabaseConnection databaseConnection;
0096:
0097: /** the meta data value retrieval object */
0098: private MetaDataValues metaData;
0099:
0100: // ---------------------------------
0101: // SQL statement type int constants
0102: // ---------------------------------
0103:
0104: public static final int ALL_UPDATES = 80;
0105:
0106: /** An SQL INSERT statement */
0107: public static final int INSERT = 80;
0108: /** An SQL UPDATE statement */
0109: public static final int UPDATE = 81;
0110: /** An SQL DELETE statement */
0111: public static final int DELETE = 82;
0112: /** An SQL SELECT statement */
0113: public static final int SELECT = 10;
0114: /** A DESCRIBE statement - table meta data */
0115: public static final int DESCRIBE = 16;
0116: /** An SQL EXPLAIN statement */
0117: public static final int EXPLAIN = 15;
0118: /** An SQL EXECUTE statement (procedure) */
0119: public static final int EXECUTE = 11;
0120: /** An SQL DROP TABLE statement */
0121: public static final int DROP_TABLE = 20;
0122: /** An SQL CREATE TABLE statement */
0123: public static final int CREATE_TABLE = 21;
0124: /** An SQL ALTER TABLE statement */
0125: public static final int ALTER_TABLE = 22;
0126: /** An SQL CREATE SEQUENCE statement */
0127: public static final int CREATE_SEQUENCE = 23;
0128: /** An SQL CREATE FUNCTION statement */
0129: public static final int CREATE_FUNCTION = 26;
0130: /** An SQL CREATE PROCEDURE statement */
0131: public static final int CREATE_PROCEDURE = 25;
0132: /** An SQL GRANT statement */
0133: public static final int GRANT = 27;
0134: /** An SQL GRANT statement */
0135: public static final int CREATE_SYNONYM = 28;
0136: /** An unknown SQL statement */
0137: public static final int UNKNOWN = 99;
0138: /** A commit statement */
0139: public static final int COMMIT = 12;
0140: /** A rollback statement */
0141: public static final int ROLLBACK = 13;
0142: /** A connect statement */
0143: public static final int CONNECT = 14;
0144:
0145: /** <p>Creates a new instance */
0146: public QuerySender() {
0147: this (null, false);
0148: }
0149:
0150: /**
0151: * Creates a new instance with the specified connection
0152: * properties object as the connection provider and a keep flag
0153: * that determines whether connections are retained or closed between
0154: * requests.
0155: *
0156: * @param the connection properties object
0157: * @param whether the connection should be kept between requests
0158: */
0159: public QuerySender(DatabaseConnection databaseConnection) {
0160: this (databaseConnection, false);
0161: }
0162:
0163: /**
0164: * Creates a new instance with the specified connection
0165: * properties object as the connection provider and a keep flag
0166: * that determines whether connections are retained or closed between
0167: * requests.
0168: *
0169: * @param the connection properties object
0170: * @param whether the connection should be kept between requests
0171: */
0172: public QuerySender(DatabaseConnection databaseConnection,
0173: boolean keepAlive) {
0174: this .keepAlive = keepAlive;
0175: this .databaseConnection = databaseConnection;
0176: maxUseCount = ConnectionManager.getMaxUseCount();
0177: statementResult = new SqlStatementResult();
0178: }
0179:
0180: /** <p>Retrieves a description of the specified table using
0181: * the connection's <code>DatabaseMetaData</code> object
0182: * and the method <code>getColumns(...)</code>.
0183: *
0184: * @param the table name to describe
0185: * @return the query result
0186: */
0187: public SqlStatementResult getTableDescription(String tableName)
0188: throws Exception {
0189:
0190: if (!prepared()) {
0191: return statementResult;
0192: }
0193:
0194: try {
0195:
0196: /* -------------------------------------------------
0197: * Database meta data values are case-sensitive.
0198: * search for a match and use as returned from dmd.
0199: * -------------------------------------------------
0200: */
0201:
0202: String _tableName = null;
0203: String _schemaName = null;
0204: String schemaName = databaseConnection.getUserName();
0205:
0206: boolean valueFound = false;
0207: DatabaseMetaData dmd = conn.getMetaData();
0208: ResultSet rs = dmd.getSchemas();
0209:
0210: while (rs.next()) {
0211: _schemaName = rs.getString(1);
0212: if (_schemaName.equalsIgnoreCase(schemaName)) {
0213: valueFound = true;
0214: break;
0215: }
0216: }
0217: rs.close();
0218:
0219: if (!valueFound) {
0220: _schemaName = null;
0221: }
0222:
0223: valueFound = false;
0224: rs = dmd.getTables(null, _schemaName, null, null);
0225:
0226: while (rs.next()) {
0227: _tableName = rs.getString(3);
0228: if (_tableName.equalsIgnoreCase(tableName)) {
0229: valueFound = true;
0230: break;
0231: }
0232: }
0233: rs.close();
0234:
0235: if (!valueFound) {
0236: statementResult.setMessage("Invalid table name");
0237: } else {
0238: rs = dmd
0239: .getColumns(null, _schemaName, _tableName, null);
0240: statementResult.setResultSet(rs);
0241: }
0242:
0243: } catch (SQLException e) {
0244: statementResult.setSqlException(e);
0245: if (stmnt != null) {
0246: stmnt.close();
0247: }
0248: closeConnection(conn);
0249: } catch (OutOfMemoryError e) {
0250: statementResult.setMessage(e.getMessage());
0251: releaseResources();
0252: }
0253: return statementResult;
0254: }
0255:
0256: private boolean prepared() throws SQLException {
0257:
0258: if (databaseConnection == null
0259: || !databaseConnection.isConnected()) {
0260: statementResult.setMessage("Not Connected");
0261: return false;
0262: }
0263:
0264: // check the connection is valid
0265: if (conn == null) {
0266: try {
0267: conn = ConnectionManager
0268: .getConnection(databaseConnection);
0269: if (keepAlive) {
0270: conn.setAutoCommit(commitMode);
0271: }
0272: useCount = 0;
0273: } catch (DataSourceException e) {
0274: handleDataSourceException(e);
0275: }
0276: }
0277: // check its still open
0278: else if (conn.isClosed()) {
0279: statementResult.setMessage("Connection closed.");
0280: return false;
0281: }
0282:
0283: statementResult.reset();
0284: if (conn != null) { // still null?
0285: conn.clearWarnings();
0286: } else {
0287: statementResult.setMessage("Connection closed.");
0288: return false;
0289: }
0290: return true;
0291: }
0292:
0293: /** <p>Executes the specified query (SELECT) and returns
0294: * a <code>ResultSet</code> object from this query.
0295: * <p>If an exception occurs, null is returned and
0296: * the relevant error message, if available, assigned
0297: * to this object for retrieval.
0298: *
0299: * @param the SQL query to execute
0300: * @return the query result
0301: */
0302: public SqlStatementResult getResultSet(String query)
0303: throws SQLException {
0304:
0305: if (!prepared()) {
0306: return statementResult;
0307: }
0308:
0309: stmnt = conn.createStatement();
0310:
0311: try {
0312: ResultSet rs = stmnt.executeQuery(query);
0313: statementResult.setResultSet(rs);
0314: useCount++;
0315: return statementResult;
0316: } catch (SQLException e) {
0317: statementResult.setSqlException(e);
0318: if (stmnt != null) {
0319: stmnt.close();
0320: }
0321: closeConnection(conn);
0322: return statementResult;
0323: }
0324:
0325: }
0326:
0327: /** <p>Executes the specified procedure.
0328: *
0329: * @param the SQL procedure to execute
0330: * @return the query result
0331: */
0332: public SqlStatementResult executeProcedure(DatabaseProcedure proc)
0333: throws Exception {
0334:
0335: if (!prepared()) {
0336: return statementResult;
0337: }
0338:
0339: ProcedureParameter[] param = proc.getParameters();
0340: Arrays.sort(param, new ParameterSorter());
0341:
0342: String procQuery = null;
0343: boolean hasOut = false;
0344: boolean hasParameters = (param != null && param.length > 0);
0345:
0346: List<ProcedureParameter> outs = null;
0347: List<ProcedureParameter> ins = null;
0348:
0349: if (hasParameters) {
0350:
0351: // split the params into ins and outs
0352: outs = new ArrayList<ProcedureParameter>();
0353: ins = new ArrayList<ProcedureParameter>();
0354:
0355: int type = -1;
0356: for (int i = 0; i < param.length; i++) {
0357: type = param[i].getType();
0358: if (type == DatabaseMetaData.procedureColumnIn
0359: || type == DatabaseMetaData.procedureColumnInOut) {
0360:
0361: // add to the ins list
0362: ins.add(param[i]);
0363:
0364: } else if (type == DatabaseMetaData.procedureColumnOut
0365: || type == DatabaseMetaData.procedureColumnResult
0366: || type == DatabaseMetaData.procedureColumnReturn
0367: || type == DatabaseMetaData.procedureColumnUnknown
0368: || type == DatabaseMetaData.procedureColumnInOut) {
0369:
0370: // add to the outs list
0371: outs.add(param[i]);
0372:
0373: }
0374: }
0375:
0376: char QUESTION_MARK = '?';
0377: String COMMA = ", ";
0378:
0379: // init the string buffer
0380: StringBuffer sb = new StringBuffer("{ ");
0381:
0382: // build the out params place holders
0383: for (int i = 0, n = outs.size(); i < n; i++) {
0384: sb.append(QUESTION_MARK);
0385: if (i < n - 1) {
0386: sb.append(COMMA);
0387: }
0388: }
0389:
0390: sb.append(" = call ");
0391:
0392: if (proc.getSchema() != null) {
0393: sb.append(proc.getSchema()).append('.');
0394: }
0395:
0396: sb.append(proc.getName()).append("( ");
0397:
0398: // build the ins params place holders
0399: for (int i = 0, n = ins.size(); i < n; i++) {
0400: sb.append(QUESTION_MARK);
0401: if (i < n - 1) {
0402: sb.append(COMMA);
0403: }
0404: }
0405:
0406: sb.append(" ) }");
0407:
0408: // determine if we have out params
0409: hasOut = !(outs.isEmpty());
0410: procQuery = sb.toString();
0411: } else {
0412: StringBuffer sb = new StringBuffer();
0413: sb.append("{ call ");
0414:
0415: if (proc.getSchema() != null) {
0416: sb.append(proc.getSchema()).append('.');
0417: }
0418:
0419: sb.append(proc.getName()).append("( ) }");
0420:
0421: procQuery = sb.toString();
0422: }
0423:
0424: //Log.debug(procQuery);
0425:
0426: // null value literal
0427: String NULL = "null";
0428:
0429: // whether a result set is returned
0430: boolean isResultSet = false;
0431:
0432: // clear any warnings
0433: conn.clearWarnings();
0434:
0435: Log.info("Executing: " + procQuery);
0436:
0437: CallableStatement cstmnt = null;
0438: try {
0439: // prepare the statement
0440: cstmnt = conn.prepareCall(procQuery);
0441: stmnt = cstmnt;
0442: } catch (SQLException e) {
0443: if (Log.isDebugEnabled()) {
0444: e.printStackTrace();
0445: }
0446: statementResult.setSqlException(e);
0447: return statementResult;
0448: }
0449:
0450: // check if we are passing parameters
0451: if (hasParameters) {
0452: // the parameter index counter
0453: int index = 1;
0454:
0455: // the java.sql.Type value
0456: int dataType = -1;
0457:
0458: // the parameter input value
0459: String value = null;
0460:
0461: // register the out params
0462: for (int i = 0, n = outs.size(); i < n; i++) {
0463: //Log.debug("setting out at index: " + index);
0464: cstmnt.registerOutParameter(index, outs.get(i)
0465: .getDataType());
0466: index++;
0467: }
0468:
0469: try {
0470:
0471: // register the in params
0472: for (int i = 0, n = ins.size(); i < n; i++) {
0473: value = ins.get(i).getValue();
0474: dataType = ins.get(i).getDataType();
0475:
0476: if (MiscUtils.isNull(value)
0477: || value.equalsIgnoreCase(NULL)) {
0478: cstmnt.setNull(index, dataType);
0479: } else {
0480:
0481: switch (dataType) {
0482:
0483: case Types.TINYINT:
0484: byte _byte = Byte.valueOf(value)
0485: .byteValue();
0486: cstmnt.setShort(index, _byte);
0487: break;
0488:
0489: case Types.SMALLINT:
0490: short _short = Short.valueOf(value)
0491: .shortValue();
0492: cstmnt.setShort(index, _short);
0493: break;
0494:
0495: case Types.CHAR:
0496: case Types.VARCHAR:
0497: case Types.LONGVARCHAR:
0498: cstmnt.setString(index, value);
0499: break;
0500:
0501: case Types.BIT:
0502: case Types.BOOLEAN:
0503: boolean _boolean = Boolean.valueOf(value)
0504: .booleanValue();
0505: cstmnt.setBoolean(index, _boolean);
0506: break;
0507:
0508: case Types.BIGINT:
0509: long _long = Long.valueOf(value)
0510: .longValue();
0511: cstmnt.setLong(index, _long);
0512: break;
0513:
0514: case Types.REAL:
0515: float _float = Float.valueOf(value)
0516: .floatValue();
0517: cstmnt.setFloat(index, _float);
0518: break;
0519:
0520: case Types.INTEGER:
0521: int _int = Integer.valueOf(value)
0522: .intValue();
0523: cstmnt.setInt(index, _int);
0524: break;
0525:
0526: case Types.DECIMAL:
0527: case Types.NUMERIC:
0528: cstmnt.setBigDecimal(index, new BigDecimal(
0529: value));
0530: break;
0531: /*
0532: case Types.DATE:
0533: case Types.TIMESTAMP:
0534: case Types.TIME:
0535: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value));
0536: */
0537: case Types.FLOAT:
0538: case Types.DOUBLE:
0539: double _double = Double.valueOf(value)
0540: .doubleValue();
0541: cstmnt.setDouble(index, _double);
0542: break;
0543:
0544: }
0545:
0546: }
0547:
0548: // increment the index
0549: index++;
0550: }
0551:
0552: }
0553: // catch formatting exceptions
0554: catch (Exception e) {
0555: statementResult.setOtherErrorMessage(e.getClass()
0556: .getName()
0557: + ": " + e.getMessage());
0558: return statementResult;
0559: }
0560:
0561: }
0562:
0563: try {
0564: cstmnt.clearWarnings();
0565: boolean hasResultSet = cstmnt.execute();
0566: Hashtable results = new Hashtable();
0567:
0568: if (hasOut) {
0569: // incrementing index
0570: int index = 1;
0571:
0572: // return value from each registered out
0573: String returnValue = null;
0574:
0575: for (int i = 0; i < param.length; i++) {
0576:
0577: int type = param[i].getType();
0578: int dataType = param[i].getDataType();
0579:
0580: if (type == DatabaseMetaData.procedureColumnOut
0581: || type == DatabaseMetaData.procedureColumnResult
0582: || type == DatabaseMetaData.procedureColumnReturn
0583: || type == DatabaseMetaData.procedureColumnUnknown
0584: || type == DatabaseMetaData.procedureColumnInOut) {
0585:
0586: switch (dataType) {
0587:
0588: case Types.TINYINT:
0589: returnValue = Byte.toString(cstmnt
0590: .getByte(index));
0591: break;
0592:
0593: case Types.SMALLINT:
0594: returnValue = Short.toString(cstmnt
0595: .getShort(index));
0596: break;
0597:
0598: case Types.CHAR:
0599: case Types.VARCHAR:
0600: case Types.LONGVARCHAR:
0601: cstmnt.getString(index);
0602: break;
0603:
0604: case Types.BIT:
0605: case Types.BOOLEAN:
0606: returnValue = Boolean.toString(cstmnt
0607: .getBoolean(index));
0608: break;
0609:
0610: case Types.INTEGER:
0611: returnValue = Integer.toString(cstmnt
0612: .getInt(index));
0613: break;
0614:
0615: case Types.BIGINT:
0616: returnValue = Long.toString(cstmnt
0617: .getLong(index));
0618: break;
0619:
0620: case Types.REAL:
0621: returnValue = Float.toString(cstmnt
0622: .getFloat(index));
0623: break;
0624:
0625: case Types.DECIMAL:
0626: case Types.NUMERIC:
0627: returnValue = cstmnt.getBigDecimal(index)
0628: .toString();
0629: break;
0630:
0631: case Types.DATE:
0632: case Types.TIMESTAMP:
0633: case Types.TIME:
0634: returnValue = cstmnt.getDate(index)
0635: .toString();
0636: break;
0637:
0638: case Types.FLOAT:
0639: case Types.DOUBLE:
0640: returnValue = Double.toString(cstmnt
0641: .getDouble(index));
0642: break;
0643:
0644: }
0645:
0646: if (returnValue == null) {
0647: returnValue = "NULL";
0648: }
0649: results.put(param[i].getName(), returnValue);
0650: index++;
0651: }
0652:
0653: }
0654:
0655: }
0656:
0657: if (!hasResultSet) {
0658: statementResult.setUpdateCount(cstmnt.getUpdateCount());
0659: } else {
0660: statementResult.setResultSet(cstmnt.getResultSet());
0661: }
0662:
0663: useCount++;
0664: statementResult.setOtherResult(results);
0665: } catch (SQLException e) {
0666: if (Log.isDebugEnabled()) {
0667: e.printStackTrace();
0668: }
0669: statementResult.setSqlException(e);
0670: } catch (Exception e) {
0671: statementResult.setMessage(e.getMessage());
0672: } finally {
0673: if (cstmnt != null) {
0674: cstmnt.close();
0675: }
0676: cstmnt = null;
0677: closeConnection(conn);
0678: }
0679: return statementResult;
0680: }
0681:
0682: /** <p>Executes the specified procedure and returns
0683: * a <code>ResultSet</code> object from this query.
0684: * <p>If an exception occurs, null is returned and
0685: * the relevant error message, if available, assigned
0686: * to this object for retrieval.
0687: *
0688: * @param the SQL procedure to execute
0689: * @return the query result
0690: */
0691: public SqlStatementResult executeProcedure(String query)
0692: throws Exception {
0693:
0694: if (!prepared()) {
0695: return statementResult;
0696: }
0697:
0698: //Log.debug("query " + query);
0699:
0700: String execString = "EXECUTE ";
0701: String callString = "CALL ";
0702:
0703: int nameIndex = -1;
0704: int index = query.toUpperCase().indexOf(execString);
0705:
0706: // check if EXECUTE was entered
0707: if (index != -1) {
0708: nameIndex = execString.length();
0709: } else { // must be CALL
0710: nameIndex = callString.length();
0711: }
0712:
0713: // the procedure name
0714: String procedureName = null;
0715:
0716: // check for input brackets
0717: boolean possibleParams = false;
0718: index = query.indexOf("(", nameIndex);
0719: if (index != -1) {
0720: possibleParams = true;
0721: procedureName = query.substring(nameIndex, index);
0722: } else {
0723: procedureName = query.substring(nameIndex);
0724: }
0725:
0726: //Log.debug("name: " + procedureName);
0727:
0728: if (metaData == null) {
0729: metaData = new MetaDataValues(databaseConnection, false);
0730: } else {
0731: metaData.setDatabaseConnection(databaseConnection);
0732: }
0733:
0734: DatabaseProcedure procedure = metaData.getProcedureColumns(
0735: null, null, procedureName);
0736:
0737: if (procedure != null) {
0738:
0739: if (possibleParams) {
0740: String params = query.substring(index + 1, query
0741: .indexOf(")"));
0742: if (!MiscUtils.isNull(params)) {
0743:
0744: // check that the proc accepts params
0745: if (!procedure.hasParameters()) {
0746: statementResult
0747: .setSqlException(new SQLException(
0748: "Procedure call was invalid"));
0749: return statementResult;
0750: }
0751:
0752: int paramIndex = 0;
0753: ProcedureParameter[] parameters = procedure
0754: .getParameters();
0755:
0756: // extract the parameters
0757: StringTokenizer st = new StringTokenizer(params,
0758: ",");
0759: while (st.hasMoreTokens()) {
0760: String value = st.nextToken().trim();
0761:
0762: // check applicable param
0763: for (int i = paramIndex; i < parameters.length; i++) {
0764: paramIndex++;
0765:
0766: int type = parameters[i].getType();
0767: if (type == DatabaseMetaData.procedureColumnIn
0768: || type == DatabaseMetaData.procedureColumnInOut) {
0769:
0770: // check the data type and remove quotes if char
0771: int dataType = parameters[i]
0772: .getDataType();
0773: if (dataType == Types.CHAR
0774: || dataType == Types.VARCHAR
0775: || dataType == Types.LONGVARCHAR) {
0776:
0777: if (value.indexOf("'") != -1) {
0778: // assuming quotes at start and end
0779: value = value.substring(1,
0780: value.length() - 1);
0781: }
0782:
0783: }
0784:
0785: parameters[i].setValue(value);
0786: break;
0787: }
0788: }
0789:
0790: }
0791:
0792: }
0793: }
0794:
0795: // execute the procedure
0796: return executeProcedure(procedure);
0797: } else {
0798: statementResult.setSqlException(new SQLException(
0799: "Procedure or Function name specified is invalid"));
0800: return statementResult;
0801: }
0802:
0803: /*
0804: StringBuffer sb = new StringBuffer("{ call ");
0805: int indexOfExec = query.indexOf(execString);
0806:
0807: // check if EXECUTE was entered
0808: if (indexOfExec == -1) {
0809: sb.append(query.substring(
0810: query.indexOf(callString) + 5, query.length()));
0811: }
0812: else {
0813: sb.append(query.substring(indexOfExec + 8, query.length()));
0814: }
0815:
0816: sb.append(" }");
0817: cstmnt = conn.prepareCall(sb.toString());
0818: boolean isResultSet = false;
0819:
0820: try {
0821: cstmnt.setEscapeProcessing(false);
0822: cstmnt.clearWarnings();
0823:
0824: isResultSet = cstmnt.execute();
0825:
0826: if (isResultSet) {
0827: ResultSet rs = cstmnt.getResultSet();
0828: statementResult.setResultSet(rs);
0829: }
0830: else {
0831: int result = cstmnt.getUpdateCount();
0832: if (result == -1) {
0833: result = -10000;
0834: }
0835: statementResult.setUpdateCount(result);
0836: }
0837:
0838: useCount++;
0839: statementResult.setSqlWarning(cstmnt.getWarnings());
0840:
0841: }
0842: catch (SQLException e) {
0843: statementResult.setSqlException(e);
0844: }
0845: finally {
0846: if (cstmnt != null) {
0847: cstmnt.close();
0848: }
0849: cstmnt = null;
0850: closeConnection(conn);
0851: }
0852: return statementResult;
0853: */
0854: }
0855:
0856: public SqlStatementResult executeQuery(String query)
0857: throws Exception {
0858: return executeQuery(getQueryType(query), query);
0859: }
0860:
0861: public SqlStatementResult executeQuery(int type, String query)
0862: throws Exception {
0863: statementResult.setType(type);
0864:
0865: switch (type) {
0866: case SELECT:
0867: case EXPLAIN:
0868: return getResultSet(query);
0869: case INSERT:
0870: case UPDATE:
0871: case DELETE:
0872: case DROP_TABLE:
0873: case CREATE_TABLE:
0874: case ALTER_TABLE:
0875: case CREATE_SEQUENCE:
0876: case CREATE_FUNCTION:
0877: case CREATE_PROCEDURE:
0878: case GRANT:
0879: case CREATE_SYNONYM:
0880: return updateRecords(query);
0881:
0882: case UNKNOWN:
0883: return execute(query);
0884:
0885: case DESCRIBE:
0886: int tableNameIndex = query.indexOf(" ");
0887: return getTableDescription(query
0888: .substring(tableNameIndex + 1));
0889:
0890: case EXECUTE:
0891: return executeProcedure(query);
0892:
0893: case COMMIT:
0894: return commitLast(true);
0895:
0896: case ROLLBACK:
0897: return commitLast(false);
0898:
0899: /*
0900: case CONNECT:
0901: return establishConnection(query.toUpperCase());
0902: */
0903: }
0904: return statementResult;
0905: }
0906:
0907: public SqlStatementResult execute(String query) throws Exception {
0908: return execute(query, true);
0909: }
0910:
0911: public SqlStatementResult execute(String query,
0912: boolean enableEscapes) throws SQLException {
0913:
0914: if (!prepared()) {
0915: return statementResult;
0916: }
0917:
0918: stmnt = conn.createStatement();
0919: boolean isResultSet = false;
0920:
0921: try {
0922: stmnt.setEscapeProcessing(enableEscapes);
0923: isResultSet = stmnt.execute(query);
0924:
0925: if (isResultSet) {
0926: ResultSet rs = stmnt.getResultSet();
0927: statementResult.setResultSet(rs);
0928: } else {
0929: int updateCount = stmnt.getUpdateCount();
0930:
0931: if (updateCount == -1)
0932: updateCount = -10000;
0933:
0934: statementResult.setUpdateCount(updateCount);
0935: }
0936:
0937: useCount++;
0938: statementResult.setSqlWarning(stmnt.getWarnings());
0939: return statementResult;
0940: } catch (SQLException e) {
0941: statementResult.setSqlException(e);
0942: }
0943: /*
0944: finally {
0945:
0946: if (stmnt != null) {
0947: stmnt.close();
0948: stmnt = null;
0949: }
0950: closeConnection(conn);
0951:
0952: }
0953: */
0954: return statementResult;
0955:
0956: }
0957:
0958: /** <p>Executes the specified query and returns 0 if this
0959: * executes successfully. If an exception occurs, -1 is
0960: * returned and the relevant error message, if available,
0961: * assigned to this object for retrieval. This will
0962: * typically be called for a CREATE PROCEDURE/FUNCTION
0963: * call.
0964: *
0965: * @param the SQL query to execute
0966: * @return the number of rows affected
0967: */
0968: public SqlStatementResult createProcedure(String query)
0969: throws Exception {
0970:
0971: if (!prepared()) {
0972: return statementResult;
0973: }
0974:
0975: stmnt = conn.createStatement();
0976:
0977: try {
0978: stmnt.clearWarnings();
0979: stmnt.setEscapeProcessing(false);
0980: boolean isResultSet = stmnt.execute(query);
0981:
0982: if (!isResultSet) {
0983: int updateCount = stmnt.getUpdateCount();
0984:
0985: if (updateCount == -1)
0986: updateCount = -10000;
0987:
0988: statementResult.setUpdateCount(updateCount);
0989: } else { // should never be a result set
0990: ResultSet rs = stmnt.getResultSet();
0991: statementResult.setResultSet(rs);
0992: }
0993:
0994: useCount++;
0995: statementResult.setSqlWarning(stmnt.getWarnings());
0996: } catch (SQLException e) {
0997: statementResult.setSqlException(e);
0998: } finally {
0999: if (stmnt != null) {
1000: stmnt.close();
1001: }
1002: closeConnection(conn);
1003: }
1004:
1005: return statementResult;
1006: }
1007:
1008: /** <p>Executes the specified query and returns
1009: * the number of rows affected by this query.
1010: * <p>If an exception occurs, -1 is returned and
1011: * the relevant error message, if available, assigned
1012: * to this object for retrieval.
1013: *
1014: * @param the SQL query to execute
1015: * @return the number of rows affected
1016: */
1017: public SqlStatementResult updateRecords(String query)
1018: throws SQLException {
1019:
1020: if (!prepared()) {
1021: return statementResult;
1022: }
1023:
1024: stmnt = conn.createStatement();
1025:
1026: try {
1027: int result = stmnt.executeUpdate(query);
1028: statementResult.setUpdateCount(result);
1029: useCount++;
1030: } catch (SQLException e) {
1031: statementResult.setSqlException(e);
1032: } finally {
1033: if (stmnt != null) {
1034: stmnt.close();
1035: }
1036: closeConnection(conn);
1037: }
1038:
1039: return statementResult;
1040:
1041: }
1042:
1043: /*
1044: public SqlStatementResult establishConnection(String query) {
1045: statementResult.reset();
1046: String connectString = "CONNECT ";
1047: int index = query.indexOf("CONNECT ") + connectString.length();
1048: String name = query.substring(index).trim();
1049: DatabaseConnection dc = ConnectionProperties.getDatabaseConnection(name, true);
1050:
1051: if (dc == null) {
1052: statementResult.setMessage("The connection does not exist");
1053: }
1054:
1055: return statementResult;
1056: }
1057: */
1058:
1059: /** <p>Commits or rolls back the last executed
1060: * SQL query or queries.
1061: *
1062: * @param true to commit - false to roll back
1063: */
1064: public SqlStatementResult commitLast(boolean commit) {
1065: try {
1066: statementResult.reset();
1067: statementResult.setUpdateCount(0);
1068:
1069: if (commit) {
1070: conn.commit();
1071: Log.info("Commit complete.");
1072: statementResult.setMessage("Commit complete.");
1073: closeMaxedConn();
1074: } else {
1075: conn.rollback();
1076: Log.info("Rollback complete.");
1077: statementResult.setMessage("Rollback complete.");
1078: closeMaxedConn();
1079: }
1080:
1081: } catch (SQLException e) {
1082: if (Log.isDebugEnabled()) {
1083: e.printStackTrace();
1084: }
1085: statementResult.setSqlException(e);
1086: }
1087: return statementResult;
1088:
1089: }
1090:
1091: /** <p>Closes a connection which has reached its
1092: * maximum use count and retrieves a new one from
1093: * the <code>DBConnection</code> object.
1094: */
1095: private void closeMaxedConn() throws SQLException {
1096: if (keepAlive && useCount > maxUseCount) {
1097: destroyConnection();
1098: }
1099: }
1100:
1101: /**
1102: * Destroys the open connection.
1103: */
1104: public void destroyConnection() throws SQLException {
1105: try {
1106: ConnectionManager.close(databaseConnection, conn);
1107: conn = null;
1108: // prepared();
1109: // useCount = 0;
1110: } catch (DataSourceException e) {
1111: handleDataSourceException(e);
1112: }
1113: }
1114:
1115: /** <p>Sets the connection's commit mode to the
1116: * specified value.
1117: *
1118: * @param true for auto-commit, false otherwise
1119: */
1120: public void setCommitMode(boolean commitMode) {
1121: this .commitMode = commitMode;
1122: //Log.debug("commitMode: " + commitMode);
1123: try {
1124: if (keepAlive && (conn != null && !conn.isClosed())) {
1125: conn.setAutoCommit(commitMode);
1126: }
1127: } catch (SQLException e) {
1128: if (Log.isDebugEnabled()) {
1129: e.printStackTrace();
1130: }
1131: }
1132: }
1133:
1134: /**
1135: * Cancels the current SQL statement being executed.
1136: */
1137: public void cancelCurrentStatement() {
1138: Log.info("Attempting to cancel the current statement...");
1139: try {
1140: stmnt.cancel();
1141: stmnt.close();
1142: stmnt = null;
1143: Log.info("Statement cancelled");
1144: closeConnection(conn);
1145: statementResult.setMessage("Statement cancelled.");
1146: } catch (SQLException e) {
1147: if (Log.isDebugEnabled()) {
1148: e.printStackTrace();
1149: }
1150: }
1151: }
1152:
1153: /**
1154: * Determines the type of query from the specified query.
1155: *
1156: * @param the SQL query to analyse
1157: * @result the type of SQL query
1158: */
1159: public int getQueryType(String query) {
1160: int type = -1;
1161:
1162: query = query.toUpperCase();
1163:
1164: if (query.indexOf("CREATE TABLE ") == 0)
1165: type = CREATE_TABLE;
1166:
1167: else if (query.indexOf("CREATE ") == 0
1168: && (query.indexOf("PROCEDURE ") != -1 || query
1169: .indexOf("PACKAGE ") != -1))
1170: type = CREATE_PROCEDURE;
1171:
1172: else if (query.indexOf("CREATE ") == 0
1173: && query.indexOf("FUNCTION ") != -1)
1174: type = CREATE_FUNCTION;
1175:
1176: else if (query.indexOf("CONNECT ") == 0)
1177: type = CONNECT;
1178:
1179: else if (query.indexOf("INSERT ") == 0)
1180: type = INSERT;
1181:
1182: else if (query.indexOf("UPDATE ") == 0)
1183: type = UPDATE;
1184:
1185: else if (query.indexOf("DELETE ") == 0)
1186: type = DELETE;
1187:
1188: else if (query.indexOf("DROP TABLE ") == 0)
1189: type = DROP_TABLE;
1190:
1191: else if (query.indexOf("ALTER TABLE ") == 0)
1192: type = ALTER_TABLE;
1193:
1194: else if (query.indexOf("CREATE SEQUENCE ") == 0)
1195: type = CREATE_SEQUENCE;
1196:
1197: else if (query.indexOf("CREATE SYNONYM ") == 0)
1198: type = CREATE_SYNONYM;
1199:
1200: else if (query.indexOf("GRANT ") == 0)
1201: type = GRANT;
1202:
1203: else if (query.indexOf("EXECUTE ") == 0
1204: || query.indexOf("CALL ") == 0)
1205: type = EXECUTE;
1206:
1207: else if (query.indexOf("COMMIT") == 0)
1208: type = COMMIT;
1209:
1210: else if (query.indexOf("ROLLBACK") == 0)
1211: type = ROLLBACK;
1212:
1213: else if (query.indexOf("SELECT ") == 0)
1214: type = SELECT;
1215:
1216: else if (query.indexOf("EXPLAIN ") == 0)
1217: type = EXPLAIN;
1218:
1219: else if (query.indexOf("DESC ") == 0
1220: || query.indexOf("DESCRIBE ") == 0)
1221: type = DESCRIBE;
1222:
1223: else
1224: type = UNKNOWN;
1225:
1226: return type;
1227: }
1228:
1229: /** <p>Closes the specified database connection.
1230: * <p>If the specified connection is NULL, the open
1231: * connection held by this class is closed.
1232: *
1233: * @param the connection to close
1234: */
1235: public void closeConnection(Connection c) throws SQLException {
1236: try {
1237: // if this not the connection assigned to this object
1238: if (c != null && c != conn) {
1239: c.close();
1240: c = null;
1241: } else { // otherwise proceed to close
1242: closeConnection();
1243: }
1244: /*
1245: if (!keepAlive) {
1246: c.close();
1247: }
1248: else if (c == null) {
1249: if (conn != null) {
1250: conn.close();
1251: }
1252: conn = null;
1253: }
1254: */
1255: } catch (SQLException e) {
1256: if (Log.isDebugEnabled()) {
1257: e.printStackTrace();
1258: }
1259: }
1260: }
1261:
1262: /**
1263: * Close the database connection of this object.
1264: * If destroy is true, the connection will be
1265: * closed using connection.close(). Otherwise,
1266: * the value of keepAlive for this instance will
1267: * be respected.
1268: *
1269: * @param whether to call close() on the connection object
1270: */
1271: public void closeConnection(boolean destroy) {
1272: if (destroy) {
1273: try {
1274: if (conn != null) {
1275: conn.close();
1276: }
1277: conn = null;
1278: } catch (SQLException e) {
1279: if (Log.isDebugEnabled()) {
1280: e.printStackTrace();
1281: }
1282: }
1283: }
1284: }
1285:
1286: /**
1287: * Closes the database connection of this object.
1288: */
1289: public void closeConnection() throws SQLException {
1290: // if set to keep the connection open
1291: // for this instance - return
1292: if (keepAlive) {
1293: return;
1294: }
1295: // otherwise close it
1296: closeConnection(true);
1297: }
1298:
1299: /**
1300: * Indicates a connection has been closed.
1301: *
1302: * @param the connection thats been closed
1303: */
1304: public void disconnected(DatabaseConnection dc) {
1305: if (databaseConnection == dc) {
1306: closeConnection(true);
1307: databaseConnection = null;
1308: }
1309: }
1310:
1311: /**
1312: * Handles a DataSourceException by rethrowing as a
1313: * SQLException.
1314: */
1315: private void handleDataSourceException(DataSourceException e)
1316: throws SQLException {
1317: if (e.getCause() instanceof SQLException) {
1318: throw (SQLException) e.getCause();
1319: } else {
1320: throw new SQLException(e.getMessage());
1321: }
1322: }
1323:
1324: /** <p>Releases database resources held by this class. */
1325: public void releaseResources() {
1326:
1327: //Log.debug("releaseResources: keepAlive - " + keepAlive);
1328:
1329: try {
1330: if (stmnt != null) {
1331: stmnt.close();
1332: }
1333: stmnt = null;
1334:
1335: if (!keepAlive) {
1336: if (conn != null) {
1337: conn.close();
1338: }
1339: conn = null;
1340: }
1341:
1342: /*
1343: if (keepAlive) {
1344: conn = dbConn.getConnection();
1345: setCommitMode(commitMode);
1346: }
1347: */
1348: } catch (SQLException e) {
1349: if (Log.isDebugEnabled()) {
1350: e.printStackTrace();
1351: }
1352: }
1353: }
1354:
1355: public void releaseStatements() {
1356: try {
1357: if (stmnt != null) {
1358: stmnt.close();
1359: }
1360: stmnt = null;
1361: closeConnection(conn);
1362: } catch (Exception e) {
1363: if (Log.isDebugEnabled()) {
1364: e.printStackTrace();
1365: }
1366: }
1367: }
1368:
1369: public DatabaseConnection getDatabaseConnection() {
1370: return databaseConnection;
1371: }
1372:
1373: public void setDatabaseConnection(
1374: DatabaseConnection _databaseConnection) {
1375: if (databaseConnection != _databaseConnection) {
1376: try {
1377: // close the current connection
1378: if (databaseConnection != null && conn != null) {
1379: ConnectionManager.close(databaseConnection, conn);
1380: conn = null;
1381: }
1382: // reassign the connection
1383: databaseConnection = _databaseConnection;
1384: prepared();
1385: useCount = 0;
1386: } catch (DataSourceException e) {
1387: } catch (SQLException e) {
1388: if (Log.isDebugEnabled()) {
1389: e.printStackTrace();
1390: }
1391: }
1392: }
1393: }
1394:
1395: }
|