JNet Direct | Provides Expert Developers with Reliable, High Performance, and Secure Software Components and Tools Used in Thousands of Applications

JSQLConnect™ Support

Technical Reference

JSQLConnect Features

Technical Reference

Using JSQLConnect

Technical Support

Release Notes

General

Quick Start

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

Change directory to the folder where you installed JSQLConnect, then change directory to the examples/classes directory.
Copy the driver JAR file (JSQLConnect.jar) from your distribution to this directory.
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

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

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.

Setting the value of the trustedAuthentication connection property to True 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.

Azure Active Directory Authentication

Azure Active Directory (AAD) authentication, which is a mechanism of connecting to Azure SQL Database v12 using identities in Azure Active Directory. Use Azure Active Directory authentication to centrally manage identities of database users and as an alternative to SQL Server authentication. The JDBC Driver allows you to specify your Azure Active Directory credentials in the JDBC connection string to connect to Azure SQL DB.

When using Azure Active Directory Authentication mode, you must provide a SQL authentication method via connection property activeDirectoryAuthentication and other data that dependent on authentication method.

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).

activeDirectoryAuthentication

String
[ password ]

null

Set to password to connect to an Azure SQL Database/Data Warehouse using an Azure AD principal name and password.

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 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

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

US-ASCII

The codepage for translating single byte column data.

connectionRetryCount

Integer

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.

enablePrepareOnFirst-
-PreparedStatementCall

Boolean

true

When true, a statement is prepared (sp_prepexe + sp_exec) on first use (better for multiple executions of the same statement, but first execution is slower). When false (disabled), a statement is executed via sp_executesql the first time, and is only prepared on second execution (better when each statement is executed only once). This property might negatively affect performance when used together with disableStatementPooling.

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

The number of seconds the driver should wait before timing out a failed connection.

maxResultsSetMemorySize

Integer

Maximum memory size in bytes used by the ResultSet. The ResultSet exceeding the limit will be stored in TEMP system folder on the hard drive. The target folder can be specified using the resultSetFileDir option.

maxSizePreparedStatementsPool

Integer

100

The maximum number of opened handles for prepared statements. When the number will be exceeded, the oldest handle will be closed via sp_unprepare command. It is the long-term affect on memory resources, if sp_unprepare isn’t called.

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.

resultSetFileDir

String

null

The folder for storing ResultSet that exceed the memory limit set by the maxResultSetMemorySize option.

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. When set to auto, the driver will use SSL if the database instance supports it. If mandatory, the driver will always use SSL for this connection (and therefore the database instance must support SSL). Use the property to establish a connection with Windows Azure.

sslTrusted

Boolean

false

Set to true to specify that the database instance’s certificate must be trusted by the java runtime. This property 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.

useDateTime2

Boolean

true

If set to true driver will send parameters as DATETIME2 type. Otherwise parameters will be send as DATETIME type.

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, it 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

JDBC Datatypes by Category:

Exact Numerics

bit, tinyint, smallint, int, bigint, decimal, numeric, smallmoney, money

Approximate Numerics

real, float

Date and Time

smalldatetime, datetime, date, time, datetime2, datetimeoffset

Character Strings

char, varchar, text

Unicode Character Strings

nchar, nvarchar, ntext

Binary Strings

binary, varbinary, image

Other Data Types

xml, uniqueidentifier, sql_variant, hierarchyid, rowversion

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

Default Conversion: Type used by default, for example in the getObject() method.
Conversion Supported: Type can be converted without errors or loss of data.
Data-Dependent: 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 charts contain the JDBC driver’s conversion maps for the get<Type>() methods of the ResultSet class, and the supported conversions for the get<Type>() methods of the CallableStatement class.

Exact Numerics

BIT

bit

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

BOOLEAN

bit

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

TINYINT

tinyint

bit

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

SMALLINT

smallint

bit, tinyint

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

INTEGER

int

bit, tinyint, smallint

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

BIGINT

bigint

bit, tinyint, smallint, int

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

DECIMAL

