stored procedure call doesnt work without brackets {}

[DmitriyGod]

Driver version

latest

SQL Server version

mcr.microsoft.com/mssql/server:2022-latest

Client Operating System

any

JAVA/JVM version

any

Table schema

create procedure test_bigdecimal
@big_decimal_type      decimal(15, 5)       ,
@big_decimal_type_o    decimal(15, 5) output
as begin
   set @big_decimal_type_o = @big_decimal_type;
end;

Problem description

Hi! MSSQL driver don't allow to use prepareCall without brackets {}

Expected behavior

Like in pg, oracle, hsqldb, mariadb, mysql, snowflake, db2,
code below shouldn't cause exception:

var call = connection.prepareCall("""
    call test_bigdecimal(100.24112, ?)""");

call.registerOutParameter(1, Types.DECIMAL);
call.execute();

Actual behavior

prepareCall works only with brackets
var call = connection.prepareCall("""
    {call test_bigdecimal(100.24112, ?)}""");
call.registerOutParameter(1, Types.DECIMAL);
call.execute();

Error message/stack trace

Incorrect syntax near '@p0'

Any other details that can be helpful

JDBC trace logs

[muskan124947]

Thank you for reaching out.

As per the JDBC specification, the {} braces are required in the call escape syntax for stored procedure execution. Our driver follows this compliance to correctly parse and execute stored procedure calls.
You can find more details in the JDBC documentation here

[DmitriyGod]

Yeah, page 116 shows an example of code with escaping
But this example is in paragraph 13.4.4 which is in chapter 13.4 which says 1 and 2:

1. The SQL string used in a Statement object may include JDBC escape syntax.

2. Special escape processing might be needed for the following:

• commonly used features that do not have standard syntax defined by SQL, or where the native syntax supported by the underlying data source varies widely among vendors.
  Not the case because the SQL-standard supports calling without escaping and major drivers already support it: pg, oracle, hsqldb, mariadb, mysql, snowflake, db2
• features that are not supported by the underlying data source but are implemented by the driver.
  obviously not the case

Making calls work without escaping won't break spec compliance.
Escaping in the spec was introduced for portability. Now core drivers support calls without escaping because it's convenient.
The fact that the mssql-driver does not allow calls without escaping makes driver less portable in sql-standard sense

[muskan124947]

Thank you, I will add this as an enhancement request.