This is an automated email from the ASF dual-hosted git repository.
doebele pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/empire-db.git
The following commit(s) were added to refs/heads/master by this push:
new 933dce5 EMPIREDB-283 Changed timestamp handling for SQL-Server 2016+.
Replaced DATETIME with DATETIME2.
933dce5 is described below
commit 933dce5536fe157cd6b5e924a2c623acfb6cb083
Author: Rainer Döbele <[email protected]>
AuthorDate: Sun Jan 27 19:18:38 2019 +0100
EMPIREDB-283
Changed timestamp handling for SQL-Server 2016+.
Replaced DATETIME with DATETIME2.
---
.../empire-db-example-advanced/config.xml | 9 +--
.../empire-db-example-advanced/pom.xml | 19 +++--
.../empire-db-example-basic/config.xml | 9 +--
empire-db-examples/empire-db-example-basic/pom.xml | 19 +++--
.../empire/db/sqlserver/DBDatabaseDriverMSSQL.java | 80 +++++++++++++++++-----
.../empire/db/sqlserver/MSSqlDDLGenerator.java | 9 +--
6 files changed, 94 insertions(+), 51 deletions(-)
diff --git a/empire-db-examples/empire-db-example-advanced/config.xml
b/empire-db-examples/empire-db-example-advanced/config.xml
index 229674e..40c23c7 100644
--- a/empire-db-examples/empire-db-example-advanced/config.xml
+++ b/empire-db-examples/empire-db-example-advanced/config.xml
@@ -110,16 +110,17 @@
<properties-sqlserver>
<!-- JDBC properties for a Microsoft SQL-Server Database
connection -->
- <!-- Required jar file: sqljdbc.jar -->
<jdbcClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcClass>
- <jdbcURL>jdbc:sqlserver://esteams2:1433</jdbcURL>
- <jdbcUser>sa</jdbcUser>
- <jdbcPwd>esteam</jdbcPwd>
+ <jdbcURL>jdbc:sqlserver://esteamsvr12\sql14</jdbcURL>
+ <jdbcUser>jdbcUser</jdbcUser>
+ <jdbcPwd>jdbcPwd</jdbcPwd>
<!-- Empire-db driver configuration -->
<empireDBDriverClass>org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL</empireDBDriverClass>
<empireDBDriverProperites>
<databaseName>DBSAMPLEADV</databaseName>
<useSequenceTable>false</useSequenceTable>
+ <!-- useDateTime2: set to false for databases using
DATETIME for timestamps instead of DATETIME2 -->
+ <useDateTime2>true</useDateTime2>
</empireDBDriverProperites>
</properties-sqlserver>
diff --git a/empire-db-examples/empire-db-example-advanced/pom.xml
b/empire-db-examples/empire-db-example-advanced/pom.xml
index 8de91b1..ec1b9b0 100644
--- a/empire-db-examples/empire-db-example-advanced/pom.xml
+++ b/empire-db-examples/empire-db-example-advanced/pom.xml
@@ -56,22 +56,19 @@
<artifactId>derby</artifactId>
<!-- <scope>runtime</scope> -->
</dependency>
-
+ <!-- msssql -->
+ <dependency>
+ <groupId>com.microsoft.sqlserver</groupId>
+ <artifactId>mssql-jdbc</artifactId>
+ <version>7.0.0.jre8</version>
+ </dependency>
<!-- ojdbc
<dependency>
<groupId>com.oracle</groupId>
- <artifactId>ojdbc5</artifactId>
- <version>11.2.0.3</version>
+ <artifactId>ojdbc6</artifactId>
+ <version>11.2.0.3.0</version>
</dependency>
-->
-
- <!-- msssql
- <dependency>
- <groupId>com.microsoft</groupId>
- <artifactId>sqljdbc</artifactId>
- <version>1.0</version>
- </dependency>
- -->
</dependencies>
<build>
diff --git a/empire-db-examples/empire-db-example-basic/config.xml
b/empire-db-examples/empire-db-example-basic/config.xml
index 9fd8e91..b2f695a 100644
--- a/empire-db-examples/empire-db-example-basic/config.xml
+++ b/empire-db-examples/empire-db-example-basic/config.xml
@@ -110,16 +110,17 @@
<properties-sqlserver>
<!-- JDBC properties for a Microsoft SQL-Server Database
connection -->
- <!-- Required jar file: sqljdbc.jar -->
<jdbcClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcClass>
- <jdbcURL>jdbc:sqlserver://esteams2:1433</jdbcURL>
- <jdbcUser>sa</jdbcUser>
- <jdbcPwd>esteam</jdbcPwd>
+ <jdbcURL>jdbc:sqlserver://esteamsvr12\sql14</jdbcURL>
+ <jdbcUser>jdbcUser</jdbcUser>
+ <jdbcPwd>jdbcPwd</jdbcPwd>
<!-- Empire-db driver configuration -->
<empireDBDriverClass>org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL</empireDBDriverClass>
<empireDBDriverProperites>
<databaseName>DBSAMPLE</databaseName>
<useSequenceTable>false</useSequenceTable>
+ <!-- useDateTime2: set to false for databases using
DATETIME for timestamps instead of DATETIME2 -->
+ <useDateTime2>true</useDateTime2>
</empireDBDriverProperites>
</properties-sqlserver>
diff --git a/empire-db-examples/empire-db-example-basic/pom.xml
b/empire-db-examples/empire-db-example-basic/pom.xml
index b89d34c..e19975b 100644
--- a/empire-db-examples/empire-db-example-basic/pom.xml
+++ b/empire-db-examples/empire-db-example-basic/pom.xml
@@ -62,22 +62,19 @@
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
-
+ <!-- msssql -->
+ <dependency>
+ <groupId>com.microsoft.sqlserver</groupId>
+ <artifactId>mssql-jdbc</artifactId>
+ <version>7.0.0.jre8</version>
+ </dependency>
<!-- ojdbc
<dependency>
<groupId>com.oracle</groupId>
- <artifactId>ojdbc5</artifactId>
- <version>11.2.0.3</version>
+ <artifactId>ojdbc6</artifactId>
+ <version>11.2.0.3.0</version>
</dependency>
-->
-
- <!-- msssql
- <dependency>
- <groupId>com.microsoft</groupId>
- <artifactId>sqljdbc</artifactId>
- <version>1.0</version>
- </dependency>
- -->
</dependencies>
<build>
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 1ac97ac..1bae19f 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
@@ -19,7 +19,9 @@
package org.apache.empire.db.sqlserver;
import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.SQLException;
+import java.sql.Timestamp;
import java.util.GregorianCalendar;
import org.apache.empire.commons.StringUtils;
@@ -105,6 +107,7 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
// When set to 'false' (default) MySQL's IDENTITY feature is used.
private boolean useSequenceTable = false;
private boolean useUnicodePrefix = true;
+ private boolean useDateTime2 = true;
private DBDDLGenerator<?> ddlGenerator = null; // lazy creation
@@ -197,7 +200,26 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
this.useUnicodePrefix = useUnicodePrefix;
}
+ /**
+ * returns whether the DATETIME2 datatype is used for timestamps (instead
of DATETIME)
+ */
+ public boolean isUseDateTime2()
+ {
+ return useDateTime2;
+ }
+
+ /**
+ * Sets whether or not to use the DATETIME2 datatype instead of DATETIME
for timestamps
+ * Default is true (set to false for existing databases using DATETIME!)
+ * @param useDateTime2 true if DATETIME2 or false if DATETIME is used
+ */
+ public void setUseDateTime2(boolean useDateTime2)
+ {
+ this.useDateTime2 = useDateTime2;
+ }
+
/** {@inheritDoc} */
+ @SuppressWarnings("unused")
@Override
protected void attachDatabase(DBDatabase db, Connection conn)
{
@@ -206,6 +228,8 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
{ // Set Database
if (StringUtils.isNotEmpty(databaseName))
executeSQL("USE " + databaseName, null, conn, null);
+ // Dateformat must be ymd!
+ executeSQL("set dateformat ymd", null, conn, null);
// Sequence Table
if (useSequenceTable && db.getTable(sequenceTableName)==null)
new DBSeqTable(sequenceTableName, db);
@@ -283,10 +307,11 @@ 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 "convert(date, '{0}',
121)";
+ case SQL_DATE_TEMPLATE: return "convert(date, '{0}',
111)";
case SQL_CURRENT_DATETIME: return "getdate()";
case SQL_DATETIME_PATTERN: return "yyyy-MM-dd HH:mm:ss.SSS";
- case SQL_DATETIME_TEMPLATE: return "convert(datetime, '{0}',
121)";
+ case SQL_DATETIME_TEMPLATE: return isUseDateTime2() ?
"convert(datetime2, '{0}', 121)"
+ :
"convert(datetime, '{0}', 121)";
// functions
case SQL_FUNC_COALESCE: return "coalesce(?, {0})";
case SQL_FUNC_SUBSTRING: return "substring(?, {0}, 4000)";
@@ -341,16 +366,19 @@ public class DBDatabaseDriverMSSQL extends
DBDatabaseDriver
case BOOL: return "convert(bit, ?)";
case INTEGER: return "convert(int, ?)";
case DECIMAL: return "convert(decimal, ?)";
- case FLOAT: return "convert(float, ?)";
- case DATE: return "convert(datetime, ?, 111)";
- case DATETIME: return "convert(datetime, ?, 120)";
+ case FLOAT: return "convert(float, ?)";
+ case DATE: return "convert(date, ?, 111)";
+ case DATETIME: return isUseDateTime2() ? "convert(datetime2, ?,
121)"
+ : "convert(datetime, ?,
121)";
// Convert to text
case TEXT:
- // Date-Time-Format "YYYY-MM-DD hh.mm.ss"
+ // Date-Time-Format "YYYY-MM-DD"
if (srcType==DataType.DATE)
- return "replace(convert(nvarchar, ?, 111), '/', '-')";
+ return "convert(nvarchar, ?, 111)";
+ // Date-Time-Format "YYYY-MM-DD hh.mm.ss"
if (srcType==DataType.DATETIME)
return "convert(nvarchar, ?, 120)";
+ // other
return "convert(nvarchar, ?)";
case BLOB:
return "convert(varbinary, ?)";
@@ -436,22 +464,40 @@ public class DBDatabaseDriverMSSQL extends
DBDatabaseDriver
}
return super.getColumnAutoValue(db, column, conn);
}
-
+
/**
- * @see DBDatabaseDriver#executeSQL(String, Object[], Connection,
DBSetGenKeys)
+ * Adds special behaviour for Timestamp columns with are declared as
DATETIME
*/
@Override
- public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn,
DBSetGenKeys genKeys)
+ protected void addStatementParam(PreparedStatement pstmt, int paramIndex,
Object value)
throws SQLException
{
- int affected = super.executeSQL(sqlCmd, sqlParams, conn, genKeys);
- if (affected<0)
- { // less than 0?
- log.warn("executeSQL for {} retuned {} affected records!", sqlCmd,
affected);
- return 0;
+ if ((value instanceof Timestamp) && !this.isUseDateTime2())
+ { /*
+ * For compatibility with databases using DATETIME instead of
DATETIME2:
+ * For constraints to work, the nanoseconds part must be reduced
to milliseconds
+ * otherwise the comparison with existing database values will
fail.
+ */
+ Timestamp ts = (Timestamp)value;
+ if (ts.getNanos()!=0)
+ { // special
+ String tsAsString = ts.toString();
+ int nano = tsAsString.lastIndexOf('.');
+ int milliLength = nano+4;
+ if (nano>0 && tsAsString.length()>milliLength)
+ tsAsString = tsAsString.substring(0, milliLength);
+ // Sets timestamp as string with Milliseconds only
+ pstmt.setObject(paramIndex, tsAsString);
+ }
+ else
+ { // Sets the timestamp as provided
+ pstmt.setTimestamp(paramIndex, ts);
+ }
+ }
+ else
+ { // Default handling
+ super.addStatementParam(pstmt, paramIndex, value);
}
- return affected;
-
}
/**
diff --git
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
index 199a7fc..bf8c1eb 100644
---
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
+++
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
@@ -34,7 +34,7 @@ public class MSSqlDDLGenerator extends
DBDDLGenerator<DBDatabaseDriverMSSQL>
{
super(driver);
// set Oracle specific data types
- initDataTypes();
+ initDataTypes(driver);
// Alter Column Phrase
alterColumnPhrase = " ALTER COLUMN ";
}
@@ -43,12 +43,12 @@ public class MSSqlDDLGenerator extends
DBDDLGenerator<DBDatabaseDriverMSSQL>
* sets Oracle specific data types
* @param driver
*/
- private void initDataTypes()
+ protected void initDataTypes(DBDatabaseDriverMSSQL driver)
{ // Override data types
DATATYPE_CHAR = "NCHAR"; // Fixed length chars (unicode)
DATATYPE_VARCHAR = "NVARCHAR"; // variable length characters
(unicode)
- DATATYPE_DATE = "DATETIME";
- DATATYPE_TIMESTAMP = "DATETIME";
+ DATATYPE_DATE = "DATE";
+ DATATYPE_TIMESTAMP = (driver.isUseDateTime2() ? "DATETIME2" :
"DATETIME");
DATATYPE_CLOB = "NTEXT";
DATATYPE_BLOB = "IMAGE";
DATATYPE_UNIQUEID = "UNIQUEIDENTIFIER"; // Globally Unique
Identifier
@@ -104,6 +104,7 @@ public class MSSqlDDLGenerator extends
DBDDLGenerator<DBDatabaseDriverMSSQL>
return true;
}
+ @SuppressWarnings("unused")
@Override
protected void createDatabase(DBDatabase db, DBSQLScript script)
{
