JSQLConnect support

Back to main support page

JSQLConnect Technical Reference

Latest Update:

General

QuickStart

This section gives you quick start instructions for the example application. You need to have the database installed and started on your local machine. You can edit and recompile the example as required.

Example Application

  1. Change directory to the folder where you installed JSQLConnect then change directory to the examples/classes directory.
  2. Copy the driver JAR file (JSQLConnect.jar) from your distribution to this directory.
  3. Run the example application from the command prompt with:
    java -classpath .;./JSQLConnect.jar ExampleApplication

You should see a listing of the test table that the example creates and populates.

If you need to modify the example, open the example source code and make the required changes. You can recompile the changes with:

javac ExampleApplication.java

The examples directory also contains a comprehensive range of other examples you can run. The documentation required to compile, deploy and run each example is included in the example's source code.

Setting the Classpath

The JAR file required in the classpath is JSQLConnect.jar. This jar file contains all the classes required to use JSQLConnect.

Since 3rd party JDBC drivers are not included in the JDK you must set the classpath to include JSQLConnect.jar. This is required to instruct Java the location to load the driver classes from. If the classpath is missing an entry for JSQLConnect.jar your application will throw the common error 'class not found' when your application runs.

Setting the classpath correctly is determined by the type of architecture your application uses. The following table describes the classpath setting in various types of architectures. The examples included with your distribution also provide information on setting the classpath for various application architectures.

Application Architecture Java Environment Description
Application Applications are run direct from an operating system prompt. Classpath is configured in the operating system. Append JSQLConnect.jar to the system's classpath. Alternatively, you may specify the classpath on the java command line that executes the application using the java -classpath option.
Java IDE The application runs within the IDE. Each IDE vendor has a different method to set the classpath within their IDE. Simply setting the classpath in the OS will not work, you must add JSQLConnect.jar to the IDE classpath. Please consult the IDE vendor's documentation for all details.
Servlet or JSP Servlets and JSPs are run within a 'servlet/JSP engine' such as Tomcat. The classpath must be set according to the 'servlet/JSP engine's documentation. Simply setting the classpath in the OS will not work. Some servlet/JSP engines provide setup screens that can be used to set the engine's classpath. In that case you must append the correct JNetDirect JAR file to the engine's existing classpath and restart the engine. In other cases, you can deploy the driver by copying JSQLConnect.jar to a specific directory (e.g. 'lib') in the engines installation. Please consult the servlet engine's vendor documentation for all details. The engine's driver classpath may also be specified in an engine specific configuration file.
EJB EJBs are run within an EJB container. EJB containers are sourced from various vendors. See the discussion under Servlet or JSP.
Applet Applets run within a browser but they are downloaded from a web server. Copy JSQLConnect.jar to the web server root and specify the name of the JAR file in the applet's HTML archive tab, e.g. <applet ... archive=JSQLConnect.jar>

Note that on Windows systems, directory names longer than 8.3 or folder names with spaces can cause problems with classpaths. If you suspect these types of issues you should temporarily move the JSQLConnect.jar file into a simple directory name such as C:\Temp, change the classpath and check whether that addresses the problem.

Driver Name

The driver class name is com.jnetdirect.jsql.JSQLDriver. The driver is contained in JSQLConnect.jar.

The class name is used whenever you load the driver with the JDBC driver manager. It is also used whenever you are required to specify the class name of the driver in any driver configuration. For example, configuring a datasource within a J2EE application server may require you to enter the driver class name.

Datasource Names

Since JDBC it is possible to make database connections using datasources. The datasources available with JSQLConnect are described below. Further documentation on the use of datasources is provided later in this document.

DataSource Type Class Name Description
DataSource com.jnetdirect.jsql.JSQLDataSource The non pooling datasource.
PoolingDataSource com.jnetdirect.jsql.JSQLPoolingDataSource The connection pooling datasource. Typically used when the application does not run within a J2EE application server.
ConnectionPoolDataSource com.jnetdirect.jsql.JSQLConnectionPoolDataSource The datasource to configure J2EE application server connection pools. Typically used when the application runs within a J2EE application server.
XADataSource com.jnetdirect.jsql.JSQLXADataSource The datasource to configure J2EE XA datasources. Typically used when the application runs within a J2EE application server and an XA transaction manager.

Database Authentication

JSQLConnect supports SQL Server authentication in the following modes.

Windows Authentication Mode (Trusted Authentication)

Windows Authentication mode allows a user to connect through a Microsoft Windows user account. Trusted Authentication means SQL Server achieves login security integration with Windows by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. SQL Server then permits or denies login access based on that network user name alone, without requiring a separate login name and password.

Trusted authentication can only be used by JDBC clients connecting from Microsoft Windows platform. When installing SQL Server, you may specify either Windows Authentication Mode or mixed mode.

Trusted authentication is specified using the connection property trustedAuthentication. The property may be set in either a driver manager connection string or a datasource property. There is no need to specify the user and password properties when using trusted authentication.

SQL Server Authentication

Authentication is provided by supplying a username and password for the JDBC connection. SQL Server Authentication can be used by JDBC clients on any platform. When installing SQL Server, you must specify mixed mode authentication.

When using SQL Server authentication mode, you must provide a valid username and password for the JDBC connection. The username and password are specified using the 'user' and 'password' properties.

Kerberos/NTLM Authentication

Authentication is provided by supplying trustedAuthentication or the domain name as a connection property along with the user's domain username and password.

A non-null domain name connection property causes the driver to switch to NTLM authentication mode. NTLM Authentication can be used by JDBC clients on any platform. When using NTLM authentication the driver uses the NTLM challenge/response protocol which avoids sending the user's password directly over the network.

True value of trustedAuthentication connection property causes the driver to switch to Kerberos/NTLM authentication mode. It can be used on Windows platform only. Kerberos authentication has higher priority. NTLM will be used only if Kerberos is not acceptable for the connection for some reason. Both NTLMv1 and NTLMv2 are supported. When using Kerberos/NTLM authentication the driver uses the Kerberos/NTLM challenge/response protocol which avoids sending the user's password directly over the network.

Building the Connection URL

The general form of the connection URL is

jdbc:JSQLConnect://[serverName[\instanceName][:portNumber][/property=value[/property=value]]]

where:

  • jdbc:JSQLConnect:// (Required) is known as the sub-protocol and is constant.
  • serverName (Optional) is the address of the server to connect to. This could be a DNS or IP address, or it could be localhost for the local computer. If use IPv6 address, literal IPv6 addresses are enclosed in []. If not specified in the connection URL, the server name must be specified in the properties collection.
  • instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made.
  • portNumber (Optional) is the port to connect to on serverName. The default is 1433. If you are using the default, you do not have to specify the port, nor its preceding :, in the URL.
  • property (Optional) is one or more option connection properties. See the following section on connection properties for details. Any property from the list may be specified. Properties can be delimited with /, ; or &.

Connection Properties

The following driver properties may be specified in various ways:

  • As name=value properties in the connection URL when connecting with Driver Manager.
  • As name=value properties in the Properties parameter of the Driver Manager connect() method.
  • As values in the appropriate setter method of the driver's datasource. For example datasource.setServerName(value), datasource.setDatabaseName(value)
