ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Call stored procedure

Post new topic  Reply to topic Goto page 1, 2  Next
 Call stored procedure « View previous topic :: View next topic » 
Author Message
CAONIMA
PostPosted: Mon Feb 02, 2015 7:57 pm    Post subject: Call stored procedure Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
The procedure in Oracle DB:


TYPE detrec_tbl_t is table of crm_int_detail%ROWTYPE index by pls_integer;

PROCEDURE sugar_etrac_interface(
p_master_io IN OUT crm_int_master%ROWTYPE,
p_det_io IN OUT detrec_tbl_t,
p_response_o IN OUT crm_int_response%ROWTYPE
);
datatype:
1) p_master_io : PL/SQL RECORD
2) p_det_io: etrac.crm.detrec_tel
3)p_response_o PL/SQL RECORD

IIB ESQL Code:

DECLARE p_master_io ROW;
DECLARE p_det_io ROW;
DECLARE p_response_o ROW ;

SET P_master_io = Environment.Variable.master;
SET p_det_io.ROW[1].NUMBER = '1';
SET p_det_io.ROW[2].NUMBER = '2';
SET p_det_io.ROW[1].PROBLEM = 'Y';
SET p_det_io.ROW[1].PROBLEM = 'N';


CALL etrac(p_master_io,p_det_io.ROW[],p_response_o)


CREATE PROCEDURE etrac( )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "etrac.crm_processing.sugar_etrac_interface";

----------------------------------------------------------------------------------
We use the fist two parameters as input, the last one p_response_o as putput?
My Question is how can I define the datatype PL/SQL RECORD?
For the second parameter: detrec_tbl_t , it is a table, how can I define it and call it in ESQL ?

The detrec_tbl_t has many rows (at least two), like a list. Can I just call it from IIB to decalre it as row?
Back to top
View user's profile Send private message
nelson
PostPosted: Tue Feb 03, 2015 6:18 am    Post subject: Reply with quote

Partisan

Joined: 02 Oct 2012
Posts: 313
Hi CAONIMA,

You should read this:

http://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ac17040_.htm

And yes, you declare it as a row.
Back to top
View user's profile Send private message
CAONIMA
PostPosted: Tue Feb 03, 2015 6:25 am    Post subject: Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
Thank you for you reply.

I have already read all the documents about stored procedure, but I still cannot get the result, it always return

"The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure."

So I wonder what is the datatype in stored procedure, how should I define it in IIB using ESQL?

Thank you very much.
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Feb 03, 2015 6:38 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
nelson wrote:
And yes, you declare it as a row.


It says here:

Quote:

    All external procedures have the following restrictions:
    A stored procedure cannot be overloaded on the database side. A stored procedure is considered overloaded if there is more than one procedure of the same name in the same database schema. If the broker detects that a procedure has been overloaded, it raises an exception.
    Parameters cannot be of the ESQL REFERENCE, ROW, LIST, or INTERVAL data types.
    User-defined types cannot be used as parameters or as return values.

_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
CAONIMA
PostPosted: Tue Feb 03, 2015 6:41 am    Post subject: Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
Thank you for your reply.

So in my case, how should I write my code to define the datatype in SP?
Back to top
View user's profile Send private message
mqjeff
PostPosted: Tue Feb 03, 2015 6:43 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447
CAONIMA wrote:
Thank you for your reply.

So in my case, how should I write my code to define the datatype in SP?


You need to write a stored procedure that matches the restrictions given.

Then you can write an ESQL call to that stored procedure.

If you aren't already talking to your DBA, then you should be.
Back to top
View user's profile Send private message
CAONIMA
PostPosted: Tue Feb 03, 2015 6:46 am    Post subject: Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
Many thanks for your reply.

Here is the SP code in Oracle:

-----------------------------------------------------------------------
DECLARE
P_MASTER_IO PL/SQL RECORD;
P_DET_IO ETRAC.CRM_PROCESSING.DETREC_TBL_T;
P_RESPONSE_O PL/SQL RECORD;
BEGIN
P_MASTER_IO := NULL;
-- Modify the code to initialize the variable
-- P_DET_IO := NULL;
P_RESPONSE_O := NULL;

