RE: Issue with jdbc_next

Thanks for the reply Scott,
Yes, I did mean JDBC_nextRow().
I did solve it this morning, and it was really obvious. I had my
JDBC_SetString statements reversed(it should have been last, first not
first, last). I can't tell you how many times I looked at this,
debugged it and still didn't see it. Jeez.

The thing that really confused me was, that I mistakenly thought that
when I ran this
rs = JDBC_ExecPrepQry(stmt);
And rs had a value, that meant a successful execution(meaning I found
records meeting my sql criteria).

Thanks
Mike

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Tuesday, March 08, 2011 6:27 PM
To: RPG programming on the IBM i / System i
Subject: Re: Issue with jdbc_next

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.

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




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.