Go Java calls SQL Server for a detailed description of stored procedures

This article was reproduced from: http://dev.yesky.com/128/8088128.shtml

1 using stored procedures with no parameters

When you invoke a stored procedure without parameters using the JDBC driver, you must use the call SQL escape sequence. The syntax for a call escape sequence without parameters is as follows:

　　

The following is a reference fragment: {Call Procedure-name}

As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:

　　

The following is a reference fragment: CREATE PROCEDURE Getcontactformalnames As BEGIN SELECT TOP Ten Title + ' + FirstName + ' + LastName as Formalname From Person.Contact END

This stored procedure returns a single result set that contains a column of data (consisting of the salutation, name, and last names of the first 10 contacts in the Person.Contact table).

In the following instance, the open connection to the AdventureWorks sample database is passed to this function, and then the getcontactformalnames stored procedure is invoked using the ExecuteQuery method.

The following is a reference fragment: public static void Executesprocnoparams (Connection con) ... { Try ... { Statement stmt = Con.createstatement (); ResultSet rs = stmt.executequery ("{call dbo. Getcontactformalnames} "); 　　 while (Rs.next ()) ... { System.out.println (rs.getstring ("Formalname")); } Rs.close (); Stmt.close (); } catch (Exception e) ... { E.printstacktrace (); } }

　　2 using a stored procedure with input parameters

When you invoke a stored procedure with parameters using the JDBC driver, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for a call escape sequence with an in parameter is as follows:

　　