CRM_PROCESSING.SUGAR_ETRAC_INTERFACE(
P_MASTER_IO => P_MASTER_IO,
P_DET_IO => P_DET_IO,
P_RESPONSE_O => P_RESPONSE_O
);
DBMS_OUTPUT.PUT_LINE('P_MASTER_IO = ' || P_MASTER_IO);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('P_DET_IO = ' || P_DET_IO);
DBMS_OUTPUT.PUT_LINE('P_RESPONSE_O = ' || P_RESPONSE_O);
END;

-------------------------------------------------------------------------

I don't know what is PL/SQL RECORD means?
and how should I define a table in ESQL, because the P_DET_IO is a table.
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Feb 03, 2015 6:53 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
CAONIMA wrote:
I don't know what is PL/SQL RECORD means?
and how should I define a table in ESQL, because the P_DET_IO is a table.


mqjeff wrote:
You need to write a stored procedure that matches the restrictions given.

Then you can write an ESQL call to that stored procedure.

If you aren't already talking to your DBA, then you should be.

_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
nelson
PostPosted: Tue Feb 03, 2015 6:57 am    Post subject: Reply with quote

Partisan

Joined: 02 Oct 2012
Posts: 313
CAONIMA wrote:

I don't know what is PL/SQL RECORD means?
and how should I define a table in ESQL, because the P_DET_IO is a table.


Ok, well in Oracle we have used SYS_REFCURSOR when we need to retrieve multiple rows, honestly, not sure if the behavior of PL/SQL RECORD is the same.

You should try calling the SP in this way:

Code:
DECLARE dbData1 ROW;
DECLARE dbData2 ROW;
DECLARE dbData3 ROW;

CALL etrac(dbData1.row[],dbData2.row[],dbData3.row[]);



@Vitor... I mean. You first declare the row and then call the SP with that row as a parameter
Back to top
View user's profile Send private message
nelson
PostPosted: Tue Feb 03, 2015 7:01 am    Post subject: Reply with quote

Partisan

Joined: 02 Oct 2012
Posts: 313
well..... ... Haven´t tried sending a table to a SP. Havent noticed there were input parameters... So, I finally understand what Vitor said...

@Vitor. There is no way to achieve this?
Back to top
View user's profile Send private message
CAONIMA
PostPosted: Tue Feb 03, 2015 7:10 am    Post subject: Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
Many thanks for your patient.

It doesn't work. And the same issue.

Here is all my ESQL code

-------------------------------------------------------------------------------------

DECLARE p_master_io ROW;
DECLARE p_det_io ROW;
DECLARE p_response_o ROW ;
DECLARE pattern CHARACTER 'dd-MM-yy';


DECLARE PCNT INT CARDINALITY(InputRoot.JSON.Data.*[]);
DECLARE INC INT 1;
WHILE INC<= PCNT DO

