A (Tomcat) Java web application using jTDS to connect to a MSSQL 2008 database. This Java application for 99% executes MSSQL stored procedures using user input.
The jTDS driver replies sometimes with error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
We can avoid this by adding
prepareSQL=0 to the jTDS connection string. Then the error goes away, but with all other settings of
prepareSQL, the error stays. I don't know how many stored procedure nesting levels jTDS adds, but apparently it's too much for our application.
1) With only stored procedures to execute, of course using Prepared Statements in the Java code, how much effect does
prepareSQL=0) have for us? In other words: on every website I find people say "Never use
prepareSQL=0 in production environments", is that also applicable to this situation?
prepareSQL=0 is not a recommended solution, a security issue, etc., we should maybe look for another driver. jTDS has not been updated the past 2 years and Microsoft has a driver for JDBC 4.0. I can't find any benchmarks or comparisons between jTDS and Microsoft's JDBC 4.0 driver though. With Microsoft's 2.0 and 3.0 drivers, the general opinion seemed to be that jTDS is faster, better, more efficient. Is that still the case with JDBC 4.0 or has Microsoft passed its competitor in this?
This is a duplicate of question http://stackoverflow.com/questions/10426610/jtds-stored-procedures-preparesql-nesting-level-error but probably it fits better here... I just don't have the rep yet to attach a bounty here..