Checking Empty Sql Result

The following GET request is designed to retrieve customer information.


<Request method="GET">

    <Param name="name" type="text" required="true" />

    <!-- Show the customer information stored in the database  -->
    <Sql id="customerList">
        select * from customer where name = $name
    </Sql>  

</Request>

SQL can give out zero to many results. We try to check for 0 results using first record attribute using MPath. We can take any attribute for this purpose. We can use empty keyword inside when condition

<Sql when="empty $[customerList][0].id" id="addCustomer">
    insert into new_customer_query(name) values($name)
</Sql> 

OR we can use the standard eq comparison with null

<Sql when="$[customerList][0].id eq null" id="addCustomer">
    insert into new_customer_query(name) values($name)
</Sql> 

In our example we are saving the customer query into another table, in case our main customer table does not return any record.

<Request method="GET">

    <Param name="name" type="text" required="true" />

    <!-- Show the customer information stored in the database  -->
    <Sql id="customerList">
      select * from customer where name = $name
    </Sql>  

    <Sql when="empty $[customerList][0].id" id="addCustomer">
      insert into new_customer_query(name) values($name)
    </Sql>

</Request>

Any other element like XRequest or a Plugin can be executed based on empty Sql result. But for the sake of simplicity, we chose another Sql statement.

Icon For Arrow-up