--Fields on Master Table
DECLARE p_SUGAR_REF_NUMBER_i CHAR InputRoot.JSON.Data.*[<].SUGAR_REF_NUMBER;
DECLARE p_PROBLEM_NUMBER_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_NUMBER;
DECLARE p_SB_TYPE_i CHAR InputRoot.JSON.Data.*[<].SB_TYPE;
DECLARE p_PROBLEM_STATUS_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_STATUS;
DECLARE p_INVOICE_NUMBER_i DECIMAL CAST(InputRoot.JSON.Data.*[<].INVOICE_NUMBER AS DECIMAL);
DECLARE p_INVOICE_DATE_i TIMESTAMP CAST(InputRoot.JSON.Data.*[<].INVOICE_DATE AS TIMESTAMP FORMAT pattern);
DECLARE p_CUSTOMER_PO_NUMBER_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_PO_NUMBER;
DECLARE p_CUSTOMER_SHIP_TO_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_SHIP_TO;
DECLARE p_CUSTOMER_BILL_TO_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_BILL_TO;
DECLARE p_CUSTOMER_REBILL_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_REBILL;
DECLARE p_SCAC_i CHAR InputRoot.JSON.Data.*[<].SCAC;
DECLARE p_CARRIER_NAME_i CHAR InputRoot.JSON.Data.*[<].CARRIER_NAME;
DECLARE p_STORE_POS_NUMBER_i CHAR InputRoot.JSON.Data.*[<].STORE_POS_NUMBER;
DECLARE p_FIELD_CREDIT_FLAG_i CHAR InputRoot.JSON.Data.*[<].FIELD_CREDIT_FLAG;
DECLARE p_BLANKET_TYPE_i CHAR InputRoot.JSON.Data.*[<].BLANKET_TYPE;
DECLARE p_CREDIT_TYPE_i CHAR InputRoot.JSON.Data.*[<].CREDIT_TYPE;
DECLARE p_RETURN_FLAG_i CHAR InputRoot.JSON.Data.*[<].RETURN_FLAG;
DECLARE p_CREDIT_FLAG_i CHAR InputRoot.JSON.Data.*[<].CREDIT_FLAG;
DECLARE p_SW_INVOICE_NUMBER_i CHAR InputRoot.JSON.Data.*[<].SW_INVOICE_NUMBER;
DECLARE p_ORIGINAL_SHIPPING_LOCATION_i CHAR InputRoot.JSON.Data.*[<].ORIGINAL_SHIPPING_LOCATION;
DECLARE p_PROFORMA_CREDIT_REQUESTED_i CHAR InputRoot.JSON.Data.*[<].PROFORMA_CREDIT_REQUESTED;
DECLARE p_PROFORMA_CREDIT_ALLOWED_i CHAR InputRoot.JSON.Data.*[<].PROFORMA_CREDIT_ALLOWED;
DECLARE p_ENTRY_USER_i CHAR InputRoot.JSON.Data.*[<].ENTRY_USER;
DECLARE p_ENTRY_DATETIME_i TIMESTAMP CAST(InputRoot.JSON.Data.*[<].ENTRY_DATETIME AS TIMESTAMP FORMAT pattern);
DECLARE p_RGA_NUMBER_i CHAR InputRoot.JSON.Data.*[<].RGA_NUMBER;
DECLARE p_PROBLEM_CODE_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_CODE;
DECLARE p_PROBLEM_AREA_CODE_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_AREA_CODE;
DECLARE p_PROBLEM_CLASS_CODE_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_CLASS_CODE;
DECLARE p_CLASS_COUNT_i DECIMAL CAST(InputRoot.JSON.Data.*[<].CLASS_COUNT AS DECIMAL);
DECLARE p_CUSTOMER_PAYS_FREIGHT_FLAG_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_PAYS_FREIGHT_FLAG;
DECLARE p_CUSTOMER_PAYS_RESTOCK_FLAG_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_PAYS_RESTOCK_FLAG;
DECLARE p_CUST_PAYS_HANDLING_FLAG_i CHAR InputRoot.JSON.Data.*[<].CUSTOMER_PAYS_HANDLING_FLAG;
DECLARE p_BOL_NUMBER_i CHAR InputRoot.JSON.Data.*[<].BOL_NUMBER;
DECLARE p_PRO_NUMBER_i CHAR InputRoot.JSON.Data.*[<].PRO_NUMBER;
DECLARE p_COMMENTS1_i CHAR InputRoot.JSON.Data.*[<].COMMENTS1;
DECLARE p_COMMENTS2_i CHAR InputRoot.JSON.Data.*[<].COMMENTS2;
DECLARE p_RETURN_DSC_i CHAR InputRoot.JSON.Data.*[<].RETURN_DSC;
DECLARE p_RETURN_SCAC_i CHAR InputRoot.JSON.Data.*[<].RETURN_SCAC;
DECLARE p_RETURN_CARRIER_i CHAR InputRoot.JSON.Data.*[<].RETURN_CARRIER;
DECLARE p_REFER_NUMBER_i CHAR InputRoot.JSON.Data.*[<].REFER_NUMBER;
DECLARE p_GL_ACCT_i CHAR InputRoot.JSON.Data.*[<].GL_ACCT;
DECLARE p_PROMOTION_CODE_i CHAR InputRoot.JSON.Data.*[<].PROMOTION_CODE;
DECLARE p_FREIGHT_TERMS_i CHAR InputRoot.JSON.Data.*[<].FREIGHT_TERMS;
DECLARE p_PROB_SOURCE_USERID_i CHAR InputRoot.JSON.Data.*[<].PROB_SOURCE_USERID;
DECLARE p_AUTHORIZATION_TEXT_i CHAR InputRoot.JSON.Data.*[<].AUTHORIZATION_TEXT;
DECLARE p_SHIPPING_AMOUNT_i DECIMAL CAST(InputRoot.JSON.Data.*[<].SHIPPING_AMOUNT AS DECIMAL);
DECLARE p_FIELD_CREDIT_MEMO_FLAG_i CHAR InputRoot.JSON.Data.*[<].FIELD_CREDIT_MEMO_FLAG;
DECLARE p_REFERENCE_NAME_i CHAR InputRoot.JSON.Data.*[<].REFERENCE_NAME;
DECLARE p_REFERENCE_NUMBER_i CHAR InputRoot.JSON.Data.*[<].REFERENCE_NUMBER;
DECLARE p_ADD_CHG_DEL_i CHAR InputRoot.JSON.Data.*[<].ADD_CHG_DEL;
DECLARE p_TIME_STAMP_i TIMESTAMP CAST(InputRoot.JSON.Data.*[<].TIME_STAMP AS TIMESTAMP FORMAT pattern);
DECLARE p_POST_TRIGGER_i CHAR InputRoot.JSON.Data.*[<].POST_TRIGGER;
DECLARE p_PROCESSED_FLAG_i CHAR InputRoot.JSON.Data.*[<].PROCESSED_FLAG;
DECLARE p_PROBLEM_PREFIX_i CHAR InputRoot.JSON.Data.*[<].PROBLEM_PREFIX;
DECLARE p_CONTACT_NAME_i CHAR InputRoot.JSON.Data.*[<].CONTACT_NAME;
DECLARE p_PHONE_NUMBER_i CHAR InputRoot.JSON.Data.*[<].PHONE_NUMBER;
DECLARE p_FAX_NUMBER_i CHAR InputRoot.JSON.Data.*[<].FAX_NUMBER;
DECLARE p_EMAIL_ADDRESS_i CHAR InputRoot.JSON.Data.*[<].EMAIL_ADDRESS;
DECLARE p_SW_EMAIL_ADDRESS_i CHAR InputRoot.JSON.Data.*[<].SW_EMAIL_ADDRESS;

