Manual for the Sybase Server JDBC type 4 drivers Sytraks, Syto and Sybelux

Version 1.15
Last Modified: 25. Mar 2009

Table of Content

1. Description

This is a documentation of the Sybase Server JDBC type 4 drivers Sytraks, Syto and Sybelux.

2. Installation

2.1. Using the Driver in a Java Application

The zip file of the driver includes a jar file. The jar file is named like the driver (sytraks.jar, syto.jar or sybelux.jar). You need to add this jar file to your classpath or extract the jar file in the directory of the application.

2.2. Using the driver in an applet

Add the file Sybelux.jar to the Archive attribute of the applet tag, e.g.:

<APPLET CODE="..." Codebase="..." ARCHIVE="Sybelux.jar" WIDTH="100%" HEIGHT="100%">
...
</APPLET>

or extract the jar in the directory of the applet.

Please note: If you want to employ:

1. connection pooling:
   You need to download the JDBC 2.0 Optional Package interface (javax.sql.*) from Sun's website at: http://java.sun.com/products/jdbc/download.html#binary 
2. the DataSource interface:
   You need to download the JNDI 1.2.1 class libraries (javax.naming.*) from Sun's website at: http://java.sun.com/products/jndi/#download if you do not use the JDK 1.3.x. (J2SE version 1.3).

3. Getting Started

3.1. Sybase Server, Java and JDBC Versions

All drivers support the following Server Versions:

• Sybase ASE 11.x
• Sybase ASE 12.x
• Sybase ASA 7.x or higher

3.2. Implemented Features

• JDBC 2.0 core API
• DataSource
• Connection Pooling
• A pooled driver (JDBC 2.0) with internal pooling.
• A pool manager (i-net PLEXA(tm))
• Distributed Transactions (XA)
• JDBCRowSet
• CachedRowSet
• Savepoints on Transactions
• Multiple open ResultSets per Statement
• ResultSet Holdability
• Named Parameters in CallableStatement

3.3. Check Host Name and Port Number of Your Server

This driver works with Sybase Servers that are configured to use the TCP/IP networking protocol. Please verify that your server is currently listening on a TCP/IP port. To make sure that the RDBMS server is listening on the machine name and port number you specified use:

telnet <hostname or ip address> <port number>

If the connection is refused, then the hostname or the port number are incorrect.

3.4. Driver Class Name

The class name of the driver is: com.inet.syb.SybDriver

The class name of the pooled driver is: com.inet.pool.PoolDriver

3.5. JDBC URL Syntax of the Driver

jdbc:inetsyb:hostname:portnumber
jdbc:inetsyb:hostname:portnumber?database=MyDb&language=deutsch -> with properties
jdbc:inetsyb://servername/pipe/pipename -> with named pipes

Example:

jdbc:inetsyb:www.inetsoftware.de:3333
jdbc:inetsyb:localHost:3433
jdbc:inetsyb://MyServer/pipe/sybase/query

3.6. Implemented Driver Properties

There are two ways to put the properties to the driver:

1. append the properties to the URL like this
   
   jdbc:inetsyb:hostname:portnumber?database=MyDb&language=deutsch
   
2. call from method getConnection(String url, Properties info) from the driver manager.

3.7. Connection Example

There are seven examples included in the download zip file order.

ClassicDriver.java

An example to demonstrate how to connect and use the driver with the JDBC 1.22 interface. (This driver requires the JDBC 2.0 interface and a JDK 1.2 or higher)

 PooledDriver.java

This example shows the use of the PoolDriver. A driver with internal connection pooling.

WithDataSource.java

How to create a connection with a datasource.

WithPoolDataSourece.java

How to use the PooledConnection of the driver.

WithPoolManager.java

This demonstrates the use of the PoolManager i-net PLEXA(tm). i-net PLEXA(tm) is an example of a pool manager. i-net PLEXA(tm) comes with i-net SYBELUX(tm).

WithCachedRowSet.java

How to use the CachedRowSet from i-net software (i-net SYBELUX(tm)).

WithJDBCRowSet.java

How to use the JDBCRowSet from i-net software (i-net SYBELUX(tm)).

