Statement of Sybase stored procedure and Java call Method

1. No response parameters

Create procedure Test (@ A int, @ B INT)

As

Begin

Insert into tbl_test (a, B) values (@ A, @ B)

End

--------

Call method: Test (1) or EXEC "test (1 )"

2. There are response parameters

Create procedure Test2 (@ A int, @ B INT, @ C int output, @ d int output)

As

Begin

Select @ C = @ A + @ B

Select @ d = @ A * @ B

End

-------

Call method:

Declare @ CC int, @ dd int

Test2 2, 3, @ CC output, @ dd output

3. Return record set

Create procedure test3 (@ a int)

As

Begin

Select * From tbl_test where a> @

End

4. Java method for calling a stored procedure:

The callablestatement object is for all DBMS
Provides a standard method for calling stored procedures. Stored procedures are stored in the database. Callablestatement is used to call stored procedures.
Object content. This call is written using a code-changing syntax. There are two forms: one with result parameters, the other form does not contain the result parameter (for information about the code-changing syntax, see 4th
Section "statement "). The result parameter is an output (out) parameter, which is the return value of the stored process. Both forms can contain variable numbers of input (in parameter) and output (Out
Parameters) or input and output parameters. The question mark is used as a placeholder for the parameter.

The syntax for calling stored procedures in JDBC is as follows. Note that square brackets indicate that the content is optional; square brackets themselves are not part of the syntax.

{Call process name [(?, ?, ...)]}

The syntax of the returned result parameter is as follows:

{? = Call process name [(?, ?, ...)]}

The syntax for stored procedures without parameters is similar:

{Call process name}

Generally, the person who creates the callablestatement object should know the DBMS used
It supports stored procedures and knows what these processes are. However, if you need to check multiple databasemetadata types
Methods can provide such information. For example, if the DBMS supports calling stored procedures, the supportsstoredprocedures method returns
True, and the getprocedures method returns the description of the stored procedure.

Callablestatement inherits the statement method (they are used to process General SQL statements) and inherits
Preparedstatement methods (they are used to process in parameters ). All methods defined in callablestatement are used to process the out
Output part of the parameter or inout parameter: register the JDBC Type of the out parameter (General SQL type), retrieve results from these parameters, or check whether the returned value is
JDBC null.

4.1 create a callablestatement object.
The callablestatement object is created using the connection method preparecall. The following example creates a callablestatement instance, which contains a call to the stored procedure gettestdata. This process has two variables but does not include the result parameters:

Callablestatement cstmt = con. preparecall (
"{Call gettestdata (?, ?)} ");

Where? The placeholder is the In, out, or inout parameter, depending on the stored process gettestdata.

4.2 In and out parameters
The in parameter is passed to the callablestatement object through the setxxx method. This method is inherited from preparedstatement. The type of the input parameter determines the setxxx method used (for example, using setfloat to pass in the float value ).

If the out parameter is returned in the stored procedure, you must register the JDBC of each out parameter before executing the callablestatement object.
Type (this is required because some DBMS requires the JDBC Type ). The registered JDBC Type is registeroutparameter.
Method. After the statement is executed, the getxxx method of callablestatement retrieves the parameter value. Correct getxxx
The method is the Java type corresponding to the JDBC Type registered by each parameter. In other words, registeroutparameter uses JDBC
Type (so it matches the JDBC Type returned by the database), and getxxx converts it to Java type.

As an example, the following code first registers the out parameter, executes the stored procedure called by cstmt, and then retrieves the value returned in the out parameter. Method getbyte
Extracts a java byte from the first out parameter, while getbigdecimal extracts a bigdecimal from the second out parameter.
Object (three digits after the decimal point ):

Callablestatement cstmt = con. preparecall (
"{Call gettestdata (?, ?)} ");
Cstmt. registeroutparameter (1, java. SQL. types. tinyint );
Cstmt. registeroutparameter (2, java. SQL. types. decimal, 3 );
Cstmt.exe cutequery ();
Byte x = cstmt. getbyte (1 );
Java. Math. bigdecimal n = cstmt. getbigdecimal (2, 3 );

Unlike resultset, callablestatement does not provide a special mechanism for retrieving large out values in incremental mode.

