• Subject: Re: JDBCR4 where no result set returned
  • From: Buck Calabro <kc2hiz@xxxxxxxxx>
  • Date: Fri, 12 Oct 2018 12:14:13 -0400
  • Autocrypt: addr=kc2hiz@xxxxxxxxx; keydata= xsDiBEcbaT4RBADqmM9OgXil65pjrxclJpxuAF6vraI3kkmJbEHb5ElL7EquHE3QDuFqFgIB 4NZLHDbVAh0AD5exAX+r+xg//UvtBc2k34HROnCpWTMnIOaSVhhVjpYEbZGLz6wfrRpu4Qyn 45iaKT4F0qcHo+0LrGQPef3xrFkUhxURgzY5zgo6+wCg/XjYJ155witPWB2CbNf6RAm9QT0D /jSp6YhvE3xPE12aBuRYM678JTbaQfuYv4HUfug1Wz/0zH5btfEihWVN4wbKaoQ/H/29v2TP /Lyh8XTVd3Z0rz4iaSD5fGicn81WPANBeIepLB8vpfEik6UhHpN1DJkz6Ryw2mgx8p53LhHV Ck4Jt0HP2TAl3f7QTXGFOiFzJwEqBACsHk/gFpKAHdv7n4vJoHqp0RNgOOyhnTThlulPilt6 tAaSe10FOrrugBuLMn7wXBANQ1ApmIb5yNjhYqPREj65OVv2MUbw8H2HnQs//Z6aodyR/kzU 2q2G9A/YFI1LL0m/gvaVbEj/wE0ybBgFkrcoEFeStkqS5HzLEFGUDFXhD80fQnVjayBDYWxh YnJvIDxrYzJoaXpAZ21haWwuY29tPsKFBBMRAgBFAhsDBgsJCAcDAgQVAggDBBYCAwECHgEC F4AFAkcbdMokGGh0dHA6Ly9rZXlzZXJ2ZXIudmVyaWRpcy5jb206MTEzNzEvAAoJEN7KcclH umuRfngAoNXU6AXqyTR8FRuoXKBGS4k7bPUEAJ912WKSkjpCt0axjrq6j22e5XgWzc7BTQRH G2k+EAgAnLXJ9hOqedgsIYM3LuomBBNN+7WTFSVaJ3Rqz8XVZtJvLL0bIRAvpVK9L9rYXlCR cPAm0YNK6H2DR7sQxWlxEH4mWB+jTCTALpcVq+Kpfbw5qDdn+9DVMS7tBOchtTlPSGgdKgn7 sTObra8cHtX/ddTB6OLzHeTXr4PZbUwVeQdIStdwMmozKBQvgjXWKi1GiuYbwYkCM/zJEUCs J36BIE4li9xohJ5O4iKC20YVckMJfZLbn1a2gVgn6Re8C5ezNewT0qM8ZDCUNENWAxsU/c9J UCFQ2QcMU+25b84D5yPxnEKna5U9Fz2JjRjWy5ZKZx2+WhZj0r2Tw6/kGb28AwADBgf/WBsn JSMHxyVfg+LKLHpdANwa9jdrKOt2WjJbWOiJ9l7SmqD0oi3c22FFxRXKsFfjCikLk9wbLZKH SqqnOePvMMHqNcqQTSv7+ARjxnBH4g6dhqg+zmebKpt8zV2awQzYSSm4YY6IqzkWmPNAN7BU zUtSAfL4UU2PljTnT9m443aVCTXMne5l90HQv/gdJ121owg5KuGE6LodTpoR4hn9nbdKWtfY pDNoykvR+GN5y335yF2Zp/j6QgdxWezjou5Y3/6PUZLEsJagWe9hAcKb1eiO2bmg+1bFYu0T g5Mvb27nqfFeHHFysC7a7sXtxp/pqNLNDcK6j/7Th6vF7/n98cJJBBgRAgAJBQJHG2k+AhsM AAoJEN7KcclHumuR9SgAnRuJWHon4GP58xbqCiFR/jSUfvRgAJ47KZ1UNoXgdftoePnbrZu6 W+poEw==
  • List-archive: <https://archive.midrange.com/midrange-l/>
  • List-help: <mailto:midrange-l-request@midrange.com?subject=help>
  • List-id: Midrange Systems Technical Discussion <midrange-l.midrange.com>
  • List-post: <mailto:midrange-l@midrange.com>
  • List-subscribe: <https://lists.midrange.com/mailman/listinfo/midrange-l>, <mailto:midrange-l-request@midrange.com?subject=subscribe>
  • List-unsubscribe: <https://lists.midrange.com/mailman/options/midrange-l>, <mailto:midrange-l-request@midrange.com?subject=unsubscribe>

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);


This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2019 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].