0001: /*
0002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
0003: C/Oņa, 107 1š2 28050 Madrid (Spain)
0004:
0005: Redistribution and use in source and binary forms, with or without
0006: modification, are permitted provided that the following conditions
0007: are met:
0008:
0009: 1. Redistributions of source code must retain the above copyright
0010: notice, this list of conditions and the following disclaimer.
0011:
0012: 2. The end-user documentation included with the redistribution,
0013: if any, must include the following acknowledgment:
0014: "This product includes software parts from hipergate
0015: (http://www.hipergate.org/)."
0016: Alternately, this acknowledgment may appear in the software itself,
0017: if and wherever such third-party acknowledgments normally appear.
0018:
0019: 3. The name hipergate must not be used to endorse or promote products
0020: derived from this software without prior written permission.
0021: Products derived from this software may not be called hipergate,
0022: nor may hipergate appear in their name, without prior written
0023: permission.
0024:
0025: This library is distributed in the hope that it will be useful,
0026: but WITHOUT ANY WARRANTY; without even the implied warranty of
0027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
0028:
0029: You should have received a copy of hipergate License with this code;
0030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
0031: */
0032:
0033: package com.knowgate.dataobjs;
0034:
0035: import java.security.AccessControlException;
0036:
0037: import java.lang.System;
0038: import java.util.HashMap;
0039: import java.util.Iterator;
0040:
0041: import java.sql.DriverManager;
0042: import java.sql.Connection;
0043: import java.sql.ResultSet;
0044: import java.sql.DatabaseMetaData;
0045: import java.sql.CallableStatement;
0046: import java.sql.Statement;
0047: import java.sql.SQLException;
0048: import java.sql.Timestamp;
0049:
0050: import com.knowgate.debug.DebugFile;
0051: import com.knowgate.misc.Environment;
0052: import com.knowgate.misc.Gadgets;
0053: import com.knowgate.jdc.JDCConnection;
0054: import com.knowgate.jdc.JDCConnectionPool;
0055:
0056: import java.beans.Beans;
0057:
0058: /**
0059: * <p>Singleton object for database binding.</p>
0060: * @author Sergio Montoro Ten
0061: * @version 3.0
0062: */
0063:
0064: public class DBBind extends Beans {
0065:
0066: // *****************
0067: // Private Variables
0068:
0069: private JDCConnectionPool oConnPool;
0070: private String sProfileName;
0071: private String sDatabaseProductName;
0072: private int iDatabaseProductId;
0073: private Exception oConnectXcpt;
0074:
0075: private static HashMap oGlobalTableMap;
0076:
0077: private HashMap oTableMap;
0078:
0079: private static final String VERSION = "3.0.6";
0080:
0081: // ***********
0082: // Constructor
0083:
0084: /**
0085: * <p>Create DBBind.</p>
0086: * Read database connection properties from hipergate.cnf.<br>
0087: * This file must be placed at the directory pointed by KNOWGATE_PROFILES
0088: * environment variable.<br>
0089: * By defualt hipergate.cnf is placed on C:\WINNT\ for Windows Systems and
0090: * /etc/ for UNIX Systems.
0091: *
0092: */
0093: public DBBind() {
0094:
0095: // This is a special variable only set after DriverManager.getConnection() from initialize()
0096: // If DriverManager.getConnection() fails the the exception will be stored and
0097: // re-thrown each time DBBind.getConnection() is called.
0098: oConnectXcpt = null;
0099:
0100: try {
0101: initialize("hipergate");
0102: } catch (Exception e) {
0103: oConnectXcpt = e;
0104: if (DebugFile.trace)
0105: DebugFile.writeln(e.getClass().getName() + " "
0106: + e.getMessage());
0107: }
0108: }
0109:
0110: /**
0111: * <p>Create DBBind.</p>
0112: * Read database connection properties from specified properties file.
0113: * @param sProfile Name of properties file without extension.<br>
0114: * For example "hipergate" or "portal".<br>
0115: * The properties file must be placed at the directory pointed by
0116: * KNOWGATE_PROFILES environment variables.
0117: */
0118: public DBBind(String sProfile) {
0119:
0120: oConnectXcpt = null;
0121:
0122: try {
0123: initialize(sProfile);
0124: } catch (AccessControlException e) {
0125: oConnectXcpt = e;
0126: if (DebugFile.trace)
0127: DebugFile.writeln("AccessControlException "
0128: + e.getMessage());
0129: } catch (ClassNotFoundException e) {
0130: oConnectXcpt = e;
0131: if (DebugFile.trace)
0132: DebugFile.writeln("ClassNotFoundException "
0133: + e.getMessage());
0134: } catch (SQLException e) {
0135: oConnectXcpt = e;
0136: if (DebugFile.trace)
0137: DebugFile.writeln("SQLException " + e.getMessage());
0138: } catch (NullPointerException e) {
0139: oConnectXcpt = e;
0140: if (DebugFile.trace)
0141: DebugFile.writeln("NullPointerException "
0142: + e.getMessage());
0143: } catch (UnsatisfiedLinkError e) {
0144: oConnectXcpt = new Exception("UnsatisfiedLinkError "
0145: + e.getMessage(), e);
0146: if (DebugFile.trace)
0147: DebugFile.writeln("UnsatisfiedLinkError "
0148: + e.getMessage());
0149: } catch (NumberFormatException e) {
0150: oConnectXcpt = new Exception("NumberFormatException "
0151: + e.getMessage(), e);
0152: if (DebugFile.trace)
0153: DebugFile.writeln("NumberFormatException "
0154: + e.getMessage());
0155: }
0156: }
0157:
0158: /**
0159: * <P>Close DBBind</P>
0160: * Close connections from pool.<BR>
0161: * Stop connection reaper.<BR>
0162: */
0163: public void close() {
0164:
0165: if (DebugFile.trace) {
0166: DebugFile.writeln("Begin DBBind.close()");
0167: DebugFile.incIdent();
0168: }
0169:
0170: oConnectXcpt = null;
0171:
0172: oGlobalTableMap = null;
0173:
0174: oTableMap.clear();
0175: oTableMap = null;
0176:
0177: oConnPool.close();
0178:
0179: oConnPool = null;
0180:
0181: if (DebugFile.trace) {
0182: DebugFile.decIdent();
0183: DebugFile.writeln("End DBBind.close()");
0184: }
0185: }
0186:
0187: // ----------------------------------------------------------
0188:
0189: /**
0190: * Close and reopen the connection pool and reload the table map cache
0191: * @throws SQLException
0192: * @throws ClassNotFoundException
0193: */
0194: public void restart() throws SQLException, ClassNotFoundException {
0195:
0196: if (DebugFile.trace) {
0197: DebugFile.writeln("Begin DBBind.restart()");
0198: DebugFile.incIdent();
0199: }
0200:
0201: oConnectXcpt = null;
0202:
0203: oGlobalTableMap = null;
0204:
0205: oTableMap.clear();
0206: oTableMap = null;
0207:
0208: try {
0209: oConnPool.close();
0210: } catch (Exception e) {
0211: if (DebugFile.trace)
0212: DebugFile.writeln(e.getClass().getName() + " "
0213: + e.getMessage());
0214: }
0215:
0216: oConnPool = null;
0217:
0218: initialize(sProfileName);
0219:
0220: if (DebugFile.trace) {
0221: DebugFile.incIdent();
0222: DebugFile.writeln("End DBBind.restart()");
0223: }
0224: } // restart
0225:
0226: // ----------------------------------------------------------
0227:
0228: /**
0229: * Get connection pool used by this database binding
0230: * @return Reference to JDCConnectionPool
0231: */
0232: public JDCConnectionPool connectionPool() {
0233: return oConnPool;
0234: }
0235:
0236: // ----------------------------------------------------------
0237:
0238: private void loadDriver(String sProfile)
0239: throws ClassNotFoundException, NullPointerException {
0240:
0241: Class oDriver;
0242: String sDriver;
0243:
0244: if (DebugFile.trace)
0245: DebugFile.writeln("Begin DBBind.loadDriver(" + sProfile
0246: + ")");
0247:
0248: sDriver = Environment.getProfileVar(sProfile, "driver");
0249:
0250: if (DebugFile.trace)
0251: DebugFile.writeln(" driver=" + sDriver);
0252:
0253: if (null == sDriver)
0254: throw new NullPointerException(
0255: "Could not find property driver at " + sProfile);
0256:
0257: oDriver = Class.forName(sDriver);
0258:
0259: if (DebugFile.trace)
0260: DebugFile.writeln("End DBBind.loadDriver()");
0261: } // loadDriver()
0262:
0263: // ----------------------------------------------------------
0264:
0265: private static boolean in(String sStr, String[] aSet) {
0266:
0267: boolean bRetVal = false;
0268:
0269: if (aSet != null) {
0270: final int iLen = aSet.length;
0271:
0272: for (int i = 0; i < iLen && !bRetVal; i++)
0273: bRetVal = sStr.equalsIgnoreCase(aSet[i]);
0274: } // fi
0275:
0276: return bRetVal;
0277: }
0278:
0279: // ----------------------------------------------------------
0280:
0281: protected void initialize(String sProfile)
0282: throws ClassNotFoundException, SQLException,
0283: NullPointerException, AccessControlException,
0284: UnsatisfiedLinkError, NumberFormatException {
0285:
0286: int i;
0287: Connection oConn;
0288: DatabaseMetaData oMData;
0289: ResultSet oRSet;
0290: String TableTypes[] = new String[1];
0291: DBTable oTable;
0292: String sCatalog;
0293: String sSchema;
0294: String sTableName;
0295: Iterator oTableIterator;
0296: String[] aExclude;
0297:
0298: oTableMap = new HashMap(255);
0299: oGlobalTableMap = oTableMap;
0300:
0301: if (DebugFile.trace) {
0302: DebugFile.writeln("hipergate package build "
0303: + DBBind.VERSION);
0304: DebugFile.envinfo();
0305:
0306: DebugFile.writeln("Begin DBBind.initialize(" + sProfile
0307: + ")");
0308: DebugFile.incIdent();
0309: }
0310:
0311: sProfileName = sProfile;
0312:
0313: // ****************
0314: // Load JDBC driver
0315: loadDriver(sProfile);
0316:
0317: if (DebugFile.trace)
0318: DebugFile.writeln("Load Driver "
0319: + Environment.getProfileVar(sProfile, "driver")
0320: + " : OK\n");
0321:
0322: if (DebugFile.trace)
0323: DebugFile.writeln("Trying to connect to "
0324: + Environment.getProfileVar(sProfile, "dburl")
0325: + " with user "
0326: + Environment.getProfileVar(sProfile, "dbuser"));
0327:
0328: // **********************************************************
0329: // Get database connection parameters from file hipergate.cnf
0330:
0331: // New for v2.2 *
0332: try {
0333: DriverManager.setLoginTimeout(Integer.parseInt(Environment
0334: .getProfileVar(sProfile, "logintimeout", "20")));
0335: } catch (Exception x) {
0336: if (DebugFile.trace)
0337: DebugFile
0338: .writeln("DriverManager.setLoginTimeout() "
0339: + x.getClass().getName() + " "
0340: + x.getMessage());
0341: }
0342: // **************
0343:
0344: try {
0345: oConn = DriverManager.getConnection(Environment
0346: .getProfileVar(sProfile, "dburl"), Environment
0347: .getProfileVar(sProfile, "dbuser"), Environment
0348: .getProfileVar(sProfile, "dbpassword"));
0349: } catch (SQLException e) {
0350: if (DebugFile.trace)
0351: DebugFile.writeln("DriverManager.getConnection("
0352: + Environment.getProfileVar(sProfile, "dburl")
0353: + ","
0354: + Environment.getProfileVar(sProfile, "dbuser")
0355: + ", ...) SQLException [" + e.getSQLState()
0356: + "]:" + String.valueOf(e.getErrorCode()) + " "
0357: + e.getMessage());
0358: oConnectXcpt = new SQLException(
0359: "DriverManager.getConnection("
0360: + Environment.getProfileVar(sProfile,
0361: "dburl")
0362: + ","
0363: + Environment.getProfileVar(sProfile,
0364: "dbuser") + ", ...) "
0365: + e.getMessage(), e.getSQLState(), e
0366: .getErrorCode());
0367: throw (SQLException) oConnectXcpt;
0368: }
0369:
0370: if (DebugFile.trace) {
0371: DebugFile.writeln("Database Connection to "
0372: + Environment.getProfileVar(sProfile, "dburl")
0373: + " : OK\n");
0374: DebugFile.writeln("Calling Connection.getMetaData()");
0375: }
0376:
0377: oMData = oConn.getMetaData();
0378:
0379: if (DebugFile.trace)
0380: DebugFile
0381: .writeln("Calling DatabaseMetaData.getDatabaseProductName()");
0382:
0383: sDatabaseProductName = oMData.getDatabaseProductName();
0384:
0385: if (DebugFile.trace) {
0386: DebugFile.writeln("Database is \"" + sDatabaseProductName
0387: + "\"");
0388: DebugFile.writeln("Product version "
0389: + oMData.getDatabaseProductVersion());
0390: DebugFile.writeln(oMData.getDriverName() + " "
0391: + oMData.getDriverVersion());
0392: DebugFile.writeln("Max connections "
0393: + String.valueOf(oMData.getMaxConnections()));
0394: DebugFile.writeln("Max statements "
0395: + String.valueOf(oMData.getMaxStatements()));
0396: }
0397:
0398: if (sDatabaseProductName.equals(DBMSNAME_POSTGRESQL))
0399: iDatabaseProductId = DBMS_POSTGRESQL;
0400: else if (sDatabaseProductName.equals(DBMSNAME_MSSQL))
0401: iDatabaseProductId = DBMS_MSSQL;
0402: else if (sDatabaseProductName.equals(DBMSNAME_ORACLE))
0403: iDatabaseProductId = DBMS_ORACLE;
0404: else if (sDatabaseProductName.equals(DBMSNAME_MYSQL))
0405: iDatabaseProductId = DBMS_MYSQL;
0406: else
0407: iDatabaseProductId = DBMS_GENERIC;
0408:
0409: Functions.setForDBMS(sDatabaseProductName);
0410:
0411: // **********************
0412: // Cache database catalog
0413:
0414: sCatalog = oConn.getCatalog();
0415:
0416: if (DebugFile.trace)
0417: DebugFile.writeln("Catalog is \"" + sCatalog + "\"");
0418:
0419: if (DebugFile.trace)
0420: DebugFile.writeln("Gather metadata : OK");
0421:
0422: sSchema = Environment.getProfileVar(sProfile, "schema", "");
0423:
0424: if (DebugFile.trace)
0425: DebugFile.writeln("Schema is \"" + sSchema + "\"");
0426:
0427: i = 0;
0428:
0429: TableTypes[0] = "TABLE";
0430:
0431: if (DBMS_ORACLE == iDatabaseProductId) {
0432: aExclude = new String[] { "AUDIT_ACTIONS",
0433: "STMT_AUDIT_OPTION_MAP", "DUAL", "PSTUBTBL",
0434: "USER_CS_SRS", "USER_TRANSFORM_MAP", "CS_SRS",
0435: "HELP", "SDO_ANGLE_UNITS", "SDO_AREA_UNITS",
0436: "SDO_DIST_UNITS", "SDO_DATUMS",
0437: "SDO_CMT_CBK_DML_TABLE", "SDO_CMT_CBK_FN_TABLE",
0438: "SDO_CMT_CBK_DML_TABLE", "SDO_PROJECTIONS",
0439: "SDO_ELLIPSOIDS", "SDO_GEOR_XMLSCHEMA_TABLE",
0440: "SDO_GR_MOSAIC_0", "SDO_GR_MOSAIC_1",
0441: "SDO_GR_MOSAIC_2", "SDO_GR_MOSAIC_3",
0442: "SDO_TOPO_RELATION_DATA", "SDO_TOPO_TRANSACT_DATA",
0443: "SDO_TXN_IDX_DELETES", "DO_TXN_IDX_EXP_UPD_RGN",
0444: "SDO_TXN_IDX_INSERTS", "SDO_CS_SRS", "IMPDP_STATS",
0445: "OLAP_SESSION_CUBES", "OLAP_SESSION_DIMS",
0446: "OLAPI_HISTORY", "OLAPI_IFACE_OBJECT_HISTORY",
0447: "OLAPI_IFACE_OP_HISTORY",
0448: "OLAPI_MEMORY_HEAP_HISTORY",
0449: "OLAPI_MEMORY_OP_HISTORY", "OLAPI_SESSION_HISTORY",
0450: "OLAPTABLEVELS", "OLAPTABLEVELTUPLES",
0451: "OLAP_OLEDB_FUNCTIONS_PVT", "OLAP_OLEDB_KEYWORDS",
0452: "OLAP_OLEDB_MDPROPS", "OLAP_OLEDB_MDPROPVALS",
0453: "OGIS_SPATIAL_REFERENCE_SYSTEMS",
0454: "SYSTEM_PRIVILEGE_MAP", "TABLE_PRIVILEGE_MAP" };
0455:
0456: if (DebugFile.trace) {
0457: ResultSet oSchemas = null;
0458: try {
0459: int iSchemaCount = 0;
0460: oSchemas = oMData.getSchemas();
0461: while (oSchemas.next()) {
0462: DebugFile.writeln("schema name = "
0463: + oSchemas.getString(1));
0464: iSchemaCount++;
0465: }
0466: oSchemas.close();
0467: oSchemas = null;
0468: if (0 == iSchemaCount)
0469: DebugFile.writeln("no schemas found");
0470: } catch (Exception sqle) {
0471: try {
0472: if (null != oSchemas)
0473: oSchemas.close();
0474: } catch (Exception ignore) {
0475: }
0476: DebugFile
0477: .writeln("SQLException at DatabaseMetaData.getSchemas() "
0478: + sqle.getMessage());
0479: }
0480: DebugFile.writeln("DatabaseMetaData.getTables("
0481: + sCatalog + ", null, %, {TABLE})");
0482: }
0483:
0484: oRSet = oMData.getTables(sCatalog, null, "%", TableTypes);
0485:
0486: while (oRSet.next()) {
0487:
0488: if (oRSet.getString(3).indexOf('$') < 0
0489: && !in(oRSet.getString(3).toUpperCase(),
0490: aExclude)) {
0491: oTable = new DBTable(sCatalog, sSchema, oRSet
0492: .getString(3), ++i);
0493:
0494: sTableName = oTable.getName().toLowerCase();
0495:
0496: if (oTableMap.containsKey(sTableName))
0497: oTableMap.remove(sTableName);
0498:
0499: oTableMap.put(sTableName, oTable);
0500:
0501: if (DebugFile.trace)
0502: DebugFile.writeln("Reading table "
0503: + oTable.getName());
0504: } else if (DebugFile.trace)
0505: DebugFile.writeln("Skipping table "
0506: + oRSet.getString(3));
0507: } // wend
0508:
0509: } else {
0510: if (DBMS_POSTGRESQL == iDatabaseProductId)
0511: aExclude = new String[] { "sql_languages",
0512: "sql_features", "sql_implementation_info",
0513: "sql_packages", "sql_sizing",
0514: "sql_sizing_profiles", "pg_ts_cfg",
0515: "pg_logdir_ls", "pg_ts_cfgmap", "pg_ts_dict",
0516: "pg_ts_parses", "pg_ts_parser",
0517: "pg_reload_conf" };
0518: else if (DBMS_MSSQL == iDatabaseProductId)
0519: aExclude = new String[] { "syscolumns", "syscomments",
0520: "sysdepends", "sysfilegroups", "sysfiles",
0521: "sysfiles1", "sysforeignkeys",
0522: "sysfulltextcatalogs", "sysfulltextnotify",
0523: "sysindexes", "sysindexkeys", "sysmembers",
0524: "sysobjects", "syspermissions",
0525: "sysproperties", "sysprotects",
0526: "sysreferences", "systypes", "sysusers" };
0527: else
0528: aExclude = null;
0529:
0530: if (DebugFile.trace)
0531: DebugFile.writeln("DatabaseMetaData.getTables("
0532: + sCatalog + ", " + sSchema + ", %, {TABLE})");
0533:
0534: oRSet = oMData
0535: .getTables(sCatalog, sSchema, "%", TableTypes);
0536:
0537: // For each table, keep its name in a memory map
0538:
0539: if (sSchema.length() > 0) {
0540:
0541: while (oRSet.next()) {
0542:
0543: sTableName = oRSet.getString(3);
0544:
0545: if (!oRSet.wasNull()) {
0546: oTable = new DBTable(sCatalog, Environment
0547: .getProfileVar(sProfile, "schema",
0548: "dbo"), sTableName, ++i);
0549:
0550: sTableName = oTable.getName().toLowerCase();
0551:
0552: if (!in(sTableName, aExclude)) {
0553: if (oTableMap.containsKey(sTableName))
0554: oTableMap.remove(sTableName);
0555:
0556: oTableMap.put(sTableName, oTable);
0557:
0558: if (DebugFile.trace)
0559: DebugFile.writeln("Reading table "
0560: + sSchema + "."
0561: + oTable.getName());
0562: } // fi (!in(sTableName, aExclude))
0563: } // fi (!oRSet.wasNull())
0564: } // wend
0565: } else { // sSchema == ""
0566: while (oRSet.next()) {
0567:
0568: sTableName = oRSet.getString(3);
0569:
0570: if (!oRSet.wasNull()) {
0571: oTable = new DBTable(sCatalog, "", sTableName,
0572: ++i);
0573:
0574: sTableName = oTable.getName().toLowerCase();
0575:
0576: if (!in(sTableName, aExclude)) {
0577: if (oTableMap.containsKey(sTableName))
0578: oTableMap.remove(sTableName);
0579:
0580: oTableMap.put(sTableName, oTable);
0581:
0582: if (DebugFile.trace)
0583: DebugFile.writeln("Reading table "
0584: + oTable.getName());
0585: } // fi (!in(sTableName, aExclude))
0586: } // fi (!oRSet.wasNull())
0587: } // wend
0588: } // fi (sSchema == "")
0589: } // fi (DBMS_ORACLE!=iDatabaseProductId)
0590:
0591: oRSet.close();
0592:
0593: if (DebugFile.trace && oTableMap.size() == 0)
0594: DebugFile.writeln("No tables found");
0595:
0596: oTableIterator = oTableMap.values().iterator();
0597:
0598: // For each table, read its column structure and keep it in memory
0599:
0600: while (oTableIterator.hasNext()) {
0601: oTable = (DBTable) oTableIterator.next();
0602: oTable.readColumns(oConn, oMData);
0603: } // wend
0604:
0605: if (DebugFile.trace)
0606: DebugFile.writeln("Table scan : OK");
0607:
0608: oConn.close();
0609: oConn = null;
0610:
0611: // Create database connection pool
0612:
0613: if (DebugFile.trace)
0614: DebugFile.writeln("new JDCConnectionPool("
0615: + Environment.getProfileVar(sProfile, "dburl")
0616: + ","
0617: + Environment.getProfileVar(sProfile, "dbuser")
0618: + ",...,"
0619: + Environment.getProfileVar(sProfile, "poolsize",
0620: "32")
0621: + ","
0622: + Environment.getProfileVar(sProfile,
0623: "maxconnections", "100") + ")");
0624:
0625: // ***************************************************************
0626: // New for v2.2
0627: // Perform aditional checkings of hipergate.cnf integer values and
0628: // add logintimeout and connectiontimeout property handling
0629:
0630: int iPoolSize, iMaxConns, iLoginTimeout;
0631: long iConnectionTimeout;
0632:
0633: try {
0634: iPoolSize = Integer.parseInt(Environment.getProfileVar(
0635: sProfile, "poolsize", "32"));
0636: if (iPoolSize < 0)
0637: throw new NumberFormatException();
0638: } catch (NumberFormatException nfe) {
0639: if (DebugFile.trace) {
0640: DebugFile.writeln("poolsize property at " + sProfile
0641: + ".cnf must be a positive integer value");
0642: DebugFile.decIdent();
0643: }
0644: throw new NumberFormatException("poolsize property at "
0645: + sProfile
0646: + ".cnf must be a positive integer value");
0647: }
0648:
0649: try {
0650: iMaxConns = Integer.parseInt(Environment.getProfileVar(
0651: sProfile, "maxconnections", "100"));
0652: if (iMaxConns < 0)
0653: throw new NumberFormatException();
0654: } catch (NumberFormatException nfe) {
0655: if (DebugFile.trace) {
0656: DebugFile.writeln("maxconnections property at "
0657: + sProfile
0658: + ".cnf must be a positive integer value");
0659: DebugFile.decIdent();
0660: }
0661: throw new NumberFormatException(
0662: "maxconnections property at " + sProfile
0663: + ".cnf must be a positive integer value");
0664: }
0665:
0666: try {
0667: iLoginTimeout = Integer.parseInt(Environment.getProfileVar(
0668: sProfile, "logintimeout", "20"));
0669: } catch (NumberFormatException nfe) {
0670: if (DebugFile.trace) {
0671: DebugFile.writeln("logintimeout property at "
0672: + sProfile + ".cnf must be an integer value");
0673: DebugFile.decIdent();
0674: }
0675: throw new NumberFormatException("logintimeout property at "
0676: + sProfile + ".cnf must be an integer value");
0677: }
0678: if (iLoginTimeout <= 0) {
0679: if (DebugFile.trace) {
0680: DebugFile.writeln("logintimeout property at "
0681: + sProfile + ".cnf must be greater than zero");
0682: DebugFile.decIdent();
0683: }
0684: throw new NumberFormatException("logintimeout property at "
0685: + sProfile + ".cnf must be greater than zero");
0686: }
0687:
0688: try {
0689: iConnectionTimeout = Long.parseLong(Environment
0690: .getProfileVar(sProfile, "connectiontimeout",
0691: "60000"));
0692: } catch (NumberFormatException nfe) {
0693: if (DebugFile.trace) {
0694: DebugFile.writeln("connectiontimeout property at "
0695: + sProfile + ".cnf must be an integer value");
0696: DebugFile.decIdent();
0697: }
0698: throw new NumberFormatException(
0699: "connectiontimeout property at " + sProfile
0700: + ".cnf must be an integer value");
0701: }
0702: if (iConnectionTimeout < 1000l) {
0703: if (DebugFile.trace) {
0704: DebugFile.writeln("connectiontimeout property at "
0705: + sProfile
0706: + ".cnf must be greater than 1000 miliseconds");
0707: DebugFile.decIdent();
0708: }
0709: throw new NumberFormatException(
0710: "connectiontimeout property at "
0711: + sProfile
0712: + ".cnf must be greater than 1000 miliseconds");
0713: }
0714:
0715: // ***************************************************************
0716:
0717: oConnPool = new JDCConnectionPool(this , Environment
0718: .getProfileVar(sProfile, "dburl"), Environment
0719: .getProfileVar(sProfile, "dbuser"), Environment
0720: .getProfileVar(sProfile, "dbpassword"), iPoolSize,
0721: iMaxConns, iLoginTimeout, iConnectionTimeout);
0722:
0723: if (null != oConnPool) {
0724: if (DebugFile.trace)
0725: DebugFile.writeln("Connection pool creation : OK");
0726:
0727: try {
0728: oConnPool.setReaperDaemonDelay(Long
0729: .parseLong(Environment.getProfileVar(sProfile,
0730: "connectionreaperdelay", "30000")));
0731: } catch (NumberFormatException nfe) {
0732: if (DebugFile.trace) {
0733: DebugFile
0734: .writeln("connectionreaperdelay property at "
0735: + sProfile
0736: + ".cnf must be an integer value");
0737: DebugFile.decIdent();
0738: }
0739: throw new NumberFormatException(
0740: "connectionreaperdelay property at " + sProfile
0741: + ".cnf must be an integer value");
0742: } catch (IllegalArgumentException iae) {
0743: if (DebugFile.trace) {
0744: DebugFile
0745: .writeln("connectionreaperdelay property at "
0746: + sProfile
0747: + ".cnf must be greater than 1000");
0748: DebugFile.decIdent();
0749: }
0750: throw new NumberFormatException(
0751: "connectionreaperdelay property at " + sProfile
0752: + ".cnf must must be greater than 1000");
0753: }
0754: } else {
0755: if (DebugFile.trace)
0756: DebugFile.writeln("Connection pool creation failed!");
0757: }
0758:
0759: if (DebugFile.trace) {
0760: DebugFile.decIdent();
0761: DebugFile.writeln("End DBBind.initialize()");
0762: }
0763: } // initialize
0764:
0765: // ----------------------------------------------------------
0766:
0767: /**
0768: * Get the name of Database Management System Connected
0769: * @return one of { "Microsoft SQL Server", "Oracle", "PostgreSQL" }
0770: * @throws SQLException
0771: */
0772:
0773: public String getDatabaseProductName() throws SQLException {
0774:
0775: if (null != oConnectXcpt)
0776: throw (SQLException) oConnectXcpt;
0777:
0778: return sDatabaseProductName;
0779: }
0780:
0781: // ----------------------------------------------------------
0782:
0783: /**
0784: * <p>Get Name of profile used for initializing DBBind</p>
0785: * Profile Name is the properties file name ("hipergate.cnf") without extension.<br>
0786: * For example "hipergate", "real", "demo", "test", "portal"
0787: * @return Profile name
0788: */
0789: public String getProfileName() {
0790: return sProfileName;
0791: }
0792:
0793: // ----------------------------------------------------------
0794:
0795: /**
0796: * Checks if an object exists at database
0797: * Checking is done directly against database catalog tables,
0798: * if current user does not have enought priviledges for reading
0799: * database catalog tables methos may fail or return a wrong result.
0800: * @param oConn Database connection
0801: * @param sObjectName Objeto name
0802: * @param sObjectType Objeto type
0803: * C = CHECK constraint
0804: * D = Default or DEFAULT constraint
0805: * F = FOREIGN KEY constraint
0806: * L = Log
0807: * P = Stored procedure
0808: * PK = PRIMARY KEY constraint (type is K)
0809: * RF = Replication filter stored procedure
0810: * S = System table
0811: * TR = Trigger
0812: * U = User table
0813: * UQ = UNIQUE constraint (type is K)
0814: * V = View
0815: * X = Extended stored procedure
0816: * @return <b>true</b> if object exists, <b>false</b> otherwise
0817: * @throws SQLException
0818: * @throws UnsupportedOperationException If current database management system is not supported for this method
0819: */
0820:
0821: public static boolean exists(JDCConnection oConn,
0822: String sObjectName, String sObjectType)
0823: throws SQLException, UnsupportedOperationException {
0824:
0825: return oConn.exists(sObjectName, sObjectType);
0826:
0827: } // exists()
0828:
0829: // ----------------------------------------------------------
0830:
0831: /**
0832: * Get datamodel version
0833: * @param oConn JDCConnection object
0834: * @return vs_stamp field from k_version table
0835: * @throws SQLException
0836: */
0837: public static String getDataModelVersion(JDCConnection oConn)
0838: throws SQLException {
0839: String sVersion = null;
0840:
0841: if (DebugFile.trace) {
0842: DebugFile
0843: .writeln("Begin DBBind.getDataModelVersion([Connection])");
0844: DebugFile.incIdent();
0845: }
0846:
0847: if (DBBind.exists(oConn, DB.k_version, "U")) {
0848: Statement oStmt = oConn.createStatement();
0849: ResultSet oRSet = oStmt
0850: .executeQuery("SELECT vs_stamp FROM "
0851: + DB.k_version);
0852: if (oRSet.next())
0853: sVersion = oRSet.getString(1);
0854: oRSet.close();
0855: oStmt.close();
0856: }
0857:
0858: if (DebugFile.trace) {
0859: DebugFile.decIdent();
0860: DebugFile.writeln("End DBBind.getDataModelVersion() : "
0861: + sVersion);
0862: }
0863:
0864: return sVersion;
0865: } // getDataModelVersion
0866:
0867: // ----------------------------------------------------------
0868:
0869: /**
0870: * Get datamodel version number
0871: * @param oConn JDCConnection object
0872: * @return for 2.0.8-> 20008 , 2.1.0 -> 20100, etc.
0873: * @throws SQLException
0874: */
0875: public static int getDataModelVersionNumber(JDCConnection oConn)
0876: throws SQLException {
0877:
0878: String sVersion = getDataModelVersion(oConn);
0879:
0880: if (null == sVersion)
0881: return 0;
0882:
0883: final int iLen = sVersion.length();
0884: String sMajor = "", sMinor = "", sRevision = "";
0885: int iDots = 0;
0886:
0887: for (int i = 0; i < iLen; i++) {
0888: if (sVersion.charAt(i) >= '0' && sVersion.charAt(i) <= '9') {
0889: switch (iDots) {
0890: case 0:
0891: sMajor += sVersion.charAt(i);
0892: break;
0893: case 1:
0894: sMinor += sVersion.charAt(i);
0895: break;
0896: case 2:
0897: sRevision += sVersion.charAt(i);
0898: }
0899: } else if (sVersion.charAt(i) == '.')
0900: iDots++;
0901: } // next (i)
0902:
0903: return Integer.parseInt(sMajor
0904: + Gadgets.leftPad(sMinor, '0', 2)
0905: + Gadgets.leftPad(sRevision, '0', 2));
0906: } // getDataModelVersionNumber
0907:
0908: // ----------------------------------------------------------
0909:
0910: /**
0911: * <p>Get current value for a sequence</p>
0912: * @param oConn JDCConnection
0913: * @param sSequenceName Sequence name.
0914: * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
0915: * @return Current sequence value
0916: * @throws SQLException
0917: * @throws UnsupportedOperationException Not all databases support sequences.
0918: * On Oracle and PostgreSQL, native SEQUENCE objects are used,
0919: * on MySQL and Microsoft SQL Server the stored procedure k_sp_currval simulates sequences,
0920: * this function is not supported on other DataBase Management Systems.
0921: * @since 3.0
0922: */
0923:
0924: public static int currVal(JDCConnection oConn, String sSequenceName)
0925: throws SQLException, UnsupportedOperationException {
0926:
0927: Statement oStmt;
0928: ResultSet oRSet;
0929: CallableStatement oCall;
0930: int iCurrVal;
0931:
0932: if (DebugFile.trace) {
0933: DebugFile
0934: .writeln("Begin hipergate DBBind.currVal([JDCConnection], "
0935: + sSequenceName + ")");
0936: DebugFile.incIdent();
0937: }
0938:
0939: switch (oConn.getDataBaseProduct()) {
0940:
0941: case JDCConnection.DBMS_MYSQL:
0942: case JDCConnection.DBMS_MSSQL:
0943:
0944: if (DebugFile.trace)
0945: DebugFile
0946: .writeln("Connection.prepareCall({call k_sp_currval ('"
0947: + sSequenceName + "',?)})");
0948:
0949: oCall = oConn.prepareCall("{call k_sp_currval (?,?)}");
0950: oCall.setString(1, sSequenceName);
0951: oCall.registerOutParameter(2, java.sql.Types.INTEGER);
0952: oCall.execute();
0953: iCurrVal = oCall.getInt(2);
0954: oCall.close();
0955: oCall = null;
0956: break;
0957:
0958: case JDCConnection.DBMS_POSTGRESQL:
0959: oStmt = oConn.createStatement();
0960:
0961: if (DebugFile.trace)
0962: DebugFile
0963: .writeln("Statement.executeQuery(SELECT nextval('"
0964: + sSequenceName + "'))");
0965:
0966: oRSet = oStmt.executeQuery("SELECT nextval('"
0967: + sSequenceName + "')");
0968: oRSet.next();
0969: iCurrVal = oRSet.getInt(1) - 1;
0970: oRSet.close();
0971:
0972: if (DebugFile.trace)
0973: DebugFile
0974: .writeln("Statement.executeQuery(SELECT setval('"
0975: + sSequenceName
0976: + "',"
0977: + String.valueOf(iCurrVal) + "))");
0978:
0979: oRSet = oStmt.executeQuery("SELECT setval('"
0980: + sSequenceName + "'," + String.valueOf(iCurrVal)
0981: + ")");
0982: oRSet.close();
0983:
0984: oStmt.close();
0985: break;
0986:
0987: case JDCConnection.DBMS_ORACLE:
0988: oStmt = oConn.createStatement();
0989:
0990: if (DebugFile.trace)
0991: DebugFile.writeln("Statement.executeQuery(SELECT "
0992: + sSequenceName + ".CURRVAL))");
0993:
0994: oRSet = oStmt.executeQuery("SELECT " + sSequenceName
0995: + ".CURRVAL FROM dual");
0996: oRSet.next();
0997: iCurrVal = oRSet.getInt(1);
0998: oRSet.close();
0999: oStmt.close();
1000: break;
1001:
1002: default:
1003: throw new UnsupportedOperationException(
1004: "function currVal() not supported on current DBMS");
1005: }
1006:
1007: oConn = null;
1008:
1009: if (DebugFile.trace) {
1010: DebugFile.decIdent();
1011: DebugFile.writeln("End DBBind.currVal() : "
1012: + String.valueOf(iCurrVal));
1013: }
1014:
1015: return iCurrVal;
1016: } // currVal
1017:
1018: // ----------------------------------------------------------
1019:
1020: /**
1021: * <p>Get current value for a sequence</p>
1022: * @param oSQLConn Database connection
1023: * @param sSequenceName Sequence name.
1024: * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
1025: * @return Current sequence value
1026: * @throws SQLException
1027: * @throws UnsupportedOperationException Not all databases support sequences.
1028: * On Oracle and PostgreSQL, native SEQUENCE objects are used,
1029: * on MySQL and Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
1030: * this function is not supported on other DataBase Management Systems.
1031: * @since 3.0
1032: */
1033:
1034: public static int currVal(Connection oSQLConn, String sSequenceName)
1035: throws SQLException, UnsupportedOperationException {
1036: return currVal(new JDCConnection(oSQLConn, null), sSequenceName);
1037: }
1038:
1039: // ----------------------------------------------------------
1040:
1041: /**
1042: * <p>Get next value for a sequence</p>
1043: * @param oConn JDCConnection
1044: * @param sSequenceName Sequence name.
1045: * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
1046: * @return int Next sequence value
1047: * @throws SQLException
1048: * @throws UnsupportedOperationException Not all databases support sequences.
1049: * On Oracle and PostgreSQL, native SEQUENCE objects are used,
1050: * on Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
1051: * this function is not supported on other DataBase Management Systems.
1052: * @since 3.0
1053: */
1054: public static int nextVal(JDCConnection oConn, String sSequenceName)
1055: throws SQLException, UnsupportedOperationException {
1056:
1057: Statement oStmt;
1058: ResultSet oRSet;
1059: CallableStatement oCall;
1060: int iNextVal;
1061:
1062: if (DebugFile.trace) {
1063: DebugFile
1064: .writeln("Begin hipergate DBBind.nextVal([JDCConnection], "
1065: + sSequenceName + ")");
1066: DebugFile.incIdent();
1067: }
1068:
1069: switch (oConn.getDataBaseProduct()) {
1070:
1071: case JDCConnection.DBMS_MYSQL:
1072: case JDCConnection.DBMS_MSSQL:
1073:
1074: if (DebugFile.trace)
1075: DebugFile
1076: .writeln("Connection.prepareCall({call k_sp_nextval ('"
1077: + sSequenceName + "',?)})");
1078:
1079: oCall = oConn.prepareCall("{call k_sp_nextval (?,?)}");
1080: oCall.setString(1, sSequenceName);
1081: oCall.registerOutParameter(2, java.sql.Types.INTEGER);
1082: oCall.execute();
1083: iNextVal = oCall.getInt(2);
1084: oCall.close();
1085: oCall = null;
1086: break;
1087:
1088: case JDCConnection.DBMS_POSTGRESQL:
1089: oStmt = oConn.createStatement();
1090:
1091: if (DebugFile.trace)
1092: DebugFile
1093: .writeln("Statement.executeQuery(SELECT nextval('"
1094: + sSequenceName + "'))");
1095:
1096: oRSet = oStmt.executeQuery("SELECT nextval('"
1097: + sSequenceName + "')");
1098: oRSet.next();
1099: iNextVal = oRSet.getInt(1);
1100: oRSet.close();
1101: oStmt.close();
1102: break;
1103:
1104: case JDCConnection.DBMS_ORACLE:
1105: oStmt = oConn.createStatement();
1106:
1107: if (DebugFile.trace)
1108: DebugFile
1109: .writeln("Statement.executeQuery(SELECT nextval('"
1110: + sSequenceName + "'))");
1111:
1112: oRSet = oStmt.executeQuery("SELECT " + sSequenceName
1113: + ".NEXTVAL FROM dual");
1114: oRSet.next();
1115: iNextVal = oRSet.getInt(1);
1116: oRSet.close();
1117: oStmt.close();
1118: break;
1119:
1120: default:
1121: throw new UnsupportedOperationException(
1122: "function nextVal() not supported on current DBMS");
1123: }
1124:
1125: oConn = null;
1126:
1127: if (DebugFile.trace) {
1128: DebugFile.decIdent();
1129: DebugFile.writeln("End DBBind.nextVal() : "
1130: + String.valueOf(iNextVal));
1131: }
1132:
1133: return iNextVal;
1134: } // nextVal
1135:
1136: // ----------------------------------------------------------
1137:
1138: /**
1139: * <p>Get next value for a sequence</p>
1140: * @param oSQLConn Database connection
1141: * @param sSequenceName Sequence name.
1142: * In MySQL and SQL Server sequences are implemented using row locks at k_sequences table.
1143: * @return int Next sequence value
1144: * @throws SQLException
1145: * @throws UnsupportedOperationException Not all databases support sequences.
1146: * On Oracle and PostgreSQL, native SEQUENCE objects are used,
1147: * on Microsoft SQL Server the stored procedure k_sp_nextval simulates sequences,
1148: * this function is not supported on other DataBase Management Systems.
1149: */
1150:
1151: public static int nextVal(Connection oSQLConn, String sSequenceName)
1152: throws SQLException, UnsupportedOperationException {
1153:
1154: return nextVal(new JDCConnection(oSQLConn, null), sSequenceName);
1155: }
1156:
1157: // ----------------------------------------------------------
1158:
1159: /**
1160: * Format Date in ODBC escape sequence style
1161: * @param dt Date to be formated
1162: * @param sFormat Format Type "d" or "ts" or "shortTime".
1163: * Use d for { d 'yyyy-mm-dd' }, use ts for { ts 'ts=yyyy-mm-dd hh:nn:ss' }<br>
1164: * use shortTime for hh:mm<br>
1165: * use shortDate for yyyy-mm-dd<br>
1166: * use dateTime for yyyy-mm-dd hh:mm:ss<br>
1167: * @return Formated date
1168: * @throws IllegalArgumentException if dt is of type java.sql.Date
1169: */
1170:
1171: public static String escape(java.util.Date dt, String sFormat)
1172: throws IllegalArgumentException {
1173: String str = "";
1174: String sMonth, sDay, sHour, sMin, sSec;
1175:
1176: if (sFormat.equalsIgnoreCase("ts")
1177: || sFormat.equalsIgnoreCase("d")) {
1178: str = DBBind.Functions.escape(dt, sFormat);
1179: } else if (sFormat.equalsIgnoreCase("shortTime")) {
1180: sHour = (dt.getHours() < 10 ? "0"
1181: + String.valueOf(dt.getHours()) : String.valueOf(dt
1182: .getHours()));
1183: sMin = (dt.getMinutes() < 10 ? "0"
1184: + String.valueOf(dt.getMinutes()) : String
1185: .valueOf(dt.getMinutes()));
1186: str += sHour + ":" + sMin;
1187: } else if (sFormat.equalsIgnoreCase("shortDate")) {
1188: sMonth = (dt.getMonth() + 1 < 10 ? "0"
1189: + String.valueOf((dt.getMonth() + 1)) : String
1190: .valueOf(dt.getMonth() + 1));
1191: sDay = (dt.getDate() < 10 ? "0"
1192: + String.valueOf(dt.getDate()) : String.valueOf(dt
1193: .getDate()));
1194:
1195: str += String.valueOf(dt.getYear() + 1900) + "-" + sMonth
1196: + "-" + sDay;
1197: } else {
1198: sMonth = (dt.getMonth() + 1 < 10 ? "0"
1199: + String.valueOf((dt.getMonth() + 1)) : String
1200: .valueOf(dt.getMonth() + 1));
1201: sDay = (dt.getDate() < 10 ? "0"
1202: + String.valueOf(dt.getDate()) : String.valueOf(dt
1203: .getDate()));
1204: sHour = (dt.getHours() < 10 ? "0"
1205: + String.valueOf(dt.getHours()) : String.valueOf(dt
1206: .getHours()));
1207: sMin = (dt.getMinutes() < 10 ? "0"
1208: + String.valueOf(dt.getMinutes()) : String
1209: .valueOf(dt.getMinutes()));
1210: sSec = (dt.getSeconds() < 10 ? "0"
1211: + String.valueOf(dt.getSeconds()) : String
1212: .valueOf(dt.getSeconds()));
1213:
1214: str += String.valueOf(dt.getYear() + 1900) + "-" + sMonth
1215: + "-" + sDay + " " + sHour + ":" + sMin + ":"
1216: + sSec;
1217: }
1218:
1219: return str;
1220: } // escape()
1221:
1222: // ----------------------------------------------------------
1223:
1224: /**
1225: * Format Timestamp in ODBC escape sequence style
1226: * @param ts Timestamp to be formated
1227: * @param sFormat Format Type "d" or "ts" or "shortTime".
1228: * Use d for { d 'yyyy-mm-dd' }, use ts for { ts 'ts=yyyy-mm-dd hh:nn:ss' }<br>
1229: * use shortTime for hh:mm<br>
1230: * use shortDate for yyyy-mm-dd<br>
1231: * use dateTime for yyyy-mm-dd hh:mm:ss<br>
1232: * @return Formated date
1233: * @since 3.0
1234: */
1235:
1236: public static String escape(Timestamp ts, String sFormat) {
1237: return DBBind.escape(new java.util.Date(ts.getTime()), sFormat);
1238: }
1239:
1240: // ----------------------------------------------------------
1241:
1242: /**
1243: * <p>Get {@link DBTable} object by name</p>
1244: * @param sTable Table name
1245: * @return DBTable object or <b>null</b> if no table was found with given name.
1246: * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1247: * the HashMap is loaded upon first call to a DBBind constructor. If getTable()
1248: * is called before creating any instance of DBBind an IllegalStateException
1249: * will be raised.
1250: * @deprecated Use {@link #getDBTable(String) getDBTable} instead
1251: */
1252:
1253: public static DBTable getTable(String sTable)
1254: throws java.lang.IllegalStateException {
1255:
1256: if (null == oGlobalTableMap)
1257: throw new IllegalStateException(
1258: "DBBind global table map not initialized, call DBBind constructor first");
1259:
1260: return (DBTable) oGlobalTableMap.get(sTable.toLowerCase());
1261: } // getTable
1262:
1263: // ----------------------------------------------------------
1264:
1265: /**
1266: * <p>Get {@link DBTable} object by name</p>
1267: * @param sTable Table name
1268: * @return DBTable object or <b>null</b> if no table was found with given name.
1269: * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1270: * the HashMap is loaded upon first call to a DBBind constructor.
1271: * If getDBTable() is called before creating any instance of DBBind then an
1272: * IllegalStateException will be thrown.
1273: * @since 2.0
1274: */
1275:
1276: public DBTable getDBTable(String sTable)
1277: throws IllegalStateException {
1278:
1279: if (null == oTableMap)
1280: throw new IllegalStateException(
1281: "DBBind internal table map not initialized, call DBBind constructor first");
1282:
1283: return (DBTable) oTableMap.get(sTable.toLowerCase());
1284: } // getDBTable
1285:
1286: /**
1287: * <p>Get map of {@link DBTable} objects</p>
1288: * @return HashMap
1289: * @throws IllegalStateException DBTable objects are cached in a static HasMap,
1290: * the HashMap is loaded upon first call to a DBBind constructor.
1291: * If getDBTablesMap() is called before creating any instance of DBBind
1292: * then an IllegalStateException will be thrown.
1293: * @since 3.0
1294: */
1295: public HashMap getDBTablesMap() throws IllegalStateException {
1296:
1297: if (null == oTableMap)
1298: throw new IllegalStateException(
1299: "DBBind internal table map not initialized, call DBBind constructor first");
1300:
1301: return oTableMap;
1302: } // getDBTablesMap
1303:
1304: // ----------------------------------------------------------
1305:
1306: /**
1307: * <p>Get a {@link JDCConnection} instance from connection pool</p>
1308: * @param sCaller Symbolic name identifying the caller program or subroutine,
1309: * this field is used for statistical control of database accesses,
1310: * performance tunning and debugging open/close mismatch.
1311: * @return An open connection to the database.
1312: * @throws SQLException
1313: */
1314:
1315: public synchronized JDCConnection getConnection(String sCaller)
1316: throws SQLException {
1317: JDCConnection oConn;
1318:
1319: if (DebugFile.trace) {
1320: DebugFile.writeln("Begin DBBind.getConnection(" + sCaller
1321: + ")");
1322: DebugFile.incIdent();
1323:
1324: if (null != oConnectXcpt) {
1325: DebugFile.writeln("Previous exception "
1326: + oConnectXcpt.getMessage());
1327: DebugFile.decIdent();
1328: }
1329: }
1330:
1331: if (null != oConnectXcpt) {
1332: if (oConnectXcpt instanceof SQLException)
1333: throw (SQLException) oConnectXcpt;
1334: else
1335: throw new SQLException(oConnectXcpt.getClass()
1336: .getName()
1337: + " " + oConnectXcpt.getMessage());
1338: }
1339:
1340: if (null != oConnPool) {
1341: oConn = oConnPool.getConnection(sCaller);
1342: } else {
1343: if (DebugFile.trace)
1344: DebugFile.writeln("ERROR: connection pool not set");
1345: oConn = null;
1346: }
1347:
1348: if (DebugFile.trace) {
1349: DebugFile.decIdent();
1350: DebugFile.writeln("End DBBind.getConnection(" + sCaller
1351: + ") : "
1352: + (null == oConn ? "null" : "[Connection]"));
1353: }
1354:
1355: return oConn;
1356: } // getConnection()
1357:
1358: // ----------------------------------------------------------
1359: /**
1360: *
1361: * @return Get Current System Time
1362: */
1363:
1364: public static long getTime() {
1365:
1366: return System.currentTimeMillis();
1367: }
1368:
1369: // ===========================================================================
1370:
1371: /**
1372: * <p>Aliases for common SQL functions in different database dialects.</p>
1373: * @author Sergio Montoro Ten
1374: * @version 1.2
1375: */
1376:
1377: public static class Functions {
1378:
1379: /**
1380: * <p>ISNULL(value, default)</p>
1381: * Get value or default if value is null
1382: */
1383: public static String ISNULL;
1384:
1385: /**
1386: * <p>String concatenation</p>
1387: * Str1 CONCAT Str2
1388: */
1389: public static String CONCAT;
1390:
1391: /**
1392: * Get System Date
1393: */
1394: public static String GETDATE;
1395:
1396: /**
1397: * <p>Transform String to lowercase</p>
1398: * LOWER(str)
1399: */
1400: public static String LOWER;
1401:
1402: /**
1403: * <p>Transform String to uppercase</p>
1404: * UPPER(str)
1405: */
1406: public static String UPPER;
1407:
1408: /**
1409: * <p>Get string length</p>
1410: * LENGTH(str)
1411: */
1412: public static String LENGTH;
1413:
1414: /**
1415: * <p>Get character from ASCII code</p>
1416: * CHAR([0..255])
1417: */
1418: public static String CHR;
1419:
1420: /**
1421: * <p>Case-insensitve LIKE operator (PostgreSQL only)</p>
1422: */
1423: public static String ILIKE;
1424:
1425: public static int iDBMS;
1426:
1427: // -------------------------------------------------------------------------
1428:
1429: private static void setForDBMS(String sDBMSName)
1430: throws UnsupportedOperationException {
1431:
1432: if (sDBMSName.equals("Microsoft SQL Server")) {
1433: iDBMS = JDCConnection.DBMS_MSSQL;
1434: ISNULL = "ISNULL";
1435: CONCAT = "+";
1436: GETDATE = "GETDATE()";
1437: LOWER = "LOWER";
1438: UPPER = "UPPER";
1439: LENGTH = "LEN";
1440: CHR = "CHAR";
1441: ILIKE = "LIKE";
1442:
1443: } else if (sDBMSName.equals("Oracle")) {
1444: iDBMS = JDCConnection.DBMS_ORACLE;
1445: ISNULL = "NVL";
1446: CONCAT = "||";
1447: GETDATE = "SYSDATE";
1448: LOWER = "LOWER";
1449: UPPER = "UPPER";
1450: LENGTH = "LENGTH";
1451: CHR = "CHR";
1452: ILIKE = "LIKE";
1453:
1454: } else if (sDBMSName.equals("PostgreSQL")) {
1455: iDBMS = JDCConnection.DBMS_POSTGRESQL;
1456: ISNULL = "COALESCE";
1457: CONCAT = "||";
1458: GETDATE = "current_timestamp";
1459: LOWER = "lower";
1460: UPPER = "upper";
1461: LENGTH = "char_length";
1462: CHR = "chr";
1463: ILIKE = "ILIKE";
1464:
1465: } else if (sDBMSName.equals("MySQL")) {
1466: iDBMS = JDCConnection.DBMS_MYSQL;
1467: ISNULL = "COALESCE";
1468: CONCAT = null; // MySQL uses CONCAT() function instead of an operator
1469: GETDATE = "NOW()";
1470: LENGTH = "CHAR_LENGTH";
1471: CHR = "CHAR";
1472: LOWER = "LCASE";
1473: UPPER = "UCASE";
1474: ILIKE = "LIKE";
1475:
1476: } else
1477: throw new UnsupportedOperationException(
1478: "unsupported DBMS");
1479:
1480: } // setForDBMS
1481:
1482: // -------------------------------------------------------------------------
1483:
1484: private static String escape(java.util.Date dt, String sFormat)
1485: throws UnsupportedOperationException {
1486: String str;
1487: String sMonth, sDay, sHour, sMin, sSec;
1488:
1489: sMonth = (dt.getMonth() + 1 < 10 ? "0"
1490: + String.valueOf((dt.getMonth() + 1)) : String
1491: .valueOf(dt.getMonth() + 1));
1492: sDay = (dt.getDate() < 10 ? "0"
1493: + String.valueOf(dt.getDate()) : String.valueOf(dt
1494: .getDate()));
1495: sHour = (dt.getHours() < 10 ? "0"
1496: + String.valueOf(dt.getHours()) : String.valueOf(dt
1497: .getHours()));
1498: sMin = (dt.getMinutes() < 10 ? "0"
1499: + String.valueOf(dt.getMinutes()) : String
1500: .valueOf(dt.getMinutes()));
1501: sSec = (dt.getSeconds() < 10 ? "0"
1502: + String.valueOf(dt.getSeconds()) : String
1503: .valueOf(dt.getSeconds()));
1504:
1505: switch (iDBMS) {
1506:
1507: case JDCConnection.DBMS_MSSQL:
1508: str = "{ " + sFormat.toLowerCase() + " '";
1509:
1510: str += String.valueOf(dt.getYear() + 1900) + "-"
1511: + sMonth + "-" + sDay + " ";
1512:
1513: if (sFormat.equalsIgnoreCase("ts")) {
1514: str += sHour + ":" + sMin + ":" + sSec;
1515: }
1516:
1517: str = str.trim() + "'}";
1518: break;
1519:
1520: case JDCConnection.DBMS_ORACLE:
1521: if (sFormat.equalsIgnoreCase("ts"))
1522: str = "TO_DATE('"
1523: + String.valueOf(dt.getYear() + 1900) + "-"
1524: + sMonth + "-" + sDay + " " + sHour + ":"
1525: + sMin + ":" + sSec
1526: + "','YYYY-MM-DD HH24-MI-SS')";
1527: else
1528: str = "TO_DATE('"
1529: + String.valueOf(dt.getYear() + 1900) + "-"
1530: + sMonth + "-" + sDay + "','YYYY-MM-DD')";
1531: break;
1532:
1533: case JDCConnection.DBMS_POSTGRESQL:
1534: if (sFormat.equalsIgnoreCase("ts"))
1535: str = "TIMESTAMP '"
1536: + String.valueOf(dt.getYear() + 1900) + "-"
1537: + sMonth + "-" + sDay + " " + sHour + ":"
1538: + sMin + ":" + sSec + "'";
1539: else
1540: str = "DATE '"
1541: + String.valueOf(dt.getYear() + 1900) + "-"
1542: + sMonth + "-" + sDay + "'";
1543: break;
1544:
1545: case JDCConnection.DBMS_MYSQL:
1546: if (sFormat.equalsIgnoreCase("ts"))
1547: str = "CAST('"
1548: + String.valueOf(dt.getYear() + 1900) + "-"
1549: + sMonth + "-" + sDay + " " + sHour + ":"
1550: + sMin + ":" + sSec + "' AS DATETIME)";
1551: else
1552: str = "CAST('"
1553: + String.valueOf(dt.getYear() + 1900) + "-"
1554: + sMonth + "-" + sDay + "' AS DATE)";
1555: break;
1556: default:
1557: throw new UnsupportedOperationException(
1558: "DBBind.Functions.escape(Date,String) unsupported DBMS");
1559: } // end switch()
1560:
1561: return str;
1562: } // escape()
1563:
1564: // -------------------------------------------------------------------------
1565:
1566: public static String toChar(Object oData, int iLength)
1567: throws UnsupportedOperationException {
1568: String sRetVal;
1569:
1570: switch (iDBMS) {
1571: case JDCConnection.DBMS_ORACLE:
1572: sRetVal = "TO_CHAR(" + oData.toString() + ")";
1573: break;
1574: case JDCConnection.DBMS_MYSQL:
1575: sRetVal = "CAST(" + oData.toString() + " AS CHAR)";
1576: break;
1577: case JDCConnection.DBMS_POSTGRESQL:
1578: case JDCConnection.DBMS_MSSQL:
1579: sRetVal = "CAST(" + oData.toString() + " AS VARCHAR("
1580: + String.valueOf(iLength) + "))";
1581: break;
1582: default:
1583: throw new UnsupportedOperationException(
1584: "DBBind.Functions.toChar(Date,String) unsupported DBMS");
1585: }
1586:
1587: return sRetVal;
1588: } // toChar()
1589:
1590: } // Functions
1591:
1592: // ===========================================================================
1593:
1594: public static final int DBMS_GENERIC = 0;
1595: public static final int DBMS_MYSQL = 1;
1596: public static final int DBMS_POSTGRESQL = 2;
1597: public static final int DBMS_MSSQL = 3;
1598: public static final int DBMS_ORACLE = 5;
1599:
1600: private static final int DBMS_UNKNOWN = -1;
1601: private static final int DBMS_SYBASE = 4;
1602: private static final int DBMS_B2 = 6;
1603: private static final int DBMS_INFORMIX = 7;
1604:
1605: private static final String DBMSNAME_MSSQL = "Microsoft SQL Server";
1606: private static final String DBMSNAME_POSTGRESQL = "PostgreSQL";
1607: private static final String DBMSNAME_ORACLE = "Oracle";
1608: private static final String DBMSNAME_MYSQL = "MySQL";
1609:
1610: } // DBBind
|