Issue with Virtual StoredProcedure on Inserting Data into a postgressql DB
girisharavamidhan Mar 19, 2018 10:03 AMHi Team,
I have created a virtual stored prcedure to insert data into postgres DB. Below is my prcedure
BEGIN DECLARE long VARIABLES.workflowinstanceid = cast(VM_KOCKWIDFBPMDB.SP_INSERTTICKET.workflowinstanceid AS long); DECLARE integer VARIABLES.workflowid = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.workflowid; DECLARE string VARIABLES.description = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.description; DECLARE string VARIABLES.createdby = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.createdby; DECLARE string VARIABLES.priority = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.priority; DECLARE timestamp VARIABLES.targetdate = cast(VM_KOCKWIDFBPMDB.SP_INSERTTICKET.targetdate AS timestamp); DECLARE object VARIABLES.ticketdetails = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.ticketdetails(native type JSON); DECLARE string VARIABLES.attachmenturls = VM_KOCKWIDFBPMDB.SP_INSERTTICKET.attachmenturls; DECLARE integer VARIABLES.ticketid = 0; DECLARE integer VARIABLES.stageId = 0; INSERT INTO KWIDF.tickets (KWIDF.tickets.workflowinstanceid, KWIDF.tickets.workflowid, KWIDF.tickets.description, KWIDF.tickets.createdby, KWIDF.tickets.createddate, KWIDF.tickets.priority, KWIDF.tickets.targetdate, KWIDF.tickets.ticketdetails, KWIDF.tickets.attachmenturls) VALUES (VARIABLES.workflowinstanceid, VARIABLES.workflowid, VARIABLES.description, VARIABLES.createdby, now(), VARIABLES.priority, VARIABLES.targetdate, VARIABLES.ticketdetails, VARIABLES.attachmenturls); VARIABLES.ticketid = (SELECT KWIDF.tickets.ticketid FROM KWIDF.tickets WHERE KWIDF.tickets.workflowinstanceid = VARIABLES.workflowinstanceid); INSERT INTO KWIDF.tickethistory (KWIDF.tickethistory.ticketid, KWIDF.tickethistory.ticketseqid, KWIDF.tickethistory.createdby, KWIDF.tickethistory.createddate, KWIDF.tickethistory.ticketstagedetails, KWIDF.tickethistory.attachmenturls, KWIDF.tickethistory.ticketstage) VALUES (VARIABLES.ticketid, 1, VARIABLES.createdby, now(), VARIABLES.ticketdetails, VARIABLES.attachmenturls, 'Created'); VARIABLES.stageId = (SELECT KWIDF.tickethistory.tickethistoryid FROM KWIDF.tickethistory WHERE (KWIDF.tickethistory.ticketid = VARIABLES.ticketid) AND (KWIDF.tickethistory.ticketstage = 'Created')); IF(VARIABLES.stageId IS NOT NULL) BEGIN SELECT 'SUCCESS' AS OP; END ELSE BEGIN SELECT 'FAIL' AS OP; END END
Below is the Odata call I am making.
http://localhost:8080/dsdataserver/dsl.svc/VM_KOCKWIDFBPMDB/1/FDWorkFlow/ALL_PROJECTS/SP_INSERTTICKET?workflowinstanceid=262&description=%27test%27&createdby=%27girisha%27&priority=%27High%27&ticketdetails=%27{}%27&attachmenturls=%27test.com%27&ticketstage=%27Created%27http://localhost:8080/dsdataserver/dsl.svc/VM_KOCKWIDFBPMDB/1/FDWorkFlow/ALL_PROJECTS/SP_INSERTTICKET?workflowinstanceid…
Below is my postgres table script.
-- Table: kockwidf.tickets
-- DROP TABLE kockwidf.tickets;
CREATE TABLE kockwidf.tickets ( ticketid serial NOT NULL, workflowinstanceid bigint NOT NULL, workflowid bigint, description character varying(250), createdby character varying(50), createddate timestamp without time zone, priority character varying(20), targetdate timestamp without time zone, ticketdetails json, attachmenturls character varying(500), ticketstage character varying(50), CONSTRAINT tickets_pk PRIMARY KEY (ticketid), CONSTRAINT tickets_fk_workflows FOREIGN KEY (workflowid) REFERENCES kockwidf.workflows (workflowid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); ALTER TABLE kockwidf.tickets OWNER TO postgres;
I am getting the error whenever if the JSON object has some details.But its working with empty JSON object.i.e.{}. Please help me in this regard.If I insert the data through virtural view trigger to the same json column its working fine.Below is my JNDI Connection string jdbc:postgresql://localhost:5432/dsbpm_db?stringtype=unspecified
Please help me to fix this issue.