Property Type Default Description
applicationName String JSQLConnect The application name. Used to identify the specific application in various SQL Server profiling and logging tools.
asciiStringParameters Boolean false Set to true to specify that prepared parameters for character data are sent as ASCII rather than UNICODE. This parameter can improve performance for character data index lookup on non-Unicode, SQL Server tables. For example, ASCII row keys can be compared directly without the overhead of conversion from UNICODE.
autoCommit Boolean true Set to false to disable autoCommit mode.
booleanLiterals Boolean false Set to true to enable driver of translation of true to 1 and false to 0. This property can be used to set integer type (typically bit) columns with boolean literals.
bulkMaxSize Integer 2048 Used if useBulkLoad=true. The maximum number of consecutive sql insert statements in a batch to be processed via Insert Bulk with single BULK INSERT statement, otherwise bulk insert will be splitted to several BULK INSERT statements.
bulkMinSize Integer 10 Used if useBulkLoad=true. The minimal number of consecutive sql insert statements in a batch to be processed via BULK INSERT, otherwise via batch execution.
codepage String null The codepage for translating single byte column data.
connectionRetryCount Integer 0 The number of times to retry a database connection.
connectionRetryWait Integer 100 The number of milliseconds the driver should wait between connection attempts.
databaseName, database String null The name of the database to connect to. If not stated, connect to the default database.
disableStatementPooling Boolean false Set to true to disable the pooling of prepared statements.
domain String null Set to the name of the domain name of the client. Used for NTLM authentication only. A non-null domain signals that the driver should use NTLM authentication. The user name and password supplied when using NTLM authentication must be the user's domain user name and password.
enableAlwaysEncrypted Boolean false Set to true to enable Always Encrypted feature. When Always Encrypted is enabled, the JDBC driver transparently encrypts and decrypts sensitive data stored in encrypted database columns in the SQL Server.
enableFailover Boolean false Set to true to enable failover operation. See the section on failover for more details.
instanceName String null The SQL Server instance name to connect to. When not specified a connection is made to the default instance.
iterativeBatching Boolean false Set to true to execute a batch of statements sending each to the database separately.
keyStoreProvider String null This property identifies which key store to seamlessly set up for the connection with Always Encrypted. JQSLConnect supports setting up of the Java Key Store. Note that to use this property, you also need to set the keyStoreLocation and keyStoreSecret properties for the Java Key Store.
keyStoreLocation String null Identifies the path to the keystore file that stores the column master key to be used with Always Encrypted data. Note that the path must include the keystore filename.
keyStorePassword String null Identifies the password to use for the keystore as well as for the key.
lastUpdateCount Boolean false Set to true to have the driver return only the last update count. For example, this property may be used if update counts from triggers should not be returned to the application. The default is to return all update counts.
lockTimeout Integer -1 The number of milliseconds to wait before the database reports a lock time-out. The default behavior is to wait forever. If specified, this value will be the default for all statements on the connection. Note that Statement.setQueryTimeout() may be used to set the timeout for specific statements. The value may be 0 which specifies no wait.
loginTimeout Integer 0 The number of seconds the driver should wait before timing out a failed connection.
packetSize Integer [-1 | 0 | 512..32767] 8000 The network packet size used to communicate with SQL Server, specified in bytes. A value of -1 indicates using the server default packet size. A value of 0 indicates using the maximum value, which is 32767. If this property is less than 512, will be using value 512. If this property is more than 32767, will be used value 32767.
password String null The database password.
portNumber, port Integer [0..65535] 1433 The port that SQL Server is operating on.
readOnlyIntent Boolean false Set to true to specify that the application intent is ReadOnly. The default is ReadWrite.
selectMethod String [default | cursor] null Set to cursor to specify that all statements will use server side cursor with type TYPE_SCROLL_SENSITIVE.
serverName, server String null The server running SQL Server.
ssl String [auto | mandatory] null Set to auto or mandatory. auto means the driver will use SSL if the database instance supports it. mandatory means that the driver will always use SSL for this connection (and therefore the database instance must support SSL).
sslTrusted Boolean false Set to true to specify that the database instance's certificate must be trusted by the java runtime. sslTrusted can be used with either the auto or mandatory ssl connection property settings.
traceActivity Boolean false Used to enable trace activity logging for SQL Server 2012 and later versions.
trustedAuthentication Boolean false Set to true to enable trusted NT authentication.
useBulkLoad Boolean false Set to true to use Insert Bulk instead of Batch execution when it's possible.
useNTLMv2 Boolean false Set to true to enable NTLMv2 authentication.
user String null The database user.
useTimeAsSQLDateTime Boolean true If set to true, java.sql.Time values will be used as SQL Server datetime values. For example, time value '12:10:00' will be converted to '1900-01-01 12:10:00' datetime value. Otherwise, if useTimeAsSQLDateTime is set to false value will be converted to '1970-01-01 12:10:00'.
useTransactionSnapshotMode Boolean false Inits transaction snapshot mode. It means snapshot isolation level is set instead of repeatable read isolation level.
xopenStates Boolean false Set to true to specify that the driver returns XOPEN compliant state codes in exceptions. The default is to return SQL 99 state codes.

Setting DataSource Properties in J2EE Configurations

Data sources are the preferred mechanism to create JDBC connections in a J2EE environment. Data sources provide connections, pooled connections and distributed connections without hard coding connection properties into Java code. All JSQLConnect data sources may set or get the value of any property via the appropriate setter and getter methods respectively.

J2EE products (such as application servers and servlet/JSP engines) typically allow you to configure datasources for database access. Any property from the list above may be specified wherever the configuration allows you to enter a property as a property=value pair. When specifying property values in J2EE configurations take care that case sensitivity is correct.

Connection Examples

Connect to the local machine to the default database with username 'sa' and password 'sesame'.

jdbc:JSQLConnect://localhost/user=sa/password=sesame

Connect to the local machine to the named instance.

jdbc:JSQLConnect://localhost\instanceName/user=sa/password=sesame

Connect to a named database on a remote server.

jdbc:JSQLConnect://example.com/database=pubs/user=sa/password=sesame

Connect on the non-default port '4000' to the remote server.

jdbc:JSQLConnect://example.com:4000/database=northwind/user=sa/password=sesame

Connect to a remote database using trusted authentication.

jdbc:JSQLConnect://123.123.123.123/trustedAuthentication=true

Connect specifying a customized application name.

jdbc:JSQLConnect://example.com/trustedAuthentication=true/applicationName=MyApp

Connect to the local machine using IPv6 address.

jdbc:JSQLConnect://[::1]/user=sa/password=sesame

Making the Database Connection

The examples in the JSQLConnect distribution illustrate various methods of making database connections. For DriverManager connections please see the ExampleApplication.java and for datasource connections see ExampleDataSource.java.

If you are connecting from an applet it's important to use getCodeBase().getHost() to find the server name. This method avoids connection exceptions that may occur due to applet sandbox restrictions. See ExampleApplet.java for details.

Datasource API Documentation

This section provides detailed documentation for each JSQLConnect datasource method.

Reinstalling a license

The section describes how to re-install a paid license for JSQLConnect. You may need to re-install a license, for example, if you have an older version of JSQLConnect and wish to install your paid license into a newer version, and your upgrade to that newer version is a free upgrade.

