Using Dynamic Sql in Stored Procedures 1

[mynwo]

Hi All,

I need to know how we can use dynamic sql in a stored procedure. Basically i need to use the procedure with insert & update clause. My users will use the procedure in java & pass the parameters for the insert & in back-end i will insert the data into the table. Basically we don't want the users to give insert/update clause in their application. I would really appreciate if you could give me some examples. Please advice.

Regards,

lloyd

 

[olded]

Lloyd:

Unfortunately, you can can't build dynamic management statements, DML, in Informix stored procs. The best you can hope for is to create stored procs with parameters passed that are used in hard-coded DML. Below is an example:

Regards,

Ed

CREATE PROCEDURE sap_reccncllntn( p_receiptid CHAR(15))
RETURNING INTEGER;
DEFINE p_status SMALLINT;
DEFINE p_count SMALLINT;
DEFINE p_count_x SMALLINT;
DEFINE p_mrpid LIKE sap_recinterface.mrpid;
DEFINE p_workorder LIKE sap_recinterface.workorder;
DEFINE p_item LIKE sap_recinterface.item;
DEFINE p_received_qty LIKE sap_recinterface.received_qty;
DEFINE p_batch_number LIKE sap_recinterface.batch_number;
DEFINE p_stock_id LIKE sap_recinterface.stock_id;

ON EXCEPTION
SET p_status
RETURN p_status;
END EXCEPTION
LET p_status = 0;
SELECT count(*) INTO p_count FROM sap_recinterface WHERE receiptid = p_receiptid ;
IF (p_count = 0)
THEN
RETURN 0 ;
END IF

UPDATE sap_recinterface
SET sent_flag = 'X'
WHERE receiptid = p_receiptid AND sent_flag = 'N' ;
LET p_count_x = DBINFO('SQLCA.SQLERRD2') ;
IF p_count != p_count_x
THEN
FOREACH SELECT mrpid,workorder,item,received_qty,batch_number,stock_id
INTO p_mrpid,p_workorder,p_item,p_received_qty,p_batch_number,p_stock_id
FROM sap_recinterface WHERE receiptid = p_receiptid AND sent_flag = 'Y' AND movement_type = '101'

INSERT INTO sap_recinterface (receiptid,batch_number,movement_type,logid,mrpid,workorder,stock_id,order_status, item,received_qty,sent_flag)
VALUES (p_receiptid,p_batch_number,'102',NULL,p_mrpid,p_workorder,p_stock_id,p_item,p_received_qty,'N') ;

END FOREACH
END IF
RETURN p_status ;
END PROCEDURE;

 

[mynwo]

Hi Ed,

Thanks a lot for your advice & for the examples too.

Best Regards,

Lloyd

 

[mynwo]

Hi Ed,

If its not possible using Stored Procedures, then how can we use it. Is thru 4gl or Esqlc. Please advice. It would be great if u could provide some examples.

Regards,

lloyd