|
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,I appreciate you saying it can be done, that helped push me forward.
There was a little learning curve I had to get over, but I'm there now.
Took me some trial and error, but I got there.
for an error, and if found, using the default value for the data type.
I'm not sure how I feel about the JDBC_GetInt (String, etc)
monitoring
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).
JDBC data types in JDBCR4_H using the values from:
Because I was registering data types, I went ahead and coded all of
the
http://docs.oracle.com/javase/1.4.2/docs/api/constant-values.html#japrocedure that has output parameters:
va
.sql.Types.ARRAY
So, for the archives, here is an example of using a SQL Server
stored
output';
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, ?
stmt = JDBC_PrepCall( gConnection: executeStmt );REsultSet, since there may not be a resultset if your output is all
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
done through parameters!
about that.
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
I'm not clear on how to retrieve output parameters.
-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
parameter is the CallableStatement. The CallableStatement is the
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
ResultSet object when processing a result set. I'm not sure what it
should be when not working with a result set.
I figured I'd take this moment to learn how to handle the stored
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
procedure as it is defined.
http://archive.midrange.com/rpg400-l.--
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
--
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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
