I use 0 cursors in our myriad of other non-rpg programs. And some of them are now starting to use ORM's. When I mean cursors I mean SQL Cursors. Of course you still have to loop somehow, but they are not the SQL looping constructs, they are the native language constructs of "for/while"
It seems cursors are the only construct for "looping through records" in RPG. I still have yet to see an example where you retrieve a "set" of records to a temporary variable and then use the regular RPG looping mechanisms to loop through them.
In other languages you populate in an memory dataset and use while loops, for loops, foreach loops, or parallel processing for loops.
While we are on the topic I am still not sure how you fetch 100 records at a time, loop through them and then fetch another 100 records at a time in RPG.
Things like that are a simple affair with an ORM. By simple I mean:
Var result = criteria.SetFirstResult(0).SetMaxResult(10); //Fetch first 10 records
Var result2 = criteria.SetFirstResult(10).SetMaxResults(10); //Fetch next 10 records
This is an nhibernate example starting at first record and getting 10 records and proceeding to get the next 10 records. Behind the scenes the SQL is all generated for you.
Now the ORM way is the extreme end of the spectrum in that it essentially eliminates all your SQL code, instead you just define the data model and it generates the SQL for you. There are merits and downsides to this though, sometimes the generate SQL is not ideal.
You still have the classic approach which generally follows this:
1. Define your connection
2. Open your connection
3. Define your SQL command
4. Retrieve your resuts
5. Iterate through the results
Which on face value is MORE steps then the /EXEC SQL and cursor logic. However steps 1-4 are usually put into "helper" classes so it ends up being a one line affair to get your results in your myriad of other programs:
DataSet ds = DataManager.Fetch("select * from table");
For(DataRow row in ds.tables[0])
{
//Do some record stuff.
}
The helper class in this instance is a static DataManager.
Now compare that to the RPG equivalent:
/exec sql
declare mainCursor cursor
for mainStatement
/end-exec
/exec sql
prepare mainStatement
from :sql
/end-exec
/exec sql
open mainCursor
/end-exec
/exec sql
fetch next from mainCursor
into :mainDS
/end-exec
/free
dow sqlstt = '00000' ;
//Do some logic here
/end-free
/exec sql
fetch next from mainCursor
into :mainDS
/end-exec
/free
enddo ;
/end-free
/exec sql
close mainCursor
/end-exec
/free
*inlr = *on ;
/end-free
I'd love to learn of a less ugly way of doing this in RPG? Move all code to pure SQL using a stored procedure? Another language?
-----Original Message-----
From: Jon Paris [mailto:jon.paris@xxxxxxxxxxxxxx]
Sent: Saturday, October 25, 2014 3:54 PM
To: Midrange-L Midrange-l
Subject: Re: Can I use DDS to create an SQL table name
I'd love to see some examples Matt.
I've used SQL in a number of other languages and I don't feel that any of them resulted in any better/simpler code than embedded SQL in RPG.
If you need more that one cursor you're going to have more than one cursor no matter what the language - so how can RPG be responsible for "To many" [sic]
Jon Paris
www.partner400.com
www.SystemiDeveloper.com
On Oct 24, 2014, at 5:26 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:
My only problem with this, is if you go the SQL route within your RPG programs the code is atrocious. To many cursors and /EXEC SQL commands.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
