JSQLConnect™ Support

Technical Reference

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.
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.
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:
DataSource
Class Name:
com.jnetdirect.jsql.JSQLDataSource
Description:
The non pooling datasource.
DataSource Type:
PoolingDataSource
Class Name:
com.jnetdirect.jsql.JSQLPoolingDataSource
Description:
The connection pooling datasource. Typically used when the application does not run within a J2EE application server.
DataSource Type:
ConnectionPoolDataSource
Class Name:
com.jnetdirect.jsql.JSQLConnectionPoolDataSource
Description:
The datasource to configure J2EE application server connection pools. Typically used when the application runs within a J2EE application server.
DataSource Type:
XADataSource
Class Name:
com.jnetdirect.jsql.JSQLXADataSource
Description:
The datasource to configure J2EE XA datasources. Typically used when the application runs within a J2EE application server and an XA transaction manager.
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.
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 &.
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
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
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
US-ASCII
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.
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
0
The number of seconds the driver should wait before timing out a failed connection.
maxResultsSetMemorySize
Integer
0
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.
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.
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
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.
This section provides detailed documentation for each JSQLConnect datasource method.
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.
JDBC Datatypes by Category:
Category
Datatypes
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.
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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 Strings
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
NULL
All Data Types except rowversion
Exact Numerics
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
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
JDBC Type
Default Conversion
Conversion Supported
Data-Dependent
SQLXML
xml
Character Strings
 
Unicode Character Strings
sql_variant
JAVA_OBJECT
 
Exact Numerics
 
Approximate Numerics
 
Binary Strings
 
Other Data Types except rowversion
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.
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
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.
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.
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.
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.
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.
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);
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
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.
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.
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.
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:
TYPE_FORWARD_ONLY
SQL Server Cursor Type:
Fast Forward Only
Server Load:
Light
Characteristics:
Forward Only, Read Only
Application Requirements:
Fast, Access all data, Multiple result sets.
JDBC Type:
TYPE_SCROLL_INSENSITIVE
SQL Server Cursor Type:
Static Cursor
Server Load:
Heavy
Characteristics:
Other user’s updates are not reflected.
Application Requirements:
Application needs a database snapshot.
JDBC Type:
TYPE_SCROLL_SENSITIVE
SQL Server Cursor Type:
Keyset Cursor
Server Load:
Medium
Characteristics:
Other user’s updates are reflected, row membership fixed.
Application Requirements:
Application needs to see changed data for existing rows only.
JDBC Type:
TYPE_SCROLL_SENSITIVE+1
SQL Server Cursor Type:
Dynamic
Server Load:
Heavy
Characteristics:
Other user’s updates are reflected, row membership reflects other’s inserts and deletes.
Application Requirements:
Application needs to see changed data for existing rows as well as inserted and deleted rows for lifetime of cursor.
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:
CONCUR_READ_ONLY
Characteristics:
Read Only
Row Locks:
No
Application Requirements:
Application requires only read-only access to rows.
JDBC Concurrency:
CONCUR_UPDATABLE
Characteristics:
Optimistic Read Write
Row Locks:
No
Application Requirements:
Database assumes row contention is unlikely but possible. Row integrity checked with a timestamp comparison.
JDBC Concurrency:
CONCUR_UPDATABLE+1
Characteristics:
Pessimistic Read Write
Row Locks:
Yes
Application Requirements:
Database assumes row contention is likely. Row integrity is ensured with row locking.
JDBC Concurrency:
CONCUR_UPDATABLE+2
Characteristics:
Optimistic Read Write
Row Locks:
No
Application Requirements:
Database assumes row contention is unlikely but possible. Row integrity checked with a row data comparison.
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:
Statement is created with TYPE_SCROLL_INSENSITIVE
Description:
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.
Remedy:
Use TYPE_SCROLL_SENSITIVE(n) to avoid creating a static cursor.
Cause:
Table design precludes a KEYSET cursor
Description:
The underlying table does not have unique keys to enable SQL Server to uniquely identify a row.
Remedy:
Add unique keys to the table to provide unique identification of each row.
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.
JSQLConnect supports all SQL Server isolation levels. The default is Read Committed.
Isolation Level
Dirty Read
Non-Repeateable 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.
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:
Connection c = <new connection>
Statement s = c.createStatement(..);
s.execute(..)
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(..);
}
}
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.
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.
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.
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.
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");
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 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
<resourceProperties name="serverName" type="java.lang.String" value="%0Server1%1Server2">
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 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.
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");
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.
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)
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.
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");
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 Class:
com.jnetdirect.jsql.JSQLConnectionPoolDataSource
Implements:
javax.sql.ConnectionPoolDataSource
Description:
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.
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.
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:
JSQLConnect Class:
com.jnetdirect.jsql.JSQLXADataSource
Implements:
javax.sql.XADataSource
Description:
The class factory for DistributedConnections (XAConnections).
JSQLConnect Class:
com.jnetdirect.jsql.JSQLXAResource
Implements:
javax.transaction.xa.XAResource
Description:
The transaction manager’s resource adapter for the XAConnection.
XA Distributed transaction connections are also pooled connections.
JSQLConnect provides support for various types of JDBC connected rowsets.
JSQLConnect Rowsets are JavaBeans compliant. The classes for rowset support follow:
JSQLConnect Class:
com.jnetdirect.jsql.JSQLJDBCRowset
Implements:
javax.sql.rowset.JdbcRowSet
Description:
JDBC Connected Rowsets.
JSQLConnect Class:
com.jnetdirect.jsql.CachedRowSet
Implements:
javax.sql.rowset.CachedRowSet
Description:
JDBC Cached Rowsets with offline update and resynchronization.
Please see ExampleCachedRowset included with the distribution for example code.
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.
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"/>
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
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