On 10/11/2018 6:16 PM, Don Brown wrote:
Thanks Buck you solved the error but unfortunately not the problem. I also
appreciate the explanation of how you got there - I have tucked that one
away for future reference.
Perhaps you may have a suggestion on if this is possible or not.
The MS SQL SP does as per the snipped sample below.
<begin_snip>
ALTER PROCEDURE [dbo].[details]
@BookingID int,
@OperatorID int
AS
BEGIN
if @BookingLookup is null
begin
print 'Booking does not exist'
return 1
end
Is there a way to retrieve the returned value of 1 or 2 ?
It is not a result set so getResultSet returns noting, I have now tried
getUpdateCount and it returns -1. I do not know what else to try ???
I don't speak Microsoft (not a hater, just lack of exposure).
If my Google-fu is working today, it looks as though MSSQL SPs can
return an INT, and that this is automatic/implicit. This would mean that
we want to access this returned value, which is not (as you've
discovered) a result set, nor is it an OUT parameter.
The examples I've seen have this form:
CallableStatement proc =
connection.prepareCall("{ ? = call dbo.mySproc() }");
proc.registerOutParameter(1, Types.INTEGER);
proc.execute();
int returnValue = proc.getInt(1);
My advice (as always) is to take this Java sample and run it in Java to
make sure it works as expected. If it doesn't, there's no point trying
to turn it into RPG :-/ Once the Java code is proved to work, we need to
find the equivalent HSSFR4 sub-procedures to emulate it.
Starting with the 'prepareCall', I open the source member for JDBCR4 and
search for it. Specifically, I'm looking for the 'prepareCall' that is
the 3rd parameter on an EXTPROC. Scott has a very nice way of formatting
his prototypes, so this is pretty easy to see over on the right side of
the code. The sub-procedure name that you want to use in your RPG code
is on the PR line, at the top, on the left. It's kind of important to
remember that Scott defined 'low level' prototypes that are used
internally by the EXPORTed prototypes that we want. So 'PrepareCall' is
the low level prototype, and JDBC_PrepCall() is the one we want in our
RPG code. You're probably already doing this but note that the web
example puts '? = call blahblah' allowing for a 'return value'.
The next thing the web sample does is register the output parameter.
Again, searching the source for 'registerOutParameter' locates the
method - it's part of the 'CallableStatement' class - and Scott called
out sub-procedure 'registerOutParameter'. What to do about the web
example's 'Types.INTEGER' though? A peep at the prototype doesn't
immediately help. Looking at the Javadoc:
https://docs.oracle.com/javase/8/docs/api/java/sql/CallableStatement.html#registerOutParameter-int-int-
I see that the 'sqlType' is an int, but where are these defined? The
Javadoc refers me to java.sql.Types, so let me go look there:
https://docs.oracle.com/javase/8/docs/api/java/sql/Types.html Looking
for INTEGER, I then follow the link to Constant values:
https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.INTEGER
and I can see (finally!) that integers are '4'. This seems like it
should model the web example: JDBC_RegisterOutParameter(callable: 1: 4);
[Note: to be very honest, I didn't look very hard to see if
Types.INTEGER was already defined somewhere... I'm sort of in brute
force mode here, sorry.]
The next thing the example does is 'execute()' instead of
'executeCall()'. Let's have a peep in the source to see if we can't find
that. Scott called it JDBC_Execute which should give:
JDBC_Execute(callable);
Finally, retrieve the thing that MSSQL put in the 'return' value. By now
you will be bored searching the source, but looking for 'getInt' locates
the 'CallableStatement; class method that Scott named 'JDBC_GetInt'
which yields: return_val = JDBC_GetInt(callable: 1);
To recap, we have this web example Java code:
CallableStatement proc =
connection.prepareCall("{ ? = call dbo.mySproc() }");
proc.registerOutParameter(1, Types.INTEGER);
proc.execute();
int returnValue = proc.getInt(1);
Which should translate into this RPG code:
proc = JDBC_PrepCall(conn: '? = call myproc');
JDBC_RegisterOutParameter(proc: 1: 4);
JDBC_Execute(proc);
return_val = JDBC_GetInt(proc: 1);
midrange.com
