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



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.