Page tree
Skip to end of metadata
Go to start of metadata

The below  POST request is writtent to handle 3 parameters namely

  • comment
  • postId
  • publisherId
  • url


For this example, the user will not be sending postId, in such a case, we can run the following query using the $url parameter

Select Case Example
select CASE 
  WHEN $postId is null THEN (SELECT POST_ID as id from POST where POST_URL= $url)
  ELSE $postId END
as 'id'


POST Request

Fetch PostId from database
<Request method="POST" status="201">
	<Sql id="existingPost" output="false" type="query">
	   select CASE 
	      WHEN $postId is null THEN (SELECT POST_ID as id from POST where POST_URL= $url)
	      ELSE $postId END
	   as 'id'
	</Sql>
	<Sql id="comment">
       insert into COMMENT ( TIMESTAMP, MESSAGE, POST_ID, PUBLISHER_ID) 
       values (current_timestamp(), $comment, $[existingPost][0].id, $publisherId)
    </Sql>
</Request>

Generate postId with insert query

Below insert query is run when the postId is empty, to create a new post. Here you can check if the duplicate record is not created in the database, and you can mark the URL field as unique in your schema.

<Request method="POST" status="201">
    <Desc>Create new post if not</Desc>
    <Sql id="createPost" output="false" when="empty $postId">
       insert into POST(PUBLISHER_ID, POST_TYPE, CREATION_DATE, PAGE_URL) 
       values(2, 2, '2020-10-15 18:12:05', $url)      		
    </Sql>
	<Sql id="existingPost" output="false" type="query">
		select CASE 
		   WHEN $postId is null THEN (SELECT POST_ID as id from POST where POST_URL= $url)
		   ELSE $postId END
		as 'id'
	</Sql>
	<Sql id="comment">
       insert into COMMENT ( TIMESTAMP, MESSAGE, POST_ID, PUBLISHER_ID) 
       values (current_timestamp(), $comment, $[existingPost][0].id, $publisherId)
    </Sql>
</Request>

In the second sql statement, we have not applied the when condition to peform if-else check using select statement. The existing post fetched from the second sql statement is used in the insert, thereby merging the request parameter and the value returned from the database.


  • No labels