4.3 inout Parameters
Besides calling the registeroutparameter method, the inout parameter must call the appropriate setxxx
Method (this method is inherited from preparedstatement ). The setxxx method sets the parameter value as the input parameter, while
The registeroutparameter method registers its JDBC Type as an output parameter. The setxxx method provides a Java
The driver first converts the value to the JDBC value and then sends it to the database.

The JDBC Type of this in value and the JDBC Type provided to the registeroutparameter Method
The type should be the same. Then, to retrieve the output value, use the corresponding getxxx method. For example, if the Java type is byte, you should use the setbyte method.
To assign the input value. Registeroutparameter should be provided with a JDBC Type of tinyint type, and getbyte should also be used
To retrieve the output value (section 8th "ing between JDBC and Java types" provides detailed information and type ing tables ).

In the next example, there is a stored procedure revisetotal. Its unique parameter is the inout parameter. The method setbyte sets this parameter to 25, and the driver will take it
The JDBC tinyint type is sent to the database. Then, registeroutparameter registers the parameter as JDBC
Tinyint. After the stored procedure is executed, a new JDBC tinyint value is returned. The getbyte method uses this new value as a java byte
Type search.

Callablestatement cstmt = con. preparecall (
"{Call revisetotal (?)} ");
Cstmt. setbyte (1, 25 );
Cstmt. registeroutparameter (1, java. SQL. types. tinyint );
Cstmt.exe cuteupdate ();
Byte x = cstmt. getbyte (1 );

4.4 first retrieve the result and then retrieve the out Parameter
Due to the limitations of some DBMS, in order to achieve maximum portability, we recommend that you first retrieve the results produced by executing the callablestatement object, and then use the callablestatement. getxxx method to retrieve out parameters.

If the callablestatement object returns multiple resultset objects (by calling the execute method ),
All results should be retrieved before the parameters. In this case, the statement method must be used to ensure that all results are accessed.
Getresultset, getupdatecount, and getmoreresults are called until no results exist.

After retrieving all the results, you can use the callablestatement. getxxx method to retrieve the value in the out parameter.

4.5 retrieve the null value as the out Parameter
The value returned to the out parameter may be JDBC null. In this case, the JDBC null value is converted to getxxx
The value returned by the getxxx method is null, 0, or false, depending on the type of the getxxx method. For the resultset object, You Need To Know 0 or false
Whether it is the only method that originates from JDBC null is to use the method wasnull for detection. If the last value read by the getxxx method is JDBC
If the value is null, true is returned. Otherwise, flase is returned. Section 5th "resultset" provides detailed information.
The callablestatement object is for all DBMS
Provides a standard method for calling stored procedures. Stored procedures are stored in the database. Callablestatement is used to call stored procedures.
Object content. This call is written using a code-changing syntax. There are two forms: one with result parameters, the other form does not contain the result parameter (for information about the code-changing syntax, see 4th
Section "statement "). The result parameter is an output (out) parameter, which is the return value of the stored process. Both forms can contain variable numbers of input (in parameter) and output (Out
Parameters) or input and output parameters. The question mark is used as a placeholder for the parameter.

The syntax for calling stored procedures in JDBC is as follows. Note that square brackets indicate that the content is optional; square brackets themselves are not part of the syntax.

{Call process name [(?, ?, ...)]}

The syntax of the returned result parameter is as follows:

{? = Call process name [(?, ?, ...)]}

The syntax for stored procedures without parameters is similar:

{Call process name}

Generally, the person who creates the callablestatement object should know the DBMS used
It supports stored procedures and knows what these processes are. However, if you need to check multiple databasemetadata types
Methods can provide such information. For example, if the DBMS supports calling stored procedures, the supportsstoredprocedures method returns
True, and the getprocedures method returns the description of the stored procedure.

Callablestatement inherits the statement method (they are used to process General SQL statements) and inherits
Preparedstatement methods (they are used to process in parameters ). All methods defined in callablestatement are used to process the out
Output part of the parameter or inout parameter: register the JDBC Type of the out parameter (General SQL type), retrieve results from these parameters, or check whether the returned value is
JDBC null.