Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
redhat37 Absent Member.
Absent Member.
1056 views

ACUSQL how do I call a stored procedure and pass a table type as one of the Parameters.

I have a specification to call a stored procedure which accepts a Table type as one of the parameters.

Is it possible to create this type of scenario within AcuCobol as a data structure that can be passed to the parameter

My current code looks like this and i get an “Invalid SQL data type”,  when trying to call the procedure as i am using a temp table created on the database to pass to the procedure but this is not working.

 

MOVE "PAYDAY_TEMP" TO chvJournalName.
MOVE "01" TO chvVendorCode.

EXEC SQL declare spcursor10 cursor for
:ret-code = exec P_F_G_UPD_LEDGER_JOURNAL (
:chvJournalName,
:chvVendorCode
)
END-EXEC.

if SQLCODE < 0
perform error-exit
exit paragraph.


EXEC SQL
open spcursor10
END-EXEC.

if SQLCODE < 0
perform error-exit
exit paragraph.

move 0 to num-rows-read.

perform until SQLCODE not = 0
EXEC SQL
FETCH spcursor10 into
:chvReturnStatus,
:intBatchNumber,
:intRecordsReceived,
:intRecordsProcessed,
:intRecordsFailed
END-EXEC
if SQLCODE < 0
perform error-exit
exit perform
else
if sqlcode = 0
add 1 to num-rows-read
move intRecordsReceived to ws-intRecordsReceived
move intRecordsProcessed to ws-intRecordsProcessed
move intRecordsFailed to ws-intRecordsFailed
move spaces to ws-line
string
chvReturnStatus delimited by " "
" " delimited by size
intBatchNumber delimited by " "
" " delimited by size
ws-intRecordsReceived delimited by " "
" " delimited by size
ws-intRecordsProcessed delimited by " "
" " delimited by size
ws-intRecordsFailed delimited by " "
" " delimited by size
into ws-line
end-string
MODIFY FORM1-LB-1, ITEM-TO-ADD IS WS-LINE
end-if
end-if
end-perform.

 

The specification reads as follows for the stored procedure.

5.2.2 P_F_G_UPD_LEDGER_JOURNAL


This artefact exposes a table valued parameter for the 3rd Party Vendor to insert GL Journals into the staging table
in the SOLAR Integration database:

5.2.2.1 Input Parameter(s)

5.2.2.1.1 Payroll and Asset GL Integration Required Attributes

The following parameter(s) are required as input parameters to fulfil the purpose of the artefact:


Ledger Journal Table Type - ttLedgerJournal as Table

Vendor Code as VarChar(2)

The procedure will return the following values.

