1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19 package org.apache.openjpa.jdbc.sql;
20
21 import java.lang.reflect.Method;
22 import java.sql.Connection;
23 import java.sql.DatabaseMetaData;
24 import java.sql.SQLException;
25 import java.sql.Types;
26 import java.util.Arrays;
27 import java.util.StringTokenizer;
28
29 import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration;
30 import org.apache.openjpa.jdbc.kernel.exps.FilterValue;
31 import org.apache.openjpa.jdbc.kernel.exps.Lit;
32 import org.apache.openjpa.jdbc.kernel.exps.Param;
33 import org.apache.openjpa.jdbc.kernel.exps.Val;
34 import org.apache.openjpa.jdbc.schema.Column;
35 import org.apache.openjpa.jdbc.schema.Index;
36 import org.apache.openjpa.jdbc.schema.Schema;
37 import org.apache.openjpa.jdbc.schema.Sequence;
38 import org.apache.openjpa.jdbc.schema.Table;
39 import org.apache.openjpa.kernel.Filters;
40 import org.apache.openjpa.lib.util.Localizer;
41 import org.apache.openjpa.meta.JavaTypes;
42 import org.apache.openjpa.util.OpenJPAException;
43 import org.apache.openjpa.util.UnsupportedException;
44
45 import serp.util.Strings;
46
47 /**
48 * Dictionary for IBM DB2 database.
49 */
50 public class DB2Dictionary
51 extends AbstractDB2Dictionary {
52 private static final Localizer _loc = Localizer.forPackage
53 (DB2Dictionary.class);
54
55 public String optimizeClause = "optimize for";
56 public String rowClause = "row";
57 protected int db2ServerType = 0;
58 public static final int db2ISeriesV5R3OrEarlier = 1;
59 public static final int db2UDBV81OrEarlier = 2;
60 public static final int db2ZOSV8xOrLater = 3;
61 public static final int db2UDBV82OrLater = 4;
62 public static final int db2ISeriesV5R4OrLater = 5;
63 private static final String forUpdate = "FOR UPDATE";
64 private static final String withRSClause = "WITH RS";
65 private static final String withRRClause = "WITH RR";
66 private static final String useKeepUpdateLockClause
67 = "USE AND KEEP UPDATE LOCKS";
68 private static final String useKeepExclusiveLockClause
69 = "USE AND KEEP EXCLUSIVE LOCKS";
70 private static final String forReadOnlyClause = "FOR READ ONLY";
71 protected String databaseProductName = null;
72 protected String databaseProductVersion = null;
73 protected int maj = 0;
74 protected int min = 0;
75
76 private int defaultBatchLimit = 100;
77
78 public DB2Dictionary() {
79 platform = "DB2";
80 validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
81 + "SYSIBM.SYSTABLES";
82 supportsSelectEndIndex = true;
83
84 nextSequenceQuery = "VALUES NEXTVAL FOR {0}";
85
86 sequenceSQL = "SELECT SEQSCHEMA AS SEQUENCE_SCHEMA, "
87 + "SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES";
88 sequenceSchemaSQL = "SEQSCHEMA = ?";
89 sequenceNameSQL = "SEQNAME = ?";
90 characterColumnSize = 254;
91
92 binaryTypeName = "BLOB(1M)";
93 longVarbinaryTypeName = "BLOB(1M)";
94 varbinaryTypeName = "BLOB(1M)";
95 clobTypeName = "CLOB(1M)";
96 longVarcharTypeName = "LONG VARCHAR";
97 datePrecision = MICRO;
98 storeCharsAsNumbers = false;
99
100 fixedSizeTypeNameSet.addAll(Arrays.asList(new String[]{
101 "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR", "LONG VARGRAPHIC",
102 }));
103 systemSchemas = new String(
104 "SYSCAT,SYSIBM,SYSSTAT,SYSIBMADM,SYSTOOLS");
105 maxConstraintNameLength = 18;
106 maxIndexNameLength = 18;
107 maxColumnNameLength = 30;
108 supportsDeferredConstraints = false;
109 supportsDefaultDeleteAction = false;
110 supportsAlterTableWithDropColumn = false;
111
112 supportsNullTableForGetColumns = false;
113 requiresCastForMathFunctions = true;
114 requiresCastForComparisons = true;
115
116 reservedWordSet.addAll(Arrays.asList(new String[]{
117 "AFTER", "ALIAS", "ALLOW", "APPLICATION", "ASSOCIATE", "ASUTIME",
118 "AUDIT", "AUX", "AUXILIARY", "BEFORE", "BINARY", "BUFFERPOOL",
119 "CACHE", "CALL", "CALLED", "CAPTURE", "CARDINALITY", "CCSID",
120 "CLUSTER", "COLLECTION", "COLLID", "COMMENT", "CONCAT",
121 "CONDITION", "CONTAINS", "COUNT_BIG", "CURRENT_LC_CTYPE",
122 "CURRENT_PATH", "CURRENT_SERVER", "CURRENT_TIMEZONE", "CYCLE",
123 "DATA", "DATABASE", "DAYS", "DB2GENERAL", "DB2GENRL", "DB2SQL",
124 "DBINFO", "DEFAULTS", "DEFINITION", "DETERMINISTIC", "DISALLOW",
125 "DO", "DSNHATTR", "DSSIZE", "DYNAMIC", "EACH", "EDITPROC", "ELSEIF",
126 "ENCODING", "END-EXEC1", "ERASE", "EXCLUDING", "EXIT", "FENCED",
127 "FIELDPROC", "FILE", "FINAL", "FREE", "FUNCTION", "GENERAL",
128 "GENERATED", "GRAPHIC", "HANDLER", "HOLD", "HOURS", "IF",
129 "INCLUDING", "INCREMENT", "INDEX", "INHERIT", "INOUT", "INTEGRITY",
130 "ISOBID", "ITERATE", "JAR", "JAVA", "LABEL", "LC_CTYPE", "LEAVE",
131 "LINKTYPE", "LOCALE", "LOCATOR", "LOCATORS", "LOCK", "LOCKMAX",
132 "LOCKSIZE", "LONG", "LOOP", "MAXVALUE", "MICROSECOND",
133 "MICROSECONDS", "MINUTES", "MINVALUE", "MODE", "MODIFIES", "MONTHS",
134 "NEW", "NEW_TABLE", "NOCACHE", "NOCYCLE", "NODENAME", "NODENUMBER",
135 "NOMAXVALUE", "NOMINVALUE", "NOORDER", "NULLS", "NUMPARTS", "OBID",
136 "OLD", "OLD_TABLE", "OPTIMIZATION", "OPTIMIZE", "OUT", "OVERRIDING",
137 "PACKAGE", "PARAMETER", "PART", "PARTITION", "PATH", "PIECESIZE",
138 "PLAN", "PRIQTY", "PROGRAM", "PSID", "QUERYNO", "READS", "RECOVERY",
139 "REFERENCING", "RELEASE", "RENAME", "REPEAT", "RESET", "RESIGNAL",
140 "RESTART", "RESULT", "RESULT_SET_LOCATOR", "RETURN", "RETURNS",
141 "ROUTINE", "ROW", "RRN", "RUN", "SAVEPOINT", "SCRATCHPAD",
142 "SECONDS", "SECQTY", "SECURITY", "SENSITIVE", "SIGNAL", "SIMPLE",
143 "SOURCE", "SPECIFIC", "SQLID", "STANDARD", "START", "STATIC",
144 "STAY", "STOGROUP", "STORES", "STYLE", "SUBPAGES", "SYNONYM",
145 "SYSFUN", "SYSIBM", "SYSPROC", "SYSTEM", "TABLESPACE", "TRIGGER",
146 "TYPE", "UNDO", "UNTIL", "VALIDPROC", "VARIABLE", "VARIANT", "VCAT",
147 "VOLUMES", "WHILE", "WLM", "YEARS",
148 }));
149
150 super.setBatchLimit(defaultBatchLimit);
151
152 selectWordSet.add("WITH");
153 }
154
155 public boolean supportsRandomAccessResultSet(Select sel,
156 boolean forUpdate) {
157 return !forUpdate
158 && super.supportsRandomAccessResultSet(sel, forUpdate);
159 }
160
161 protected void appendSelectRange(SQLBuffer buf, long start, long end,
162 boolean subselect) {
163 // appends the literal range string, since DB2 is unable to handle
164 // a bound parameter for it
165 // do not generate FETCH FIRST clause for subselect
166 if (!subselect)
167 buf.append(" FETCH FIRST ").append(Long.toString(end)).
168 append(" ROWS ONLY");
169 }
170
171 protected void appendSelect(SQLBuffer selectSQL, Object alias, Select sel,
172 int idx) {
173 // if this is a literal value, add a cast...
174 Object val = sel.getSelects().get(idx);
175 if (val instanceof Lit)
176 selectSQL.append("CAST(");
177
178 // ... and add the select per super's behavior...
179 super.appendSelect(selectSQL, alias, sel, idx);
180
181 // ... and finish the cast
182 if (val instanceof Lit) {
183 Class c = ((Lit) val).getType();
184 int javaTypeCode = JavaTypes.getTypeCode(c);
185 int jdbcTypeCode = getJDBCType(javaTypeCode, false);
186 String typeName = getTypeName(jdbcTypeCode);
187 selectSQL.append(" AS " + typeName);
188
189 // if the literal is a string, use the default char col size
190 // in the cast statement.
191 if (String.class.equals(c))
192 selectSQL.append("(" + characterColumnSize + ")");
193
194 selectSQL.append(")");
195 }
196 }
197
198 public String[] getCreateSequenceSQL(Sequence seq) {
199 String[] sql = super.getCreateSequenceSQL(seq);
200 if (seq.getAllocate() > 1)
201 sql[0] += " CACHE " + seq.getAllocate();
202 return sql;
203 }
204
205 protected String getSequencesSQL(String schemaName, String sequenceName) {
206 StringBuffer buf = new StringBuffer();
207 buf.append(sequenceSQL);
208 if (schemaName != null || sequenceName != null)
209 buf.append(" WHERE ");
210 if (schemaName != null) {
211 buf.append(sequenceSchemaSQL);
212 if (sequenceName != null)
213 buf.append(" AND ");
214 }
215 if (sequenceName != null)
216 buf.append(sequenceNameSQL);
217 return buf.toString();
218 }
219
220 public Connection decorate(Connection conn)
221 throws SQLException {
222 // some versions of the DB2 driver seem to default to
223 // READ_UNCOMMITTED, which will prevent locking from working
224 // (multiple SELECT ... FOR UPDATE statements are allowed on
225 // the same instance); if we have not overridden the
226 // transaction isolation in the configuration, default to
227 // TRANSACTION_READ_COMMITTED
228 conn = super.decorate(conn);
229
230 if (conf.getTransactionIsolationConstant() == -1
231 && conn.getTransactionIsolation() < conn.TRANSACTION_READ_COMMITTED)
232 conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
233
234 return conn;
235 }
236
237 private boolean isJDBC3(DatabaseMetaData meta) {
238 try {
239 // JDBC3-only method, so it might throw a AbstractMethodError
240 return meta.getJDBCMajorVersion() >= 3;
241 } catch (Throwable t) {
242 return false;
243 }
244 }
245
246 public void connectedConfiguration(Connection conn) throws SQLException {
247 super.connectedConfiguration(conn);
248
249 DatabaseMetaData metaData = conn.getMetaData();
250 databaseProductName = metaData.getDatabaseProductName();
251 databaseProductVersion = metaData.getDatabaseProductVersion();
252
253 // Determine the type of DB2 database
254 // First check for AS/400
255 getProductVersionMajorMinorForISeries();
256
257 if (maj > 0) {
258 if (isDB2ISeriesV5R3OrEarlier())
259 db2ServerType = db2ISeriesV5R3OrEarlier;
260 else if (isDB2ISeriesV5R4OrLater())
261 db2ServerType = db2ISeriesV5R4OrLater;
262 }
263
264 if (db2ServerType == 0) {
265 if (isJDBC3(metaData)) {
266 maj = metaData.getDatabaseMajorVersion();
267 min = metaData.getDatabaseMinorVersion();
268 }
269 else
270 getProductVersionMajorMinor();
271
272 // Determine the type of DB2 database for ZOS & UDB
273 if (isDB2UDBV81OrEarlier())
274 db2ServerType = db2UDBV81OrEarlier;
275 else if (isDB2ZOSV8xOrLater())
276 db2ServerType = db2ZOSV8xOrLater;
277 else if (isDB2UDBV82OrLater())
278 db2ServerType = db2UDBV82OrLater;
279 }
280
281 // verify that databae product is supported
282 if (db2ServerType == 0 || maj == 0)
283 throw new UnsupportedException(_loc.get("db-not-supported",
284 new Object[] {databaseProductName, databaseProductVersion }));
285
286 if (maj >= 9 || (maj == 8 && min >= 2)) {
287 supportsLockingWithMultipleTables = true;
288 supportsLockingWithInnerJoin = true;
289 supportsLockingWithOuterJoin = true;
290 forUpdateClause = "WITH RR USE AND KEEP UPDATE LOCKS";
291 if (maj >=9)
292 supportsXMLColumn = true;
293 }
294
295 // platform specific settings
296 switch (db2ServerType) {
297 case db2ZOSV8xOrLater:
298 // DB2 Z/OS
299 characterColumnSize = 255;
300 lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
301 + "SYSIBM.SYSDUMMY1";
302 nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
303 + "SYSIBM.SYSDUMMY1";
304 sequenceSQL = "SELECT SCHEMA AS SEQUENCE_SCHEMA, "
305 + "NAME AS SEQUENCE_NAME FROM SYSIBM.SYSSEQUENCES";
306 sequenceSchemaSQL = "SCHEMA = ?";
307 sequenceNameSQL = "NAME = ?";
308 if (maj == 8)
309 // DB2 Z/OS Version 8: no bigint support, hence map Java
310 // long to decimal
311 bigintTypeName = "DECIMAL(31,0)";
312 break;
313 case db2ISeriesV5R3OrEarlier:
314 case db2ISeriesV5R4OrLater:
315 lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
316 + "SYSIBM.SYSDUMMY1";
317 nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
318 + "SYSIBM.SYSDUMMY1";
319 validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
320 + "QSYS2.SYSTABLES";
321 sequenceSQL = "SELECT SEQUENCE_SCHEMA, "
322 + "SEQUENCE_NAME FROM QSYS2.SYSSEQUENCES";
323 sequenceSchemaSQL = "SEQUENCE_SCHEMA = ?";
324 sequenceNameSQL = "SEQUENCE_NAME = ?";
325 break;
326 }
327 }
328
329 /**
330 * Get the update clause for the query based on the
331 * updateClause and isolationLevel hints
332 */
333 protected String getForUpdateClause(JDBCFetchConfiguration fetch,
334 boolean isForUpdate, Select sel) {
335 int isolationLevel;
336 // For db2UDBV81OrEarlier and db2ISeriesV5R3OrEarlier:
337 // "optimize for" clause appears before "for update" clause.
338 StringBuffer forUpdateString = new StringBuffer(
339 getOptimizeClause(sel));
340 try {
341 // Determine the isolationLevel; the fetch
342 // configuration data overrides the persistence.xml value
343 if (fetch != null && fetch.getIsolation() != -1)
344 isolationLevel = fetch.getIsolation();
345 else
346 isolationLevel = conf.getTransactionIsolationConstant();
347
348 if (isForUpdate) {
349 switch(db2ServerType) {
350 case db2ISeriesV5R3OrEarlier:
351 case db2UDBV81OrEarlier:
352 if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE)
353 forUpdateString.append(" ").append(forUpdateClause);
354 else
355 forUpdateString.append(" ").append(forUpdate)
356 .append(" ").append(withRSClause);
357 break;
358 case db2ZOSV8xOrLater:
359 case db2UDBV82OrLater:
360 if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
361 forUpdateString.append(" ").append(forReadOnlyClause)
362 .append(" ").append(withRRClause)
363 .append(" ").append(useKeepUpdateLockClause);
364 } else {
365 forUpdateString.append(" ").append(forReadOnlyClause)
366 .append(" ").append(withRSClause)
367 .append(" ").append(useKeepUpdateLockClause);
368 }
369 break;
370 case db2ISeriesV5R4OrLater:
371 if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
372 forUpdateString.append(" ").append(forReadOnlyClause)
373 .append(" ").append(withRRClause)
374 .append(" ").append(useKeepExclusiveLockClause);
375 } else {
376 forUpdateString.append(" ").append(forReadOnlyClause)
377 .append(" ").append(withRSClause)
378 .append(" ").append(useKeepExclusiveLockClause);
379 }
380 break;
381 }
382 }
383 }
384 catch (Exception e) {
385 if (log.isTraceEnabled())
386 log.error(e.toString(),e);
387 }
388 return forUpdateString.toString();
389 }
390
391 public boolean isDB2UDBV82OrLater() {
392 boolean match = false;
393 if (databaseProductName != null &&
394 (databaseProductVersion.indexOf("SQL") != -1
395 || databaseProductName.indexOf("DB2/") != -1)
396 && ((maj == 8 && min >= 2) || (maj >= 9)))
397 match = true;
398 return match;
399 }
400
401 public boolean isDB2ZOSV8xOrLater() {
402 boolean match = false;
403 if (databaseProductName != null &&
404 (databaseProductVersion.indexOf("DSN") != -1
405 || databaseProductName.indexOf("DB2/") == -1)
406 && maj >= 8)
407 match = true;
408 return match;
409 }
410
411 public boolean isDB2ISeriesV5R3OrEarlier() {
412 boolean match = false;
413 if (databaseProductName != null &&
414 databaseProductName.indexOf("AS") != -1
415 && ((maj == 5 && min <=3) || maj < 5))
416 match = true;
417 return match;
418 }
419
420 public boolean isDB2ISeriesV5R4OrLater() {
421 boolean match = false;
422 if (databaseProductName != null &&
423 databaseProductName.indexOf("AS") != -1
424 && (maj >=6 || (maj == 5 && min >=4)))
425 match = true;
426 return match;
427 }
428
429 public boolean isDB2UDBV81OrEarlier() {
430 boolean match = false;
431 if (databaseProductName != null &&
432 (databaseProductVersion.indexOf("SQL") != -1
433 || databaseProductName.indexOf("DB2/") != -1) &&
434 ((maj == 8 && min <= 1) || maj < 8))
435 match = true;
436 return match;
437 }
438
439 /** Get the version Major/Minor for the ISeries
440 */
441 private void getProductVersionMajorMinorForISeries() {
442 // ISeries DBProdName DB2 UDB for AS/400
443 // (Toolbox)DBProdVersion 05.04.0000 V5R4m0
444 // ISeries DB2 UDB for AS/400
445 // (Native) V5R4M0
446 if (databaseProductName.indexOf("AS") != -1) {
447 String s = databaseProductVersion.substring(databaseProductVersion
448 .indexOf('V'));
449 s = s.toUpperCase();
450
451 StringTokenizer stringtokenizer = new StringTokenizer(s, "VRM"
452 , false);
453 if (stringtokenizer.countTokens() == 3) {
454 String s1 = stringtokenizer.nextToken();
455 maj = Integer.parseInt(s1);
456 String s2 = stringtokenizer.nextToken();
457 min = Integer.parseInt(s2);
458 }
459 }
460 }
461
462 private void getProductVersionMajorMinor() {
463 // Incase JDBC driver version is lower than 3
464 // use following info to determine Major and Minor
465 // CLI vs JCC
466 // ZDBV8 DBProdName DB2 DB2
467 // DBProdVersion 08.01.0005 DSN08015
468 // ZDBV9 DB2 DB2
469 // 09.01.0005 DSN09015
470 // WinV9 DB2/NT DB2/NT
471 // 09.01.0000 SQL09010
472 // SolarisV9 DB2/SUN64
473 // SQL0901
474 // Linux DB2/LINUX DB2/LINUX
475 // 09.01.0000 SQL0901
476 if (databaseProductVersion.indexOf("09") != -1) {
477 maj = 9;
478 if (databaseProductVersion.indexOf("01") != -1) {
479 min = 1;
480 }
481 } else if (databaseProductVersion.indexOf("08") != -1) {
482 maj = 8;
483 min = 2;
484 if (databaseProductVersion.indexOf("01") != -1) {
485 min = 1;
486 }
487 }
488 }
489
490 protected String getOptimizeClause(Select sel) {
491 if (sel != null && sel.getExpectedResultCount() > 0) {
492 StringBuffer buf = new StringBuffer();
493 buf.append(" ").append(optimizeClause).append(" ")
494 .append(String.valueOf(sel.getExpectedResultCount()))
495 .append(" ").append(rowClause);
496 return buf.toString();
497 }
498
499 return "";
500 }
501
502 public OpenJPAException newStoreException(String msg, SQLException[] causes,
503 Object failed) {
504 if (causes != null && causes.length > 0)
505 msg = appendExtendedExceptionMsg(msg, causes[0]);
506 return super.newStoreException(msg, causes, failed);
507 }
508
509 /**
510 * Append exception information from SQLCA to the exsisting
511 * exception meassage
512 */
513 private String appendExtendedExceptionMsg(String msg, SQLException sqle){
514 final String GETSQLCA ="getSqlca";
515 String exceptionMsg = new String();
516 try {
517 Method sqlcaM2 = sqle.getNextException().getClass()
518 .getMethod(GETSQLCA,null);
519 Object sqlca = sqlcaM2.invoke(sqle.getNextException(),
520 new Object[] {});
521 Method getSqlErrpMethd = sqlca.getClass().
522 getMethod("getSqlErrp", null);
523 Method getSqlWarnMethd = sqlca.getClass().
524 getMethod("getSqlWarn", null);
525 Method getSqlErrdMethd = sqlca.getClass().
526 getMethod("getSqlErrd", null);
527 StringBuffer errdStr = new StringBuffer();
528
529 int[] errds = (int[]) getSqlErrdMethd.invoke(sqlca, new Object[]{});
530 for (int i = 0; i < errds.length; i++)
531 errdStr.append(errdStr.length() > 0 ? ", " : "").
532 append(errds[i]);
533 exceptionMsg = exceptionMsg.concat( "SQLCA OUTPUT" +
534 "[Errp=" + getSqlErrpMethd.invoke(sqlca, new Object[]{})
535 + ", Errd=" + errdStr);
536
537 String Warn = new String((char[]) getSqlWarnMethd.
538 invoke(sqlca, new Object[]{}));
539 if(Warn.trim().length() != 0)
540 exceptionMsg = exceptionMsg.concat(", Warn=" +Warn + "]" );
541 else
542 exceptionMsg = exceptionMsg.concat( "]" );
543 msg = msg.concat(exceptionMsg);
544 return msg;
545 } catch (Throwable t) {
546 return sqle.getMessage();
547 }
548 }
549
550 public int getDb2ServerType() {
551 return db2ServerType;
552 }
553
554 protected void appendLength(SQLBuffer buf, int type) {
555 if (type == Types.VARCHAR)
556 buf.append("(").append(Integer.toString(characterColumnSize)).
557 append(")");
558 }
559
560 /**
561 * If this dictionary supports XML type,
562 * use this method to append xml predicate.
563 *
564 * @param buf the SQL buffer to write the comparison
565 * @param op the comparison operation to perform
566 * @param lhs the left hand side of the comparison
567 * @param rhs the right hand side of the comparison
568 * @param lhsxml indicates whether the left operand maps to xml
569 * @param rhsxml indicates whether the right operand maps to xml
570 */
571 public void appendXmlComparison(SQLBuffer buf, String op, FilterValue lhs,
572 FilterValue rhs, boolean lhsxml, boolean rhsxml) {
573 super.appendXmlComparison(buf, op, lhs, rhs, lhsxml, rhsxml);
574 if (lhsxml && rhsxml)
575 appendXmlComparison2(buf, op, lhs, rhs);
576 else if (lhsxml)
577 appendXmlComparison1(buf, op, lhs, rhs);
578 else
579 appendXmlComparison1(buf, op, rhs, lhs);
580 }
581
582 /**
583 * Append an xml comparison predicate.
584 *
585 * @param buf the SQL buffer to write the comparison
586 * @param op the comparison operation to perform
587 * @param lhs the left hand side of the comparison (maps to xml column)
588 * @param rhs the right hand side of the comparison
589 */
590 private void appendXmlComparison1(SQLBuffer buf, String op,
591 FilterValue lhs, FilterValue rhs) {
592 boolean castrhs = false;
593 Class rc = Filters.wrap(rhs.getType());
594 int type = 0;
595 if (rhs.isConstant()) {
596 type = getJDBCType(JavaTypes.getTypeCode(rc), false);
597 castrhs = true;
598 }
599
600 appendXmlExists(buf, lhs);
601
602 buf.append(" ").append(op).append(" ");
603
604 buf.append("$");
605 if (castrhs)
606 buf.append("Parm");
607 else
608 rhs.appendTo(buf);
609
610 buf.append("]' PASSING ");
611 appendXmlVar(buf, lhs);
612 buf.append(", ");
613
614 if (castrhs)
615 appendCast(buf, rhs, type);
616 else
617 rhs.appendTo(buf);
618
619 buf.append(" AS \"");
620 if (castrhs)
621 buf.append("Parm");
622 else
623 rhs.appendTo(buf);
624 buf.append("\")");
625 }
626
627 /**
628 * Append an xml comparison predicate. (both operands map to xml column)
629 *
630 * @param buf the SQL buffer to write the comparison
631 * @param op the comparison operation to perform
632 * @param lhs the left hand side of the comparison (maps to xml column)
633 * @param rhs the right hand side of the comparison (maps to xml column)
634 */
635 private void appendXmlComparison2(SQLBuffer buf, String op,
636 FilterValue lhs, FilterValue rhs) {
637 appendXmlExists(buf, lhs);
638
639 buf.append(" ").append(op).append(" ");
640
641 buf.append("$").append(rhs.getColumnAlias(
642 rhs.getFieldMapping().getColumns()[0])).
643 append("/*/");
644 rhs.appendTo(buf);
645
646 buf.append("]' PASSING ");
647 appendXmlVar(buf, lhs);
648 buf.append(", ");
649 appendXmlVar(buf, rhs);
650 buf.append(")");
651 }
652
653 private void appendXmlVar(SQLBuffer buf, FilterValue val) {
654 buf.append(val.getColumnAlias(
655 val.getFieldMapping().getColumns()[0])).
656 append(" AS ").
657 append("\"").append(val.getColumnAlias(
658 val.getFieldMapping().getColumns()[0])).
659 append("\"");
660 }
661
662 private void appendXmlExists(SQLBuffer buf, FilterValue val) {
663 buf.append("XMLEXISTS('");
664 buf.append("$").append(val.getColumnAlias(
665 val.getFieldMapping().getColumns()[0])).
666 append("/*[");
667 val.appendTo(buf);
668 }
669
670 /**
671 * add CAST for a scalar function where operand is a param
672 *
673 * @param func original string
674 * @param target substring to look for
675 * @param asString
676 * @return updated string (func)
677 */
678 private String addCastAsString(String func, String target,
679 String asString) {
680 String fstring = func;
681 if (func.indexOf(target) != -1)
682 fstring = Strings.replace(
683 func, target, "CAST(" + target + asString + ")");
684 return fstring;
685 }
686
687 /**
688 * add CAST for a function operator where operand is a param
689 *
690 * @param func function name
691 * @param val type
692 * @return updated string (func)
693 */
694 public String addCastAsType(String func, Val val) {
695 String fstring = null;
696 String type = getTypeName(getJDBCType(JavaTypes.getTypeCode(val
697 .getType()), false));
698 if (String.class.equals(val.getType()))
699 type = type + "(" + characterColumnSize + ")";
700 fstring = "CAST(? AS " + type + ")";
701 return fstring;
702 }
703
704 /**
705 * Return the batch limit. If the batchLimit is -1, change it to 100 for
706 * best performance
707 */
708 public int getBatchLimit() {
709 int limit = super.getBatchLimit();
710 if (limit == UNLIMITED) {
711 limit = defaultBatchLimit;
712 if (log.isTraceEnabled())
713 log.trace(_loc.get("batch_unlimit", String.valueOf(limit)));
714 }
715 return limit;
716 }
717
718 /**
719 * Return the correct CAST function syntax
720 *
721 * @param val operand of cast
722 * @param func original string
723 * @return a String with the correct CAST function syntax
724 */
725 public String getCastFunction(Val val, String func) {
726 if (val instanceof Lit || val instanceof Param)
727 if (func.indexOf("VARCHAR") == -1)
728 func = addCastAsString(func, "{0}", " AS VARCHAR(1000)");
729 return func;
730 }
731
732 public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find,
733 FilterValue start) {
734 if (find.getValue() != null) { // non constants
735 buf.append("(LOCATE(CAST((");
736 find.appendTo(buf);
737 buf.append(") AS VARCHAR(1000)), ");
738 } else {
739 // this is a constant
740 buf.append("(LOCATE(");
741 find.appendTo(buf);
742 buf.append(", ");
743 }
744 if (str.getValue() != null) {
745 buf.append("CAST((");
746 str.appendTo(buf);
747 buf.append(") AS VARCHAR(1000))");
748 } else {
749 str.appendTo(buf);
750 }
751 if (start != null) {
752 if (start.getValue() == null) {
753 buf.append(", CAST((");
754 start.appendTo(buf);
755 buf.append(") AS INTEGER) + 1");
756 } else {
757 buf.append(", ");
758 start.appendTo(buf);
759 }
760 }
761 buf.append(") - 1)");
762 }
763
764 /**
765 * Cast the specified value to the specified type.
766 *
767 * @param buf the buffer to append the cast to
768 * @param val the value to cast
769 * @param type the type of the case, e.g. {@link Types#NUMERIC}
770 */
771 public void appendCast(SQLBuffer buf, FilterValue val, int type) {
772
773 // Convert the cast function: "CAST({0} AS {1})"
774 int firstParam = castFunction.indexOf("{0}");
775 String pre = castFunction.substring(0, firstParam); // "CAST("
776 String mid = castFunction.substring(firstParam + 3);
777 int secondParam = mid.indexOf("{1}");
778 String post;
779 if (secondParam > -1) {
780 post = mid.substring(secondParam + 3); // ")"
781 mid = mid.substring(0, secondParam); // " AS "
782 } else
783 post = "";
784
785 // No need to add CAST if the value is a constant
786 if (val instanceof Lit || val instanceof Param) {
787 buf.append(pre);
788 val.appendTo(buf);
789 buf.append(mid);
790 buf.append(getTypeName(type));
791 appendLength(buf, type);
792 buf.append(post);
793 } else {
794 val.appendTo(buf);
795 String sqlString = buf.getSQL(false);
796 if (sqlString.endsWith("?")) {
797 // case "(?" - convert to "CAST(? AS type"
798 String typeName = getTypeName(type);
799 if (String.class.equals(val.getType()))
800 typeName = typeName + "(" + characterColumnSize + ")";
801 String str = "CAST(? AS " + typeName + ")";
802 buf.replaceSqlString(sqlString.length() - 1,
803 sqlString.length(), str);
804 }
805 }
806 }
807
808 /**
809 * Create an index if necessary for some database tables
810 */
811 public void createIndexIfNecessary(Schema schema, String table,
812 Column pkColumn) {
813 if (isDB2ZOSV8xOrLater()) {
814 // build the index for the sequence tables
815 // the index name will the fully qualified table name + _IDX
816 Table tab = schema.getTable(table);
817 Index idx = tab.addIndex(tab.getFullName() + "_IDX");
818 idx.setUnique(true);
819 idx.addColumn(pkColumn);
820 }
821 }
822 }
Save This Page