Java – Syntax of JDBC Connection prepareCall SQL

javajdbcstored-procedures

I am reading the JavaDocs for Connection#prepareCall:

sql – an SQL statement that may contain one or more '?' parameter placeholders. Typically this statement is specified using JDBC call escape syntax.

According to this popular mkyong JDBC tutorial, I see the method executed like so:

String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(insertStoreProc);

I am wondering:

  1. Why is the string encapsulated in curly braces ({ ... })?
  2. Why does call proceed the name of the procedure?

Most importantly: is { call <nameOfProcedure> } the correct syntax for executing all stored procedures across all JDBC drivers? Or is { call ... } specific to a particular type of driver?

Are there variations of this procedure invocation syntax? For instance, are there scenarios/drivers where one might pass "{ execute <nameOfProcedure> }" into the prepareCall method? Is there documentation on any of this?

Update:

According to CallableStatement, JDBC offers 2 valid syntaxes for calling procs in a standard way, across all drivers:

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

And:

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

But it is still unclear as to when to use either (that is: when to preprend call with ?=).

Best Answer

The JDBC specification, section 13.4 Escape Syntax defines a number of escapes called JDBC escapes. These escapes are wrapped in curly braces. The specification for the call escape is:

If a database supports stored procedures, they can be invoked using JDBC escape syntax as follows:

{call <procedure_name> [(<argument-list>)]}

or, where a procedure returns a result parameter:

{? = call <procedure_name> [(<argument-list>)]}

The square brackets indicate that the (argument-list) portion is optional. Input arguments may be either literals or parameter markers. See “Setting Parameters” on page 108 for information on parameters.

This is also documented on java.sql.CallableStatement

So the name call and the curly braces are both specified in the JDBC specification.

As to the second part of your question. JDBC is a specification that tries to be database independent as much as possible and to do this it - usually - defaults to the SQL standard. IIRC the SQL specification specifies that a stored procedure either has no return value or a single return value. If the stored procedure has no return value, then you use the first call syntax. If the stored procedure has a single return value, then you use the second.

Stored procedures can also have OUT parameters (not to be confused with result sets), which are defined in the normal argument list.

Related Topic