3.8. Monitored Failover

With the interface FailoverListener it is possible to manage the reconnection process. The FaloverListener receives an event if the Connection was broken, if the reconnection process was successful and if an error occured be the try of reconnect. In depend of the value returned from FailoverListener, a new try of reconnect will be started or not.
The Connection tryes to reconnect to the same server with the same properties. If reconnection was successful, all Statements, PreparedStatements and CallableStatements will be restored. The ResultSets won't be restored. For restore that, you have to executed the Statement again.
For more information see com.inet.pool.PoolManager.addFailoverListener(), com.inet.pool.FailoverListener, com.inet.pool.FailoverEvent and sample.MonitoredFailover.

3.8.1. Configuration via Connection properties

There are multiple possibilities about how to configure the Monitored Failover. It is possible to use the default implementation of the FailoverListener or to use your own implementation. The default FailoverListener is similar to the TimedFailoverListener in the samples package. To every created Connection automatically an instance of the configured FailoverListener will be added.
Set a FailoverListener via Connection properties:

Example URLs:

jdbc:inetpool:inetora:localhost?database=orcl&useDefaultFailoverListener=true&waitTimeToNextAttempt=100&totalTimeOfAttempts=10
jdbc:inetpool:inetora:localhost?database=orcl&failoverListener=test.MyFirtsFailoverListener

3.8.2. Configuration in Java

For more flexibility it is also possible to manage the registered FailoverListener of a Connection in Java. A FailoverListener can be only added to a Connection, that implements the interface TAFConnection. Those Connection can be received from:

• com.inet.pool.PoolDriver
• com.inet.ora.PDataSource
• com.inet.tds.PDataSource
• com.inet.syb.PDataSource
• com.inet.drda.PDataSource

The interface TAFConnection allows you to add and remove a FailoverListener to/from the Connection.

Example:

//receive a Connection
Class.forName("com.inet.pool.PoolDriver");
Connection con = DriverManager.getConnection("jdbc:inetpool:inetora:localhost?database=orcl", "scott", "tiger");

//add a FailoverListener
FailoverListener fl = new sample.MonitoredFailover.CounteredFailoverListener(99);
((TAFConnection)con).addFailoverListener(fl);

//remove a FailoverListener
((TAFConnection)con).removeFailoverListener(fl);

//remove all FailoverListener
((TAFConnection)con).removeAllFailoverListener();

//get all registered FailoverListener
FailoverListener[] fls = getFailoverListener();

4. Named Pipes

Another solution to connect to the Sybase Server are named pipes. Named pipes are working only in the Java VM 1.1.7 or higher and the Java VM 1.2Beta 4 or higher. Named pipes are equal to files with a UNC path. We have tested named pipes only with the Win32 VM from Sun. If you want to use named pipes from another platform you will need to install SMB (server message block) on the client or you will need to install NFS (network file system) on the Sybase Server.
The default pipe of the Sybase Server is "/pipe/sybase/query" but you can change this pipe name in the server manager.

5. Escape Clauses

The driver implements the following escape clauses

5.1. Date and Time

• {d 'yyyy-mm-dd'}
• {t 'hh:mm:ss[.fff]'}
• {ts 'yyyy-mm-dd hh:mm:ss[.fff]'}

5.2. Stored Procedures

• {call storedProcedures('Param1'[,'Param2'][,?][...])}
• {? = call storedProcedures('Param1'[,'Param2'][,?][...])}

Examples:

st = con.createStatement();
st.execute("{call MyProc('value')}");

pr = con.prepareStatement("{call MyProc('value')}");
pr.execute();

pr = con.prepareStatement("{call MyProc( ? )}");
pr.setString("value");
pr.execute();

5.3. Functions

5.3.1. Numeric Functions

• {fn ABS(number)}
• {fn ACOS(float)}
• {fn ASIN(float)}
• {fn ATAN(float)}
• {fn ATAN2(float1,float2)}
• {fn CEILING(number)}
• {fn COS(float)}
• {fn COT(float)}
• {fn DEGREES(number)}
• {fn EXP(float)}
• {fn FLOOR(number)}
• {fn LOG(float)}
• {fn LOG10(float)}
• {fn PI()}
• {fn POWER(number, power)}
• {fn RADIANS(number)}
• {fn RAND(integer)}
• {fn ROUND(number, places)}
• {fn SIGN(number)}
• {fn SIN(float)}
• {fn SQRT(float)}
• {fn TAN(float)}

