ORM is the acronym for object relational mapper. It's software that you can use in object oriented languages where you define your database model once, and just retrieve records using the model without using SQL. One of the advantages is compile time checking of your query. You no longer have typos in your SQL strings.
Very cool stuff, all the cool kids on java / .NET use it.
http://en.wikipedia.org/wiki/Object-relational_mapping
It's very nice to have so that you can focus on the business logic of the program rather than making sure you got your SQL code correct :-)
-----Original Message-----
From: Gary Thompson [mailto:gthompson@xxxxxxxxxxx]
Sent: Monday, October 27, 2014 9:03 AM
To: Midrange Systems Technical Discussion
Subject: RE: Can I use DDS to create an SQL table name
Matt,
Not sure what "ORM" means, but maybe this will be helpful
I routinely retrieve a data set using embedded SQL in RPGLE
1) define a DS array - lets assume your DS array is named sp_DS
2) assume you define a cursor to select data for sp_DS named sp_Csr
3) and you define sp_Max and set it to a value that indicates the Size of the DS array
4) You might then have a statement like this to load the DS array
Exec SQL FETCH sp_Csr FOR :sp_Max ROWS INTO :sp_DS;
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Phil McCullough
Sent: Monday, October 27, 2014 7:50 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Can I use DDS to create an SQL table name
There is no ORM on the IBMi, is there?
-----Original Message-----
From: Matt Olson [mailto:Matt.Olson@xxxxxxxx]
Sent: Monday, October 27, 2014 8:37 AM
To: Midrange-L Midrange-l
Subject: RE: Can I use DDS to create an SQL table name
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.
--
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