You can extract your license and install into a different JSQLConnect.jar as follows. Assume JSQLConnect.jar is the original licensed jar you received from JNetDirect and JSQLConnect.jar is the new jar that needs to have the license installed in it. JAR is the Java archive tool included in JDK 1.3. (Older versions of JAR do not support the update (u) option and you will have to install the license using different commands).

Extract the original license using JAR

jar -fvx JSQLConnectOriginal.jar JSQLLicense.class

Install the license into the new JAR

jar -fvu JSQLConnect.jar JSQLLicense.class

List the contents of the JAR and ensure JSQLLicense.class is in the new JAR

jar -ft JSQLConnect.jar

Finally, redeploy JSQLConnect.jar back to your application environment. Verify that no trial license message is displayed by the driver.

Supported DataType Conversions

This matrix describes the combination of SQL Server and JDBC datatypes supported. The entire set of SQL Server data types is listed across the top. The set of JDBC data type calls is listed vertically.

There are three categories of conversions that are supported by the JDBC driver’s methods:

  • Df Default conversion: Type used by default. For example in getObject() method.
  • C Conversion supported: Type can be converted without errors or loss data.
  • D Data Dependent conversion: Conversions from underlying character types to numeric types require that the character types contain values that can be converted into that type.

T-SQL Types Mapped to JDBC Types

The following chart contains the JDBC driver’s conversion map for the get<Type>() methods of the ResultSet class, and the supported conversions for the get<Type>() methods of the CallableStatement class.

JDBC type Exact Numerics Approximate Numerics Date and Time Character Strings Unicode Character Strings Binary Strings Other Data Types
bit tinyint smallint int bigint decimal numeric smallmoney money real float smalldatetime datetime date time datetime2 datetimeoffset char varchar text nchar nvarchar ntext binary varbinary image xml uniqueidentifier sql_variant hierarchyid rowversion
BIT Df D D D D D D D D D D D D D D D D D D D D
BOOLEAN Df D D D D D D D D D D D D D D D D D D D D
TINYINT C Df D D D D D D D D D D D D D D D D D D D
SMALLINT C C Df D D D D D D D D D D D D D D D D D D
INTEGER C C C Df D D D D D D D D D D D D D D D D D
BIGINT C C C C Df D D D D D D D D D D D D D D D D
DECIMAL C C C C C Df Df Df Df D D D D D D D D D D D D
NUMERIC C C C C C Df Df Df Df D D D D D D D D D D D D
REAL C C C D D D D D D Df D D D D D D D D
FLOAT C C C C D D D C D C Df D D D D D D D
DOUBLE C C C C D D D C D C Df D D D D D D D
DATE D D Df D D D D D D D D D
TIME D D Df D D D D D D D D D
TIMESTAMP Df Df C C Df Df D D D D D D D
CHAR C C C C C C C C C C C C C C C C C Df C C C C C D D D C Df C C
VARCHAR C C C C C C C C C C C C C C C C C C Df C C C C D D D C Df C C
LONGVARCHAR C C C C C C C C C C C C C C C C C C Df Df C C C D D D C C C C
CLOB C C C C C C C C C C C C C C C C C C C C C C C D D D C C C C
NCHAR C C C C C C C C C C C C C C C C C C C C Df C C D D D C Df C C
NVARCHAR C C C C C C C C C C C C C C C C C C C C C Df C D D D C Df C C
LONGNVARCHAR C C C C C C C C C C C C C C C C C C C C C Df Df D D D C C C C
NCLOB C C C C C C C C C C C C C C C C C C C C C C C D D D C C C C
BINARY C C C C C C C C C C C C C C C Df C C C C D Df
VARBINARY C C C C C C C C C C C C C C C C Df C C C D Df
LONGVARBINARY C C C C C C C C C C C C C C C C Df Df C C D C
BLOB C C C C C C C C C C C C C C C C C C C C D C
SQLXML D D D D D D Df
JAVA_OBJECT C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C

JDBC Types Mapped to T-SQL Types

The following chart contains the JDBC driver’s conversion map for the Java typed data passed to the set<Type>() methods of the PreparedStatement class and the CallableStatement class.

JDBC type Exact Numerics Approximate Numerics Date and Time Character Strings Unicode Character Strings Binary Strings Other Data Types
bit tinyint smallint int bigint decimal numeric smallmoney money real float smalldatetime datetime date time datetime2 datetimeoffset char varchar text nchar nvarchar ntext binary varbinary image xml uniqueidentifier sql_variant hierarchyid rowversion
NULL C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C
BIT Df C C C C C C C C C C C C C C C C C C C D
BOOLEAN Df C C C C C C C C C C C C C C C C C C C D
TINYINT D Df C C C C C C C C C C C C C C C C C C D
SMALLINT D D Df C C C C C C C C C C C C C C C C C D
INTEGER D D D Df C C C D C D C C C C C C C C C C D
BIGINT D D D D Df C C D D D D C C C C C C C C C D
DECIMAL D D D D D Df Df Df Df D D C C C C C C C C C D
NUMERIC D D D D D Df Df Df Df D D C C C C C C C C C D
REAL D D D D D D D D D Df C C C C C C C D
FLOAT D D D D D D D D D D Df C C C C C C D
DOUBLE D D D D D D D D D D Df C C C C C C D
DATE D D Df C C C C C C C C D
TIME D D Df C C C C C C C C D
TIMESTAMP D D D D Df Df C C C C C C D
CHAR D D D D D D D D D D D D D D D D D Df C C C C C D D D D D D D
VARCHAR D D D D D D D D D D D D D D D D D C Df C C C C D D D D D D D
LONGVARCHAR D D D D D D D D D D D D D D D D D C Df C C C C D D D D D D D
CLOB D D D D D D D D D D D D D D D D D C Df C C C C D D D D D D D
NCHAR D D D D D D D D D D D D D D D D D C C C Df C C D D D D D D D
NVARCHAR D D D D D D D D D D D D D D D D D C C C C Df C D D D D D D D
LONGNVARCHAR D D D D D D D D D D D D D D D D D C C C C Df C D D D D D D D
NCLOB D D D D D D D D D D D D D D D D D C C C C Df C D D D D D D D
BINARY D D D D D D D D D C C C C C C Df C C D D D D
VARBINARY D D D D D D D D D C C C C C C C Df C D D D D
LONGVARBINARY D D D D D D D D D C C C C C C C Df C D D D D
BLOB D D D D D D D D D C C C C C C C Df C D D D D
SQLXML C C C C C C Df D
JAVA_OBJECT D D D D D D D D D D D D D D D D D C C C C C C D D D D D D D

Unicode Support

Since release 7.0, SQL Server provides the new datatypes NCHAR, NVARCHAR and NTEXT. These columns are designed for the storage of Unicode data they all support double byte characters.

These new datatypes are supported by JSQLConnect since the driver automatically handles the UTF conversions from the Java environment to the SQL Server environment. NCHAR and NVARCHAR can store a maximum of 4000 characters. NTEXT stores a maximum string length of 230 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered.

Note that to insert literal Unicode data into a Unicode column you require SQL Server's national prefix N. E.g.

INSERT INTO MyTable VALUES (N'Some Unicode String')

In addition, JSQLConnect, supports the naming of tables, columns and procedures with either ASCII or UNICODE characters.

Codepage Support

