I'll try here to show you as you wish an example that a simple SP can do.
See the SQL cursor as a buffer retrieved when the SP is called from the client.
This SP may be called from any client running on any platform provided that the client is ILE compliant.
Example:
Assume you get on the AS/400 a physical file named MYFILE in libray MYLIB with the following records:
[tt] -- FILE1 on server side
Unit code Conveyor Name Chrono# Seq#
LOJOCF LOEVNA LOWBNG LOWCNG
99 CONVEYOR Z380_A 24369 1
99 CONVEYOR Z380_A 24370 1
99 CONVEYOR Z380_A 24371 1
99 CONVEYOR Z380_A 24372 1
99 CONVEYOR Z380_A 24373 1
[/tt]
On the Client side, you want to select either all the records from this file or just the records having a given Chrono#. The choice is performed via the £opt parameter.
1- ILE program MYLIB/MYSP on the server side
Write e.g. a SQLRPGLE program to do the job (this example is taken from the url of your previous thread).
[tt]
* This example is written in ILE-RPG
*
* Define option and chrono as integer
D £opt s 3p 0
D £chrono s 13p 0
* Define chrono
D pchrono s 13p 0
C *entry plist
C parm £opt
C parm £chrono
C eval pchrono = £chrono
C £opt caseq 1 onerec
C £opt caseq 2 allrec
C endcs
C eval *inlr = *on
C return
*
****************************
C onerec begsr
****************************
* Process request for a single record.
C/EXEC SQL DECLARE C1 CURSOR FOR
C+ SELECT
C+ LOJOCF,
C+ LOEVNA,
C+ LOWBNG,
C+ LOWCNG
C+
C+ FROM MYFILE
C+
C+ WHERE LOWBNG =
CHRONO
C+
C+ FOR FETCH ONLY -- READ ONLY CURSOR
C/END-EXEC
C*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C*
C/EXEC SQL
C+ SET RESULT SETS CURSOR C1
C/END-EXEC
C endsr
****************************
C allrec begsr
****************************
* Process request to return all records
C/EXEC SQL DECLARE C2 CURSOR FOR
C+ SELECT
C+ LOJOCF,
C+ LOEVNA,
C+ LOWBNG,
C+ LOWCNG
C+
C+ FROM MYFILE
C+
C+ ORDER BY LOWING, LOWBNG
C+
C+ FOR FETCH ONLY
C/END-EXEC
C*
C/EXEC SQL
C+ OPEN C2
C/END-EXEC
C*
C/EXEC SQL
C+ SET RESULT SETS CURSOR C2
C/END-EXEC
C endsr
[/tt]
2- Create the stored procedure MYLIB/MYSP with command RUNSQLSTM
DROP PROCEDURE MYLIB/MYSP;
CREATE PROCEDURE MYLIB/MYSP(IN OPT DEC (3 , 0), IN CHRONO DEC (13 ,
0)) RESULT SETS 2 LANGUAGE RPGLE SPECIFIC MYLIB/MYSP NOT
DETERMINISTIC CONTAINS SQL EXTERNAL NAME MYLIB/MYSP PARAMETER STYLE
SQL
3- VB/VBA program on the client side
Now write e.g. a VB program or whatever language you use to retrieve the selected records.
The example shown is however written in VB
[tt]
Option Explicit
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim strCnn As String
Dim strSQL As String
Dim parm1 As Variant
Dim parm2 As Variant
Public Sub cmdGetRS_AS400_P()
Set Cnn = New ADODB.Connection
With Cnn
.ConnectionString = strCnn
.CursorLocation = adUseClient
.Open "Provider=IBMDA400;Data Source=AS400_IP#;", "Password", "USER"
End With
strSQL = "MYLIB.MYSP"
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = Cnn
Cmd.CommandText = strSQL
Cmd.CommandType = adCmdStoredProc
Set parm1 = Cmd.CreateParameter("Opt")
parm1.Type = adVarChar
parm1.Direction = adParamInput
parm1.Size = 3
parm1.Value = "001"
Cmd.Parameters.Append parm1
Set parm2 = Cmd.CreateParameter("Chrono")
parm2.Type = adVarChar
parm2.Direction = adParamInput
parm2.Size = 13
parm2.Value = "0000000024371"
Cmd.Parameters.Append parm2
'*** Cmd.Execute
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic
Rs.LockType = adLockReadOnly
Set Rs.Source = Cmd
Rs.Open
' On Error Resume Next
While Not Rs.EOF
' Debug.Print Err, Error(Err)
Debug.Print Rs(0), Rs(1), Rs(2), Rs(3)
Rs.MoveNext
Wend
Debug.Print "Rcd count = "; Rs.RecordCount
Rs.Close
Cnn.Close
Set Cnn = Nothing
Set Cmd = Nothing
Set Rs = Nothing
End Sub
[/tt]
You may either paste this code in a VB program, a VBA Macro in Excel or MS.Access and run it.
If you run the program as is with
parm1.Value = "001"
parm2.Value = "0000000024371"
you'll get only the 3rd record of MYFILE.
You may change
parm1.Value = "002"
parm2.Value = "0000000000000"
to get all the records from MYFILE.
To gain some more in the SPs, you can also check out
that can be of some interest to call iSeries programs using SQL and the JDBC driver.
Hope that helps in your next development.
Philippe
