"Incorrect syntax" exception when prefacing SP names with "dbo." and nam

We're currently trying to evaluate SQLJDBC 2005 1.1 June CTP's support for database mirroring automatic failover. Unfortunately we're getting unexpected exceptions for calls that work fine w/ jtds that our blocking our ability to perform these evaluations without us making substantial changes to our codebase.

The first issue is with the name used when calling a stored procedure -- SP names that start with "dbo." give us the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. src:{call dbo.xyz(?,?,?,?,?,?,?,?)}

The call will work if we change the SQL statement to {call xyz(...)}. I don't understand why we would need to do this, especially given that the documentation for the driver shows call statements with the "dbo." prefix.

We're also having problems using named parameters with stored procedures (for both in and out parametes). Our code has parameter names of the form "@.param" as is standard with TSQL (and is required when using jtds). However, this won't work with SQLJDBC -- it only seems to accept parameter names w/o the leading "@.". Why is this so?

Finally, we were able to cause a NullPointerException within the driver due to an incorrectly built Properties object that contained an Integer for loginTimeout instead of a String:

java.lang.NullPointerException

at java.util.Hashtable.put(Hashtable.java:396)

at java.util.Properties.setProperty(Properties.java:128)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.fixupProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.mergeURLAndSuppliedProperties(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

While this was due to a bug in our code I would think that such common errors would be better handled.

Hi,

I am not able to reproduce the error that you are seeing with executing a stored procedure with the dbo prefix. What you have provided there looks like it should work fine. Could you provide a code sample that demonstrates the error?

Thank you,

--David Olix

JDBC Development

|||

We're using the Spring JDBC template helper classes in this instance :

JdbcTemplate t = getJdbcTemplate();

Object result = t.execute("{call dbo.SP(?, ?, ?, ?, ?, ?, ?)}",
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.registerOutParameter( "@.pParam1", Types.INTEGER );
cs.registerOutParameter( "@.pParam2", Types.DATE );
cs.registerOutParameter( "@.pParam3", Types.DATE );
cs.registerOutParameter( "@.pParam4", Types.DATE );
cs.registerOutParameter( "@.pParam5", Types.INTEGER );
cs.setInt("@.pParam6", i)
cs.setString( "@.pParam7", s1);
cs.setString( "@.pParam8", s2);
cs.execute();

return result; // object created based on out params

}

The template is basically doing a "connection.prepareCall()" with the passed in SQL string, and then calling doInCallableStatement from within a try/catch block that translates

exceptions.

Were you able to reproduce the issue with "@." not being accepted for named params? That problem is effectively impossible for us to workaround, as we would need

to alter large sections of code when switching between jtds and sqljdbc.

|||

Ok, I was able to track down the cause of the Incorrect syntax exception by running a DB trace. The core problem appears to be the use of named parameters -- the call to registerOutParameter resulted in the following being executed on the server:

exec sp_sproc_columns dbo.SP, @.ODBCVer=3

This statement is incorrect -- it needs to be exec sp_sproc_columns [dbo.SP], @.ODBCVer=3

|||

Yes, that's it. If you'd like to submit a bug so that you can track progress on the fix, you may do so through the MSDN Product Feedback center at http://msdn.microsoft.com/feedback . You may have to jump through one small hoop (search for a resolution to your problem) before being taken to the "submit feedback" button that goes to the bug submission form.

I am still researching the "@." issue and will get back to you when I have an answer there.

Thank you,

--David Olix

JDBC Development