If you use international extended characters but do not use the Unicode (double byte) data types (NCHAR, NVARCHAR, NTEXT) you must specify the codepage to use with your application. The codepage is set using the codepage connection setting. As with all connection settings, the codepage setting can be set in the connection URL, in the connection properties or via the setCodepage() method of a data source.

The specified codepage must be supported by your current JVM and match the codepage being used in the target database. Codepage specifications are case sensitive.

Example to use the Greek codepage:

jdbc:JSQLConnect://<ServerName>/database=master/codepage=Cp1253

JDBC Exceptions handling

All database error conditions are returned to Java applications as SQL Exceptions. The Java application may query the returned Exception as follows.

getMessage() - returns the full text of the exception. The error message text describes the problem. The error message text often includes placeholders for information (such as object names) that are inserted in the error message when it is displayed.

getErrorCode() - returns the specific database error number. Error codes are database specific.

getState() - returns the standard XOPEN state code of the exception.

Troubleshooting Connectivity

The JSQLConnect requires that TCP/IP be installed and running to communicate with your SQL Server database. You can use the SQL Server Configuration Manager to verify which network library protocols are installed.

A database connection attempt might fail for many reasons. These can include the following:

  • TCP/IP is not enabled for SQL Server, or the server or port number specified is incorrect. Verify that SQL Server is listening with TCP/IP on the specified server and port. This might be reported with an exception similar to: "The login has failed. The TCP/IP connection to the host has failed." This indicates one of the following:
    • SQL Server is installed but TCP/IP has not been installed as a network protocol for SQL Server by using the SQL Server Network Utility for SQL Server 2000, or the SQL Server Configuration Manager for SQL Server 2005 and later.
    • TCP/IP is installed as a SQL Server protocol, but it is not listening on the port specified in the JDBC connection URL. The default port is 1433, but SQL Server can be configured at product installation to listen on any port. Make sure that SQL Server is listening on port 1433. Or, if the port has been changed, make sure that the port specified in the JDBC connection URL matches the changed port.
    • The address of the machine that is specified in the JDBC connection URL does not refer to a server where SQL Server is installed and started.
    • The networking operation of TCP/IP between the client and server running SQL Server is not operable. You can check TCP/IP connectivity to SQL Server by using telnet. For example, from a command prompt key in:
      telnet 123.123.123.123 1433
      where 123.123.123.123 is the address of the computer that is running SQL Server and 1433 is the port it is listening on. If you receive a message that states "Telnet cannot connect," TCP/IP is not listening on that port for SQL Server connections. Use the SQL Server Network Utility for SQL Server 2000, or the SQL Server Configuration Manager for SQL Server 2005 and later to make sure that SQL Server is configured to use TCP/IP on port 1433.
    • The port that is used by the server has not been opened in the firewall. This includes the port that is used by the server or optionally, the port associated with a named instance of the server.
  • The specified database name is incorrect. Make sure that you are logging on to an existing SQL Server database.
  • The user name or password is incorrect. Make sure that you have the correct values.
  • When you use SQL Server Authentication, the JSQLConnect requires that SQL Server is installed with SQL Server Authentication, which is not the default. Make sure that this option is included when you install or configure your instance of SQL Server.

Tracing Driver Operation and Trouble Shooting

To debug and resolve support issues you can enable tracing in the operation of the driver.

N.B.! Tracing adversely affects the performance of the driver. Use it only when required and disable after use.

Please setup tracing as described below and rerun the application. Ensure that the error or condition at issue is captured when you rerun the application with tracing on.

The driver will trace driver operation to the file specified for tracing. If requested to do so, please forward the trace file to JNetDirect technical support (zipping the file if it is large) along with the version of MS SQL Server in use.

To enable the use of tracing, the JDBC driver uses the logging APIs in java.util.logging, which provides a set of classes for creating Logger and LogRecord objects.

When you develop your application, you can make calls to Logger objects, which in turn create LogRecord objects, which are then passed to Handler objects for processing. Logger and Handler objects both use logging levels, and optionally logging filters, to regulate which LogRecord are processed. When the logging operations are complete, the Handler objects can optionally use Formatter objects to publish the log information.

By default, the java.util.logging framework writes its output to a file. This output log file must have write permissions for the context under which the JDBC driver is running.

For more information about using the various logging objects for program tracing, see the Java Logging Overview.

Logging Levels

Every log message that is created has an associated logging level. The logging level determines the importance of the log message, which is defined by the Level class in java.util.logging. Enabling logging at one level also enables logging at all higher levels.

The following table describes each of the available logging levels.

Logging Level Description
SEVERE Indicates a serious failure and is the highest level of logging. In the JDBC driver, this level is used for reporting errors and exceptions.
WARNING Indicates a potential problem.
INFO Provides informational messages.
CONFIG Provides configuration messages.
FINE Provides basic tracing information including all exceptions thrown by the public methods.
FINER Provides detailed tracing information including all public method entry and exit points with the associated parameter data types, and all public properties for public classes. In addition, input parameters, output parameters, and method return values.
FINEST Provides highly detailed tracing information. This is the lowest level of logging.
OFF Turns off logging.
ALL Enables logging of all messages.

Logging Categories

When you create a Logger object, you must tell the object which named entity or category that you are interested in getting log information from. The JDBC driver supports the following public logging categories, which are all defined in the com.jnetdirect.jsql driver package.

Logging Category Description
com.jnetdirect.jsql Logs messages for all classes.
com.jnetdirect.jsql.Driver Logs messages in the JSQLDriver class.
com.jnetdirect.jsql.Connection Logs messages in the JSQLConnection class.
com.jnetdirect.jsql.Statement Logs messages in the JSQLStatement, the JSQLPreparedStatement and the JSQLCallableStatement classes.
com.jnetdirect.jsql.ResultSet Logs messages in the JSQLResultSet class.
com.jnetdirect.jsql.DatabaseMetaData Logs messages in the JSQLDatabaseMetaData class.
com.jnetdirect.jsql.ResultSetMetaData Logs messages in the JSQLResultSetMetaData class.
com.jnetdirect.jsql.ParameterMetaData Logs messages in the JSQLParameterMetaData class.
com.jnetdirect.jsql.SQLException Logs messages in the SQLException class.

Enabling Tracing Programmatically

Tracing can be enabled programmatically by creating a Logger object and indicating the category to be logged. For example, the following code shows how to enable logging for SQL statements:

Logger logger = Logger.getLogger("com.jnetdirect.jsql.Statement");
logger.setLevel(Level.FINER);

To turn off logging in your code, use the following:

logger.setLevel(Level.OFF);

To log all available categories, use the following:

Logger logger = Logger.getLogger("com.jnetdirect.jsql");
logger.setLevel(Level.FINE);

Since JDBC 4.1 you can call the Driver method getParentLogger(). For example:

Logger logger = DriverManager.getDriver("jdbc:JSQLConnect://").getParentLogger();
logger.setLevel(Level.FINER);

To disable a specific category from being logged, use the following:

Logger logger = Logger.getLogger("com.jnetdirect.jsql.Statement");
logger.setLevel(Level.OFF);

Enabling Tracing by Using Configuration File

You can also enable tracing by using the logging.properties file, which can be found in the lib directory of your Java Runtime Environment (JRE) installation. This file can be used to set the default values for the loggers and handlers that will be used when tracing is enabled.

You can use a different file by specifying a filename with the java.util.logging.config.file system property. For example:

java -Djava.util.logging.config.file=myfile

