Attribute VB_Name = "spCall"
'----------------------------------------------------------------------------
'
' (c) Copyright IBM Corp. 2007 All rights reserved.
'
' The following sample of source code ("Sample") is owned by International
' Business Machines Corporation or one of its subsidiaries ("IBM") and is
' copyrighted and licensed, not sold. You may use, copy, modify, and
' distribute the Sample in any form without payment to IBM, for the purpose of
' assisting you in the development of your applications.
'
' The Sample code is provided to you on an "AS IS" basis, without warranty of
' any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
' IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
' not allow for the exclusion or limitation of implied warranties, so the above
' limitations or exclusions may not apply to you. IBM shall not be liable for
' any damages you suffer as a result of using, copying, modifying or
' distributing the Sample, even if IBM has been advised of the possibility of
' such damages.
'----------------------------------------------------------------------------
'
' SOURCE FILE NAME: spCall.bas
'
' SAMPLE: How to call stored procedures
'
' This module calls the stored procedures in C, C++, CLI or JAVA.
' Most of the stored procedures are written in C, C++, CLI and JAVA,
' but some of them are only supported in one or more languages.
'
' Before running this sample, you must create and catalog the
' stored procedures in the spserver stored procedure library.
' Versions of the spserver sample program are provided with
' the C, C++, CLI, and Java samples. To create and catalog
' the spserver sample in the language of your choice,
' please follow the directions in the corresponding
' README file.
'
' STORED PROCEDURES IN C/C++/CLI/JAVA:
' OUT_LANGUAGE
' IN_PARAMS
' OUT_PARAM
' INOUT_PARAM
' ALL_DATA_TYPES
' CLOB_EXTRACT
' ONE_RESULT_SET
' TWO_RESULT_SETS
'
' STORED PROCEDURES ONLY IN C/C++/CLI:
' DB2SQL_EXAMPLE
' DBINFO_EXAMPLE
' MAIN_EXAMPLE
'
' STORED PROCEDURES ONLY IN JAVA:
' DECIMAL_TYPE
'
' FORMS USED:
' frmMain (Demo.frm)
'
'----------------------------------------------------------------------------
'
' For more information on the sample programs, see the README file.
'
' For information on building ADO applications with Visual Basic,
' see the Developing ADO.NET and OLE DB Applications book.
'
' For information on using SQL statements, see the SQL Reference.
'
' For the latest information on programming, compiling, and running DB2
' applications, visit the DB2 Information Center:
' http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
'----------------------------------------------------------------------------
Option Explicit
'This procedure calls the stored procedure OUT_LANGUAGE, which returns
'the language in which the stored procedure implemented.
Public Function CallSP_OUT_LANGUAGE(con As ADODB.Connection) As String
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "OUT_LANGUAGE"
cmd.CommandType = adCmdStoredProc
'
' The following section of code is not required when using:
' cmd.Parameters.Refresh
' But it can make execution faster. It eliminates the need
' for ADO to fetch the parameter metrics from the server.
'
'define parameter(s)
Dim param As New ADODB.Parameter
With param
.Name = "LANGUAGE"
.Type = adChar
.Size = 8
.Direction = adParamOutput
cmd.Parameters.Append param
End With
Set param = Nothing
'call the stored procedure
cmd.Execute
'return the result
CallSP_OUT_LANGUAGE = cmd.Parameters("LANGUAGE").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure IN_PARAMS, which takes
'input parameters and modifies the salary of employees in the
'"STAFF" table
Public Function CallSP_IN_PARAMS(con As ADODB.Connection)
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "IN_PARAMS"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameters
cmd.Parameters("LOWSAL").Value = 15000
cmd.Parameters("MEDSAL").Value = 20000
cmd.Parameters("HIGHSAL").Value = 25000
cmd.Parameters("DEPARTMENT").Value = "E11"
'call the stored procedure
cmd.Execute
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure OUT_PARAM, which returns
'the median salary of employees in the "STAFF" table
Public Function CallSP_OUT_PARAM(con As ADODB.Connection) As Double
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "OUT_PARAM"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'call the stored procedure
cmd.Execute
'return the result
CallSP_OUT_PARAM = cmd.Parameters("MEDIANSALARY").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure INOUT_PARAM, which takes
'a parameter as input and returns to the same parameter as output
'the median salary of employees whose salary is greater than the
'input parameter in the "STAFF" table
Public Function CallSP_INOUT_PARAM(con As ADODB.Connection, _
ByVal dblMedian As Double) As Double
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "INOUT_PARAM"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameters
cmd.Parameters("MEDIANSALARY").Value = dblMedian
'call the stored procedure
cmd.Execute
'return the result
CallSP_INOUT_PARAM = cmd.Parameters("MEDIANSALARY").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure ALL_DATA_TYPES, which
'returns values in various data types
Public Function CallSP_ALL_DATA_TYPES(con As ADODB.Connection) _
As Parameters
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "ALL_DATA_TYPES"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameters
cmd.Parameters("SMALL").Value = 32000
cmd.Parameters("INTIN").Value = 2147483647
cmd.Parameters("BIGIN").Value = 922337203685480#
cmd.Parameters("REALIN").Value = 100000
cmd.Parameters("DOUBLEIN").Value = 2500000
'call the stored procedure
cmd.Execute
'return the result
Set CallSP_ALL_DATA_TYPES = cmd.Parameters
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure DECIMAL_TYPE, which
'returns a value in decimal data type
Public Function CallSP_DECIMAL_TYPE(con As ADODB.Connection) As Variant
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "DECIMAL_TYPE"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameters
cmd.Parameters("DECIMALIN").Value = 400000
'call the stored procedure
cmd.Execute
'return the result
CallSP_DECIMAL_TYPE = cmd.Parameters("DECIMALIN").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure CLOB_EXTRACT, which returns
'the department information portion of the resume of an employee
Public Function CallSP_CLOB_EXTRACT(con As ADODB.Connection) As String
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "CLOB_EXTRACT"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameters
cmd.Parameters("NUMBER").Value = "000140" 'employee ID
'call the stored procedure
cmd.Execute
'return the result
CallSP_CLOB_EXTRACT = cmd.Parameters("BUFFER").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure ONE_RESULT_SET and passes
'an IN parameter to the stored procedure. It returns a recordset
'that contains all rows from the "STAFF" table where "SALARY" is
'greater than the value of the IN parameter.
Public Function CallSP_ONE_RESULT_SET(con As ADODB.Connection) _
As ADODB.Recordset
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "ONE_RESULT_SET"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameter value
'(it can be any arbitrary value, 17645 is the median)
cmd.Parameters("SALVALUE").Value = 17645
'call the stored procedure and return a recordset
Set CallSP_ONE_RESULT_SET = cmd.Execute
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure TWO_RESULT_SETS and passes
'an IN parameter to the stored procedure. It returns two recordsets,
'one contains all rows from the "STAFF" table where "SALARY" is
'greater than the value of the IN parameter, and the other one contains
'all rows from the "STAFF" table where "SALARY" is less than the value
'of the IN parameter.
Public Function CallSP_TWO_RESULT_SETS(con As ADODB.Connection) _
As ADODB.Recordset
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "TWO_RESULT_SETS"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameter value
'(it can be any arbitrary value, 17645 is the median)
cmd.Parameters("SALARY").Value = 17645
'call the stored procedure and return a recordset
Set CallSP_TWO_RESULT_SETS = cmd.Execute
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure DB2SQL_EXAMPLE and passes
'a JOB as an IN parameter to the stored procedure. It returns an
'average salary of the job as an OUT parameter by executing a SQL
'statement in the stored procedure.
Public Function CallSP_DB2SQL_EXAMPLE(con As ADODB.Connection, _
strJob As String) As Double
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "DB2SQL_EXAMPLE"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameter value
cmd.Parameters("JOB").Value = strJob
'call the stored procedure
cmd.Execute
'return the result
CallSP_DB2SQL_EXAMPLE = cmd.Parameters("SALARY").Value
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure DB2SQL_EXAMPLE and passes
'a JOB as an IN parameter to the stored procedure. It returns a
'set of parameters containing informations of the table and the
'database.
Public Function CallSP_DBINFO_EXAMPLE(con As ADODB.Connection, _
strJob As String) _
As ADODB.Parameters
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "DBINFO_EXAMPLE"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameter value
cmd.Parameters("JOB").Value = strJob
'call the stored procedure
cmd.Execute
'return the result
Set CallSP_DBINFO_EXAMPLE = cmd.Parameters
'release the object
Set cmd = Nothing
End Function
'This procedure calls the stored procedure MAIN_EXAMPLE and passes
'a JOB as an IN parameter to the stored procedure. It returns an
'average salary of the job as an OUT parameter by executing a SQL
'statement in the stored procedure.
Public Function CallSP_MAIN_EXAMPLE(con As ADODB.Connection, _
strJob As String) As Double
'setup connection and datasource
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = con
'setup stored procedure info to be called
cmd.CommandText = "MAIN_EXAMPLE"
cmd.CommandType = adCmdStoredProc
'fill in parameter info from stored procedure
cmd.Parameters.Refresh
'set up the parameter value
cmd.Parameters("JOB").Value = strJob
'call the stored procedure
cmd.Execute
'return the result
CallSP_MAIN_EXAMPLE = cmd.Parameters("SALARY").Value
'release the object
Set cmd = Nothing
End Function
'This procedure gets all available stored procedure names
Public Function GetProcedureNames(con As ADODB.Connection) As String
Dim cat As New ADOX.Catalog
Dim pro As ADOX.Procedure
Dim strTokens As String
'open the Catalog
cat.ActiveConnection = con
'refresh the Procedures collection
cat.Procedures.Refresh
'return names of all Procedures
strTokens = ""
For Each pro In cat.Procedures
strTokens = strTokens & pro.Name & " "
Next
GetProcedureNames = strTokens
'release the objects
Set pro = Nothing
Set cat = Nothing
End Function
'This procedure gets all available stored procedure names
Public Function GetProcedures(con As ADODB.Connection) As Object
'define and initialize new Catalog object
Dim cat As New ADOX.Catalog
cat.ActiveConnection = con
'refresh the Procedures collection
cat.Procedures.Refresh
'return information of all Procedures
Set GetProcedures = cat.Procedures
'release the objects
Set cat = Nothing
End Function