The following is a reference fragment: {Call procedure-name[([Parameter][,[parameter]] ...)}

When constructing a call escape sequence, use the? (question mark) character to specify an in parameter. This character acts as a placeholder for the parameter value to pass to the stored procedure. You can use one of the setter methods of the Sqlserverpreparedstatement class to specify a value for the parameter. The setter method that can be used is determined by the data type of the in parameter.

When passing a value to a setter method, you need to specify not only the actual value to be used in the parameter, but also the ordinal position of the parameter in the stored procedure. For example, if a stored procedure contains a single in parameter, its ordinal value is 1. If a stored procedure contains two parameters, the first ordinal value is 1, and the second ordinal value is 2.

As an instance of how to invoke a stored procedure that contains an in parameter, use the SQL Server 2005 AdventureWorks uspGetEmployeeManagers stored procedure in the sample database. This stored procedure takes a single input parameter named EmployeeID, which is an integer value, and then returns a recursive list of employees and their managers based on the specified EmployeeID. The following is the Java code that calls this stored procedure:

　　

The following is a reference fragment: public static void Executesprocinparams (Connection con) ... { Try ... { PreparedStatement pstmt = con.preparestatement ("{Call Dbo.uspgetemployeemanagers (?)}"); Pstmt.setint (1, 50); ResultSet rs = Pstmt.executequery (); while (Rs.next ()) ... { System.out.println ("EMPLOYEE:"); System.out.println (rs.getstring ("LastName") + "," + rs.getstring ("FirstName"); System.out.println ("MANAGER:"); System.out.println (rs.getstring ("managerlastname") + "," + rs.getstring ("Managerfirstname"); System.out.println (); } Rs.close (); Pstmt.close (); } catch (Exception e) ... { E.printstacktrace (); } }

　　3 using a stored procedure with output parameters

When calling such stored procedures using the JDBC driver, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for a call escape sequence with an out parameter is as follows:

　　

The following is a reference fragment: {Call procedure-name[([Parameter][,[parameter]] ...)}

When constructing a call escape sequence, use the? (question mark) character to specify an out parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for an out parameter, you must specify the data type of each parameter by using the Registeroutparameter method of the Sqlservercallablestatement class before running the stored procedure.

The value that you specify for the out parameter by using the Registeroutparameter method must be one of the JDBC data types that java.sql.Types contains, and it is mapped to one of the cost SQL Server data types. For more information about JDBC and SQL Server data types, see Understanding JDBC Driver data types.

When you pass a value to the Registeroutparameter method for an out parameter, you must not only specify the data type to use for this parameter, but you must specify the ordinal position of the parameter or the name of the parameter in the stored procedure. For example, if a stored procedure contains a single out parameter, its ordinal value is 1, and if the stored procedure contains two parameters, the first ordinal value is 1 and the second ordinal value is 2.

As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database: According to the specified integer in parameter (employeeID), the stored procedure also returns a single integer out parameter (managerid). The value returned in the Employeeid,out parameter contained in the HumanResources.Employee table is ManagerID.

In the following instance, the open connection to the AdventureWorks sample database is passed to this function, and then the Getimmediatemanager stored procedure is invoked using the Execute method:

The following is a reference fragment: Public static void executestoredprocedure (Connection con)   {  try  .... {  callablestatement cstmt = con.preparecall ("{call dbo. Getimmediatemanager (?,  ?)} ");  cstmt.setint (1, 5);  cstmt.registeroutparameter (2, java.sql.types.integer);  cstmt.execute ();  system.out.println ("manager id: "  + cstmt.getint (2));  }  catch  (exception e)   ..... {  e.printstacktrace ();  } }

This example uses an ordinal position to identify the parameter. Alternatively, you can use the name of the parameter, rather than its ordinal position, to identify this parameter. The following code example modifies the previous example to show how to use named parameters in a Java application. Note that these parameter names correspond to the parameter names in the definition of the stored procedure: CREATE PROCEDURE Getimmediatemanager

　　

The following is a reference fragment: @employeeID INT, @managerID INT OUTPUT As BEGIN SELECT @managerID = ManagerID From HumanResources.Employee WHERE EmployeeID = @employeeID END

The stored procedure may return an update count and multiple result sets. Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, which specifies that multiple result sets and update counts should be retrieved before retrieving out parameters. That is, the application should retrieve all ResultSet objects and update counts first, and then use the Callablestatement.getter method to retrieve the out parameters. Otherwise, when the out parameter is retrieved, the ResultSet object and the update count that have not been retrieved are lost.

　4 using a stored procedure with a return state

When calling this stored procedure using the JDBC driver, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for a call escape sequence that returns a status parameter is as follows:

　

The following is a reference fragment: {[? =]call procedure-name[([Parameter][,[parameter]] ...)}

When constructing a call escape sequence, use the? (question mark) character to specify the return status parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for the return status parameter, you must use the Registeroutparameter method of the Sqlservercallablestatement class to specify the data type of the parameter before executing the stored procedure.

In addition, when you pass the return status parameter value to the Registeroutparameter method, you need to specify not only the data type of the parameter to use, but also the ordinal position of the parameter in the stored procedure. For a return status parameter, its ordinal position is always 1, because it is always the first parameter when a stored procedure is called. Although the Sqlservercallablestatement class supports using the name of the parameter to indicate a specific parameter, you can only use the ordinal position number of the parameter for the return state parameter.

As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:

　　

The following is a reference fragment: CREATE PROCEDURE checkcontactcity (@cityName CHAR (50)) As BEGIN IF ((SELECT COUNT (*) From Person.Address WHERE city = @cityName) > 1) RETURN 1 ELSE RETURN 0 END

The stored procedure returns a status value of 1 or 0, depending on whether the city specified by the CityName parameter can be found in table person.address.

In the following instance, the open connection to the AdventureWorks sample database is passed to this function, and then the checkcontactcity stored procedure is invoked using the Execute method:

　

The following is a reference fragment: Public static void executestoredprocedure (Connection con)   {  try  .... {  callablestatement cstmt = con.preparecall ("{?  = call dbo. Checkcontactcity (?)} ");  cstmt.registeroutparameter (1, java.sql.types.integer);  cstmt.setstring (2,  "Atlanta");  cstmt.execute ();  system.out.println ("return status: "  + cstmt.getint (1));  }  cstmt.close ();  catch  (exception e)   ..... {  e.printstacktrace ();  } }

5 using a stored procedure with update count

After you use the Sqlservercallablestatement class to build a call to a stored procedure, you can use either the Execute or the Executeupdate method to invoke the stored procedure. The Executeupdate method returns an int value that contains the number of rows affected by this stored procedure, but the Execute method does not return this value. If you use the Execute method and you want to count the number of rows affected, you can call the Getupdatecount method after you run the stored procedure.

As an instance, create the following tables and stored procedures in the SQL Server 2005 AdventureWorks Sample database:

　　

The following is a reference fragment: CREATE TABLE TestTable (Col1 int IDENTITY, Col2 varchar (50), Col3 int); 　　 CREATE PROCEDURE updatetesttable @Col2 varchar (50), @Col3 int As BEGIN UPDATE TestTable SET Col2 = @Col2, Col3 = @Col3 END;

In the following instance, the open connection to the AdventureWorks sample database is passed to this function, and the updatetesttable stored procedure is invoked using the Execute method, and then the row count affected by the stored procedure is returned using the Getupdatecount method.

The following is a reference fragment: Public static void executeupdatestoredprocedure (Connection con)   {  try  .... {  callablestatement cstmt = con.preparecall ("{call dbo. Updatetesttable (?,  ?)} ");  cstmt.setstring (1,  "A");  cstmt.setint (2, 100);  cstmt.execute ();  int count = cstmt.getupdatecount ();  cstmt.close ();  system.out.println ("rows affected: "  + count);  }  catch  (exception e)   ..... {  e.printstacktrace ();  } }