JS Object in SQL Statement

[loud maple]

Hi, I have a question about using JS Object in SQL statement

My JS Object Returns '617283','617296'

when I want to use it in SQL Statement by passing it into

<WHERE IN (-paste here-)>

it works, If I call that function like

<WHERE IN ({{JSObj.myfunc()}})>

it does not work even if I saw in logs as parameters '617283','617296' with STRING value

[fervent parcel]

Hi @loud maple. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

It seems like you're trying to use the result of a JS Object function in an SQL WHERE IN clause. Based on the information provided, you can try the following approach:

1. Create a JS Object with a function that returns the desired values as a string. For example:

// JS Object: JSObj
{
  myfunc: () => {
    return "'617283','617296'";
  }
}

2. In your SQL query, use the {{ }} notation to call the JS Object function:

SELECT * FROM your_table WHERE column_name IN ({{ JSObj.myfunc() }});

However, if this approach doesn't work, it might be due to limitations in the way Appsmith handles the interpolation of JS Object functions in SQL queries. In that case, you can try an alternative approach by using the params argument to pass the values into your query. Here's an example:

1. Modify your SQL query to use {{ this.params.values }}:

SELECT * FROM your_table WHERE column_name IN ({{ this.params.values }});

2. Call the query using the run() function and pass the values as a parameter:

YourQueryName.run({ values: JSObj.myfunc() });

Remember to replace YourQueryName with the actual name of your query.

For more information on passing parameters to queries, you can refer to this documentation (https://docs.appsmith.com/reference/appsmith-framework/query-object#pass-parameters-to-run).

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@loud maple kapa.ai is still learning and improving, please let us how it did by reacting below

[west tiger]

Hey There,

We are happy to announce that we have added @kapa.ai chat assistant to our
community to provide you with quick and immediate assistance. If @kapa.ai is
unable to provide you with a satisfactory answer, our team will respond to your
query as soon as possible.

In the meantime you can ask more questions like - @kapa.ai how can I self-host appsmith on docker?

Thank you for being a part of our community.

Best regards,
Pranav

[west tiger]

Can you send a screenshot of the output of that function please?

[south raft]

I also tried using select

await LoadData.run({table: "contracts"})

and on the SQL Datasource I put this

SELECT * FROM {{this.params.table}} ORDER BY id;

[south raft]

<@&725604214666952714> Please help at your convenience

[west tiger]

Please turn OFF Use Prepared Statement and try to run the query again.

[south raft]

This works! thanks