decimal, numeric, smallmoney, money

bit, tinyint, smallint, int, bigint

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

NUMERIC

decimal, numeric, smallmoney, money

bit, tinyint, smallint, int, bigint

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

Approximate Numerics

REAL

real

bit, tinyint, smallint

int, bigint, decimal, numeric, smallmoney, money

float

Character Strings

Unicode Character Strings

sql_variant

FLOAT

float

bit, tinyint, smallint, int, smallmoney, real

bigint, decimal, numeric, money

Character Strings

Unicode Character Strings

sql_variant

DOUBLE

float

bit, tinyint, smallint, int, smallmoney, real

bigint, decimal, numeric, money

Character Strings

Unicode Character Strings

sql_variant

Date and Time

DATE

date

smalldatetime, datetime, datetime2, datetimeoffset

Character Strings

Unicode Character Strings

sql_variant

TIME

time

smalldatetime, datetime, datetime2, datetimeoffset

Character Strings

Unicode Character Strings

sql_variant

TIMESTAMP

smalldatetime, datetime, datetime2, datetimeoffset

date, time

Character Strings

Unicode Character Strings

sql_variant

Character Strings

CHAR

char, uniqueidentifier

Exact Numerics

Approximate Numerics

Date and Time

varchar, text

Unicode Character Strings

xml, sql_variant, hierarchyid

Binary Strings

VARCHAR

varchar, uniqueidentifier

Exact Numerics

Approximate Numerics

Date and Time

char, text

Unicode Character Strings

xml, sql_variant, hierarchyid

Binary Strings

LONGVARCHAR

varchar, text

Exact Numerics

Approximate Numerics

Date and Time

char

Unicode Character Strings

xml, uniqueidentifier, sql_variant, hierarchyid

Binary Strings

CLOB

Exact Numerics

Approximate Numerics

Date and Time

char, text

Unicode Character Strings

xml, sql_variant, hierarchyid

Binary Strings

Unicode Character Strings

NCHAR

nchar, uniqueidentifier

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

nvarchar, ntext

xml, sql_variant, hierarchyid

Binary Strings

NVARCHAR

nvarchar, uniqueidentifier

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

nchar, ntext

xml, sql_variant, hierarchyid

Binary Strings

LONGNVARCHAR

nvarchar, ntext

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

nchar

xml, uniqueidentifier, sql_variant, hierarchyid

Binary Strings

NCLOB

N/A

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

Unicode Character Strings

xml, uniqueidentifier, sql_variant, hierarchyid

Binary Strings

BINARY

binary, hierarchyid

Exact Numerics

Character Strings

Unicode Character Strings

varbinary, image

xml, uniqueidentifier

sql_variant

VARBINARY

varbinary, hierarchyid

Exact Numerics

Character Strings

Unicode Character Strings

binary, image

xml, uniqueidentifier

sql_variant

LONGVARBINARY

varbinary, image

Exact Numerics

Character Strings

Unicode Character Strings

binary

xml, uniqueidentifier, hierarchyid

sql_variant

BLOB

Exact Numerics

Character Strings

Unicode Character Strings

Binary Strings

xml, uniqueidentifier, hierarchyid

sql_variant

Other Data Types

SQLXML

xml

Character Strings

Unicode Character Strings

JAVA_OBJECT

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

Unicode Character Strings

Binary Strings

Other Data Types

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.

NULL

NULL

All Data Types except rowversion

Exact Numerics

BIT

bit

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

BOOLEAN

bit

All other Exact Numerics

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

sql_variant

TINYINT

tinyint

All other Exact Numerics except bit

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

bit

sql_variant

SMALLINT

smallint

All other Exact Numerics except bit, tinyint

Approximate Numerics

Character Strings

Unicode Character Strings

Binary Strings

bit, tinyint

sql_variant

INTEGER

int

bigint, decimal, numeric, money

float

Character Strings

Unicode Character Strings

Binary Strings

bit, tinyint, smallint, smallmoney

real

sql_variant

BIGINT

bigint

