Re: JDBCR4 - Stored Procedure Output Parameters -- RPG400-L

You will find it included with all my projects. If you look at the first
item on the list and look at the source code you will see 6 sources for
XVERRH*. These are the standard error handler. I will send it to you if you
want.

XVERRH - Top level.
XVERRH_B - Bindary
XVERRH_M01 - Throw functions.
XVERRH_M02 - Message Storage functions
XVERRH_M03 - Send messages functions
XVERRH_PR - Prototypes

On Fri, Mar 1, 2013 at 7:48 AM, Anderson, Kurt <KAnderson@xxxxxxxxxxxx>wrote:

That's how we pass errors back through all of our regular procedure calls
(either output parms or return values). What I've found is that it's best
not to make the error parms optional. Unfortunately adding a parm like an
error indicator is easy if you make it optional so you don't break any
existing code. We're also in a small shop, so it's easier to enforce the
use of error indicators. Ultimately a programmer has to be responsible at
some level in regard to recognizing errors. Whether it's a Monitor, or a
parameter, MONMSG, or whatever.

By the way, Alan, I checked the site you linked to in a previous email,
but I couldn't find XVERRH (I searched on XVERRH, ERR, and XVE).

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Thursday, February 28, 2013 5:12 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: JDBCR4 - Stored Procedure Output Parameters

I tried that idea 20 years but it always failed because the programmer
would never check it which is why I went to a standard error handler. It
can be ignored but only by using a monitor.

On Thu, Feb 28, 2013 at 4:07 PM, Anderson, Kurt <KAnderson@xxxxxxxxxxxx

wrote:

That may be preferable.

Here is the solution I came up with (but it requires that the
programmer takes advantage of the new parameter). Of course if I
didn't make the new parm optional, it'd at least get the programmer to

acknowledge the

parameter. The added lines are marked with 29563.

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* JDBC_GetInt(): Get an integer from a called statement
*
* call = (input) Callable statement to get string from
* idx = (input) Parameter index to get
29563 * error = (output) (optional) Indicator for unexpected errors
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P JDBC_GetInt...
P B export
D JDBC_GetInt...
D PI 10I 0
D call like(CallableStatement) const
D idx 10I 0 value
29563D outError n Options( *omit: *nopass )

29563D error S n
D result s 10I 0
/free
jdbc_begin_object_group(10);

monitor;
result = getInt( call: idx);
29563 error = *Off;
on-error;
result = 0;
29563 error = *On;
endmon;

jdbc_end_object_group();

29563 If %parms >= %parmNum( outError ) and %addr( outError ) <>

*null;

29563 outError = error;
29563 EndIf;

return result;
/end-free
P E

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Scott Klement
Sent: Thursday, February 28, 2013 4:34 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: JDBCR4 - Stored Procedure Output Parameters

Hi Kurt,