--Master Table
SET Environment.Variable.Master[INC].p_SUGAR_REF_NUMBER_i= p_SUGAR_REF_NUMBER_i;
SET Environment.Variable.Master[INC].p_PROBLEM_NUMBER_i= p_PROBLEM_NUMBER_i;
SET Environment.Variable.Master[INC].p_SB_TYPE_i= p_SB_TYPE_i;
SET Environment.Variable.Master[INC].p_PROBLEM_STATUS_i= p_PROBLEM_STATUS_i;
SET Environment.Variable.Master[INC].p_INVOICE_NUMBER_i = p_INVOICE_NUMBER_i;
SET Environment.Variable.Master[INC].p_INVOICE_DATE_i = p_INVOICE_DATE_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_PO_NUMBER_i = p_CUSTOMER_PO_NUMBER_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_SHIP_TO_i = p_CUSTOMER_SHIP_TO_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_BILL_TO_i = p_CUSTOMER_BILL_TO_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_REBILL_i = p_CUSTOMER_REBILL_i;
SET Environment.Variable.Master[INC].p_SCAC_i = p_SCAC_i;
SET Environment.Variable.Master[INC].p_CARRIER_NAME_i = p_CARRIER_NAME_i;
SET Environment.Variable.Master[INC].p_STORE_POS_NUMBER_i= p_STORE_POS_NUMBER_i;
SET Environment.Variable.Master[INC].p_FIELD_CREDIT_FLAG_i= p_FIELD_CREDIT_FLAG_i;
SET Environment.Variable.Master[INC].p_BLANKET_TYPE_i= p_BLANKET_TYPE_i;
SET Environment.Variable.Master[INC].p_CREDIT_TYPE_i= p_CREDIT_TYPE_i;
SET Environment.Variable.Master[INC].p_RETURN_FLAG_i= p_RETURN_FLAG_i;
SET Environment.Variable.Master[INC].p_CREDIT_FLAG_i= p_CREDIT_FLAG_i;
SET Environment.Variable.Master[INC].p_SW_INVOICE_NUMBER_i= p_SW_INVOICE_NUMBER_i;
SET Environment.Variable.Master[INC].p_ORIGINAL_SHIPPING_LOCATION_i = p_ORIGINAL_SHIPPING_LOCATION_i;
SET Environment.Variable.Master[INC].p_PROFORMA_CREDIT_REQUESTED_i= p_PROFORMA_CREDIT_REQUESTED_i;
SET Environment.Variable.Master[INC].p_PROFORMA_CREDIT_ALLOWED_i= p_PROFORMA_CREDIT_ALLOWED_i;
SET Environment.Variable.Master[INC].p_ENTRY_USER_i= p_ENTRY_USER_i;
SET Environment.Variable.Master[INC].p_ENTRY_DATETIME_i=p_ENTRY_DATETIME_i;
SET Environment.Variable.Master[INC].p_RGA_NUMBER_i= p_RGA_NUMBER_i;
SET Environment.Variable.Master[INC].p_PROBLEM_CODE_i= p_PROBLEM_CODE_i;
SET Environment.Variable.Master[INC].p_PROBLEM_AREA_CODE_i = p_PROBLEM_AREA_CODE_i;
SET Environment.Variable.Master[INC].p_PROBLEM_CLASS_CODE_i= p_PROBLEM_CLASS_CODE_i;
SET Environment.Variable.Master[INC].p_CLASS_COUNT_i= p_CLASS_COUNT_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_PAYS_FREIGHT_FLAG_i = p_CUSTOMER_PAYS_FREIGHT_FLAG_i;
SET Environment.Variable.Master[INC].p_CUSTOMER_PAYS_RESTOCK_FLAG_i = p_CUSTOMER_PAYS_RESTOCK_FLAG_i;
SET Environment.Variable.Master[INC].p_CUST_PAYS_HANDLING_FLAG_i= p_CUST_PAYS_HANDLING_FLAG_i;
SET Environment.Variable.Master[INC].p_BOL_NUMBER_i= p_BOL_NUMBER_i;
SET Environment.Variable.Master[INC].p_PRO_NUMBER_i= p_PRO_NUMBER_i;
SET Environment.Variable.Master[INC].p_COMMENTS1_i= p_COMMENTS1_i;
SET Environment.Variable.Master[INC].p_COMMENTS2_i= p_COMMENTS2_i;
SET Environment.Variable.Master[INC].p_RETURN_DSC_i= p_RETURN_DSC_i;
SET Environment.Variable.Master[INC].p_RETURN_SCAC_i= p_RETURN_SCAC_i;
SET Environment.Variable.Master[INC].p_RETURN_CARRIER_i= p_RETURN_CARRIER_i;
SET Environment.Variable.Master[INC].p_REFER_NUMBER_i= p_REFER_NUMBER_i;
SET Environment.Variable.Master[INC].p_GL_ACCT_i= p_GL_ACCT_i;
SET Environment.Variable.Master[INC].p_PROMOTION_CODE_i= p_PROMOTION_CODE_i;
SET Environment.Variable.Master[INC].p_FREIGHT_TERMS_i = p_FREIGHT_TERMS_i;
SET Environment.Variable.Master[INC].p_PROB_SOURCE_USERID_i= p_PROB_SOURCE_USERID_i;
SET Environment.Variable.Master[INC].p_AUTHORIZATION_TEXT_i= p_AUTHORIZATION_TEXT_i;
SET Environment.Variable.Master[INC].p_SHIPPING_AMOUNT_i = p_SHIPPING_AMOUNT_i;
SET Environment.Variable.Master[INC].p_FIELD_CREDIT_MEMO_FLAG_i= p_FIELD_CREDIT_MEMO_FLAG_i;
SET Environment.Variable.Master[INC].p_REFERENCE_NAME_i = p_REFERENCE_NAME_i;
SET Environment.Variable.Master[INC].p_REFERENCE_NUMBER_i = p_REFERENCE_NUMBER_i;
SET Environment.Variable.Master[INC].p_ADD_CHG_DEL_i= p_ADD_CHG_DEL_i;
SET Environment.Variable.Master[INC].p_TIME_STAMP_i = p_TIME_STAMP_i;
SET Environment.Variable.Master[INC].p_POST_TRIGGER_i= p_POST_TRIGGER_i;
SET Environment.Variable.Master[INC].p_PROCESSED_FLAG_i= p_PROCESSED_FLAG_i;
SET Environment.Variable.Master[INC].p_PROBLEM_PREFIX_i= p_PROBLEM_PREFIX_i;
SET Environment.Variable.Master[INC].p_CONTACT_NAME_i= p_CONTACT_NAME_i;
SET Environment.Variable.Master[INC].p_PHONE_NUMBER_i= p_PHONE_NUMBER_i;
SET Environment.Variable.Master[INC].p_FAX_NUMBER_i= p_FAX_NUMBER_i;
SET Environment.Variable.Master[INC].p_EMAIL_ADDRESS_i= p_EMAIL_ADDRESS_i;
SET Environment.Variable.Master[INC].p_SW_EMAIL_ADDRESS_i= p_SW_EMAIL_ADDRESS_i;

