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