Note

For information about JDBC programming, go to: http://java.sun.com/jdbc. To access the JDBC Guide: Getting Started manual for JDBC 1.0, go to: http://java.sun.com/products/jdk/1.1/docs/guide/jdbc/. To access the JDBC Guide: Getting Started manual for JDBC 2.0, go to: http://java.sun.com/products/jdk/1.2/docs/guide/jdbc/.

The following topics are included in this chapter:

• Setting Up jConnect

• Establishing a Connection

• Implementing Custom Socket Plug-Ins

• Handling Internationalization and Localization

• Working with Databases

• Implementing Advanced Features

• Handling Restrictions, Limitations, and Deviations from JDBC Standards

Setting Up jConnect

---

This section describes the tasks you need to perform before you use jConnect. Setting the jConnect Version

---

There are several versions of jConnect; use a version setting to determine:

• The default value of the LANGUAGE connection property

• The version-specific features that are available

• The default character set, if no character set is specified through the CHARSET connection property

• The default value of the CHARSET_CONVERTER connection property

• The default value of the CANCEL_ALL connection property, which is used to set the behavior of Statement.cancel( ), which by default cancels the object on which it is invoked and any other Statement objects that have begun to execute and are waiting for results

Table 2-1 lists the version settings available and their features.

Table 2-1:   jConnect version settings and their features

Version	Features	Comments
VERSION_5	The default value of the LANGUAGE connection property is null. If the CHARSET connection property does not specify a character set, jConnect uses the database's default character set.The default value for CHARSET_CONVERTER is the PureConverter class. By default, Statement.cancel( ) cancels only the Statement object it is invoked on. JDBC 2.0 methods can be used to store and retrieve Java objects as column data.	For jConnect version 5.0, the default is VERSION_5. For additional information, the comments for VERSION_4.
VERSION_4	The default value of the LANGUAGE connection property is null. If the CHARSET connection property does not specify a character set, jConnect uses the database's default character set.The default value for CHARSET_CONVERTER is the PureConverter class. By default, Statement.cancel( ) cancels only the Statement object it is invoked on. JDBC 2.0 methods can be used to store and retrieve Java objects as column data.	Messages from the server are localized according to the language setting in your local user environment. The languages supported are: Chinese, US English, French, German, Japanese, Korean, Portuguese, and Spanish. The default behavior of Statement.cancel( ) is JDBC-compliant. You can use the CANCEL_ALL connection property to set the behavior of Statement.cancel( ). See "CANCEL_ALL Connection Property". For information on Java objects as column data, see "Storing Java Objects as Column Data in a Table".
VERSION_3	The default value of the LANGUAGE connection property is us_english. If the CHARSET connection property does not specify a character set, jConnect uses the database's default character set. The default value for CHARSET_CONVERTER is the PureConverter class. By default, Statement.cancel( ) cancels the object it is invoked on and any other Statement objects that have begun to execute and are waiting for results.	See the comments for VERSION_2.
VERSION_2	The default value of the LANGUAGE connection property is us_english. If the CHARSET connection property does not specify a character set, the default character set is iso_1. The default value for CHARSET_CONVERTER is the TruncationConverter class, unless the CHARSET connection property specifies a multibyte or 8-bit character set, in which case the default CHARSET_CONVERTER is the PureConverter class. By default, Statement.cancel( ) cancels the object it is invoked on and any other Statement objects that have begun to execute and are waiting for results.	VERSION_2 is the default version setting for jConnect version 2.x. Note: VERSION_5 is the default version setting for jConnect version 5.0. The LANGUAGE connection property determines the language in which messages from the jConnect driver and messages from the server appear. For information on the CHARSET and CHARSET_CONVERTER connection classes, see "jConnect Character-Set Converters". The VERSION_2 default behavior of Statement.cancel( ) is not JDBC-compliant. Use the CANCEL_ALL connection property to set the behavior of Statement.cancel( ). See "CANCEL_ALL Connection Property".

