Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Syntax of JDBC Connection prepareCall SQL

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 ?=).

like image 353
IAmYourFaja Avatar asked Dec 09 '14 15:12

IAmYourFaja


People also ask

What is the syntax for JDBC?

Syntax: Statement statement = connection. createStatement(); Implementation: Once the Statement object is created, there are three ways to execute it.

What is prepareCall?

The prepareCall() method of Connection interface returns the instance of CallableStatement. Syntax is given below: public CallableStatement prepareCall("{ call procedurename(?,?...?)}");

What is the correct syntax to establish the connection while using JDBC?

Java DB: jdbc:derby:testdb;create=true , where testdb is the name of the database to connect to, and create=true instructs the DBMS to create the database. Note: This URL establishes a database connection with the Java DB Embedded Driver.

What is JDBC connection with example?

JDBC is an acronym for Java Database Connectivity. It's an advancement for ODBC ( Open Database Connectivity ). JDBC is a standard API specification developed in order to move data from frontend to the backend. This API consists of classes and interfaces written in Java.


1 Answers

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.

like image 151
Mark Rotteveel Avatar answered Oct 13 '22 23:10

Mark Rotteveel