Re: JDBC CallableStatement, plain Program Call, and output parameters...

Joe Pluta skrev den 29-08-2008 14:16:

Thorbjørn Ravn Andersen wrote:

Coy Krill skrev den 26-08-2008 17:59:

Our software vendor has several RPGLE programs that they use like an API. Here is the code I use to wrap one of their more useful ones. It basically make an SQL reference to the external program and tells SQL how the parameters work etc. This should get you started.

Thanks!

I am getting near :) I can invoke my program but it crashes. I have destilled the problem to being that I need integer values in a PIC 9(5) field (which apparently is a ZONED(5,0) when I answer D to the inquery message in QSYSOPR), and that DECIMAL seems not to initialize it properly and CHAR(5) wasn't what I thought :) Chapter 6 in "Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries" did not make it clear for me, so perhaps one on the list can nudge me in the right direction?

Ah, the joys of numeric data. You have two options:

Thanks for responding :) The amount of experience of this list is truly
astonishing!

In the SQL, specify NUMERIC rather than DECIMAL. This will make the parameters zoned decimal.

This didn't work - bleh. Data dump is:

===
MCH1202 exception in program ADD2NUM in TRATEST at MI instruction
number 0035 COBOL statement number 14.
FORMATTED DATA DUMP FOR PROGRAM ADD2NUM.TRATEST 15:48:17
29.08.08
NAME OFFSET ATTRIBUTES
VALUE

A 009120
ZONED(5,0)

**INVALID DATA
'4EF0F0F0F1'X

B 009126
ZONED(5,0)

**INVALID DATA
'4EF0F0F0F2'X

C 00912C
ZONED(5,0)

**INVALID DATA
'4EF0F0F0F0'X

D 009132
ZONED(5,0)

**INVALID DATA
'4EF0F0F0F0'X

STATIC STORAGE FOR PROGRAM ADD2NUM.TRATEST BEGINS AT OFFSET
072240 IN THE PROGRAM STATIC STORAGE AREA (PSSA)
AUTOMATIC STORAGE FOR PROGRAM ADD2NUM.TRATEST BEGINS AT OFFSET
000080 IN THE PROGRAM AUTOMATIC STORAGE AREA (PASA)
====

Again A is 10 and B is 25, but it doesn't look like it in the above.


(Update: Found out that it looks basically right but there is a plus sign in front, which appararently is not right. You mentioned signed numbers, so perhaps you would care to elaborate? :)

In the COBOL, specify COMP-3 on your variables. This will make the values packed.

This worked! Joy! Happiness! Astonishment! (Just for the record, I
can do 10000 calls to the prepared statement setting new parameters and retreiving the result each time in approximately 8.5 seconds from my PC giving a round trip value of about 0.85 ms)

It gets a little trickier with signed data. I would lean towards NUMERIC to avoid any issues.

Since the Cobol program to be called is essentially cast in stone (and
most likely written with chisels originally), it is not an option to
change it, and my Cobol literate colleague who have helped me on this,
also happens to be head of the Quality Assurance SWAT team and does not
like the idea of a Cobol wrapper just for this purpose. I know for a
fact the values passed will be between 1 and 32000.

Any suggestions? (I'm SOOOO close :)