openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SQLServerDictionary.java - openjpa - Git at Google

 /*
  * 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.openjpa.jdbc.sql;

 import java.io.InputStream;
 import java.io.Reader;
 import java.sql.Blob;
 import java.sql.Clob;
 import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Time;
 import java.sql.Types;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
 import java.time.OffsetDateTime;
 import java.util.Arrays;
 import java.util.Calendar;
 import java.util.Locale;

 import org.apache.openjpa.jdbc.identifier.DBIdentifier;
 import org.apache.openjpa.jdbc.kernel.JDBCStore;
 import org.apache.openjpa.jdbc.kernel.exps.FilterValue;
 import org.apache.openjpa.jdbc.schema.Column;
 import org.apache.openjpa.kernel.Filters;
 import org.apache.openjpa.lib.util.Localizer;
 import org.apache.openjpa.meta.JavaTypes;
 import org.apache.openjpa.util.StoreException;


 /**
  * Dictionary for Microsoft SQL Server.
  */
 public class SQLServerDictionary extends AbstractSQLServerDictionary {

     public static final String VENDOR_MICROSOFT = "microsoft";
     public static final String VENDOR_NETDIRECT = "netdirect";
     public static final String VENDOR_JTDS = "jtds";

     private static final Localizer _loc =
         Localizer.forPackage(SQLServerDictionary.class);

     private String schemaCase = SCHEMA_CASE_PRESERVE;
     /**
      * Flag whether to treat UNIQUEIDENTIFIER as VARBINARY or VARCHAR
      */
     public boolean uniqueIdentifierAsVarbinary = true;

     /**
      * SQLServer doesn't like a java.sql.Time as default.
      * So either we send it as String or people configure sendTimeAsDatetime=false on the Connection.
      * This is depending how the Database actually is setup.
      * To mitigate misconfiguration we can work around by sending the time as String to the JDBC driver.
      */
     public Boolean sendTimeAsString = null;

     public SQLServerDictionary() {
         platform = "Microsoft SQL Server";
         // SQLServer locks on a table-by-table basis
         forUpdateClause = null;
         tableForUpdateClause = "WITH (UPDLOCK)";
         supportsNullTableForGetColumns = false;
         requiresAliasForSubselect = true;
         stringLengthFunction = "LEN({0})";

         timeWithZoneTypeName = "TIME";
         timestampWithZoneTypeName = "DATETIMEOFFSET";

         indexPhysicalForeignKeys = true; // MS-SQLServer does not automatically create an index for a foreign key so we will

         // reservedWordSet subset that CANNOT be used as valid column names
         // (i.e., without surrounding them with double-quotes)
         // generated at 2021-05-02T16:15:30.630 via org.apache.openjpa.reservedwords.ReservedWordsIT
         invalidColumnWordSet.addAll(Arrays.asList(new String[] {
             "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AUTHORIZATION", "BACKUP", "BEGIN", "BETWEEN", "BREAK", "BROWSE",
             "BULK", "BY", "CASCADE", "CASE", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTERED", "COALESCE", "COLLATE", "COLUMN",
             "COMMIT", "COMPUTE", "CONSTRAINT", "CONTAINS", "CONTAINSTABLE", "CONTINUE", "CONVERT", "CREATE", "CROSS", "CURRENT",
             "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE", "DBCC", "DEALLOCATE",
             "DECLARE", "DEFAULT", "DELETE", "DENY", "DESC", "DISTINCT", "DISTRIBUTED", "DOUBLE", "DROP", "ELSE", "END", "END-EXEC",
             "ERRLVL", "ESCAPE", "EXCEPT", "EXEC", "EXECUTE", "EXISTS", "EXIT", "EXTERNAL", "FETCH", "FILE", "FILLFACTOR", "FOR",
             "FOREIGN", "FREETEXT", "FREETEXTTABLE", "FROM", "FULL", "FUNCTION", "GOTO", "GRANT", "GROUP", "HAVING", "HOLDLOCK",
             "IDENTITY", "IDENTITY_INSERT", "IDENTITYCOL", "IF", "IN", "INDEX", "INNER", "INSERT", "INTERSECT", "INTO", "IS",
             "JOIN", "KEY", "KILL", "LEFT", "LIKE", "LINENO", "MERGE", "NATIONAL", "NOCHECK", "NONCLUSTERED", "NOT", "NULL",
             "NULLIF", "OF", "OFF", "OFFSETS", "ON", "OPEN", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET", "OPENXML", "OPTION",
             "OR", "ORDER", "OUTER", "OVER", "PERCENT", "PLAN", "PRIMARY", "PRINT", "PROC", "PROCEDURE", "PUBLIC", "RAISERROR",
             "READ", "READTEXT", "RECONFIGURE", "REFERENCES", "REPLICATION", "RESTORE", "RESTRICT", "RETURN", "REVOKE", "RIGHT",
             "ROLLBACK", "ROWCOUNT", "ROWGUIDCOL", "RULE", "SAVE", "SCHEMA", "SELECT", "SESSION_USER", "SET", "SETUSER", "SHUTDOWN",
             "SOME", "STATISTICS", "SYSTEM_USER", "TABLE", "TABLESAMPLE", "TEXTSIZE", "THEN", "TO", "TOP", "TRAN", "TRANSACTION",
             "TRIGGER", "TRUNCATE", "TSEQUAL", "UNION", "UNIQUE", "UPDATE", "UPDATETEXT", "USE", "USER", "VALUES", "VARYING",
             "VIEW", "WAITFOR", "WHEN", "WHERE", "WHILE", "WITH", "WRITETEXT",
         }));
     }

     @Override
     public void connectedConfiguration(Connection conn) throws SQLException {
         super.connectedConfiguration(conn);
         boolean requiresWarnings = true;
         DatabaseMetaData meta = conn.getMetaData();
         String driverName = meta.getDriverName();
         String url = meta.getURL();
         if (driverVendor == null) {
             // serverMajorVersion of 8==2000, 9==2005, 10==2008,  11==2012
             if (meta.getDatabaseMajorVersion() >= 9) {
                 setSupportsXMLColumn(true);
                 if (sendTimeAsString == null) {
                     sendTimeAsString = Boolean.FALSE;
                 }
             }
             if (meta.getDatabaseMajorVersion() >= 10) {
                 // MSSQL 2008 supports new date, time and datetime2 types
                 // Use DATETIME2 which has 100ns vs. 3.333msec precision
                 dateTypeName = "DATE";
                 timeTypeName = "TIME";
                 timestampTypeName = "DATETIME2";
                 datePrecision = MICRO / 10;
             }
             if (meta.getDatabaseMajorVersion() >= 11) {
                 //SQLServer 2012 supports range select
                 rangePosition = RANGE_POST_SELECT;
                 supportsSelectStartIndex = true;
                 supportsSelectEndIndex = true;
             }
             if (driverName != null) {
                 if (driverName.startsWith("Microsoft SQL Server")) {
                     // v1.1, 1.2, 2.0 or 3.0 driver
                     driverVendor = VENDOR_MICROSOFT;
                     if (meta.getDriverMajorVersion() >= 2) {
                         // see http://blogs.msdn.com/jdbcteam/archive/2007/05/\
                         // 02/what-is-adaptive-response-buffering-and-why-\
                         // should-i-use-it.aspx
                         // 2.0 driver connectURL automatically includes
                         // responseBuffering=adaptive
                         // and disableStatementPooling=true
                         requiresWarnings = false;
                     }
                 } else {
                     if ("NetDirect JSQLConnect".equals(driverName))
                         driverVendor = VENDOR_NETDIRECT;
                     else if (driverName.startsWith("jTDS"))
                         driverVendor = VENDOR_JTDS;
                     else if ("SQLServer".equals(driverName)) {
                         if (url != null &&
                             url.startsWith("jdbc:microsoft:sqlserver:"))
                             driverVendor = VENDOR_MICROSOFT;
                         else if (url != null &&
                             url.startsWith("jdbc:datadirect:sqlserver:"))
                             driverVendor = VENDOR_DATADIRECT;
                         else
                             driverVendor = VENDOR_OTHER;
                     }
                     // old way of determining xml support
                     if (driverName.indexOf(platform) != -1) {
                         String versionString =
                             driverName.substring(platform.length() + 1);
                         if (versionString.indexOf(" ") != -1)
                             versionString = versionString.substring(0,
                                 versionString.indexOf(" "));
                         int version = Integer.parseInt(versionString);
                         if (version >= 2005)
                             setSupportsXMLColumn(true);
                     }
                 }
             } else {
                 driverVendor = VENDOR_OTHER;
             }
         }

         // warn about not using cursors for pre-2.0 MS driver
         // as connectURL includes selectMethod=direct
         if (((VENDOR_MICROSOFT.equalsIgnoreCase(driverVendor) &&
             requiresWarnings) ||
             VENDOR_DATADIRECT.equalsIgnoreCase(driverVendor)) &&
             (url.toLowerCase(Locale.ENGLISH).indexOf("selectmethod=cursor") == -1))
             log.warn(_loc.get("sqlserver-cursor", url));

         // warn about prepared statement caching if using pre-2.0 MS drivers
         // as connectURL includes responseBuffering=full
         String props = conf.getConnectionFactoryProperties();
         if ((props != null) &&
             VENDOR_MICROSOFT.equalsIgnoreCase(driverVendor) &&
             requiresWarnings &&
             (props.toLowerCase(Locale.ENGLISH).indexOf("maxcachedstatements=0") == -1))
             log.warn(_loc.get("sqlserver-cachedstmnts"));
     }

     @Override
     public Column[] getColumns(DatabaseMetaData meta, String catalog,
         String schemaName, String tableName, String columnName, Connection conn)
         throws SQLException {
         return getColumns(meta, DBIdentifier.newCatalog(catalog),
             DBIdentifier.newSchema(schemaName),
             DBIdentifier.newTable(tableName),
             DBIdentifier.newColumn(columnName),
             conn);
     }

     @Override
     public Column[] getColumns(DatabaseMetaData meta, DBIdentifier catalog,
         DBIdentifier schemaName, DBIdentifier tableName, DBIdentifier columnName, Connection conn)
         throws SQLException {

         Column[] cols = super.getColumns(meta, catalog, schemaName, tableName,
             columnName, conn);

         // for opta driver, which reports nvarchar as unknown type
         for (int i = 0; cols != null && i < cols.length; i++) {
             String typeName = cols[i].getTypeIdentifier().getName();
             if (typeName == null)
                 continue;

             typeName = typeName.toUpperCase(Locale.ENGLISH);
             if ("NVARCHAR".equals(typeName))
                 cols[i].setType(Types.VARCHAR);
             else if ("UNIQUEIDENTIFIER".equals(typeName)) {
                 if (uniqueIdentifierAsVarbinary)
                     cols[i].setType(Types.VARBINARY);
                 else
                     cols[i].setType(Types.VARCHAR);
             } else if ("NCHAR".equals(typeName))
                 cols[i].setType(Types.CHAR);
             else if ("NTEXT".equals(typeName))
                 cols[i].setType(Types.CLOB);
         }
         return cols;
     }

     @Override
     protected void appendLength(SQLBuffer buf, int type) {
         if (type == Types.VARCHAR)
             buf.append("(").append(Integer.toString(characterColumnSize))
                 .append(")");
     }

     /**
      * If this dictionary supports XML type, use this method to append xml
      * predicate.
      *
      * @param buf the SQL buffer to write the comparison
      * @param op the comparison operation to perform
      * @param lhs the left hand side of the comparison
      * @param rhs the right hand side of the comparison
      * @param lhsxml indicates whether the left operand maps to xml
      * @param rhsxml indicates whether the right operand maps to xml
      */
     @Override
     public void appendXmlComparison(SQLBuffer buf, String op, FilterValue lhs,
         FilterValue rhs, boolean lhsxml, boolean rhsxml) {
         super.appendXmlComparison(buf, op, lhs, rhs, lhsxml, rhsxml);
         if (lhsxml && rhsxml)
             appendXmlComparison2(buf, op, lhs, rhs);
         else if (lhsxml)
             appendXmlComparison1(buf, op, lhs, rhs);
         else
             appendXmlComparison1(buf, op, rhs, lhs);
     }

     /**
      * Append an xml comparison predicate
      *
      * @param buf the SQL buffer to write the comparison
      * @param op the comparison operation to perform
      * @param lhs the left hand side of the comparison (maps to xml column)
      * @param rhs the right hand side of the comparison
      */
     private void appendXmlComparison1(SQLBuffer buf, String op,
         FilterValue lhs, FilterValue rhs) {
         boolean castrhs = rhs.isConstant();
         if (castrhs)
             appendXmlValue(buf, lhs);
         else
             appendXmlExist(buf, lhs);
         buf.append(" ").append(op).append(" ");
         if (castrhs)
             rhs.appendTo(buf);
         else {
             buf.append("sql:column(\"");
             rhs.appendTo(buf);
             buf.append("\")").append("]') = 1");
         }
     }

     private void appendXmlExist(SQLBuffer buf, FilterValue lhs) {
         buf.append(lhs.getColumnAlias(lhs.getFieldMapping().getColumns()[0]))
             .append(".exist('").append("/*[");
         lhs.appendTo(buf);
     }

     /**
      * Append an xml comparison predicate (both operands map to xml column)
      *
      * @param buf the SQL buffer to write the comparison
      * @param op the comparison operation to perform
      * @param lhs the left hand side of the comparison (maps to xml column)
      * @param rhs the right hand side of the comparison (maps to xml column)
      */
     private void appendXmlComparison2(SQLBuffer buf, String op,
         FilterValue lhs, FilterValue rhs) {
         appendXmlValue(buf, lhs);
         buf.append(" ").append(op).append(" ");
         appendXmlValue(buf, rhs);
     }

     private void appendXmlValue(SQLBuffer buf, FilterValue val) {
         Class<?> rc = Filters.wrap(val.getType());
         int type = getJDBCType(JavaTypes.getTypeCode(rc), false);
         boolean isXmlAttribute = (val.getXmlMapping() == null)
             ? false : val.getXmlMapping().isXmlAttribute();
         buf.append(val.getColumnAlias(val.getFieldMapping().getColumns()[0]))
             .append(".value(").append("'(/*/");
         val.appendTo(buf);
         if (!isXmlAttribute)
             buf.append("/text()");
         buf.append(")[1]','").append(getTypeName(type));
         appendLength(buf, type);
         buf.append("')");
     }

     /**
      * Return DB specific schemaCase
      */
     @Override
     public String getSchemaCase() {
         return schemaCase;
     }

     @Override
     public void setSupportsXMLColumn(boolean b) {
         super.setSupportsXMLColumn(b);
         // MS SQL Server requires XML data in UTF-16 or UCS-2 instead of JAXB default of UTF-8
         super.setXMLTypeEncoding("UTF-16");
     }

     @Override
     public boolean isFatalException(int subtype,  SQLException ex) {
         String errorState = ex.getSQLState();
         if ((subtype == StoreException.LOCK  || subtype == StoreException.QUERY)
           &&("1222".equals(errorState) || "HY008".equals(errorState) || "HYT00".equals(errorState)))
          return false;
         return super.isFatalException(subtype, ex);
     }

     /**
      * Obtain an {@link InputStream} by using {@link ResultSet#getBlob(int)} and
      * {@link Blob#getBinaryStream()}.
      * Unfortunately this will load entire BLOB into memory.
      * The alternative {@link ResultSet#getBinaryStream(int)} provides true streaming but
      * the stream can be consumed only as long as {@link ResultSet} is open.
      */
     @Override
     public InputStream getLOBStream(JDBCStore store, ResultSet rs, int column) throws SQLException {
         Blob blob = rs.getBlob(column);
         if (blob == null) {
             return null;
         }
         return blob.getBinaryStream();
     }

     /**
      * Obtain a {@link Reader} by using {@link ResultSet#getClob(int)} and
      * {@link Clob#getCharacterStream()}.
      * Unfortunately this will load entire CLOB into memory.
      * The alternative {@link ResultSet#getCharacterStream(int)} provides true streaming but
      * the stream can be consumed only as long as {@link ResultSet} is open.
      */
     @Override
     public Reader getCharacterStream(ResultSet rs, int column) throws SQLException {
         Clob clob = rs.getClob(column);
         if (clob == null) {
             return null;
         }
         return clob.getCharacterStream();
     }


     @Override
     public LocalDate getLocalDate(ResultSet rs, int column) throws SQLException {
         return rs.getObject(column, LocalDate.class);
     }

     @Override
     public void setLocalTime(PreparedStatement stmnt, int idx, LocalTime val, Column col) throws SQLException {
         stmnt.setObject(idx, val);
     }

     @Override
     public LocalTime getLocalTime(ResultSet rs, int column) throws SQLException {
         return rs.getObject(column, LocalTime.class);
     }

     @Override
     public void setLocalDateTime(PreparedStatement stmnt, int idx, LocalDateTime val, Column col) throws SQLException {
         stmnt.setObject(idx, val);
     }

     @Override
     public LocalDateTime getLocalDateTime(ResultSet rs, int column) throws SQLException {
         return rs.getObject(column, LocalDateTime.class);
     }

     @Override
     public void setOffsetDateTime(PreparedStatement stmnt, int idx, OffsetDateTime val, Column col) throws SQLException {
         stmnt.setObject(idx, val);
     }

     /**
      * h2 does intentionally not support {@code getTimestamp()} for 'TIME WITH TIME ZONE' columns.
      * See h2 ticket #413.
      */
     @Override
     public OffsetDateTime getOffsetDateTime(ResultSet rs, int column) throws SQLException {
         return rs.getObject(column, OffsetDateTime.class);
     }

     @Override
     public void setTime(PreparedStatement stmnt, int idx, Time val, Calendar cal, Column col) throws SQLException {
         if (sendTimeAsString) {
             stmnt.setString(idx, val.toString());
         }
         else {
             // use Time
             super.setTime(stmnt, idx, val, cal, col);
         }
     }

     @Override
     public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find,
         FilterValue start) {
         buf.append("CHARINDEX(");
         find.appendTo(buf);
         buf.append(", ");
         str.appendTo(buf);
         if (start != null) {
             buf.append(", ");
             start.appendTo(buf);
         }
         buf.append(")");
     }

     @Override
     protected void appendSelectRange(SQLBuffer buf, long start, long end, boolean subselect) {
         //SQL Server 2012 supports range select
         if (this.getMajorVersion() >= 11) {
             //we need an order by clause....
             if (!buf.getSQL().contains(" ORDER BY ")) {
                 buf.append(" ORDER BY 1 ");
             }
             buf.append(" OFFSET ").append(Long.toString(start)).append(" ROWS ").
                     append(" FETCH NEXT ").append(Long.toString(end - start)).append(" ROWS ONLY ");
         } else {
             super.appendSelectRange(buf, start, end, subselect);
         }
     }
 }
	/*
	* 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.openjpa.jdbc.sql;

	import java.io.InputStream;
	import java.io.Reader;
	import java.sql.Blob;
	import java.sql.Clob;
	import java.sql.Connection;
	import java.sql.DatabaseMetaData;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Time;
	import java.sql.Types;
	import java.time.LocalDate;
	import java.time.LocalDateTime;
	import java.time.LocalTime;
	import java.time.OffsetDateTime;
	import java.util.Arrays;
	import java.util.Calendar;
	import java.util.Locale;

	import org.apache.openjpa.jdbc.identifier.DBIdentifier;
	import org.apache.openjpa.jdbc.kernel.JDBCStore;
	import org.apache.openjpa.jdbc.kernel.exps.FilterValue;
	import org.apache.openjpa.jdbc.schema.Column;
	import org.apache.openjpa.kernel.Filters;
	import org.apache.openjpa.lib.util.Localizer;
	import org.apache.openjpa.meta.JavaTypes;
	import org.apache.openjpa.util.StoreException;


	/**
	* Dictionary for Microsoft SQL Server.
	*/
	public class SQLServerDictionary extends AbstractSQLServerDictionary {

	public static final String VENDOR_MICROSOFT = "microsoft";
	public static final String VENDOR_NETDIRECT = "netdirect";
	public static final String VENDOR_JTDS = "jtds";

	private static final Localizer _loc =
	Localizer.forPackage(SQLServerDictionary.class);

	private String schemaCase = SCHEMA_CASE_PRESERVE;
	/**
	* Flag whether to treat UNIQUEIDENTIFIER as VARBINARY or VARCHAR
	*/
	public boolean uniqueIdentifierAsVarbinary = true;

	/**
	* SQLServer doesn't like a java.sql.Time as default.
	* So either we send it as String or people configure sendTimeAsDatetime=false on the Connection.
	* This is depending how the Database actually is setup.
	* To mitigate misconfiguration we can work around by sending the time as String to the JDBC driver.
	*/
	public Boolean sendTimeAsString = null;

	public SQLServerDictionary() {
	platform = "Microsoft SQL Server";
	// SQLServer locks on a table-by-table basis
	forUpdateClause = null;
	tableForUpdateClause = "WITH (UPDLOCK)";
	supportsNullTableForGetColumns = false;
	requiresAliasForSubselect = true;
	stringLengthFunction = "LEN({0})";

	timeWithZoneTypeName = "TIME";
	timestampWithZoneTypeName = "DATETIMEOFFSET";

	indexPhysicalForeignKeys = true; // MS-SQLServer does not automatically create an index for a foreign key so we will

	// reservedWordSet subset that CANNOT be used as valid column names
	// (i.e., without surrounding them with double-quotes)
	// generated at 2021-05-02T16:15:30.630 via org.apache.openjpa.reservedwords.ReservedWordsIT
	invalidColumnWordSet.addAll(Arrays.asList(new String[] {
	"ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AUTHORIZATION", "BACKUP", "BEGIN", "BETWEEN", "BREAK", "BROWSE",
	"BULK", "BY", "CASCADE", "CASE", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTERED", "COALESCE", "COLLATE", "COLUMN",
	"COMMIT", "COMPUTE", "CONSTRAINT", "CONTAINS", "CONTAINSTABLE", "CONTINUE", "CONVERT", "CREATE", "CROSS", "CURRENT",
	"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE", "DBCC", "DEALLOCATE",
	"DECLARE", "DEFAULT", "DELETE", "DENY", "DESC", "DISTINCT", "DISTRIBUTED", "DOUBLE", "DROP", "ELSE", "END", "END-EXEC",
	"ERRLVL", "ESCAPE", "EXCEPT", "EXEC", "EXECUTE", "EXISTS", "EXIT", "EXTERNAL", "FETCH", "FILE", "FILLFACTOR", "FOR",
	"FOREIGN", "FREETEXT", "FREETEXTTABLE", "FROM", "FULL", "FUNCTION", "GOTO", "GRANT", "GROUP", "HAVING", "HOLDLOCK",
	"IDENTITY", "IDENTITY_INSERT", "IDENTITYCOL", "IF", "IN", "INDEX", "INNER", "INSERT", "INTERSECT", "INTO", "IS",
	"JOIN", "KEY", "KILL", "LEFT", "LIKE", "LINENO", "MERGE", "NATIONAL", "NOCHECK", "NONCLUSTERED", "NOT", "NULL",
	"NULLIF", "OF", "OFF", "OFFSETS", "ON", "OPEN", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET", "OPENXML", "OPTION",
	"OR", "ORDER", "OUTER", "OVER", "PERCENT", "PLAN", "PRIMARY", "PRINT", "PROC", "PROCEDURE", "PUBLIC", "RAISERROR",
	"READ", "READTEXT", "RECONFIGURE", "REFERENCES", "REPLICATION", "RESTORE", "RESTRICT", "RETURN", "REVOKE", "RIGHT",
	"ROLLBACK", "ROWCOUNT", "ROWGUIDCOL", "RULE", "SAVE", "SCHEMA", "SELECT", "SESSION_USER", "SET", "SETUSER", "SHUTDOWN",
	"SOME", "STATISTICS", "SYSTEM_USER", "TABLE", "TABLESAMPLE", "TEXTSIZE", "THEN", "TO", "TOP", "TRAN", "TRANSACTION",
	"TRIGGER", "TRUNCATE", "TSEQUAL", "UNION", "UNIQUE", "UPDATE", "UPDATETEXT", "USE", "USER", "VALUES", "VARYING",
	"VIEW", "WAITFOR", "WHEN", "WHERE", "WHILE", "WITH", "WRITETEXT",
	}));
	}

	@Override
	public void connectedConfiguration(Connection conn) throws SQLException {
	super.connectedConfiguration(conn);
	boolean requiresWarnings = true;
	DatabaseMetaData meta = conn.getMetaData();
	String driverName = meta.getDriverName();
	String url = meta.getURL();
	if (driverVendor == null) {
	// serverMajorVersion of 8==2000, 9==2005, 10==2008, 11==2012
	if (meta.getDatabaseMajorVersion() >= 9) {
	setSupportsXMLColumn(true);
	if (sendTimeAsString == null) {
	sendTimeAsString = Boolean.FALSE;
	}
	}
	if (meta.getDatabaseMajorVersion() >= 10) {
	// MSSQL 2008 supports new date, time and datetime2 types
	// Use DATETIME2 which has 100ns vs. 3.333msec precision
	dateTypeName = "DATE";
	timeTypeName = "TIME";
	timestampTypeName = "DATETIME2";
	datePrecision = MICRO / 10;
	}
	if (meta.getDatabaseMajorVersion() >= 11) {
	//SQLServer 2012 supports range select
	rangePosition = RANGE_POST_SELECT;
	supportsSelectStartIndex = true;
	supportsSelectEndIndex = true;
	}
	if (driverName != null) {
	if (driverName.startsWith("Microsoft SQL Server")) {
	// v1.1, 1.2, 2.0 or 3.0 driver
	driverVendor = VENDOR_MICROSOFT;
	if (meta.getDriverMajorVersion() >= 2) {
	// see http://blogs.msdn.com/jdbcteam/archive/2007/05/\
	// 02/what-is-adaptive-response-buffering-and-why-\
	// should-i-use-it.aspx
	// 2.0 driver connectURL automatically includes
	// responseBuffering=adaptive
	// and disableStatementPooling=true
	requiresWarnings = false;
	}
	} else {
	if ("NetDirect JSQLConnect".equals(driverName))
	driverVendor = VENDOR_NETDIRECT;
	else if (driverName.startsWith("jTDS"))
	driverVendor = VENDOR_JTDS;
	else if ("SQLServer".equals(driverName)) {
	if (url != null &&
	url.startsWith("jdbc:microsoft:sqlserver:"))
	driverVendor = VENDOR_MICROSOFT;
	else if (url != null &&
	url.startsWith("jdbc:datadirect:sqlserver:"))
	driverVendor = VENDOR_DATADIRECT;
	else
	driverVendor = VENDOR_OTHER;
	}
	// old way of determining xml support
	if (driverName.indexOf(platform) != -1) {
	String versionString =
	driverName.substring(platform.length() + 1);
	if (versionString.indexOf(" ") != -1)
	versionString = versionString.substring(0,
	versionString.indexOf(" "));
	int version = Integer.parseInt(versionString);
	if (version >= 2005)
	setSupportsXMLColumn(true);
	}
	}
	} else {
	driverVendor = VENDOR_OTHER;
	}
	}

	// warn about not using cursors for pre-2.0 MS driver
	// as connectURL includes selectMethod=direct
	if (((VENDOR_MICROSOFT.equalsIgnoreCase(driverVendor) &&
	requiresWarnings) \|\|
	VENDOR_DATADIRECT.equalsIgnoreCase(driverVendor)) &&
	(url.toLowerCase(Locale.ENGLISH).indexOf("selectmethod=cursor") == -1))
	log.warn(_loc.get("sqlserver-cursor", url));

	// warn about prepared statement caching if using pre-2.0 MS drivers
	// as connectURL includes responseBuffering=full
	String props = conf.getConnectionFactoryProperties();
	if ((props != null) &&
	VENDOR_MICROSOFT.equalsIgnoreCase(driverVendor) &&
	requiresWarnings &&
	(props.toLowerCase(Locale.ENGLISH).indexOf("maxcachedstatements=0") == -1))
	log.warn(_loc.get("sqlserver-cachedstmnts"));
	}

	@Override
	public Column[] getColumns(DatabaseMetaData meta, String catalog,
	String schemaName, String tableName, String columnName, Connection conn)
	throws SQLException {
	return getColumns(meta, DBIdentifier.newCatalog(catalog),
	DBIdentifier.newSchema(schemaName),
	DBIdentifier.newTable(tableName),
	DBIdentifier.newColumn(columnName),
	conn);
	}

	@Override
	public Column[] getColumns(DatabaseMetaData meta, DBIdentifier catalog,
	DBIdentifier schemaName, DBIdentifier tableName, DBIdentifier columnName, Connection conn)
	throws SQLException {

	Column[] cols = super.getColumns(meta, catalog, schemaName, tableName,
	columnName, conn);

	// for opta driver, which reports nvarchar as unknown type
	for (int i = 0; cols != null && i < cols.length; i++) {
	String typeName = cols[i].getTypeIdentifier().getName();
	if (typeName == null)
	continue;

	typeName = typeName.toUpperCase(Locale.ENGLISH);
	if ("NVARCHAR".equals(typeName))
	cols[i].setType(Types.VARCHAR);
	else if ("UNIQUEIDENTIFIER".equals(typeName)) {
	if (uniqueIdentifierAsVarbinary)
	cols[i].setType(Types.VARBINARY);
	else
	cols[i].setType(Types.VARCHAR);
	} else if ("NCHAR".equals(typeName))
	cols[i].setType(Types.CHAR);
	else if ("NTEXT".equals(typeName))
	cols[i].setType(Types.CLOB);
	}
	return cols;
	}

	@Override
	protected void appendLength(SQLBuffer buf, int type) {
	if (type == Types.VARCHAR)
	buf.append("(").append(Integer.toString(characterColumnSize))
	.append(")");
	}

	/**
	* If this dictionary supports XML type, use this method to append xml
	* predicate.
	*
	* @param buf the SQL buffer to write the comparison
	* @param op the comparison operation to perform
	* @param lhs the left hand side of the comparison
	* @param rhs the right hand side of the comparison
	* @param lhsxml indicates whether the left operand maps to xml
	* @param rhsxml indicates whether the right operand maps to xml
	*/
	@Override
	public void appendXmlComparison(SQLBuffer buf, String op, FilterValue lhs,
	FilterValue rhs, boolean lhsxml, boolean rhsxml) {
	super.appendXmlComparison(buf, op, lhs, rhs, lhsxml, rhsxml);
	if (lhsxml && rhsxml)
	appendXmlComparison2(buf, op, lhs, rhs);
	else if (lhsxml)
	appendXmlComparison1(buf, op, lhs, rhs);
	else
	appendXmlComparison1(buf, op, rhs, lhs);
	}

	/**
	* Append an xml comparison predicate
	*
	* @param buf the SQL buffer to write the comparison
	* @param op the comparison operation to perform
	* @param lhs the left hand side of the comparison (maps to xml column)
	* @param rhs the right hand side of the comparison
	*/
	private void appendXmlComparison1(SQLBuffer buf, String op,
	FilterValue lhs, FilterValue rhs) {
	boolean castrhs = rhs.isConstant();
	if (castrhs)
	appendXmlValue(buf, lhs);
	else
	appendXmlExist(buf, lhs);
	buf.append(" ").append(op).append(" ");
	if (castrhs)
	rhs.appendTo(buf);
	else {
	buf.append("sql:column(\"");
	rhs.appendTo(buf);
	buf.append("\")").append("]') = 1");
	}
	}

	private void appendXmlExist(SQLBuffer buf, FilterValue lhs) {
	buf.append(lhs.getColumnAlias(lhs.getFieldMapping().getColumns()[0]))
	.append(".exist('").append("/*[");
	lhs.appendTo(buf);
	}

	/**
	* Append an xml comparison predicate (both operands map to xml column)
	*
	* @param buf the SQL buffer to write the comparison
	* @param op the comparison operation to perform
	* @param lhs the left hand side of the comparison (maps to xml column)
	* @param rhs the right hand side of the comparison (maps to xml column)
	*/
	private void appendXmlComparison2(SQLBuffer buf, String op,
	FilterValue lhs, FilterValue rhs) {
	appendXmlValue(buf, lhs);
	buf.append(" ").append(op).append(" ");
	appendXmlValue(buf, rhs);
	}

	private void appendXmlValue(SQLBuffer buf, FilterValue val) {
	Class<?> rc = Filters.wrap(val.getType());
	int type = getJDBCType(JavaTypes.getTypeCode(rc), false);
	boolean isXmlAttribute = (val.getXmlMapping() == null)
	? false : val.getXmlMapping().isXmlAttribute();
	buf.append(val.getColumnAlias(val.getFieldMapping().getColumns()[0]))
	.append(".value(").append("'(/*/");
	val.appendTo(buf);
	if (!isXmlAttribute)
	buf.append("/text()");
	buf.append(")[1]','").append(getTypeName(type));
	appendLength(buf, type);
	buf.append("')");
	}

	/**
	* Return DB specific schemaCase
	*/
	@Override
	public String getSchemaCase() {
	return schemaCase;
	}

	@Override
	public void setSupportsXMLColumn(boolean b) {
	super.setSupportsXMLColumn(b);
	// MS SQL Server requires XML data in UTF-16 or UCS-2 instead of JAXB default of UTF-8
	super.setXMLTypeEncoding("UTF-16");
	}

	@Override
	public boolean isFatalException(int subtype, SQLException ex) {
	String errorState = ex.getSQLState();
	if ((subtype == StoreException.LOCK \|\| subtype == StoreException.QUERY)
	&&("1222".equals(errorState) \|\| "HY008".equals(errorState) \|\| "HYT00".equals(errorState)))
	return false;
	return super.isFatalException(subtype, ex);
	}

	/**
	* Obtain an {@link InputStream} by using {@link ResultSet#getBlob(int)} and
	* {@link Blob#getBinaryStream()}.
	* Unfortunately this will load entire BLOB into memory.
	* The alternative {@link ResultSet#getBinaryStream(int)} provides true streaming but
	* the stream can be consumed only as long as {@link ResultSet} is open.
	*/
	@Override
	public InputStream getLOBStream(JDBCStore store, ResultSet rs, int column) throws SQLException {
	Blob blob = rs.getBlob(column);
	if (blob == null) {
	return null;
	}
	return blob.getBinaryStream();
	}

	/**
	* Obtain a {@link Reader} by using {@link ResultSet#getClob(int)} and
	* {@link Clob#getCharacterStream()}.
	* Unfortunately this will load entire CLOB into memory.
	* The alternative {@link ResultSet#getCharacterStream(int)} provides true streaming but
	* the stream can be consumed only as long as {@link ResultSet} is open.
	*/
	@Override
	public Reader getCharacterStream(ResultSet rs, int column) throws SQLException {
	Clob clob = rs.getClob(column);
	if (clob == null) {
	return null;
	}
	return clob.getCharacterStream();
	}


	@Override
	public LocalDate getLocalDate(ResultSet rs, int column) throws SQLException {
	return rs.getObject(column, LocalDate.class);
	}

	@Override
	public void setLocalTime(PreparedStatement stmnt, int idx, LocalTime val, Column col) throws SQLException {
	stmnt.setObject(idx, val);
	}

	@Override
	public LocalTime getLocalTime(ResultSet rs, int column) throws SQLException {
	return rs.getObject(column, LocalTime.class);
	}

	@Override
	public void setLocalDateTime(PreparedStatement stmnt, int idx, LocalDateTime val, Column col) throws SQLException {
	stmnt.setObject(idx, val);
	}

	@Override
	public LocalDateTime getLocalDateTime(ResultSet rs, int column) throws SQLException {
	return rs.getObject(column, LocalDateTime.class);
	}

	@Override
	public void setOffsetDateTime(PreparedStatement stmnt, int idx, OffsetDateTime val, Column col) throws SQLException {
	stmnt.setObject(idx, val);
	}

	/**
	* h2 does intentionally not support {@code getTimestamp()} for 'TIME WITH TIME ZONE' columns.
	* See h2 ticket #413.
	*/
	@Override
	public OffsetDateTime getOffsetDateTime(ResultSet rs, int column) throws SQLException {
	return rs.getObject(column, OffsetDateTime.class);
	}

	@Override
	public void setTime(PreparedStatement stmnt, int idx, Time val, Calendar cal, Column col) throws SQLException {
	if (sendTimeAsString) {
	stmnt.setString(idx, val.toString());
	}
	else {
	// use Time
	super.setTime(stmnt, idx, val, cal, col);
	}
	}

	@Override
	public void indexOf(SQLBuffer buf, FilterValue str, FilterValue find,
	FilterValue start) {
	buf.append("CHARINDEX(");
	find.appendTo(buf);
	buf.append(", ");
	str.appendTo(buf);
	if (start != null) {
	buf.append(", ");
	start.appendTo(buf);
	}
	buf.append(")");
	}

	@Override
	protected void appendSelectRange(SQLBuffer buf, long start, long end, boolean subselect) {
	//SQL Server 2012 supports range select
	if (this.getMajorVersion() >= 11) {
	//we need an order by clause....
	if (!buf.getSQL().contains(" ORDER BY ")) {
	buf.append(" ORDER BY 1 ");
	}
	buf.append(" OFFSET ").append(Long.toString(start)).append(" ROWS ").
	append(" FETCH NEXT ").append(Long.toString(end - start)).append(" ROWS ONLY ");
	} else {
	super.appendSelectRange(buf, start, end, subselect);
	}
	}
	}