Using .NET to Call iSeries Stored Procedures

Hey, Howard:

I need to call a stored procedure using [Microsoft] .NET. Do you have any experience with this?

--Ivana

.NET is starting to catch on as a Web programming environment, and it is possible to call stored procedures using an ODBC driver and the System.Data.Odbc framework classes. These classes came out after the initial release of .NET, but are shipped in the .NET 1.1 framework. If you do not have the ODBC framework classes, you can download them from Microsoft's Web site. Of course, to make your life easier you could just download .NET Framework Version 1.1.

Let's create a stored procedure, then go through what it takes to call the procedure. Later, I will show you how to code a working Web page with ASP.NET that calls a stored procedure and returns the results into a text field.

Creating the Procedure

First, create a table called SEQCTRL in the SQLBOOK library. This table will hold information about sequence numbers. Here is the SQL for creating the table and loading it with initial data:

CREATE TABLE SQLBOOK.SEQCTRL
	(SEQID	CHAR(10) NOT NULL PRIMARY KEY,
	 VLU INTEGER NOT NULL WITH DEFAULT);
	 
INSERT INTO SQLBOOK.SEQCTRL VALUES ('WO',1);
INSERT INTO SQLBOOK.SEQCTRL VALUES ('PO',1);

Next, issue the following SQL Create Procedure statement to create a procedure called SEQCTRL in the SQLBOOK library. This procedure takes an input argument for the kind of sequence number that you want and returns that sequence number in the second argument.

CREATE PROCEDURE SQLBOOK.GETSEQ
       ( IN REQKEY CHARACTER(10),
       INOUT RETVAL INTEGER )
 RESULT SETS 0
 LANGUAGE SQL 
 NOT DETERMINISTIC 
/*Begin Procedure Code*/
BEGIN 
DECLARE C1 CURSOR FOR 
 SELECT VLU + 1 FROM SQLBOOK . SEQCTRL 
   WHERE SEQID = REQKEY FOR UPDATE OF VLU ;
 OPEN C1 ;
 FETCH C1 INTO RETVAL ;
 UPDATE SQLBOOK . SEQCTRL SET VLU = RETVAL 
   WHERE CURRENT OF C1 ;
 END

Now you are ready to call the procedure, using .NET. The following code uses the classes available in the System.Data.Odbc namespace to call the procedure. It could be placed into a Web form or executed in response to a button click in a Windows form.

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
 'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand
        
'Open a connection to an iSeries data source       
OdbcCon.ConnectionString = "DSN=My400;UID=HARNER;PWD=Secret"
        
 'Set up the procedure call
OdbcCmd.CommandText = "{CALL SQLBOOK.GETSEQ( ?, ? )}"
OdbcCmd.CommandType = CommandType.StoredProcedure
 'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
 'Open the connection
OdbcCon.Open()
 'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("", Odbc.OdbcType.Char, _
                10).Direction = ParameterDirection.InputOutput
OdbcCmd.Parameters.Add("", Odbc.OdbcType.Int _
            ).Direction = ParameterDirection.InputOutput
 'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = "WO"
OdbcCmd.Parameters(1).Value = 0
 'call the procedure
OdbcCmd.ExecuteNonQuery()
 'put the results into a textbox control
txtResult.Text = OdbcCmd.Parameters(1).Value
 'close the connection
OdbcCon.Close()

First, the code declares the two objects we need to call a procedure, an ODBC Connection object and an ODBC Command object. The code then sets various properties of the command and connection objects to establish a connection to the iSeries, to tell the iSeries what procedure we wish to call, to set up the parameters we are going to pass to the procedure, and to initialize the parameter values. Finally, the code calls the ExecuteNonQuery method of the command object to cause the iSeries to execute the stored procedure and to return the results. The resulting parameter can then be examined by looking at the Value property of the command’s parameters collection.

Calling a Procedure from an ASPX Web Page

If you have IIS (Internet Information Server) installed on your machine, you can set up a Web page that calls a stored procedure. In addition to IIS, you will need to have the .NET Framework installed on your machine. First, make a subdirectory called AS4Samp in your www root folder, then copy the following HTML code into a file called PTEST.ASPX.

<%@ Page Language="vb" src="PTEST.ASPX.VB" inherits="MyTest"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
  <title>Calling a 400 Procedure</title>
</HEAD>
<body >
 <form id="Form1" method="post" runat="server">
  <TABLE id="Table1" cellSpacing="1" 
       cellPadding="1" width="300" border="1">
  <TR>
       <TD align="center" bgColor="blue" colSpan="3"><FONT 
       color="#ffffff">Procedure Calling .Net Example</FONT></TD>
 </TR>
 <TR>
  <TD align="right" colSpan="" rowSpan="">ID Field:</TD>
  <TD colSpan="2">
