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

This going to be kind of long.

What I do when working with cursors is to use a data structure but I almost
always use a user space mapped to the a array of records. You will note
that since I am mapping into a user space I just make the array size to be
32766 which is the maximum for fetch in SQL. This works great because I am
not using any storage in the program and it runs faster than record I/O

d RecordsIn...
d ds Qualified
d Dim(32766)
d Based(ptrRecordsIn)
d Field1...
d 9p 0
d Field2...
d 50a Varying
d etc.

. If I am doing say a single page load subfile this works well, but what
happens when I want to read a larger data set or I don't know how many I
can have to read and it might exceed 32766?

The other issue you run into is that User Space can only hold a maximum of
16MB of storage minus some overhead. If you have a large record what might
fit may be less then 32766.

All of this gets very nasty real fast.

What I want to do is just read the records in without worry about space or
what will fit so I created a Service Program that handles all of this and
takes almost no code to handle it.

Think of it as unlimited virtual storage. Underneath are user spaces. The
Service Program just manages creating new ones as needed so you are only
restricted by disk.

Here is a simple example.

First I need two data structure based on the same template.

d TD_RecordIn...
d ds
Qualified
d
Template
d Field1...
etc.

d RecordsIn...
d ds LikeDs(TD_RecordIn)
d Dim(32766)
d Based(ptrRecordsIn)

d OneRecord...
d ds LikeDs(TD_RecordIn)

A few variables.

d RecordsToRead...
d s Like(StdInt)
d Handle...
d s Like(StdInt)
d x...
d s Like(StdInt)
d Loaded...
d s Like(StdInt)

And the code.

/Free

Exec Sql Declare C1 Cursor For
Select Field1,
Field2,
etc
From File1;

Exec Sql Open C1;
If SqlState <> cSqlStateOk;
// Error Handling :
EndIf;

Handle = XVSTGM_NewEnvironment(%Size(TD_RecordIn));

DoU 0 = 1;
XVSTGM_GetRecordsToRead(Handle:
%Elem(RecordsIn):
RecordsToRead:
ptrRecordsIn);
Exec Sql Fetch Next
From C1
For :RecordsToRead Rows
Into :RecordsIn;
Select;
When SqlState = cSqlStateNoRow;
Exec Sql Close C1;
Leave;
When SqlState = cSqlStateOk;
XVSTGM_UpdateRecordsRead(Handle:
SqlErrD(3));
Other;
// Error Handling
EndSl;
EndDo;

Now we have all the records loaded and ready to process. Can be any number
of records. I tested with 2.5 million records.

Now I can just read them back to do whatever processing I need.

Loaded = XVSTGM_GetRecordCount(Handle);
For x = 1 To Loaded;
XVSTGM_GetRecord(Handle:
x:
OneRecord);
// Process Record
EndFor;

XVSTGM_ReleaseEnvironment(Handle);

Trying to decide if I put this out into open source. Depends what kind of
interest I find.

On Mon, Oct 27, 2014 at 10:15 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Mike,

You don't need to predict the # of records. You simply decide how many you
want to process at a time...

In this instance, it's 100. So you'd read a batch of 100 as many times as
needed till there were no more.

Charles

On Mon, Oct 27, 2014 at 11:21 AM, Mike Cunningham <mike.cunningham@xxxxxxx

wrote:

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.

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