decimal, numeric

Character Strings

Unicode Character Strings

Binary Strings

bit, tinyint, smallint, int, smallmoney, money

real, float

sql_variant

DECIMAL

decimal, numeric, smallmoney, money

Character Strings

Unicode Character Strings

Binary Strings

bit, tinyint, smallint, int, bigint

real, float

sql_variant

NUMERIC

decimal, numeric, smallmoney, money

Character Strings

Unicode Character Strings

Binary Strings

bit, tinyint, smallint, int, bigint

real, float

sql_variant

Approximate Numerics

REAL

real

float

Character Strings

Unicode Character Strings

All Exact Numerics

sql_variant

FLOAT

float

Character Strings

Unicode Character Strings

Exact Numerics

real

sql_variant

DOUBLE

float

Character Strings

Unicode Character Strings

Exact Numerics

real

sql_variant

Date and Time

DATE

date

datetime2, datetimeoffset

Character Strings

Unicode Character Strings

smalldatetime, datetime

sql_variant

TIME

time

datetime2, datetimeoffset

Character Strings

Unicode Character Strings

smalldatetime, datetime

sql_variant

TIMESTAMP

datetime2, datetimeoffset

Character Strings

Unicode Character Strings

smalldatetime, datetime, date, time

sql_variant

Character Strings

CHAR

char

varchar, text

Unicode Character Strings

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

VARCHAR

varchar

char, text

Unicode Character Strings

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

LONGVARCHAR

varchar

char, text

Unicode Character Strings

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

CLOB

varchar

char, text

Unicode Character Strings

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

Unicode Character Strings

NCHAR

nchar

Character Strings

nvarchar, ntext

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

NVARCHAR

nvarchar

Character Strings

nchar, ntext

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

LONGNVARCHAR

nvarchar

Character Strings

nchar, ntext

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

NCLOB

nvarchar

Character Strings

nchar, ntext

Exact Numerics

Approximate Numerics

Date and Time

Binary Strings

Other Data Types except rowversion

Binary Strings

BINARY

binary

Character Strings

Unicode Character Strings

varbinary, image

Exact Numerics

Other Data Types except rowversion

VARBINARY

varbinary

Character Strings

Unicode Character Strings

binary, image

Exact Numerics

Other Data Types except rowversion

LONGVARBINARY

varbinary

Character Strings

Unicode Character Strings

binary, image

Exact Numerics

Other Data Types except rowversion

BLOB

varbinary

Character Strings

Unicode Character Strings

binary, image

Exact Numerics

Other Data Types except rowversion

Other Data Types

SQLXML

xml

Character Strings

Unicode Character Strings

sql_variant

JAVA_OBJECT

Exact Numerics

Approximate Numerics

Binary Strings

Other Data Types except rowversion

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 and all support double byte characters.

These 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 N prefix:

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.

As an example, to specify the Greek codepage, use the following connection URL:

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

JDBC Exception 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 Troubleshooting

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

PLEASE NOTE: 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.

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.

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 a 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 configuring the logging.properties file:

# "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:

Call the Driver methods getMajorVersion() and getMinorVersion().
Call the DatabaseMetaData method getDriverVersion() or methods getDriverMajorVersion() and getDriverMinorVersion().
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:

Navigate to the MachineName directory of your JSQLConnect installation.
Use Java to execute the MachineName.class program with the command:

java MachineName
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.

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 are 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 needs to see changed data for existing rows as well as inserted and deleted rows for 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.

CONCUR_READ_ONLY

Read Only

Application requires only read-only access to rows.

CONCUR_UPDATABLE

Optimistic Read Write

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

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”.

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.

Read uncommitted

Yes

Read committed

Yes

Repeatable read

Yes

Snapshot

Sometimes possible

Serializable

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

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 JSQLXADataSource datasource 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. See the following for examples of instance-specific connections.

Using a JDBC URL:

jdbc:JSQLConnect://localhost\\instance1/user=sa/<additional props>

Using a named property:

