Hi Tom,
Could you please review the attached fix for issue
248(
https://glassfish.dev.java.net/issues/show_bug.cgi?id=248)
It changes the code to use "CURRENT DATE" instead of "CURRENT TIMESTAMP"
for ExpressionOperator.Today. I have tested the change on both db2 as
well as derby
Following is the diff
$ cvs diff
src/java/oracle/toplink/essentials/platform/database/DB2Platform.java
diff -r1.6 DB2Platform.java
467c467
< v.addElement("CURRENT TIMESTAMP");
---
> v.addElement("CURRENT DATE");
--------------------------------------------------
I tried to add a test case to cover EJBQL expression CURRENT_DATE to
JUnitEJBQLDateTimeTestSuite as follows.
public void testCurrentDate() {
List result = createEntityManager().createQuery("SELECT
OBJECT(o) FROM DateTime o WHERE o.date = CURRENT_DATE").
getResultList();
assertTrue("There should be one result", result.size() == 1);
}
The test passes on derby and db2 but fails on oracle. This is because on
Oracle ExpressionOperator.Today is mapped to SYSDATE which returns both
date and time and hence the generated sql (select ... from
CMP3_DATE_TIME where SQL_DATE = SYSDATE) for above ejbql does not return
any rows . I think we should switch mapping of ExpressionOperator.Today
to TO_DATE(SYSDATE) on OraclePlatform. What do you think?
Thanks,
Mitesh
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
// Copyright (c) 1998, 2005, Oracle. All rights reserved.
package oracle.toplink.essentials.platform.database;
import java.io.*;
import java.sql.*;
import java.util.*;
import oracle.toplink.essentials.exceptions.ValidationException;
import oracle.toplink.essentials.expressions.*;
import oracle.toplink.essentials.internal.helper.*;
import oracle.toplink.essentials.queryframework.*;
import oracle.toplink.essentials.internal.databaseaccess.*;
import oracle.toplink.essentials.internal.sessions.AbstractSession;
/**
* <p><b>Purpose</b>: Provides DB2 specific behaviour.
* <p><b>Responsibilities</b>:<ul>
* <li> Native SQL for byte[], Date, Time, & Timestamp.
* <li> Support for table qualified names.
* </ul>
*
* @since TOPLink/Java 1.0
*/
public class DB2Platform extends oracle.toplink.essentials.platform.database.DatabasePlatform {
/**
* INTERNAL:
* Append a byte[] in native DB@ format BLOB(hexString) if usesNativeSQL(),
* otherwise use ODBC format from DatabasePLatform.
*/
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'
*/
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);
}
/**
* INTERNAL:
* 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();
JavaPlatform.setTimeInMillis(localCalendar, JavaPlatform.getTimeInMillis(calendar));
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'
*/
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'
*/
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'
*/
protected void appendCalendar(Calendar calendar, Writer writer) throws IOException {
if (usesNativeSQL()) {
writer.write("'");
appendDB2Calendar(calendar, writer);
writer.write("'");
} else {
super.appendCalendar(calendar, writer);
}
}
protected Hashtable buildFieldTypes() {
Hashtable 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("INTEGER", 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));
fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR", 255));
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.
*/
public int getMaxFieldNameSize() {
return 128;
}
/**
* INTERNAL:
* returns the maximum number of characters that can be used in a foreign key
* name on this platform.
*/
public int getMaxForeignKeyNameSize() {
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.
* Willcards can be passed as arguments.
*/
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 oracle.toplink.essentials.queryframework.SQLCall(query));
}
/**
* INTERNAL:
* Used for sp calls.
*/
public String getProcedureCallHeader() {
return "CALL ";
}
/**
* INTERNAL:
*/
public String getSelectForUpdateString() {
return " FOR UPDATE";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureEndString() {
return "END";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureBeginString() {
return "BEGIN";
}
/**
* INTERNAL:
* Used for stored procedure defs.
*/
public String getProcedureAsString() {
return "";
}
/**
* INTERNAL:
* This is required in the construction of the stored procedures with
* output parameters
*/
public boolean shouldPrintOutputTokenAtStart() {
return true;
}
/**
* INTERNAL:
* This method returns the query to select the timestamp
* from the server for DB2.
*/
public ValueReadQuery getTimestampQuery() {
if (timestampQuery == null) {
timestampQuery = new ValueReadQuery();
timestampQuery.setSQLString("SELECT DISTINCT CURRENT TIMESTAMP FROM SYSIBM.SYSTABLES");
}
return timestampQuery;
}
/**
* INTERNAL:
* Initialize any platform-specific operators
*/
protected void initializePlatformOperators() {
super.initializePlatformOperators();
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToUpperCase, "UCASE"));
addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToLowerCase, "LCASE"));
addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Concat, "CONCAT"));
addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Instring, "Locate"));
addOperator(todayOperator());
//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"));
}
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 & BigDecimal maximums are dependent upon their precision & Scale
*/
public Hashtable maximumNumericValues() {
Hashtable values = new Hashtable();
values.put(Integer.class, new Integer(Integer.MAX_VALUE));
values.put(Long.class, new Long((long)Integer.MAX_VALUE));
values.put(Float.class, new Float(123456789));
values.put(Double.class, new Double((double)Float.MAX_VALUE));
values.put(Short.class, new Short(Short.MAX_VALUE));
values.put(Byte.class, new Byte(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 & BigDecimal minimums are dependent upon their precision & Scale
*/
public Hashtable minimumNumericValues() {
Hashtable values = new Hashtable();
values.put(Integer.class, new Integer(Integer.MIN_VALUE));
values.put(Long.class, new Long((long)Integer.MIN_VALUE));
values.put(Float.class, new Float(-123456789));
values.put(Double.class, new Double((double)Float.MIN_VALUE));
values.put(Short.class, new Short(Short.MIN_VALUE));
values.put(Byte.class, new Byte(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.
*/
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.
*/
public boolean shouldUseJDBCOuterJoinSyntax() {
return false;
}
/**
* INTERNAL:
* Create the sysdate operator for this platform
*/
protected ExpressionOperator todayOperator() {
ExpressionOperator result = new ExpressionOperator();
result.setSelector(ExpressionOperator.Today);
Vector v = new Vector(1);
v.addElement("CURRENT DATE");
result.printsAs(v);
result.bePrefix();
result.setNodeClass(oracle.toplink.essentials.internal.expressions.FunctionExpression.class);
return result;
}
/**
* INTERNAL:
* Build the identity query for native sequencing.
*/
public ValueReadQuery buildSelectQueryForNativeSequence() {
ValueReadQuery selectQuery = new ValueReadQuery();
StringWriter writer = new StringWriter();
writer.write("VALUES IDENTITY_VAL_LOCAL()");
selectQuery.setSQLString(writer.toString());
return selectQuery;
}
/**
* INTERNAL:
* Append the receiver's field 'identity' constraint clause to a writer
*/
public void printFieldIdentityClause(Writer writer) throws ValidationException {
try {
writer.write(" GENERATED ALWAYS AS IDENTITY");
} catch (IOException ioException) {
throw ValidationException.fileError(ioException);
}
}
/**
* INTERNAL:
* If native sequencing is being used on DB2 then the values must be
* retrieved after the insert.
* This method is to be used *ONLY* by sequencing classes
*/
public boolean shouldNativeSequenceAcquireValueAfterInsert() {
return true;
}
/**
* Return true if the receiver uses host sequence numbers, generated on the database.
* DB2 does through AS IDENTITY field types.
*/
public boolean supportsNativeSequenceNumbers() {
return true;
}
/**
* INTERNAL:
*/
public boolean supportsGlobalTempTables() {
return true;
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlPrefix() {
return "DECLARE GLOBAL TEMPORARY TABLE ";
}
/**
* INTERNAL:
*/
public DatabaseTable getTempTableForTable(DatabaseTable table) {
DatabaseTable tempTable = super.getTempTableForTable(table);
tempTable.setTableQualifier("session");
return tempTable;
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlSuffix() {
return " ON COMMIT DELETE ROWS NOT LOGGED";
}
/**
* INTERNAL:
*/
protected String getCreateTempTableSqlBodyForTable(DatabaseTable table) {
return " LIKE " + table.getQualifiedName();
}
/**
* INTERNAL:
*/
public boolean dontBindUpdateAllQueryUsingTempTables() {
return true;
}
}