0001: /*
0002: * The contents of this file are subject to the
0003: * Mozilla Public License Version 1.1 (the "License");
0004: * you may not use this file except in compliance with the License.
0005: * You may obtain a copy of the License at http://www.mozilla.org/MPL/
0006: *
0007: * Software distributed under the License is distributed on an "AS IS"
0008: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
0009: * See the License for the specific language governing rights and
0010: * limitations under the License.
0011: *
0012: * The Initial Developer of the Original Code is Simulacra Media Ltd.
0013: * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
0014: *
0015: * All Rights Reserved.
0016: *
0017: * Contributor(s):
0018: */
0019: package org.openharmonise.commons.dsi.impl;
0020:
0021: import java.sql.*;
0022: import java.sql.Date;
0023: import java.text.*;
0024: import java.util.*;
0025: import java.util.logging.*;
0026: import java.util.logging.Level;
0027:
0028: import org.openharmonise.commons.dsi.*;
0029: import org.openharmonise.commons.dsi.ddl.*;
0030: import org.openharmonise.commons.dsi.dml.*;
0031: import org.openharmonise.commons.dsi.dml.functions.*;
0032:
0033: /**
0034: * Class providing interface to a Firebird database.
0035: *
0036: * @author Fidel Viegas
0037: * @see java.sql
0038: */
0039: public class DataStoreInterfaceFirebird extends
0040: AbstractDataStoreInterface {
0041:
0042: /**
0043: * Map containing keyword mappings
0044: */
0045: private static Map keywords = null;
0046:
0047: /**
0048: * List of join tables
0049: */
0050: private static List joinTables = new Vector();
0051: private static final String TYPE_NTEXT = "BLOB SUB_TYPE TEXT CHARACTER SET UNICODE_FSS";
0052: private static final String TYPE_NVARCHAR_255 = "NCHAR VARYING (255)";
0053: private static final String TYPE_INT = "INT";
0054: private static final String KEYWORD_PRIMARY_KEY = "PRIMARY KEY";
0055: private static final String KEYWORD_UNIQUE = "UNIQUE";
0056: private static final String KEYWORD_DEFAULT = "DEFAULT";
0057: private static final String KEYWORD_NOT_NULL = "NOT NULL";
0058: private static final String KEYWORD_NULL = "NULL";
0059: private static final String KEYWORD_FOREIGN_KEY = "FOREIGN KEY";
0060: private static final String KEYWORD_REFERENCES = "REFERENCES";
0061:
0062: /**
0063: * Logger for this class
0064: */
0065: private static final Logger m_logger = Logger
0066: .getLogger(DataStoreInterfaceFirebird.class.getName());
0067:
0068: //initialise data
0069: static {
0070: DB_DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
0071: keywords = new Hashtable();
0072: // add the keywords with the respective mapping
0073: keywords.put("password", "\"password\"");
0074: keywords.put("page", "\"page\"");
0075: keywords.put("value", "\"value\"");
0076: keywords.put("output_type", "\"output_type\"");
0077: keywords.put("type", "\"type\"");
0078: keywords.put("event", "\"event\"");
0079: keywords.put("timestamp", "\"timestamp\"");
0080: keywords.put("action", "\"action\"");
0081: }
0082:
0083: /**
0084: * Constructs a firebird data store interface with no DB settings.
0085: */
0086: public DataStoreInterfaceFirebird() {
0087: ;
0088: }
0089:
0090: /**
0091: * Constructs a firebird data store interface with the given
0092: * DB settings.
0093: *
0094: * @param sJDBCDriver the JDBC driver class name
0095: * @param sDBurl the database URI
0096: * @param sDBUsr the database user name
0097: * @param sDBPwd the database user password
0098: */
0099: public DataStoreInterfaceFirebird(String sJDBCDriver,
0100: String sDBurl, String sDBUsr, String sDBPwd) {
0101: super (sJDBCDriver, sDBurl, sDBUsr, sDBPwd);
0102: }
0103:
0104: /**
0105: * Constructs a firebird data store interface with the given connection
0106: * type.
0107: *
0108: * @param nConnectionType the connection type
0109: * @throws DataStoreException if an error occurs
0110: */
0111: public DataStoreInterfaceFirebird(int nConnectionType)
0112: throws DataStoreException {
0113: super (nConnectionType);
0114: }
0115:
0116: /* (non-Javadoc)
0117: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
0118: */
0119: public int getSequenceNextValue(String sSeqName)
0120: throws DataStoreException, SQLException {
0121: Connection conn = null;
0122: ResultSet rs = null;
0123: Statement stmt = null;
0124: String sSql = null;
0125: int nSeq = -1;
0126:
0127: conn = getConnection();
0128:
0129: stmt = conn.createStatement();
0130:
0131: sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='"
0132: + sSeqName + "'";
0133: stmt.executeUpdate(sSql);
0134:
0135: sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName
0136: + "'";
0137: rs = stmt.executeQuery(sSql);
0138:
0139: if (rs.next()) {
0140: nSeq = rs.getInt(1);
0141: } else {
0142: throw new DataStoreException("Sequence [" + sSeqName
0143: + "] not found.");
0144: }
0145:
0146: if (rs != null) {
0147: rs.close();
0148: }
0149:
0150: if (stmt != null) {
0151: stmt.close();
0152: }
0153:
0154: if (isPooledConnection() && (conn != null)) {
0155: this .closeConnection(conn);
0156: }
0157:
0158: return nSeq;
0159: }
0160:
0161: /* (non-Javadoc)
0162: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#insertClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
0163: */
0164: public void insertClob(String sTable, String sColumn, String sClob,
0165: String sCondition) throws DataStoreException {
0166: Connection conn = null;
0167: Statement stmt = null;
0168: ResultSet rs = null;
0169:
0170: if ((sCondition == null) || (sCondition.length() == 0)) {
0171: throw new DataStoreException("Missing CLOB condition");
0172: }
0173:
0174: StringBuffer sSql = new StringBuffer();
0175:
0176: try {
0177: conn = getConnection();
0178: stmt = conn.createStatement();
0179:
0180: sSql.append("update ");
0181:
0182: if (keywords.containsKey(sTable) == true) {
0183: sSql.append(keywords.get(sTable));
0184: } else {
0185: sSql.append(sTable);
0186: }
0187:
0188: sSql.append(" set ");
0189:
0190: if (keywords.containsKey(sColumn) == true) {
0191: sSql.append(keywords.get(sColumn));
0192:
0193: } else {
0194: sSql.append(sColumn);
0195: }
0196:
0197: sSql.append(" = '");
0198: sSql.append(addEscapeChars(sClob));
0199: sSql.append("' where ");
0200: sSql.append(sCondition);
0201:
0202: stmt.execute(sSql.toString());
0203:
0204: if (rs != null) {
0205: rs.close();
0206: }
0207:
0208: if (stmt != null) {
0209: stmt.close();
0210: }
0211:
0212: if (isPooledConnection() && (conn != null)) {
0213: this .closeConnection(conn);
0214: }
0215: } catch (SQLException e) {
0216: throw new DataStoreException("SQLException: "
0217: + e.getMessage());
0218: }
0219: }
0220:
0221: /* (non-Javadoc)
0222: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
0223: */
0224: public void updateClob(String sTable, String sColumn, String sClob,
0225: String sCondition) throws DataStoreException {
0226: Connection conn = null;
0227: Statement stmt = null;
0228: ResultSet rs = null;
0229:
0230: if ((sCondition == null) || (sCondition.length() == 0)) {
0231: throw new DataStoreException("Missing CLOB condition");
0232: }
0233:
0234: StringBuffer sSql = new StringBuffer();
0235:
0236: try {
0237: conn = getConnection();
0238: stmt = conn.createStatement();
0239:
0240: sSql.append("update ");
0241:
0242: if (keywords.containsKey(sTable) == true) {
0243: sSql.append(keywords.get(sTable));
0244: } else {
0245: sSql.append(sTable);
0246: }
0247:
0248: sSql.append(" set ");
0249:
0250: if (keywords.containsKey(sColumn) == true) {
0251: sSql.append(keywords.get(sColumn));
0252:
0253: } else {
0254: sSql.append(sColumn);
0255: }
0256:
0257: sSql.append(" = '");
0258: sSql.append(addEscapeChars(sClob));
0259: sSql.append("' where ");
0260: sSql.append(sCondition);
0261:
0262: stmt.execute(sSql.toString());
0263:
0264: if (rs != null) {
0265: rs.close();
0266: }
0267:
0268: if (stmt != null) {
0269: stmt.close();
0270: }
0271:
0272: if (isPooledConnection() && (conn != null)) {
0273: this .closeConnection(conn);
0274: }
0275: } catch (SQLException e) {
0276: throw new DataStoreException("SQLException: "
0277: + e.getMessage());
0278: }
0279: }
0280:
0281: /* (non-Javadoc)
0282: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
0283: */
0284: public String getClob(String sTable, String sColumn,
0285: String sCondition) throws DataStoreException {
0286: Connection conn = null;
0287: Statement stmt = null;
0288: ResultSet rs = null;
0289: String sReturn = null;
0290:
0291: if ((sCondition == null) || (sCondition.length() == 0)) {
0292: throw new DataStoreException("Missing CLOB condition");
0293: }
0294:
0295: StringBuffer sSql = new StringBuffer();
0296:
0297: try {
0298: conn = getConnection();
0299:
0300: stmt = conn.createStatement();
0301:
0302: sSql.append("select ");
0303:
0304: if (keywords.containsKey(sColumn) == true) {
0305: sSql.append(keywords.get(sColumn));
0306:
0307: } else {
0308: sSql.append(sColumn);
0309: }
0310:
0311: sSql.append(" from ");
0312:
0313: if (keywords.containsKey(sTable) == true) {
0314: sSql.append(keywords.get(sTable));
0315: } else {
0316: sSql.append(sTable);
0317: }
0318:
0319: sSql.append(" where ");
0320: sSql.append(sCondition);
0321:
0322: try {
0323: rs = stmt.executeQuery(sSql.toString());
0324: } catch (SQLException e) {
0325: throw new DataStoreException("Error Executing query:"
0326: + sSql.toString(), e);
0327: }
0328:
0329: if (rs.next()) {
0330: sReturn = rs.getString(1);
0331: }
0332:
0333: if (rs != null) {
0334: rs.close();
0335: }
0336:
0337: if (stmt != null) {
0338: stmt.close();
0339: }
0340:
0341: if (isPooledConnection() && (conn != null)) {
0342: this .closeConnection(conn);
0343: }
0344: } catch (SQLException e) {
0345: throw new DataStoreException("SQLException: ", e);
0346: }
0347:
0348: return sReturn;
0349: }
0350:
0351: /* (non-Javadoc)
0352: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
0353: */
0354: protected String addEscapeChars(String sOldString) {
0355: int marker = -1;
0356: int lastmarker = 0;
0357: int quotemarker = -1;
0358:
0359: if (sOldString == null) {
0360: return "";
0361: }
0362:
0363: StringBuffer sBuf = new StringBuffer();
0364:
0365: quotemarker = sOldString.indexOf("'");
0366:
0367: if (quotemarker >= 0) {
0368: marker = quotemarker;
0369: }
0370:
0371: if (marker < 0) {
0372: return sOldString;
0373: } else {
0374: while (marker >= 0) {
0375: //append to stringbuffer
0376: sBuf.append(sOldString.substring(lastmarker, marker));
0377: sBuf.append("'");
0378:
0379: //reset markers
0380: quotemarker = -1;
0381: lastmarker = marker;
0382:
0383: quotemarker = sOldString.indexOf("'", marker + 1);
0384:
0385: if (quotemarker >= 0) {
0386: marker = quotemarker;
0387: } else {
0388: marker = -1;
0389: }
0390: }
0391:
0392: sBuf.append(sOldString.substring(lastmarker));
0393:
0394: return (sBuf.toString());
0395: }
0396: }
0397:
0398: /* (non-Javadoc)
0399: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
0400: */
0401: protected String getFunction(Function func)
0402: throws DataStoreException {
0403: String sFunc = null;
0404:
0405: if (func instanceof Substring) {
0406: sFunc = getSubstring((Substring) func);
0407:
0408: } else if (func instanceof Length) {
0409: sFunc = getLength((Length) func);
0410:
0411: } else if (func instanceof ToDate) {
0412: sFunc = getToDate((ToDate) func);
0413:
0414: } else {
0415: throw new DataStoreException("Function not supported - "
0416: + func.getClass().getName());
0417: }
0418:
0419: return sFunc;
0420: }
0421:
0422: /**
0423: * Returns the SQL 'substring' statement from the given
0424: * <code>Substring</code> <code>Function</code>
0425: *
0426: * @param substr the substring <code>Function</code>
0427: * @return the SQL 'substring' statement
0428: * @throws DataStoreException if an error occurs
0429: */
0430: private String getSubstring(Substring substr)
0431: throws DataStoreException {
0432:
0433: StringBuffer strbuf = new StringBuffer();
0434:
0435: strbuf.append("SUBSTR('").append(substr.getString()).append(
0436: "',");
0437:
0438: Object objStart = substr.getStart();
0439:
0440: if (objStart instanceof Integer) {
0441: strbuf.append(((Integer) objStart).toString());
0442: } else if (objStart instanceof String) {
0443: strbuf.append((String) objStart);
0444: } else if (objStart instanceof Function) {
0445: strbuf.append(getFunction((Function) objStart));
0446: }
0447:
0448: strbuf.append(",");
0449:
0450: Object objEnd = substr.getFinish();
0451:
0452: if (objEnd instanceof Integer) {
0453: strbuf.append(((Integer) objEnd).toString());
0454: } else if (objEnd instanceof String) {
0455: strbuf.append((String) objEnd);
0456: } else if (objEnd instanceof Function) {
0457: strbuf.append(getFunction((Function) objEnd));
0458: }
0459:
0460: strbuf.append(")");
0461:
0462: return strbuf.toString();
0463: }
0464:
0465: /**
0466: * Returns the SQL 'length' statement from the given
0467: * <code>Length</code> <code>Function</code>.
0468: *
0469: * @param func the length function
0470: * @return the SQL 'length' statement
0471: * @throws DataStoreException if an error occurs
0472: */
0473: private String getLength(Length func) throws DataStoreException {
0474: StringBuffer strbuf = new StringBuffer();
0475:
0476: strbuf.append("STRLEN(");
0477:
0478: Object lenObj = func.getLengthObject();
0479:
0480: if (lenObj instanceof String) {
0481: strbuf.append(lenObj);
0482: } else if (lenObj instanceof ColumnRef) {
0483: strbuf.append(((ColumnRef) lenObj).getFullRef());
0484: }
0485:
0486: strbuf.append(")");
0487:
0488: return strbuf.toString();
0489: }
0490:
0491: /**
0492: * Returns the SQL 'todate' statement for the given
0493: * <code>ToDate</code> <code>Function</code>.
0494: *
0495: * @param date the function
0496: * @return the SQL 'todate' statement
0497: */
0498: private String getToDate(ToDate date) {
0499: StringBuffer strbuf = new StringBuffer();
0500:
0501: strbuf.append("CONVERT(datetime,");
0502:
0503: Object objVal = date.getValue();
0504:
0505: if (objVal instanceof ColumnRef) {
0506: strbuf.append(((ColumnRef) objVal).getFullRef());
0507: } else if (objVal instanceof String) {
0508: strbuf.append("'").append(objVal).append("'");
0509: }
0510:
0511: strbuf.append(", 120 )");
0512: return strbuf.toString();
0513: }
0514:
0515: /* (non-Javadoc)
0516: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
0517: */
0518: protected String getDateAsSQL(String date) {
0519: // localise this, as it is different for different DB implementations,
0520: // Firebird server
0521: StringBuffer sSql = new StringBuffer();
0522:
0523: sSql.append("'").append(date).append("'");
0524:
0525: return sSql.toString();
0526: }
0527:
0528: /* (non-Javadoc)
0529: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
0530: */
0531: public String getDateDataType() {
0532: return "TIMESTAMP";
0533: }
0534:
0535: /* (non-Javadoc)
0536: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
0537: */
0538: public String getCLOBDataType() {
0539: return "BLOB SUB_TYPE TEXT CHARACTER SET UNICODE_FSS";
0540: }
0541:
0542: /* (non-Javadoc)
0543: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
0544: */
0545: public String getBooleanDataType() {
0546: return "BOOLEAN";
0547: }
0548:
0549: /* (non-Javadoc)
0550: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
0551: */
0552: public List getTableList() throws DataStoreException {
0553: Vector tables = new Vector();
0554: Connection con = getConnection();
0555: try {
0556: DatabaseMetaData dbMetaData = con.getMetaData(); // get the metadata
0557:
0558: // get the table names
0559: ResultSet rs = dbMetaData.getTables(null, null, "%",
0560: new String[] { "TABLE" });
0561:
0562: while (rs.next()) {
0563: tables.add(rs.getString("TABLE_NAME").trim()
0564: .toLowerCase());
0565: }
0566:
0567: rs.close();
0568: } catch (SQLException e) {
0569: throw new DataStoreException(e.getLocalizedMessage(), e);
0570: }
0571:
0572: return tables;
0573: }
0574:
0575: /* (non-Javadoc)
0576: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
0577: */
0578: public List getSequenceList() throws DataStoreException {
0579: Vector seqs = new Vector();
0580: ResultSet rs = null;
0581:
0582: try {
0583: rs = executeQuery("select seq_name from oh_seq order by seq_name");
0584:
0585: while (rs.next()) {
0586: seqs.add(rs.getString(1).trim());
0587: }
0588:
0589: rs.close();
0590: } catch (SQLException e) {
0591: throw new DataStoreException(e.getLocalizedMessage(), e);
0592: }
0593:
0594: return seqs;
0595: }
0596:
0597: /**
0598: * @param ref1
0599: * @param ref2
0600: * @param DML
0601: * @return
0602: */
0603: public String getOuterJoinCondition(ColumnRef ref1, ColumnRef ref2,
0604: AbstractDMLStatement DML) {
0605: StringBuffer sSql = new StringBuffer();
0606:
0607: String sTable1 = ref1.getTable(); // get the table name
0608: String sTable2 = ref2.getTable();
0609: String sColumn1 = ref1.getColumn();
0610: String sColumn2 = ref2.getColumn();
0611:
0612: sSql.append(" right outer join ");
0613:
0614: String sRealTable = null;
0615:
0616: try {
0617: if (joinTables.contains(sTable1) == true) {
0618: if (DML.isAlias(sTable2) == true) {
0619: sRealTable = DML.getTableName(sTable2);
0620:
0621: if (keywords.containsKey(sRealTable) == true) {
0622: sSql.append(keywords.get(sRealTable));
0623: } else {
0624: sSql.append(sRealTable);
0625: }
0626:
0627: sSql.append(" ");
0628: }
0629:
0630: if (keywords.containsKey(sTable2) == true) {
0631: sSql.append(keywords.get(sTable2));
0632: } else {
0633: sSql.append(sTable2);
0634: }
0635: } else if (joinTables.contains(sTable2) == true) {
0636: if (DML.isAlias(sTable1) == true) {
0637: sRealTable = DML.getTableName(sTable1);
0638:
0639: if (keywords.containsKey(sRealTable) == true) {
0640: sSql.append(keywords.get(sRealTable));
0641: } else {
0642: sSql.append(sRealTable);
0643: }
0644:
0645: sSql.append(" ");
0646: }
0647: if (keywords.containsKey(sTable1) == true) {
0648: sSql.append(keywords.get(sTable1));
0649: } else {
0650: sSql.append(sTable1);
0651: }
0652: }
0653: } catch (DataStoreException e) {
0654: m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
0655: }
0656:
0657: sSql.append(" on (");
0658:
0659: if (keywords.containsKey(sTable1) == true) {
0660: sSql.append(keywords.get(sTable1));
0661: } else {
0662: sSql.append(sTable1);
0663: }
0664:
0665: sSql.append(".");
0666:
0667: if (keywords.containsKey(sColumn1) == true) {
0668: sSql.append(keywords.get(sColumn1));
0669: } else {
0670: sSql.append(sColumn1);
0671: }
0672:
0673: sSql.append("=");
0674:
0675: if (keywords.containsKey(sTable2) == true) {
0676: sSql.append(keywords.get(sTable2));
0677: } else {
0678: sSql.append(sTable2);
0679: }
0680:
0681: sSql.append(".");
0682:
0683: if (keywords.containsKey(sColumn2) == true) {
0684: sSql.append(keywords.get(sColumn2));
0685: } else {
0686: sSql.append(sColumn2);
0687: }
0688:
0689: sSql.append(") ");
0690:
0691: return sSql.toString();
0692: }
0693:
0694: /**
0695: * Returns the SQL inner join condition for the two given column references.
0696: *
0697: * @param ref1 the first column reference
0698: * @param ref2 the second column reference
0699: * @return the SQL inner join condition
0700: */
0701: public String getInnerJoinCondition(ColumnRef ref1, ColumnRef ref2) {
0702: StringBuffer sSql = new StringBuffer();
0703:
0704: String sTable1 = ref1.getTable(); // get the table name
0705: String sTable2 = ref2.getTable();
0706: String sColumn1 = ref1.getColumn();
0707: String sColumn2 = ref2.getColumn();
0708:
0709: if (keywords.containsKey(sTable1) == true) {
0710: sSql.append(keywords.get(sTable1));
0711: } else {
0712: sSql.append(sTable1);
0713: }
0714:
0715: sSql.append(".");
0716:
0717: if (keywords.containsKey(sColumn1) == true) {
0718: sSql.append(keywords.get(sColumn1));
0719: } else {
0720: sSql.append(sColumn1);
0721: }
0722:
0723: sSql.append("=");
0724:
0725: if (keywords.containsKey(sTable2) == true) {
0726: sSql.append(keywords.get(sTable2));
0727: } else {
0728: sSql.append(sTable2);
0729: }
0730:
0731: sSql.append(".");
0732:
0733: if (keywords.containsKey(sColumn2) == true) {
0734: sSql.append(keywords.get(sColumn2));
0735: } else {
0736: sSql.append(sColumn2);
0737: }
0738: return sSql.toString();
0739: }
0740:
0741: /* (non-Javadoc)
0742: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getInsertStatement(org.openharmonise.commons.dsi.dml.InsertStatement)
0743: */
0744: public String getInsertStatement(InsertStatement insert)
0745: throws DataStoreException {
0746: boolean bflag = false;
0747: Vector saValues = new Vector(16);
0748: Vector ColRefs = new Vector(16);
0749:
0750: Map ValuePairs = insert.getColumnValuePairs();
0751:
0752: Set set = ValuePairs.keySet();
0753:
0754: Iterator iter = set.iterator();
0755:
0756: while (iter.hasNext()) {
0757: ColumnRef colref = (ColumnRef) iter.next();
0758: ColRefs.add(colref);
0759:
0760: saValues.add(ValuePairs.get(colref));
0761: }
0762:
0763: StringBuffer sSql = new StringBuffer();
0764:
0765: sSql.append("insert into ");
0766: String sTableName = ((ColumnRef) ColRefs.get(0)).getTable();
0767:
0768: // value and page are reserved works in interbase
0769: if (keywords.get(sTableName) != null) {
0770: sSql.append(keywords.get(sTableName));
0771: } else {
0772: sSql.append(sTableName);
0773: }
0774:
0775: if (insert.isColumnValuesBySelect()) {
0776: sSql.append(" ");
0777: sSql.append(getSelectStatement(insert
0778: .getColumnValuesSelect()));
0779: } else {
0780: sSql.append(" (");
0781: String sColumnName = null;
0782:
0783: for (int i = 0; i < ColRefs.size(); i++) {
0784: if (bflag) {
0785: sSql.append(",");
0786: }
0787: sColumnName = ((ColumnRef) ColRefs.get(i)).getColumn();
0788:
0789: // output_type, type and password are reserved words
0790: if (keywords.get(sColumnName) != null) {
0791: sSql.append(keywords.get(sColumnName));
0792:
0793: } else {
0794: sSql.append(sColumnName);
0795: }
0796:
0797: bflag = true;
0798: }
0799:
0800: sSql.append(") values (");
0801:
0802: for (int i = 0; i < saValues.size(); i++) {
0803: if (i > 0) {
0804: sSql.append(",");
0805: }
0806: try {
0807:
0808: if (saValues.get(i).getClass().getName()
0809: .equalsIgnoreCase("java.lang.String")) {
0810: sSql.append("'");
0811: sSql.append(addEscapeChars((String) saValues
0812: .get(i)));
0813: sSql.append("'");
0814: } else if (saValues.get(i).getClass().getName()
0815: .equalsIgnoreCase("java.lang.Integer")) {
0816: sSql.append((Integer) saValues.get(i));
0817: } else if (saValues.get(i) instanceof java.util.Date) {
0818: SimpleDateFormat date_formatter = new SimpleDateFormat(
0819: DB_DATEFORMAT);
0820: String sDate = date_formatter
0821: .format((java.util.Date) saValues
0822: .get(i));
0823: sSql.append(getDateAsSQL(sDate));
0824: } else if (saValues.get(i).getClass().getName()
0825: .equalsIgnoreCase(
0826: SelectStatement.class.getName())) {
0827: sSql
0828: .append(getSelectStatement((SelectStatement) saValues
0829: .get(i)));
0830: } else {
0831: throw new DataStoreException(
0832: "Error creating Insert statement: "
0833: + sSql.toString());
0834: }
0835: } catch (NullPointerException e) {
0836: throw new DataStoreException(
0837: "Null value in insert for "
0838: + ((ColumnRef) ColRefs.get(i))
0839: .getColumn());
0840: }
0841: }
0842:
0843: sSql.append(")");
0844: }
0845:
0846: return (sSql.toString());
0847: }
0848:
0849: /* (non-Javadoc)
0850: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getUpdateStatement(org.openharmonise.commons.dsi.dml.UpdateStatement)
0851: */
0852: public String getUpdateStatement(UpdateStatement update)
0853: throws DataStoreException {
0854: boolean bflag = false;
0855: Vector saValues = new Vector(16);
0856: Vector ColRefs = new Vector(16);
0857:
0858: Map ValuePairs = update.getColumnValuePairs();
0859:
0860: Set set = ValuePairs.keySet();
0861:
0862: Iterator iter = set.iterator();
0863:
0864: while (iter.hasNext()) {
0865: ColumnRef colref = (ColumnRef) iter.next();
0866: ColRefs.add(colref);
0867:
0868: saValues.add(ValuePairs.get(colref));
0869: }
0870:
0871: StringBuffer sSql = new StringBuffer();
0872:
0873: sSql.append("update ");
0874: String sTableName = ((ColumnRef) ColRefs.get(0)).getTable();
0875:
0876: // value and page are reserved works in interbase
0877: if (keywords.get(sTableName) != null) {
0878: sSql.append(keywords.get(sTableName));
0879: } else {
0880: sSql.append(sTableName);
0881: }
0882:
0883: sSql.append(" set ");
0884: String sColumnName = null;
0885:
0886: for (int i = 0; i < ColRefs.size(); i++) {
0887: if (bflag) {
0888: sSql.append(",");
0889: }
0890:
0891: // value and page are reserved works in interbase
0892: if (keywords.get(sTableName) != null) {
0893: sSql.append(keywords.get(sTableName));
0894: } else {
0895: sSql.append(sTableName);
0896: }
0897:
0898: sSql.append(".");
0899:
0900: sColumnName = ((ColumnRef) ColRefs.get(i)).getColumn();
0901:
0902: // output_type, type and password are reserved words
0903: if (keywords.get(sColumnName) != null) {
0904: sSql.append(keywords.get(sColumnName));
0905: } else {
0906: sSql.append(sColumnName);
0907: }
0908:
0909: sSql.append("=");
0910:
0911: if (saValues.get(i) == null) {
0912: sSql.append("null");
0913: } else if (saValues.get(i).getClass().getName()
0914: .equalsIgnoreCase("java.lang.String")) {
0915: sSql.append("'");
0916: sSql.append(addEscapeChars((String) saValues.get(i)));
0917: sSql.append("'");
0918: } else if (saValues.get(i).getClass().getName()
0919: .equalsIgnoreCase("java.lang.Integer")) {
0920: sSql.append(((Integer) saValues.get(i)).toString());
0921: } else if (saValues.get(i).getClass().getName()
0922: .equalsIgnoreCase("java.util.Date")
0923: || saValues.get(i).getClass().getName()
0924: .equalsIgnoreCase("java.sql.Date")) {
0925: SimpleDateFormat date_formatter = new SimpleDateFormat(
0926: DB_DATEFORMAT);
0927: String sDate = date_formatter
0928: .format((java.util.Date) saValues.get(i));
0929: sSql.append(getDateAsSQL(sDate));
0930: } else {
0931: throw new DataStoreException(
0932: "Error creating Update statement: "
0933: + sSql.toString());
0934: }
0935:
0936: bflag = true;
0937: }
0938:
0939: sSql.append(" where ");
0940:
0941: sSql.append(generateWhereClause(update));
0942:
0943: return (sSql.toString());
0944: }
0945:
0946: /* (non-Javadoc)
0947: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDeleteStatement(org.openharmonise.commons.dsi.dml.DeleteStatement)
0948: */
0949: public String getDeleteStatement(DeleteStatement delete)
0950: throws DataStoreException {
0951: String sTable = delete.getTable();
0952:
0953: if (!delete.hasWhereClause()) {
0954: throw new DataStoreException(
0955: "Delete statements without Where clauses are not allowed.");
0956: }
0957:
0958: WhereConditionGroup where = delete.getWhereConditions();
0959:
0960: StringBuffer sSql = new StringBuffer();
0961:
0962: sSql.append("delete from ");
0963:
0964: if (keywords.get(sTable) != null) {
0965: sSql.append(keywords.get(sTable));
0966: } else {
0967: sSql.append(sTable);
0968: }
0969:
0970: sSql.append(" where ");
0971: sSql.append(generateWhereClause(delete));
0972:
0973: return (sSql.toString());
0974: }
0975:
0976: /* (non-Javadoc)
0977: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSelectStatement(org.openharmonise.commons.dsi.dml.SelectStatement)
0978: */
0979: public String getSelectStatement(SelectStatement select)
0980: throws DataStoreException {
0981: List SelectCols = select.getSelectColumns();
0982: List maxcols = select.getSelectMaxColumns();
0983: JoinConditions outerJoins = null;
0984: JoinConditions innerJoins = null;
0985: WhereConditionGroup where = select.getWhereConditions();
0986:
0987: joinTables.clear(); // clear the join tables before proceeding
0988:
0989: Set colSet = select.getOrderByColumns();
0990:
0991: StringBuffer sSql = new StringBuffer();
0992:
0993: sSql.append("select ");
0994:
0995: if (select.isLimit()) {
0996: sSql.append("first ");
0997: sSql.append(select.getLimit());
0998: sSql.append(" ");
0999: }
1000:
1001: if (select.isDistinct()) {
1002: sSql.append("distinct ");
1003: }
1004:
1005: if ((SelectCols == null) || (SelectCols.size() == 0)) {
1006: sSql.append("*");
1007: } else {
1008: boolean bMax = false;
1009:
1010: for (int i = 0; i < SelectCols.size(); i++) {
1011: if ((maxcols != null)
1012: && maxcols.contains(new Integer(i))) {
1013: bMax = true;
1014: }
1015:
1016: if (i > 0) {
1017: sSql.append(",");
1018: }
1019:
1020: if (SelectCols.get(i).getClass().getName()
1021: .equalsIgnoreCase("java.lang.Integer")) {
1022: sSql.append((Integer) SelectCols.get(i));
1023: } else {
1024: if (bMax) {
1025: sSql.append("max(");
1026: }
1027:
1028: ColumnRef colRef = (ColumnRef) SelectCols.get(i);
1029: // get the column reference
1030:
1031: String sTable = colRef.getTable(); // get the table
1032: String sColumn = colRef.getColumn(); // get the column
1033:
1034: if (keywords.containsKey(sTable) == true) {
1035: sSql.append(keywords.get(sTable));
1036: } else {
1037: sSql.append(sTable);
1038: }
1039:
1040: sSql.append(".");
1041:
1042: if (keywords.containsKey(sColumn) == true) {
1043: sSql.append(keywords.get(sColumn));
1044: } else {
1045: sSql.append(sColumn);
1046: }
1047:
1048: if (bMax) {
1049: sSql.append(")");
1050: }
1051: }
1052: }
1053:
1054: if (colSet.isEmpty() == false) {
1055: Iterator iter = colSet.iterator();
1056: int i = 0;
1057: while (iter.hasNext()) {
1058: ColumnRef ordercol = (ColumnRef) iter.next();
1059: sSql.append(",");
1060: sSql.append(getOrderByRef(ordercol));
1061: sSql.append(" AS upperCol").append(i++);
1062: }
1063:
1064: }
1065: }
1066:
1067: sSql.append(" from ");
1068:
1069: Vector forTables = new Vector();
1070: String sTable = null;
1071:
1072: if (select.hasJoinConditions()) {
1073: outerJoins = select.getJoinConditions().getOuterJoins();
1074: innerJoins = select.getJoinConditions().getInnerJoins();
1075: // add all the where tables into the
1076: // forTables list
1077: if (select.hasWhereConditions()) {
1078: List wvec = where.getTableList();
1079: for (int i = 0; i < wvec.size(); i++) {
1080: sTable = (String) wvec.get(i);
1081: if (forTables.contains(sTable) == false) {
1082: forTables.add(wvec.get(i));
1083: }
1084: }
1085: }
1086:
1087: // Now we add all the table list from the
1088: // inner joins to the forTables list
1089: List jvec = innerJoins.getTableList(); // gets the list of inner joins
1090: for (int i = 0; i < jvec.size(); i++) {
1091: sTable = (String) jvec.get(i);
1092: if (forTables.contains(sTable) == false) {
1093: forTables.add(sTable);
1094: }
1095: }
1096:
1097: // this part creates a list of tables
1098: // that go into the for list
1099: for (int i = 0; i < outerJoins.size(); i++) {
1100: String sTable1 = outerJoins.getLeftTableName(i);
1101: String sTable2 = outerJoins.getRightTableName(i);
1102:
1103: if (forTables.contains(sTable1) == true) {
1104: if (forTables.contains(sTable2) == true) {
1105: forTables.remove(sTable2);
1106: }
1107: } else if (forTables.contains(sTable1) == false
1108: && forTables.contains(sTable2) == false) {
1109: forTables.add(sTable1);
1110: }
1111: }
1112:
1113: joinTables.addAll(forTables); // add it to the general joinTables
1114: } else if (SelectCols.size() > 0) {
1115: forTables = new Vector(1);
1116: String sTempTable = ((ColumnRef) SelectCols.get(0))
1117: .getTable();
1118: forTables.add(sTempTable);
1119: } else {
1120: forTables = new Vector(1);
1121: if (select.hasWhereConditions()) {
1122: String sTempTable = null;
1123:
1124: List wvec = where.getTableList();
1125: sTempTable = (String) wvec.get(0);
1126:
1127: forTables.add(sTempTable);
1128: }
1129: }
1130:
1131: // generate the from clause
1132: sSql.append(generateFromClause(select, forTables));
1133:
1134: if (select.hasJoinConditions() == true) {
1135: for (int i = 0; i < outerJoins.size(); i++) {
1136: sSql.append(" ");
1137: // this is a continuing part.
1138: // think it over.
1139: sSql.append(getOuterJoinCondition(outerJoins
1140: .getLeftColumnRef(i), outerJoins
1141: .getRightColumnRef(i), select));
1142: }
1143: }
1144:
1145: if (select.hasWhereClause()) {
1146: sSql.append(" where ");
1147:
1148: if ((innerJoins != null) && (innerJoins.size() > 0)) {
1149: // append all the inner joins
1150: for (int i = 0; i < innerJoins.size(); i++) {
1151: sSql.append(" ");
1152:
1153: if (i > 0) {
1154: sSql.append("and ");
1155: }
1156:
1157: // this is a continuing part.
1158: // think it over.
1159: sSql.append(getInnerJoinCondition(innerJoins
1160: .getLeftColumnRef(i), innerJoins
1161: .getRightColumnRef(i)));
1162: }
1163: sSql.append(" and");
1164: }
1165:
1166: sSql.append(generateWhereClause(select));
1167: }
1168:
1169: if (colSet.isEmpty() == false) {
1170: sSql.append(generateOrderByClause(select));
1171: }
1172:
1173: return (sSql.toString());
1174: }
1175:
1176: /* (non-Javadoc)
1177: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#generateOrderByClause(org.openharmonise.commons.dsi.dml.SelectStatement)
1178: */
1179: protected String generateOrderByClause(SelectStatement select) {
1180: StringBuffer sSql = new StringBuffer();
1181: Set orderColSet = select.getOrderByColumns();
1182:
1183: Iterator iter = orderColSet.iterator();
1184: int i = 0;
1185:
1186: if (iter.hasNext()) {
1187: sSql.append(" order by ");
1188:
1189: while (iter.hasNext()) {
1190: ColumnRef ordercol = (ColumnRef) iter.next();
1191:
1192: int nOrderColType = ordercol.getDataType();
1193:
1194: if ((nOrderColType == ColumnRef.TEXT)
1195: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1196: sSql.append("upperCol").append(i++);
1197: } else {
1198: sSql.append(getOrderByRef(ordercol));
1199: }
1200:
1201: sSql.append(" ");
1202: sSql.append(select.getOrderByDirection(ordercol));
1203: if (iter.hasNext()) {
1204: sSql.append(",");
1205: }
1206: }
1207: }
1208:
1209: return (sSql.toString());
1210: }
1211:
1212: /* (non-Javadoc)
1213: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getOrderByRef(org.openharmonise.commons.dsi.ColumnRef)
1214: */
1215: protected String getOrderByRef(ColumnRef ordercol) {
1216: StringBuffer sSql = new StringBuffer();
1217: int nOrderColType = ordercol.getDataType();
1218:
1219: if ((nOrderColType == ColumnRef.TEXT)
1220: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1221: sSql.append("upper(");
1222: }
1223:
1224: if (nOrderColType == ColumnRef.LONG_TEXT) {
1225: sSql.append("cast (");
1226: }
1227:
1228: String sTable = ordercol.getTable();
1229:
1230: if (ordercol.hasTableAlias() == true) {
1231: sTable = ordercol.getTableAlias();
1232: }
1233:
1234: if (keywords.get(sTable) != null) {
1235: sSql.append(keywords.get(sTable));
1236: } else {
1237: sSql.append(sTable);
1238: }
1239:
1240: sSql.append(".");
1241:
1242: String sColumn = ordercol.getColumn();
1243:
1244: if (keywords.get(sColumn) != null) {
1245: sSql.append(keywords.get(sColumn));
1246: } else {
1247: sSql.append(sColumn);
1248: }
1249:
1250: if (nOrderColType == ColumnRef.LONG_TEXT) {
1251: sSql.append(" as char(80)");
1252: }
1253:
1254: if (nOrderColType == ColumnRef.LONG_TEXT) {
1255: sSql.append(")");
1256: }
1257:
1258: if ((nOrderColType == ColumnRef.TEXT)
1259: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1260: sSql.append(")");
1261: }
1262:
1263: return sSql.toString();
1264: }
1265:
1266: /**
1267: * Generate 'from' clause from the given DML statment.
1268: *
1269: * @param DML the DML statement
1270: * @param saTables the list of table names from the DML statment
1271: * @return the 'from' clause
1272: * @throws DataStoreException if an error occurs
1273: */
1274: protected String generateFromClause(AbstractDMLStatement DML,
1275: Vector saTables) throws DataStoreException {
1276: StringBuffer sSql = new StringBuffer();
1277:
1278: for (int i = 0; i < saTables.size(); i++) {
1279: String sTable = (String) saTables.get(i);
1280:
1281: if (i > 0) {
1282: sSql.append(",");
1283: }
1284:
1285: if (DML.isAlias(sTable) == true) {
1286: String sRealTable = DML.getTableName(sTable);
1287:
1288: // append the table name
1289: if (keywords.containsKey(sRealTable) == true) {
1290: sSql.append(keywords.get(sRealTable));
1291: } else {
1292: sSql.append(sRealTable);
1293: }
1294:
1295: sSql.append(" ");
1296:
1297: // append the table alias
1298: if (keywords.containsKey(sTable) == true) {
1299: sSql.append(keywords.get(sTable));
1300: } else {
1301: sSql.append(sTable);
1302: }
1303: } else { // else if the table is not an alias, then
1304: if (keywords.containsKey(sTable) == true) {
1305: sSql.append(keywords.get(sTable));
1306: } else {
1307: sSql.append(sTable);
1308: }
1309: }
1310: }
1311:
1312: return (sSql.toString());
1313: }
1314:
1315: /* (non-Javadoc)
1316: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#generateWhereClause(org.openharmonise.commons.dsi.dml.SelectStatement)
1317: */
1318: protected String generateWhereClause(SelectStatement select)
1319: throws DataStoreException {
1320:
1321: return generateWhereClause((AbstractDMLStatement) select);
1322: }
1323:
1324: /* (non-Javadoc)
1325: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#generateWhereClause(org.openharmonise.commons.dsi.dml.AbstractDMLStatement)
1326: */
1327: protected String generateWhereClause(AbstractDMLStatement DML)
1328: throws DataStoreException {
1329: if (!DML.hasWhereClause()) {
1330: return null;
1331: }
1332:
1333: WhereConditionGroup where = DML.getWhereConditions();
1334:
1335: return (generateWhereClause(where, DML));
1336: }
1337:
1338: /**
1339: * Returns the SQL 'where' clause for the given DML statement.
1340: *
1341: * @param where the where conditions for the statement
1342: * @param DML the DML statement
1343: * @return the SQL 'where' clause
1344: * @throws DataStoreException if an error occurs
1345: */
1346: private String generateWhereClause(WhereConditionGroup where,
1347: AbstractDMLStatement DML) throws DataStoreException {
1348: boolean bAnd = false;
1349:
1350: StringBuffer sSql = new StringBuffer();
1351:
1352: for (int i = 0; i < where.size(); i++) {
1353: if (bAnd) {
1354: sSql.append(" ");
1355: sSql.append(where.getStringingOperator());
1356: }
1357:
1358: if (where.isWhereConditionsLeaf(i) == false) {
1359: sSql.append(" (");
1360: sSql.append(generateWhereClause(
1361: (WhereConditionGroup) where.getCondition(i),
1362: DML));
1363: sSql.append(")");
1364: } else {
1365: StringBuffer sCol = new StringBuffer();
1366:
1367: String sColumn = where.getColumnName(i);
1368: String sTableName = where.getTableName(i);
1369:
1370: if (keywords.get(sTableName) != null) {
1371: sCol.append(keywords.get(sTableName));
1372: } else {
1373: sCol.append(sTableName);
1374: }
1375:
1376: sCol.append(".");
1377:
1378: if (keywords.get(sColumn) != null) {
1379: sCol.append(keywords.get(sColumn));
1380: } else {
1381: sCol.append(sColumn);
1382: }
1383:
1384: String sOperator = where.getOperator(i);
1385: List Values = where.getValues(i);
1386:
1387: if (!sOperator.equalsIgnoreCase("NOT IN")
1388: && !sOperator.equalsIgnoreCase("IN")
1389: && !sOperator.equalsIgnoreCase("BETWEEN")
1390: && !sOperator.equalsIgnoreCase("OR")
1391: && !sOperator.equalsIgnoreCase("CONTAINS") /*&&
1392: !sOperator.equalsIgnoreCase("STARTS_WITH")*/
1393: ) {
1394: for (int j = 0; j < Values.size(); j++) {
1395: sSql.append(" ");
1396: sSql.append(sCol.toString());
1397:
1398: if (Values.get(j) == null) {
1399: if (sOperator.equals("=") == true
1400: || sOperator.equals("is") == true) {
1401: sSql.append(" is null");
1402: } else if (sOperator.equals("!=") == true
1403: || sOperator.equals("is not") == true) {
1404: sSql.append(" is not null");
1405: }
1406: continue;
1407: }
1408:
1409: if (sOperator.equalsIgnoreCase("LIKE")
1410: || sOperator
1411: .equalsIgnoreCase("STARTS_WITH")) {
1412: sSql.append(" ");
1413: sSql.append("LIKE");
1414: sSql.append(" ");
1415: } else {
1416: sSql.append(sOperator);
1417: }
1418:
1419: if (Values
1420: .get(j)
1421: .getClass()
1422: .getName()
1423: .equalsIgnoreCase(
1424: SelectStatement.class.getName())) {
1425: SelectStatement query = (SelectStatement) Values
1426: .get(j);
1427: sSql.append("(");
1428: sSql.append(getSelectStatement(query));
1429: sSql.append(")");
1430: } else if (Values.get(j).getClass().getName()
1431: .equalsIgnoreCase("java.util.Date")
1432: || Values.get(j).getClass().getName()
1433: .equalsIgnoreCase(
1434: "java.sql.Date")) {
1435: SimpleDateFormat date_formatter = new SimpleDateFormat(
1436: DB_DATEFORMAT);
1437: String sDate = date_formatter
1438: .format((java.util.Date) Values
1439: .get(j));
1440: sSql.append(getDateAsSQL(sDate));
1441: } else if (Values.get(j) instanceof Function) {
1442: sSql.append(getFunction((Function) Values
1443: .get(j)));
1444: } else {
1445: if (!Values.get(j).getClass().getName()
1446: .equalsIgnoreCase(
1447: "java.lang.Integer")) {
1448: sSql.append("'");
1449: }
1450:
1451: sSql.append(addEscapeChars(Values.get(j)
1452: .toString()));
1453:
1454: if (!Values.get(j).getClass().getName()
1455: .equalsIgnoreCase(
1456: "java.lang.Integer")) {
1457: if (sOperator.equals("STARTS_WITH")) {
1458: sSql.append("%");
1459: }
1460: sSql.append("'");
1461: }
1462: }
1463: }
1464: } else if (sOperator.equalsIgnoreCase("IN")
1465: || sOperator.equalsIgnoreCase("NOT IN")) {
1466: sSql.append(" ");
1467: sSql.append(sCol);
1468: sSql.append(" ");
1469: sSql.append(sOperator);
1470: sSql.append(" (");
1471:
1472: for (int j = 0; j < Values.size(); j++) {
1473: if (j != 0) {
1474: sSql.append(",");
1475: }
1476:
1477: if (Values
1478: .get(j)
1479: .getClass()
1480: .getName()
1481: .equalsIgnoreCase(
1482: SelectStatement.class.getName())) {
1483: SelectStatement query = (SelectStatement) Values
1484: .get(j);
1485:
1486: sSql.append(getSelectStatement(query));
1487: } else if (Values.get(j).getClass().getName()
1488: .equalsIgnoreCase("java.util.Date")
1489: || Values.get(j).getClass().getName()
1490: .equalsIgnoreCase(
1491: "java.sql.Date")) {
1492: SimpleDateFormat date_formatter = new SimpleDateFormat(
1493: DB_DATEFORMAT);
1494: String sDate = date_formatter
1495: .format((java.util.Date) Values
1496: .get(j));
1497: sSql.append(getDateAsSQL(sDate));
1498: } else {
1499: if (!Values.get(j).getClass().getName()
1500: .equalsIgnoreCase(
1501: "java.lang.Integer")) {
1502: sSql.append("'");
1503: }
1504:
1505: sSql.append(addEscapeChars(Values.get(j)
1506: .toString()));
1507:
1508: if (!Values.get(j).getClass().getName()
1509: .equalsIgnoreCase(
1510: "java.lang.Integer")) {
1511: sSql.append("'");
1512: }
1513: }
1514: }
1515:
1516: sSql.append(")");
1517: } else if (sOperator.equalsIgnoreCase("BETWEEN")) {
1518: sSql.append(" ");
1519: sSql.append(sCol.toString());
1520: sSql.append(sOperator);
1521: sSql.append(" ");
1522:
1523: if (Values.get(0).getClass().getName()
1524: .equalsIgnoreCase("java.util.Date")
1525: || Values.get(0).getClass().getName()
1526: .equalsIgnoreCase("java.sql.Date")) {
1527: SimpleDateFormat date_formatter = new SimpleDateFormat(
1528: DB_DATEFORMAT);
1529: String sDate = date_formatter
1530: .format((java.util.Date) Values.get(0));
1531: sSql.append(getDateAsSQL(sDate));
1532: } else {
1533: if (!Values.get(0).getClass().getName()
1534: .equalsIgnoreCase("java.lang.Integer")) {
1535: sSql.append("'");
1536: }
1537:
1538: sSql.append(Values.get(0).toString());
1539:
1540: if (!Values.get(0).getClass().getName()
1541: .equalsIgnoreCase("java.lang.Integer")) {
1542: sSql.append("'");
1543: }
1544: }
1545:
1546: sSql.append(" AND ");
1547:
1548: if (Values.get(1).getClass().getName()
1549: .equalsIgnoreCase("java.util.Date")
1550: || Values.get(1).getClass().getName()
1551: .equalsIgnoreCase("java.sql.Date")) {
1552: SimpleDateFormat date_formatter = new SimpleDateFormat(
1553: DB_DATEFORMAT);
1554: String sDate = date_formatter
1555: .format((java.util.Date) Values.get(1));
1556: sSql.append(getDateAsSQL(sDate));
1557: } else {
1558: if (!Values.get(1).getClass().getName()
1559: .equalsIgnoreCase("java.lang.Integer")) {
1560: sSql.append("'");
1561: }
1562:
1563: sSql.append(Values.get(1).toString());
1564:
1565: if (!Values.get(1).getClass().getName()
1566: .equalsIgnoreCase("java.lang.Integer")) {
1567: sSql.append("'");
1568: }
1569: }
1570: } else if (sOperator.equalsIgnoreCase("CONTAINS") == true) {
1571: sSql.append("(");
1572: for (int j = 0; j < Values.size(); j++) {
1573: if (j != 0) {
1574: sSql.append(" OR ");
1575: }
1576:
1577: sSql.append(" (");
1578: sSql.append(sCol);
1579: sSql.append(" ");
1580: sSql.append("LIKE");
1581: sSql.append(" ");
1582:
1583: if ((Values.get(j) instanceof Integer) == false) {
1584: sSql.append("'%");
1585: }
1586:
1587: sSql.append(addEscapeChars(Values.get(j)
1588: .toString()));
1589:
1590: sSql.append("%'");
1591:
1592: sSql.append(")");
1593: }
1594: sSql.append(")");
1595: }
1596: }
1597:
1598: bAnd = true;
1599: }
1600:
1601: return (sSql.toString());
1602: }
1603:
1604: /* (non-Javadoc)
1605: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
1606: */
1607: public String getJoinCondition(ColumnRef ref1, ColumnRef ref2,
1608: boolean bIsOuter) {
1609: return null;
1610: }
1611:
1612: /* (non-Javadoc)
1613: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
1614: */
1615: public void createTable(TableDefinition tblDef)
1616: throws DataStoreException {
1617: StringBuffer str = new StringBuffer();
1618:
1619: str.append("create table ").append(tblDef.getName());
1620: str.append(" (");
1621:
1622: Iterator iter = tblDef.iterator();
1623:
1624: while (iter.hasNext()) {
1625: ColumnDefinition coldef = (ColumnDefinition) iter.next();
1626:
1627: str.append(coldef.getName());
1628: str.append(" ");
1629: int nDataType = coldef.getDataType();
1630:
1631: if (nDataType == ColumnDefinition.NUMBER) {
1632: str.append(TYPE_INT);
1633: } else if (nDataType == ColumnDefinition.TEXT) {
1634: str.append(TYPE_NVARCHAR_255);
1635: } else if (nDataType == ColumnDefinition.LONG_TEXT) {
1636: str.append(TYPE_NTEXT);
1637: } else if (nDataType == ColumnDefinition.DATE) {
1638: str.append(getDateDataType());
1639: } else if (nDataType == ColumnDefinition.BOOLEAN) {
1640: str.append(getBooleanDataType());
1641: }
1642:
1643: Object defaultVal = coldef.getDefault();
1644:
1645: if (defaultVal != null) {
1646:
1647: str.append(" ").append(KEYWORD_DEFAULT).append(" ");
1648:
1649: if (defaultVal instanceof String
1650: && (nDataType == ColumnDefinition.TEXT || nDataType == ColumnDefinition.LONG_TEXT)) {
1651: str.append(defaultVal);
1652: } else if (defaultVal instanceof Date) {
1653:
1654: } else if (defaultVal instanceof Integer) {
1655: str.append(((Integer) defaultVal).intValue());
1656: } else if (coldef.allowNulls() == true) {
1657: str.append(KEYWORD_NULL);
1658: }
1659: }
1660:
1661: if (coldef.allowNulls() == false) {
1662: str.append(" ");
1663: str.append(KEYWORD_NOT_NULL);
1664: }
1665:
1666: if (coldef.isPrimaryKey()) {
1667: str.append(" ").append(KEYWORD_PRIMARY_KEY);
1668: } else if (coldef.isUnique()) {
1669: str.append(" ").append(KEYWORD_UNIQUE);
1670: } else if (coldef.isForeignKey()) {
1671: str.append(" ").append(KEYWORD_REFERENCES).append(" ")
1672: .append(coldef.getForeignKeyReference());
1673: }
1674:
1675: if (iter.hasNext()) {
1676: str.append(",");
1677: }
1678: }
1679:
1680: str.append(")");
1681:
1682: execute(str.toString());
1683: }
1684: }
|