The following is an example of the settings that you can make in the logging.properties files:

# "handlers" specifies a comma separated list of log Handler classes.
# These handlers will be installed during VM startup.
handlers= java.util.logging.ConsoleHandler

# To also add the FileHandler, use the following line instead.
#handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler

# Default global logging level.
.level= INFO

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = %h/java%u.log
java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

# Limit the message that are printed on the console to INFO and above.
java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

# Facility specific properties.
com.jnetdirect.jsql.level = SEVERE

Getting the Driver Version

The version of the installed JSQLConnect driver may be found in 3 ways:

  1. Call the Driver methods getMajorVersion() and getMinorVersion().
  2. Call the DatabaseMetaData method getDriverVersion() or methods getDriverMajorVersion() and getDriverMinorVersion().
  3. Displayed in the Readme.html of the product distribution.

Machine Specific Licensing

Some JSQLConnect licenses will only operate on specific machines. Licenses that are machine specific are described in the product pricing tables. For machine specific licenses you must provide the machine name when ordering. To obtain the machine name:

  1. Navigate to the MachineName directory of your JSQLConnect installation.
  2. Use Java to execute the MachineName.class program with the command:
    java MachineName
  3. Copy and paste the machine name from MachineName.txt to your order form or PO.

Advanced Topics

Statement Pooling

JSQLConnect supports prepared statement pooling to provide maximized performance for JDBC applications. Statement pooling, combined with connection pooling, provides even greater performance gains than using connection pooling alone. Statement pooling operates by JSQLConnect caching the state of a prepared statement the first time the database prepares the statement. JSQLConnect will then retrieve subsequent preparations of the statement from the statement pool rather than resubmit the statement to the DBMS again. Thus statement pooling minimizes the load on the database and further enhances application performance.

Statement pooling may also be used in conjunction with connection pooling. Statement pools are preserved when pooled connections are closed. Therefore, the statement pool is already available when the application next acquires a connection and even the first statement preparations on the connection will not need to be processed by the DBMS.

The execution of prepared statements operates as follows:

  • PREPARE PHASE: When the application first prepares a statement the driver sends the statement to the DBMS for analysis. The DBMS analyses the statement for syntax and column validation and identifies the optimal execution plan for subsequent invocations of the statement. JSQLConnect caches the statement's prepared state at this point. Subsequent preparations of the statement are now retrieved from the statement pool.
  • EXECUTE PHASE: The application sets statement parameters and executes the statement. Since the analysis overhead of the prepare phase does not need to be repeated, performance generally improves for each statement execution.

This scenario generally provides performance advantages when prepared statements are prepared once and executed (re-used) many times during the lifetime of the application. In this case the ratio of statement prepares to statement executes is low. This is the recommended usage pattern of prepared statements.

However, in some cases it may not be possible to realize the performance improvements of prepared statements effectively since the ratio of statement prepares to statement executes may be quite high. In these cases, overall performance of the application may actually be reduced by many executions of the statement prepare phase. Therefore, in these cases, the connection property disableStatementPooling may be set to disable the use of prepared statement pools.

The setting of disableStatementPooling is totally transparent to application code. Prepared statements may always be used by the application regardless of this property's setting. This connection property simply changes the way in which prepared statements are handled by the driver.

Cursor Types

JSQLConnect supports the following cursor types. For a full description of SQL Server cursor types please refer to SQL Server Books On Line, Cursor Types.

JDBC Type SQL Server Cursor Type Server Load Characteristics Application Requirements
TYPE_FORWARD_ONLY Fast Forward Only Light Forward Only, Read Only Fast, Access all data, Multiple result sets.
TYPE_SCROLL_INSENSITIVE Static Cursor Heavy Other user's updates not reflected Application needs a database snapshot.
TYPE_SCROLL_SENSITIVE Keyset Cursor Medium Other user's updates are reflected, row membership fixed Application needs to see changed data for existing rows only.
TYPE_SCROLL_SENSITIVE+1 Dynamic Heavy Other user's updates are reflected, row membership reflects other's inserts and deletes Application need to see changed data for existing rows and see inserted and deleted rows during lifetime of cursor.

Concurrency Control

Concurrency control refers to the various techniques used to preserve the integrity of the database when multiple users are updating rows simultaneously. Incorrect concurrency can lead to problems such as dirty reads, phantom reads and non-repeatable reads. JSQLConnect provides interfaces to all the concurrency techniques used by SQL Server to resolve these issues. For a full description of SQL Server concurrency please refer to SQL Server Books On Line, Concurrency.

JDBC Concurrency Characteristics Row Locks Application
CONCUR_READ_ONLY Read Only No Application requires only read-only access to rows.
CONCUR_UPDATABLE Optimistic Read Write No Database assumes row contention is unlikely but possible. Row integrity checked with a timestamp comparison.
CONCUR_UPDATABLE+1 Pessimistic Read Write Yes Database assumes row contention is likely. Row integrity is ensured with row locking
CONCUR_UPDATABLE+2 Optimistic Read Write No Database assumes row contention is unlikely but possible. Row integrity checked with a row data comparison.

Guaranteeing an Updateable Result Set

An updateable result set is a result set in which rows may be inserted, updated and deleted. In the following cases SQL Server will not be able to create an updateable cursor. The SQL Server exception generated is "Cursor is READ ONLY".

Cause Description Remedy
Statement is created with TYPE_SCROLL_INSENSITIVE SQL Server creates a static, snapshot cursor which is disconnected from the underlying table rows in order to protect the cursor from row updates by other users. Use TYPE_SCROLL_SENSITIVE(n) to avoid creating a static cursor.
Table design precludes a KEYSET cursor The underlying table does not have unique keys to enable SQL Server to uniquely identify a row. Add unique keys to the table to provide unique identification of each row.

Row Locking Support

JSQLConnect uses SQL Server row locks which implement concurrency control among multiple users performing modifications in a database at the same time. By default, transactions and locks are managed on a per connection basis. For example, if an application opens two JDBC connections, locks acquired by one connection cannot be shared with the other connection. Neither connection can acquire locks that would conflict with locks held by the other connection.

Locking is used to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.

Isolation Levels

JSQLConnect supports all SQL Server isolation levels. The default is Read Committed.

Isolation Level Dirty Read Non Repeatable Read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Snapshot No No Sometimes possible
Serializable No No No

Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then later update the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values, lost updates cannot occur at the default isolation level of read committed.

Thread Safety and Threading Scenarios

The JSQLConnect connection class is thread safe. Statements and result sets are not thread safe. All communication with the database engine is synchronized at the connection level. Transaction control (commit, rollback etc.) is managed in JDBC at the connection level. Therefore, if multiple threads require independent transaction control they must each create and operate with their own connections.

With these rules in mind, the following driver threading scenarios are legal.

Single Threaded Operation

Connection c = <new connection>
Statement s = c.createStatement(..);
s.execute(..)

Multiple Threads Using a Single Connection

Connection c = <new connection>
SomeThread t1 = new SomeThread(c);
t1.start();
SomeThread t2 = new SomeThread(c);
t2.start();

class SomeThread implements Runnable {

    SomeThread(Connection c) {
        connection = c;
    }

    run() {
        Statement s=connection.createStatement(..);
        s.execute(..);
    }
}

Local and Distributed Transactions

