|
1
19 package org.openharmonise.commons.dsi.impl;
20
21 import java.io.*;
22 import java.sql.*;
23 import java.text.*;
24 import java.util.*;
25 import java.util.Date  ;
26 import java.util.logging.*;
27 import java.util.logging.Level ;
28
29 import org.openharmonise.commons.dsi.*;
30 import org.openharmonise.commons.dsi.ddl.*;
31 import org.openharmonise.commons.dsi.dml.*;
32 import org.openharmonise.commons.dsi.dml.functions.*;
33
34
35
36
43 public class DataStoreInterfaceSQLServer extends AbstractDataStoreInterface {
44
45 private static final String TYPE_NTEXT = "NTEXT";
46 private static final String TYPE_NVARCHAR_255 = "NVARCHAR (255)";
47 private static final String TYPE_INT = "INT";
48 private static final String KEYWORD_PRIMARY_KEY = "PRIMARY KEY";
49 private static final String KEYWORD_UNIQUE = "UNIQUE";
50 private static final String KEYWORD_DEFAULT = "DEFAULT";
51 private static final String KEYWORD_NOT_NULL = "NOT NULL";
52 private static final String KEYWORD_FOREIGN_KEY = "FOREIGN KEY";
53 private static final String KEYWORD_REFERENCES = "REFERENCES";
54
55 public static Date SQLSERVER_CONVERSION_DATE_LIMIT = null;
56
57
60 private static final Logger m_logger = Logger.getLogger(DataStoreInterfaceSQLServer.class.getName());
61
62 static {
64 String date_format = "MM-dd-yyyy";
65 SimpleDateFormat format = new SimpleDateFormat(date_format);
66
67 try {
68 SQLSERVER_CONVERSION_DATE_LIMIT = format.parse("01-01-1753");
69 } catch (ParseException e) {
70 m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
71 }
72 m_bIsNationalCharacterSupported = true;
73 }
74
75
78 public DataStoreInterfaceSQLServer() {
79 }
80
81
88 public DataStoreInterfaceSQLServer(int nConnectionType)
89 throws Exception {
90 super(nConnectionType);
91 }
92
93
94
97 public int getSequenceNextValue(String sSeqName) throws DataStoreException,
98 SQLException {
99 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
108 stmt = conn.createStatement();
109
110 sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='" + sSeqName +
111 "'";
112 stmt.executeUpdate(sSql);
113
114 sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName + "'";
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
140
143 public void insertClob(String sTable, String sColumn, String sClob,
144 String sCondition) throws DataStoreException {
145 Connection conn = null;
146 Statement stmt = null;
147 ResultSet rs = null;
148
149 if ((sCondition == null) || (sCondition.length() == 0)) {
150 throw new DataStoreException("Missing CLOB condition");
151 }
152
153 StringBuffer sSql = new StringBuffer ();
154
155 try {
156 conn = getConnection();
157 stmt = conn.createStatement();
158
159 sSql.append("update ");
160 sSql.append(sTable);
161 sSql.append(" set ");
162 sSql.append(sColumn);
163 sSql.append(" = N'");
164 sSql.append(addEscapeChars(sClob));
165 sSql.append("' where ");
166 sSql.append(sCondition);
167
168 stmt.execute(sSql.toString());
169
170 if (isPooledConnection() && (conn != null)) {
171 this.closeConnection(conn);
172 }
173 } catch (SQLException e) {
174 throw new DataStoreException("SQLException: " + e.getMessage());
175 }
176 }
177
178
179
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 sSql.append("update ");
199 sSql.append(sTable);
200 sSql.append(" set ");
201 sSql.append(sColumn);
202 sSql.append(" = N'");
203 sSql.append(addEscapeChars(sClob));
204 sSql.append("' where ");
205 sSql.append(sCondition);
206
207 stmt.execute(sSql.toString());
208
209 if (stmt != null) {
210 stmt.close();
211 }
212
213 if (rs != null) {
214 rs.close();
215 }
216
217 if (isPooledConnection() && (conn != null)) {
218 this.closeConnection(conn);
219 }
220 } catch (SQLException e) {
221 throw new DataStoreException("SQLException: " + e.getMessage());
222 }
223 }
224
225
228 public String getClob(String sTable, String sColumn, String sCondition)
229 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
245 stmt = conn.createStatement();
246
247 sSql.append("select ");
248 sSql.append(sColumn);
249 sSql.append(" from ");
250 sSql.append(sTable);
251 sSql.append(" where ");
252 sSql.append(sCondition);
253
254 try {
255 rs = stmt.executeQuery(sSql.toString());
256 } catch (SQLException e) {
257 throw new DataStoreException(sSql.toString() + " : " +
258 e.getMessage());
259 }
260
261 if (rs.next()) {
262 Reader is = rs.getCharacterStream(1);
263 BufferedReader buffR = new BufferedReader(is);
264
265 StringBuffer sBuffContent = new StringBuffer ();
266 String sTemp = buffR.readLine();
267
268 while(sTemp!=null) {
269 sBuffContent.append(sTemp);
270 sTemp = buffR.readLine();
271 }
272 sReturn = sBuffContent.toString();
273 }
274
275 if (stmt != null) {
276 stmt.close();
277 }
278
279 if (rs != null) {
280 rs.close();
281 }
282
283 if (isPooledConnection() && (conn != null)) {
284 this.closeConnection(conn);
285 }
286 } catch (SQLException e) {
287 throw new DataStoreException("SQLException",e);
288 } catch (UnsupportedEncodingException e) {
289 m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
290 } catch (IOException e) {
291 m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
292 }
293
294 return sReturn;
295 }
296
297
298
301 protected String addEscapeChars(String sOldString) {
302 int marker = -1;
303 int lastmarker = 0;
304 int quotemarker = -1;
305
306 if (sOldString == null) {
307 return "";
308 }
309
310 StringBuffer sBuf = new StringBuffer ();
311
312 quotemarker = sOldString.indexOf("'");
313
314 if (quotemarker >= 0) {
315 marker = quotemarker;
316 }
317
318 if (marker < 0) {
319 return sOldString;
320 } else {
321 while (marker >= 0) {
322 sBuf.append(sOldString.substring(lastmarker, marker));
324 sBuf.append("'");
325
326
327 quotemarker = -1;
329 lastmarker = marker;
330
331 quotemarker = sOldString.indexOf("'", marker + 1);
332
333 if (quotemarker >= 0) {
334 marker = quotemarker;
335 } else {
336 marker = -1;
337 }
338 }
339
340 sBuf.append(sOldString.substring(lastmarker));
341
342 return (sBuf.toString());
343 }
344 }
345
346
349 public String getDateDataType() {
350 return "DATETIME";
351 }
352
353
356 public String getCLOBDataType() {
357 return TYPE_NTEXT;
358 }
359
360
363 public String getBooleanDataType() {
364 return "BIT";
365 }
366
367
370 public List getTableList() throws DataStoreException {
371 Vector tables = new Vector();
372 ResultSet rs = null;
373
374 try {
375 rs = executeQuery(
376 "select name from sysobjects where type = 'u' and name != 'dtproperties' order by name");
377
378 while (rs.next()) {
379 tables.add(rs.getString(1).trim());
380 }
381
382 rs.close();
383 } catch (SQLException e) {
384 throw new DataStoreException(e);
385 }
386
387 return tables;
388 }
389
390
393 public List getSequenceList() throws DataStoreException {
394 Vector seqs = new Vector();
395 ResultSet rs = null;
396
397 try {
398 rs = executeQuery("select seq_name from oh_seq order by seq_name");
399
400 while (rs.next()) {
401 seqs.add(rs.getString(1).trim());
402 }
403
404 rs.close();
405 } catch (SQLException e) {
406 throw new DataStoreException(e);
407 }
408
409 return seqs;
410 }
411
412
415 protected String getDateAsSQL(String date) {
416 StringBuffer sSql = new StringBuffer ();
419
420 sSql.append(" CONVERT(datetime, '");
421 sSql.append(date);
422 sSql.append("', 120 )");
423
424 return sSql.toString();
425 }
426
427
430 public String getJoinCondition(ColumnRef ref1, ColumnRef ref2,
431 boolean bIsOuter) {
432 StringBuffer sSql = new StringBuffer ();
433
434 sSql.append(ref1.getFullRef());
435 sSql.append("=");
436
437 if (bIsOuter == true) {
438 sSql.append("*");
439 }
440
441 sSql.append(ref2.getFullRef());
442
443 return sSql.toString();
444 }
445
446
449 protected String getFunction(Function func) throws DataStoreException {
450
451
452 String sFunc = null;
453
454 if(func instanceof Substring) {
455 sFunc = getSubstring((Substring) func);
456
457 } else if(func instanceof Length) {
458 sFunc = getLength((Length) func);
459
460 } else if(func instanceof ToDate) {
461 sFunc = getToDate((ToDate) func);
462
463 } else {
464 throw new DataStoreException("Function not supported - " + func.getClass().getName());
465 }
466
467 return sFunc;
468 }
469
470
471
478 private String getToDate(ToDate date) {
479 StringBuffer strbuf = new StringBuffer ();
480
481 strbuf.append("CONVERT(datetime,");
482
483 Object objVal = date.getValue();
484
485 if(objVal instanceof ColumnRef) {
486 strbuf.append(((ColumnRef)objVal).getFullRef());
487 } else if(objVal instanceof String ) {
488 strbuf.append("'").append(objVal).append("'");
489 }
490
491 strbuf.append(", 120 )");
492 return strbuf.toString();
493 }
494
495
503 private String getSubstring(Substring substr) throws DataStoreException {
504
505 StringBuffer strbuf = new StringBuffer ();
506
507 strbuf.append("SUBSTRING('").append(substr.getString()).append("',");
508
509 Object objStart = substr.getStart();
510
511 if(objStart instanceof Integer ) {
512 strbuf.append(((Integer )objStart).toString());
513 } else if(objStart instanceof String ) {
514 strbuf.append((String )objStart);
515 } else if(objStart instanceof Function) {
516 strbuf.append(getFunction((Function)objStart));
517 }
518
519 strbuf.append(",");
520
521 Object objEnd = substr.getFinish();
522
523 if(objEnd instanceof Integer ) {
524 strbuf.append(((Integer )objEnd).toString());
525 } else if(objEnd instanceof String ) {
526 strbuf.append((String )objEnd);
527 } else if(objEnd instanceof Function) {
528 strbuf.append(getFunction((Function)objEnd));
529 }
530
531 strbuf.append(")");
532
533 return strbuf.toString();
534 }
535
536
544 private String getLength(Length func) throws DataStoreException {
545 StringBuffer strbuf = new StringBuffer ();
546
547 strbuf.append("LEN(");
548
549 Object lenObj = func.getLengthObject();
550
551 if(lenObj instanceof String ) {
552 strbuf.append(lenObj);
553 } else if(lenObj instanceof ColumnRef) {
554 strbuf.append(((ColumnRef)lenObj).getFullRef());
555 }
556
557 strbuf.append(")");
558
559 return strbuf.toString();
560 }
561
562
565 public void createTable(TableDefinition tblDef) throws DataStoreException {
566 StringBuffer str = new StringBuffer ();
567
568 str.append("create table ")
569 .append(tblDef.getName());
570 str.append(" (");
571
572 Iterator iter = tblDef.iterator();
573
574 while (iter.hasNext()) {
575 ColumnDefinition coldef = (ColumnDefinition) iter.next();
576
577 str.append(coldef.getName());
578 str.append(" ");
579 int nDataType = coldef.getDataType();
580
581 if(nDataType == ColumnDefinition.NUMBER) {
582 str.append(TYPE_INT);
583 } else if(nDataType == ColumnDefinition.TEXT) {
584 str.append(TYPE_NVARCHAR_255);
585 } else if(nDataType == ColumnDefinition.LONG_TEXT) {
586 str.append(TYPE_NTEXT);
587 } else if(nDataType == ColumnDefinition.DATE) {
588 str.append(getDateDataType());
589 } else if(nDataType == ColumnDefinition.BOOLEAN) {
590 str.append(getBooleanDataType());
591 }
592
593
594
595 if(coldef.isPrimaryKey()) {
596 str.append(" ").append(KEYWORD_PRIMARY_KEY);
597 } else if(coldef.isUnique()) {
598 str.append(" ").append(KEYWORD_UNIQUE);
599 }
600
601 Object defaultVal = coldef.getDefault();
602
603 if(defaultVal != null) {
604
605 str.append(" ").append(KEYWORD_DEFAULT).append(" ");
606
607 if(defaultVal instanceof String && (nDataType == ColumnDefinition.TEXT
608 || nDataType == ColumnDefinition.LONG_TEXT)) {
609 str.append(defaultVal);
610 } else if(defaultVal instanceof Date ) {
611
612 } else if(defaultVal instanceof Integer ) {
613 str.append(((Integer )defaultVal).intValue());
614 }
615 }
616
617 if(coldef.allowNulls() == false) {
618 str.append(" ").append(KEYWORD_NOT_NULL);
619 }
620
621 if(coldef.isForeignKey()) {
622 str.append(" ").append(KEYWORD_FOREIGN_KEY)
623 .append(" ").append(KEYWORD_REFERENCES)
624 .append(" ").append(coldef.getForeignKeyReference());
625 }
626
627 if(iter.hasNext()) {
628 str.append(",");
629 }
630 }
631
632 str.append(")");
633
634 execute(str.toString());
635
636 }
637
638 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.