Chapter 2 Programming Information

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:

Use the DriverManager.getConnection( ) method in your application.
When you define the URL.

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 String.equalsIgnoreCase(String) method to compare property names).

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
BE_AS_JDBC_COMPLIANT_ AS_POSSIBLE	Adjusts other properties to ensure that jConnect methods respond in a way that is as compliant as possible with the JDBC 2.0 standard. These properties are affected (and overridden) when this property is set to true: CANCEL_ALL (set to false) LANGUAGE CURSOR (set to true for jConnect 4.x, set to false for jConnect 5.x) SELECT_OPENS_CURSOR (set to true) FAKE_METADATA (set to true) GET_BY_NAME_USES_COLUMN_LABEL (set to false)	false
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
FAKE_METADATA	When you call the ResultSetMetaData methods getCatalogName, getSchemaName, and getTableName and this property is set to true, the call will return empty strings ("") because the server does not supply useful metadata. When this property is set to false, calling these methods throws a "Not Implemented" SQLException. If you have enabled wide tables and are using an Adaptive Server 12.5 or later, this property setting is ignored, because the server does supply useful metadata.	true
GET_BY_NAME_USES_ COLUMN_LABEL	Provides backward compatibility with versions of jConnect previous to 4.5/5.5. With Adaptive Server Enterprise version 12.5, jConnect has access to more metadata than was previously available. Previous to version 12.5, column name and column alias meant the same thing. jConnect can now differentiate between the two when used with a 12.5 or later Adaptive Server with wide tables enabled. To preserve backward compatibility, set this property to true.If you want calls to getByte, getInt, get* (String columnName) to look at the actual name for the column (called for in the JDBC 2.0 specification), set this property to false.	true
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
IGNORE_DONE_IN_PROC	When set to true, intermediate update results (as in stored procedures) are not returned, only the final result set.	false
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".	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 setXXX methods 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.	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
REQUEST_HA_SESSION	This property indicates whether the connecting client wants to begin a HA failover session with a version 12 or later Adaptive Server configured for failover. Setting this property to true causes jConnect to attempt a failover login. If you do not set this connection property, a failover session will not start, even if the server is configured for failover. You cannot reset the property once a connection has been made.If you want more flexibility for requesting failover sessions, code the client application to set REQUEST_HA_SESSION at runtime.	false
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. 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
SERIALIZE_REQUESTS	If set to true, jConnect waits for responses from the server before sending additional requests.	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 an http-tunnelled session (created using the jConnect TDS-tunnelling servlet) will remain alive while idle. After the specified time, the connection will be automatically closed. For more information about the TDS-tunnelling servlet, see page §.	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. jConnect uses DatabaseMetaData for some features, including Distributed Transaction Management support (JTA/JTS) and dynamic class loading (DCL). If you receive error 010SJ, which indicates that your application requires metadata, install the stored procedures for returning metadata that come with jConnect (see "Installing Stored Procedures" in Chapter 3 of the jConnect for JDBC Installation Guide).	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:port from the "query" entry.

You can connect to a specific database using this format:

jdbc:sybase:Tds:host:port/database

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

Example

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

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 or 7.x.

Connecting to Adaptive Server Anywhere 5.x.x

If you have to connect to Adaptive Server Anywhere version 5.x.x via jConnect, you must run the Adaptive Server Anywhere Open Server Gateway dbos50, which is distributed with Adaptive Server Anywhere.

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.

Install Open Server Gateway 5.5.x3 or later and the Open Server DLLs. Use Open Server DLLs, version 11.1.
Add an entry for the gateway to your %SYBASE%\ini\sql.ini file (using, for example, sqledit).
Start the gateway by entering:
```
start dbos50 gateway-demo 
```
where gateway-demo is the gateway name defined in step 2.
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.

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

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

When 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 (see Example 1 on page 23).

You can use the alias name for to reference the attribute instead of the OID. See Example 2 on page 24.

Table 2-3 shows the required formatting.

Table 2-3: Directory service information for JNDI
Attribute description	Alias	OID (object_id)
Interfaces entry replacement in LDAP directory services	sybaseServer	1.3.6.1.4.1.897.4.1.1
Collection point for sybaseServer LDAP attributes	sybaseServer	1.3.6.1.4.1.897.4.2
Version Attribute	sybaseVersion	1.3.6.1.4.1.897.4.2.1
Servername Attribute	sybaseServer	1.3.6.1.4.1.897.4.2.2
Service Attribute	sybaseService	1.3.6.1.4.1.897.4.2.3
Status Attribute	sybaseStatus	1.3.6.1.4.1.897.4.2.4
Address Attribute	sybaseAddress	1.3.6.1.4.1.897.4.2.5
Security Mechanism Attribute	sybaseSecurity	1.3.6.1.4.1.897.4.2.6
Retry Count Attribute	sybaseRetryCount	1.3.6.1.4.1.897.4.2.7
Loop Delay Attribute	sybaseRetryDelay	1.3.6.1.4.1.897.4.2.8
jConnect Connection Protocol	sybaseJconnectProtocol	1.3.6.1.4.1.897.4.2.9
jConnect Connection Property	sybaseJconnectProperty	1.3.6.1.4.1.897.4.2.10
Database Name	sybaseDatabasename	1.3.6.1.4.1.897.4.2.11
High Availability Failover Servername Attribute	sybaseHAservername	1.3.6.1.4.1.897.4.2.15
ResourceManager Name	sybaseResourceManagerName	1.3.6.1.4.1.897.4.2.16
ResourceManager Type	sybaseResourceManagerType	1.3.6.1.4.1.897.4.2.17
JDBCDataSource Interface	sybaseJdbcDataSource- Interface	1.3.6.1.4.1.897.4.2.18
ServerType	sybaseServerType	1.3.6.1.4.1.897.4.2.19

Attributes in italics are required.

The following examples show connection information entered for the database server SYBASE11 under an LDAP directory service. Example 1 uses the attribute's OID, Example 2 uses the attribute's alias, which is case insensitive. You can use either the OID or the alias.

Example 1

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

Example 2

dn: servername=SYBASE11,o=MyCompany,c=US
 servername:SYBASE11
 sybaseAddress:TCP#1#giotto  1266
 sybaseAddress:TCP#1#giotto  1337
 sybaseAddress:TCP#1#standby1  4444
 sybaseJconnectProperty:REPEAT_READ=false&PACKETSIZE=1024
 sybaseJconnectProperty:CONNECTION_FAILOVER=true
 sybaseDatabasename:pubs2
 sybaseJconnectProtocol:Tds

In these examples, 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. You do not need to specify a connection protocol, but if you do, you must enter the attribute as "Tds ", not "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#standby  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);

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.