set p_master_io.Row[INC] = Environment.Variable.Master[INC];
set INC = INC+1;
END WHILE;
--Fields on Child Table


SET PCNT =CARDINALITY(InputRoot.JSON.Data.*[<].*[<].*[]);
SET INC = 1;

WHILE INC<=PCNT DO


DECLARE p_SUGAR_REF_NUMBER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].SUGAR_REF_NUMBER;
DECLARE p_PROBLEM_NUMBER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PROBLEM_NUMBER;
DECLARE p_LINE_NUMBER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].LINE_NUMBER;
DECLARE p_LINE_SEQUENCE_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].LINE_SEQUENCE;
DECLARE p_LINE_STATUS_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].LINE_STATUS;
DECLARE p_INVOICE_NUMBER_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].INVOICE_NUMBER AS DECIMAL);
DECLARE p_INVOICE_ROW_POS_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].INVOICE_ROW_POS AS DECIMAL);
DECLARE p_INVOICE_ROW_SUB_POS_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].INVOICE_ROW_SUB_POS AS DECIMAL);
DECLARE p_INVOICE_ROW_SEQUENCE_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].INVOICE_ROW_SEQUENCE AS DECIMAL);
DECLARE p_PROD_ORD_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PROD_ORD_BY_CUSTOMER;
DECLARE p_QTY_ORD_BY_CUSTOMER_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].QTY_ORD_BY_CUSTOMER AS DECIMAL);
DECLARE p_UOM_ORD_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].UOM_ORD_BY_CUSTOMER;
DECLARE p_PRICE_ORD_BY_CUSTOMER_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PRICE_ORD_BY_CUSTOMER AS DECIMAL);
DECLARE p_MANUAL_PRICE_ORD_BY_CUST_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].MANUAL_PRICE_ORD_BY_CUSTOMER;
DECLARE p_PROD_RCV_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PROD_RCV_BY_CUSTOMER;
DECLARE p_QTY_RCV_BY_CUSTOMER_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].QTY_RCV_BY_CUSTOMER AS DECIMAL);
DECLARE p_UOM_RCV_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].UOM_RCV_BY_CUSTOMER;
DECLARE p_PRICE_RCV_BY_CUSTOMER_I DECIMAL CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PRICE_RCV_BY_CUSTOMER AS DECIMAL);
DECLARE p_MANUAL_PRICE_RCV_BY_CUST_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].MANUAL_PRICE_RCV_BY_CUSTOMER;
DECLARE p_PROD_RET_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PROD_RET_BY_CUSTOMER;
DECLARE p_QTY_RET_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].QTY_RET_BY_CUSTOMER;
DECLARE p_UOM_RET_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].UOM_RET_BY_CUSTOMER;
DECLARE p_PRICE_RET_BY_CUSTOMER_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PRICE_RET_BY_CUSTOMER;
DECLARE p_MANUAL_PRICE_RET_BY_CUST_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].MANUAL_PRICE_RET_BY_CUSTOMER;
DECLARE p_PRINT_FLAG_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PRINT_FLAG;
DECLARE p_PASS_THRU_FLAG_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PASS_THRU_FLAG;
DECLARE p_LINE_TYPE_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].LINE_TYPE;
DECLARE p_LOOSE_UNITS_FLAG_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].LOOSE_UNITS_FLAG;
DECLARE p_TIME_STAMP_I TIMESTAMP CAST(InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].TIME_STAMP AS TIMESTAMP FORMAT pattern);
DECLARE p_PROCESSED_FLAG_I CHAR InputRoot.JSON.Data.CRM_INT_MASTER.CRM_INT_DETAIL.Item[INC].PROCESSED_FLAG;