Transaction processing is a mandatory requirement of all applications that need to ensure consistency of their persistent data. Transaction processing can be either performed locally or distributed as described below.

Local Transactions

Transactions are atomic, consistent, isolated, and durable (ACID) modules of execution.

  • Atomicity - A transaction will either commit or abort. If a transaction commits, all of its effects remain; if it aborts, all of its effects are undone. For example, when you rename an object, either the new name is created and the old name is deleted (commit), or the object is not renamed (abort).
  • Consistency - A transaction is a correct transformation of the system state; it preserves the state invariants. For example, when you add an element to a double-linked list, all four forward and backward pointers are updated.
  • Isolation - Concurrent transactions are isolated from the updates of other incomplete transactions; these updates do not constitute a consistent state. This property is often called serializability. For example, a second transaction traversing the double-linked list mentioned in the previous consistency example will see the list before or after the insert, but will only see complete changes.
  • Durability - Once a transaction commits, its effects will persist even if there are system failures. For example, after the object in the previous atomicity example is renamed, it will have the new name even if the system fails and reboots right after the commit completes.

JSQLConnect fully supports local transactions via the following JDBC APIs: Connection.setAutoCommit(), Connection.commit(), Connection.rollback(). See Sun's standard JDBC API specifications for the details of these APIs. Local transactions are typically managed explicitly by the application or automatically by the J2EE application server.

Distributed Transactions Using Microsoft Distributed Transaction Coordinator

A distributed transaction is a transaction that updates data on two or more networked databases while retaining the important atomic, consistent, isolated, and durable properties of transaction processing. Distributed transaction support was added to the JDBC API in the JDBC 2.0 Optional API specification. The management of distributed transactions is typically performed automatically by the Java Transaction Service (JTS) transaction manager within a J2EE application server environment. However, JSQLConnect will support distributed transactions under any Java Transaction API (JTA) compliant transaction manager.

JSQLConnect now seamlessly integrates with Microsoft Distributed Transaction Coordinator (MS-DTC) to provide true distributed transaction support with MS SQL Server. MS-DTC is a distributed transaction facility provided by Microsoft for Microsoft Windows systems. MS-DTC uses proven transaction processing technology from Microsoft to support XA features such as the complete two-phase distributed commit protocol and the recovery of distributed transactions.

MS-DTC must be installed and running on your system if MS-DTC support is enabled in JSQLConnect. MS-DTC can be installed along with SQL Server or standalone. The MS-DTC service should be marked automatic in service manager to ensure its running when the server is booted.

To enable MS-DTC integration you need to set the dtcEnabled property of the JSQLXADataSource. For example, setDtcEnabled(true). Be default JSQLXADataSource connections operate using local transactions during distributed transaction processing. For more information on the properties that control JSQLConect/MS-DTC integration please see the documentation for the datasource JSQLXADataSource in the datasources API documentation.

Installation of JSQLConnect/MS_DTC Interface

The components are included in the XAInstall directory of your JSQLConnect installation. Please view the readme.txt file there to carry out the installation of these components.

Named and Multiple SQL Instances

SQL Server allows the installation of multiple database instances per server. Each instance is identified by a specific name. To connect to a named instance of SQL Server you must specify the instance name as a JDBC URL property or a datasource property. If no instance name property is specified a connection to the default instance is created.

Examples:

Using a JDBC URL

jdbc:JSQLConnect://localhost\\instance1/user=sa/<more properties as required>

or using a named property

jdbc:JSQLConnect://remoteHost/instanceName=instance1/user=sa/<more properties as required>

Using a datasource

DataSource.setInstanceName("instance1");

Secure SSL Operation

In many secure applications security is provided by SSL may be required for data transmission. JSQLConnect provides full SSL features such as encrypted data transmission and database identity authentication to JDBC clients.

To enable SSL, use the ssl connection property. Please note that the JSSE package must be available to the Java runtime. JSSE is installed for JRE 1.4 and higher. For older JRE the JSSE package must be downloaded from Oracle and installed.

The ssl connection property can set to auto or mandatory:

  • auto means JSQLConnect will query the database instance's SSL capabilities. If the instance supports SSL, the driver will create an SSL connection. If the instance does not support SSL a non SSL connection will be established.
  • mandatory means JSQLConnect will always create an SSL connection to the database instance. If the database instance does not support SSL an exception will be generated.

To use SSL in JSQLConnect SQL Server must be configured for SSL operation. Specifically, it must be configured with a certificate. For details, please review the Microsoft documentation relating to configuring SQL Server for SSL operation.

The connection property sslTrusted controls whether the certificate presented by the database instance must be trusted by the Java runtime. If sslTrusted is true, the certificate authority ('CA') that signed the database instance certificate must be trusted by JSSE. (i.e. defined in the JSSE truststore). If sslTrusted is false no check is made that the database instance certificate is trusted.

Failover Support

Failover support allows the application to specify alternate failover configurations if a database connection cannot be completed. JSQLConnect supports failover support for a chain of up to 10 fail over configurations. Failover is supported for both Driver Manager and Datasource database connections.

Failover configurations are set using a specific syntax in connection properties. In addition, the enableFailover property must be set to true. Any connection property value may be prefixed with %n (0<=n<10) to specify which failover configuration it applies to. Failover properties must be numbered starting at 0. Properties that are not prefixed with the failover prefix will apply to all configurations.

Example failover JDBC connection URLs:

jdbc:JSQLConnect://%0Server1%1Server2%2Server3/database=Master/user=sa

If the connection to 'Server1' fails, a connection will be attempted to 'Server2' and if that fails then to 'Server3'.

jdbc:JSQLConnect://%0Server1%1Server2/database=Master/user=%0User1%1User2

If the connection to 'Server1' fails, a connection will be attempted to 'Server2' with username 'User2'.

jdbc:JSQLConnect:/Server1:%01433%11533/database=Master/user=sa

If the connection to the database instance on port '1433' fails, a connection will be attempted on port '1533'.

jdbc:JSQLConnect://Server/database=%0Master%1Backup/user=sa

If the connection to database 'Master' fails, a connection will be attempted to database 'Backup'.

jdbc:JSQLConnect://%0Server1%1Server2/database=Master/user=%0sa%1User/password=%0%1sesame

A connection is attempted to 'Server1' using an empty password and then 'Server2' with the password 'sesame'.

In addition, failover syntax may be used to set any property of a datasource either directly or in resource configurations.

Examples:

Datasource.setServer("%0Server1%1Server2%2Server3");
<resourceProperties name="serverName" type="java.lang.String" value="%0Server1%1Server2">

Row Identifiers

SQL Server numeric IDENTITY columns can be used to ensure that each row in a table has a unique row identity (ROWID) that is generated by SQL Server.

For example:

CREATE TABLE AUTOKEYS (KEYCOLUMN INT IDENTITY, DATACOLUMN VARCHAR(10))

creates a table where SQL Server generates a ROWID value for column KEYCOLUMN.

It is a common requirement that the application be able to retrieve the ROWID for inserted rows. The following sections describe various techniques to retrieve the ROWID.

Use API to retrieve ROWID (not supported in JDBC 2.0):

Statement st = con.createStatement();
int updateCount = st.executeUpdate("INSERT INTO AUTOKEYS (DATACOLUMN) VALUES ('abc')", Statement.RETURN_GENERATED_KEYS);

