Returning multiple records via a db access module 1

[robT78]

Hello all,

Does anyone have experience in creating modules that act as segregating layers between the database and the business logic/GUI/etc?

We're trying to embrace this new component-based world and create single components that solely provide db functions.

We had a crack at this last year and ended up using user spaces to return multiple records but came up against a maximum barrier of 16MB.

Anyone know of a better method here? (or should we just settle for direct file access?)

Cheers,
Rob

 

[Mercury2]

.. create single components that solely provide db functions...
You mean like SQL and/or stored procedures do ?
Nab

 

[JonParis]

16Mb is too small? The "simple" answer is to simply link multiple user spaces by placing an entry in each that identifies the next in the chain. But that's an ugly answer and I wonder if you _really_ need to do what you're asking. After all - if returning to single record I/O in the program is an answer, then why not simply return subsets?

Most of the time when isolating I/O functions you are either retrieving a single item or a set of items, for subsequent display to the user. Most of the applications that I have seen use that I/O functions will return a set - but only of (say) 20 items that match. If you want the next set then you request that and so on. The size of the set should match what the user will see in the "subfile". After all why waste time reading what may never be used by the user.

If you _really_ have a requirement for result sets of infinite size (if 16M isn't enough you may as well assume infitinte!) then use the right tool which is SQL. Use the CLI interface if you don't have the product and cannot embed SQL in your RPG.

 

[robT78]

many thanks guys,

had a feeling we were heading down the wrong track...!
or using the wrong tool...!

i've very limited experience of embedding SQL (powers that be are a bit protective!) but have done it here before so assume we have the right product (?) - how would you wrap up the embedded SQL into a re-usable module that would pass back multiple records?

do you know if there is any literature on the subject?

talkturkey - what do you mean by stored procedures? are these (IBM) generic procedures that can be tapped into to provide db functions? or bespoke? (we created a load of db procedures that sat within an ILE module at last attempt)

cheers,
rob

 

[Mercury2]

Yes robT78, this is almost what I mean, but not only simple functions. A Stored Procedure can return resultsets composed of some or many records that you can tune for your needs as JonParis said above. You can create SP's that sit on the 400 in any HLL language or SQL Language(SPL) to fit your needs. But you already know that, don't you ?

 

[robT78]

errr - no. hadn't heard of SPs until you mentioned them.
should i have?

infoseeker has an example:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaikodbcrpghostex.htm

but i still don't quite understand how the result sets get back to the calling pgm/client?

is the sql cursor returned outside of the parameter list?
and how?

(i guess what I'm really after is an example of the client code)

many thanks for your help - this definately looks like the way forward. cheers, rob

 

[Mercury2]

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

http://www.midrangeserver.com/mpo/mpo031402-story06.html

that can be of some interest to call iSeries programs using SQL and the JDBC driver.

Hope that helps in your next development.
Philippe

 

[Mercury2]

And fell free to ask me any related questions. I'll try to give the the best answers as I can.
Regards
Philippe

 

[robT78]

cool - cheers

related question #1:
you wouldn't happen to have an example of some RPG client code, would you? (i've spent 30mins trawling the web to no avail!)

related question #2:
would the RPG client need to use SQLCLI to return RESULT SETS or is there another way?

thanks, Rob

 

[Mercury2]

Hello,

As you already probably know, RPG is usually the language used on the server side of a Client/Server application and really not the best client language to retrieve a result sets from SP be it written in RPGLE or any other HLL language.
C, C++, Delphi, VB, etc, are frequently used to do this kind of job on the Windows machines.
It seemed to me that it was not possible or at least a difficult task that finally shouldn't bring much benefit. So I made a Google search (with search criteria RPG AND "result set" AND CLI) and noticed 2 important urls that confirm what I say :

http://archive.midrange.com/rpg400-l/200105/msg00608.html

- Question
"Is it possible to retrieve stored procedure result set(s) using RPGIV? If so, can someone point me to the manual or an example?"
- Answer
"it could only be done using CLI programming, and could be found in the book "Who Knew You Could Do That with RPG
IV? A Sorcerer’s Guide to System Access and More", SG24-5402-00, which I guess you already know of. I don't know if it will be available in future releases for normal RPG IV, but as of today CLI seems to be the only way to do it."

http://search400.techtarget.com/ateQuestionNResponse/0,289625,sid3_cid520336_tax285216,00.html

- Question:
"Is there an easy way to access the results of a call to a stored procedure from the "green screen" side?"
- Here is the top notch guru Kent Milligan's answer :
"None of the green screen SQL interfaces support a stored procedure call that returns a result set. SQL CLI does have the support to process a result set [...]. The SQL CLI calls can be embedded into C, RPG, & COBOL programs that can easily be called from green screen batch process."

I think that now you get direct responses to your questions and that I could shed some light on this.
Don't forget to look at the interesting book Who Knew You Could Do That with RPG IV? A Sorcerer’s Guide to System Access and More
And, as usual, fell free to ask me any related questions.
HTH
Regards
Philippe