001: //** Copyright Statement ***************************************************
002: //The Salmon Open Framework for Internet Applications (SOFIA)
003: // Copyright (C) 1999 - 2002, Salmon LLC
004: //
005: // This program is free software; you can redistribute it and/or
006: // modify it under the terms of the GNU General Public License version 2
007: // as published by the Free Software Foundation;
008: //
009: // This program is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: // GNU General Public License for more details.
013: //
014: // You should have received a copy of the GNU General Public License
015: // along with this program; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: //
018: // For more information please visit http://www.salmonllc.com
019: //** End Copyright Statement ***************************************************
020: package com.salmonllc.sql;
021:
022: import java.sql.ResultSet;
023: import java.sql.Statement;
024:
025: import com.salmonllc.util.MessageLog;
026:
027: /**
028: * Driver for DB2 Universal DB
029: * @author Mike Patnode (mike@mpsharp.com)
030: */
031:
032: class DSDataSourceDB2 extends DSDataSourceJDBC {
033:
034: /**
035: * DSDataSourceDB2 constructor comment.
036: */
037: public DSDataSourceDB2() {
038: super ();
039: }
040:
041: private int findAlias(DataStore ds, String table) {
042: try {
043: int count = ds.getAliasCount();
044:
045: for (int i = 0; i < count; i++) {
046: if (ds.getAlias(i) != null)
047: if (ds.getAlias(i).equalsIgnoreCase(table))
048: return i;
049:
050: if (ds.getTable(i).equalsIgnoreCase(table))
051: return i;
052: }
053:
054: if (ds.getDefaultTable() != null) {
055: if (ds.getDefaultTable().equalsIgnoreCase(table))
056: return count;
057: }
058: } catch (Exception e) {
059: }
060:
061: return -1;
062: }
063:
064: /**
065: * This method was created in VisualAge.
066: * @return java.lang.String
067: * @param ds com.salmonllc.sql.DataStore
068: */
069: public String generateSelect(DataStore ds, String criteria,
070: boolean countOnly) throws DataStoreException {
071:
072: //build the column list
073: StringBuffer colList = new StringBuffer();
074: colList.append("SELECT ");
075: if (ds.getDistinct())
076: colList.append("DISTINCT ");
077: if (countOnly)
078: colList.append("count(*)");
079: else {
080: for (int i = 0; i < ds.getColumnCount(); i++) {
081: String databaseName = ds.getColumnDatabaseName(i);
082: if (databaseName != null) {
083: colList.append(databaseName);
084: colList.append(",");
085: }
086: }
087: colList.setCharAt(colList.length() - 1, ' ');
088: }
089:
090: //build the join portion of the from clause
091: StringBuffer joinClause = new StringBuffer();
092: boolean aliasUsed[] = new boolean[ds.getAliasCount() + 1];
093: for (int i = 0; i < ds.getJoinCount(); i++) {
094: String join = " inner join ";
095: if (ds.getJoinOuter(i))
096: join = " left outer join ";
097: String leftTable = ds.getJoinLeftColumn(i, 0);
098: String rightTable = ds.getJoinRightColumn(i, 0);
099: int pos = leftTable.indexOf(".");
100: if (pos > -1)
101: leftTable = leftTable.substring(0, pos);
102: pos = rightTable.indexOf(".");
103: if (pos > -1)
104: rightTable = rightTable.substring(0, pos);
105: int index = findAlias(ds, leftTable);
106: if (index > -1) {
107: if (aliasUsed[index])
108: leftTable = "";
109: aliasUsed[index] = true;
110: }
111: index = findAlias(ds, rightTable);
112: if (index > -1)
113: aliasUsed[index] = true;
114: joinClause.append(" " + leftTable + join + rightTable
115: + " ON ");
116: for (int j = 0; j < ds.getJoinColumnCount(i); j++) {
117: if (j > 0)
118: joinClause.append(" AND ");
119: joinClause.append(ds.getJoinLeftColumn(i, j) + " = "
120: + ds.getJoinRightColumn(i, j));
121: }
122: }
123: StringBuffer fromClause = new StringBuffer();
124: fromClause.append(" FROM ");
125: if ((ds.getDefaultTable() != null)
126: && (!aliasUsed[ds.getAliasCount() + 1]))
127: fromClause.append(ds.getDefaultTable() + ",");
128: for (int i = 0; i < ds.getAliasCount(); i++) {
129: if (!aliasUsed[i]) {
130: fromClause.append(ds.getTable(i));
131: if (ds.getAlias(i) != null)
132: fromClause.append(" " + ds.getAlias(i));
133: fromClause.append(",");
134: }
135: }
136: if (fromClause.length() > 0 && joinClause.length() == 0)
137: fromClause.setCharAt(fromClause.length() - 1, ' ');
138: fromClause.append(joinClause);
139:
140: //build the where clause
141:
142: StringBuffer whereClause = new StringBuffer();
143: // fc: 07/18/02 Added check for empty criteria to stop a bad sql statement from being generated.
144: if (criteria != null && !criteria.trim().equals("")
145: && ds.getCriteria() != null
146: && !ds.getCriteria().trim().equals(""))
147: criteria = "(" + criteria + ") AND (" + ds.getCriteria()
148: + ")";
149: else if (criteria == null)
150: criteria = ds.getCriteria();
151: if (criteria != null)
152: if (!criteria.trim().equals(""))
153: whereClause.append(" WHERE " + criteria);
154:
155: //finish it up and return
156: String retVal = colList.toString() + fromClause.toString()
157: + whereClause.toString();
158: if (ds.getGroupBy() != null)
159: retVal += " GROUP BY " + ds.getGroupBy();
160: if (ds.getHaving() != null)
161: retVal += " HAVING " + ds.getHaving();
162: if (ds.getOrderBy() != null && !countOnly)
163: retVal += " ORDER BY " + ds.getOrderBy();
164: return retVal;
165:
166: }
167:
168: /**Called by SOFIA to find an auto-generated Primary Key after it does an Insert.
169: * Note that you must call setAutoIncrement(pkey_col, true) and setUpdatable(pkey_col, false)
170: * for autogenerated pkeys to work.
171: */
172: protected void populateAutoIncrementValue(DataStoreRow row,
173: DBConnection conn, int colNo) {
174: try {
175: Statement st = conn.createStatement();
176:
177: // This is how you find the most-recently-added row's autonumber key in DB2.
178: ResultSet r = st
179: .executeQuery("values identity_val_local()");
180: if (r.next()) {
181: int colStat = row.getDSDataRow().getColumnStatus(colNo);
182: int rowStat = row.getDSDataRow().getRowStatus();
183: Object val = new Integer(r.getInt(1));
184:
185: row.setData(colNo, val);
186: row.getDSDataRow().setColumnStatus(colNo, colStat);
187: row.getDSDataRow().setRowStatus(rowStat);
188: }
189: r.close();
190: st.close();
191: } catch (Exception e) {
192: MessageLog.writeErrorMessage(
193: "Error getting last auto increment value", e, this);
194: }
195:
196: }
197: }
|