snowflake-cloud-data-platformmulesoftanypoint-studio

Mulesoft Snowflake Connector Parameterized Queries


In Mulesoft Anypoint studio, how do I reference the input parameters in the SQL string (refer to screenshot)? I have tried using #[payload.x], but that does not get interpolated and results in an error. Not sure what the correct syntax to use is.

Snowflake Insert Configuration Screen

For convenience, this is the SQL String:

INSERT INTO adoit_capability_relationships ("capabilityId", "itemType", "itemId", "itemName", "dataLoadDate") VALUES(capabilityId, itemType, itemId, itemName, CURRENT_TIMESTAMP());

And here are the input parameters I am trying to use:

{
    "capabilityId": payload.capabilityId,
    "itemType": payload.itemType,
    "itemId": payload.itemId,
    "itemName": payload.itemName,
    "dataLoadDate": "CURRENT_TIMESTAMP()"
}

Solution

  • Using :param_name and INSERT INTO ... SELECT:

    INSERT INTO adoit_capability_relationships 
                ("capabilityId", "itemType", "itemId", "itemName", "dataLoadDate") 
    SELECT :capabilityId, :itemType, :itemId, :itemName, CURRENT_TIMESTAMP();