5.3.2. String functions

• {fn ASCII(string)}
• {fn CHAR(code)}
• {fn CONCAT(string1, string2)}
• {fn DIFFERENCE(string1, string2)}
• {fn INSERT(string1, start, length, string2)}
• {fn LCASE(string)}
• {fn LEFT(string, count)}
• {fn LENGTH(string)}
• {fn LOCATE(string1, string2, start)}
• {fn LTRIM(string)}
• {fn REPEAT(string, count)}
• {fn RIGHT(string, count)}
• {fn RTRIM(string)}
• {fn SOUNDEX(string)}
• {fn SPACE(count)}
• {fn SUBSTRING(string, start, length)}
• {fn UCASE(string)}

5.3.3. Time und Date Functions

• {fn now()}
• {fn curdate()}
• {fn curtime()}
• {fn DAYNAME(datetime)}
• {fn DAYOFMONTH(datetime)}
• {fn DAYOFWEEK(datetime)}
• {fn DAYOFYEAR(datetime)}
• {fn HOUR(datetime)}
• {fn MINUTE(datetime)}
• {fn MONTH(datetime)}
• {fn MONTHNAME(datetime)}
• {fn QUARTER(datetime)}
• {fn SECOND(datetime)}
• {fn WEEK(datetime)}
• {fn YEAR(datetime)}
• {fn TIMESTAMPDIFF(interval, count, datetime)}
• {fn TIMESTAMPADD(interval, datetime, datetime2)}

interval may be one of the following:
SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR

5.4. Outer Join

• {oj table1 LEFT OUTER JOIN table2 ON table1.id = table2.id}
• {oj table1 RIGHT OUTER JOIN table2 ON table1.id = table2.id}

6. Character Converting

By default character converting is disabled. To use character converting in the driver you need to append "charset=YourCharSet" to the url.

Examples: 

"jdbc:inetsyb:localhost:5000?charset=Cp1250"
"jdbc:inetsyb:localhost:5000?charset=" + sun.io.ByteToCharConverter.getDefault().getCharacterEncoding();
"jdbc:inetsyb:localhost:5000?charset=" + System.getProperty("file.encoding");
"jdbc:inetsyb:localhost:5000?charset=" + (new java.io.InputStreamReader(in)).getEncoding();

TIP: The property charset is case-sensitive in JAVA. The name of the character set is also case-sensitive.
You can test the availability of a character set with:

"test string".getBytes( charset );

If this line executes correctly the character set is available in the current VM.

Note: The UTF8 character set is required to store values into UNIVARCHAR and UNICHAR columns.

7. Result Set's Types and Cursors

There is two ways to retrieve data from server:

• the server sends the complete result to the driver
• the server sends the rows in blocks

The first method is effective for a not very large amount of data. In that case data will be complete cached on the client, that can cause out of memory problems. This method is used by the driver if there is no cursor (e.g. if the Result Set's type is TYPE_FORWARD_ONLY and the Result Set's concurrency is CONCUR_READ_ONLY and the flag useCursorsAlways is not set to "true").
An another way is to use a cursor. A serverside cursor is generated if:

• the Result Set's type or concurrency is: TYPE_SCROLL_SENSITIVE / TYPE_SCROLL_INSENSITIVE / CONCUR_UPDATABLE
• the connection property "useCursorsAlways" is set to "true"
• a not null cursor name was set with Statement.setCursorName()
• a fetch size different from 0 was set with Statement.setFetchSize()

A cursor allows to fetch data from the server in blocks that is useful to receive a large amount of data. The number of rows that will be fetched from the database can be set with the method setFetchSize(). By default this value is 128. The fetch size can affect the performance. The "correct" value depends on the data types containing in the table and should be choose experimental for the best performance of your application.
Since Sybase Server doesn't support scrollable cursors the data is cached on the client for TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE Result sets and therefore for a large amount of data is recommended to use Result set of type TYPE_FORWARD_ONLY.