jdbc:JSQLConnect://remoteHost/instanceName=instance1/user=sa/<additional props>

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 be 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. See the following for failover connection examples.

Multi-server failover JDBC connection URL. If the connection to ‘Server1’ fails, a connection will be attempted to ‘Server2’. If that fails, ‘Server3’ will be used.

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

Failover JDBC connection URL with alternative username. If the connection to ‘Server1’ fails, a connection will be attempted to ‘Server2’ with username ‘User2’.

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

Failover JDBC connection URL with alternative port. If the connection to the database instance on port ‘1433’ fails, a connection will be attempted on port ‘1533’.

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

Failover JDBC connection URL with alternative database. If the connection to database ‘Master’ fails, a connection will be attempted to database ‘Backup’.

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

Failover JDBC connection URL with alternative password. A connection is attempted to ‘Server1’ using no password, and then ‘Server2’ with the password ‘sesame’.

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

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

Datasource.setServer("%0Server1%1Server2%2Server3");

And

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.

As an example, this statement will create a table in which SQL Server generates a ROWID value for column KEYCOLUMN:

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

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 the 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 the row and select the ROWID
"INSERT INTO AUTOKEYS (DATACOLUMN) VALUES ('abc') SELECT @@IDENTITY"
);

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. As a simple example:

for (int i = 0; i < 10; i++) {
statement.addBatch("INSERT INTO MyTable VALUES ('colValue')");
}

// here data will be sent via Bulk Load not via batch execution
statement.executeBatch();

Statement.executeBatch() behavior corresponds to “bulk insert.” As such, it 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(',')
//generally the first column in CSV file is used for column names
.setSkipFirstRow(true)
.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 here.

Prerequisites

Configure Always Encrypted in your database. This involves provisioning Always Encrypted keys and setting up encryption for selected database columns.
Download and install the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files for your Java version. Be sure to read the Readme included in the zip file for installation instructions and pertinent details on possible export/import issues.

Policy files for Java 6
Policy files for Java 7
Policy files for Java 8
JDK 9 and later ship with, and use by default, unlimited policy files.

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/.../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 use 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 of providing these credentials in a connection string:

jdbc:.../keyStoreProvider=KEYSTORE/keyStoreLocation=PATH_TO_KEYSTORE_FILE/keyStorePassword=KEYSTORE_PW

Limitations

JSQLConnect JDBC Driver has some limitations when updating and inserting data for encrypted columns. Only the following data types are supported:

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.

DataSource 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 code snippet shows how an application might connect using a datasource:

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.

Connection pooling is provided via the following class:

JSQLConnect Connection Pooling

JSQLConnect also provides its own connection pooling facility that is fully JDBC compliant. This allows 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 that provide the distributed transaction implementation are:

XA Distributed transaction connections are also pooled connections.

Rowsets

JSQLConnect provides support for various types of JDBC connected rowsets.

JSQLConnect Rowsets are JavaBeans compliant. The classes for rowset support follow:

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:

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:

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\">" +
"    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-08-25T00:00:00\"/>" +
"    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-10-03T00:00:00\"/>" +
"  </Customers>" +
"  <Customers CustomerID=\"Customer2\" ContactName=\"David\">" +
"    <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\">" +
"    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-08-25T00:00:00\"/>" +
"    <Orders CustomerID=\"Customer1\" OrderDate=\"2000-10-03T00:00:00\"/>" +
"  </Customers>" +
"  <Customers CustomerID=\"Customer2\" ContactName=\"David\">" +
"    <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

Subscribe to our Newsletter

Contact Us

12110 Sunset Hills Road, Suite 600

Reston, VA 20190

(703) 463-2434

(703) 935 4096

P.O. Box 404 Herndon, VA 20170

JSQLConnect™ Support

Technical Reference

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

Unicode Character Strings

Binary Strings

Other Data Types

NULL

Exact Numerics

Approximate Numerics

Date and Time

Character Strings

Unicode Character Strings

Binary Strings

Other Data Types

Subscribe to our Newsletter

Contact Us

Send Us a Message