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