My server is a SA 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?


asked 05 Jul '11, 15:33

Thiago%20Reis's gravatar image

Thiago Reis
accept rate: 40%

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


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

permanent link

answered 05 Jul '11, 16:04

Karim%20Khamis's gravatar image

Karim Khamis
accept rate: 40%

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.

(05 Jul '11, 16:34) Thiago Reis
Replies hidden

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.

(06 Jul '11, 04:05) Volker Barth

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.

permanent link

answered 06 Jul '11, 07:44

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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

(06 Jul '11, 08:01) Volker Barth
Replies hidden

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.

(06 Jul '11, 08:13) Karim Khamis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 Jul '11, 15:33

question was seen: 1,940 times

last updated: 06 Jul '11, 08:13