<asp:TextBox id="txtID" value="WO" runat="server">
       </asp:TextBox></TD>
 </TR>
 <TR>
  <TD align="right" colSpan="" rowSpan="">Result:</TD>
  <TD colSpan="2">
     <asp:TextBox id="txtResult" runat="server" ReadOnly="True">
     </asp:TextBox>
  </TD>
 </TR>
 <TR>
  <TD align="right" colSpan="3">
   <asp:Button id="Button1"  runat="server"  Text="Submit">
   </asp:Button></TD>
 </TR>
</TABLE>
</form>
</body>
</HTML>

The code above creates a simple Web form and uses server-side ASP controls to render text boxes. Note that the server-side ASP controls are highlighted in green type. These controls will be evaluated on the server, and you can set their properties and call their methods via code. So the above code renders an HTML page, which has three server side controls: txtID, txtResult, and Button1. Also note that the txtResult control is set to read-only mode. To use this form, create a file called PTEST.ASPX.VB and paste the following code into that file:

imports microsoft.visualbasic.strings
imports system.data
imports system.data.odbc

Public Class MyTest
 Inherits System.Web.UI.Page 
 Protected WithEvents txtID As System.Web.UI.WebControls.TextBox
 Protected WithEvents txtResult As System.Web.UI.WebControls.TextBox
 Protected WithEvents Button1 As System.Web.UI.WebControls.Button

'Here is code for the button click event

 public Sub Button1_Click(ByVal sender As System.Object, ByVal e As
       System.EventArgs) Handles Button1.Click
  Dim OdbcCon As OdbcConnection
  Dim OdbcCmd As OdbcCommand
  OdbcCon = New OdbcConnection
  OdbcCmd = New OdbcCommand
  OdbcCon.ConnectionString = _
             "DSN=Holley;UID=HARNER;PWD=secret"
  OdbcCmd.CommandText = "{CALL SQLBOOK.GETSEQ( ?, ? )}"
  OdbcCmd.CommandType = CommandType.StoredProcedure
  OdbcCmd.Connection = OdbcCon
  OdbcCon.Open()
  OdbcCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Char, _
                10).Direction = ParameterDirection.InputOutput
  OdbcCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int _
             ).Direction = ParameterDirection.InputOutput
  OdbcCmd.Parameters(0).Value = Trim(txtID.Text)
  OdbcCmd.Parameters(1).Value = 0
  OdbcCmd.ExecuteNonQuery()
  txtResult.text=OdbcCmd.Parameters(1).Value
  OdbcCon.Close()
 End Sub

end class

The code above is associated with the Web page by the first line in the HTML file:

<%@ Page Language="vb" src="PTEST.ASPX.VB" inherits="MyTest"%> 

This line informs .NET that the programming language of the page is Visual Basic and that the source code for the page exists in the file PTEST.ASPX.VB, and this page inherits a class called MyTest. This technique of coding is called "code behind," as it allows you to place the application code in a file separate from the HTML presentation code. When the Web server renders the HTML page, it will read this directive and bring all of the code in the PTEST.ASPX.VB file into this page and will then compile the page. Code behind makes it easy to separate application logic from your HTML display.

The VB code creates a public class called MyTest, which will be used to encapsulate the logic we need to call the stored procedure and handle events on the HTML page. This class consists of a public subroutine called Button1_Click, which will handle the Click event fired when a user clicks the button on the HTML form. Clicking the button causes the form to be posted back to the Web server, which then calls the Button1_Click subroutine in the MyTest class. The Button1_Click subroutine then connects to the iSeries, sets up the parameters, and calls the stored procedure. Any results from the procedure call are returned into the Web form by setting the .Text property of the txtResult text box to the value stored in the ODBC command’s parameter collection.

To view the sample, simply open a browser window and surf to the address localhost/PTEST.ASPX. You should see something similar to the following:

Once the button is clicked, you should see the results of the procedure call in the Results text box.

Howard F. Arner, Jr., is the author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities, and Import Expert 400, a program for quickly importing data from almost any data source to the iSeries. You can purchase a copy of Howard's book or learn more about his products at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com.

Sponsored By ADVANCED SYSTEMS CONCEPTS

WHAT IF Creating Queries, Reports and Downloads was Easy, Fast and Convenient for ALL Users? - - - Make it happen with SEQUEL ! - - - Get a Free Trial today. Read More

---

Editors: Howard Arner, Joe Hertvik, Ted Holt, David Morris,

Managing Editor: Shannon Pastore

Publisher and Advertising Director: Jenny Thomas

Advertising Sales Representative: Kim Reed

Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

---