Originally, I let JDBC_GetInt() crash when there was an error.
However, this caused a memory leak, because the call to
jdbc_begin_object_group() would get called without the corresponding
jdbc_end_object_group() (which wasn't reached because the code crashed).

So I added the MONITOR to catch the error and fix the memory leak, but
of course, this now sets the result to 0, which can be misleading as
you've pointed out.

I guess one solution is to send an *ESCAPE message after the
jdbc_end_object_group if there's an error. This would definitely make
it clear that an error occurred, while still eliminating the memory leak.

What do you think?

On 2/28/2013 3:29 PM, Anderson, Kurt wrote:

Hi Scott,

There was a little learning curve I had to get over, but I'm there now.

I appreciate you saying it can be done, that helped push me forward.
Took me some trial and error, but I got there.

I'm not sure how I feel about the JDBC_GetInt (String, etc)
monitoring

for an error, and if found, using the default value for the data type.
In my trial and error phase, I had an error on JDBC_GetInt (b/c I
didn't register the output parameter), and at the time we were using
that Int value as a success/fail, where success = 0 and fail = -1
(which is what my SQL server guy said they use, and I was fine with).
Well, the unexpected error encountered in JDBC_GetInt forced the value
to be 0, which in this case I was treating as a success value. (We
have since changed to using JDBC_GetBoolean for the success/fail

parameter and reverting back to

1=Success/True and 0=Fail/False.) Before switching to Boolean, I added

in

a 3rd optional "error" parameter, which can be seen in my example
below (in JDBC_GetString).

Because I was registering data types, I went ahead and coded all of
the

JDBC data types in JDBCR4_H using the values from:

http://docs.oracle.com/javase/1.4.2/docs/api/constant-values.html#ja
va
.sql.Types.ARRAY

So, for the archives, here is an example of using a SQL Server
stored

procedure that has output parameters:

D JDBCTYPE_BOOLEAN...
D C 16
D JDBCTYPE_VARCHAR...
D C 12

D executeStmt S 32767a Varying
D message S 150a
D stmt S Like( CallableStatement )

executeStmt = 'exec spBulkInsertCdrMaster ?, ?, ? output, ?

output';

stmt = JDBC_PrepCall( gConnection: executeStmt );

JDBC_SetString( stmt: 1: %trim( epFilePath ) );

// Set the replacement parameter (#2) based on the mode.
If epMode = MODE_APPEND;
JDBC_SetInt( stmt: 2: 0 );
ElseIf epMode = MODE_REPLACE;
JDBC_SetInt( stmt: 2: 1 );
EndIf;

// Set output parameters
JDBC_RegisterOutParameter( stmt: 3: JDBCTYPE_BOOLEAN );
JDBC_RegisterOutParameter( stmt: 4: JDBCTYPE_VARCHAR );

If JDBC_ExecPrepUpd( stmt ) < 0;
// commented out code

// Successful execute
Else;

// Retrieve the output parameters
success = JDBC_GetBoolean( stmt: 3 );
message = JDBC_GetString( stmt: 4: error );
If error;
success = *Off;
EndIf;
EndIf;

JDBC_freePrepStmt( stmt );

-Kurt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Wednesday, February 27, 2013 6:42 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: JDBCR4 - Stored Procedure Output Parameters

Hi Kurt,

CallableStatement is the correct input. You would not want to pass
a

REsultSet, since there may not be a resultset if your output is all
done through parameters!

So I guess I don't understand your objection to using a
CallableStatement. When you call the stored procedure, you're doing
something like

TheCallableStatement = JDBC_PrepCall(conn: 'call the-procedure')

Just pass TheCallableStatement back to JDBC_getString() and friends.
Also, don't forget to register your output parameters. Java is
weird

about that.

-SK

On 2/27/2013 5:10 PM, Anderson, Kurt wrote:

Hi,

I see how processing a result set from a stored procedure works,
but

I'm not clear on how to retrieve output parameters.

In the JDBCR4 presentation, I see this:
"JDBC_getString(), JDBC_getInt(), JDBC_getShort(),
JDBC_getBoolean() Get the values of output parameters passed from the

stored procedure"

Looking at the JDBC_get... procedures, it appears that the first

parameter is the CallableStatement. The CallableStatement is the
ResultSet object when processing a result set. I'm not sure what it
should be when not working with a result set.

This is the stored procedure definition I'm working with:
create procedure [dbo].[spBulkInsertCdrMaster] (@fileName
varchar(200), @replaceCdrMaster int, @result varchar(500) output,
@message varchar(500) output)

We do have the option to change from output parms to a result set,
but

I figured I'd take this moment to learn how to handle the stored
procedure as it is defined.

Thanks,
Kurt Anderson
Sr. Programmer/Analyst
CustomCall Data Systems, a division of Enghouse Systems Ltd.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx<mailto: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<mailto: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 (AS/400 and 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 IBM i (AS/400 and 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 IBM i (AS/400 and 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 IBM i (AS/400 and 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.