8. Debugging

If you have problems with the driver then you can enable the logging of the driver with:

DriverManager.setLogStream( System.out );

or

DriverManager.setLogWriter( new PrintWriter(System.out) );

You can also use any other PrintStream or PrintWriter to make this output. This can be used if you can not see the default PrintStreams (for example in a Servlet Engine or an Application Server).

PrintStream ps = new PrintStream( new FileOutputStream( "c:\\driver.log" ) );
DriverManager.setLogStream( ps );

9. Known problems

1. The method getTableName() in the ResultSetMetaData requires a "for browse" statement for ASE earlier than 12.5.
2. The following methods are not supported by this release:
   • PrepareStatement.setArray (int i, Array x)
   • PrepareStatement.setRef (int i, Ref x)
   • PrepareStatement.setURL (int i, URL x)
   • CallableStatement.getObject (int i, java.util.Map map)
   • CallableStatement.getRef (int i)
   • CallableStatement.getArray (int i)
   • CallableStatement.getURL (int i)
   • DatabaseMetaData.getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types)
   • DatabaseMetaData.getAttributes (String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern)
   • ResultSet.getObject (int i, java.util.Map map)
   • ResultSet.getRef (int i)
   • ResultSet.getArray (int i)
   • ResultSet.getURL (int i )
   • ResultSet.updateArray (int i, Array x)
   • ResultSet.updateRef (int i, Ref x)
3. Some ASA7 version are incompatible with driver. Solution is updating to latest ASA7 version. Known working versions:7.0.4.3541, 7.0.3.2046. Not working versions:7.0.3.2054

10. Support

If you have any problems then send an email providing as much information of what occured or happened as possible to <Sybase_driver>@inetsoftware.de where <Sybase_driver> is Sybelux, Syto or Sytraks.

You can find more info about our support at: http://www.inetsoftware.de/support/

11. Changes

11.1. Changes in version 1.04. (Sybelux 1.01)

• Wide table support for Adaptive Server version 12.5 has been added.
• Character and binary columns can contain more than 255 bytes of data
• Tables can now contain 1,024 columns
• You can send and retrieve up to 2,048 parameters
• The ResultSetMetaData methods getCatalogName, getSchemaName, getColumnLabel and getTableName return useful information
• The data types UNICHAR and UNIVARCHAR for Adaptive Server version 12.5 are now supported.
• The object type of an output parameter returned by CallableStatement is corresponded now to the registered typ.
• The mapping from a string value to the target sql typ in the method PreparedStatement.setObject(int, Object, int, int) is now performed.
• The bug with executing a batch of stored procedures was fixed.
• The bug when BigDecimal values in Prepared and CallableStatement were incorrect stored was fixed.
• The connection property "logging" was added.
• the methods getCharset() / setCharset() were added to com.inet.syb.SybDataSource

11.2. Changes in version 1.06. (Sybelux 1.03)

• A NullPointerException with duplicate call of Statement.close() was fixed.
• Support for Types.BLOB and Types.CLOB was added to the method ResultSet.setNull()
• A bug with string data larger 16384 and ASE 12.5 was fixed.
• A bug with binary data larger 16384 and ASE 12.5 was fixed.
• A bug with the UTF8 converter from Java was compensated. We added our own converter.
• The method getGeneratedKeys() was implemented.
• A bug with SP's and parameters was fixed (Procedure xxx expects parameter @yyy, which was not supplied.).
• The call setObject( x, value, Types.BIT ) accepts numeric Strings now.
• A bug with PreparedStatement.getMetaData() was fixed.
• A bug that column aliases was ignored was fixed.
• The return value of getDatabaseProductName() was changed.

11.3. Changes in version 1.07.

• Support for varchar > 255 and SP's was added.
• getObject(int,Map) calls getObject(int) now.

11.4. Changes in version 1.08.

• A bug in XAResource.commit() was fixed
• "Logging" property added for XDataSource
• XAConnection supported
• The properties "loginTimeOut" and "queryTimeout" were added.
• DatabaseMetaData now also returns the last 4 columns (SCOPE_CATLOG, SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE) defined by JDBC API.
  The value of them is always null, because Sybase database does not provide this information.
