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.