| JSQLConnect Technical Reference |
Last Update 26 Jun, 2009
|
General
J2EE Database Connections
J2EE Servlet/JSP Tomcat Setup Using JSQLConnect in
Vendor IDE's and Application Servers This section gives you quick start instructions for the example application. You need the 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. 2. 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.
Note that on Window's 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 2.0 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.
JSQLConnect supports SQL Server authentication in the following two modes. Windows Authentication Mode (Trusted Authentication) - Windows Authentication mode allows a user to connect through a Microsoft Windows Windows® 2000 or NT® 4.0 user account. Trusted Authentication means SQL Server achieves login security integration with Windows NT 4.0 or Windows 2000 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 NT 4.0 or Windows 2000 platforms. When installing SQL Server you may specify either Windows Authentication Mode or mixed mode. Trusted authentication is specified using the 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. The components required to use trusted authentication with JSQLConnect are:-
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. NTLM Authentication - Authentication is provided by
supplying 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. Building the Connection URL jdbc:JSQLConnect://serverName:port/property=value[/property=value] where :-
Connect to the local machine to the default database with username 'sa'. Connect to a named database on a remote server. Connect on the non-default port 4000 to the remote server. Connect to a remote database using trusted authentication. Connect to a 6.5 SQL Server. Connect specifying a customized application name. The following driver properties may be specified in various ways:- 1. As name=value properties in the connection URL when connecting
with Driver Manager.
Setting DataSource Properties in J2EE Configurations 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. Making the Database Connection The examples in the JSQLConnect distribution illustrate various methods of making database connections. For DriverManager connections please see see the ExampleApplication.java and for datasource connections see ExampleDataSource.java. If you are connecting from an applet its 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 Detailed API Documentation 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 JSQLConnectOrginal.jar is the original licensed jar you received from NetDirect and JSQLConnect.jar is the new jar that needs to have the license installed in it. JAR is the Java archive tool included in Javasoft's 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). JSQLConnect Versions 3.2717, 2.2717 or after rem Extract the original license using JAR
Finally, redeploy JSQLConnect.jar back to your application environment. Verify that no trial license message is displayed by the driver. Supported DataType Conversions This matrix describes the combination of SQL Server and JDBC datatypes
supported. The entire set of SQL Server data types is listed across the
top. The set of JDBC data type calls is listed vertically.
D indicates that this is the default data type mapping. C indicates that the datatype conversion is supported. Since release 7.0, SQL Server provides the new datatypes NCHAR, NVARCHAR and NTEXT. These column are designed for the storage of Unicode data they all support double byte characters. These new datatypes are supported by JSQLConnect since the driver automatically handles the UTF conversions from the Java environment to the SQL Server environment. NCHAR and NVARCHAR can store a maximum of 4000 characters. NTEXT stores a maximum of 1,073,741,823 characters. Note that to insert literal Unicode data into a Unicode column you require SQL Server's national prefix 'N'. Eg 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. Example to use the Greek codepage:
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. JSQLConnect requires that TCP/IP be installed and running for your SQL Server database. Use SQL Server Server Network Utility to verify which network library protocols are installed. A database connection attempt may fail for many reasons. Here is some typical ones :- TCP/IP is not enabled for SQL Server or the server or port number specified is incorrect. Check that SQL Server is listening with TCP/IP on the specified servern and port. This may be reported with an exception similar to "Connection refused: no further information". This indicates that either :- 1. SQL Server is installed but TCP/P has not been installed as a
network protocol for SQL Server using SQL Server Server Network Utility. You can check TCP/IP connectivity to SQL Server using telnet. For example, from a command prompt key in : telnet 123.456.123.456 1433 where 123.456.123.456 is the address of the server running SQL Server and 1433 is the port it is listening on. If you get a message saying Telnet cannot connect then TCP/IP is not listening on that port for SQL Server connections. Use SQL Server Server Network Utility to ensure SQL Server is configured to use TCP/IP on port 1433. The specified database is incorrect. Ensure you are logging into an existing SQL Server database or schema The username or password is incorrect. Ensure you have the correct values When using SQL Server authentication, JSQLConnect requires that SQL Server is installed with SQL Server Authentication (not Windows NT authentication). Ensure this option is included when installing or configuring your SQL Sever installation. Tracing Driver Operation and Trouble Shooting To debug and resolve support issues you can enable tracing in the operation of the driver. NB 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 NetDirect technical support (zipping the file if it is large) along with the version of MS SQL Server in use. We recommend JSQLConnect's custom tracing feature be used since it traces driver activity across multiple connections. Enable tracing using the driver URL Use the logfile setting either as a connection option or property. Examples:- In the connection URL - jdbc:JSQLConnect://localhost/database=Master/user=sa/logfile=MyLog.txt If you require a directory in the log file name please use the properties alternative below as directory delimiters may be invalid in the URL. Enable tracing using the connection properties Properties props = new Properties(); Enable tracing with DriverManager
PrintWriter pw = new PrintWriter(new FileOutputStream(
"\someDirectory\MyLog.txt")); Enable JDBC Tracing with a Datasource Set the log file using either the setLogfile() or setLogWriter() methods on the datasource. setLogFile("\someDirectory\MyLog.txt"); or setLogWriter(new PrintWriter(new FileOutputStream("\someDirectory\MyLog.txt"));); The version of the installed JSQLConnect driver may be found in 3 ways:- 1. Call the DatabaseMetaData methods getDriverMajorVersion() and
getDriverMinorVersion(). 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. 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.
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.
Guaranteeing an Updateable Result Set An updateable result set (JDBC 2.0) 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".
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.
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
Multiple Threads Using a Single Connection
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.
JSQLConnect fully supports local transactions via the following JDBC APIs :- Connection.setAutoCommit(), Connection.commit(), Connection.rollback(). See Sun's standard JDBC API specifications for the details of these APIs. Local transactions are typically managed explicitly by the application or automatically by the J2EE application server. Distributed Transactions Using Microsoft® Distributed Transaction Coordinator A distributed transaction is a transaction that updates data on two or more networked databases while retaining the important atomic, consistent, isolated, and durable properties of transaction processing. Distributed transaction support was added to the JDBC API in the JDBC 2.0 Optional API specification. The management of distributed transactions is typically performed automatically by the Java Transaction Service (JTS) transaction manager within a J2EE application server environment. However, JSQLConnect will support distributed transactions under any Java Transaction API (JTA) compliant transaction manager. JSQLConnect now seamlessly integrates with Microsoft® Distributed Transaction Coordinator (MS-DTC) to provide true distributed transaction support with MS SQL Server 2000 and 7.0. 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. For more information on MS-DTC please visit Microsoft Distributed Transaction Coordinator. MS-DTC must be installed and running on your system if MS-DTC support is enabled in JSQLConnect. MS-DTC can be installed along with SQL Server or standalone. The MS-DTC service should be marked 'automatic' in service manager to ensure its running when the server is booted. To enable MS-DTC integration you need to set the 'dtcEnabled' property of the JSQLXADataSource. For example, setDtcEnabled(true). Be default JSQLXADataSource connections operate using local transactions during distributed transaction processing. For more information on the properties that control JSQLConect/MS-DTC integration please see the documentation for the datasource JSQLXADataSource in the datasources API documentation. Installation of JSQLConnect/MS_DTC Interface The components are included in the XAInstall directory of your JSQLConnect installation. Please view the readme.txt file there to carry out the installation of these components. Named and Multiple SQL 2000 Instances SQL Server 2000 allows the installation of multiple database instances per server. Each instance is identified by a specific name. E.G. instance1. To connect to a named instance of SQL Server you must specify the instance name as a JDBC URL property or a datasource property. If no instance name property is specified a connection to the default instance is created. Examples Using a JDBC URL jdbc:JSQLConnect://localhost\\instance1/user=sa/<more properties as required> or using a named property:- jdbc:JSQLConnect://remoteHost/instanceName=instance1/user=sa/<more properties as required> Using a datasource DataSource.setInstanceName("instance1"); In many secure applications security by provided by SSL may be 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's 1.4 and higher. For older JRE's the JSSE package must be downloaded from Sun and installed. The ssl connection property can set set to "auto" or "mandatory" :- "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's certificate must be trusted by JSSE. (i.e. defined in the JSSE
truststore). If "sslTrusted" is false no check is made that the database's
instance's 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 support is supported for both Driver Manager and Datasource database connections. Failover configurations are specified by using a specific syntax within connection properties. In addition the 'enableFailover' property must be set to 'true'. Any connection property may be prefixed with '%n' (0<=n<10) to specify which failover configuration it applies to. Failover properties must be numbered starting at 0. Properties that are not prefixed with the failover prefix will apply to all configurations. Example failover JDBC connection URLs :- jdbc:JSQLConnect://%0Server1%1Server2%2Server3/database=Master/user=sa If the connection to 'Server1' fails, a connection will be attempted to 'Server2' and if that fails then to 'Server3'. jdbc:JSQLConnect://%0Server1%1Server2/database=Master/user=%0User1%1User2 If the connection to 'Server1' fails, a connection will be attempted to 'Server2' with username 'User2'. jdbc:JSQLConnect:/Server1:%01433%11533/database=Master/user=sa If the connection to the database instance on port 1433 fails, a connection will be attempted on port 1533. jdbc:JSQLConnect://Server/database=%0Master%1Backup/user=sa If the connection to database 'Master' fails, a connection will be attempted to database 'Backup'. jdbc:JSQLConnect://%0Server1%1Server2/database=Master/user=%0sa%1User/password=%0%1sesame A connection is attempted to Server1 using an empty password and then Server2 with the password 'sesame' In addition, failover syntax may be used to set any property of a datasource either directly or in resource configurations. Examples Datasource.setServer("%0Server1%1Server2%2Server3"); <resourceProperties name="serverName" type="java.lang.String" value="%0Server1%1Server2"> The use of SQL Server numeric IDENTITY columns can be used to ensure that each row in a table has a unique row identity (ROWID) that is generated by SQL Server. For example :- CREATE TABLE AUTOKEYS (KEYCOLUMN INT IDENTITY, DATACOLUMN VARCHAR(10)) creates a table where SQL Server generates a ROWID value for column KEYCOLUMN. It is a common requirement that the application be able to retrieve the ROWID's for inserted rows. The following sections describe various techniques to retrieve the ROWID. JSQLConnect 3.0 (JDBC 3.0) JDBC 3.0 supports a number of API's to retrieve ROWID. ROWID support in JDBC 3.0 is database independent which ensures that your code will operate without change on any JDBC 3.0 driver. See the JDBC 3.0 specification for a detailed description of the various ROWID APIs. Here is a JDBC 3.0 example:- Statement st = con.createStatement(); JSQLConnect 2.0 (JDBC 2.0) JDBC 2.0 does not support APIs for ROWID retrieval. The workaround requires SQL Server specific techniques. For example:- Statement st = con.createStatement(); The following sections document the support for the J2EE and JDBC 2.0 Optional API features. The source code examples provided in the product distribution provide a good reference for getting started with the these features. First, ensure your Java environment (JDK, JRE) includes the
javax.sql package. This is a required package for any JDBC application
using the Optional API. Recent JDK JDK 1.4 and higher already contain this
package so there is no need to install it separately. If the javax.sql package
is not included with your JVM download and install the JDBC 2.0 Optional
Package from Javasoft. The classes are bundled into file
jdbc2_0-stdext.jar - make sure it is included in your classpath. Some of NetDirect's Optional API examples use Javasoft's File System JNDI provider. If you will modify the examples to operate with a JNDI different provider and already have that provider setup then you can skip this section. This section can be used to create a simple file system based JNDI provider to get started with the Optional API features. Download the File System JNDI service provider from Javasoft. Make sure the File System provider JARs are in the classpath. The required JNDI downloads are found at http://www.javasoft.com/products/jndi/index.html Introduction JSQLConnect is a J2EE certified product and provides full support for J2EE and the JDBC 2.0 Optional API. For general information on using the Optional API we suggest the Javasoft documentation at Javasoft documentation for general details on how to use the JDBC 2.0 Optional APIs. Javasoft also provides a good tutorial covering the features at http://developer.java.sun.com/developer/Books/JDBCTutorial/ JSQLConnect provides support for J2EE / JDBC 2.0 data sources. The JSQLConnect datasource interface is implemented by class com.jnetdirect.jsql.JSQLDataSource. See the JSQLConnect detailed API documentation for a detailed description of each datasource and its properties. Please also see the various datasource examples included with the distribution for example code. Data Source Properties All datasources support the ability to set and get any property that is associated with the underlying driver's property set. See JSQLConnect connection properties. Examples: setServerName("localhost") The following shows how an application connects using a data source.
JSQLConnect provides support for J2EE / JDBC 2.0 connection pooling. This support is provided in two ways :- J2EE Application Server Pooling Implementations JSQLConnect implements the JDBC 2.0 required interfaces to enable JSQLConnect to participate in any middleware vendor's connection pooling implementation that is JDBC 2.0 compliant. Middleware such as J2EE application servers often provide compliant connection pooling facilities - JSQLConnect will pool connections in these environments. The classes for the connection pooling implementation are as follows :-
JSQLConnect Connection Pooling JSQLConnect also provides its own connection pooling facility that is fully JDBC 2.0 compliant. This is enables connection pooling for:-
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 finally construct to make sure pooled connections are closed even if an
exception occurs.
JSQLConnect provides support for J2EE / JDBC 2.0 Optional distributed transactions. JDBC connections obtained from a JSQLConnect XADataSource can participate in standard distributed transaction processing environments such as J2EE application servers. The classes for the distributed transaction implementation are as follows :-
XA Distributed transaction connections are also pooled connections. JSQLConnect provides support for various types of JDBC 2.0 connected rowsets. JSQLConnect Rowsets are Java BeansT compliant. The classes for rowset support are as follows :-
Please see ExampleCachedRowset included with the distribution for example code. Tomcat Servlet/JSP Setup As a certified J2EE product, JSQLConnect may be used within J2EE servlet and JSP applications. The are now many servlet/JSP products available. In this section we focus on the official reference implementation of servlets/JSP called Apache Tomcat that is freely downloadable. The following concepts can be adapted as required to other servlet/JSP products by consulting the vendor's technical documentation. We will not provide a tutorial here for getting started with the Tomcat product. That material is covered in that product's download. The intention is to demonstrate how JSQLConnect can be installed into the Tomcat servlet/JSP environment. We assume that you have Tomcat installed and the example servlets running correctly. The sections below describe how to setup and run various JSQLConnect examples under a standard Tomcat installation. Deploying the Driver JSQLConnect is a 3rd party package and must therefore be deployed correctly into the Tomcat environment. The recommended deployment is to copy JSQLConnect.jar into the common/lib ($CATALINA_HOME/common/lib) directory of your Tomcat installation. This will make JSQLConnect available to all web applications and Tomcat internal classes. Restart Tomcat after you have deployed the JSQLConnect.jar. Deploying the Sample JSQLConnect JSP Application Create the new directory webapps\examples\jsp\JSQLConnect in your Tomcat installation. Copy ExampleJSP.jsp from the JSQLConnect distribution to this directory. Test the JSP with the URL http://localhost:8080/examples/jsp/JSQLConnect/ExampleJSP.jsp. Deploying the Sample JSQLConnect Servlet Copy the ExampleServlet.class from the JSQLConnect distribution to the webapps\examples\WEB-INF\classes directory in your Tomcat installation. Test the servlet with the URL http://localhost:8080/examples/servlet/ExampleServlet Creating a Tomcat JNDI Connection Pool Create a Tomcat JNDI connection pool resource in the server.xml configuration file. This entry specifies the name, driver and properties to create the pool with. For example:-
<Resource name="jdbc/JSQLConnect-Pool" auth="Container"
type="javax.sql.DataSource"/> Restart Tomcat. Now create a resource reference in your application's web.xml that your application will refer to the connection pool as. For example:- <resource-ref> Finally, code the connection in your application. For example:- Context initCtx = new InitialContext(); Deploying the Driver JSQLConnect is a 3rd party packages and must therefore be deployed correctly into the Tomcat environment. The simplest deployment is to copy JSQLConnect.jar into the Tomcat lib directory (E.G. /jakarta-tomcat-3.2.1/lib). After copying the JAR, restart Tomcat. The JAR file will be automatically added to the Tomcat classpath and made available for servlets and JSP's. Deploying the Example Servlet Tomcat provides the deployment of applications in 'web applications'. For this example we will simply add ExampleServlet to the sample web application that is pre-configured when you download and install Tomcat. The name of this web application is 'examples'. Edit and compile ExampleServlet.java to connect to and process a table in your specific database. Copy ExampleServlet.class to \jakarta-tomcat-3.2.1\webapps\examples\WEB-INF\classes. Invoke the example in a browser with:- http://localhost:8080/examples/servlet/ExampleServlet
J2EE EJB Sun Reference Implementation Setup Configuring the Application Server As a certified J2EE product, JSQLConnect may be used within J2EE Application Servers. The are now many EJB application servers available. In the following discussion we focus on Javasoft's reference J2EE application server that is freely downloadable from www.javasoft.com. The following concepts can be adapted as required to other application servers by consulting the vendor's technical documentation. We will not provide a tutorial here for getting started with the J2EE application server. That material is covered in that product's download. The intention is to demonstrate how JSQLConnect can be installed into the application server environment. We assume that you have the application server installed and the 'getting started' applications working correctly. J2EE Reference Implementation 1.3 J2EEAdmin is a utility provided with the reference implementation to configure JDBC datasources. Please see the reference implementation for a complete description of this utility. First, JSQLConnect.jar must be deployed to %J2EE_HOME%/lib/system and the jar file(s) appended to the J2EE_CLASSPATH. Edit bin/userconfig.bat and add JSQLConnect.jar to the J2EE classpath, for example:- set J2EE_CLASSPATH=/j2sdkee1.3.1/lib/system/JSQLConnect.jar Register the driver with :- j2eeadmin -addJdbcDriver com.jnetdirect.jsql.JSQLDriver j2eeadmin -addJdbcDatasource jdbc/DataSource1 jdbc:JSQLConnect://localhost/database=mydatabase/user=user/password=pwd Your EJB code may now obtain database connections from this datasource. For example :-
For example to create datasource jdbc/XA_DataSource1:- j2eeadmin -addJdbcXADatasource jdbc/XA_DataSource1 com.jnetdirect.jsql.JSQLXADataSource
user password -props serverName=localhost database=mydatabase J2EE provides a deployment tool called deploytool. It is used to deploy applications to the application server. Using deploy tool select your EJB class and open the Resource Ref's panel. Add a new resource factory with coded name = jdbc/DataSource1, type = javax.sql.DataSource, Authentication=container. In the deployment settings set the JNDI name to jdbc/DataSource1 and enter the user name and password for the database connection. Now deploy (or update) your application to the application server. In the process of deploying the application, this step creates a mapping from the datasource name used in your EJB to the datasources specified in the application server's configuration file. Run your EJB client to test the datasource connections. Using JSQLConnect in Vendor IDE's and Application Servers This section provides details of how JSQLConnect is setup in various IDE's (Integrated Development Environments) and J2EE Application servers. In general, every IDE or application server that needs to use JSQLConnect requires to know how to find the driver classes. This is typically achieved by setting the environment's classpath to point to JSQLConnect.jar and/or deploying JSQLConnect.jar into a specific directory in the environment's runtime. The procedures detailing how this deployment is performed for each specific environment is usually different. However, the required procedures will be documented in the product's technical documentation. Generally the instructions are included in sections relating the the deployment of third party libraries. Application Servers In addition, applications running within J2EE application server environments typically use a JNDI datasource to acquire JDBC connections. Therefore you need to define one or more datasources within the application server that are configured to use JSQLConnect. Again, the details on how these configurations are performed varies from one application server to the next and you will need to consult the vendors technical documentation for details. Also note that in many cases additions or changes to the application servers environment requires that the application server be restarted before the changes are effective. Transaction Management in Application Servers JDBC Datasources can support either non-distributed or distributed (supporting XA transactions) connections. A common misconception appears to be that database connections must always be acquired from an XA data source for EJB applications that require transactional support such as commit, rollback etc (either via bean managed or container managed transactions). This is actually not the case - an XA datasource is only required when database connections are required to participate in a distributed 2-phase commit cycle which is managed by the application server's distributed transaction manager. Distributed transactions are generally only required in cases where multiple, distributed databases (or other transactional resources) are being updated in a single transaction. So in summary, transaction control (commit, rollback) can be performed on database connections acquired from non XA datasources. Such transactional control may be controlled by the application server (container managed) or the application (bean managed). XA datasources should be used when multiple, distributed databases or resources are being updated by a single transaction.
All JBuilder applications are created within the framework of a project. To use JSQLConnect within your project first open that project. Now navigate to "Project - Project Properties - Paths Tab - Required Libraries Tab". This tab defines the list of libraries that your project requires. It is necessary to create a library entry for JSQLConnect and add it to your project. Hit the add button and create a new library called, for example, JSQLConnect. Any name can be used. Under the library paths hit the add button and navigate to the directory where you have copied JSQLConnect.jar and click on this jar file. The library (for example JSQLConnect) should now be listed as a required library in your project. Now that you have defined the library you can add that library to other projects in the same manner. Using JSQLConnect with JBuilder JDBC Explorer and JDBC Pilot Start JBuilder and navigate to Tools-Enterprise Setup, Database Drivers. Hit 'Add', then "New', then 'Add' under library paths. Navigate to \JSQLConnect\JSQLConnect.jar and make sure its entered under the 'Library Paths' for the new library. Hit OK and restart JBuilder. Go into Database Pilot and click File-New. The drop down does not show the new driver. So enter com.jnetdirect.jsql.JSQLDriver in the Driver field. Enter the URL (EG jdbc:JSQLConnect://localhost/database=mydatabase) in the URL field and hit OK. Now the new driver entry will be listed under 'Database URLs'. Double click on the new entry to connect and enter the username and password. You will now see the list of tables, views and procedures in the database. For more information setting up JSQLConnect with JBuilder JDBC Explorer and JDBC Pilot please see :- http://community.borland.com/article/0,1410,23173,00.html To use JSQLConnect in Your Application JSQLConnect.jar must be deployed into the IDE classpath. Copy JSQLConnect.jar to the existing directory /lib/ext under the IDE installation root. To Use the JDBC Form Wizard, Database Schema or Database Explorer Features First, create a driver entry for JSQLConnect:- Navigate to File - Explorer and click on the "Runtime" tab. Right click on "Drivers" and click "Add Driver". In the "Name:" field enter JSQLConnect (this can be any name of your choosing), in the "Driver:" field enter "com.jnetdirect.jsql.JSQLDriver". In the "Database URL Prefix:" field enter any valid JSQLConnect URL connection string. For example "jdbc:JSQLConnect://localhost/database=mydatabase" Second, create a database connection:- Navigate to File - Explorer and and click on the "Runtime" tab. Right click on "Databases" and click "Add Connection". In the name selection choose the name of the JSQLConnect driver entry you created previously. E.G. "JSQLConnect". in the "Database URL:" field keep the default or change the URL to connect to a different database or server. Provide the username and password in the remaining fields. Right click the new connection and hit "connect". You will now be able to browse tables, views and procedure in the database. JRUN 4.0 Driver Installation To make JSQLConnect available to your JRUN applications - stop the JRUN server, copy JSQLConnect.jar into the {jrun_root}/servers/lib directory of your JRUN installation and restart JRUN. This makes JSQLConnect available to all server applications. Install Datasources In order to connect you must create a JSQLConnect datasource. Start the JRUN Management Console and navigate to the server your application runs under. (Example: To create a datasource for the JRUN samples navigate to the 'samples' server). Navigate to 'Resouces-JDBC Data Sources' Enter a new datasource name, set the database driver to 'not-listed' and press 'add'. Enter 'Driver Class Name' as com.jnetdirect.jsql.JSQLDriver. Enter the URL for the JSQLConnect connection. Enter the username and password as required. Press 'submit' to save the datasource. The datasource should now be listed. Press 'verify' to ensure the connection can be made. (Example: Datasource name=myexample, JNDI Name: myexample, URL: jdbc:JSQLConnect://localhost/database=mydatabase) Datasource References Create a resource-ref in the application's web.xml. Map the resource-ref to the JNDI datasource context with a resource-ref entry in the application's jrun-web.xml. Aquire connections using the ctx.lookup for the resource reference. Example: You can modify the JRUN bundled servlet sample {jrun_root}/servers/samples/techniques-ear/techniques-war/WEB-INF/classes/DatasourceAccess.java to use the new JSQLConnect datasource. 1. Modify DatasourceAccess.java to use the new datasource using
Change the example query to
Recompile the example.
3. Map the resource reference to the JNDI context in jrun-web.xml using
4. Ensure the JRUN samples server is running and run the example using the URL http://localhost:8200/techniques/servlet/DatasourceAccess JRUN 3.1 and 3.0 To make JSQLConnect available to the JRun run time copy JSQLConnect.jar into
the /lib/ext directory of your JRun installation and restart JRun. To make JSQLConnect available to the Weblogic J2EE server copy JSQLConnect.jar into the /lib directory of your Weblogic installation. In addition you must add JSQLConnect.jar to the classpath of the script that starts Weblogic server. For example, for the example server, in file startExamplesServer.cmd add JSQLConnect.jar to the classpath:- set
CLASSPATH=.;.\lib\weblogic_sp.jar;.\lib\weblogic.jar;.\samples\eval\cloudscape\lib\cloudscape.jar;.\config\examples\serverclasses;.\lib\JSQLConnect.jar Creating Weblogic Connection Pools Start the Weblogic Server Console and navigate to the JDBC node. Right click on
"connection pools" and click "Configure a new JDBC Connection Pool". Enter the
name of the new connection pool - you can use any name you wish. In "URL:"
enter any valid JSQLConnect connection URL. For example:- Under "Driver Classname" enter com.jnetdirect.jsql.JSQLDriver. Under properties enter any remaining connection properties required as key=value pairs. For example user=myusername Set the remaining connection pool properties such as initial and maximum capacity as appropriate. Deploy the connection pool to the required servers under the "Targets" tab. Creating Weblogic Datasources Now that you have created a JSQLConnect connection pool you can create datasources that use that pool. Create a datasource under "JDBC-Datasource" and set its "Pool Name" to the name of the JSQLConnect connection pool you created previously. When your application acquires a connection from this datasource one of the JSQLConnect connections in the connection pool will be issued. JDBC Connection Sequence In summary the JDBC connection process is :- 1. Your EJB references a resource reference name via an initial context
lookup. See the Weblogic JNDI documentation and examples to learn how to reference JDBC datasources from your application. Weblogic XA Datasources To provide distributed XA connections to your EJB application you must:- 1. Setup a new connection pool entry under Services-JDBC-Connection Pools as above. However, the "Driver Classname" must be com.jnetdirect.jsql.JSQLXADataSource. This is the JSQLConnect XA compliant data source class. 2. Setup a datasource as above. The only difference for XA is that the datasource must now be defined in the Services-JDBC-Tx Data Sources section. Define the XA datasource so that it points to the XA connection pool you defined in 1.
WebSphere 6.0 To make JSQLConnect available to the WebSphere 6.0 server copy
JSQLConnect.jar into the /lib directory of your WebSphere installation. Navigate to Resources, JDBC Providers. Select the appropriate scope for the
JDBC provider: Cell, Node or Server and click New. Step 1, database type 'user-defined' Set the classpath to the driver.
e.g. ${WAS_INSTALL_ROOT}/lib/JSQLConnect.jar
Restart the WebSphere server. Create a WAS 6.0 Data Source using JSQLConnect
To make JSQLConnect available to the WebSphere 5.0 server copy JSQLConnect.jar into the /lib directory of your WebSphere installation. Restart the WebSphere server. Create a JSQLConnect JDBC Resource Start the WebSphere administration console. Navigate to Resources-JDBC Drivers, select the required resource scope and hit new. Select User-Defined JDBC Provider. Create the resource with the following settings:- Classpath: ${User-defined_JDBC_DRIVER_PATH}/JSQLConnect.jar Create a WAS 5.0 Data Source using JSQLConnect 1) Select the JSQLConnect resource you created above. Click on Data Sources, New. 2. Enter "Name:" - any name of your choosing, JSQLConnect connection property. For example to enter the server name property enter name: serverName, value: the IP or name of the database server, Type: java.lang.String. Please note that property names are case sensitive. If the case entered for a property name is incorrect Websphere will silently ignore the property setting. Ensure all the setting you have entered above are saved. During application deployment (or application assembly) bind the resource references your application uses to to the new datasource you have created above by mapping the resource reference to the datasource's JNDI name. (Re)start your Websphere application. Create a Websphere version 4.0 Data Source using JSQLConnect See the section below for creating version 4.0 datasources. WebSphere 4.0 To make JSQLConnect available to the WebSphere server copy JSQLConnect.jar into the /lib directory of your WebSphere installation. Restart the WebSphere server. Create a JSQLConnect Driver Definition Start the WebSphere administration console. Navigate to Resources-JDBC Drivers and hit new. Create the driver with the following settings:- Resource Provider Type = User defined JDBC Driver Create a Data Source using JSQLConnect Now that you have created the driver definition you can create data sources using JSQLConnect. 1. Navigate to Resources-JDBC Drivers and then "Data Sources" under the driver definition you just created. Click "new" to create a new data source. 2. Enter "Name:" - any name of your choosing, PLEASE NOTE WebSphere generates a null pointer exception at startup if you provide a non blank data source password. You need to ensure the password is a non zero length string. This is appears to be a Websphere bug. JDBC Connection Sequence In summary the JDBC connection process is :- 1. Your EJB references a resource reference name via an initial context
lookup. See the WebSphere JNDI documentation and examples to learn how to reference JDBC datasources from your application. WebSphere XA Datasources To create an XA data source you must create a new driver definition as described above. The only difference is that the class name specifies the JSQLConnect XA data source:- Implementation Class Name = com.jnetdirect.jsql.JSQLXADataSource Now you can create data sources under the XA driver and use them as usual.
JBoss 3.0 and 4.0 now uses JCA (Java Connector Architecture) to deploy and load JDBC drivers as resource adapters. To deploy JSQLConnect:- 1. To make JSQLConnect available to the application server copy JSQLConnect.jar to the %JBOSS_HOME%/server/<configuration>/lib directory. E.G. jboss-3.0.0/server/default/lib. 2. Create a resource XML descriptor. See the example descriptors in the JSQLConnect distribution under Examples\JBoss_3.0_Configurations. Deploy the resource adapter. EG copy it to %JBOSS_HOME%/server/<configuration>/deploy 3. Connect to the database using the datasource name specified in the resource descriptor. JBOSS Example Project using JSQLConnect You can test the datasource created by the JSQLConnect example JBOSS resource
adapters easily. The resource adapter created by the examples is java:/JSQL_DS
Test with the the JBOSS sample 'transaction' project without code changes by
changing the project's .ant.properties to include:- To make JSQLConnect available to the JBoss application server 2.4 copy the JSQLConnect.jar %JBOSS_HOME%/lib/ext directory. Add an entry for the driver to the list of drivers that JBoss loads at startup. This list is stored in the jboss.jcml file. The file can be found in %JBOSS_HOME%/conf/ <config-name>. For a standard distribution of JBoss, <config-name> is default whilst for the JBoss-Jetty it is jetty. The entry should be a single, continuous list of comma-separated JDBC driver names. Open the file and look for the entry that begins with the following mbean tag: <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider"> The list of drivers is kept in an <attribute> sub-tag of this entry named
Drivers. Add the JSQLConnect driver to the list as shown below. At this point, you have told JBoss about your driver. You will now set up a
connection pool that your EJBeans can connect to. You will create a connection
pool named MyConnectionPool. To create the connection pool, locate the mbean
entry that begins with the following line in the jboss.jcml file.<
/p><p><mbeancode="org.jboss.jdbc.XADataSourceLoader"name="DefaultDomain:service=XADataSource,name=DefaultDS"> Set the attribute values as required for the connection pool. Using a JBoss Datasource<br><mbeancode="org.jboss.jdbc.XADataSourceLoader"name="DefaultDomain:service=XADataSource,name=SQLServerPool"> When JBoss starts check your log to verify the driver and connection pools are
created successfully. This shows the driver is found and loaded:- More info for JBoss can be found at http://www.jboss.org/docs/. Using JSQLConnect with the JBoss Examples JBoss ships with an example CMP bean called CDBean.java. This example is documented at http://www.jboss.org/online-manual/HTML/ch03.html. You can test this example using JSQLConnect and the connection pool just created by simply changing the %JBOSS_HOME%\conf\default\standardjaws.xml configuration file lines from:-
<datasource>java:/DefaultDS</datasource> Distributed XA Transactions In order to enable XA two-phase commit transactions in JBoss you need to use the
JSQLConnect
XADataSource. For example:-<
/p><p><mbeancode="org.jboss.jdbc.XADataSourceLoader"name="DefaultDomain:service=XADataSource,name=SQLServerPool">
XML Support 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 2000 can be used to process XML data in a SQL 2000 database. The following examples require the new XML support provided by SQL 2000. 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
Result: <XMLTESTTABLE numericColumn="1" characterColumn="data1"
datetimeColumn="2001-01-01T00: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.
Result: <row numericColumn="1" characterColumn="data1"
datetimeColumn="2001-01-01T00:00:00"/> Building a Resultset from an XML document The following example shows how to build a JDBC result set from an XML document using SQL Server's sp_xml_preparedocument procedure.
Result:- Customer1 2000-08-25 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.
Results:- |