Pass a text parameter to a database query

Queries and Resources
1

I have a workflow that executes a select query to my retool database. I need to use a text parameter, that has been passed to the workflow via a webhook.

The parameter is a javascript that gives me the following result:
{"data":"'UFDWEB%'"}

However if I use the parameter in my query I get an empty result.
SELECT * FROM tablename WHERE fieldname LIKE {{ project_name.data}}

If I use the parameter directly in the query it works.
SELECT * FROM tablename WHERE fieldname LIKE 'UFDWEB%'

Any idea what I'm missing here?

2

If you log project_name.data is it just a string? Or is it an array with a single element or something like that?

Without knowing the entire flow, is there a need to do this asynchronously and await the result of project_name.data?

3

project_name.data is a string and I also have the problem when I click through the workflow step by step.

The evaluated string of my query seems perfectly fine:
"SELECT * FROM jira_ticket_cache
WHERE
jira_ticket_key LIKE 'FFBMIP%'"

If I copy this exact string to the query component I receive results.
Other JS variables in my query I left out for simplicity reasons where interpreted just fine.

That's really confusing

4

Hi @Barbara_Theiss, happy to help! :slightly_smiling_face:
From looking at the response object from the JS query:{"data":"'UFDWEB%'"}
It looks like the value looks has extra quotation marks: "'UFDWEB%'"
Are we getting the value for that query from an input field where we may be adding quotation marks? If that is not the case, we could parse that string at the end of your JS query before returning the object:

let originalString = resultFromYourJS; // ("'UFDWEB%'")
let parsedString = originalString.replace(/^'|'$/g, ''); 
return {"data": parsedString}

Let us know how it goes!

5

Hi @Paulo ,

no, I think the string seems right. I just add the single quotation marks for my sql query. If I click in the editor the Query String looks good but still gives my zero results.

screen

If I copy this exact string into the query field it gives my results. So I really don't understand what I am missing here.

6

Although it may all look good when clicking the editor and looking at the Query String, the formatting of the data may have an impact in how the IDE interprets our query at runtime.

For example, when we format the JSON like: {"data":"'Carlos%'"}
The Query String looks great:

But the output is an empty array.

However, when we copy paste the Query String, we get a result:

Note: This is what you may be experiencing.


In contrast, when I format the data like: {"data":"Carlos%"}
​

I do get the expected results when the query runs:


Although the Query String now shows the following query:


That obviously fails when I copy & paste it, due to the syntax error:


This may not be the root cause of your issue but it's worth exploring.

Could you share the JS from your "project_name" reference?

1 Like
7

Hi, thanks a lot for the step by step guiding! I removed the single quotes from my JS variable and although there is no visual difference it's working now!

1 Like
8

You are welcome! Happy to hear it's working now. :slightly_smiling_face: