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.
When you log in to SQL Anywhere, the system stored procedure "dbo.sp_login_environment()" will automatically be executed.
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:
SET TEMPORARY OPTION escape_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.).
When the SQL Anywhere engine receives a statement that looks like this
UPDATE [table] SET [column]='\\ .....' WHERE ....
the string literal '\\ .....' is processed according to this rule in SQL Anywhere 9
ASA SQL Reference SQL Language Elements Strings ... To represent a backslash character, use two backslashes in a row (\\). For example, 'c:\\temp'
and the same rule in SQL Anywhere 12: String literals
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?
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
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.
answered 03 Oct '12, 19:26