| /* |
| * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved. |
| * Copyright (c) 1998, 2021 IBM Corporation. All rights reserved. |
| * |
| * This program and the accompanying materials are made available under the |
| * terms of the Eclipse Public License v. 2.0 which is available at |
| * http://www.eclipse.org/legal/epl-2.0, |
| * or the Eclipse Distribution License v. 1.0 which is available at |
| * http://www.eclipse.org/org/documents/edl-v10.php. |
| * |
| * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause |
| */ |
| |
| // Contributors: |
| // Oracle - initial API and implementation from Oracle TopLink |
| // 09/14/2011-2.3.1 Guy Pelletier |
| // - 357533: Allow DDL queries to execute even when Multitenant entities are part of the PU |
| // 02/19/2015 - Rick Curtis |
| // - 458877 : Add national character support |
| // 02/24/2016-2.6.0 Rick Curtis |
| // - 460740: Fix pessimistic locking with setFirst/Max results on DB2 |
| // 03/13/2015 - Jody Grassel |
| // - 462103 : SQL for Stored Procedure named parameter with DB2 generated with incorrect marker |
| // 04/15/2016 - Dalia Abo Sheasha |
| // - 491824: Setting lock timeout to 0 issues a NOWAIT causing an error in DB2 |
| // 08/22/2017 - Will Dazey |
| // - 521037: DB2 default schema is doubled for sequence queries |
| // 12/06/2018 - Will Dazey |
| // - 542491: Add new 'eclipselink.jdbc.force-bind-parameters' property to force enable binding |
| package org.eclipse.persistence.platform.database; |
| |
| import java.io.*; |
| import java.sql.*; |
| import java.util.*; |
| |
| import org.eclipse.persistence.exceptions.ValidationException; |
| import org.eclipse.persistence.expressions.*; |
| import org.eclipse.persistence.internal.helper.*; |
| import org.eclipse.persistence.internal.sessions.AbstractRecord; |
| import org.eclipse.persistence.internal.sessions.AbstractSession; |
| import org.eclipse.persistence.internal.databaseaccess.DatabaseCall; |
| import org.eclipse.persistence.internal.databaseaccess.FieldTypeDefinition; |
| import org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter; |
| import org.eclipse.persistence.internal.expressions.ParameterExpression; |
| import org.eclipse.persistence.internal.expressions.SQLSelectStatement; |
| import org.eclipse.persistence.queries.*; |
| import org.eclipse.persistence.tools.schemaframework.FieldDefinition; |
| |
| /** |
| * <p> |
| * <b>Purpose</b>: Provides DB2 specific behavior. |
| * <p> |
| * <b>Responsibilities</b>: |
| * <ul> |
| * <li>Support for schema creation. |
| * <li>Native SQL for byte[], Date, Time, {@literal &} Timestamp. |
| * <li>Support for table qualified names. |
| * <li>Support for stored procedures. |
| * <li>Support for temp tables. |
| * <li>Support for casting. |
| * <li>Support for database functions. |
| * <li>Support for identity sequencing. |
| * <li>Support for SEQUENCE sequencing. |
| * </ul> |
| * |
| * @since TOPLink/Java 1.0 |
| */ |
| public class DB2Platform extends org.eclipse.persistence.platform.database.DatabasePlatform { |
| |
| public DB2Platform() { |
| super(); |
| //com.ibm.db2.jcc.DB2Types.CURSOR |
| this.cursorCode = -100008; |
| this.shouldBindLiterals = false; |
| this.pingSQL = "VALUES(1)"; |
| } |
| |
| @Override |
| public void initializeConnectionData(Connection connection) throws SQLException { |
| // DB2 database doesn't support NVARCHAR column types and as such doesn't support calling |
| // get/setNString() on the driver. |
| this.driverSupportsNationalCharacterVarying = false; |
| } |
| |
| /** |
| * INTERNAL: |
| * Append a byte[] in native DB@ format BLOB(hexString) if usesNativeSQL(), |
| * otherwise use ODBC format from DatabasePLatform. |
| */ |
| @Override |
| protected void appendByteArray(byte[] bytes, Writer writer) throws IOException { |
| if (usesNativeSQL()) { |
| writer.write("BLOB(x'"); |
| Helper.writeHexString(bytes, writer); |
| writer.write("')"); |
| } else { |
| super.appendByteArray(bytes, writer); |
| } |
| } |
| |
| /** |
| * INTERNAL: |
| * Appends the Date in native format if usesNativeSQL() otherwise use ODBC |
| * format from DatabasePlatform. Native format: 'mm/dd/yyyy' |
| */ |
| @Override |
| protected void appendDate(java.sql.Date date, Writer writer) throws IOException { |
| if (usesNativeSQL()) { |
| appendDB2Date(date, writer); |
| } else { |
| super.appendDate(date, writer); |
| } |
| } |
| |
| /** |
| * INTERNAL: |
| * Write a timestamp in DB2 specific format (mm/dd/yyyy). |
| */ |
| protected void appendDB2Date(java.sql.Date date, Writer writer) throws IOException { |
| writer.write("'"); |
| // PERF: Avoid deprecated get methods, that are now very inefficient and |
| // used from toString. |
| Calendar calendar = Helper.allocateCalendar(); |
| calendar.setTime(date); |
| |
| if ((calendar.get(Calendar.MONTH) + 1) < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(calendar.get(Calendar.MONTH) + 1)); |
| writer.write('/'); |
| if (calendar.get(Calendar.DATE) < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(calendar.get(Calendar.DATE))); |
| writer.write('/'); |
| writer.write(Integer.toString(calendar.get(Calendar.YEAR))); |
| writer.write("'"); |
| |
| Helper.releaseCalendar(calendar); |
| } |
| |
| /** |
| * INTERNAL: |
| * Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff). |
| */ |
| protected void appendDB2Timestamp(java.sql.Timestamp timestamp, Writer writer) throws IOException { |
| // PERF: Avoid deprecated get methods, that are now very inefficient and |
| // used from toString. |
| Calendar calendar = Helper.allocateCalendar(); |
| calendar.setTime(timestamp); |
| |
| writer.write(Helper.printDate(calendar)); |
| writer.write('-'); |
| if (calendar.get(Calendar.HOUR_OF_DAY) < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(calendar.get(Calendar.HOUR_OF_DAY))); |
| writer.write('.'); |
| if (calendar.get(Calendar.MINUTE) < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(calendar.get(Calendar.MINUTE))); |
| writer.write('.'); |
| if (calendar.get(Calendar.SECOND) < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(calendar.get(Calendar.SECOND))); |
| writer.write('.'); |
| |
| Helper.releaseCalendar(calendar); |
| |
| // Must truncate the nanos to six decimal places, |
| // it is actually a complex algorithm... |
| String nanoString = Integer.toString(timestamp.getNanos()); |
| int numberOfZeros = 0; |
| for (int num = Math.min(9 - nanoString.length(), 6); num > 0; num--) { |
| writer.write('0'); |
| numberOfZeros++; |
| } |
| if ((nanoString.length() + numberOfZeros) > 6) { |
| nanoString = nanoString.substring(0, (6 - numberOfZeros)); |
| } |
| writer.write(nanoString); |
| } |
| |
| /** |
| * Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff). |
| */ |
| protected void appendDB2Calendar(Calendar calendar, Writer writer) throws IOException { |
| int hour; |
| int minute; |
| int second; |
| if (!Helper.getDefaultTimeZone().equals(calendar.getTimeZone())) { |
| // Must convert the calendar to the local timezone if different, as |
| // dates have no timezone (always local). |
| Calendar localCalendar = Helper.allocateCalendar(); |
| localCalendar.setTimeInMillis(calendar.getTimeInMillis()); |
| hour = calendar.get(Calendar.HOUR_OF_DAY); |
| minute = calendar.get(Calendar.MINUTE); |
| second = calendar.get(Calendar.SECOND); |
| Helper.releaseCalendar(localCalendar); |
| } else { |
| hour = calendar.get(Calendar.HOUR_OF_DAY); |
| minute = calendar.get(Calendar.MINUTE); |
| second = calendar.get(Calendar.SECOND); |
| } |
| writer.write(Helper.printDate(calendar)); |
| writer.write('-'); |
| if (hour < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(hour)); |
| writer.write('.'); |
| if (minute < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(minute)); |
| writer.write('.'); |
| if (second < 10) { |
| writer.write('0'); |
| } |
| writer.write(Integer.toString(second)); |
| writer.write('.'); |
| |
| // Must truncate the nanos to six decimal places, |
| // it is actually a complex algorithm... |
| String millisString = Integer.toString(calendar.get(Calendar.MILLISECOND)); |
| int numberOfZeros = 0; |
| for (int num = Math.min(3 - millisString.length(), 3); num > 0; num--) { |
| writer.write('0'); |
| numberOfZeros++; |
| } |
| if ((millisString.length() + numberOfZeros) > 3) { |
| millisString = millisString.substring(0, (3 - numberOfZeros)); |
| } |
| writer.write(millisString); |
| } |
| |
| /** |
| * INTERNAL: |
| * Append the Time in Native format if usesNativeSQL() otherwise use ODBC |
| * format from DAtabasePlatform. Native Format: 'hh:mm:ss' |
| */ |
| @Override |
| protected void appendTime(java.sql.Time time, Writer writer) throws IOException { |
| if (usesNativeSQL()) { |
| writer.write("'"); |
| writer.write(Helper.printTime(time)); |
| writer.write("'"); |
| } else { |
| super.appendTime(time, writer); |
| } |
| } |
| |
| /** |
| * INTERNAL: |
| * Append the Timestamp in native format if usesNativeSQL() is true |
| * otherwise use ODBC format from DatabasePlatform. Native format: |
| * 'YYYY-MM-DD-hh.mm.ss.SSSSSS' |
| */ |
| @Override |
| protected void appendTimestamp(java.sql.Timestamp timestamp, Writer writer) throws IOException { |
| if (usesNativeSQL()) { |
| writer.write("'"); |
| appendDB2Timestamp(timestamp, writer); |
| writer.write("'"); |
| } else { |
| super.appendTimestamp(timestamp, writer); |
| } |
| } |
| |
| /** |
| * INTERNAL: |
| * Append the Timestamp in native format if usesNativeSQL() is true |
| * otherwise use ODBC format from DatabasePlatform. Native format: |
| * 'YYYY-MM-DD-hh.mm.ss.SSSSSS' |
| */ |
| @Override |
| protected void appendCalendar(Calendar calendar, Writer writer) throws IOException { |
| if (usesNativeSQL()) { |
| writer.write("'"); |
| appendDB2Calendar(calendar, writer); |
| writer.write("'"); |
| } else { |
| super.appendCalendar(calendar, writer); |
| } |
| } |
| |
| @Override |
| protected Hashtable<Class<?>, FieldTypeDefinition> buildFieldTypes() { |
| Hashtable<Class<?>, FieldTypeDefinition> fieldTypeMapping = new Hashtable<>(); |
| |
| fieldTypeMapping.put(Boolean.class, new FieldTypeDefinition("SMALLINT DEFAULT 0", false)); |
| |
| fieldTypeMapping.put(Integer.class, new FieldTypeDefinition("INTEGER", false)); |
| fieldTypeMapping.put(Long.class, new FieldTypeDefinition("BIGINT", false)); |
| fieldTypeMapping.put(Float.class, new FieldTypeDefinition("FLOAT", false)); |
| fieldTypeMapping.put(Double.class, new FieldTypeDefinition("FLOAT", false)); |
| fieldTypeMapping.put(Short.class, new FieldTypeDefinition("SMALLINT", false)); |
| fieldTypeMapping.put(Byte.class, new FieldTypeDefinition("SMALLINT", false)); |
| fieldTypeMapping.put(java.math.BigInteger.class, new FieldTypeDefinition("BIGINT", false)); |
| fieldTypeMapping.put(java.math.BigDecimal.class, new FieldTypeDefinition("DECIMAL", 15)); |
| fieldTypeMapping.put(Number.class, new FieldTypeDefinition("DECIMAL", 15)); |
| if(getUseNationalCharacterVaryingTypeForString()){ |
| fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", DEFAULT_VARCHAR_SIZE, "FOR MIXED DATA")); |
| }else { |
| fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", DEFAULT_VARCHAR_SIZE)); |
| } |
| fieldTypeMapping.put(Character.class, new FieldTypeDefinition("CHAR", 1)); |
| fieldTypeMapping.put(Byte[].class, new FieldTypeDefinition("BLOB", 64000)); |
| fieldTypeMapping.put(Character[].class, new FieldTypeDefinition("CLOB", 64000)); |
| fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BLOB", 64000)); |
| fieldTypeMapping.put(char[].class, new FieldTypeDefinition("CLOB", 64000)); |
| fieldTypeMapping.put(java.sql.Blob.class, new FieldTypeDefinition("BLOB", 64000)); |
| fieldTypeMapping.put(java.sql.Clob.class, new FieldTypeDefinition("CLOB", 64000)); |
| |
| fieldTypeMapping.put(java.sql.Date.class, new FieldTypeDefinition("DATE", false)); |
| fieldTypeMapping.put(java.sql.Time.class, new FieldTypeDefinition("TIME", false)); |
| fieldTypeMapping.put(java.sql.Timestamp.class, new FieldTypeDefinition("TIMESTAMP", false)); |
| |
| return fieldTypeMapping; |
| } |
| |
| /** |
| * INTERNAL: returns the maximum number of characters that can be used in a |
| * field name on this platform. |
| */ |
| @Override |
| public int getMaxFieldNameSize() { |
| return 128; |
| } |
| |
| /** |
| * INTERNAL: returns the maximum number of characters that can be used in a |
| * foreign key name on this platform. |
| */ |
| @Override |
| public int getMaxForeignKeyNameSize() { |
| return 18; |
| } |
| |
| /** |
| * INTERNAL: |
| * returns the maximum number of characters that can be used in a unique key |
| * name on this platform. |
| */ |
| @Override |
| public int getMaxUniqueKeyNameSize() { |
| return 18; |
| } |
| |
| /** |
| * INTERNAL: |
| * Return the catalog information through using the native SQL catalog |
| * selects. This is required because many JDBC driver do not support |
| * meta-data. Wildcards can be passed as arguments. |
| * This is currently not used. |
| */ |
| public Vector getNativeTableInfo(String table, String creator, AbstractSession session) { |
| String query = "SELECT * FROM SYSIBM.SYSTABLES WHERE TBCREATOR NOT IN ('SYS', 'SYSTEM')"; |
| if (table != null) { |
| if (table.indexOf('%') != -1) { |
| query = query + " AND TBNAME LIKE " + table; |
| } else { |
| query = query + " AND TBNAME = " + table; |
| } |
| } |
| if (creator != null) { |
| if (creator.indexOf('%') != -1) { |
| query = query + " AND TBCREATOR LIKE " + creator; |
| } else { |
| query = query + " AND TBCREATOR = " + creator; |
| } |
| } |
| return session.executeSelectingCall(new org.eclipse.persistence.queries.SQLCall(query)); |
| } |
| |
| /** |
| * INTERNAL: |
| * Used for sp calls. |
| */ |
| @Override |
| public String getProcedureCallHeader() { |
| return "CALL "; |
| } |
| |
| /** |
| * INTERNAL: |
| * Used for pessimistic locking in DB2. |
| * Without the "WITH RS" the lock is not held. |
| */ |
| // public String getSelectForUpdateString() { return " FOR UPDATE"; } |
| @Override |
| public String getSelectForUpdateString() { |
| return " FOR READ ONLY WITH RS USE AND KEEP UPDATE LOCKS"; |
| //return " FOR READ ONLY WITH RR"; |
| //return " FOR READ ONLY WITH RS"; |
| //return " FOR UPDATE WITH RS"; |
| } |
| |
| /** |
| * INTERNAL: |
| * Used for stored procedure defs. |
| */ |
| @Override |
| public String getProcedureEndString() { |
| return "END"; |
| } |
| |
| /** |
| * Used for stored procedure defs. |
| */ |
| @Override |
| public String getProcedureBeginString() { |
| return "BEGIN"; |
| } |
| |
| /** |
| * INTERNAL: |
| * Used for stored procedure defs. |
| */ |
| @Override |
| public String getProcedureAsString() { |
| return ""; |
| } |
| |
| /** |
| * Obtain the platform specific argument string |
| */ |
| @Override |
| public String getProcedureArgument(String name, Object parameter, Integer parameterType, StoredProcedureCall call, AbstractSession session) { |
| if (name != null && shouldPrintStoredProcedureArgumentNameInCall()) { |
| return getProcedureArgumentString() + name + " => " + "?"; |
| } |
| return "?"; |
| } |
| |
| /** |
| * INTERNAL: |
| * This is required in the construction of the stored procedures with output |
| * parameters. |
| */ |
| @Override |
| public boolean shouldPrintOutputTokenAtStart() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: |
| * This method returns the query to select the timestamp from the server for |
| * DB2. |
| */ |
| @Override |
| public ValueReadQuery getTimestampQuery() { |
| if (timestampQuery == null) { |
| timestampQuery = new ValueReadQuery(); |
| timestampQuery.setSQLString("SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1"); |
| timestampQuery.setAllowNativeSQLQuery(true); |
| } |
| return timestampQuery; |
| } |
| |
| /** |
| * INTERNAL: |
| * Initialize any platform-specific operators |
| */ |
| @Override |
| protected void initializePlatformOperators() { |
| super.initializePlatformOperators(); |
| |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToUpperCase, "UCASE")); |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToLowerCase, "LCASE")); |
| addOperator(concatOperator()); |
| addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Instring, "Locate")); |
| // CR#2811076 some missing DB2 functions added. |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToNumber, "DECIMAL")); |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToChar, "CHAR")); |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.DateToString, "CHAR")); |
| addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToDate, "DATE")); |
| addOperator(ltrim2Operator()); |
| addOperator(rtrim2Operator()); |
| } |
| |
| @Override |
| public boolean isDB2() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: |
| * Builds a table of maximum numeric values keyed on java class. This is |
| * used for type testing but might also be useful to end users attempting to |
| * sanitize values. |
| * <p> |
| * <b>NOTE</b>: BigInteger {@literal &} BigDecimal maximums are dependent upon their |
| * precision {@literal &} Scale |
| */ |
| @Override |
| public Hashtable<Class<? extends Number>, ? super Number> maximumNumericValues() { |
| Hashtable<Class<? extends Number>, ? super Number> values = new Hashtable<>(); |
| |
| values.put(Integer.class, Integer.MAX_VALUE); |
| values.put(Long.class, (long) Integer.MAX_VALUE); |
| values.put(Float.class, 123456789F); |
| values.put(Double.class, (double) Float.MAX_VALUE); |
| values.put(Short.class, Short.MAX_VALUE); |
| values.put(Byte.class, Byte.MAX_VALUE); |
| values.put(java.math.BigInteger.class, new java.math.BigInteger("999999999999999")); |
| values.put(java.math.BigDecimal.class, new java.math.BigDecimal("0.999999999999999")); |
| return values; |
| } |
| |
| /** |
| * INTERNAL: |
| * Builds a table of minimum numeric values keyed on java class. This is |
| * used for type testing but might also be useful to end users attempting to |
| * sanitize values. |
| * <p> |
| * <b>NOTE</b>: BigInteger {@literal &} BigDecimal minimums are dependent upon their |
| * precision {@literal &} Scale |
| */ |
| @Override |
| public Hashtable<Class<? extends Number>, ? super Number> minimumNumericValues() { |
| Hashtable<Class<? extends Number>, ? super Number> values = new Hashtable<>(); |
| |
| values.put(Integer.class, Integer.MIN_VALUE); |
| values.put(Long.class, (long) Integer.MIN_VALUE); |
| values.put(Float.class, (float) -123456789); |
| values.put(Double.class, (double) Float.MIN_VALUE); |
| values.put(Short.class, Short.MIN_VALUE); |
| values.put(Byte.class, Byte.MIN_VALUE); |
| values.put(java.math.BigInteger.class, new java.math.BigInteger("-999999999999999")); |
| values.put(java.math.BigDecimal.class, new java.math.BigDecimal("-0.999999999999999")); |
| return values; |
| } |
| |
| /** |
| * INTERNAL: |
| * Allow for the platform to ignore exceptions. This is required for DB2 |
| * which throws no-data modified as an exception. |
| */ |
| @Override |
| public boolean shouldIgnoreException(SQLException exception) { |
| if (exception.getMessage().equals("No data found") || exception.getMessage().equals("No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table") |
| || (exception.getErrorCode() == 100)) { |
| return true; |
| } |
| return super.shouldIgnoreException(exception); |
| } |
| |
| /** |
| * INTERNAL: |
| * JDBC defines and outer join syntax, many drivers do not support this. So |
| * we normally avoid it. |
| */ |
| @Override |
| public boolean shouldUseJDBCOuterJoinSyntax() { |
| return false; |
| } |
| |
| /** |
| * INTERNAL: |
| * The Concat operator is of the form {@code .... VARCHAR ( <operand1> || <operand2> )} |
| */ |
| private ExpressionOperator concatOperator() { |
| ExpressionOperator exOperator = new ExpressionOperator(); |
| exOperator.setType(ExpressionOperator.FunctionOperator); |
| exOperator.setSelector(ExpressionOperator.Concat); |
| Vector<String> v = new Vector<>(5); |
| v.add("VARCHAR("); |
| v.add(" || "); |
| v.add(")"); |
| exOperator.printsAs(v); |
| exOperator.bePrefix(); |
| exOperator.setNodeClass(ClassConstants.FunctionExpression_Class); |
| return exOperator; |
| } |
| |
| /** |
| * INTERNAL: |
| * The 2 arg LTRIM operator is of the form {@code .... TRIM (LEADING, <operand2> FROM <operand1> )} |
| */ |
| private ExpressionOperator ltrim2Operator() { |
| ExpressionOperator operator = new ExpressionOperator(); |
| operator.setType(ExpressionOperator.FunctionOperator); |
| operator.setSelector(ExpressionOperator.LeftTrim2); |
| Vector<String> v = new Vector<>(5); |
| v.add("TRIM(LEADING "); |
| v.add(" FROM "); |
| v.add(")"); |
| operator.printsAs(v); |
| operator.bePrefix(); |
| // Bug 573094 |
| int[] indices = { 1, 0 }; |
| operator.setArgumentIndices(indices); |
| operator.setNodeClass(ClassConstants.FunctionExpression_Class); |
| operator.setIsBindingSupported(false); |
| return operator; |
| } |
| |
| /** |
| * INTERNAL: |
| * The 2 arg RTRIM operator is of the form {@code .... TRIM (TRAILING, <operand2> FROM <operand1> )} |
| */ |
| private ExpressionOperator rtrim2Operator() { |
| ExpressionOperator operator = new ExpressionOperator(); |
| operator.setType(ExpressionOperator.FunctionOperator); |
| operator.setSelector(ExpressionOperator.RightTrim2); |
| Vector<String> v = new Vector<>(5); |
| v.add("TRIM(TRAILING "); |
| v.add(" FROM "); |
| v.add(")"); |
| operator.printsAs(v); |
| operator.bePrefix(); |
| // Bug 573094 |
| int[] indices = { 1, 0 }; |
| operator.setArgumentIndices(indices); |
| operator.setNodeClass(ClassConstants.FunctionExpression_Class); |
| operator.setIsBindingSupported(false); |
| return operator; |
| } |
| |
| /** |
| * INTERNAL: Build the identity query for native sequencing. |
| */ |
| @Override |
| public ValueReadQuery buildSelectQueryForIdentity() { |
| ValueReadQuery selectQuery = new ValueReadQuery(); |
| StringWriter writer = new StringWriter(); |
| writer.write("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"); |
| |
| selectQuery.setSQLString(writer.toString()); |
| return selectQuery; |
| } |
| |
| /** |
| * INTERNAL: Append the receiver's field 'identity' constraint clause to a |
| * writer. |
| * Used by table creation with sequencing. |
| */ |
| @Override |
| public void printFieldIdentityClause(Writer writer) throws ValidationException { |
| try { |
| writer.write(" GENERATED ALWAYS AS IDENTITY"); |
| } catch (IOException ioException) { |
| throw ValidationException.fileError(ioException); |
| } |
| } |
| |
| @Override |
| protected void printFieldTypeSize(Writer writer, FieldDefinition field, FieldTypeDefinition ftd) throws IOException { |
| super.printFieldTypeSize(writer, field, ftd); |
| String suffix = ftd.getTypesuffix(); |
| if (suffix != null) { |
| writer.append(" " + suffix); |
| } |
| } |
| |
| /** |
| * INTERNAL: Indicates whether the platform supports identity. DB2 does |
| * through AS IDENTITY field types. |
| * This is used by sequencing. |
| */ |
| @Override |
| public boolean supportsIdentity() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: DB2 supports temp tables. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| public boolean supportsGlobalTempTables() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: DB2 temp table syntax. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| protected String getCreateTempTableSqlPrefix() { |
| return "DECLARE GLOBAL TEMPORARY TABLE "; |
| } |
| |
| /** |
| * INTERNAL: DB2 temp table syntax. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| public DatabaseTable getTempTableForTable(DatabaseTable table) { |
| DatabaseTable tempTable = super.getTempTableForTable(table); |
| tempTable.setTableQualifier("session"); |
| return tempTable; |
| } |
| |
| /** |
| * INTERNAL: DB2 temp table syntax. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| protected String getCreateTempTableSqlSuffix() { |
| return " ON COMMIT DELETE ROWS NOT LOGGED"; |
| } |
| |
| /** |
| * INTERNAL: DB2 allows LIKE to be used to create temp tables, which avoids having to know the types. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| protected String getCreateTempTableSqlBodyForTable(DatabaseTable table) { |
| return " LIKE " + table.getQualifiedNameDelimited(this); |
| } |
| |
| /** |
| * INTERNAL: DB2 does not support NOWAIT. |
| */ |
| @Override |
| public String getNoWaitString() { |
| return ""; |
| } |
| |
| /** |
| * INTERNAL: DB2 has issues with binding with temp table queries. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| public boolean dontBindUpdateAllQueryUsingTempTables() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: DB2 does not allow NULL in select clause. |
| * This is used by UpdateAllQuerys. |
| */ |
| @Override |
| public boolean isNullAllowedInSelectClause() { |
| return false; |
| } |
| |
| /** |
| * INTERNAL |
| * DB2 has some issues with using parameters on certain functions and relations. |
| * This allows statements to disable binding only in these cases. |
| * If users set casting on, then casting is used instead of dynamic SQL. |
| */ |
| @Override |
| public boolean isDynamicSQLRequiredForFunctions() { |
| if(shouldForceBindAllParameters()) { |
| return false; |
| } |
| return !isCastRequired(); |
| } |
| |
| /** |
| * INTERNAL: |
| * DB2 requires casting on certain operations, such as the CONCAT function, |
| * and parameterized queries of the form, ":param = :param". This method |
| * will write CAST operation to parameters if the type is known. |
| * This is not used by default, only if isCastRequired is set to true, |
| * by default dynamic SQL is used to avoid the issue in only the required cases. |
| */ |
| @Override |
| public void writeParameterMarker(Writer writer, ParameterExpression parameter, AbstractRecord record, DatabaseCall call) throws IOException { |
| String paramaterMarker = "?"; |
| Object type = parameter.getType(); |
| // Update-all query requires casting of null parameter values in select into. |
| if ((type != null) && (this.isCastRequired || ((call.getQuery() != null) && call.getQuery().isUpdateAllQuery()))) { |
| BasicTypeHelperImpl typeHelper = BasicTypeHelperImpl.getInstance(); |
| String castType = null; |
| if (typeHelper.isBooleanType(type) || typeHelper.isByteType(type) || typeHelper.isShortType(type)) { |
| castType = "SMALLINT"; |
| } else if (typeHelper.isIntType(type)) { |
| castType = "INTEGER"; |
| } else if (typeHelper.isLongType(type)) { |
| castType = "BIGINT"; |
| } else if (typeHelper.isFloatType(type)) { |
| castType = "REAL"; |
| } else if (typeHelper.isDoubleType(type)) { |
| castType = "DOUBLE"; |
| } else if (typeHelper.isStringType(type)) { |
| castType = "VARCHAR(" + getCastSizeForVarcharParameter() + ")"; |
| } |
| |
| if (castType != null) { |
| paramaterMarker = "CAST (? AS " + castType + " )"; |
| } |
| } |
| writer.write(paramaterMarker); |
| } |
| |
| /** |
| * INTERNAL: |
| * DB2 does not seem to allow FOR UPDATE on queries with multiple tables. |
| * This is only used by testing to exclude these tests. |
| */ |
| @Override |
| public boolean supportsLockingQueriesWithMultipleTables() { |
| return false; |
| } |
| |
| /** |
| * INTERNAL: DB2 added SEQUENCE support as of (I believe) v8. |
| */ |
| @Override |
| public ValueReadQuery buildSelectQueryForSequenceObject(String qualifiedSeqName, Integer size) { |
| return new ValueReadQuery("VALUES(NEXT VALUE FOR " + qualifiedSeqName + ")"); |
| } |
| |
| /** |
| * INTERNAL: DB2 added SEQUENCE support as of (I believe) v8. |
| */ |
| @Override |
| public boolean supportsSequenceObjects() { |
| return true; |
| } |
| |
| /** |
| * INTERNAL: DB2 added SEQUENCE support as of (I believe) v8. |
| */ |
| @Override |
| public boolean isAlterSequenceObjectSupported() { |
| return true; |
| } |
| |
| @Override |
| public boolean shouldPrintForUpdateClause() { |
| return false; |
| } |
| /** |
| * INTERNAL: |
| * Print the SQL representation of the statement on a stream, storing the fields |
| * in the DatabaseCall. This implementation works MaxRows and FirstResult into the SQL using |
| * DB2's ROWNUMBER() OVER() to filter values if shouldUseRownumFiltering is true. |
| */ |
| @Override |
| public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement){ |
| int max = 0; |
| int firstRow = 0; |
| |
| if (statement.getQuery()!=null){ |
| max = statement.getQuery().getMaxRows(); |
| firstRow = statement.getQuery().getFirstResult(); |
| } |
| |
| if ( !(this.shouldUseRownumFiltering()) || ( !(max>0) && !(firstRow>0) ) ){ |
| super.printSQLSelectStatement(call, printer, statement); |
| statement.appendForUpdateClause(printer); |
| return; |
| } else if ( max > 0 ){ |
| statement.setUseUniqueFieldAliases(true); |
| printer.printString("SELECT * FROM (SELECT * FROM (SELECT "); |
| printer.printString("EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM ("); |
| call.setFields(statement.printSQL(printer)); |
| printer.printString(") AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= "); |
| printer.printParameter(DatabaseCall.MAXROW_FIELD); |
| printer.printString(") AS EL_TEMP3 WHERE EL_ROWNM > "); |
| printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); |
| // If we have a ForUpdate clause, it must be on the outermost query |
| statement.appendForUpdateClause(printer); |
| } else {// firstRow>0 |
| statement.setUseUniqueFieldAliases(true); |
| printer.printString("SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM ("); |
| call.setFields(statement.printSQL(printer)); |
| printer.printString(") AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM > "); |
| printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); |
| statement.appendForUpdateClause(printer); |
| } |
| call.setIgnoreFirstRowSetting(true); |
| call.setIgnoreMaxResultsSetting(true); |
| } |
| |
| } |