• If logging property is true and in DriverManager was no logStream set, now the driver writes to System.out
• A timeout of 5 seconds was added to Connetion.isClosed() to reduce the risk of hanging.
• Support for quoted SP's with group number was added.
• Method DatabaseMetaData.getColumns now supports also Sybase SQL Server 10.0
• Fixes in isAfterLast and isBeforeFirst for empty Resultsets
• Method DatabaseMetaData.getProcedureColumns now returns exactly 13 columns.
• Clob and Blob support in setObject methods.
• PreparedStatements with setObject(int,null) now does not throw type mismatch Exception in execute() in the attempt to set this for character columns.

11.5. Changes in version 1.09.

• The methods setAppname() and getAppname() were added to the datasources.
• The rounding for Timestamp (DATETIME) values was changed for reading and writing.
• The bug that reading result sets from ASA 9 would fail was fixed.
• The bug that the server would hang up on using PreparedStatement.setUnicodeStream was fixed.
• The bug that metadata requests would throw an Exception if there was no match (with ASA), was fixed.
• An ArrayIndexOutOfBoundsException with the setXXX() methods of the RowSets when the parameters were not set in numerical order was fixed.
• Added the property chained.
• The bug that a char column could not be converted to a Date properly was fixed.

11.6. Changes in version 1.10.

• A bug that case sensitive charsets was ignored was fixed.

11.7. Changes in version 1.11.

• The bug that DatabaseMetadata.getBestRowIdentifier() returned a ResultSet with an empty 9th column is fixed.
• ResultSet.getXXX(String) now throws a SQLException instead of a NullPointerException if the parameter is null.
• A bug in DatabaseMetadata causing a NullPointerException (e.g. getTables() ) has been fixed.
• The com.inet.pool.TimedFailoverListener / DefaultFailoverListener calculated the end of reconnect process too early.
• A bug that DatabaseMetadata.getProcedureColumns() returned wrong data type info for the columns for ASA7 has been fixed. (date/time/datetime/unsigned smallint/unsigned bigint)
• A bug that an ArithmeticException was thrown when Connection.isClosed() was called has been fixed.
• A bug of ASA7 returning stored procedure columns in wrong order has been fixed.
• ASA9 has the same bug returning wrong data types for columns calling DatabaseMetadata.getProcedureColumns() and possible in wrong order. This bug has been fixed for versions up to ASA9 too.
• A bug that produce the message "Unknown Parameter type:..." with CallableStatements was fixed. The problem occur on reexecuting the CallableStatement without reregistering the output parameters.
• The method DatabaseMetaData.getAttributes() now returns an empty ResultSet instead of throwing a SQLException.

11.8. Changes in version 1.12

• An ArrayIndexOutOfBoundsException in DatabaseMetaData.getProcedureColumns() with ASA7 was fixed.
• The methods of DatabaseMetadata with the catalog parameter now returns the correct result instead of an Exception if the catalog parameter is different from the Connection catalog.
• Fixed problem with FOR UPDATE clause. If Statement.setMaxFieldSize was called with this Statement Object, execution failed.
• Behaviour change: ResultSet.update___ now throws immediately a SQLExecption if it is read only.
• nNew JDBC URL property "impltran".

11.9. Changes in version 1.13

• CallableStatement.getXXX() could cause a NumberFormatException because the wrong type information (of the type set with registerOutputParameter()) was used.

11.10. Changes in version 1.14 (14. Mar 2007)

• DatabaseMetaData.getProcedureColumns() returned wrong column data type information for datetime and univarchar parameters with ASE 12.

11.11. Changes in version 1.15 (25. Mar 2009)

• Instances of PDataSource could not have been serialized.

12. Copyright

Copyright by i-net software
More info and updates can be found at http://www.inetsoftware.de

© 2000-2009 i-net software

• i-net Clear Reports Reporting
• /
• Use i-net JDBC Drivers. Get connected.
• /
• i-net HelpDesk - die Service-Management Lösung für den Mittelstand.
• /
• FAQ & Knowledge-Base