× 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. October 2010

Re: Using Scott's JDBCR4 doing select inside a read loop

USING is great...But broken at v5r4...

Select a.*
From a
Join b
Using (fld1, fld2, fld3)

at v5r4 will return
a.fld1, a.fld2, a.fld3, a.fld4, a.fld5, <...>

at 6.1 and higher
a.fld4, a.fld5, <...>

Another example:
Select *
From a
Join b
Using (fld1, fld2, fld3)

at v5r4 will return
a.fld1, a.fld2, a.fld3, a.fld4, a.fld5, <...>, b.fld1, b.fld2, b.fld3,
b.fld4, b.fld5, <...>

at 6.1 and higher will return
fld1, fld2, fld3, a.fld4, a.fld5, <...>, b.fld4, b.fld5, <...>

Note that the fields specified in the USING clause only appear once in
the result set and are NOT qualified.

Why do I say it's broken at v5r4 and fixed at 6.1 and higher? The follwing:
Select *
From a
Join b Using (fld1, fld2, fld3)
Join c Using (fld1, fld2, fld3)

Fails at v5r4 but works at 6.1 and higher.

Moral of the story, don't use USING in production code till you get to 6.1

HTH,
Charles

On Wed, Oct 13, 2010 at 10:03 AM, Schutte, Michael D
<Michael_Schutte@xxxxxxxxxxxx> wrote:
FYI, If all the columns that are joined have the same name, you could use the USING clause instead of the ON clause.

i.e.

select a.*
from a
join b
 on a.fld1 = b.fld1
 and a.fld2  = b.fld2
 and a.fld3 = b.fld3
could be written like so.

Select a.*
 From a
 Join b
 Using (fld1, fld2, fld3)


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Cunningham
Sent: Wednesday, October 13, 2010 9:01 AM
To: RPG programming on the IBM i / System i
Subject: RE: Using Scott's JDBCR4 doing select inside a read loop

IN my case it was column names used on a join. They needed to be qualified as the vendor used the same column name in two tables I was joining.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Wednesday, October 13, 2010 12:31 AM
To: RPG programming on the IBM i / System i
Subject: Re: Using Scott's JDBCR4 doing select inside a read loop

Not sure what is meant by "invalid names".  I assume you mean table
names?   Or maybe column names?   I would expect that either of these
would be tested on the JDBC_ExecPrepQry.

However, something like invalid SQL syntax (DELECT instead of SELECT, for example) would be caught on the JDBC_PrepStmt().  But...  I'm just guessing... it's really the JDBC driver itself that determines stuff like that.


On 10/12/2010 7:38 AM, Mike Cunningham wrote:
Thanks for the suggestion Scott. I found my problem and it was not with your tool. The app was actually failing on the inner SELECT clause (I had a field name spelled wrong). There was another error higher on the stack.

Where does the SELECT actually get tested for valid names? On the jdbc_prepStmt or on the jdbc_ExecPrepQry?  I have the   if (prepstm = *NULL);    test and it did not trap it there but I have nothing after the jdbc_ExecPrepQry() to test for a failure. I would assume "rs" would be *NULL if that failed.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Monday, October 11, 2010 11:40 PM
To: RPG programming on the IBM i / System i
Subject: Re: Using Scott's JDBCR4 doing select inside a read loop

Hi Mike,

Please either remove the inner JDBC_FreeResult(), or remove the 'stmt_close' from the internals of the JDBC_FreeResult() routine, and see if that helps.



On 10/11/2010 12:46 PM, Mike Cunningham wrote:
What would be the proper sequence of instructions using JDBCR4 to do a second SELECT while looking through the result set of the first SELECT?

My code was like this and it run OK

SQL_Statement = 'SELECT.........";
prepstm = JDBC_prepStmt(conn : SQL_Statement); Select_PO_NO =
'P0000041';
jdbc_setString(prepstm: 1: Select_PO_NO); rs =
jdbc_ExecPrepQry(prepstm); dow (jdbc_nextRow(rs));
    HPOs.Purch00001 = jdbc_getCol(rs: 1);
   ...
Enddo;
JDBC_FreeResult(rs);
JDBC_FreePrepStmt(prepstm);
JDBC_Close(conn);

Then I added the "Exsr  getVendorAddress;" and started getting a dump
on JDBC_FreeResult(rs);

SQL_Statement = 'SELECT.........";
prepstm = JDBC_prepStmt(conn : SQL_Statement); Select_PO_NO =
'P0000041';
jdbc_setString(prepstm: 1: Select_PO_NO); rs =
jdbc_ExecPrepQry(prepstm); dow (jdbc_nextRow(rs));
    HPOs.Purch00001 = jdbc_getCol(rs: 1); Exsr  getVendorAddress;
   ...
Enddo;
JDBC_FreeResult(rs);
JDBC_FreePrepStmt(prepstm);
JDBC_Close(conn);

-------------------------------------------------
GetVendorAddress  BEGSR
aSQL_Statement = 'SELECT.........";
aprepstm = JDBC_prepStmt(conn : aSQL_Statement); ars =
jdbc_ExecPrepQry(aprepstm); dow (jdbc_nextRow(rs));
    HAddress_ls.Addre00001 = jdbc_getCol(ars: 2);
    ...
Enddo;
JDBC_FreeResult(ars);
JDBC_FreePrepStmt(aprepstm);
                                           ENDSR


Error is a MCH3601   Pointer not set for location referenced.

FATAL ERROR in native method: Received NULL object reference
   java.lang.Throwable

If I remove the EXSR it runs clean. Add it back and it dumps.  I have tried removing the JDBC_FreeResult(ars);  and JDBC_FreePrepStmt(aprepstm); from the end of the GetVendorAddress routine and it still dumps.   If I remove the JDBC_FreeResult(rs);    JDBC_FreePrepStmt(prepstm); from the very end it dumps on the      JDBC_Close(conn);

--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 ...
Replies:
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.