3 Replies Latest reply on Mar 19, 2018 7:18 PM by Ramesh Reddy

    Issue with Virtual StoredProcedure on Inserting Data into a postgressql DB

    Girish Aravamudhan Newbie

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