1 UNIQUE_IDENTIFIER BIGINT
2 FINANCIAL_PERIOD VARCHAR (6)
3 GL_TRANS_TYPE VARCHAR (2)
4 VENDOR_CODE` VARCHAR (2)
5 JOURNAL_REFERENCE VARCHAR (14)
6 JOURNAL_DESCRIPTION VARCHAR (60)
7 TRANSACTION_DATE INT
8 SCOA_FUNCTION_GUID VARCHAR (40)
9 SCOA_FUND_GUID VARCHAR (40)
10 SCOA_ITEM_GUID VARCHAR (40)11 SCOA_PROJECT_GUID VARCHAR (40)
12 SCOA_COST_GUID VARCHAR (40)
13 SCOA_REGION_GUID VARCHAR (40)
14 ENTITY_COST_CENTRE VARCHAR (10)
15 ENTITY_SUB_ITEM VARCHAR (10)
16 ENTITY_PROJECT VARCHAR (10)
17 DEBIT_AMOUNT
NUMERIC (25,
2)
18 CREDIT_AMOUNT
NUMERIC (25,
2)

 


 

0 Likes
2 Replies
Micro Focus Expert
Micro Focus Expert

RE: ACUSQL how do I call a stored procedure and pass a table type as one of the Parameters.

Can you provide the COBOL data types you've defined?
VARCHAR(n)* To be treated as a VARCHAR, the elementary items in a group item consisting of only a numeric item and a character item need to be level 49 as in the following:
01 name.
49 name-length PIC S9(4) COMP-5.
49 name-name PIC X(n).
0 Likes
redhat37 Absent Member.
Absent Member.

RE: ACUSQL how do I call a stored procedure and pass a table type as one of the Parameters.

Hi, Thanks for the tip.

I have tried the following and get a SQL invalid data type error when calling the stored procedure.

I create the table in normal working storage as follows because i cannot define it in the EXEC SQL BEGIN DECLARE SECTION END-EXEC. section of my code it returns a error compiling.

01 WS-JCNT PIC 9(9).
01 C_JOURNAL.
02 C_JOURNALD OCCURS 1 TO 10000 DEPENDING ON WS-JCNT.
49 C_UNIQUE_IDENTIFIER PIC 9(18).
49 C_FINANCIAL_PERIOD PIC X(6).
49 C_TRANSACTION_TYPE PIC X(2).
49 C_VENDOR_CODE PIC X(2).
49 C_JOURNAL_REFERENCE PIC X(14).
49 C_JOURNAL_DESCRIPTION_1 PIC X(60).
49 C_JOURNAL_DESCRIPTION_2 PIC X(60).
49 C_JOURNAL_DESCRIPTION_3 PIC X(60).
49 C_TRANSACTION_DATE PIC 9(8).
49 C_SCOA_FUNCTION_GUID PIC X(40).
49 C_SCOA_FUND_GUID PIC X(40).
49 C_SCOA_ITEM_GUID PIC X(40).
49 C_SCOA_PROJECT_GUID PIC X(40).
49 C_SCOA_COST_GUID PIC X(40).
49 C_SCOA_REGION_GUID PIC X(40).
49 C_ENTITY_COST_CENTRE_CODE PIC X(10).
49 C_ENTITY_SUB_ITEM_CODE PIC X(10).
49 C_ENTITY_PROJECT_CODE PIC X(10).
49 C_FLEET_UNIT_CODE PIC X(6).
49 C_FLEET_EXPENDITURE_INCOME_CODE PIC X(2).
49 C_FLEET_READING PIC S9(9)V99.
49 C_JOB_NUMBER PIC X(7).
49 C_JOB_ACTIVITY_CODE PIC X(2).
49 C_JOB_CATEGORY_CODE PIC X(2).
49 C_QUANTITY PIC S9(9)V99.
49 C_DEBIT_AMOUNT PIC S9(9)V99.
49 C_CREDIT_AMOUNT PIC S9(9)V99.


In the EXEC SQL BEGIN DECLARE SECTION END-EXEC I define it as follows

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 dsn-name pic x(120).
01 userid pic x(30).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(30).


01 get-function.
05 ttLedgerJournal PIC X(30000).
05 chvVendorCode PIC X(2).


01 num-rows pic s9(5) comp-5.
01 num-rows-read pic s9(5) comp-5.
01 ret-code pic s9(4) comp-5.


01 JOURNAL_RETURN.
05 chvReturnStatus pic x(50).
05 intBatchNumber pic 9(18).
05 intRecordsReceived pic s9(5) comp-5.
05 intRecordsProcessed pic s9(5) comp-5.
05 intRecordsFailed pic s9(5) comp-5.



EXEC SQL END DECLARE SECTION END-EXEC.

My Code looks like this : and this is where the SQL invalid data type occurs after the the open spcursor10, I have tried using C_JOURNAL end the EXEC_SQL declare section but it does not compile as the C_JOURNAL is in normal working storage.

TEST-SUBMIT-JOURNAL.
MOVE C_JOURNAL TO ttLedgerJournal.
MOVE "01" TO chvVendorCode.

EXEC SQL declare spcursor10 cursor for
:ret-code = exec P_F_G_UPD_LEDGER_JOURNAL (
:ttLedgerJournal,
:chvVendorCode
)
END-EXEC.

if SQLCODE < 0
perform error-exit
exit paragraph.


EXEC SQL
open spcursor10
END-EXEC.

if SQLCODE < 0
perform error-exit
exit paragraph.

move 0 to num-rows-read.

perform until SQLCODE not = 0
EXEC SQL
FETCH spcursor10 into
:chvReturnStatus,
:intBatchNumber,
:intRecordsReceived,
:intRecordsProcessed,
:intRecordsFailed
END-EXEC
if SQLCODE < 0
perform error-exit
exit perform
else
if sqlcode = 0
add 1 to num-rows-read
move intRecordsReceived to ws-intRecordsReceived
move intRecordsProcessed to ws-intRecordsProcessed
move intRecordsFailed to ws-intRecordsFailed
move spaces to ws-line
string
chvReturnStatus delimited by " "
" " delimited by size
intBatchNumber delimited by " "
" " delimited by size
ws-intRecordsReceived delimited by " "
" " delimited by size
ws-intRecordsProcessed delimited by " "
" " delimited by size
ws-intRecordsFailed delimited by " "
" " delimited by size
into ws-line
end-string
MODIFY FORM1-LB-1, ITEM-TO-ADD IS WS-LINE
end-if
end-if
end-perform.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.