× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2004

RE: SQL overrides, was Can SQL recognize a nested data structure?

Rob,

I did a simple test and here are my codes.  Tempfile is the file that
contains data.  I pass this as a parm to clle. MyFile is the name
overridden to.  I have MyFile with different format that tempfile. At
seton lr, I have iimds blank.  The file does not get overridden. What am
I doing wrong?

The 2nd question was if I have the extname in the sqlrpgle a static,
then this does not serve my purpose.  I want the same file name passed
to be the data structure for iimds (and not hard code it to
extname(tempfile).  Is this possible?

Thanks,
Sudha

CL:
        /* file - name of file passed in  */
        pgm parm(&file)
        dcl &FILE *char len(10)                              
        /* over ride file name with MyFile */
        ovrdbf    file(MyFile) tofile(&FILE) ovrscope (*JOB)
        /* call sql module to see if the file name is passed for
override */
        call      testqual                            
        /* done                            */ 
        endpgm                                               
        
sqlrpgle:
         * external data structure
        diimds          e ds                  extname(tempfile)
occurs(10) 
        d  rrn                          10s 0

         * declare cursor from overridden file
        c/exec sql declare s1 scroll cursor for

        c+ select a.*,rrn(a) from MyFile a

        c/end-exec                            
         * open cursor                             
        c/exec sql open s1

        c/end-exec    
         * fetch 10 rows from cursor into datastructure
        c/exec sql fetch from s1 for 10 rows into :iimds

        c/end-exec

         * close cursor
        c/exec sql close s1

        c/end-exec                                     
         * view iimds in debug mode.                    
        c                   seton
lr

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Thursday, August 19, 2004 11:34 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL overrides, was Can SQL recognize a nested data
structure?

Interesting.  I've done overrides before for SQL (so I can access
specific 
members) and that worked.  Perhaps it's the SCOPE of your override
that's 
blowing you grief?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<SRamanujan@xxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/19/2004 11:08 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Can SQL recognize a nested data structure?






Thanks Rob. Very interesting. 

My question about CL is we have a combination of old(rpg36/rpg) and new
(rpg4) modules and keep calling them from each other.  So the cl
override works for the job and we are fine.  But it does not work
usually for sql and we need to do a prepare like you did. Which is
usually more work. 

I think, 4a doesn't work (I tried) 4b does. And I am in v5r1 but we have
to compile down to accommodate our clients. Perhaps it will work in
v5r2.

Thanks again,
Sudha

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Thursday, August 19, 2004 10:55 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Can SQL recognize a nested data structure?

1)  As far as what qualified does - RTFM.
But basically it simply does the following.  If your file, IIM, has a 
field, IPROD, you will now refer to it as the datastructure name dot
field 
name, or, iim.iprod.  This allows you to do things like:
     D new           e Ds                  extname(iim) qualified
     D old           e Ds                  extname(iim) qualified
      /free
       new.iprod = old.iprod;

2)  Will an override of the database work on the extname?
Are you asking if you write your own CL program to compile the RPGLE 
program, and you do an override of the IIM prior to the compile, will
the 
field names in the NEW datastructure and the field names in the OLD 
datastructure, now have the field names in the overridden file instead?
I 
would assume so.  But there's a common joke in America about assume
being 
a compound word.  I can't go into any more detail because I'll get
flooded 
from email programs with 'objectionable' content messages.

3)  A common way to do overrides of databases without using CL, in 
tradition I/O is to use something like
     Fmyfile    if   e             disk    extfile(MyVar) usropn 
      /free 
       MyVar='QTEMP/TESTFILE'; 
       Open(e) Myfile; 

4)  There are two ways to specify a file name in your SQL like you are 
trying to do.  (Hopefully they'll both work.)
4a)  Method 1
       MyString='Select * from ?' 
      /end-free 
     C/EXEC SQL 
     C+ Prepare S1 from :MyString 
     C/END-EXEC 
     C/EXEC SQL 
     C+ Declare C1 cursor for S1
     C/END-EXEC 
      // MyFile will be placed in the question mark position in S1 
     C/EXEC SQL 
     C+ Open C1 Using :MyFile 
     C/END-EXEC 
4b)  Method 2
       MyString='Select * from ' + :MyFile 
      /end-free 
     C/EXEC SQL 
     C+ Prepare S1 from :MyString 
     C/END-EXEC 
     C/EXEC SQL 
     C+ Declare C1 cursor for S1
     C/END-EXEC 
      // MyFile will be placed in the question mark position in S1 
     C/EXEC SQL 
     C+ Open C1
     C/END-EXEC 

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<SRamanujan@xxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/19/2004 10:04 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Can SQL recognize a nested data structure?






A couple of questions, a little off topic. But, what does the
'qualified' do in the d-spec?  will an override of a database work on
the extname. i.e. can extname(iim) in the example, take up the
overridden database name in the cl?  And will that affect the being file
sql-ed also?  I haven't been able to pass the file name as parameter to
sql 'select before i.e. select * from :filename'. Can this be done now?

Thanks.

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Wednesday, August 18, 2004 10:01 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Can SQL recognize a nested data structure?

This will work in V5R3  (tested it here):


     D iimds         e Ds                  extname(iim) dim(10)
     D                                     qualified
     D  rrn                          10s 0



Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.