Java call SQL Server stored procedure parameters and return result sets at the same time

2010-03-31  来源:本站原创  分类:Java  人气:466 

Transfer from: http://blog.csdn.net/kirbylynx/archive/2008/12/09/3483449.aspx

Such as a SQL Server stored procedure:

create procedure proc_test
@ Q_type int,
@ Value int,
@ Count int output
as
begin
update mytable set value = @ value where type = @ q_type
set @ count = @ @ rowcount
select * from mytable where type = @ q_type
end
go
This stored procedure, both the output parameters, but also return a result set, but with the java call him, that both have access to, the code is as follows:

Connection conn = MyConnectionPool.getConnection ();
CallableStatement cstmt = conn.prepareCall ("(call proc_test (?,?,?)}");
cstmt.setInt (1, type);
cstmt.setInt (2, value);
cstmt.registerOutParameter (3, java.sql.Types.INTEGER);
ResultSet rs = cstmt.executeQuery ();
while (rs.next ()) (
doSomeThingToResultSet (rs);
)
doSomeThingToOutParameter (cstmt.getInt (3));
rs.close ();
cstmt.close ();
conn.close ();
The key is this?

Must cstmt.executeQuery () to get the result set, using cstmt.execute () and then getResultSet () is not eligible for, and executeQuery () to ensure that the first implementation of the update and then select;
Obtain the output parameters cstmt.getInt (3) the result set must be processed before the implementation of all elements, if the code is changed as follows:
........
doSomeThingToOutParameter (cstmt.getInt (3));
while (rs.next ()) (
doSomeThingToResultSet (rs);
)
........

The result is, in the rs.next () that it will throw exception: java.sql.SQLException: [Microsoft] [SQLServer 2000 Driver for JDBC] Object has been closed.

Very interesting, is not it?

This article comes from CSDN blog, reproduced, please indicate the source: http://blog.csdn.net/kirbylynx/archive/2008/12/09/3483449.aspx

相关文章
  • Java call SQL Server stored procedure parameters and return result sets at the same time 2010-03-31

    Transfer from: http://blog.csdn.net/kirbylynx/archive/2008/12/09/3483449.aspx Such as a SQL Server stored procedure: create procedure proc_test @ Q_type int, @ Value int, @ Count int output as begin update mytable set value = @ value where type = @ q

  • Java call SQL Server stored procedure example 2010-03-22

    Recently made a small Java program (the first to write Java project oh), to the online search for a long time to find a better point of calling a stored procedure example, and are commonly used online setXXX ((int parameterIndex, XXX x) form. This fo

  • SQL Server stored procedure 2010-06-21

    Assist a VC + + project, with the database is SQLServer, the definition of a lot of stored procedures. After corresponding to modify the database structure, the natural need to modify the stored procedure, we first find out. Sql Server stored procedu

  • Learning SQL SERVER stored procedure - one of the knowledge stored procedure syntax 2010-12-04

    CREATE PROCEDURE Create a stored procedure, stored procedure is saved that can accept and return user-supplied parameters of Transact-SQL statement collection. You can create a process for permanent use, or for temporary use in a session (local tempo

  • Detailed SQL SERVER stored procedure 2010-12-10

    Transfer from: http://tieba.baidu.com/f?kz=100695261 Detailed SQL SERVER stored procedure MS SQL Server is now increasingly becoming the most important WindowNT above the operating system of a database management system, with the introduction of MS S

  • SQL SERVER stored procedure, study notes 2011-04-22

    SQL SERVER stored procedure, study notes The common or complex work, written in advance by the SQL statement using a specified name and stored, then later to call the database to provide well-defined stored procedure functions the same service, simpl

  • SQL Server stored procedure return values 2010-02-23

    SQL Server stored procedure return value in three ways, see the following code: 1, the definition of the table table1 create table table1 ( name varchar (30), age integer, primary key (name, age) ) 2, the definition of the stored procedure create pro

  • SQL SERVER stored procedure with large amounts of data into 2010-04-08

    SQL SERVER stored procedure to bulk insert data in database tables so the database system development, especially the need for database operations and optimization of the performance test, we need to test tables in the database to insert large amount

  • Note that little bit of sql server stored procedure 2010-11-10

    sql server stored procedure is provided by the use of Transact-SQL programs written language. It follows the following principles: 1), variable declaration 2), ANSI-compliant SQL commands (Select, Update ....) 3), the general flow control commands (i

  • Optimization of SQL Server stored procedure seven methods [reprint] 2010-12-13

    Optimization of SQL Server stored procedure seven methods Optimized stored procedures there are many ways, the most commonly used are described below 7. 1. Using SET NOCOUNT ON option We use the SELECT statement, in addition to returns the correspond

  • SQL Server stored procedure or function call Com Custom Components 2010-09-21

    Although the MS SQl stored procedure comes with a encryption method, but outside there are software solutions available off. The solution is to write a DLL or OLE program, and then call on the line in a database, SQL, many extended stored procedure i

  • sql server stored procedure, little attention 2010-11-10

    sql server stored procedure is to use it provides Transact-SQL language, the written procedures. It followed several principles: 1), variables description 2), ANSI-compliant SQL commands (Select, Update ....) 3), the general flow control commands (if

  • JDBC stored procedure to read multiple result sets 2010-09-30

    Read about the JDBC SQL Server stored procedure multiple result sets to generate reports of the solution, a former colleague has been resolved, and also wrote a paper issued, and was cited many times! But feel his code more complex, intentions are no

  • Detailed two SQL Server stored procedure: sp_MSforeachtable / sp_MSforeachdb 2010-10-18

    1. Introduction: As a DBA will often need to check all of the database or user table, for example: Check the capacity of all the database; to see all the users to specify the database table capacity, number of records in all tables ..., we generally

  • Learning SQL SERVER stored procedure - one of the stored procedure syntax knowledge 2010-12-04

    CREATE PROCEDURE Create stored procedures, stored procedure is saved can accept and return user-supplied parameters of Transact-SQL statement collection. You can create a process for permanent use, or for temporary use in a session (local temporary p

  • SQL SERVER stored procedure database application developed 2010-07-27

    SQL SERVER database application developed by the stored procedure definition: Stored procedure is acceptable to save up and return the user supplied parameters of Transact-SQL statement collection. Can create a process for permanent use, or for tempo

  • jdbc sql server stored procedure is called when "the statement does not return a result set" solutions 2010-12-30

    Call in the JDBC stored procedures in SQL Server when the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement does not return a result set . at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLSer

  • Detailed two of SQL Server stored procedure: sp_MSforeachtable / sp_MSforeachdb 2010-10-18

    1 Introduction: As a DBA will often need to check all of the database or the user table, for example: check the capacity of all the database; see all user tables in the specified database size, number of records ... all the tables, we generally deal

  • jdbc sql server stored procedure is called when "the statement does not return a result set" solution 2010-12-30

    In JDBC call stored procedure in SQL Server when the following exceptions: com.microsoft.sqlserver.jdbc.SQLServerException: The statement does not return a result set . at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerE

  • About sql server stored procedure and use 2011-10-12

    Syntax: DECLARE { {@ Local_variable data_type} } [,... N] For example: declare @ ID int - declare a variable named @ ID, type int-type Three in the SQL Server window, print out the value of the variable Syntax: PRINT 'any ASCII text' | @ local_variab