SA12 sajdbc driver and double backslash replacement

[former_SQLA_member1694894]

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.

[jeff_albion]

Can you show us a JDBC sample of what you're describing? In general, backslashes in Java strings should be escaped.

[former_SQLA_member1694894]

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

[VolkerBarth]

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

[former_SQLA_member1694894]

It means that two backslashes in the input would be turned into one.

[jeff_albion]

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

[Breck_Carter]

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.

[former_SQLA_member1694894]

Thank you Jeff ! Your suggestion works fine.

[VolkerBarth]

Another interesting point: As to the docs, altering the setting of that option is not really recommended:

escape_character option
This option is reserved for system use. Do not change the setting of this option.

I surely would not alter a setting whose effect is not documented at all:)

[VolkerBarth]

Feel free to "accept" Jeff's answer then...

[Former Member]

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.

[former_SQLA_member1694894]

Thank you Jonathan. Your suggestion is really precious ! ... becouse, as commented by Breck, the new sajdbc4 driver don't apply the same jconnect parsing rules ... so it requires an integration.

[Breck_Carter]

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?

[former_SQLA_member1694894]

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 ?

[Breck_Carter]

I think that would be surprising. Please show us the exact Java code that works with jconnect, and the exact Java code that doesn't work with the other driver.

[former_SQLA_member1694894]

The code is the same (our application) connected to the same DB (SA12). I've only changed the connection string (... and the driver, of course ....).

The code is something like:

....
Connection cn = DriverManager.getConnection("jdbc:sqlanywhere:uid=xxx; pwd=xxx; eng=sa12dbserv; dbn=xxx;");
CallableStatement cs = cn.prepareCall("UPDATE mgaa SET DesSec='\\\\' WHERE IDaa=38798");
int rc = cs.executeUpdate();
...
...
With JConnect in the code changes are only for connection string:
...
DriverManager.getConnection("jdbc:sybase:Tds:@srv:2638?ServiceName=xxx");
...