[Original] output parameters have not yet been processed is derived from multiple data sets returned

Environment: Database SQL server2005, jdk1.6, IDE myeclipse, link tool: net. SourceForge. jtds. JDBC. Driver

Problem:

When java. SQL. callablestatment is used in Java to call the Stored Procedure processing, the following problems occur:

Error: Java. SQL. sqlexception: output parameters have not yet been processed. Call getmoreresults ().

 

ProgramThe calling method in is proved correct, because it is okay to replace a stored procedure.

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

So the problem is locked to my stored procedure.

The stored procedure makes the following judgment:

Select 0 from testtable where name = @ name

The purpose of this judgment is to determine whether the user name already exists. But this is the final tangle!

The reason is that,Select 0 returns a dataset, and your return data will be in the last dataset.

If you want to obtain the result, you can find getmoreresults () in the dataset in Java until the correct results is found.

OrChange the prediction returned by the dataset to the value assignment statement.No problem.

For example, changeSelect @ uid = ID from testtable where name = @ name

 

================================================ I am a demarcation line ==== ==============================================

Appendix: callablestatment Stored Procedure method:

 

Overview
The callablestatement object provides a standard method for calling stored procedures for all DBMS systems. Stored procedures are stored in the database. The call to a stored procedure is the content contained in the callablestatement object. This call is written using a code-changing syntax. There are two forms: one with result parameters, the other form does not contain result parameters (for information about the code-changing syntax, see section 4th "statements "). The result parameter is an output (out) parameter, which is the return value of the stored process. The two types can contain variable input (in parameter), output (out parameter), or input and output (inout parameter) 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 that the DBMS used supports stored procedures and what these processes are. However, multiple databasemetadata methods can provide such information if you need to check. For example, if the DBMS supports calling stored procedures, the supportsstoredprocedures method returns true, and the getprocedures method returns a description of the stored procedure.

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

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.

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 for a stored procedure, the JDBC Type of each out parameter must be registered before the callablestatement object is executed (this is required because some DBMS require the JDBC Type ). You can use the registeroutparameter method to register the JDBC Type. After the statement is executed, the getxxx method of callablestatement retrieves the parameter value. The correct getxxx method is the Java type corresponding to the JDBC Type registered by each parameter (for the standard ing from the JDBC Type to the Java type, see the table in section 8.6.1 ). In other words, registeroutparameter uses the JDBC Type (so it matches the JDBC Type returned by the database), and getxxx converts it to the Java type.