SET Environment.Variable.Detail.Item[INC].p_SUGAR_REF_NUMBER_I= p_SUGAR_REF_NUMBER_I;
SET Environment.Variable.Detail.Item[INC].p_PROBLEM_NUMBER_I= p_PROBLEM_NUMBER_I;
SET Environment.Variable.Detail.Item[INC].p_p_LINE_NUMBER_I= p_LINE_NUMBER_I;
SET Environment.Variable.Detail.Item[INC].p_LINE_SEQUENCE_I= p_LINE_SEQUENCE_I;
SET Environment.Variable.Detail.Item[INC].p_LINE_STATUS_I= p_LINE_STATUS_I;
SET Environment.Variable.Detail.Item[INC].p_INVOICE_NUMBER_I= p_INVOICE_NUMBER_I;
SET Environment.Variable.Detail.Item[INC].p_INVOICE_ROW_POS_I= p_INVOICE_ROW_POS_I;
SET Environment.Variable.Detail.Item[INC].p_INVOICE_ROW_SUB_POS_I= p_INVOICE_ROW_SUB_POS_I;
SET Environment.Variable.Detail.Item[INC].p_INVOICE_ROW_SEQUENCE_I= p_INVOICE_ROW_SEQUENCE_I;
SET Environment.Variable.Detail.Item[INC].p_PROD_ORD_BY_CUSTOMER_I=p_PROD_ORD_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_QTY_ORD_BY_CUSTOMER_I= p_QTY_ORD_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_UOM_ORD_BY_CUSTOMER_I= p_UOM_ORD_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_PRICE_ORD_BY_CUSTOMER_I= p_PRICE_ORD_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_MANUAL_PRICE_ORD_BY_CUST_I=p_MANUAL_PRICE_ORD_BY_CUST_I;
SET Environment.Variable.Detail.Item[INC].p_PROD_RCV_BY_CUSTOMER_I= p_PROD_RCV_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_QTY_RCV_BY_CUSTOMER_I= p_QTY_RCV_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_UOM_RCV_BY_CUSTOMER_I= p_UOM_RCV_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_PRICE_RCV_BY_CUSTOMER_I= p_PRICE_RCV_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_MANUAL_PRICE_RCV_BY_CUST_I= p_MANUAL_PRICE_RCV_BY_CUST_I;
SET Environment.Variable.Detail.Item[INC].p_PROD_RET_BY_CUSTOMER_I= p_PROD_RET_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_QTY_RET_BY_CUSTOMER_I=p_QTY_RET_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_UOM_RET_BY_CUSTOMER_I= p_UOM_RET_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_PRICE_RET_BY_CUSTOMER_I= p_PRICE_RET_BY_CUSTOMER_I;
SET Environment.Variable.Detail.Item[INC].p_MANUAL_PRICE_RET_BY_CUST_I= p_MANUAL_PRICE_RET_BY_CUST_I;
SET Environment.Variable.Detail.Item[INC].p_PRINT_FLAG_I= p_PRINT_FLAG_I;
SET Environment.Variable.Detail.Item[INC].p_PASS_THRU_FLAG_I= p_PASS_THRU_FLAG_I;
SET Environment.Variable.Detail.Item[INC].p_LINE_TYPE_I= p_LINE_TYPE_I;
SET Environment.Variable.Detail.Item[INC].p_LOOSE_UNITS_FLAG_I= p_LOOSE_UNITS_FLAG_I;
SET Environment.Variable.Detail.Item[INC].p_TIME_STAMP_I= p_TIME_STAMP_I;
SET Environment.Variable.Detail.Item[INC].p_PROCESSED_FLAG_I= p_PROCESSED_FLAG_I;
set p_det_io.Row[INC] = Environment.Variable.Detail.Item[INC];
SET INC = INC +1;
END WHILE;


