You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by in...@apache.org on 2015/11/08 16:22:23 UTC
empire-db git commit: EMPIREDB-233
Repository: empire-db
Updated Branches:
refs/heads/master 7ac3454c4 -> e846070ef
EMPIREDB-233
- changed in MSSQL driver DATE and DATETIME templates
- removed 'set dateformat' sql from attachDatabase
- added junit test class
Project: http://git-wip-us.apache.org/repos/asf/empire-db/repo
Commit: http://git-wip-us.apache.org/repos/asf/empire-db/commit/e846070e
Tree: http://git-wip-us.apache.org/repos/asf/empire-db/tree/e846070e
Diff: http://git-wip-us.apache.org/repos/asf/empire-db/diff/e846070e
Branch: refs/heads/master
Commit: e846070effcb179dbb97f4c658fc10368dc4e68b
Parents: 7ac3454
Author: inemeth <in...@apache.org>
Authored: Sun Nov 8 16:22:05 2015 +0100
Committer: inemeth <in...@apache.org>
Committed: Sun Nov 8 16:22:05 2015 +0100
----------------------------------------------------------------------
.../db/sqlserver/DBDatabaseDriverMSSQL.java | 6 +-
.../db/mssql/DBDatabaseDriverMSSqlDateTest.java | 188 +++++++++++++++++++
2 files changed, 190 insertions(+), 4 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/empire-db/blob/e846070e/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
----------------------------------------------------------------------
diff --git a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
index 5b8b812..66645e0 100644
--- a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
@@ -204,8 +204,6 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
{ // Set Database
if (StringUtils.isNotEmpty(databaseName))
executeSQL("USE " + databaseName, null, conn, null);
- // Set Dateformat
- executeSQL("SET DATEFORMAT ymd", null, conn, null);
// Sequence Table
if (useSequenceTable && db.getTable(sequenceTableName)==null)
new DBSeqTable(sequenceTableName, db);
@@ -282,10 +280,10 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
case SQL_BOOLEAN_FALSE: return "0";
case SQL_CURRENT_DATE: return "convert(char, getdate(), 111)";
case SQL_DATE_PATTERN: return "yyyy-MM-dd";
- case SQL_DATE_TEMPLATE: return "'{0}'";
+ case SQL_DATE_TEMPLATE: return "convert(date, '{0}', 121)";
case SQL_CURRENT_DATETIME: return "getdate()";
case SQL_DATETIME_PATTERN: return "yyyy-MM-dd HH:mm:ss.SSS";
- case SQL_DATETIME_TEMPLATE: return "'{0}'";
+ case SQL_DATETIME_TEMPLATE: return "convert(datetime, '{0}', 121)";
// functions
case SQL_FUNC_COALESCE: return "coalesce(?, {0})";
case SQL_FUNC_SUBSTRING: return "substring(?, {0}, 4000)";
http://git-wip-us.apache.org/repos/asf/empire-db/blob/e846070e/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
----------------------------------------------------------------------
diff --git a/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java b/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
new file mode 100644
index 0000000..3f0c12c
--- /dev/null
+++ b/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
@@ -0,0 +1,188 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.db.mssql;
+
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.Timestamp;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+
+import org.apache.empire.DBResource;
+import org.apache.empire.DBResource.DB;
+import org.apache.empire.data.DataMode;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.db.DBRecord;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+import org.junit.Ignore;
+import org.junit.Rule;
+import org.junit.Test;
+
+@Ignore
+public class DBDatabaseDriverMSSqlDateTest {
+
+ @Rule
+ public DBResource dbResource = new DBResource(DB.MSSQL_JTDS);
+
+ @Test
+ public void testDateFormatYMD() throws Exception {
+ runTestWithDateFormat("ymd");
+ }
+
+ @Test
+ public void testDateFormatDMY() throws Exception {
+ runTestWithDateFormat("dmy");
+ }
+
+ public void runTestWithDateFormat(String dbDateFormat) throws Exception {
+ Connection conn = dbResource.getConnection();
+
+ DBDatabaseDriver driver = dbResource.newDriver();
+ DateTimeTestDB db = new DateTimeTestDB();
+
+ // Encoding issue occur when prepared statement is disabled
+ // db.setPreparedStatementsEnabled(true);
+
+ db.open(driver, dbResource.getConnection());
+
+ if (!databaseExists(conn, db)) {
+ DBSQLScript script = new DBSQLScript();
+ db.getCreateDDLScript(db.getDriver(), script);
+ System.out.println(script.toString());
+ script.run(db.getDriver(), dbResource.getConnection(), false);
+ }
+
+ // STEP 5: Clear Database (Delete all records)
+ clearDatabase(conn, db);
+
+ // MSSQL datetime Accuracy => Rounded to increments of .000, .003, or
+ // .007 seconds => ignore ms for comparison
+ DateFormat truncDateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+ DateFormat dateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+
+ Timestamp lastLoginTs = new Timestamp(System.currentTimeMillis());
+ Date regDate = dateFmt.parse("2015-08-20 00:00:00.000");
+
+ {
+
+ driver.executeSQL("SET DATEFORMAT " + dbDateFormat, null, conn,
+ null);
+ DBRecord rec = new DBRecord();
+ rec.create(db.USER_INFO);
+ rec.setValue(db.USER_INFO.USER, "john.doe");
+ rec.setValue(db.USER_INFO.REG_DATE, regDate);
+ rec.setValue(db.USER_INFO.LAST_LOGIN, lastLoginTs);
+ rec.update(conn);
+
+ int id = rec.getInt(db.USER_INFO.ID);
+
+ DBRecord recRead = new DBRecord();
+ recRead.read(db.USER_INFO, id, conn);
+
+ assertEquals(truncDateFmt.format(lastLoginTs),
+ truncDateFmt.format(recRead
+ .getDateTime(db.USER_INFO.LAST_LOGIN)));
+ assertEquals(truncDateFmt.format(regDate),
+ truncDateFmt.format(recRead
+ .getDateTime(db.USER_INFO.REG_DATE)));
+
+ }
+
+ }
+
+ /**
+ * <PRE>
+ * Empties all Tables.
+ * </PRE>
+ */
+ private static void clearDatabase(Connection conn, DateTimeTestDB db) {
+ DBCommand cmd = db.createCommand();
+ // Delete all Employees (no constraints)
+ db.executeSQL(cmd.getDelete(db.USER_INFO), conn);
+ }
+
+ /**
+ * <PRE>
+ * Checks whether the database exists or not by executing
+ * select count(*) from USER_INFO
+ * If the Departments table does not exist the querySingleInt() function return -1 for failure.
+ * Please note that in this case an error will appear in the log which can be ignored.
+ * </PRE>
+ */
+ private static boolean databaseExists(Connection conn, DateTimeTestDB db) {
+ // Check whether DB exists
+ DBCommand cmd = db.createCommand();
+ cmd.select(db.USER_INFO.count());
+ // Check using "select count(*) from DEPARTMENTS"
+
+ //System.out
+ //.println("Checking whether table USER_INFO exists (SQLException will be logged if not - please ignore) ...");
+ try {
+ return (db.querySingleInt(cmd, -1, conn) >= 0);
+ } catch (Exception e) {
+ return false;
+ }
+
+ }
+
+ private static class DateTimeTestDB extends DBDatabase {
+
+ private static final long serialVersionUID = 1L;
+
+ public static class UserInfoTable extends DBTable {
+
+ public final DBTableColumn ID;
+ public final DBTableColumn USER;
+ public final DBTableColumn REG_DATE;
+ public final DBTableColumn LAST_LOGIN;
+
+ public UserInfoTable(DBDatabase db) {
+ super("USER_INFO", db);
+ ID = addColumn("DEPARTMENT_ID", DataType.AUTOINC, 0,
+ DataMode.AutoGenerated);
+ USER = addColumn("USER", DataType.TEXT, 80, DataMode.NotNull);
+ REG_DATE = addColumn("REG_DATE", DataType.DATE, 80,
+ DataMode.NotNull);
+ LAST_LOGIN = addColumn("LAST_LOGIN", DataType.DATETIME, 0,
+ DataMode.Nullable);
+
+ // Primary Key
+ setPrimaryKey(ID);
+
+ }
+
+ }
+
+ public final UserInfoTable USER_INFO = new UserInfoTable(this);
+
+ public DateTimeTestDB() {
+ super();
+ }
+
+ }
+
+}