Skip to main content

How to use Parameters in Run SQL Query activity with Auto Complete

Jesse Langdon
Edited

I have a workflow in which I am attempting to populate an Auto Complete text input with the results of a query to a non-spatial SQL Server database. I have a server workflow with the Run SQL Query that is called by a client workflow using a Run Workflow activity. But how do I properly format the Run SQL Query “Parameters” to use the Auto Complete input from the client workflow correctly?

I'm able successfully return results with a very simple client workflow which calls the server workflow to run the query, using a hard-coded value for the SQL parameter (as outlined in the VertiGIS documentation):

Example:
={"PermitNum" : '15100101'}

Since this is returning a result from the SQL Server database, I assume the Run SQL Query activity is connecting to the database.

However, how do I use the input from my AutoComplete text input within the Run SQL Query parameter statement? Do I use the Get Workflow Inputs activity to send the text input string to the Run SQL Query activity within the server workflow? That doesn't seem to work. Or do I just use Get Form Event Data > Text Replace (as pictured) and then use the output from Text Replace in the Run SQL Query?

There are no examples of how to do this in the documentation, so I'm going through a lot of trial and error (that seems to just result in error)…

Any help or advice would be greatly appreciated. Thank you!

0

Comments

8 comments

Date Votes
  • Nico Burgerhart

    Do you need a WHERE clause with a LIKE operator?

    I think then you will need the Run SQL Query Unsafe activity.

    https://docs.vertigisstudio.com/workflow/latest/help/Default.htm#wf5/help/activities/sql-query-unsafe.htm#Run_SQL_Query_Unsafe_Activity

    There was a sample with a Run SQL Query here https://support.vertigis.com/hc/en-us/community/posts/11498329623954/comments/11498329637522 but it isn't available anymore due to support forum migrations.

    0
  • Ken Lyon
    Edited

    I think the standard Run SQL Query activity should be adequate. The Run SQL Query Unsafe activity is only needed when the query itself needs to be constructed on the fly.

    Imagine I'm searching for a state in the USA (a popular example) and I type “New” into the auto-complete. I might expect the query to be something like:

    SELECT STATE_NAME, POPULATION
    FROM US_STATES
    WHERE STATE_NAME LIKE ‘%New’

    The only part of this that's going to change is the text within the quotes.

    I think it should be possible to have this:

    SELECT STATE_NAME, POPULATION
    FROM US_STATES
    WHERE STATE_NAME LIKE @searchText

    The parameters input could be this:

    = {
      “searchText”: “%New”
    }

    To make it dynamic, you could do this:

    = {
      “searchText”: `%${$formEventData1.argument}`
    }

    You probably won't need the Text Replace activity as the data provider usually escapes the contents of the parameters. That's one of the things that makes it safe to use parameters.

    0
  • Jesse Langdon
    Edited

    I greatly appreciate the feedback! Here is my SELECT statement in the Command Text of the Run SQL Query activity:

    SELECT PermitNumber = (FolderYear + FolderSequence + FolderType) FROM Folder WHERE FolderYear = SubString(@PermitNum, 1, 2) AND FolderSequence LIKE SubString(@PermitNum, 3, Len(@PermitNum)) + '%' GROUP BY (FolderYear + FolderSequence + FolderType) ORDER BY (FolderYear + FolderSequence + FolderType);

    And here are the Parameters:

    ={"PermitNum" : '%${$formEventData1.argument}'}      (as per Ken Lyon's suggestion)

    This is the client workflow:

    And this is the server workflow that's called:

    Running this in or Sandbox unfortunately results in a 400 (Bad Request) error in the console when attempting to execute the Run Workflow activity:

    I'm sending $formEventData1.argument as the argument for the Run Workflow Activity, which is the output of the Get Form Event Data activity, so maybe there's something not formatted correctly there?

    0
  • Jesse Langdon

    After reading through the Run Workflow activity documentation, I added the Get Workflow Inputs activity to the server workflow, before Run SQL Query activity:


    After making this change, I still receive a 400 (Bad Request) error when calling the RunWorkflow1 activity. I dug a little deeper into the error, and here's what I'm seeing in the browser's Network Response:

    {							
 "errors": {
   "inputs": [
     "Error converting value \"15100105\" to type 'System.Collections.Generic.IDictionary`2[System.String,System.Object]'. Path 'inputs', line 1, position 73."
   ]
 },
 "type": "https://tools.ietf.org/html/rfc9110#section-15.5.1",
 "title": "One or more validation errors occurred.",
 "status": 400,
 "traceId": "00-2ab598e51a8c007710b397f3d44e57bf-613e18391f01dfe0-00"
}

    So I must have either the Run SQL Query activity misconfigured somehow ( Parameters are set to: ={"PermitNum": `${$getWorkflowInputs1.inputs}%`} ), or there's something wrong with the argument I'm sending to the Run Workflow activity.

    Anyone have any ideas?

    0
  • Ken Lyon
    Edited

    First of all, I confused matters with the reference to the form element in the parameters. I had forgotten that it's a server only activity, so the value needs to be passed in to the inputs of the server workflow. Sorry about that. I see from subsequent comments that you solved that problem, though.

    I see two things that don't quite look right.

    1. If you want one of the inputs from the workflow, you would refer to it like this:
      $getWorkflowInputs1.inputs["myInputName"]
      Where “myInputName” is the name you gave the input in the Arguments input of the Run Workflow activity in the Client Workflow.
    2. In the Parameters input to the Run SQL Query activity, you will need backticks around the text after the colon.

    Combining these, I'd guess you need something like this for the Parameters input:

    = {
        “PermitNum”: `${$getWorkflowInputs1.inputs["myInputName"}%`
    }


    Again, please note that “myInputName” needs to be replaced with a real value.

    0
  • Nico Burgerhart

    Sorry for confusion with my suggestion for Run SQL Query Unsafe activity.

    0
  • Jesse Langdon

    Thanks for the response.

    So in the Run Workflow activity, for the Argument I tried:

    =$formEventData1.argument (from the Get Form Event Data activity, this is the output “Argument”)

    Then in the server workflow, in the  Run SQL Query activity, for Parameters I used:

    ={"PermitNum": `${$getWorkflowInputs1.inputs["$formEventData1.argument"]}`}

    Nothing really happens in the workflow when run in the Sandbox (although I get no error in the browser Console), so I'm assuming this is incorrect. I think "$formEventData1.argument" is a string literal, versus the value it should represent (i.e. the user-supplied string in the Auto Complete text input).

    There are just nuances to how the javascript syntax works that I'm not familiar with, so apologies for my confusion. I assume I am not formatting the $formEventData1.argument variable correctly in the Parameters… 

    Thanks!

    0
  • Ken Lyon

    The expression =$formEventData1.argument will give you the value from the event, likely the text itself. The Run Workflow activity expects the Arguments input (notice it's plural) to be an object. This could work:

    = {
      “searchText”: $formEventData1.argument
    }

    When retrieving the value from the inputs within the Server Workflow, the name has to match the key you provided in the object, nothing to do with the client workflow's variable you used to populate it.

    To match my example of setting the Arguments input above, use this:

    $getWorkflowInputs1.inputs["searchText"]

    That will then give you the same text that was entered in the text box.

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post