RE: Can I use DDS to create an SQL table name -- MIDRANGE-L

How would you utilize this technique if you can't predict how many records your select will return? In the example below it looks like you know that you will never get more than 100 records.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, October 27, 2014 10:00 AM
To: Midrange Systems Technical Discussion
Subject: Re: Can I use DDS to create an SQL table name

Matt,

It's simple to fetch multiple rows into an array or multi-occurrence DS...

Just use
fetch myCursor for 100 rows into :myArray;

SQLERRD(3) shows you the number of rows actually returned...

If you're going to fetch 1 row at a time, you're better off with native RLA.

Charles

On Mon, Oct 27, 2014 at 9:36 AM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

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.

--
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.

--
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.