if (updateCount > 0 ) { //The insert was successful
    ResultSet keyValues = st.getGeneratedKeys();
    keyValues.next();
    System.out.println("Inserted row identity is:" + keyValues.getInt(1));
}

Use SQL Server specific techniques:

Statement st = con.createStatement();
int updateCount = st.executeUpdate("INSERT INTO AUTOKEYS (DATACOLUMN) VALUES ('abc') SELECT @@IDENTITY"); //Insert the row and select the ROWID

if (updateCount > 0 ) { //The insert was successful
    if (st.getMoreResults()) { //Move to the ROWID result set
        ResultSet rs = st.getResultSet();
        rs.next();
        System.out.println("Inserted row identity is:" + rs.getInt(1));
    }
}

Bulk Load Feature

Bulk Load Feature allows the insertion of big data faster during batch execution because data is sent to SQL Server through the BULK INSERT statement. The bulk load support is set up transparently where no changes in the source code are required. Just use useBulkLoad=true in your connection URL. The Bulk Load Feature is supported by both java.sql.Statement and java.sql.PreparedStatement and is executed within a user-defined transaction, i.e. it can be rollbacked.

When should you use Bulk Load?

  • bulk load provides better performance in comparison with non-bulk insertions in case of a massive data insertion
  • bulk load provides a higher level in performance for a remote DB versus a local one
  • bulk load provides a significant increase in performance for types when binary representation is more compact than string. As an example, bulk load is about 4 times faster for bigdecimal type as compared with varchar

How to use Bulk Load Feature?

  • Add useBulkLoad=true to your connection url, e.g. jdbc:JSQLConnect://remoteHost/.../useBulkLoad=true
  • Use batch execution in your code. The simplest example
    for (int i = 0; i     statement.addBatch("INSERT INTO MyTable VALUES ('colValue')");
    }
    statement.executeBatch(); // here data will be sent via Bulk Load not via batch execution

Statement.executeBatch() behavior corresponds to "bulk insert", for example the Bulk Load Feature enforces strict data validation that could cause existing scripts to fail when they are executed on invalid data.

Loading Data From a CSV File

JSQLConnect provides support for loading data from a CSV file. To use this feature you should create an instance of the JSQLBulkLoad class as shown in the following example.

  • JSQLBulkLoad bulkLoad = new JSQLBulkLoad(connection, "table_to_insert");

Also you can specify the columns to which you want to load the data, field delimeter and batch size.

  • JSQLBulkLoad bulkLoad = new JSQLBulkLoad(connection, "table_to_insert(COL1, COL2, COL3)")
        .setFieldTerminator(',')
        .setSkipFirstRow(true) //generally the first column in CSV file is used for column names
        .setBatchSize(80);

Then, use the load() method, specifying the CSV file.

  • bulkLoad.load("data.csv");

Using Always Encrypted with the JDBC Driver

Always Encrypted allows clients to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server. An Always Encrypted enabled driver achieves this by transparently encrypting and decrypting sensitive data in the client application. The driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the values to SQL Server. Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results.

You can read more information about this feature from this article: Always Encrypted

Prerequisites

The easiest way to enable the encryption of parameters, and the decryption of query results targeting the encrypted columns, is by setting the value of the enableAlwaysEncrypted connection string keyword to true.

The following is an example of a connection string that enables Always Encrypted in the JDBC driver:

jdbc:sqlserver://localhost/user=USER/password=PASSWORD/databaseName=DATABASE/enableAlwaysEncrypted=true

Working with Column Master Key Stores

To encrypt a parameter value or to decrypt data in query results, Driver needs to obtain a column encryption key that is configured for the target column. Column encryption keys are stored in encrypted form in the database metadata. Each column encryption key has a corresponding column master key that was used to encrypt the column encryption key. The database metadata does not store the column master keys – it only contains the information about a key store containing a particular column master key and the location of the key in the key store.

To obtain a plaintext value of a column encryption key, Driver first obtains the metadata about both the column encryption key and its corresponding column master key, and then it uses the information in the metadata to contact the key store, containing the column master key, and to decrypt the encrypted column encryption key. The Driver communicates with a key store using a column master key store provider.

Windows Certificate Store Provider

The name of the provider is "MSSQL_CERTIFICATE_STORE". This provider can be used to store column master keys in the Windows Certificate Store. Use the SQL Server Management Studio (SSMS) Always Encrypted wizard or other supported tools to create the column master key and column encryption key definitions in the database. The same wizard can be used to generate a self signed certificate in the Windows Certificate Store that be used as a column master key for the always encrypted data. For more information on column master key and column encryption key T-SQL syntax visit CREATE COLUMN MASTER KEY and CREATE COLUMN ENCRPTION KEY respectively.

Custom Key Store Provider

Three new connection string keywords are introduced to allow a client aplication to declaratively specify the credentials the driver needs to authenticate to the Java Key Store. The driver would initialize the provider, based on the values of the following three properties of the connection string, for the specific connections.

keyStoreProvider: identifies which key store to seamlessly set up for the connection with Always Encrypted.

keyStoreLocation: Identifies the path to the keystore file that stores the column master key.

keyStorePassword: Identifies the password to use for the keystore as well as for the key.

The T-SQL syntax for creating the column master key is:

CREATE COLUMN MASTER KEY [CMK_name]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MY_CUSTOM_KEYSTORE',
    KEY_PATH = N'key_alias'
)
            

Here is an example on providing these credentials in connection string:

jdbc:sqlserver://localhost/user=USER/password=PASSWORD/databaseName=DATABASE/enableAlwaysEncrypted=true/keyStoreProvider=MY_CUSTOM_KEYSTORE/keyStoreLocation=PATH_TO_THE_KEYSTORE_FILE/keyStorePassword=KEY_STORE_PASSWORD

Limitations

JSQLConnect JDBC Driver have limitations to updating and inserting data for encrypted columns. There are supported data types on the targeting columns:

  • bit
  • tinyint
  • smallint
  • int
  • bigint
  • real
  • float
  • decimal
  • nvarchar(4000), nvarchar(max)
  • binary(max)
  • date
  • time(7)
  • datetime2(7)
  • datetimeoffset(7)

J2EE Database Connections

J2EE DataSources

JSQLConnect provides support for J2EE / JDBC data sources. The JSQLConnect datasource interface is implemented by class com.jnetdirect.jsql.JSQLDataSource. See the JSQLConnect detailed API documentation for a detailed description of each datasource and its properties.

Please also see the various datasource examples included with the distribution for example code.

Data Source Properties

All datasources support the ability to set and get any property that is associated with the underlying driver's property set. See JSQLConnect connection properties.

Examples:

setServerName("localhost")
setDatabaseName("MyDatabase")

The following shows how an application connects using a data source.

Context ctx = new InitialContext(System.getProperties()); //initialize JDNDI
DataSource ds = (DataSource) ctx.lookup("MyDataSource");
Connection c = ds.getConnection("user", "pwd");

J2EE Connection Pooling

JSQLConnect provides support for J2EE / JDBC connection pooling. This support is provided in two ways:

J2EE Application Server Pooling Implementations

JSQLConnect implements the JDBC required interfaces to enable JSQLConnect to participate in any middleware vendor's connection pooling implementation that is JDBC compliant. Middleware such as J2EE application servers often provide compliant connection pooling facilities - JSQLConnect will pool connections in these environments.

