206 lines
10 KiB
HTML
206 lines
10 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Example: Stored procedure calls from Visual Basic with return values" />
|
||
|
|
<meta name="abstract" content="The following example of Visual Basic source code shows how to call an iSeries server stored procedure and then retrieve the returned values into Visual Basic variables." />
|
||
|
|
<meta name="description" content="The following example of Visual Basic source code shows how to call an iSeries server stored procedure and then retrieve the returned values into Visual Basic variables." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzaikspex.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="vbdef" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Example: Stored procedure calls from Visual Basic with return values</title>
|
||
|
|
</head>
|
||
|
|
<body id="vbdef"><a name="vbdef"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Example: Stored procedure calls from Visual Basic with return values</h1>
|
||
|
|
<div><p>The following example of Visual Basic source code shows how to
|
||
|
|
call an iSeries™ server
|
||
|
|
stored procedure and then retrieve the returned values into Visual Basic variables.
|
||
|
|
</p>
|
||
|
|
<div class="section"><p>Visual Basic is able to call external functions that are found
|
||
|
|
in a DLL. Since all ODBC drivers are DLLs, Visual Basic can be used to code
|
||
|
|
directly to the ODBC APIs. By coding directly to the ODBC APIs a Visual Basic
|
||
|
|
application can call an iSeries server stored procedure and return result
|
||
|
|
values. See <a href="rzaikdirectcodeodbcapis.htm#directcodeodbcapis">Code directly to ODBC APIs</a> for
|
||
|
|
more information.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><pre>'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Because of the way Visual Basic stores and manages the String data *
|
||
|
|
'* type, it is recommended that you use an array of Byte data type *
|
||
|
|
'* instead of a String variable on the <span class="keyword">SQL</span>BindParameter API. *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
Dim sTemp As String
|
||
|
|
Custnum As Integer
|
||
|
|
Dim abCustname(34) As Byte
|
||
|
|
Dim abAddress(34) As Byte
|
||
|
|
Dim abCity(24) As Byte
|
||
|
|
Dim abState(1) As Byte
|
||
|
|
Dim abPhone(14) As Byte
|
||
|
|
Dim abStatus As Byte
|
||
|
|
Dim RC As Integer
|
||
|
|
Dim nullx As Long 'Used to pass null pointer, not pointer to null
|
||
|
|
Dim lpSQL_NTS As Long 'Used to pass far pointer to SQL_NTS
|
||
|
|
Static link(7) As Long 'Used as an array of long pointers to the size
|
||
|
|
'each parameter which will be bound
|
||
|
|
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Initialize the variables needed on the API calls *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
link(1) = 6
|
||
|
|
link(2) = Ubound(abCustname) +1
|
||
|
|
link(3) = Ubound(abAddress) +1
|
||
|
|
link(4) = Ubound(abCity) +1
|
||
|
|
link(5) = Ubound(abState) +1
|
||
|
|
link(6) = Ubound(abPhone) +1
|
||
|
|
link(7) = 1
|
||
|
|
|
||
|
|
RC = 0
|
||
|
|
nullx = 0
|
||
|
|
lpSQL_NTS = SQL_NTS ' -3 means passed as sz string
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Create the procedure on the iSeries. This will define the *
|
||
|
|
'* procedure's name, parameters, and how each parameter is passed. *
|
||
|
|
'* Note: This information is stored in the server catalog tables and *
|
||
|
|
'* and only needs to be executed one time for the life of the stored *
|
||
|
|
'* procedure. It normally would not be run in the client application. *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
sTemp = "Create Procedure Storedp2 (:Custnum in integer, "
|
||
|
|
sTemp = sTemp & ":Custname out char(35), :Address out char(35),"
|
||
|
|
sTemp = sTemp & ":City out char(25), :State out char(2),"
|
||
|
|
sTemp = sTemp & ":Phone out char(15), :Status out char(1))
|
||
|
|
sTemp = sTemp & "(External name rastest.storedp2 language cobol General)"
|
||
|
|
|
||
|
|
RC = SQLExecDirect(Connection.hstmt, sTemp, Len(sTemp))
|
||
|
|
|
||
|
|
'Ignore error assuming that any error would be from procedure already
|
||
|
|
'created.
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Prepare the call of the procedure to the iSeries. *
|
||
|
|
'* For best performance, prepare the statement only one time and
|
||
|
|
'* execute many times.
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
sTemp = "Call storedp2(?, ?, ?, ?, ?, ?, ?)"
|
||
|
|
RC = SQLPrepare(Connection.hstmt, sTemp, Len(sTemp))
|
||
|
|
|
||
|
|
If (RC <> SQL_SUCCESS) Then
|
||
|
|
DescribeError Connection.hdbc, Connection.hstmt
|
||
|
|
frmMain.Status.Caption = "Error on SQL_Prepare " & RTrim$(Tag)
|
||
|
|
End If
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Bind all of the columns passed to the stored procedure. This will *
|
||
|
|
'* set up the variable's data type, input/output characteristics, *
|
||
|
|
'* length, and initial value. *
|
||
|
|
'* The SQLDescribeParam API can optionally be used to retrieve the
|
||
|
|
'* parameter types.
|
||
|
|
'* *
|
||
|
|
'* To properly pass an array of byte to a stored procedure and receive *
|
||
|
|
'* an output value back, you must pass the first byte ByRef. *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, _
|
||
|
|
SQL_NUMERIC, 6, 0, Custnum, 6, link(1))
|
||
|
|
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 35, 0, abCustname(0), UBound(abCustname)+1, link(2))
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 35, 0, abAddress(0), UBound(abAddress)+1, link(3))
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 25, 0, abCity(0), UBound(abCity)+1, link(4))
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 2, 0, abState(0), UBound(abState)+1, link(5))
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 6, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 15, 0, abPhone(0), UBound(abPhone)+1, link(6))
|
||
|
|
RC = SQLBindParameter(Connection.hstmt, 7, SQL_PARAM_OUTPUT, SQL_C_CHAR, _
|
||
|
|
SQL_CHAR, 1, 0, abStatus, 1, link(7))
|
||
|
|
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* The Prepare and Bind only needs to be execute once. The Stored
|
||
|
|
'* procedure can now be called multiple times by just changing the data
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
Do While
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* Read in a customer number *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
Custnum = Val(input.text)
|
||
|
|
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Execute the call of the procedure to the iSeries. *
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
RC = SQLExecute(Connection.hstmt)
|
||
|
|
frmMain.Status.Caption = "Ran Stored Proc" & RTrim$(Tag)
|
||
|
|
|
||
|
|
If (RC <> SQL_SUCCESS) Then
|
||
|
|
DescribeError Connection.hdbc, Connection.hstmt
|
||
|
|
frmMain.Status.Caption = "Error on Stored Proc Execute " & RTrim$(Tag
|
||
|
|
End If
|
||
|
|
|
||
|
|
'***********************************************************************
|
||
|
|
'* *
|
||
|
|
'* Set text labels to display the output data *
|
||
|
|
'* You must convert the array of Byte back to a String
|
||
|
|
'* *
|
||
|
|
'***********************************************************************
|
||
|
|
|
||
|
|
|
||
|
|
lblCustname = StrConv(abCustname(), vbUnicode)
|
||
|
|
lblAddress = StrConv(abAddress(), vbUnicode)
|
||
|
|
lblCity = StrConv(abCity(), vbUnicode)
|
||
|
|
lblState = StrConv(abState(), vbUnicode)
|
||
|
|
lblPhone = StrConv(abPhone(), vbUnicode)
|
||
|
|
lblStatus = StrConv(abStatus(), vbUnicode)
|
||
|
|
|
||
|
|
Loop</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzaikspex.htm" title="View examples of stored procedures.">Examples: Stored procedures</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|