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: import java.sql.*;
023: import java.text.*;
024: import java.util.*;
025: import java.util.Date;
026: import java.util.logging.*;
027: import java.util.logging.Level;
028:
029: import org.openharmonise.commons.dsi.*;
030: import org.openharmonise.commons.dsi.ddl.*;
031: import org.openharmonise.commons.dsi.dml.*;
032: import org.openharmonise.commons.dsi.dml.functions.*;
033:
034: /**
035: * Class providing interface to a MS SQLServer database.
036: *
037: * @author Michael Bell
038: *
039: * @see java.sql
040: */
041: public class DataStoreInterfaceSQLServer extends
042: AbstractDataStoreInterface {
043:
044: private static final String TYPE_NTEXT = "NTEXT";
045: private static final String TYPE_NVARCHAR_255 = "NVARCHAR (255)";
046: private static final String TYPE_INT = "INT";
047: private static final String KEYWORD_PRIMARY_KEY = "PRIMARY KEY";
048: private static final String KEYWORD_UNIQUE = "UNIQUE";
049: private static final String KEYWORD_DEFAULT = "DEFAULT";
050: private static final String KEYWORD_NOT_NULL = "NOT NULL";
051: private static final String KEYWORD_FOREIGN_KEY = "FOREIGN KEY";
052: private static final String KEYWORD_REFERENCES = "REFERENCES";
053:
054: public static Date SQLSERVER_CONVERSION_DATE_LIMIT = null;
055:
056: /**
057: * Logger for this class
058: */
059: private static final Logger m_logger = Logger
060: .getLogger(DataStoreInterfaceSQLServer.class.getName());
061:
062: //static initialiser block
063: static {
064: String date_format = "MM-dd-yyyy";
065: SimpleDateFormat format = new SimpleDateFormat(date_format);
066:
067: try {
068: SQLSERVER_CONVERSION_DATE_LIMIT = format
069: .parse("01-01-1753");
070: } catch (ParseException e) {
071: m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
072: }
073: m_bIsNationalCharacterSupported = true;
074: }
075:
076: /**
077: * Constructs a SQLServer data store interface with no DB settings.
078: */
079: public DataStoreInterfaceSQLServer() {
080: }
081:
082: /**
083: * Constructs a SQLServer data store interface with the given connection
084: * type.
085: *
086: * @param nConnectionType the connection type
087: * @throws DataStoreException if any errors occur
088: */
089: public DataStoreInterfaceSQLServer(int nConnectionType)
090: throws Exception {
091: super (nConnectionType);
092: }
093:
094: /* (non-Javadoc)
095: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceNextValue(java.lang.String)
096: */
097: public int getSequenceNextValue(String sSeqName)
098: throws DataStoreException, SQLException {
099: Connection conn = null;
100: ResultSet rs = null;
101: Statement stmt = null;
102: String sSql = null;
103: int nSeq = -1;
104:
105: conn = getConnection();
106:
107: stmt = conn.createStatement();
108:
109: sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='"
110: + sSeqName + "'";
111: stmt.executeUpdate(sSql);
112:
113: sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName
114: + "'";
115: rs = stmt.executeQuery(sSql);
116:
117: if (rs.next()) {
118: nSeq = rs.getInt(1);
119: } else {
120: throw new DataStoreException("Sequence [" + sSeqName
121: + "] not found.");
122: }
123:
124: if (rs != null) {
125: rs.close();
126: }
127:
128: if (stmt != null) {
129: stmt.close();
130: }
131:
132: if (isPooledConnection() && (conn != null)) {
133: this .closeConnection(conn);
134: }
135:
136: return nSeq;
137: }
138:
139: /* (non-Javadoc)
140: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#insertClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
141: */
142: public void insertClob(String sTable, String sColumn, String sClob,
143: String sCondition) throws DataStoreException {
144: Connection conn = null;
145: Statement stmt = null;
146: ResultSet rs = null;
147:
148: if ((sCondition == null) || (sCondition.length() == 0)) {
149: throw new DataStoreException("Missing CLOB condition");
150: }
151:
152: StringBuffer sSql = new StringBuffer();
153:
154: try {
155: conn = getConnection();
156: stmt = conn.createStatement();
157:
158: sSql.append("update ");
159: sSql.append(sTable);
160: sSql.append(" set ");
161: sSql.append(sColumn);
162: sSql.append(" = N'");
163: sSql.append(addEscapeChars(sClob));
164: sSql.append("' where ");
165: sSql.append(sCondition);
166:
167: stmt.execute(sSql.toString());
168:
169: if (isPooledConnection() && (conn != null)) {
170: this .closeConnection(conn);
171: }
172: } catch (SQLException e) {
173: throw new DataStoreException("SQLException: "
174: + e.getMessage());
175: }
176: }
177:
178: /* (non-Javadoc)
179: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#updateClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
180: */
181: public void updateClob(String sTable, String sColumn, String sClob,
182: String sCondition) throws DataStoreException {
183: Connection conn = null;
184: Statement stmt = null;
185: ResultSet rs = null;
186:
187: if ((sCondition == null) || (sCondition.length() == 0)) {
188: throw new DataStoreException("Missing CLOB condition");
189: }
190:
191: StringBuffer sSql = new StringBuffer();
192:
193: try {
194: conn = getConnection();
195: stmt = conn.createStatement();
196:
197: sSql.append("update ");
198: sSql.append(sTable);
199: sSql.append(" set ");
200: sSql.append(sColumn);
201: sSql.append(" = N'");
202: sSql.append(addEscapeChars(sClob));
203: sSql.append("' where ");
204: sSql.append(sCondition);
205:
206: stmt.execute(sSql.toString());
207:
208: if (stmt != null) {
209: stmt.close();
210: }
211:
212: if (rs != null) {
213: rs.close();
214: }
215:
216: if (isPooledConnection() && (conn != null)) {
217: this .closeConnection(conn);
218: }
219: } catch (SQLException e) {
220: throw new DataStoreException("SQLException: "
221: + e.getMessage());
222: }
223: }
224:
225: /* (non-Javadoc)
226: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getClob(java.lang.String, java.lang.String, java.lang.String)
227: */
228: public String getClob(String sTable, String sColumn,
229: String sCondition) throws DataStoreException {
230: Connection conn = null;
231: Statement stmt = null;
232: ResultSet rs = null;
233: String sReturn = null;
234:
235: if ((sCondition == null) || (sCondition.length() == 0)) {
236: throw new DataStoreException("Missing CLOB condition");
237: }
238:
239: StringBuffer sSql = new StringBuffer();
240:
241: try {
242: conn = getConnection();
243:
244: stmt = conn.createStatement();
245:
246: sSql.append("select ");
247: sSql.append(sColumn);
248: sSql.append(" from ");
249: sSql.append(sTable);
250: sSql.append(" where ");
251: sSql.append(sCondition);
252:
253: try {
254: rs = stmt.executeQuery(sSql.toString());
255: } catch (SQLException e) {
256: throw new DataStoreException(sSql.toString() + " : "
257: + e.getMessage());
258: }
259:
260: if (rs.next()) {
261: Reader is = rs.getCharacterStream(1);
262: BufferedReader buffR = new BufferedReader(is);
263:
264: StringBuffer sBuffContent = new StringBuffer();
265: String sTemp = buffR.readLine();
266:
267: while (sTemp != null) {
268: sBuffContent.append(sTemp);
269: sTemp = buffR.readLine();
270: }
271: sReturn = sBuffContent.toString();
272: }
273:
274: if (stmt != null) {
275: stmt.close();
276: }
277:
278: if (rs != null) {
279: rs.close();
280: }
281:
282: if (isPooledConnection() && (conn != null)) {
283: this .closeConnection(conn);
284: }
285: } catch (SQLException e) {
286: throw new DataStoreException("SQLException", e);
287: } catch (UnsupportedEncodingException e) {
288: m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
289: } catch (IOException e) {
290: m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
291: }
292:
293: return sReturn;
294: }
295:
296: /* (non-Javadoc)
297: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#addEscapeChars(java.lang.String)
298: */
299: protected String addEscapeChars(String sOldString) {
300: int marker = -1;
301: int lastmarker = 0;
302: int quotemarker = -1;
303:
304: if (sOldString == null) {
305: return "";
306: }
307:
308: StringBuffer sBuf = new StringBuffer();
309:
310: quotemarker = sOldString.indexOf("'");
311:
312: if (quotemarker >= 0) {
313: marker = quotemarker;
314: }
315:
316: if (marker < 0) {
317: return sOldString;
318: } else {
319: while (marker >= 0) {
320: //append to stringbuffer
321: sBuf.append(sOldString.substring(lastmarker, marker));
322: sBuf.append("'");
323:
324: //reset markers
325: quotemarker = -1;
326: lastmarker = marker;
327:
328: quotemarker = sOldString.indexOf("'", marker + 1);
329:
330: if (quotemarker >= 0) {
331: marker = quotemarker;
332: } else {
333: marker = -1;
334: }
335: }
336:
337: sBuf.append(sOldString.substring(lastmarker));
338:
339: return (sBuf.toString());
340: }
341: }
342:
343: /* (non-Javadoc)
344: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateDataType()
345: */
346: public String getDateDataType() {
347: return "DATETIME";
348: }
349:
350: /* (non-Javadoc)
351: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getCLOBDataType()
352: */
353: public String getCLOBDataType() {
354: return TYPE_NTEXT;
355: }
356:
357: /* (non-Javadoc)
358: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getBooleanDataType()
359: */
360: public String getBooleanDataType() {
361: return "BIT";
362: }
363:
364: /* (non-Javadoc)
365: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getTableList()
366: */
367: public List getTableList() throws DataStoreException {
368: Vector tables = new Vector();
369: ResultSet rs = null;
370:
371: try {
372: rs = executeQuery("select name from sysobjects where type = 'u' and name != 'dtproperties' order by name");
373:
374: while (rs.next()) {
375: tables.add(rs.getString(1).trim());
376: }
377:
378: rs.close();
379: } catch (SQLException e) {
380: throw new DataStoreException(e);
381: }
382:
383: return tables;
384: }
385:
386: /* (non-Javadoc)
387: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getSequenceList()
388: */
389: public List getSequenceList() throws DataStoreException {
390: Vector seqs = new Vector();
391: ResultSet rs = null;
392:
393: try {
394: rs = executeQuery("select seq_name from oh_seq order by seq_name");
395:
396: while (rs.next()) {
397: seqs.add(rs.getString(1).trim());
398: }
399:
400: rs.close();
401: } catch (SQLException e) {
402: throw new DataStoreException(e);
403: }
404:
405: return seqs;
406: }
407:
408: /* (non-Javadoc)
409: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getDateAsSQL(java.lang.String)
410: */
411: protected String getDateAsSQL(String date) {
412: // localise this, as it is different for different DB implementations,
413: // SQL Server
414: StringBuffer sSql = new StringBuffer();
415:
416: sSql.append(" CONVERT(datetime, '");
417: sSql.append(date);
418: sSql.append("', 120 )");
419:
420: return sSql.toString();
421: }
422:
423: /* (non-Javadoc)
424: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getJoinCondition(org.openharmonise.commons.dsi.ColumnRef, org.openharmonise.commons.dsi.ColumnRef, boolean)
425: */
426: public String getJoinCondition(ColumnRef ref1, ColumnRef ref2,
427: boolean bIsOuter) {
428: StringBuffer sSql = new StringBuffer();
429:
430: sSql.append(ref1.getFullRef());
431: sSql.append("=");
432:
433: if (bIsOuter == true) {
434: sSql.append("*");
435: }
436:
437: sSql.append(ref2.getFullRef());
438:
439: return sSql.toString();
440: }
441:
442: /* (non-Javadoc)
443: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#getFunction(org.openharmonise.commons.dsi.dml.Function)
444: */
445: protected String getFunction(Function func)
446: throws DataStoreException {
447:
448: String sFunc = null;
449:
450: if (func instanceof Substring) {
451: sFunc = getSubstring((Substring) func);
452:
453: } else if (func instanceof Length) {
454: sFunc = getLength((Length) func);
455:
456: } else if (func instanceof ToDate) {
457: sFunc = getToDate((ToDate) func);
458:
459: } else {
460: throw new DataStoreException("Function not supported - "
461: + func.getClass().getName());
462: }
463:
464: return sFunc;
465: }
466:
467: /**
468: * Returns the SQL 'todate' statement for the given
469: * <code>ToDate</code> <code>Function</code>.
470: *
471: * @param date the function
472: * @return the SQL 'todate' statement
473: */
474: private String getToDate(ToDate date) {
475: StringBuffer strbuf = new StringBuffer();
476:
477: strbuf.append("CONVERT(datetime,");
478:
479: Object objVal = date.getValue();
480:
481: if (objVal instanceof ColumnRef) {
482: strbuf.append(((ColumnRef) objVal).getFullRef());
483: } else if (objVal instanceof String) {
484: strbuf.append("'").append(objVal).append("'");
485: }
486:
487: strbuf.append(", 120 )");
488: return strbuf.toString();
489: }
490:
491: /**
492: * Returns the SQL 'substring' statement from the given
493: * <code>Substring</code> <code>Function</code>.
494: *
495: * @param substr the substring <code>Function</code>
496: * @return the SQL 'substring' statement
497: * @throws DataStoreException if an error occurs
498: */
499: private String getSubstring(Substring substr)
500: throws DataStoreException {
501:
502: StringBuffer strbuf = new StringBuffer();
503:
504: strbuf.append("SUBSTRING('").append(substr.getString()).append(
505: "',");
506:
507: Object objStart = substr.getStart();
508:
509: if (objStart instanceof Integer) {
510: strbuf.append(((Integer) objStart).toString());
511: } else if (objStart instanceof String) {
512: strbuf.append((String) objStart);
513: } else if (objStart instanceof Function) {
514: strbuf.append(getFunction((Function) objStart));
515: }
516:
517: strbuf.append(",");
518:
519: Object objEnd = substr.getFinish();
520:
521: if (objEnd instanceof Integer) {
522: strbuf.append(((Integer) objEnd).toString());
523: } else if (objEnd instanceof String) {
524: strbuf.append((String) objEnd);
525: } else if (objEnd instanceof Function) {
526: strbuf.append(getFunction((Function) objEnd));
527: }
528:
529: strbuf.append(")");
530:
531: return strbuf.toString();
532: }
533:
534: /**
535: * Returns the SQL 'length' statement from the given
536: * <code>Length</code> <code>Function</code>.
537: *
538: * @param func the length function
539: * @return the SQL 'length' statement
540: * @throws DataStoreException if an error occurs
541: */
542: private String getLength(Length func) throws DataStoreException {
543: StringBuffer strbuf = new StringBuffer();
544:
545: strbuf.append("LEN(");
546:
547: Object lenObj = func.getLengthObject();
548:
549: if (lenObj instanceof String) {
550: strbuf.append(lenObj);
551: } else if (lenObj instanceof ColumnRef) {
552: strbuf.append(((ColumnRef) lenObj).getFullRef());
553: }
554:
555: strbuf.append(")");
556:
557: return strbuf.toString();
558: }
559:
560: /* (non-Javadoc)
561: * @see org.openharmonise.commons.dsi.AbstractDataStoreInterface#createTable(org.openharmonise.commons.dsi.ddl.TableDefinition)
562: */
563: public void createTable(TableDefinition tblDef)
564: throws DataStoreException {
565: StringBuffer str = new StringBuffer();
566:
567: str.append("create table ").append(tblDef.getName());
568: str.append(" (");
569:
570: Iterator iter = tblDef.iterator();
571:
572: while (iter.hasNext()) {
573: ColumnDefinition coldef = (ColumnDefinition) iter.next();
574:
575: str.append(coldef.getName());
576: str.append(" ");
577: int nDataType = coldef.getDataType();
578:
579: if (nDataType == ColumnDefinition.NUMBER) {
580: str.append(TYPE_INT);
581: } else if (nDataType == ColumnDefinition.TEXT) {
582: str.append(TYPE_NVARCHAR_255);
583: } else if (nDataType == ColumnDefinition.LONG_TEXT) {
584: str.append(TYPE_NTEXT);
585: } else if (nDataType == ColumnDefinition.DATE) {
586: str.append(getDateDataType());
587: } else if (nDataType == ColumnDefinition.BOOLEAN) {
588: str.append(getBooleanDataType());
589: }
590:
591: if (coldef.isPrimaryKey()) {
592: str.append(" ").append(KEYWORD_PRIMARY_KEY);
593: } else if (coldef.isUnique()) {
594: str.append(" ").append(KEYWORD_UNIQUE);
595: }
596:
597: Object defaultVal = coldef.getDefault();
598:
599: if (defaultVal != null) {
600:
601: str.append(" ").append(KEYWORD_DEFAULT).append(" ");
602:
603: if (defaultVal instanceof String
604: && (nDataType == ColumnDefinition.TEXT || nDataType == ColumnDefinition.LONG_TEXT)) {
605: str.append(defaultVal);
606: } else if (defaultVal instanceof Date) {
607:
608: } else if (defaultVal instanceof Integer) {
609: str.append(((Integer) defaultVal).intValue());
610: }
611: }
612:
613: if (coldef.allowNulls() == false) {
614: str.append(" ").append(KEYWORD_NOT_NULL);
615: }
616:
617: if (coldef.isForeignKey()) {
618: str.append(" ").append(KEYWORD_FOREIGN_KEY).append(" ")
619: .append(KEYWORD_REFERENCES).append(" ").append(
620: coldef.getForeignKeyReference());
621: }
622:
623: if (iter.hasNext()) {
624: str.append(",");
625: }
626: }
627:
628: str.append(")");
629:
630: execute(str.toString());
631:
632: }
633:
634: }
|