Call sugar_etrac_interface (p_master_io.Row[], p_det_io.Row[], p_response_o.Row[]);



RETURN TRUE;
END;




CREATE PROCEDURE sugar_etrac_interface()
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "etrac.crm_processing.sugar_etrac_interface";


CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
Back to top
View user's profile Send private message
mqjeff
PostPosted: Tue Feb 03, 2015 7:15 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447
It doesn't work because you can't do that.

You have to change the stored procedure.
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Feb 03, 2015 7:17 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
nelson wrote:
@Vitor. There is no way to achieve this?





Unless someone knows different, with @mqjeff on this one.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
CAONIMA
PostPosted: Tue Feb 03, 2015 8:18 am    Post subject: Reply with quote

Acolyte

Joined: 03 Dec 2014
Posts: 65
They probably cannot change the stored procedure.

Do you know how to define the %ROWTYPE in ESQL?
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Feb 03, 2015 8:22 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
CAONIMA wrote:
Do you know how to define the %ROWTYPE in ESQL?


If you tried reading the comments on this thread, you'd notice 2 things:

- %ROWTYPE is a ROW in ESQL
- ESQL does not allow a ROW type to be a parameter.

It's for this reason you've been advised (multiple times) to do it a different way. It will not work this way because that's not allowed. Or as my most worthy associate put it:

mqjeff wrote:
It doesn't work because you can't do that.

_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Call stored procedure
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
  
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.