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