The classes for the connection pooling implementation are as follows:

JSQLConnect Class Implements Description
com.jnetdirect.jsql.JSQLConnectionPoolDataSource javax.sql.ConnectionPoolDataSource The class factory for PooledConnections. This class is a connection factory allowing the J2EE application server to populate its connection pool with physical connections. If your J2EE vendor's configuration requires a class implementing ConnectionPoolDataSource specify the class name com.jnetdirect.jsql.JSQLConnectionPoolDataSource

JSQLConnect Connection Pooling

JSQLConnect also provides its own connection pooling facility that is fully JDBC compliant. This is enables connection pooling for:

  • applications and applets that do not run within a J2EE container.
  • servlets, JSPs and EJBs that run within a J2EE container that does not support pooling.
  • any java application that requires the various management features of JSQLConnect connection pooling that are not offered by the container's built in pooling mechanism.

The implementation class is com.jnetdirect.jsql.JSQLPoolingDataSource.

Please see ExampleConnectionPool included with the distribution for example code.

JDBC application code should always close connections explicitly to derive the most benefit from pooling. When the application explicitly closes a connection the pooling implementation can reuse the connection immediately. If the connection is not closed other applications cannot reuse it. Applications can use the try/finally construct to make sure pooled connections are closed even if an exception occurs.

Distributed Transactions (XA Transactions)

JSQLConnect provides support for J2EE / JDBC Optional distributed transactions. JDBC connections obtained from a JSQLConnect XADataSource can participate in standard distributed transaction processing environments such as J2EE application servers.

The classes for the distributed transaction implementation are as follows:

JSQLConnect Class Implements Description
com.jnetdirect.jsql.JSQLXADataSource javax.sql.XADataSource The class factory for DistributedConnections (XAConnections).
com.jnetdirect.jsql.JSQLXAResource javax.transaction.xa.XAResource The transaction manager's resource adapter for the XAConnection.

XA Distributed transaction connections are also pooled connections.

Rowsets

JSQLConnect provides support for various types of JDBC connected rowsets.

JSQLConnect Rowsets are Java BeansT compliant. The classes for rowset support are as follows:

JSQLConnect Class Implements Description
com.jnetdirect.jsql.JSQLJDBCRowset Rowset JDBC Connected Rowsets.
com.jnetdirect.jsql.CachedRowSet CachedRowset JDBC Cached Rowsets with offline update and resynchronization.

Please see ExampleCachedRowset included with the distribution for example code.

XML Support

Introduction

XML (Extensible Markup Language) is rapidly becoming the standard format for transferring data between applications and organizations. Since both XML and JDBC are data-centric technologies it is important that JDBC and XML interoperate fully in enterprise applications. The following examples describe ways in which JSQLConnect JDBC and SQL can be used to process XML data in a SQL database.

The following examples require the new XML support provided by SQL. Please note that they will not operate with previous versions of SQL Server.

Retrieving XML Data

SQL queries may be executed to return results as XML rather than as standard rowsets. The following examples illustrate how data residing in tables may be retrieved in XML format.

Auto Mode

AUTO mode returns query results as nested XML elements. Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate attribute of the element. When the ELEMENTS option is specified, the table columns are mapped to subelements instead of attributes. By default, AUTO mode maps the table columns to XML attributes

Statement s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM xmlTestTable FOR XML AUTO");
rs.next();
System.out.println("+++" + rs.getString(1));

Result:

<XMLTESTTABLE numericColumn="1" characterColumn="data1" datetimeColumn="2001-01-01T00:00:00"/>
<XMLTESTTABLE numericColumn="2" characterColumn="data2" datetimeColumn="2001-01-02T00:00:00"/>
<XMLTESTTABLE numericColumn="3" characterColumn="data3" datetimeColumn="2001-01-03T00:00:00"/>

Raw Mode

RAW mode transforms each row in the query result set into an XML element with the generic identifier row. Each column value that is not NULL is mapped to an attribute of the XML element in which the attribute name is the same as the column name.

Statement s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM xmlTestTable FOR XML RAW");
rs.next();
System.out.println("+++" + rs.getString(1));

Result:

<row numericColumn="1" characterColumn="data1" datetimeColumn="2001-01-01T00:00:00"/>
<row numericColumn="2" characterColumn="data2" datetimeColumn="2001-01-02T00:00:00"/>
<row numericColumn="3" characterColumn="data3" datetimeColumn="2001-01-03T00:00:00"/>

Building a Resultset from an XML document

The following example shows how to build a JDBC result set from an XML document using SQL Server's sp_xml_preparedocument procedure.

/* Parse the XML document and select the required columns */

String exec = "DECLARE @_hDoc int " +
              "EXEC sp_xml_preparedocument @_hDoc OUTPUT, N'" +
              "<ROOT>" +
              "  <Customers CustomerID=\"Customer1\" ContactName=\"Joe\" CompanyName=\"Company1\">" +
              "    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-08-25T00:00:00\"/>" +
              "    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-10-03T00:00:00\"/>" +
              "  </Customers>" +
              "  <Customers CustomerID=\"Customer2\" ContactName=\"David\" CompanyName=\"Company2\">" +
              "    <Orders CustomerID=\"Customer2\" OrderDate=\"2000-09-23T00:00:00\"/>" +
              "  </Customers>" +
              "</ROOT>'" +
              "SELECT * FROM OPENXML(@_hDoc, N'/ROOT/Customers/Orders') with (CustomerID nchar(10) '../@_CustomerID', OrderDate datetime)" +
              "EXEC sp_xml_removedocument @_hdoc";

/* Retrieve and display the result set */

s = s.executeQuery(exec);
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
    for (int i = 0; i < md.getColumnCount(); i++) {
        System.out.print(rs.getString(i + 1) + " ");
    }
    System.out.println();
}

Result:

Customer1 2000-08-25 00:00:00.0
Customer1 2000-10-03 00:00:00.0
Customer2 2000-09-23 00:00:00.0

Writing XML Data

The following example illustrates how write table rows from an XML document.

Assume we have created this table:

CREATE TABLE Customers (CustomerID varchar(10), ContactName varchar(10))

The following code inserts the customers from the XML document into table Customers.

/* Parse the XML document and select the required columns */

String exec = "DECLARE @_hDoc int "+
              "EXEC sp_xml_preparedocument @_hDoc OUTPUT, N'"+
              "<ROOT>" +
              "  <Customers CustomerID=\"Customer1\" ContactName=\"Joe\" CompanyName=\"Company1\">" +
              "    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-08-25T00:00:00\"/>" +
              "    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-10-03T00:00:00\"/>" +
              "  </Customers>" +
              "  <Customers CustomerID=\"Customer2\" ContactName=\"David\" CompanyName=\"Company2\">" +
              "    <Orders CustomerID=\"Customer2\" OrderDate=\"2000-09-23T00:00:00\"/>" +
              "  </Customers>" +
              "</ROOT>'" +
              "INSERT Customers SELECT * FROM OPENXML(@_hDoc, N'/ROOT/Customers') WITH Customers "+
              "EXEC sp_xml_removedocument @_hDoc";

/* Insert the rows into the table */

int nRowsInserted = s.executeUpdate(exec);
System.out.println("Rows inserted = " + nRowsInserted);

Results:

Rows inserted = 2