001: /*
002: * The contents of this file are subject to the
003: * Mozilla Public License Version 1.1 (the "License");
004: * you may not use this file except in compliance with the License.
005: * You may obtain a copy of the License at http://www.mozilla.org/MPL/
006: *
007: * Software distributed under the License is distributed on an "AS IS"
008: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
009: * See the License for the specific language governing rights and
010: * limitations under the License.
011: *
012: * The Initial Developer of the Original Code is Simulacra Media Ltd.
013: * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
014: *
015: * All Rights Reserved.
016: *
017: * Contributor(s):
018: */
019: package org.openharmonise.commons.dsi.impl;
020:
021: import java.io.*;
022:
023: import java.sql.*;
024:
025: import java.util.*;
026:
027: import org.openharmonise.commons.dsi.*;
028: import org.openharmonise.commons.dsi.ddl.*;
029: import org.openharmonise.commons.dsi.dml.*;
030:
031: /**
032: * Class providing interface to an Oracle database.
033: *
034: * @author Michael Bell
035: * @see java.sql
036: */
037: public class DataStoreInterfaceOracle extends
038: AbstractDataStoreInterface {
039:
040: //static initialiser block
041: static {
042: DB_DATEFORMAT = "MM-dd-yyyy HH:mm:ss";
043: }
044:
045: /**
046: * Constructs a Oracle data store interface with no DB settings
047: */
048: public DataStoreInterfaceOracle() {
049: }
050:
051: /**
052: * Constructs a Oracle data store interface with the given connection
053: * type
054: *
055: * @param nConnectionType the connection type
056: * @throws DataStoreException if any errors occur
057: */
058: public DataStoreInterfaceOracle(int nConnectionType)
059: throws DataStoreException {
060: super (nConnectionType);
061: }
062:
063: /* (non-Javadoc)
064: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
065: */
066: public int getSequenceNextValue(String sSeqName)
067: throws DataStoreException, SQLException {
068: Connection conn = null;
069: ResultSet rs = null;
070: Statement stmt = null;
071: String sSql = null;
072: int nSeq = -1;
073:
074: conn = getConnection();
075: stmt = conn.createStatement();
076:
077: sSql = "select " + sSeqName + ".NEXTVAL from dual";
078:
079: rs = stmt.executeQuery(sSql);
080:
081: if (rs.next()) {
082: nSeq = rs.getInt(1);
083: } else {
084: throw new DataStoreException("Sequence [" + sSeqName
085: + "] not found.");
086: }
087:
088: if (rs != null) {
089: rs.close();
090: }
091:
092: if (stmt != null) {
093: stmt.close();
094: }
095:
096: if (isPooledConnection() && (conn != null)) {
097: this .closeConnection(conn);
098: }
099:
100: return nSeq;
101: }
102:
103: /* (non-Javadoc)
104: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#insertClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
105: */
106: public void insertClob(String sTable, String sColumn, String sClob,
107: String sCondition) throws DataStoreException {
108: Connection conn = null;
109: Statement stmt = null;
110: ResultSet rs = null;
111:
112: if ((sCondition == null) || (sCondition.length() == 0)) {
113: throw new DataStoreException("Missing CLOB condition");
114: }
115:
116: StringBuffer sSql = new StringBuffer();
117:
118: try {
119: conn = getConnection();
120: stmt = conn.createStatement();
121:
122: sSql.append("update ");
123: sSql.append(sTable);
124: sSql.append(" set ");
125: sSql.append(sColumn);
126: sSql.append("=");
127: sSql.append("");
128: sSql.append("empty_clob()");
129: sSql.append(" where ");
130: sSql.append(sCondition);
131:
132: stmt.executeUpdate(sSql.toString());
133:
134: sSql.setLength(0);
135:
136: conn.setAutoCommit(false);
137:
138: //get CLOB locator
139: sSql.append("select ");
140: sSql.append(sColumn);
141: sSql.append(" from ");
142: sSql.append(sTable);
143: sSql.append(" where ");
144: sSql.append(sCondition);
145: sSql.append(" for update");
146:
147: rs = stmt.executeQuery(sSql.toString());
148:
149: Clob clob = null;
150:
151: if (rs.next()) {
152: clob = rs.getClob(1);
153: }
154:
155: //create input stream
156: Writer outstream = clob.setCharacterStream(0);
157:
158: try {
159: outstream.write(addEscapeChars(sClob));
160: outstream.close();
161: } catch (IOException e) {
162: throw new DataStoreException(
163: "Error writing CLOB to database: "
164: + e.getMessage());
165: }
166:
167: conn.commit();
168: conn.setAutoCommit(true);
169:
170: if (isPooledConnection() && (conn != null)) {
171: this .closeConnection(conn);
172: }
173: } catch (SQLException e) {
174: throw new DataStoreException("SQLException: "
175: + e.getMessage());
176: }
177: }
178:
179: /* (non-Javadoc)
180: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
181: */
182: public void updateClob(String sTable, String sColumn, String sClob,
183: String sCondition) throws DataStoreException {
184: Connection conn = null;
185: Statement stmt = null;
186: ResultSet rs = null;
187:
188: if ((sCondition == null) || (sCondition.length() == 0)) {
189: throw new DataStoreException("Missing CLOB condition");
190: }
191:
192: StringBuffer sSql = new StringBuffer();
193:
194: try {
195: conn = getConnection();
196: stmt = conn.createStatement();
197:
198: conn.setAutoCommit(false);
199:
200: //get CLOB locator
201: sSql.append("select ");
202: sSql.append(sColumn);
203: sSql.append(" from ");
204: sSql.append(sTable);
205: sSql.append(" where ");
206: sSql.append(sCondition);
207:
208: sSql.append(" for update");
209:
210: rs = stmt.executeQuery(sSql.toString());
211:
212: Clob clob = null;
213:
214: if (rs.next()) {
215: clob = rs.getClob(1);
216: }
217:
218: //create input stream
219: Writer outstream = clob.setCharacterStream(0);
220:
221: try {
222: outstream.write(addEscapeChars(sClob));
223: outstream.close();
224: } catch (IOException e) {
225: throw new DataStoreException(
226: "Error writing CLOB to database: "
227: + e.getMessage());
228: }
229:
230: conn.commit();
231: conn.setAutoCommit(true);
232:
233: if (stmt != null) {
234: stmt.close();
235: }
236:
237: if (rs != null) {
238: rs.close();
239: }
240:
241: if (isPooledConnection() && (conn != null)) {
242: this .closeConnection(conn);
243: }
244: } catch (SQLException e) {
245: throw new DataStoreException("SQLException: "
246: + e.getMessage());
247: }
248: }
249:
250: /* (non-Javadoc)
251: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
252: */
253: public String getClob(String sTable, String sColumn,
254: String sCondition) throws DataStoreException {
255: Connection conn = null;
256: Statement stmt = null;
257: ResultSet rs = null;
258: String sReturn = null;
259:
260: if ((sCondition == null) || (sCondition.length() == 0)) {
261: throw new DataStoreException("Missing CLOB condition");
262: }
263:
264: StringBuffer sSql = new StringBuffer();
265:
266: try {
267: conn = getConnection();
268:
269: conn.setAutoCommit(false);
270:
271: stmt = conn.createStatement();
272:
273: sSql.append("select ");
274: sSql.append(sColumn);
275: sSql.append(" from ");
276: sSql.append(sTable);
277: sSql.append(" where ");
278: sSql.append(sCondition);
279:
280: rs = stmt.executeQuery(sSql.toString());
281:
282: if (rs.next()) {
283:
284: Clob clob = rs.getClob(1);
285:
286: try {
287: sReturn = Clob2String(clob);
288: } catch (Exception e) {
289: throw new DataStoreException(
290: "Error converting CLOB to a String: "
291: + e.getMessage());
292: }
293: }
294:
295: conn.commit();
296: conn.setAutoCommit(true);
297:
298: if (stmt != null) {
299: stmt.close();
300: }
301:
302: if (rs != null) {
303: rs.close();
304: }
305:
306: if (isPooledConnection() && (conn != null)) {
307: this .closeConnection(conn);
308: }
309: } catch (SQLException e) {
310: throw new DataStoreException("SQLException: "
311: + e.getMessage());
312: }
313:
314: return sReturn;
315: }
316:
317: /**
318: * Converts and Oracle Clob object to a Java String object.
319: *
320: * @param clob the CLOB to convert
321: * @return the content as string
322: * @throws DataStoreException if an error occurs
323: */
324: private String Clob2String(Clob clob) throws DataStoreException {
325: StringBuffer strbuf = new StringBuffer();
326: String sMattString = "";
327:
328: try {
329: // get character stream to retrieve clob data
330: char[] buffer = new char[(int) clob.length()];
331:
332: long mattLong = 1;
333:
334: sMattString = clob.getSubString(mattLong, (int) clob
335: .length());
336:
337: } catch (SQLException e) {
338: throw new DataStoreException("SQLException: "
339: + e.getMessage());
340: }
341:
342: return sMattString;
343: }
344:
345: /* (non-Javadoc)
346: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
347: */
348: protected String addEscapeChars(String sOldString) {
349: int marker = -1;
350: int lastmarker = 0;
351: int quotemarker = -1;
352:
353: if (sOldString == null) {
354: return "";
355: }
356:
357: StringBuffer sBuf = new StringBuffer();
358:
359: quotemarker = sOldString.indexOf("'");
360:
361: if (quotemarker >= 0) {
362: marker = quotemarker;
363: }
364:
365: if (marker < 0) {
366: return sOldString;
367: } else {
368: while (marker >= 0) {
369: //append to stringbuffer
370: sBuf.append(sOldString.substring(lastmarker, marker));
371: sBuf.append("'");
372:
373: //reset markers
374: quotemarker = -1;
375: lastmarker = marker;
376:
377: quotemarker = sOldString.indexOf("'", marker + 1);
378:
379: if (quotemarker >= 0) {
380: marker = quotemarker;
381: } else {
382: marker = -1;
383: }
384: }
385:
386: sBuf.append(sOldString.substring(lastmarker));
387:
388: return (sBuf.toString());
389: }
390: }
391:
392: /* (non-Javadoc)
393: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
394: */
395: public String getDateDataType() {
396: return "DATE";
397: }
398:
399: /* (non-Javadoc)
400: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
401: */
402: public String getCLOBDataType() {
403: return "CLOB";
404: }
405:
406: /* (non-Javadoc)
407: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
408: */
409: public String getBooleanDataType() {
410: return "SMALLINT";
411: }
412:
413: /* (non-Javadoc)
414: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
415: */
416: public List getTableList() throws DataStoreException {
417: Vector tables = new Vector();
418: ResultSet rs = null;
419:
420: try {
421: rs = executeQuery("select table_name from user_tables where table_name not like '%$%' order by table_name");
422:
423: while (rs.next()) {
424: tables.add(rs.getString(1).trim());
425: }
426:
427: rs.close();
428: } catch (SQLException e) {
429: throw new DataStoreException(e);
430: }
431:
432: return tables;
433: }
434:
435: /* (non-Javadoc)
436: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
437: */
438: public List getSequenceList() throws DataStoreException {
439: Vector seqs = new Vector();
440: ResultSet rs = null;
441:
442: try {
443: rs = executeQuery("select sequence_name from seq order by sequence_name");
444:
445: while (rs.next()) {
446: seqs.add(rs.getString(1).trim());
447: }
448:
449: rs.close();
450: } catch (SQLException e) {
451: throw new DataStoreException(e);
452: }
453:
454: return seqs;
455: }
456:
457: /* (non-Javadoc)
458: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
459: */
460: protected String getDateAsSQL(String date) {
461: // localise this, as it is different for different DB implementations,
462: // SQL Server
463: StringBuffer sSql = new StringBuffer();
464:
465: sSql.append(" TO_DATE('");
466: sSql.append(date);
467: sSql.append("','MM-DD-YYYY HH24:MI:SS' )");
468:
469: return sSql.toString();
470: }
471:
472: /* (non-Javadoc)
473: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
474: */
475: public String getJoinCondition(ColumnRef ref1, ColumnRef ref2,
476: boolean bIsOuter) {
477: StringBuffer sSql = new StringBuffer();
478:
479: sSql.append(ref1.getFullRef());
480:
481: if (bIsOuter == true) {
482: sSql.append("(+)");
483: }
484:
485: sSql.append("=");
486:
487: sSql.append(ref2.getFullRef());
488:
489: return sSql.toString();
490: }
491:
492: /* (non-Javadoc)
493: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
494: */
495: protected String getFunction(Function func)
496: throws DataStoreException {
497: throw new DataStoreException("Not implemented");
498: }
499:
500: /* (non-Javadoc)
501: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
502: */
503: public void createTable(TableDefinition tblDef)
504: throws DataStoreException {
505: throw new UnsupportedOperationException(
506: "Method not implemented");
507: }
508: }
|