Connection properties for Ianywhere JDBC driver

[ThiagoReis]

Hello,

My server is a SA 11.0.1.2627 on Windows 2008 R2x64.

After running "SET OPTION PUBLIC.timestamp_format='YYYY-MM-DD HH:NN:SS.SSS'" I could check that this default value is not working for applications based on iAnywhere JDBC Driver (SAJDBC.JAR).

For some reason, when I check SA_CONN_PROPERTIES the value is 'yyyy-mm-dd hh:nn:ss.ssssss'.

From Sybase Central I can confirm that the "timestamp_format" value for PUBLIC is 'YYYY-MM-DD HH:NN:SS.SSS' and the "timestamp_format" for the specific user is 'YYYY-MM-DD HH:NN:SS.SSS' too.

I´ve checked SP_LOGIN_ENVIRONMENT, SA_POST_LOGIN_PROCEDURE and SP_TSQL_ENVIRONMENT and none of these procedures are setting 'yyyy-mm-dd hh:nn:ss.ssssss'.

For connections based on other drivers, the timestamp_format is correct ('YYYY-MM-DD HH:NN:SS.SSS').

The same problem is happening with ISOLATION_LEVEL. I´ve set PUBLIC OPTION to '0' and have changed SP_TSQL_ENVIRONMENT to "SET TEMPORARY OPTION isolation_level='0';" and it´s not working for iAnywhere driver....just for other drivers.

What is wrong?

Thank´s

[Former Member]

The setting of various options upon connecting for an ODBC connection are documented; see

SQL Anywhere Server - Programming » ODBC support » Server options changed by ODBC

The options changed using the TEMPORARY setting so that the change affects only the current connection. The options that are changed are:

• date_format: yyyy-mm-dd
• date_order: ymd
• isolation_level: the setting is based on the SQL_ATTR_TXN_ISOLATION/SA_SQL_ATTR_TXN_ISOLATION attribute setting of SQLSetConnectAttr.
• time_format: hh:nn:ss
• timestamp_format: yyyy-mm-dd hh:nn:ss.ssssss
• timestamp_with_time_zone_format: yyyy-mm-dd hh:nn:ss.ssssss +hh:nn

Unfortunately, the documentation for the JDBC driver does not indicate that these settings are also changed for the SQL Anywhere JDBC driver to match ODBC behaviour.

[VolkerBarth]

Are they changed for jConnect connections as well, or does that driver only adapt those options that simulate TDS-compatible behaviour?

And is there a difference between the iAnywhere JDBC driver and the newer SQL Anywhere JDBC drivers?

(I've looked up the docs for JDBC and do agree that these adaptions should be documented more clearly...)

[Former Member]

The setting for jConnect are completely different in order to mimic ASE behaviour.

There are no differences between the iAnywhere and SQL Anywhere JDBC drivers with respect to default option settings.

[Former Member]

The SQL Anywhere JDBC driver behaves the same as the SQL Anywhere ODBC driver in the sense that by default the timestamp_format is set to yyyy-mm-dd hh:nn:ss.ssssss and the isolation level is set to 0 upon connection. These settings are performed using set temporary option and override the public setting. You need to change your application to perform a:

Connection.createStatement().execute( "set temporary option timestamp_format =" );

after your connection has been established. By the same token, you should also call

Connection.setTransactionIsolation(...)

immediately after connecting to get an isolation level that is different from the ODBC/JDBC default.

[ThiagoReis]

I thought that some of the "login procedures" would do that for me. I can do some workaround for timestamp_format, but isolation_level is beeing set to 1 and I can´t change it to 0.

[VolkerBarth]

As Karim has stated, you can do this by explicitly calling setTransactionIsolation().

Cf. the docs and this answer - it's originally focussed on jConnect but treats all JDBC drivers.