Hello...

I have the code above:

create table t (id integer primary key, name varchar(40), dt timestamp);
insert into t values (1, 'test 1', now());
insert into t values (2, 'test 2', now());
insert into t values (3, 'test 3', now());
commit;
--wait some seconds
update t set name = 'New Name' where t.id = 1;
commit;
SELECT * FROM t;
--see the column dt

Forgiven... When i update any column, all timestamp columns also be updated, the new value is now()..

id name dt
1 New Name 2014-04-14 15:29:54.21
2 test 2 2014-04-14 15:28:42.56
3 test 3 2014-04-14 15:28:42.56

Solved: http://search.sybase.com/kbx/solvedcases?id_number=11433803

asked 14 Apr '14, 14:20

DRauber's gravatar image

DRauber
226121521
accept rate: 0%

edited 17 Apr '14, 13:07

Please describe the result / problem - don't leave it to readers of this forum to guess what you are seeing... since you may not get an answer if you do?

(14 Apr '14, 14:26) Mark Culp

Is that the exact code you are executing?

How are you executing that code? It doesn't look like code that uses JDBC... and it certainly isn't expected behavior.

It also doesn't look like output produced by ISQL...

id,name,dt
1,'New Name','2014-04-14 14:44:46.142'
2,'test 2','2014-04-14 14:44:46.144'
3,'test 3','2014-04-14 14:44:46.145'
(14 Apr '14, 14:50) Breck Carter

The coded is exactly this

I performed in two ways..
ISQL, using JDBC, works fine..
Squirrel Sql Client, causes error..
Using jconn3.jar
$java -jar jconn3.jar
jConnect (TM) for JDBC(TM)/6.0(Build 25570)/P/EBF12436/JDK14/Tue Feb 22 0:06:16 2005

Thanks

(14 Apr '14, 15:01) DRauber
Replies hidden
Comment Text Removed
1

Can you confirm the version of SQL Anywhere that you are testing - in prior posts, it appears it was version 9. Can you expand on what you mean by Squirrel causes "error'. In testing with SQL Anywhere 16, I see the same behaviour in both DBISQL and Squirrel - the column dt is unchanged after the update statement which is expected since the schema you have provided does not have a DEFAULT column value or trigger that would cause the column dt to change.

(14 Apr '14, 16:04) Chris Keating
Replies hidden

I use Sql Anywhere 9.0.2.3951. Thanks.

(14 Apr '14, 16:07) DRauber

Exactly what version of SQL Anywhere are you using? That determines exactly what version of Jconnect is certified for use.

Please turn on "request level logging" in SQL Anywhere, so we can see exactly what is being passed to the database engine. Assuming you are using SQL Anywhere 12 (because you mention jconn3) please see this Help topic.

(14 Apr '14, 16:14) Breck Carter

I have started the database with option -zr all, and save the logs:
Squirrel - https://db.tt/7nG9h4Ma DBIsql - https://db.tt/enTDmoxT The instructions are executed excatly this:
:::SQL create table t (id integer primary key, name varchar(40), dt timestamp);
insert into t values (1, 'test 1', now());
insert into t values (2, 'test 2', now());
insert into t values (3, 'test 3', now());
commit;

--wait some seconds
update t set name = 'New Name' where t.id = 1;
commit;
SELECT * FROM t;
--see the column dt

(14 Apr '14, 17:02) DRauber

Sql Anywhere 9.0.2.3951.

should I use the other version of jconnector?

(14 Apr '14, 17:06) DRauber

It is still not clear what the problem that you are encountering. Can you elaborate?

Please note that the dbisql session appears to using SQL Anywhere JDBC driver - I cannot recall when DBISQL shifted from jConnect to SA JDBC or when the option to switch between the drivers was permitted. It is important to note that the jConnect driver has some behaviours that are different because of its heritage is JDBC connectivity to ASE. When used with SQL Anywhere, the connection is configured with some TDS compatiblity behaviours that differ from can differ from SA connections.

(14 Apr '14, 17:33) Chris Keating

Here's what the 9.0.2 Help says about jConnect...

ASA Programming Guide

JDBC Programming

Using the jConnect JDBC driver

The jConnect driver files


The jConnect JDBC driver is installed into a set of directories under the Sybase\Shared directory. Two versions of jConnect are supplied:

jConnect 4.5 This version of jConnect is for use when developing JDK 1.1 applications. jConnect 4.5 is installed into the Sybase\Shared\jConnect-4_5 directory.

jConnect 4.5 is supplied as a set of classes.

jConnect 5.5 This version of jConnect is for use when developing JDK 1.2 or later applications. jConnect 5.5 is installed into the Sybase\Shared\jConnect-5_5 directory.

jConnect 5.5 is supplied as a jar file named jconn2.jar.

Examples in this chapter use jConnect 5.5. Users of jConnect 4.5 must make appropriate substitutions.

Setting the CLASSPATH for jConnect For your application to use jConnect, the jConnect classes must be in your classpath at compile time and run time, so the Java compiler and Java runtime can locate the necessary files.

The following command adds the jConnect 5.5 driver to an existing CLASSPATH environment variable where path is your Sybase\Shared directory.

set classpath=%classpath%;path\jConnect-5_5\classes\jconn2.jarThe following command adds the jConnect 4.5 driver to an existing CLASSPATH environment variable:

set classpath=%classpath%;path\jConnect-4_5\classesImporting the jConnect classes The classes in jConnect are all in the com.sybase package.

If you are using jConnect 5.5, your application must access classes in com.sybase.jdbc2.jdbc. You must import these classes at the beginning of each source file:

import com.sybase.jdbc2.jdbc.If you are using jConnect 4.5, the classes are in com.sybase.jdbc. You must import these classes at the beginning of each source file:

import com.sybase. jdbc.

(14 Apr '14, 20:29) Breck Carter
More comments hidden
showing 4 of 10 show all flat view

Apparently, it has been a very long time since we worked regularly with SQL Anywhere 9 and jConnect as it turns out that this is expected behaviour.

This behaviour is controlled by the automatic_timestamp option. When it is turned on, TIMESTAMP columns are provided a default value of TIMESTAMP. If the table has already been created via a TDS bases connection such as jConnect, you will need to change the column default from TIMESTAMP to another value ie., blank.

permanent link

answered 17 Apr '14, 11:09

Chris%20Keating's gravatar image

Chris Keating
7.7k49127
accept rate: 32%

As Chris Keating notes, much to our chagrin, this is expected behavior for ASA 9.0.2.

See: http://search.sybase.com/kbx/solvedcases?id_number=11433803

This has been an interesting investigation to say the least.

permanent link

answered 17 Apr '14, 12:17

JBSchueler's gravatar image

JBSchueler
3.3k41564
accept rate: 19%

1

Wow, the mystery of old options...

Just to add:

The mentioned T-SQL compatibility option "automatic_timestamp" has been discontinued in SA 11.0.0 - to cite:

automatic_timestamp New columns with the TIMESTAMP data type that do not have an explicit default value defined are never given a default value of the Transact-SQL timestamp.


So for v10 and below, I would think one could resolve the issue by either temporarily set that option to "off" or to use a login procedure that would do that automatically for TDS connections...

(17 Apr '14, 12:29) Volker Barth

I think the problem is related to understanding that SQL Anywhere TIMESTAMP columns are quite different from ASE/SQL Server TIMESTAMP columns. ASE/SQL Server TIMESTAMP columns can be used for row versioning - they update automatically.

Not so with SQL Anywhere. Here TIMESTAMP is equivalent to the DATETIME data type. These columns do not update automatically.

Try this...

create table t (id integer primary key, name varchar(40), dt timestamp default timestamp );

Also see the documentation on DEFAULT TIMESTAMP and DEFAULT CURRENT TIMESTAMP.

permanent link

answered 15 Apr '14, 18:34

JBSchueler's gravatar image

JBSchueler
3.3k41564
accept rate: 19%

edited 15 Apr '14, 18:39

1

The original question reported that a timestamp column without a default timestamp clause was being updated as if it did have a default timestamp clause... quite the opposite problem :)

(15 Apr '14, 20:58) Breck Carter
Replies hidden

Then I'd like to see the JDBC connection string because I find it extremely hard to believe that this behavior occurs with a SQL Anywhere 9 database.

(16 Apr '14, 10:03) JBSchueler
Replies hidden

Really, using the datetime type, do the update correctly. But is there any property or way to do this using timestamp? I do not have autonomy over all tables, and several use timestamp .. Many thanks for the clarification!

(16 Apr '14, 10:22) DRauber
Replies hidden

Breck.. uses the clause "default timestamp" causes no difference in outcome

(16 Apr '14, 10:24) DRauber

You surely do not use an user-defined datatype with a default that is automatically applied to the column?

Could you show us the actual table definition?

(16 Apr '14, 10:35) Volker Barth

> I find it extremely hard to believe

Indeed... if the engine was doing this, it would be one of the signs of the apocalypse :)

(16 Apr '14, 10:42) Breck Carter

We don't care about the other tables, we care about the CREATE TABLE you have shown us... is it, or is it not, the exact code that you have executed via SQuirreL?

Have you tried using jconn2.jar, or do you not read the responses?

(16 Apr '14, 10:46) Breck Carter

The actual table definition is in the question.

(16 Apr '14, 10:47) Breck Carter

Everything that I read indicates that DRauber is attempting to move ASA 9.0 schema to an ASE database and discovering that some things like TIMESTAMP columns don't work the same. DATETIME solves the problem. Why? Because DATETIME on ASE isn't TIMESTAMP.

(16 Apr '14, 10:54) JBSchueler

The behaviour that you are describing is 100% the behaviour of timestamp in an Adaptive Server ENTERPRISE database server. SQL Anywhere timestamp columns do not change value as a result of an update unless the schema includes a mechanism to cause the value to change such as a trigger or a column default value of timestamp i.e., dt timestamp default timestamp.

Can you post the output of the query

SELECT @@version

from whatever software (Squirel SQL?) that you are running that is showing the DT column being updated?

(16 Apr '14, 11:01) Chris Keating

Yes, I tried with jconn2 and jconn3, btw, the result is the same!

(16 Apr '14, 13:47) DRauber

I found the "problem":
Using Squirrel SQL, jconn2.jar, Sybase Sql ANYWHERE 9.0.2.3951
i perform this:

--drop table t; create table t (id integer, name char(40), dt timestamp default null);
insert into t values (1, 'A', now());
SELECT * FROM t;
--wait some seconds
UPDATE t set name = 'B';
SELECT * FROM t;
--the column dt is not updated (correct!!)

perform this:
--drop table t; create table t (id integer, name char(40), dt timestamp);
insert into t values (1, 'A', now());
SELECT * FROM t;
--wait some seconds
UPDATE t set name = 'B';
SELECT * FROM t;
--the column dt is updated (error!!)

If the default value is not set, causes the problem!

Thanks!

(16 Apr '14, 14:11) DRauber

Certainly not the engine! Because through isql the result is correct! The hypothesis is JConnector.

(16 Apr '14, 14:30) DRauber

Not possible. Why do you show us the "drop table" line preceded by --? I think you are repeating the "insert into t" followed by the UPDATE. Shut down the server and restart it with these options ...

dbeng9 -zr sql -o test9.txt your-database.db

Then rerun your test, edit test9.txt, and copy/paste the results here on the forum. Then you might have a believer!

(16 Apr '14, 17:18) JBSchueler

I do better.. If you can, access the pc by teamviewer
ID - 237 056 878
PWD - 5910
And see with your eyes :D

thanks a lot!!

(16 Apr '14, 17:46) DRauber

OK, I downloaded Squirrel, connected with jconn2, and I am now a believer. I will investigate this more tomorrow to figure out what is going on. Is there an ASE compatibility option for TIMESTAMP column behavior?

This has something to do with executing the following (under the hood by the JDBC driver)

set rowcount 100

(16 Apr '14, 18:22) JBSchueler
1

Betcha it's something SQuirreL is doing all by itself... keep us posted!

(16 Apr '14, 20:28) Breck Carter

Feel free to drop/modify these TeamViewer access, if not already done...

(17 Apr '14, 03:40) Volker Barth

Good morning friends! Many thanks for your attention so far. I wrote a small application in java using the Jconn2 to confirm whether the squirrel is the culprit or not. I came to a conclusion, it is not! When the table is created using the Jconn2, the column value by default is as timestamp. When the table is created by isql, the column value by default is blank. java program: http://ubuntuone.com/3rKVK1uK4W3wN3pUJcwumT

Thank you very much.

(17 Apr '14, 07:28) DRauber

So the actual table definition does differ w.r.t. column defaults, i.e. when you enter

 create table t (id integer, name char(40), dt timestamp);

the table is created as

 create table t (id integer, name char(40), dt timestamp default null);

? Are there further differences, say with respect to NULL/NOT NULL - where ASE and SA have different defaults, as well? Confine this doc page from SA 12.0.1.

(I'm asking as not everyone interested in this FAQ may be ready to download further contents from other sites and try to re-do your own tests...)

(17 Apr '14, 07:43) Volker Barth

I understand..
let me say that the conclusion arrived, using jconn2 or jconn3:

create table t (id integer, name char(40), dt timestamp);

JConnector add the option "default timestamp" in dt column.

create table t (id integer, name char(40), dt timestamp default null);

JConnector create the table with default null.

ie the default JConnector arrow as default timestamp for timestamp columns.

Thanks..

(17 Apr '14, 09:21) DRauber
More comments hidden
showing 5 of 21 show all flat view
permanent link

answered 17 Apr '14, 09:46

DRauber's gravatar image

DRauber
226121521
accept rate: 0%

edited 17 Apr '14, 10:42

FWIW, that's the naked CREATE TABLE statement from the RLL file (without a DEFAULT TIMESTAMP), so there is something strang going on:

I. 04/17 10:38:04. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM    
I. 04/17 10:38:45. ** REQUEST conn: 1     STMT_EXECUTE_ANY_IMM    "set rowcount @p0"
I. 04/17 10:38:45. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM    
I. 04/17 10:38:45. ** REQUEST conn: 1     STMT_EXECUTE_ANY_IMM    "create table t (id integer, name char(40), dt timestamp);
I. 04/17 10:38:45. insert into t values (1, 'AAA', now());"
I. 04/17 10:38:45. ** WARNING conn: 1     code: 105 "Procedure has completed"
I. 04/17 10:38:45. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM

And that's the UPDATE statement (without a value for the timestamp column):

I. 04/17 10:39:27. ** REQUEST conn: 1     STMT_EXECUTE_ANY_IMM    "set rowcount @p0"
I. 04/17 10:39:27. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM    
I. 04/17 10:39:27. ** REQUEST conn: 1     STMT_EXECUTE_ANY_IMM    "update t set name = 'New Name';"
I. 04/17 10:39:27. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM    
I. 04/17 10:39:34. ** REQUEST conn: 1     STMT_EXECUTE_ANY_IMM    "select * from t"
I. 04/17 10:39:34. ** WARNING conn: 1     code: 100 "Row not found"
I. 04/17 10:39:34. ** WARNING conn: 1     code: 105 "Procedure has completed"
I. 04/17 10:39:34. ** DONE    conn: 1     STMT_EXECUTE_ANY_IMM


Given Jack has already noticed the following (to quote from a comment from yesterday):

This has something to do with executing the following (under the hood by the JDBC driver)

set rowcount 100

these calls do appear here, too...

(17 Apr '14, 10:19) Volker Barth

I don't understand!
I Follow exactly the instructions sent by Breck Carter to create the database..
I run create table and insert in my java program.
In the isql run select * from t
After, run update t set name='What a hell?'
In the isql i run select * from t and the column dt was updated!

:(

(17 Apr '14, 10:49) DRauber

Anyone like to access my test PC for try?

(17 Apr '14, 11:00) DRauber
Replies hidden

I guess currently you have done all there is to do to show the problem, particularly when a true expert like Jack Schueler has committed to take the time to study that effect. - I would wait for his feedback...

(17 Apr '14, 11:12) Volker Barth

That has been a fitting guess, apparently:)

(17 Apr '14, 12:31) Volker Barth

Please run your SQuirreL test against an empty SQL Anywhere 9 database with "request level logging" turned on.

Here's how to create and start that database:

"%ASANY9%\win32\dbinit.exe" ddd9.db
"%ASANY9%\win32\dbeng9.exe" -o "c:\temp\ddd9log.txt" -zr sql ddd9.db 

Here's how to connect:

ENG=ddd9;DBN=ddd9;UID=dba;PWD=sql;

Please post the entire "c:\temp\ddd9log.txt" log file so we can see what the server is seeing.

It will contain all the SQL coming from the client side; for example:

I. 04/17 09:15:58. ** REQUEST conn: 1     STMT_PREPARE            "select * from dummy"
permanent link

answered 17 Apr '14, 09:24

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 17 Apr '14, 09:25

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×39
×22

question asked: 14 Apr '14, 14:20

question was seen: 7,925 times

last updated: 17 Apr '14, 13:07