Re: Issue with jdbc_next

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Mike,

I assume you mean JDBC_nextRow().  (If not, I'll need you to explain 
what "jdbc_next" is.)

Here's the code for JDBC_nextRow(), in it's entirety:

D nextRec...
D                 PR             1N   ExtProc(*JAVA:
D                                     'java.sql.ResultSet':
D                                     'next')

P JDBC_NextRow    B                   export
D JDBC_NextRow    PI             1N
D   rs                                like(ResultSet)
 /free
     monitor;
        return nextRec(rs);
     on-error;
        return *OFF;
     endmon;
 /end-free
P                 E

Seems to me there's two reasons why jdbc_nextRow() would return *OFF 
(which seems to be what you're wondering about).

1) the next() method of the java.sql.ResultSet() class is returning 
FALSE, indicating that there aren't any rows to read.

2) the call to the next() method is causing an error to be sent to 
JDBCR4, in which case the MONITOR block takes control, and returns *OFF. 
 If that happens, there should be a message in your job log that 
explains what the error was.

So...  it shouldn't be hard to tell which one is the case... check your 
job log.  If the next() method is returning FALSE, indicating that 
there's no records, then could it be your SQL statement returns no 
records?  If the next() method is sending you an exception, look at the 
details of the message(s) to find out what the exception is.

Good luck




On 3/8/2011 3:02 PM, Smith, Mike wrote:
> I'm having a small issue with using jdbc_next.
> I have a program that executes 1 of 3 different sql statements.
> I think all 3 are executing, but 1 of the 3 does not retrieve anything
> using jdbc_next
>
> The pertinent parts of the code are shown below.
> Only when a single user is selected does it fail.  If I check the value
> of RS after the JDBC_EXECPREPQRY(STMT)  it has a value something like
> 870760860.
> However, it immediately exits the dow loop below it.  Maybe I'm
> mistaken, but I thought that by RS having a value, it meant that it
> executed the sql successfully
>
> Does anyone see anything obvious with this?
>
> Thanks
> Mike
>
>           // if supervisor selected
>           if super>  *blanks ;
>           stmt = JDBC_PrepStmt(connms: 'select * from +
>                 udf  join names on udf.nameid = names.id +
>                 where udfnum = 2 and udftext = ?') ;
>
>           JDBC_SetString(stmt:1: super );
>
>           rs = JDBC_ExecPrepQry(stmt);
>
>           else ;
>
>           // if single user selected
>           if last>  *blanks ;
>
>           stmt = JDBC_PrepStmt(connms: 'select * from +
>                 udf  join names on udf.nameid = names.id +
>                 where udfnum = 2 and lname = ? and fname  = ?') ;
>
>           JDBC_SetString(stmt:1: first );
>           JDBC_SetString(stmt:2: last );
>
>           rs = JDBC_ExecPrepQry(stmt);
>
>           else ;
>
>           //Select all Users
>           stmt = JDBC_PrepStmt(connms: 'select * from +
>                 udf  join names on udf.nameid = names.id +
>                 where udfnum = 2 ') ;
>           rs = JDBC_ExecPrepQry(stmt);
>
>           endIf ;
>
>           endIf ;
>            x = 0;
>           dow (JDBC_nextRow(rs)<>  *OFF);
>
>             x=x+1 ;
>             A_LNAME(x) = (jdbc_getCol(rs: 8));
>             A_FNAME(x) = (jdbc_getCol(rs: 9));
>           EndDo ;
>
>
>
> NOTICE:  This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.