Retool Database: Prepared Statements Difference Between Production and Staging?

💬 Queries and Resources
1

I have the following sql statement for production but the it will not work in staging unless I remove the single quotes around timestamp. Trying to figure out how to get a consistent behavior between the two. Here's the statement:

SELECT
*
FROM
messages
WHERE
role IS NOT NULL
AND role != ''
AND timestamp >= '{{ code3.data.startTime }}'
AND timestamp < '{{ code3.data.endTime }}'
ORDER BY
timestamp;

2

Hello there, welcome to the forum!
You can access the timestamp dynamically using retoolContext and a ternary operator:

SELECT
*
FROM
messages
WHERE
role IS NOT NULL
AND role != ''
AND timestamp >= {{ retoolContext.environment == 'production' ? code3.data.startTime.toString() : code3.data.startTime }}
AND timestamp < {{ retoolContext.environment == 'production' ? code3.data.startTime.toString() : code3.data.startTime }}
ORDER BY
timestamp;

EDIT: Clarification: I'm using "toString" to transform the object to a string, and if the environment is not production, the field will remain as it's originally defined.
Hope this helps!

3

@sidetool thanks. I got it to work by turning off prepared statements on the production environment. for some reason staging doesn't have any option for controlling this setting so if you use both and you don't want to use a ternary like you propose then you have to just turn it off in production which is unfortunate from a security point of view.

1 Like