The version values are constant values from the SybDriver class. When referring to the version constant, use the following syntax:
com.sybase.jdbcx.SybDriver.VERSION_5
Use SybDriver.setVersion( ) to set the jConnect version. The following code samples show how to load the jConnect driver and set the version.For jConnect 4.1:
import com.sybase.jdbcx.SybDriver;
SybDriver sybDriver = (SybDriver)
Class.forName ("com.sybase.jdbc.SybDriver").newInstance();
sybDriver.setVersion
(com.sybase.jdbcx.SybDriver.VERSION_4);
DriverManager.registerDriver(sybDriver);
For jConnect 5.0:
import com.sybase.jdbcx.SybDriver;
SybDriver sybDriver = (SybDriver)
Class.forName
("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
sybDriver.setVersion
(com.sybase.jdbcx.SybDriver.VERSION_5);
DriverManager.registerDriver(sybDriver);
You can call setVersion( ) multiple times to change the version setting. New connections inherit the behavior associated with the version setting at the time the connection is made. Changing the version setting during a session does not affect the current connection. As described in the next section, you can use the JCONNECT_VERSION connection property to override the SybDriver version setting and specify a different version setting for an individual connection. JCONNECT_VERSION Connection Property The JCONNECT_VERSION connection property allows you to specify the jConnect version setting for an individual session.You can set JCONNECT_VERSION to an integer value of "2," "3," "4," or "5," depending on the version characteristics you want (see Table 2-1). CANCEL_ALL Connection Property CANCEL_ALL is a Boolean-valued connection property for specifying the behavior of the Statement.cancel( ) method.

Note	In jConnect versions earlier than 4.0, the default for CANCEL_ALL is "true." In jConnect version 4.0 and later, to comply with the JDBC specification, if you set the connection property JCONNECT_VERSION to "4" or above, the default setting for CANCEL_ALL is "false."

The settings for CANCEL_ALL have the following effect on Statement.cancel( ):

• If CANCEL_ALL is "false," invoking Statement.cancel( ) cancels only the Statement object it is invoked on. Thus, if stmtA is a Statement object, stmtA.cancel( ) will cancel the execution of the SQL statement contained in stmtA in the database, but no other statements will be affected. stmtA is canceled whether it is in cache waiting to execute or has started to execute and is waiting for results.

• If CANCEL_ALL is "true," invoking Statement.cancel( ) cancels not only the object it is invoked on, but also any other Statement objects on the same connection that have executed and are waiting for results.

The following example sets CANCEL_ALL to "false." In the example, props is a Properties object for specifying connection properties.
...
props.put("CANCEL_ALL", "false");

Note	To cancel the execution of all Statement objects on a connection, regardless of whether or not they have begun execution on the server, use the extension method SybConnection.cancel( ).

Invoking the jConnect Driver

---

There are two suggested ways to register and invoke the Sybase jConnect driver:

• Use Class.forName( ).newInstance to create an instance of com.sybase.jdbcx.SybDriver .

The following example creates an instance of com.sybase.jdbcx.SybDriver. SybDriver contains initialization code that registers an instance as a driver at the time it is created.

For jConnect 4.1:

Class.forName("com.sybase.jdbc.SybDriver").newInstance();

For jConnect 5.0:

Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();

• Add the jConnect driver to the jdbc.drivers system property . At initialization, the DriverManager class attempts to load the drivers listed in jdbc.drivers. This is less efficient than the previous approach. You can list multiple drivers in this property, separated with a colon (:). The following code samples show how to add a driver to jdbc.drivers within a program:

For jConnect 4.1:

Properties sysProps = System.getProperties();
String drivers = "com.sybase.jdbc.SybDriver";
String oldDrivers =
sysProps.getProperty("jdbc.drivers");
if (oldDrivers != null)
drivers += ":" + oldDrivers;
sysProps.put("jdbc.drivers", drivers.toString());

For jConnect 5.0:

Properties sysProps = System.getProperties();
String drivers = "com.sybase.jdbc2.jdbc.SybDriver";
String oldDrivers =
sysProps.getProperty("jdbc.drivers");
if (oldDrivers != null)
drivers += ":" + oldDrivers;
sysProps.put("jdbc.drivers", drivers.toString());

Note	System.getProperties( ) is not allowed for Java applets. Use the Class.forName( ) method, instead.

Establishing a Connection

---

This section describes how to establish a connection to an Adaptive Server Enterprise or Adaptive Server Anywhere database using jConnect. Setting Connection Properties

---

Table 2-2 lists the connection properties for jConnect and indicates their default values. You must set the connection properties before you make a connection.There are two ways to set the driver connection properties:

• Using the DriverManager.getConnection( ) method in your application

• When you define the URL

Note	Driver connection properties set in the URL do not override any corresponding connection properties set in the application using the DriverManager.getConnection( ) method.

To obtain a current list of properties for any driver, use the Driver.getDriverPropertyInfo(String url, Properties props), which returns an array of DriverPropertyInfo objects. The array lists:

• The driver properties

• The current settings on which the driver properties are based

• The URL and props passed in

Driver connection property names are not case-sensitive (jConnect uses the prop.equalsIgnoreCase( ) method to make property names case-insensitive) .

Table 2-2:   Connection properties

Property	Description	Default Value
APPLICATIONNAME	A user-defined property. The server side can be programmed to interpret the value given to this property.	Null
CANCEL_ALL	Determines the behavior of the Statement.cancel( ) method. See "CANCEL_ALL Connection Property".	Depends on version setting. (See "Setting the jConnect Version".)
CHARSET	Specifies the character set for strings passed through TDS. If you specify a charset, it must match a charset listed in syscharsets. If null, jConnect uses the server's default charset.	Null
CHARSET_CONVERTER_ CLASS	Use this property to specify the character-set converter class you want jConnect to use. jConnect uses the version setting from SybDriver.setVersion( ) to determine the default character-set converter class to use. See "Selecting a Character-Set Converter" for details.	Version dependent.
CONNECTION_FAILOVER	For use with the Java Naming and Directory Interface (JNDI). See "CONNECTION_FAILOVER Connection Property".	true
DYNAMIC_PREPARE	Determines whether dynamic SQL prepared statements are precompiled in the database. See "DYNAMIC_PREPARE Connection Property".	false
EXPIRESTRING	A read-only property that contains the license expiration date. Expiration is "never" except for evaluation copies of jConnect.	Never
HOSTNAME	The name of the current host.	None
HOSTPROC	Identifies the application's process on the host machine.	None
JCONNECT_VERSION	Use this property to set version-specific characteristics. See "JCONNECT_VERSION Connection Property".	5
LANGUAGE	Set this property for error messages returned from the server and for jConnect messages. It must match a language in syslanguages.	Version dependent. See "Setting the jConnect Version".
LANGUAGE_CURSOR	Set this property to true if you want jConnect to use "language cursors" instead of "protocol cursors." See "Cursor Performance and the LANGUAGE_CURSOR Connection Property".	false
LITERAL_PARAMS	This property is for use only with Adaptive Server Anywhere, which requires you to send prepared statement parameters as literals. For all other Sybase databases, this property can be set to "false." When set to "true," any parameters set by the setXXXmethods in the PreparedStatement interface are inserted literally into the SQL statement when it is executed. If set to "false," parameter markers are left in the SQL statement and the parameter values are sent to the server separately.	false
PACKETSIZE	Network packet size.	512
PASSWORD	Login password. Set automatically if using the getConnection(String, String, String) method, or explicitly if using getConnection(String, Props).	None
PROTOCOL_CAPTURE	The PROTOCOL_CAPTURE connection property is used to specify a file for capturing TDS communication between an application and an Adaptive Server. See "Custom Socket Implementation Error" and "Using the Ribo Utility".	Null
PROXY	Gateway address. For the HTTP protocol, the URL is: http://host:port. To use the HTTPS protocol that supports encryption, the URL is https://host:port/servlet_alias.	None
REMOTEPWD	Remote server passwords for access via server-to-server remote procedure calls. See "Performing Server-to-Server Remote Procedure Calls".	None
REPEAT_READ	Determines whether the driver keeps copies of columns and output parameters so that columns can be read out of order or repeatedly. See "REPEAT_READ Connection Property".	true
SELECT_OPENS_CURSOR	If set to "true," calls to Statement.executeQuery( ) will automatically generate a cursor when the query contains a "FOR UPDATE" clause. If you have previously called Statement.setFetchSize( ) or Statement.setCursorName( ) on the same statement, a setting of "true" for SELECT_OPENS_CURSOR has no effect. Note: You may experience some performance degradation when SELECT_OPENS_CURSOR is set to "true." See "Using Cursors with Result Sets" for more information on using cursors with jConnect.	false
SERVICENAME	The name of a back-end database server that a DirectConnect gateway serves. Also used to indicate the database to which Adaptive Server Anywhere wants to connect.	None
SESSION_ID	When this property is set, jConnect assumes that an application is trying to resume communication on an existing TDS session held open by the TDS-tunnelling gateway. jConnect skips the login negotiations and forwards all requests from the application to the specified session ID.	Null
SESSION_TIMEOUT	Use this property to specify the amount of time (in seconds) that a server connection can remain idle before the connection is automatically closed.	Null
SQLINITSTRING	Use this property to define a set of commands to be passed to the back-end database server. These must be SQL commands that can be executed using the Statement.executeUpdate( ) method.	Null
SYBSOCKET_FACTORY	Use this property to enable jConnect to use your custom socket implementation. Set SYBSOCKET_FACTORY either to: The name of a class that implements com.sybase.jdbcx.SybSocketFactory; or "DEFAULT," which instantiates a new java.net.Socket( ) See "Implementing Custom Socket Plug-Ins".	Null
STREAM_CACHE_SIZE	Maximum size used to cache statement response streams.	Null (unlimited cache size)
USE_METADATA	When set to "true," a DatabaseMetaData object will be created and initialized when you establish a connection. The DatabaseMetaData object is necessary to connect to a specified database. If you do not need to connect to a database specified in the URL or use DatabaseMetaData for application purposes, you can set this property to "false," which will result in a faster connection time.	true
USER	Login ID. Set automatically if using the getConnection(String, String, String) method, or explicitly if using getConnection(String, Props).	None
VERSIONSTRING	Read-only version information for the JDBC driver.	jConnect driver version

The following code is an example of setting connection properties. The sample programs provided with jConnect also contain examples of setting these properties.
Properties props = new Properties();
props.put("user", "userid");
props.put("password", "user_password");
/*
* If the program is an applet that wants to access
* a server that is not on the same host as the
* web server, then it uses a proxy gateway.
*/
props.put("proxy", "localhost:port");
/*
* Make sure you set connection properties before
* attempting to make a connection. You can also
* set the properties in the URL.
*/
Connection con = DriverManager.getConnection
("jdbc:sybase:Tds:host:port", props);
Connecting to Adaptive Server Enterprise

---

In your Java application, define a URL using the jConnect driver to connect to an Adaptive Server. The basic format of the URL is:
jdbc:sybase:Tds:host:port
where:

jdbc:sybase - Identifies the driver.

Tds - The Sybase communication protocol for Adaptive Server.

host:port - The Adaptive Server host name and listening port. See $SYBASE/interfaces (UNIX) or %SYBASE%\ini\sql.ini (Windows) for the entry that your database or Open Server application uses. Obtain the host:portfrom the "query" entry.

You can connect to a specific database using this format:
jdbc:sybase:Tds:host:port/database

Note	To connect to a specific database using Adaptive Server Anywhere 6.x or DirectConnect, use the SERVICENAME connection property to specify the database name instead of "/database."

ExampleThe following code creates a connection to an Adaptive Server on host "myserver" listening on port 3697:
SysProps.put("user","userid");
SysProps.put("password","user_password");
String url = "jdbc:sybase:Tds:myserver:3697";
Connection_con =
DriverManager.getConnection(url,SysProps);
URL Connection Property Parameters You can specify the values for the jConnect driver connection properties when you define a URL.

Note	Driver connection properties set in the URL do not override any corresponding connection properties set in the application using the DriverManager.getConnection( ) method.

To set a connection property in the URL, append the property name and its value to the URL definition. Use this syntax:
jdbc:sybase:Tds:host:port/database?
property_name=value
To set multiple connection properties, append each additional connection property and value, preceded by "&." For example:
jdbc:sybase:Tds:myserver:1234/mydatabase?
LITERAL_PARAMS=true&PACKETSIZE=512&HOSTNAME=myhost
If the value for one of the connection properties contains "&," precede the "&" in the connection property value with a backslash (\). For example, if your host name is "a&bhost," use this syntax:
jdbc:sybase:Tds:myserver:1234/mydatabase?
LITERAL_PARAMS=true&PACKETSIZE=512&HOSTNAME=
a\&bhost
Do not use quotes for connection property values, even if they are strings. For example, use:
HOSTNAME=myhost
not:
HOSTNAME="myhost"
Connecting to Adaptive Server Anywhere

---

To use jConnect with Adaptive Server Anywhere, you should upgrade to Adaptive Server Anywhere version 6.x. Connecting to Adaptive Server Anywhere 5.0.xIf you have to connect to Adaptive Server Anywhere version 5.0.x via jConnect, you must run the Adaptive Server Anywhere Open Server Gateway dbos50, which is distributed with Adaptive Server Anywhere.

Note

The free download version of Adaptive Server Anywhere, available from the Powersoft Web site, does not include this Open Server Gateway. Call Powersoft at (800) 265-4555 to receive a CD that includes the Open Server Gateway and the required Open Server DLLs. You will be charged only for shipping and handling.

1. Install Open Server Gateway 5.5.03 or later and the Open Server DLLs. Use Open Server DLLs, version 11.1.

1. Add an entry for the gateway to your %SYBASE%\ini\sql.ini file (using, for example, sqledit).

1. Start the gateway by entering:

start dbos50 gateway-demo

where gateway-demo is the gateway name defined in step 2.

1. When the Open Server Gateway is running, you can define a connection as follows:

jdbc:sybase:Tds:host:port

host is the host name where the Adaptive Server Anywhere and Open Server gateway is running, and port is the port number defined in sql.ini.

Note	To support multiple Adaptive Server Anywhere databases, use sqledit to add an entry with a different port for each database, then run the Open Server Gateway for each database.

Connecting to a Server Using JNDI

---

In jConnect 4.0 and later, you can use the Java Naming and Directory Interface (JNDI) to provide connection information, which offers:

• A centralized location where you can specify host names and ports for connecting to a server. You do not need to hard code a specific host and port number in an application.

• A centralized location where you can specify connection properties and a default database for all applications to use.

• The jConnect CONNECTION_FAILOVER property for handling unsuccessful connection attempts. When CONNECTION_FAILOVER is set to "true," jConnect attempts to connect to a sequence of host/port server addresses in the JNDI name space until one succeeds.

To use jConnect with JNDI, you need to make sure that certain information is available in any directory service that JNDI accesses and that required information is set in the javax.naming.Context class. This section covers the following topics:

• Connection URL for Using JNDI

• Required Directory Service Information

• CONNECTION_FAILOVER Connection Property

• Providing JNDI Context Information

Connection URL for Using JNDITo specify that jConnect use JNDI to obtain connection information, place "jndi" as the URL's subprotocol after "sybase":
jdbc:sybase:jndi:protocol-information-for-use-with-JNDI
Anything that follows "jndi" in the URL is handled through JNDI. For example, to use JNDI with the Lightweight Directory Access Protocol (LDAP), you might enter:
jdbc:sybase:jndi:ldap://LDAP_hostname:port_number/servername=
Sybase11,o=MyCompany,c=US
This URL tells JNDI to obtain information from an LDAP server, gives the host name and port number of the LDAP server to use, and provides the name of a database server in an LDAP-specific form. Required Directory Service InformationWhen you use JNDI with jConnect, JNDI needs to return the following information for the target database server:

• A host name and port number to connect to

• The name of the database to use

• Any connection properties that individual applications are not allowed to set on their own

This information needs to be stored according to a fixed format in any directory service used for providing connection information. The required format consists of a numerical object identifier (OID), which identifies the type of information being provided (for example, the destination database), followed by the formatted information. Table 2-3 shows the required formatting.

Table 2-3:   Directory service information required for JNDI

Type of Information	Object Identifier (OID)	Format	Comments
Host and port	1.3.6.1.4.1.897.4.2.5	TCP#1#hostname  portnumber	You can specify multiple hosts and ports as separate entries, which lets you use CONNECTION_FAILOVER.
Connection property	1.3.6.1.4.1.897.4.2.10	Prop1=value&Prop2= value&Prop3=value&...	You can specify multiple connection properties by using a separate entry for each property or by putting multiple properties, separated by ampersands, in a single entry.
Database	1.3.6.1.4.1.897.4.2.11	databasename	None.
Connection protocol	1.3.6.1.4.1.897.4.2.9	Tds	Optional, but if you use a connection protocol, it must always be "Tds."

The following example shows connection information entered for the database server SYBASE11 under an LDAP directory service:
dn: servername=SYBASE11,o=MyCompany,c=US
servername: SYBASE11
1.3.6.1.4.1.897.4.2.5:TCP#1#giotto 1266
1.3.6.1.4.1.897.4.2.5:TCP#1#giotto 1337
1.3.6.1.4.1.897.4.2.5:TCP#1#standby1 4444
1.3.6.1.4.1.897.4.2.10:REPEAT_READ=false&PACKETSIZE=1024
1.3.6.1.4.1.897.4.2.10:CONNECTION_FAILOVER=true
1.3.6.1.4.1.897.4.2.11:pubs2
1.3.6.1.4.1.897.4.2.9:Tds
In this example, SYBASE11 can be accessed through either port 1266 or port 1337 on host "giotto" and it can be accessed through port 4444 on host "standby1." Two connection properties, REPEAT_READ and PACKETSIZE, are set within one entry. The CONNECTION_FAILOVER connection property is set as a separate entry. Applications connecting to SYBASE11 are initially connected with the pubs2 database. A connection protocol does not need to be specified, but if it is, it must be TDS. CONNECTION_FAILOVER Connection Property CONNECTION_FAILOVER is a Boolean-valued connection property you can use when jConnect uses JNDI to get connection information.If CONNECTION_FAILOVER is set to "true," jConnect makes multiple attempts to connect to a server. If one attempt to connect to a host and port number associated with a server fails, jConnect uses JNDI to get the next host and port number associated with the server and attempts to connect through them. Connection attempts proceed sequentially through all the hosts and ports associated with a server.For example, suppose CONNECTION_FAILOVER is set to "true," and a database server is associated with the following hosts and port numbers, as in the earlier LDAP example:
1.3.6.1.4.1.897.4.2.5:TCP#1#giotto 1266
1.3.6.1.4.1.897.4.2.5:TCP#1#giotto 1337
1.3.6.1.4.1.897.4.2.5:TCP#1#standby1 4444
To get a connection to the server, jConnect tries to connect to the host "giotto" at port 1266. If this fails, jConnect tries port 1337 on "giotto." If this fails, jConnect tries to connect to host "standby1" through port 4444.The default for CONNECTION_FAILOVER is "true." If CONNECTION_FAILOVER is set to "false," jConnect attempts to connect to an initial host and port number. If the attempt fails, jConnect throws a SQL exception and does not try again. Providing JNDI Context Information To use jConnect with JNDI, a developer should be familiar with the JNDI specification from Sun Microsystems, available from the Web:

http://java.sun.com/products/jndi

In particular, the developer needs to make sure that required initialization properties are set in javax.naming.directory.DirContext when JNDI and jConnect are used together. These properties can be set either at the system level or at runtime.Two key properties are:

• Context.INITIAL_CONTEXT_FACTORY

This property takes the fully qualified class name of the initial context factory for JNDI to use. This determines the JNDI driver that is used with the URL specified in the Context.PROVIDER_URL property.

• Context.PROVIDER_URL

This property takes the URL of the directory service that the driver (for example, the LDAP driver) is to access. The URL should be a string, such as "ldap://ldaphost:427".

The following example shows how to set context properties at runtime and how to get a connection using JNDI and LDAP. In the example, the INITIAL_CONTEXT_FACTORY context property is set to invoke Sun Microsystem's implementation of an LDAP service provider. The PROVIDER_URL context property is set to the URL of an LDAP directory service located on the host "ldap_server1" at port 983.
Properties props = new Properties();

/* We want to use LDAP, so INITIAL_CONTEXT_FACTORY is set to the
* class name of an LDAP context factory. In this case, the
* context factory is provided by Sun's implementation of a
* driver for LDAP directory service.
*/
props.put(Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.ldap.LdapCtxFactory");

/* Now, we set PROVIDER_URL to the URL of the LDAP server that
* is to provide directory information for the connection.
*/
props.put(Context.PROVIDER_URL, "ldap://ldap_server1:983");

/* Set up additional context properties, as needed. */
props.put("user", "xyz");
props.put("password", "123");

/* get the connection */
Connection con = DriverManager.getConnection
("jdbc:sybase:jndi:ldap://ldap_server1:983" +
"/servername=Sybase11,o=MyCompany,c=US",props);
Note that the connection string passed to getConnection( ) contains LDAP-specific information, which the developer must provide.When JNDI properties are set at runtime, as in the preceding example, jConnect passes them to JNDI to be used in initializing a server, as in the following jConnect code:
javax.naming.directory.DirContext ctx =
new javax.naming.directory.InitialDirContext(props);
jConnect then obtains the connection information it needs from JNDI by invoking DirContext.getAtributes( ), as in the following example, where ctx is a DirContext object:
javax.naming.directory.Attributes attrs =
ctx.getAttributes(ldap://ldap_server1:983/servername=
Sybase11, SYBASE_SERVER_ATTRIBUTES);
In the example, SYBASE_SERVER_ATTRIBUTES is an array of strings defined within jConnect. The array values are the OIDs for the required directory information listed in Table 2-3. Implementing Custom Socket Plug-Ins

---

This section discusses how to plug a custom socket implementation into an application to customize the communication between a client and server. javax.net.ssl.SSLSocket is an example of a socket that you could customize to enable encryption.com.sybase.jdbcx.SybSocketFactory is a Sybase extension interface that contains the createSocket(String, int, Properties) method that returns a java.net.Socket. In order for a jConnect version 4.1 or later driver to load a custom socket, an application must:

• Implement this interface

• Define the createSocket(..) method

jConnect uses the new socket for its subsequent input/output operations. Classes that implement SybSocketFactory create sockets and provide a general framework for the addition of public socket-level functionality.
/**
* Returns a socket connected to a ServerSocket on the named host,
* at the given port.
* @param host the server host
* @param port the server port
* @param props Properties passed in through the connection
* @returns Socket
* @exception IOException, UnknownHostException
*/
public java.net.Socket createSocket(String host, int port, Properties props) throws IOException, UnknownHostException;
Passing in properties allows instances of SybSocketFactory to use connection properties to implement an intelligent socket.When you implement SybSocketFactory to produce a socket, the same application code can use different kinds of sockets by passing the different kinds of factories or pseudo-factories that create sockets to the application. You can customize factories with parameters used in socket construction. For example, you could customize factories to return sockets with different networking timeouts or security parameters already configured. The sockets returned to the application can be subclasses of java.net.Socket to directly expose new APIs for features such as compression, security, record marking, statistics collection, or firewall tunnelling (javax.net.SocketFactory).

Note	SybSocketFactory is intended to be an overly simplified javax.net.SocketFactory, enabling applications to bridge from java.net.* to javax.net.* if desired.

To use a custom socket with jConnect:

1. Provide a Java class that implements com.sybase.jdbcx.SybSocketFactory. See "Creating and Configuring an Custom Socket".

1. Set the SYBSOCKET_FACTORY connection property so that jConnect can use your implementation to obtain a socket.

SYBSOCKET_FACTORY Connection Property

---

To use a custom socket with jConnect, set the SYBSOCKET_FACTORY connection property to a string that is either:

• The name of a class that implements com.sybase.jdbcx.SybSocketFactory

or

• DEFAULT, which instantiates a new java.net.Socket( )

See "Setting Connection Properties" for instructions on how to set SYBSOCKET_FACTORY. Creating and Configuring an Custom Socket

---

Once jConnect obtains a custom socket, it uses the socket to connect to a server. Any configuration of the socket must be completed before jConnect obtains it.This section explains how to plug in an SSL socket implementation, such as javax.net.ssl.SSLSocket, with jConnect.

Note	Currently, no Sybase servers support SSL.

The following example shows how an implementation of SSL can create an instance of SSLSocket, configure it, and then return it. In the example, the MySSLSocketFactory class implements SybSocketFactory and extends javax.net.ssl.SSLSocketFactory to implement SSL. It contains two createSocket methods--one for SSLSocketFactory and one for SybSocketFactory--that:

• Create an SSL socket

• Invoke SSLSocket.setEnableCipherSuites( ) to specify the cipher suites available for encryption

• Return the socket to be used by jConnect

Example
public class MySSLSocketFactory extends SSLSocketFactory
implements SybSocketFactory
{
/**
* Create a socket, set the cipher suites it can use, return
* the socket.
* Demonstrates how cither suites could be hard-coded into the
* implementation.
*
* See javax.net.SSLSocketFactory#createSocket
*/
public Socket createSocket(String host, int port)
throws IOException, UnknownHostException
{
// Prepare an array containing the cipher suites that are to
// be enabled.
String enableThese[] =
{
"SSL_DH_DSS_EXPORT_WITH_DES40_CBC_SHA",
"SSL_RSA_EXPORT_WITH_RC2_CBC_40_MD5",
"SSL_DH_RSA_EXPORT_WITH_DES40_CBC_SHA"
}
;
Socket s =
SSLSocketFactory.getDefault().createSocket(host, port);
((SSLSocket)s).setEnabledCipherSuites(enableThese);
return s;
}
/**
* Return an SSLSocket.
* Demonstrates how to set cipher suites based on connection
* properties like:
* Properties _props = new Properties();
* Set other url, password, etc. properties.
* _props.put(("CIPHER_SUITES_1",
* "SSL_DH_DSS_EXPORT_WITH_DES40_CBC_SHA");
* _props.put("CIPHER_SUITES_2",
* "SSL_RSA_EXPORT_WITH_RC2_CBC_40_MD5");
* _props.put("CIPHER_SUITES_3",
* "SSL_DH_RSA_EXPORT_WITH_DES40_CBC_SHA");
* _conn = _driver.getConnection(url, _props);
*
* See com.sybase.jdbcx.SybSocketFactory#createSocket
*/
public Socket createSocket(String host, int port,
Properties props)
throws IOException, UnknownHostException
{
// check to see if cipher suites are set in the connection
// properites
Vector cipherSuites = new Vector();
String cipherSuiteVal = null;
int cipherIndex = 1;
do
{
if((cipherSuiteVal = props.getProperty("CIPHER_SUITES_"
+ cipherIndex++)) == null)
{
if(cipherIndex <= 2)
{
// No cipher suites available
// return what the object considers its default
// SSLSocket, with cipher suites enabled.
return createSocket(host, port);
}
else
{
// we have at least one cipher suite to enable
// per request on the connection
break;
}
else
}
// add to the cipher suit Vector, so that
// we may enable them together
cipherSuites.addElement(cipherSuiteVal);
}
}
while(true);
// lets you create a String[] out of the created vector
String enableThese[] = new String[cipherSuites.size()];
cipherSuites.copyInto(enableThese);
// enable the cipher suites
Socket s =
SSLSocketFactory.getDefault().createSocket
(host, port);
((SSLSocket)s).setEnabledCipherSuites(enableThese);
// return the SSLSocket
return s;
}
// other methods
}
Since jConnect requires no information about the kind of socket it is, you must complete any configuration before you return a socket.For additional information, see:

• Encrypt.java - Located in the sample (jConnect 4.1) and sample2 (jConnect 5.0) subdirectories of your jConnect directory, this sample shows you how to use the SybSocketFactory interface with jConnect applications.

• MySSLSocketFactory.java - Also located in the sample (jConnect 4.1) and sample2 (jConnect 5.0) subdirectories of your jConnect directory, this is a sample implementation of the SybSocketFactory interface that you can plug-in to your application and use.

Handling Internationalization and Localization

---

This section discusses internationalization and localization issues relevant to jConnect. jConnect Character-Set Converters

---

jConnect uses special classes for all character-set conversions. By selecting a character-set converter class, you specify how jConnect should handle single-byte and multibyte character-set conversions, and the performance impact the conversions will have on your applications.There are two character-set conversion classes. The conversion class that jConnect uses is based on the version setting (for example, VERSION_4), and the CHARSET and CHARSET_CONVERTER_CLASS connection properties.

• The TruncationConverter class works only with single-byte character sets that use ASCII characters such as iso_1 and cp850. It does not work with multibyte character sets or single-byte character sets that use non-ASCII characters.

Using the TruncationConverter class, jConnect 5.0 handles character sets in the same manner as jConnect version 2.2. The TruncationConverter class is the default converter when the version setting is VERSION_2.

• The PureConverter class is a pure Java, multibyte character-set converter. jConnect uses this class if the version setting is VERSION_4 or higher. jConnect also uses this converter with VERSION_2 if it detects a character set specified in the CHARSET connection property that is not compatible with the TruncationConverter class.

Although it enables multibyte character-set conversions, the PureConverter class may negatively impact jConnect driver performance. If driver performance is a concern, see "Improving Character-Set Conversion Performance".

Selecting a Character-Set Converter jConnect uses the version setting from SybDriver.setVersion( ) to determine the default character-set converter class to use. For VERSION_2, the default is TruncationConverter. For VERSION_4 and later, the default is PureConverter.You can also set the CHARSET_CONVERTER_CLASS connection property to specify which character-set converter you want jConnect to use. This is useful if you want to use a character-set converter other than the default for your jConnect version. For example, if you set jConnect to VERSION_4 or higher, but want to use the TruncationConverter class rather than the multibyte PureConverter class, you can set CHARSET_CONVERTER_CLASS:For jConnect 4.1:
...
props.put("CHARSET_CONVERTER_CLASS",
"com.sybase.utils.TruncationConverter")
For jConnect 5.0:
...
props.put("CHARSET_CONVERTER_CLASS",
"com.sybase.jdbc2.utils.TruncationConverter")
Setting the CHARSET Connection Property You can specify the character set to use in your application by setting the CHARSET driver property. If you do not set the CHARSET property:

• For VERSION_2, jConnect uses iso_1 as the default character set.

• For VERSION_3, VERSION_4, and VERSION_5, jConnect uses the database's default character set, and adjusts automatically to perform any necessary conversions on the client side.

You can also use the -J charset command line option for the IsqlApp application to specify a character set. To determine which character sets are installed on your Adaptive Server, issue the following SQL query on your server:
select name from syscharsets
go
For the PureConverter class, if the designated CHARSET does not work with the client's Java Virtual Machine (VM), the connection fails with a SQLException, indicating that you must set CHARSET to a character set that is supported by both Adaptive Server and the client.When the TruncationConverter class is used, character truncation is applied regardless of whether the designated CHARSET is 7-bit ASCII or not. Improving Character-Set Conversion Performance If you use multibyte character sets and need to improve driver performance, you can use the SunIoConverter class provided with the jConnect samples. See "Character-Set Conversion" for details. Supported Character Sets Table 2-4 lists the Sybase character sets that are supported by this release of jConnect. The table also lists the corresponding JDK byte converter for each supported character set.Although jConnect supports UCS-2, currently no Sybase databases or open servers support UCS-2.The Sybase sjis character set does not include the IBM or Microsoft extensions to JIS, whereas the JDK SJIS byte converter includes these extensions. As a result, conversions from Java strings to a Sybase database using sjis may result in character values that are not supported by the Sybase database. However, conversions from sjis on a Sybase database to Java strings should not have this problem. Table 2-4 lists the character sets currently supported by Sybase.

Table 2-4:   Supported Sybase character sets

SybCharset Name	JDK Byte Converter
ascii_7	8859_1
big5	Big5
cp037	Cp037
cp437	Cp437
cp500	Cp500
cp850	Cp850
cp852	Cp852
cp855	Cp855
cp857	Cp857
cp860	Cp860
cp863	Cp863
cp864	Cp864
cp866	Cp866
cp869	Cp869
cp874	Cp874
cp932	Cp932
cp936	Cp936
cp950	Cp950
cp1250	Cp1250
cp1251	Cp1251
cp1252	Cp1252
cp1253	Cp1253
cp1254	Cp1254
cp1255	Cp1255
cp1256	Cp1256
cp1257	Cp1257
cp1258	Cp1258
deckanji	EUCJIS
eucgb	GB2312
eucjis	EUCJIS
eucksc	Cp949
ibm420	Cp420
ibm918	Cp918
iso_1	8859_1
iso88592	8859-2
is088595	8859_5
iso88596	8859_6
iso88597	8859_7
iso88598	8859_8
iso88599	8859_9
iso885915	8859_15
koi8	KOI8_R
mac	Macroman
mac_cyr	MacCyrillic
mac_ee	MacCentralEurope
macgreek	MacGreek
macturk	MacTurkish
sjis (see note)	SJIS
tis620	MS874
utf8	UTF8

European Currency Symbol Support jConnect version 4.1 and later support the use of the new European currency symbol, or "euro" and its conversion to and from UCS-2 Unicode.The e uro has been added to the following Sybase character sets: cp1250, cp1251, cp1252, cp1253, cp1254, cp1255, cp1256, cp1257, cp1258, cp874, iso885915, and utf8.Character sets cp1257, cp1258, and iso885915 are new.To use the euro symbol:

• Use the PureConverter class, a pure Java, multibyte character-set converter. See "jConnect Character-Set Converters" for more information.

• Verify that the new character sets are installed on the server.

The euro symbol is currently supported only on Adaptive Server Enterprise version 11.9.2 and later; Adaptive Server Anywhere does not support the euro symbol.

• Select the appropriate character set on the client. See "Setting the CHARSET Connection Property" for more information.

• Upgrade to JDK 1.1.7 or the Java^TM 2 Platform.

Unsupported Character SetsThe following Sybase character sets are not supported in jConnect 5.0 because no JDK byte converters are analogous to the Sybase character sets:

• cp1047

• euccns

• greek8

• roman8

• turkish8

You can use these character sets with the TruncationConverter class as long as the application uses only the 7-bit ASCII subsets of these characters. Working with Databases

---

This section discusses database issues relevant to jConnect and includes these topics:

• Performing Server-to-Server Remote Procedure Calls

• Accessing Database Metadata

• Using Cursors with Result Sets

• Support for Batch Updates

• Updating the Database from the Result Set of a Stored Procedure

• Sending Image Data

• Using Date and Time Datatypes

Performing Server-to-Server Remote Procedure Calls

---

A Transact-SQL language command or stored procedure running on one server can execute a stored procedure located on another server. The server to which an application has connected logs in to the remote server, and executes a server-to-server remote procedure call.An application can specify a "universal" password for server-to-server communication; that is, a password used in all server-to-server connections. Once the connection is open, the server uses this password to log in to any remote server.By default, jConnect uses the current connection's password as the default password for server-to-server communications. However, if the passwords are different on two servers for the same user and that user is performing server-to-server remote procedure calls, the application must explicitly define passwords for each server it plans to use. jConnect version 4.1 and later include a property that lets you set a universal "remote" password or different passwords on several servers. jConnect lets you set and configure the property using the setRemotePassword( ) method in the SybDriver class:
Properties connectionProps = new Properties();

public final void setRemotePassword(String serverName, String
password, Properties connectionProps)
To use this method, the application needs to import the SybDriver class, then call the method.For jConnect 4.1:
import com.sybase.jdbcx.SybDriver;
SybDriver sybDriver = (SybDriver)
Class.forName("com.sybase.jdbc.SybDriver").newInstance();
sybDriver.setRemotePassword
(serverName, password, connectionProps);
For jConnect 5.0:
import com.sybase.jdbcx.SybDriver;
SybDriver sybDriver = (SybDriver)
Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
sybDriver.setRemotePassword
(serverName, password, connectionProps);

Note	To set different remote passwords for various servers, repeat the preceding call (appropriate for your version of jConnect) for each server.

This call adds the given server name-password pair to the given Properties object, which can be passed by the application to DriverManager in DriverManager.getConnection (server_url, props).If serverNameis NULL, the universal password will be set to password for subsequent connections to all servers except the ones specifically defined by previous calls to setRemotePassword( ).When an application sets the REMOTEPWD property, jConnect no longer sets the default universal password. Accessing Database Metadata

---

To support JDBC DatabaseMetaData methods, Sybase provides a set of stored procedures that jConnect can call for metadata about a database. These stored procedures must be installed on the server for the JDBC metadata methods to work. If the stored procedures for providing metadata are not already installed in a Sybase server, you can install them using one of two stored procedure scripts provided with jConnect:

• sql_server.sql installs stored procedures on an Adaptive Server database.

• sql_anywhere.sql installs stored procedures on an Adaptive Server Anywhere database.

Note	The most recent version of these scripts is compatible with all versions of jConnect.

See the Sybase jConnect for JDBC Installation Guide and Release Bulletin for complete instructions on installing stored procedures.In addition, to use the metadata methods, you must set the USE_METADATA connection property to "true" (its default value) when you establish a connection.You cannot get metadata about temporary tables in a database.

Note	The DatabaseMetaData.getPrimaryKeys( ) method finds primary keys declared in a table definition (CREATE TABLE) or with alter table (ALTER TABLE ADD CONSTRAINT). It does not find keys defined using sp_primarykey.

Server-Side Metadata Installation Metadata support can be implemented in either the client (ODBC, JDBC) or in the data source (server stored procedures). jConnect provides metadata support on the server, which results in the following benefits:

• Maintains jConnect's small size, which ensures the driver can be quickly downloaded from the Internet.

• Gains runtime efficiency from preloaded stored procedures on the data source.

Using Cursors with Result Sets

---

jConnect 5.0 implements many JDBC 2.0 cursor and update methods. These methods make it easier to use cursors and to update rows in a table based on values in a result set.

Note

To have full JDBC 2.0 support, use jConnect version 5.0 or later. jConnect version 4.x provides some JDBC 2.0 features via Sybase extensions and the ScrollableResultSet.java sample found in the sample subdirectory under your jConnect directory. See the com.sybase.jdbcx and the sample packages for the javadocs on these methods.

In JDBC 2.0, ResultSets are characterized by their type and their concurrency. The type and concurrency values are part of the java.sql.ResultSet interface and are described in its javadocs. Table 2-5 identifies the characteristics of java.sql.ResultSet that are available in jConnect 5.0.

Table 2-5:   java.sql.ResultSet options available in jConnect 5.0

Concurrency	Type
	TYPE_FORWARD_ONLY	TYPE_SCROLL_INSENSITIVE	TYPE_SCROLL_SENSITIVE
CONCUR_READ_ONLY	Supported in 5.0	Supported in 5.0	Not available in 5.0
CONCUR_UPDATABLE	Supported in 5.0	Not available in 5.0	Not available in 5.0

This section includes the following topics:

• Creating a Cursor

• Positioned Updates and Deletes Using JDBC 1.x Methods

• Using a Cursor with a PreparedStatement

• Support for SCROLL_INSENSITIVE Result Sets in jConnect

Creating a Cursor To create a cursor using jConnect 4.x, use either SybStatement.setCursorName( ) or SybStatement.setFetchSize( ). When you use SybStatement.setCursorName( ), you explicitly assign the cursor a name. The signature for SybStatement.setCursorName( ) is:
void setCursorName(String name) throws SQLException;
You use SybStatement.setFetchSize( ) to create a cursor and specify the number of rows returned from the database in each fetch. The signature for SybStatement.setFetchSize( ) is:
void setFetchSize(int rows) throws SQLException;
When you use setFetchSize( ) to create a cursor, the jConnect driver names the cursor. To get the cursor's name, use ResultSet.getCursorName( ). You create cursors in jConnect version 5.0 the same way as in version 4.x, but because version 5.0 supports JDBC 2.0, there is another way to create cursors. You can specify which kind of ResultSet you want returned by the statement, using the following JDBC 2.0 method on the connection:
Statement createStatement(int resultSetType, int resultSetConcurrency)throws SQL Exception
The type and concurrency correspond to the types and concurrences found on the ResultSet interface listed in Table 2-5. If you request an unsupported ResultSet, a SQL warning is chained to the connection. When the returned Statement is executed, you will receive the kind of ResultSet that is most like the one you requested. See the JDBC 2.0 specification for more details on this method's behavior.If you do not use createStatement( ), or you are using jConnect version 4.x, the default types of ResultSet are:

• If you call only Statement.executeQuery( ), then the ResultSet returned is a SybResultSet that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

• If you call setFetchSize( ) or setCursorName( ), then the ResultSet returned from executeQuery( ) is a SybCursorResultSet that is TYPE_FORWARD_ONLY and CONCUR_UPDATABLE.

To verify that the kind of ResultSet object is what you intended, the JDBC 2.0 API for ResultSet has added two methods:
int getConcurrency() throws SQLException;
int getType() throws SQLException;
The basic steps for creating and using a cursor are:

1. Create the cursor using Statement.setCursorName( ) or SybStatement.setFetchSize( ).

1. Invoke Statement.executeQuery( ) to open the cursor for a statement and return a cursor result set.

1. Invoke ResultSet.next( ) to fetch rows and position the cursor in the result set.

The following example uses each of the two methods for creating cursors and returning a result set. It also uses ResultSet.getCursorName( ) to get the name of the cursor created by SybStatement.setFetchSize( ).

// With conn as a Connection object, create a
// Statement object and assign it a cursor using
// Statement.setCursorName().
Statement stmt = conn.createStatement();
stmt.setCursorName("author_cursor");

// Use the statement to execute a query and return
// a cursor result set.
ResultSet rs = stmt.executeQuery("SELECT au_id,
au_lname, au_fname FROM authors
WHERE city = 'Oakland'");
while(rs.next())
{
...
}

// Create a second statement object and use
// SybStatement.setFetchSize()to create a cursor
// that returns 10 rows at a time.
SybStatement syb_stmt = conn.createStatement();
syb_stmt.setFetchSize(10);

// Use the syb_stmt to execute a query and return
// a cursor result set.
SybCursorResultSet rs2 =
(SybCursorResultSet)syb_stmt.executeQuery("SELECT
au_id, au_lname, au_fname FROM authors
WHERE city = 'Pinole'");
while(rs2.next())
{
...
}

// Get the name of the cursor created through the
// setFetchSize() method.
String cursor_name = rs2.getCursorName();
...
// For jConnect 5.0, create a third statement
// object using the new method on Connection,
// and obtain a SCROLL_INSENSITIVE ResultSet.
// Note: you no longer have to downcast the
// Statement or the ResultSet.
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs3 = stmt.executeQuery
("SELECT ... [whatever]");
// Execute any of the JDBC 2.0 methods that
// are valid for read only ResultSets.
rs3.next();
rs3.previous();
rs3.relative(3);
rs3.afterLast();
...
Positioned Updates and Deletes Using JDBC 1.x Methods The following example shows how to use methods in JDBC 1.x to do a positioned update. The example creates two Statement objects, one for selecting rows into a cursor result set, and the other for updating the database from rows in the result set.
// Create two statement objects and create a cursor
// for the result set returned by the first
// statement, stmt1. Use stmt1 to execute a query
// and return a cursor result set.
Statement stmt1 = conn.createStatement();
Statement stmt2 = conn.createStatement();
stmt1.setCursorName("author_cursor");
ResultSet rs = stmt1.executeQuery("SELECT
au_id,au_lname, au_fname
FROM authors WHERE city = 'Oakland'
FOR UPDATE OF au_lname");

// Get the name of the cursor created for stmt1 so
// that it can be used with stmt2.
String cursor = rs.getCursorName();

// Use stmt2 to update the database from the
// result set returned by stmt1.
String last_name = new String("Smith");
while(rs.next())
{
if (rs.getString(1).equals("274-80-9391"))
{
stmt2.executeUpdate("UPDATE authors "+
"SET au_lname = "+last_name +
"WHERE CURRENT OF " + cursor);
}
}
Deletions in a Result SetThe following example uses Statement object stmt2, from the preceding code, to perform a positioned deletion:
stmt2.executeUpdate("DELETE FROM authors
WHERE CURRENT OF " + cursor);
Positioned Updates and Deletes Using JDBC 2.0 MethodsThis section discusses JDBC 2.0 methods for updating columns in the current cursor row and updating the database from the current cursor row in a result set. They are followed by an example. Updating Columns in a Result SetJDBC 2.0 specifies a number of methods for updating column values from a result set in memory, on the client. The updated values can then be used to perform an update, insert, or delete operation on the underlying database. All of these methods are implemented in the SybCursorResultSet class.Examples of some of the JDBC 2.0 update methods available in jConnect are:
void updateAsciiStream(String columnName, java.io.InputStream x,
int length) throws SQLException;
void updateBoolean(int columnIndex, boolean x) throws
SQLException;
void updateFloat(int columnIndex, float x) throws SQLException;
void updateInt(String columnName, int x) throws SQLException;
void updateInt(int columnIndex, int x) throws SQLException;
void updateObject(String columnName, Object x) throws
SQLException;
Methods for Updating the Database from a Result SetJDBC 2.0 specifies two new methods for updating or deleting rows in the database, based on the current values in a result set. These methods are simpler in form than Statement.executeUpdate( ) in JDBC 1.x and do not require a cursor name. They are implemented in SybCursorResultSet:
void updateRow() throws SQLException;
void deleteRow() throws SQLException;

Note	The concurrency of the result set must be CONCUR_UPDATABLE, otherwise the above methods will raise an exception. For insertRow( ), all table columns that require non-null entries must be specified. Methods provided on DatabaseMetaData dictate when these changes are visible.

ExampleThe following example creates a single Statement object that is used to return a cursor result set. For each row in the result set, column values are updated in memory and then the database is updated with the row's new column values.
// Create a Statement object and set fetch size to
// 25. This creates a cursor for the Statement
// object Use the statement to return a cursor
// result set.
SybStatement syb_stmt =
(SybStatement)conn.createStatement();
syb_stmt.setFetchSize(25);
SybCursorResultSet syb_rs =
(SybCursorResultSet)syb_stmt.executeQuery(
"SELECT * from T1 WHERE ...")

// Update each row in the result set according to
// code in the following while loop. jConnect
// fetches 25 rows at a time, until fewer than 25
// rows are left. Its last fetch takes any
// remaining rows.
while(syb_rs.next())
{
// Update columns 2 and 3 of each row, where
// column 2 is a varchar in the database and
// column 3 is an integer.
syb_rs.updateString(2, "xyz");
syb_rs.updateInt(3,100);
//Now, update the row in the database.
syb_rs.updateRow();
}
// Create a Statement object using the
// JDBC 2.0 method implemented in jConnect 5.0
Statement stmt = conn.createStatement
(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
// Use the Statement to return an updatable ResultSet
ResultSet rs = stmt.executeQuery("SELECT * FROM T1 WHERE...");
// In jConnect 5.0, downcasting to SybCursorResultSet is not
// necessary. Update each row in the ResultSet in the same
// manner as above
while (rs.next())
{
rs.updateString(2, "xyz");
rs.updateInt(3,100);
rs.updateRow();
}
Deleting a Row from a Result SetTo delete a row from a cursor result set, you can use SybCursorResultSet.deleteRow( ) as follows:

while(syb_rs.next())
{
int col3 = getInt(3);
if (col3 >100)
{
syb_rs.deleteRow();
}
}
Inserting a Row Into a Result SetThe following example illustrates how to do inserts using the JDBC 2.0 API, which is only available in jConnect 5.0. There is no need to downcast to a SybCursorResultSet.
// prepare to insert
rs.moveToInsertRow();
// populate new row with column values
rs.updateString(1, "New entry for col 1");
rs.updateInt(2, 42);
// insert new row into db
rs.insertRow();
// return to current row in result set
rs.moveToCurrentRow();
Using a Cursor with a PreparedStatement Once you create a PreparedStatement object, you can use it multiple times with the same or different values for its input parameters. If you use a cursor with a PreparedStatement object, you need to close the cursor after each use and then reopen the cursor to use it again. A cursor is closed when you close its result set (ResultSet.close( )). It is opened when you execute its prepared statement (PreparedStatement.executeQuery( )). The following example shows how to create a PreparedStatement object, assign it a cursor, and execute the PreparedStatement object twice, closing and then reopening the cursor.
// Create a prepared statement object with a
// parameterized query.
PreparedStatement prep_stmt =
conn.prepareStatement(
"SELECT au_id, au_lname, au_fname "+
"FROM authors WHERE city = ? "+
"FOR UPDATE OF au_lname");

//Create a cursor for the statement.
prep_stmt.setCursorName("author_cursor");

// Assign the parameter in the query a value.
// Execute the prepared statement to return a
// result set.
prep_stmt.setString(1, "Oakland");
ResultSet rs = prep_stmt.executeQuery();

//Do some processing on the result set.
while(rs.next())
{
...
}

// Close the result, which also closes the cursor.
rs.close();

// Execute the prepared statement again with a new
// parameter value.
prep_stmt.setString(1,"San Francisco");
rs = prep_stmt.executeQuery();
// reopens cursor
Support for SCROLL_INSENSITIVE Result Sets in jConnect jConnect version 5.0 supports only TYPE_SCROLL_INSENSITIVE result sets.jConnect uses the Tabular Data Stream (TDS)--Sybase's proprietary protocol--to communicate with Sybase database servers. As of jConnect 5.0, TDS does not support scrollable cursors. To support scrollable cursors, jConnect caches the row data on demand, on the client, on each call to ResultSet.next( ). However, when the end of the result set is reached, the entire result set is stored in the client's memory. Because this may cause a performance strain, we recommend that you use TYPE_SCROLL_INSENSITIVE result sets only when the result set is reasonably small.

Note	When you use TYPE_SCROLL_INSENSITIVE ResultSets in jConnect 5.0, you can only call the isLast( ) method after the last row of the ResultSet has been read. Calling isLast( ) before the last row is reached will cause an UnimplementedOperationException to be thrown.

A sample has been added to jConnect version 4.x that provides a limited TYPE_SCROLL_INSENSITIVE ResultSet using JDBC 1.0 interfaces.This implementation uses standard JDBC 1.0 methods to produce a scroll-insensitive, read-only result set; that is, a static view of the underlying data that is not sensitive to changes made while the result set is open. ExtendedResultSet caches all of the ResultSet rows on the client. Be cautious when you use this class with large result sets.The sample.ScrollableResultSet interface:

• Is an extension of JDBC 1.0 java.sql.ResultSet.

• Defines additional methods that have the same signatures as the JDBC 2.0 java.sql.ResultSet.

• Does not contain all of the JDBC 2.0 methods. The missing methods deal with modifying the ResultSet.

The methods from the JDBC 2.0 API that are defined are:
boolean previous() throws SQLException;
boolean absolute(int row) throws SQLException;
boolean relative(int rows) throws SQLException;
boolean first() throws SQLException;
boolean last() throws SQLException;
void beforeFirst() throws SQLException;
void afterLast() throws SQLException;
boolean isFirst() throws SQLException;
boolean isLast() throws SQLException;
boolean isBeforeFirst() throws SQLException;
boolean isAfterLast() throws SQLException;
int getFetchSize() throws SQLException;
void setFetchSize(int rows) throws SQLException;
int getFetchDirection() throws SQLException;
void setFetchDirection(int direction) throws SQLException;
int getType() throws SQLException;
int getConcurrency() throws SQLException;
int getRow() throws SQLException;
To use the new sample classes, create an ExtendedResultSet using any JDBC 1.0 java.sql.ResultSet. Below are the relevant pieces of code (assume a Java 1.1 environment):
// import the sample files
import sample.*;
//import the JDBC 1.0 classes
import java.sql.*;
// connect to some db using some driver;
// create a statement and a query;
// Get a reference to a JDBC 1.0 ResultSet
ResultSet rs = stmt.executeQuery(_query);
// Create a ScrollableResultSet with it
ScrollableResultSet srs = new ExtendedResultSet(rs);
// invoke methods from the JDBC 2.0 API
srs.beforeFirst();
// or invoke methods from the JDBC 1.0 API
if (srs.next())
String column1 = srs.getString(1);
Support for Batch Updates

---

Batch updates allow a Statement object to submit multiple update commands as one unit (batch) to an underlying database for processing together.

Note	To use batch updates, you must refresh the SQL scripts in the sp directory under your jConnect installation directory.

See BatchUpdates.java in the sample (jConnect 4.1) and sample2 (jConnect 5.0) subdirectories for an example of using batch updates with Statement, PreparedStatement, and CallableStatement.jConnect also supports dynamic PreparedStatements in batch. Implementation NotesjConnect implements batch updates as specified in the JDBC 2.0 API, except as described below.

• If the JDBC 2.0 standard for implementing BatchUpdateException.getUpdateCounts( ) is modified or relaxed in the future, jConnect will continue to implement the original standard by having BatchUpdateException.getUpdateCounts( ) return an int[ ] length of M < N, indicating that the first M statements in the batch succeeded, that the M+1 statement failed, and M+2..N statements were not executed.

• Batch updates of stored procedures

To call stored procedures in batch (unchained) mode, you must create the stored procedure in unchained mode. For more information, see "Stored Procedure Executed in Unchained Transaction Mode".

• Adaptive Server Enterprise version 11.5.x and later

BatchUpdateException.getUpdateCounts( ) will return only a int[ ] length of zero. The entire transaction is rolled back if an error is encountered, resulting in zero successful rows.

• Adaptive Server Enterprise version 11.0.1

Returns 0 (zero) rows affected for stored procedures.

• SQL Anywhere version 5.5.x

• SQL Anywhere version 5.5.x does not allow you to obtain inserted row counts from stored procedures that contain inserts. For example:

create proc sp_A as insert tableA values (1, 'hello A')
create proc sp_B
as
insert tableA values (1, 'hello A')
update tableA set col1=2
create proc sp_C
as
update tableA set col1=2
delete tableA

Running executeBatch on the preceding stored procedures would result in, respectively:

0 Rows Affected
1 Rows Affected
2 Rows Affected

• There is no support for dynamic PreparedStatements in batch.

• Because SQL Anywhere 5.5.x does not natively support batch updates according to the JDBC 2.0 specification, batch updates are carried out in an executeUpdate loop.

• Batch updates in databases that do not support batch updates

jConnect carries out batch updates in an executeUpdate loop even if your database does not support batch updates. This allows you to use the same batch code, regardless of the database to which you are pointing.

See Sun Microsystems, Inc. JDBC^TM 2.0 API for more details on batch updates. Updating the Database from the Result Set of a Stored Procedure

---

jConnect includes update and delete methods that allow you to get a cursor on the result set returned by a stored procedure. You can then use the cursor's position to update or delete rows in the underlying table that provided the result set. The methods are in SybCursorResultSet:
void updateRow(String tableName) throws SQLException;
void deleteRow(String tableName) throws SQLException;
The tableName parameter identifies the database table that provided the result set.To get a cursor on the result set returned by a stored procedure, you need to use either SybCallableStatement.setCursorName( ) or SybCallableStatement.setFetchSize( ) before you execute the callable statement that contains the procedure. The following example shows how to create a cursor on the result set of a stored procedure, update values in the result set, and then update the underlying table using the SybCursorResultSet.update( ) method:
// Create a CallableStatement object for executing the stored
// procedure.
CallableStatement sproc_stmt =
conn.prepareCall("{call update_titles}");

// Set the number of rows to be returned from the database with
// each fetch. This creates a cursor on the result set.
(SybCallableStatement)sproc_stmt.setFetchSize(10);

//Execute the stored procedure and get a result set from it.
SybCursorResultSet sproc_result = (SybCursorResultSet)
sproc_stmt.executeQuery();

// Move through the result set row by row, updating values in the
// cursor's current row and updating the underlying titles table
// with the modified row values.
while(sproc_result.next())
{
sproc_result.updateString(...);
sproc_result.updateInt(...);
...
sproc_result.updateRow(titles);
}
Sending Image Data

---

jConnect has a TextPointer class with sendData( ) methods for updating an image column in an Adaptive Server Enterprise or Adaptive Server Anywhere database. In earlier versions of jConnect, you had to send image data using the setBinaryStream( ) method in java.sql.PreparedStatement. The TextPointer.sendData( ) methods use java.io.InputStream and greatly improve performance when you send image data to an Adaptive Server database.To obtain instances of the TextPointer class, you can use either of two getTextPtr( ) methods in SybResultSet:
public TextPointer getTextPtr(String columnName)
public TextPointer getTextPtr(int columnIndex)
Public Methods in the TextPointer ClassThe com.sybase.jdbc package contains the TextPointer class. Its public method interface is:
public void sendData(InputStream is, boolean log)
throws SQLException
public void sendData(InputStream is, int length,
boolean log) throws SQLException
public void sendData(InputStream is, int offset,
int length, boolean log) throws SQLException
public void sendData(byte[] byteInput, int offset,
int length, boolean log) throws SQLEXception
sendData(InputStream is, boolean log) - Updates an image column with data in the specified input stream. sendData(InputStream is, int length, boolean log) - Updates an image column with data in the specified input stream. length is the number of bytes being sent. sendData(InputStream is, int offset, int length, boolean log) - Updates an image column with data in the specified input stream, starting at the byte offset given in the offset parameter and continuing for the number of bytes specified in the length parameter.sendData(byte[ ] byteInput, int offset, int length, boolean log) - Updates a column with image data contained in the byte array specified in the byteInput parameter. The update starts at the byte offset given in the offset parameter and continues for the number of bytes specified in the length parameter.Each method has a log parameter. The log parameter specifies whether image data is to be fully logged in the database transaction log. If the log parameter is set to "true," the entire binary image is written into the transaction log. If the log parameter is set to "false," the update is logged, but the image itself is not included in the log. Updating an Image Column with TextPointer.sendData( )To update a column with image data:

• Get a TextPointer object for the row and column that are to be updated.

• Use TextPointer.sendData( ) to execute the update.

The next two sections illustrate these steps with an example. In the example, image data from the file Anne_Ringer.gif is sent to update the pic column of the au_pix table in the pubs2 database. The update is for the row with author ID 899-46-2035. Getting a TextPointer Objecttext and image columns contain timestamp and page-location information that is separate from their text and image data. When data is selected from a text or image column, this extra information is "hidden" as part of the result set. A TextPointer object for updating an image column requires this hidden information, but does not need the image portion of the column data. To get this information, you need to select the column into a ResultSet object and then use SybResultSet.getTextPtr( ) (see the example that follows the next paragraph). SybResultSet.getTextPtr( ) extracts text-pointer information, ignores image data, and creates a TextPointer object.In cases where a column contains a significant amount of image data, selecting the column for one or more rows and waiting to get all the data is likely to be inefficient, since the data is not used. You can shortcut this process by using the set textsize command to minimize the amount of data returned in a packet. The following code example for getting a TextPointer object includes the use of set textsize for this purpose.
/*
* Define a string for selecting pic column data for author ID
* 899-46-2035.
*/
String getColumnData = "select pic from au_pix where au_id = '899-46-2035'";

/*
* Use set textsize to return only a single byte of column data
* to a Statement object. The packet with the column data will
* contain the "hidden" information necessary for creating a
* TextPointer object.
*/
Statement stmt= connection.createStatement();
stmt.executeUpdate("set textsize 1");

/*
* Select the column data into a ResultSet object--cast the
* ResultSet to SybResultSet because the getTextPtr method is
* in SybResultSet, which extends ResultSet.
*/
SybResultSet rs = (SybResultSet)stmt.executeQuery(getColumnData);

/*
* Position the result set cursor on the returned column data
* and create the desired TextPointer object.
*/
rs.next();
TextPointer tp = rs.getTextPtr("pic");

/*
* Now, assuming we are only updating one row, and won't need
* the minimum textsize set for the next return from the server,
* we reset textsize to its default value.
*/
stmt.executeUpdate("set textsize 0");
Executing the Update with TextPointer.sendDataThe following code uses the TextPointer object from the preceding section to update the pic column with image data in the file Anne_Ringer.gif.
/*
* First, define an input stream for the file.
*/
FileInputStream in = new FileInputStream("Anne_Ringer.gif");

/*
* Prepare to send the input stream without logging the image data
* in the transaction log.
*/
boolean log = false;

/*
* Send the image data in Anne_Ringer.gif to update the pic
* column for author ID 899-46-2035.
*/
tp.sendData(in, log);
See the TextPointers.java sample in the sample (jConnect 4.1) and sample2 (jConnect 5.0) subdirectories under your jConnect installation directory for more information. Using Date and Time Datatypes

---

JDBC uses three temporal datatypes: Time, Date, and Timestamp. Adaptive Server uses only one temporal datatype, datetime, which is equivalent to the JDBC Timestamp datatype. The Adaptive Server datetime datatype supports second resolution to 1/300th of a second. All three JDBC datatypes are treated as datetime datatypes on the server side. A JDBC Timestamp is essentially the same as a server datetime; therefore, no conversion is necessary. However, translating a JDBC Time or Date datatype to or from a server datetime datatype requires a conversion.

• To convert Time to datetime, the date 1 Jan 1970 is added.

• To convert Date to datetime, "00:00:00" is appended.

• To convert a datetime to a Date variable or a Time variable, the unused information is stripped out.

Implementation Notes

• JDBC's Timestamp datatype is not the same as Adaptive Server's timestamp datatype. The Adaptive Server timestamp datatype is a unique varbinary value used when updates are made with an "optimistic concurrency" strategy.

• When a value is inserted as a Time datatype, the date portion is essentially meaningless, so the value should be fetched back using only a Time datatype, never a Date or Timestamp datatype.

• If you use getObject( ) with an Adaptive Server Anywhere date or time column, the value will be returned as a JDBC Timestamp datatype.

Implementing Advanced Features

---

This section describes how to use advanced jConnect features. Using Event Notification

---

You can use the jConnect event notification feature to have your application notified when an Open Server procedure is executed.To use this feature, you must use the SybConnection class, which extends the Connection interface. SybConnection contains a regWatch( ) method for turning event notification on and a regNoWatch( ) method for turning event notification off.Your application must also implement the SybEventHandler interface. This interface contains one public method, void event(String proc_name, ResultSet params), which is called when the specified event occurs. The parameters of the event are passed to event( ) and it tells the application how to respond.To use event notification in your application, call SybConnection.regWatch( ) to register your application in the notification list of a registered procedure. Use this syntax:
SybConnection.regWatch(proc_name,eventHdlr,option)

• proc_name is a String that is the name of the registered procedure that generates the notification.

• eventHdler is an instance of the SybEventHandler class that you implement.

• option is either NOTIFY_ONCE or NOTIFY_ALWAYS. Use NOTIFY_ONCE if you want the application to be notified only the first time a procedure executes. Use NOTIFY_ALWAYS if you want the application to be notified every time the procedure executes.

Whenever an event with the designated proc_name occurs on the Open Server, jConnect calls eventHdlr.event( ) from a separate thread. The event parameters are passed to eventHdlr.event( ) when it is executed. Because it is a separate thread, event notification does not block execution of the application.If proc_name is not a registered procedure, or if Open Server is unable to add the client to the notification list, the call to regWatch( ) throws a SQL exception.To turn off event notification, use this call:
SybConnection.regNoWatch(proc_name)

Note	When you use Sybase event notification extensions, the application needs to call the close( ) method on the connection to remove a child thread created by the first call to regWatch( ). Failing to do so may cause the Virtual Machine to hang when exiting the application.

Event Notification Example The following example shows how to implement an event handler and then register an event with an instance of your event handler, once you have a connection:
public class MyEventHandler implements SybEventHandler
{
// Declare fields and constructors, as needed.
...
public MyEventHandler(String eventname)
{
...
}

// Implement SybEventHandler.event.
public void event(String eventName, ResultSet params)
{
try
{
// Check for error messages received prior to event
// notification.
SQLWarning sqlw = params.getWarnings();
if sqlw != null
{
// process errors, if any
...
}
// process params as you would any result set with
// one row.
ResultSetMetaData rsmd = params.getMetaData();
int numColumns = rsmd.getColumnCount();
while (params.next()) // optional
{
for (int i = 1; i <= numColumns; i++)
{
System.out.println(rsmd.getColumnName(i) + " =
" + params.getString(i));
}
// Take appropriate action on the event. For example,
// perhaps notify application thread.
...
}
}
catch (SQLException sqe)
{
// process errors, if any
...
}
}
}

public class MyProgram
{
...
// Get a connection and register an event with an instance
// of MyEventHandler.
Connection conn = DriverManager.getConnection(...);
MyEventHandler myHdlr = new MyEventHandler("MY_EVENT");

// Register your event handler.
((SybConnection)conn).regWatch("MY_EVENT", myHdlr,
SybEventHandler.NOTIFY_ALWAYS);
...
} conn.regNoWatch("MY_EVENT");
conn.close();
Handling Error Messages

---

jConnect provides two classes for returning Sybase-specific error information, SybSQLException and SybSQLWarning, as well as a SybMessageHandler interface that allows you to customize the way jConnect handles error messages received from the server. Retrieving Sybase-Specific Error InformationjConnect provides an EedInfo interface that specifies methods for obtaining Sybase-specific error information. The EedInfo interface is implemented in SybSQLException and SybSQLWarning, which extend the SQLException and SQLWarning classes. SybSQLException and SybSQLWarning contain the following methods:

• public ResultSet getEedParams( );

Returns a one-row result set containing any parameter values that accompany the error message.

• public int getStatus( );

Returns a "1" if there are parameter values, returns a "0" if there are no parameter values in the message.

• public int getLineNumber( );

Returns the line number of the stored procedure or query that caused the error message.

• public String getProcedureName( );

Returns the name of the procedure that caused the error message.

• public String getServerName( );

Returns the name of the server that generated the message.

• public int getSeverity( );

Returns the severity of the error message.

• public int getState( );

Returns information about the internal source of the error message in the server. For use by Sybase Technical Support only.

• public int getTranState( );

Returns one of the following transaction states:

• 0 The connection is currently in an extended transaction.

• 1 The previous transaction committed successfully.

• 3 The previous transaction aborted.

Note that some error messages may be SQLException or SQLWarning messages, without being SybSQLException or SybSQLWarning messages. Your application should check the type of exception it is handling before it downcasts to SybSQLException or SybSQLWarning. Customizing Error Message Handling You can use the SybMessageHandler interface to customize the way jConnect handles error messages generated by the server. Implementing SybMessageHandler in your own class for handling error messages can provide the following benefits:

• "Universal" error handling

Error handling logic can be placed in your error-message handler, instead of being repeated throughout your application.

• "Universal" error logging

Your error-message handler can contain the logic for handling all error logging.

• Remapping of error-message severity, based on application requirements.

Your error-message handler can contain logic for recognizing specific error messages and downgrading or upgrading their severity based on application considerations rather than the server's severity rating. For example, during a cleanup operation that deletes old rows, you might want to downgrade the severity of a message that a row does not exist; you may want to upgrade the severity in other circumstances.

Note	Error-message handlers implementing the SybMessageHandler interface only receive server-generated messages. They do not handle messages generated by jConnect.

When jConnect receives an error message, it checks to see if a SybMessageHandler class has been registered for handling the message. If so, jConnect invokes the messageHandler( ) method. The messageHandler( ) method accepts a SQL exception as its argument, and jConnect processes the message based on what value is returned from messageHandler( ). The error-message handler can:

• Return the SQL exception as is.

• Return a null. As a result, jConnect ignores the message.

• Create a SQL warning from a SQL exception, and return it. This results in the warning being added to the warning-message chain.

• If the originating message is a SQL warning, messageHandler( ) can evaluate the SQL warning as urgent and create and return a SQL exception to be thrown once control is returned to jConnect.

Installing an Error-Message-Handler You can install an error-message-handler implementing SybMessageHandler by calling the setMessageHandler( ) method from SybDriver, SybConnection, or SybStatement. If you install an error-message-handler from SybDriver, all subsequent SybConnection objects inherit it. If you install an error-message-handler from a SybConnection object, it is inherited by all SybStatement objects created by that SybConnection. This hierarchy only applies from the time the error-message-handler object is installed. For example, if you create a SybConnection object, myConnection, and then call SybDriver.setMessageHandler( ) to install an error-message-handler object, myConnection cannot use that object.To return the current error-message-handler object, use getMessageHandler( ). Error-Message-Handler ExampleThe following example uses jConnect version 4.1.
import java.io.*;
import java.sql.*;
import com.sybase.jdbcx.SybMessageHandler;
import com.sybase.jdbcx.SybConnection;
import com.sybase.jdbcx.SybStatement;
import java.util.*;

public class MyApp
{
static SybConnection conn = null;
static SybStatement stmt = null
static ResultSet rs = null;
static String user = "guest";
static String password = "sybase";
static String server = "jdbc:sybase:Tds:192.138.151.39:4444";
static final int AVOID_SQLE = 20001;

public MyApp()
{
try
{
Class.forName("com.sybase.jdbc.SybDriver").newInstance;
Properties props = new Properties();
props.put("user", user);
props.put("password", password);
conn = (SybConnection)
DriverManager.getConnection(server, props);
conn.setMessageHandler(new NoResultSetHandler());
stmt =(SybStatement) conn.createStatement();
stmt.executeUpdate("raiserror 20001 'your error'");

for (SQLWarning sqw = _stmt.getWarnings();
sqw != null;
sqw = sqw.getNextWarning());
{
if (sqw.getErrorCode() == AVOID_SQLE);
{
System.out.println("Error" + sqw.getErrorCode()+
" was found in the Statement's warning list.");
break;
}
}
stmt.close();
conn.close();
}
catch(Exception e)
{
System.out.println(e.getMessage());
e.printStackTrace();
}
}

class NoResultSetHandler implements SybMessageHandler
{
public SQLException messageHandler(SQLException sqe)
{
int code = sqe.getErrorCode();
if (code == AVOID_SQLE)
{
System.out.println("User " + _user + " downgrading " +
AVOID_SQLE + " to a warning");
sqe = new SQLWarning(sqe.getMessage(),
sqe.getSQLState(),sqe.getErrorCode());
}
return sqe;
}
}

public static void main(String args[])
{
new MyApp();
}
Storing Java Objects as Column Data in a Table

---

Some database products make it possible to directly store Java objects as column data in a database. In such databases, Java classes are treated as datatypes, and you can declare a column with a Java class as its datatype. jConnect supports storing Java objects in a database by implementing the setObject( ) methods defined in the PreparedStatement interface and the getObject( ) methods defined in the CallableStatement and ResultSet interfaces. This allows you to use jConnect with an application that uses native JDBC classes and methods to directly store and retrieve Java objects as column data.

Note	To use the getObject( ) and setObject( ) methods, set the jConnect version to VERSION_4 or higher. See "Setting the jConnect Version".

The following sections describe the requirements and procedures for storing objects in a table and retrieving them using JDBC with jConnect:

• Prerequisites for Storing Java Objects As Column Data

• Sending Java Objects to a Database

• Receiving Java Objects from the Database

Note

Currently, Adaptive Server Enterprise does not support storing java objects in tables. Adaptive Server Anywhere 6.0.0 and 6.0.1 are able to store Java objects in a table, with some limitations. See "Release Limitations:Adaptive Server Anywhere Version 6.0 in Chapter 1 of the jConnect for JDBC Installation Guide and Release Bulletin for more information.

Prerequisites for Storing Java Objects As Column Data To store Java objects belonging to a user-defined Java class in a column, three requirements must be met:

• The class must implement the java.io.Serializable interface. This is because jConnect uses native Java serialization and deserialization to send objects to a database and receive them back from the database.

• The class definition must be installed in the destination database.

• The client system must have the class definition in a .class file that is accessible through the local CLASSPATH environment variable.

Sending Java Objects to a Database To send an instance of a user-defined class as column data, use one of the following setObject( ) methods, as specified in the PreparedStatement interface:
void setObject(int parameterIndex, Object x, int targetSqlType,
int scale) throws SQLException;
void setObject(int parameterIndex, Object x, int targetSqlType)
throws SQLException;
void setObject(int parameterIndex, Object x) throws SQLException;
The following example defines an Address class, shows the definition of a Friends table that has an Address column whose datatype is the Address class, and inserts a row into the table.
public class Address implements Serializable
{
public String streetNumber;
public String street;
public String apartmentNumber;
public String city;
public int zipCode;
//Methods
...
}

Create table Friends:
(firstname varchar(30) ,
lastname varchar(30),
address Address,
phone varchar(15))

// Connect to the database containing the Friends table.
Connection conn =
DriverManager.getConnection("jdbc:sybase:Tds:localhost:5000",
"username", "password");

// Create a Prepared Statement object with an insert statement //for updating the Friends table.
PreparedStatement ps = conn.prepareStatement("INSERT INTO
Friends values (?,?,?,?)");

// Now, set the values in the prepared statement object, ps.
// set firstname to "Joan."
ps.setString(1, "Joan");

// Set last name to "Smith."
ps.setString(2, "Smith");

// Assuming that we already have "Joan_address" as an instance
// of Address, use setObject(int parameterIndex, Object x) to
// set the address column to "Joan_address."
ps.setObject(3, Joan_address);

// Set the phone column to Joan's phone number.
ps.setString(4, "123-456-7890");

// Perform the insert.
ps.executeUpdate();
Receiving Java Objects from the Database A client JDBC application can receive a Java object from the database in a result set or as the value of an output parameter returned from a stored procedure.

• If a result set contains a Java object as column data, use one of the following getObject( ) methods in the ResultSet interface to assign the object to a class variable:

Object getObject(int columnIndex) throws SQLException;
Object getObject(String columnName) throws SQLException;

• If an output parameter from a stored procedure contains a Java object, use the following getObject( ) method in the CallableStatement interface to assign the object to a class variable:

Object getObject(int parameterIndex) throws SQLException;
The following example illustrates the use of
ResultSet.getObject(int parameterIndex) to assign an object received in a result set to a class variable. The example uses the Address class and Friends table used in the previous section and presents a simple application that prints a name and address on an envelope.
/*
** This application takes a first and last name, gets the
** specified person's address from the Friends table in the
** database, and addresses an envelope using the name and
** retrieved address.
*/
public class Envelope
{
Connection conn = null;
String firstName = null;
String lastName = null;
String street = null;
String city = null;
String zip = null;

public static void main(String[] args)
{
if (args.length < 2)
{
System.out.println("Usage: Envelope <firstName>
<lastName>");
System.exit(1);
}
// create a 4" x 10" envelope
Envelope e = new Envelope(4, 10);
try
{
// connect to the database with the Friends table.
conn = DriverManager.getConnection(
"jdbc:sybase:Tds:localhost:5000", "username",
"password");
// look up the address of the specified person
firstName = args[0];
lastName = args[1];
PreparedStatement ps = conn.prepareStatement(
"SELECT address FROM friends WHERE " +
"firstname = ? AND lastname = ?");
ps.setString(1, firstName);
ps.setString(2, lastName);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
Address a = (Address) rs.getObject(1);
// set the destination address on the envelope
e.setAddress(firstName, lastName, a);
}
conn.close();
}
catch (SQLException sqe)
{
sqe.printStackTrace();
System.exit(2);
}
// if everything was successful, print the envelope
e.print();
}
private void setAddress(String fname, String lname, Address a)
{
street = a.streetNumber + " " + a.street + " " +
a.apartmentNumber;
city = a.city;
zip = "" + a.zipCode;
}
private void print()
{
// Print the name and address on the envelope.
...
}
}
You can find a more detailed example of HandleObject.java in the sample (jConnect 4.1) and sample2 (jConnect 5.0) subdirectories under your jConnect directory. Handling Restrictions, Limitations, and Deviations from JDBC Standards

---

This section discusses restrictions and limitations that apply to jConnect, including how the jConnect implementation of JDBC deviates from the JDBC 1.x and 2.0 standards. The following topics are covered:

• Making Adjustments for Multithreading

• Using ResultSet.getCursorName( )

• Using setLong( ) with Large Parameter Values

• Using Compute Statements

• Executing Stored Procedures

Making Adjustments for Multithreading

---

If several threads simultaneously call methods on the same Statement, CallableStatement, or PreparedStatement--which we do not recommend-- you have to manually synchronize the calls to the methods on the Statement; jConnect does not do this automatically.For example, if you have two threads operating on the same Statement--one thread sending a query and the other thread processing warnings--you have to synchronize the calls to the methods on the Statement or conflicts may occur. Using ResultSet.getCursorName( )

---

Some JDBC drivers generate a cursor name for any SQL query so that a string can always be returned. However, jConnect does not return a name when ResultSet.getCursorName( ) is called, unless you either

• called setFetchSize( ) or setCursorName( ) on the corresponding Statement, or

• set the SELECT_OPENS_CURSOR connection property to "true," and your query was in the form of SELECT... FOR UPDATE; for example,

select au_id from authors for update
If you do not call setFetchSize( ) or setCursorName( ) on the corresponding Statement, or set the SELECT_OPENS_CURSOR connection property to "true," null is returned.According to the JDBC 2.0 API (chapter 11, "Clarifications"), all other SQL statements do not need to open a cursor and return a name.For more information on how to use cursors in jConnect see "Using Cursors with Result Sets". Using setLong( ) with Large Parameter Values

---

Implementations of the PreparedStatement.setLong( ) method set a parameter value to a SQL BIGINT datatype. Most Adaptive Server databases do not have an 8-byte BIGINT datatype. If a parameter value requires more than 4 bytes of a BIGINT, using setLong( ) may result in an overflow exception. Using Compute Statements

---

jConnect does not support computed rows. Results are automatically cancelled when a query contains a computed row. For example, the following statement is rejected:
SELECT name FROM sysobjects
WHERE type="S" COMPUTE COUNT(name)
To avoid this problem, substitute the following code:
SELECT name from sysobjects WHERE type="S"
SELECT COUNT(name) from sysobjects WHERE type="S"
Executing Stored Procedures

---

• If you execute a stored procedure in a CallableStatement object that represents parameter values as question marks, you get better performance than if you use both question marks and literal values for parameters. Further, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

The following example creates sp_stmt as a CallableStatement object for executing the stored procedure MyProc:

CallableStatement sp_stmt = conn.prepareCall(
"{call MyProc(?,?)}");

The two parameters in MyProc are represented as question marks. You can register one or both of them as output parameters using the registerOutParameter( ) methods in the CallableStatement interface.

In the following example, sp_stmt2 is a CallableStatement object for executing the stored procedure MyProc2.

CallableStatement sp_stmt2 = conn.prepareCall(
{"call MyProc2(?,'javelin')}");

In sp_stmt2, one parameter value is given as a literal value and the other as a question mark. You cannot register either parameter as an output parameter.

• jConnect does not currently support execution of stored procedures with RPC commands using name-binding for parameters. However, you can execute stored procedures using language commands, passing input parameters to them directly from Java variables using the PreparedStatement class. This is illustrated in the following code fragment:

// Prepare the statement
System.out.println("Preparing the statement...");
String stmtString = "exec " + procname + " @p3=?, @p1=?";
PreparedStatement pstmt = con.preparedStatement(stmtString);

// Set the values
pstmt.setString(1, "xyz");
pstmt.setInt(2, 123);

// Send the query
System.out.println("Executing the query...");
ResultSet rs = pstmt.executeQuery();

Related Links