Executing the "view sql statement as string" option doesn't work in MSSQL without some tweaks. looking for advice

Queries and Resources
1
  • Goal:

i have noticed, when i have a query in retool, like this...

select * from
orders
where datestamp between {{dateRange1.value.start}}
and {{dateRange1.value.end}}

retool returns the results

but when i use the arrows within the textarea box to "view sql statement as string" and copy the statement, i get this...

select * from
orders
where datestamp between 2023-12-14
and 2023-12-14

this, of course, does not work within (at least, my version - 12) of mssql unless I add the single quotes around the dates

ie - select * from
orders
where datestamp between '2023-12-14'
and '2023-12-14'

but, when i use this in retool, this blows up...

select * from
orders
where datestamp between '{{dateRange1.value.start}}'
and '{{dateRange1.value.end}}'

I have tried all kinds of thing, but clearly can't get it working.

any suggestions?

2

@josh2e when you write an SQL query with {{ }}, the preview is misleading. The value is not splashed into your query and run. They are actually passed into SQL as parameters

select * from
orders
where datestamp between {{dateRange1.value.start}}
and {{dateRange1.value.end}}

Is not the same as

select * from
orders
where datestamp between 2023-12-14
and 2023-12-14

It is more like

select * from
orders
where datestamp between %1
and %2

and %1, %2 are given to the SQL library in as ('2023-12-14', '2023-12-14')

That's why your 4th query isn't working either, because it puts the quotes '' around the parameters.

Looks like the first query you have is working, I wonder what are you looking to change?

1 Like
3

yes, the first query works within retool, but i guess i'm assuming we should be able to easily use that double arrow icon, along with the quick copy icon to ultimately paste into MSSQL (MMS or the newer azure data studio) to easily execute the query. maybe i'm expecting too much for this to work "outside" retool?

4

@josh2e I see. Sorry, the preview is not meant to be exactly executable SQL because for different data format (e.g. string, number and boolean) the rendered string may be off in a different way. But I think those errors can be corrected easily if it's not something you have to do regularly.

1 Like