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

Version 1.15
Last Modified: 25. Mar 2009

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

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.

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.

All drivers support the following Server Versions:

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

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

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.

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

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

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

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.

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

ClassicDriver.javaAn 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.javaThis example shows the use of the PoolDriver. A driver with internal connection pooling.WithDataSource.javaHow to create a connection with a datasource.WithPoolDataSourece.javaHow to use the PooledConnection of the driver.WithPoolManager.javaThis demonstrates the use of the PoolManager i-net PLEXA™. i-net PLEXA™ is an example of a pool manager. i-net PLEXA™ comes with i-net SYBELUX™.WithCachedRowSet.javaHow to use the CachedRowSet from i-net software (i-net SYBELUX™).WithJDBCRowSet.javaHow to use the JDBCRowSet from i-net software (i-net SYBELUX™).

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.

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

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();

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.

The driver implements the following escape clauses

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

• {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();

• {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)}

• {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)}

• {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

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

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.

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.

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 );

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

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/

• 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

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

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

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

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

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

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

• 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”.

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

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

• Instances of PDataSource could not have been serialized.

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

© 2000-2009 i-net software