Skip to main content

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

  • July 11, 2017
  • 2 replies
  • 0 views
D

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)

 


 

    2 replies

    Stephen Hjerpe
    • Stephen Hjerpe
    • Participating Frequently
    • July 11, 2017

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

    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.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement