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:
{ ... }
)?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?
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 ?=
).
Syntax: Statement statement = connection. createStatement(); Implementation: Once the Statement object is created, there are three ways to execute it.
The prepareCall() method of Connection interface returns the instance of CallableStatement. Syntax is given below: public CallableStatement prepareCall("{ call procedurename(?,?...?)}");
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With