Re: Newlines not allowed in Call Escape by SQL Server 2005 JDBC driver


Colin Chambers wrote:
SQL Server 2005 JDBC driver connecting to SQL 2000 database all on Windows XP
Professional SP2.

If the stored procedure call SQL Escape syntax "{call proc(a1, a2)}" is used
and the statement contains one or more line terminating characters (carriage
return and/or newline) in a position where white space is legal after the
left parenthesis then the SQL Server 2005 JDBC driver gives an "Incorrect
Syntax near '{'" message. The SQL Server 2000 JDBC driver does not exhibit
this behavior.

The following short program demonstrates the behavior.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

/**
* Test JDBC Driver Stored Procedure Escape Syntax.
*/
public class Call2005
{
public static void main(final String... a_args)
throws SQLException, ClassNotFoundException
{
// SQL Server 2000 JDBC Driver SP3

/* Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

Connection connection =
DriverManager.getConnection(

"jdbc:microsoft:sqlserver://localhost;DatabaseName=dbutil_test",
"sa",
"xxxxxxx"); */

// SQL Server 2005 JDBC Driver

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection connection =
DriverManager.getConnection(
"jdbc:sqlserver://localhost;DatabaseName=dbutil_test",
"sa",
"xxxxxxx");

String sql = "{call get_all_dacs_ci_user(\r\n'All LOCAL')}";

Statement statement = connection.createStatement();
statement.execute(sql);

CallableStatement callable_statement = connection.prepareCall(sql);
callable_statement.execute();

PreparedStatement prepared_statement =
connection.prepareStatement(sql);
prepared_statement.execute();

System.out.println("All Done");
}
}

The behavior occurs on all three types of statement, as may be shown by
commenting out lines so that only one type at a time is being tried.

If the SQL Server 2005 JDBC Driver lines near the start are commented out
and the SQL Server 2000 JDBC Driver SP3 lines are uncommented then the
complete program will run without error (assuming the driver is on the class
path).

I would have expected newlines and carriage returns to be legal in
whitespace positions in an SQL Call Escape statement. Should they?

I have never seen them included before, and I see no value to them, but
there
is no reason they shouldn't be allowed, IMHO. The JDBC SQL escape
parser
should be able to deal with them...

Joe Weinstein at BEA Systems

.

Relevant Pages
Loading