Oracle ATG Web Commerce products do not support Unicode for MS SQL Server databases. To use Microsoft SQL Server with Oracle ATG Web Commerce products, be sure the useSetUnicodeStream
property of all SQL repository components is set to false
(default). To ensure that no Oracle ATG Web Commerce components are configured to use useSetUnicodeStream
, you can set this property in your localconfig/GLOBAL.properties
file:
useSetUnicodeStream=false
If you are creating localized content, set the useSetAsciiStream
property to false
in your localconfig/GLOBAL.properties
file:
useSetAsciiStream=false
If you are using the Microsoft SQL Server 2005 JDBC driver, you must set sendStringParametersAsUnicode
to false
in your URL connection string. For example:
URL=jdbc:sqlserver://<SERVER>:<PORT>;databaseName=<DATABASE>;
sendStringParametersAsUnicode=false
The sendStringParametersAsUnicode=false
setting avoids Unicode character conversion and enables MS SQL Server to use indexes in queries.
In addition, to prevent deadlocks and timeout problems, you must turn on READ_COMMITTED_SNAPSHOT
. For example:
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON;
Using iNet (Merlia) Drivers
If you are using iNet drivers on JBoss, bear in mind that this driver does not allow for passing information by URL; therefore, some additional information must be set in the property fields, as shown in this example:
<xa-datasource> <jndi-name>ATGProductionDS</jndi-name> <track-connection-by-tx/> <isSameRM-override-value>false</isSameRM-override-value> <min-pool-size>5</min-pool-size> <max-pool-size>100</max-pool-size> <blocking-timeout-millis>5000</blocking-timeout-millis> <idle-timeout-minutes>15</idle-timeout-minutes> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> <xa-datasource-class>com.inet.tds.DTCDataSource</xa-datasource-class> <xa-datasource-property name="ServerName">server_name
</xa-datasource-property> <xa-datasource-property name="DatabaseName">database_name
</xa-datasource- property> <xa-datasource-property name="User">database_username
</xa-datasource-property> <xa-datasource-property name="Password">database_password
</xa-datasource- property> <xa-datasource-property name="Mode">71</xa-datasource-property> <!-- sql to call when connection is created --> <new-connection-sql>select 1</new-connection-sql> <!-- sql to call on an existing pooled connection when it is obtained from pool --> <check-valid-connection-sql>select 1</check-valid-connection-sql> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml --> <metadata> <type-mapping>MS SQLSERVER2000</type-mapping> </metadata> </xa-datasource>
Note: Entering passwords in clear text files entails some security risks. Take steps to secure files that contain clear text passwords.
If you are using iNet drivers with WebLogic, when you create your data source, use the following settings:
The type should be DataDirect’s MSSQL type 4 XA.
Set the following properties:
url
—The full connection string for your data source.driver
—The driver name iscom.inet.tds.DTCDataSource
.user
—User name for the database account.port
—Connection port used for the database.mode
—This should normally be set to 71, as Unicode is not supported for MS SQL.serverName
—The machine name of the database host.secureLevel
—Set this to 0 if you are not using SSL. If you are using SSL, see your database documentation for information.
Using iNet Merlia Driver with JBoss EAP 6
When configuring an iNet Merlia driver for an MSSQL data source with JBoss EAP 6, you need to manually identify the correct driver for each data source. During deployment, JBoss creates two different drivers for Merlia, based on the name of the JAR file, the iNet driver class names and the version of the driver. To register your data source, you must provide the JBoss internal driver name:
Start JBoss server and deploy the driver (the
Merlia.jar.dodeploy
driver is deployed automatically when the server starts).Open the JBoss Management Console at
http://
server-name
:
port
and then clicking on Management Console.Select Profile > Datasources
Select Add to add a new data source.
Enter a data source name and a JNDI name.
Select Next. JBoss displays the option to choose a driver for the data source and displays a list of installed drivers. Note that there are two different
Merlia.jar
drivers., which are based on the class type and version of the driver..com.inet.tds.TdsDataSource
- The JDBC 1 data source , a simple data source..com.inet.tds.PDataSource
– A pooled data source.
Identify the appropriate driver name to add to the server’s configuration file.
Once you have retrieved the driver, open the server’s configuration file and enter the correct driver information as outlined in the Configuring MSSQL Data Sources on JBoss EAP 6 section. For example:
<xa-datasource enabled="true" jndi-name="java:/ATGProductionDS" pool-name="ATGProductionDS" use-java-context="true"> <xa-datasource-property name="ServerName">localhost</xa-datasource-property> <xa-datasource-property name="DatabaseName">prod</xa-datasource-property> <xa-datasource-property name="User">sa</xa-datasource-property> <xa-datasource-property name="Password">password</xa-datasource-property> <xa-datasource-property name="Mode">71</xa-datasource-property> <xa-datasource-class>com.inet.tds.DTCDataSource</xa-datasource-class> <new-connection-sql>select 1</new-connection-sql> <driver>Merlia.jarcom.inet.tds.TdsDriver_8_0</driver> <security> <user-name>sa</user-name> <password>NB129T</password> </security> <xa-pool> <min-pool-size>5</min-pool-size> <max-pool-size>100</max-pool-size> <is-same-rm-override>false</is-same-rm-override> </xa-pool> <timeout> <blocking-timeout-millis>5000</blocking-timeout-millis> <idle-timeout-minutes>15</idle-timeout-minutes> </timeout> <validation> <exception-sorter class-name="org.jboss.resource.adapter.jdbc. vendor.DB2ExceptionSorter"/> <!-- sql to call on an existing pooled connection when it is obtained from pool --> <check-valid-connection-sql>select 1</check-valid-connection-sql> </validation> <!-- To avoid deadlocks you need set this --> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> </xa-datasource>