Possible issue in jconn3?! [Solved]

[Former Member]

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

[MarkCulp]

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?

[Breck_Carter]

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'

[Former Member]

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

[chris_keating]

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.

[Former Member]

I use Sql Anywhere 9.0.2.3951. Thanks.

[Breck_Carter]

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.

[Former Member]

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

[Former Member]

Sql Anywhere 9.0.2.3951.

should I use the other version of jconnector?

[chris_keating]

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.

[Breck_Carter]

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

[chris_keating · Accepted solution]

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.

[jack_schueler]

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.

[VolkerBarth]

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

[Former Member]

The requested file is attached:
Squirrel

My very simple Java program log

The java program for test

[VolkerBarth]

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

[Former Member]

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!

😞

[Former Member]

Anyone like to access my test PC for try?

[VolkerBarth]

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

[VolkerBarth]

That has been a fitting guess, apparently:)

[jack_schueler]

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.

[Breck_Carter]

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 🙂

[jack_schueler]

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.

[Former Member]

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!

[Former Member]

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

[VolkerBarth]

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?

[Breck_Carter]

> I find it extremely hard to believe

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

[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?

[Breck_Carter]

The actual table definition is in the question.

[jack_schueler]

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.

[chris_keating]

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?

[Former Member]

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

[Former Member]

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!

[Former Member]

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

[jack_schueler]

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!

[Former Member]

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

thanks a lot!!

[jack_schueler]

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

[Breck_Carter]

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

[VolkerBarth]

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

[Former Member]

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.

[VolkerBarth]

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

[Former Member]

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

[Breck_Carter]

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"