× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have you looked at my standard error handler XVERRH? You can find it
included with any my packages at www.think400.dk/downloads.htm or I could
send it to you?.


On Thu, Feb 28, 2013 at 3:33 PM, Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>wrote:

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.