Hi all,
Our experience about ASA9 TO SA12 migration is really a nightmare ... especially with regard to the sajdbc driver.
After a lot of case express (serious memory bug), query rewriting (to make up for poor performance) last problem is:
Updating a varchar column, any double backslash ("\\") is REPLACED with a single backslash ("\").
The same query, executed using the jconnect driver works fine (no replacement ....)
What's going on?
How I can disable it ?
Thanks.

I simply build UPDATE queries based on UI input. If the user types a double backslash in an input box the generated query is; "UPDATE [table] SET [column]='\\ .....' WHERE ....".
This query, executed using JConnect driver works fine ... while executed using sajdbc4 is escaped !!

Just to understand: "Is escaped" would mean to me that the backslashes are "doubled", i.e. two backslashes in the input would be turned into four in the query and would then be stored as two in the database. - But from your question, I'd think the opposite does happen (i.e. two backslashes in the input would be turned into one.)

As you can see from the text of this procedure in the documentation, if you're using a "TDS" connection (e.g. jConnect), we will additionally call "dbo.sp_tsql_environment()".

Part of this specific "TSQL" stored procedure has the option:

SETTEMPORARYOPTIONescape_character='OFF';

which is the reason for the behaviour differences you're observing.

If you wished your SAJDBC4 connection to "act" more like the jConnect behaviour, you could also call the sp_tsql_environment system stored procedure every time you log in to the database. You could automatically do this by changing the sp_login_environment stored procedure text to get rid of the "TDS" protocol check.

In reality though, the SAJDBC4 driver behaviour is correct from a Java point of view. You should quadruple-backslash any backslashes for correct input (\\ for the Java parsing and \\ for the SQL parsing). Without doing this, you may have trouble escaping other kinds of data (hex values, etc.).

Good catch! ...this just reinforces my hatred of the sp_login_environment architecture, not because any of its settings are "right" or "wrong" but because they introduce hidden dependencies on the protocol, which in turn is a hidden setting (how many developers know what TDS is?)

Having said that, legacy considerations mean your hands are tied; i.e., nothing can be changed, and its all up to the docs, the training, and the vigilance of the developer.

A backslash character in a string must be escaped using an additional backslash,
as follows: 'c:\\november'

Is it possible that the code used with SQL Anywhere 9 was slightly different than the code used with SQL Anywhere 12? In particular, is it possible that a host variable was used instead of a 'string literal' with SQL Anywhere 9, thus avoiding the string-literal-escape-rule?

Thank you Breck !
At this point I think that the (great) JConnect driver replace any double backslash (\\) with a quadruple backslash (\\\\) to respect the original user input !!! ....
What do you think about ?

There is a way to fix it entirely on your end. Not sure if that is what you wanted, but it would solve the problem.

You could use a Decorator JDBC driver and transfer everything through it. A Decorator JDBC driver implements all of the methods of the JDBC specification, but most of them just pass through to the underlying driver. Then you can override certain methods, and put in code that would do whatever you want.

In your case, I would just override the Connection.createStatement() or Connection.prepareStatement() methods (depending on which ones you use to create the statement) to rewrite your SQL string. You could then search and replace all \\ with whatever you need.

I personally use Log4jdbc, which has the benefit of also providing logging support, and just overload the method I want. But there are several JDBC decorators out there, and YMMV.