For exampleCodeRegister the out parameter first, execute the stored procedure called by cstmt, and then retrieve the value returned in the out parameter. The getbyte method extracts a java byte from the first out parameter, while getbigdecimal extracts a bigdecimal object from the second out parameter (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.

3. inout Parameters
Besides calling the registeroutparameter method, inout parameters that support both input and output are also required to 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 the output parameter. The setxxx method provides a Java value, which the driver first converts to a JDBC value and then sends it to the database.

The JDBC Type of this in value should be the same as the JDBC Type provided to the registeroutparameter method. 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. The JDBC Type of tinyint type should be provided to registeroutparameter, getbyte should also be used to retrieve output values (section 8th "ing between JDBC and Java types" will provide 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 sends it to the database as the JDBC tinyint type. Then, registeroutparameter registers the parameter as JDBC tinyint. After the stored procedure is executed, a new JDBC tinyint value is returned. The getbyte method retrieves the new value as a java byte type.

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. Search the result first, 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 out parameter is retrieved. In this case, to ensure that all results are accessed, the statement method getresultset, getupdatecount, and getmoreresults must be called until no results are available.

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

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 so that the value returned by the getxxx method is null, 0, or false, depending on the type of the getxxx method. For a resultset object, you must know whether 0 or false is the only method that originates from JDBC null. The method wasnull is used for detection. If the last value read by the getxxx method is JDBC null, this method returns true; otherwise, flase is returned. Section 5th "resultset" provides detailed information.

This article describes how to use DBMS stored procedures. I have explained the basic and advanced features of using stored procedures, such as returning resultset. This article assumes that you are not familiar with DBMS and JDBC, and that you can read the code written in other languages without any obstacles (that is, it is not a Java language). However, you are not required to have any stored procedure programming experience.
Stored procedures are stored in the database and executed on the database. You can use special syntax to call stored procedures in Java classes. During the call, the name of the stored procedure and the specified parameters are sent to the DBMS through a JDBC connection, the stored procedure is executed, and the result is returned through a connection (if any.
Using Stored Procedures has the same benefits as using an application server based on EJB or CORBA. The difference is that stored procedures can be used free of charge from many popular DBMS, and most application servers are very expensive. This is not just about license fees. The management and coding costs required to use the application server and the complexity of the customer program can all be replaced by the stored procedure in the DBMS.
You can use Java, Python, Perl, or C to write stored procedures, but usually use the specific language specified by your DBMS. Oracle uses PL/SQL, PostgreSQL uses PL/pgsql, and DB2 uses procedural SQL. These languages are very similar. Porting session beans between them is not more difficult than porting session beans between different implementation versions of Sun's EJB specification. In addition, stored procedures are designed to embed SQL statements, which makes them more user-friendly to express the database mechanism than Java or C.
Because stored procedures run on DBMS itself, this can help reduce the waiting time in applications. Instead of executing four or five SQL statements in Java code, you only need to execute one stored procedure on the server side. The reduction in the number of round-trips on the network can dramatically optimize the performance.

Use stored procedures

Simple old JDBC supports calling stored procedures through the callablestatement class. This class is actually a subclass of preparedstatement. Assume that we have a poets database. The database has a stored procedure for setting the age of the poet's death. The following is an old drunk Dylan Thomas (old soak Dylan Thomas) who does not specify the story or culture. Please criticize and correct him. Detailed code for calling:

Try
{
Int age = 39;
String poetname = "Dylan Thomas ";
Callablestatement proc =
Connection. preparecall ("{call set_death_age (?, ?) }");
Proc. setstring (1, poetname );
Proc. setint (2, age );
Cs.exe cute ();
}
Catch (sqlexception E)
{
//....
}

The string passed to the preparecall method is the writing specification for the stored procedure call. It specifies the name of the stored procedure ,? Represents the parameter you need to specify.
Integration with JDBC is a great convenience for stored procedures: In order to call stored procedures from applications, you do not need to use the stubs or configuration files, you don't need anything except your DBMS's JDBC driver.
When this code is executed, the stored procedure of the database is called. We didn't get the result because the stored procedure does not return the result. If the execution succeeds or fails, you will be informed by exceptions. Failure may indicate a failure in calling the stored procedure (for example, the type of a provided parameter is incorrect ), or an application failure (for example, throwing an exception indicates that "Dylan Thomas" does not exist in the poets database ")

Combined with SQL operations and stored procedures

Ing rows from a Java object to an SQL table is quite simple, but you usually need to execute several SQL statements. It may be a select lookup ID, and then an insert inserts data with the specified ID. In a database mode that is highly normalized (in line with a higher paradigm, ), multiple tables may be updated, so more statements are required. Java code expands rapidly, and the network overhead of each statement increases rapidly.
Transferring these SQL statements to a stored procedure will greatly simplify the code and only involve one network call. All associated SQL operations can occur within the database. In addition, stored procedure languages, such as PL/SQL, allow the use of SQL syntax, which is more natural than Java code. The following is an early stored procedure written in Oracle's PL/SQL language:

Create procedure set_death_age (poet varchar2, poet_age number)
Poet_id number;
Begin
Select ID into poet_id from poets where name = poet;
Insert into deaths (mort_id, age) values (poet_id, poet_age );
End set_death_age;

Very unique? No. I bet you will definitely look forward to seeing an update on a poets table. This also implies how easy it is to implement using stored procedures. Set_death_age is almost certainly a bad implementation. We should add a column in the poets table to store the age of death. Java code does not care about how the database mode is implemented, because it only calls the stored procedure. We can change the database mode in the future to improve performance, but we don't have to modify our code.
The following is the Java code for calling the above stored procedure:

Public static void setdeathage (poet dyingbard, int age)
Throws sqlexception
{
Connection con = NULL;
Callablestatement proc = NULL;

Try
{
Con = connectionpool. getconnection ();
Proc = con. preparecall ("{call set_death_age (?, ?) }");
Proc. setstring (1, dyingbard. getname ());
Proc. setint (2, age );
Proc.exe cute ();
}
Finally
{
Try
{
Proc. Close ();
}
Catch (sqlexception e ){}
Con. Close ();
}
}

To ensure maintainability, we recommend that you use static methods like this. This also enables the code that calls the stored procedure to be concentrated in a simple template code. If you use many stored procedures, you will find that you only need to copy and paste to create a new method. Because of the template-based code, you can even use scripts to automatically produce code that calls the stored procedure.

Functions

Stored Procedures can return values, so the callablestatement class has methods like getresultset to obtain the returned values. When a stored procedure returns a value, you must use the registeroutparameter method to tell the JDBC driver what the SQL type of the value is. You must also adjust the stored procedure call to indicate that the process returns a value.
The following is an example. This time, we will query the age of Dylan Thomas when he died. This stored procedure uses PostgreSQL's PL/pgsql:

Create Function snuffed_it_when (varchar) returns integer'
Declare
Poet_id number;
Poet_age number;
Begin
-- First get the ID associated with the poet.
Select ID into poet_id from poets where name = $1;
-- Get and return the age.
Select age into poet_age from deaths where mort_id = poet_id;
Return age;
End;
'Language' PL/pgsql ';

In addition, note that PL/pgsql parameter names are referenced using the $ n Syntax of UNIX and DOS scripts. At the same time, pay attention to embedded comments, which is another advantage over Java code. Writing such annotations in Java is certainly acceptable, but it looks messy and out of line with SQL statements and must be embedded into Java strings.
The following is the Java code that calls the stored procedure:

Connection. setautocommit (false );
Callablestatement proc =
Connection. preparecall ("{? = Call snuffed_it_when (?) }");
Proc. registeroutparameter (1, types. integer );
Proc. setstring (2, poetname );
Cs.exe cute ();
Int age = Proc. getint (2 );

What if an error return value type is specified? Then, a runtimeexception will be thrown when a stored procedure is called, as you encounter when using an error type in the resultset operation.

Complex return values

Many people are familiar with the stored procedure. If this is all the functions of a stored procedure, the stored procedure is not a replacement for other remote execution mechanisms. Stored Procedure functions are much more powerful than this.
When you execute an SQL query, DBMS creates a database object called a cursor (cursor) to iterate each row in the returned results. Resultset is a cursor representation of the current time point. This is why you can only move forward in the resultset without caching or specific database support.
Some DBMS allow returning a reference to a cursor from a stored procedure. JDBC does not support this function, but the JDBC drivers of Oracle, PostgreSQL, and DB2 support opening the pointer to the cursor on the resultset ).
Imagine listing all poets who have not yet lived to retirement. The following is the Stored Procedure for completing this function, returning an open cursor, also using PostgreSQL's PL/pgsql language:

Create procedure list_early_deaths () return refcursor'
Declare
Toesup refcursor;
Begin
Open toesup
Select poets. Name, deaths. Age
From poets, deaths
-- All entries in deaths are for poets.
-- But the table might become generic.
Where poets. ID = deaths. mort_id
And deaths. age <60;
Return toesup;
End;
'Language 'plpgsql ';

The following is the Java method that calls the stored procedure and outputs the result to printwriter:
Printwriter:

Static void sendearlydeaths (printwriter out)
{
Connection con = NULL;
Callablestatement toesup = NULL;
Try
{
Con = connectionpool. getconnection ();

// PostgreSQL needs a transaction to do this...
Con. setautocommit (false );

// Setup the call.
Callablestatement toesup
= Connection. preparecall ("{? = Call list_early_deaths ()}");
Toesup. registeroutparameter (1, types. Other );
Getresults.exe cute ();

resultset rs = (resultset) getresults. getObject (1);
while (RS. next ()
{< br> string name = Rs. getstring (1);
int age = Rs. getint (2);
out. println (name + "was" + age + "years old. ");
}< br> Rs. close ();
}< br> catch (sqlexception e)
{< br> // We shoshould protect these CILS.
toesup. close ();
con. close ();
}< BR >}

Because JDBC does not directly support returning a cursor from a stored procedure, we use types. Other to indicate the type of returned data for the stored procedure, then call the GetObject () method, and perform forced type conversion on the returned value.
The Java method for calling the stored procedure is a good example of mapping. Mapping is an abstract method for operations on a set. Instead of returning a set in this process, we can transfer the operation to execute it. In this example, the operation is to print the resultset to an output stream. This is a very common example. The following is another method for calling the same stored procedure:

Public class processpoetdeaths
{
Public abstract void senddeath (string name, int age );
}

Static void mapearlydeaths (processpoetdeaths Mapper)
{
Connection con = NULL;
Callablestatement toesup = NULL;
Try
{
Con = connectionpool. getconnection ();
Con. setautocommit (false );

callablestatement toesup
= connection. preparecall ("{? = Call list_early_deaths ()} ");
toesup. registeroutparameter (1, types. Other);
getresults.exe cute ();

resultset rs = (resultset) getresults. getObject (1);
while (RS. next ()
{< br> string name = Rs. getstring (1);
int age = Rs. getint (2);
mapper. senddeath (name, age);
}< br> Rs. close ();
}< br> catch (sqlexception e)
{< br> // We shoshould protect these CILS.
toesup. close ();
con. close ();
}< BR >}

This allows arbitrary processing on the resultset data without changing or copying the method for obtaining the resultset:

Static void sendearlydeaths (final printwriter out)
{
Processpoetdeaths mymapper = new processpoetdeaths ()
{
Public void senddeath (string name, int age)
{
Out. println (name + "was" + age + "years old .");
}
};
Mapearlydeaths (mymapper );
}