while the Retool Forum has already helped me solve many problems I am currently encountering a problem to which I could not yet find an answer and hope someone can help me.
I am currently developing an app which allows users to select a record from an existing SQL Table. The user has then the option of making changes to the selected record by changing alternative values for certain columns in retool table components. In the end the user should be able to save the changes back to the SQL table when clicking save.
The problem I am encountering is when writing the changed values back to SQL (Azure SQL Server). I wrote my own query to update the record which uses for nearly every value of the Update statement a value from a retool component.
The state of the SQL-Query component shows under query the correct SQL statement which when executed in SQL Server Management Studio or Visual Studio also correctly updates the table in SQL. When the same query is executed in retool the success event handler is triggered and the log shows that the query has successfully executed but no value is updated in the table (there is > 1 value which is different between the table in SQL and the query shown so the Update should run on SQL).
Trying to troubleshoot the issue I changed the query to a much shortened select statement which still executes correctly in SQL Server Management Studio or Visual Studio but in retool it shows as successfully executed and triggering the success event handler while returning 0 rows while it should return > 1.000 rows.
Here the query I tested with (I changed the names but the logic remains the same):
SELECT * FROM dbo.Table_1
WHERE ATTRIBUTE1 = '{{ Select_Record_To_Change_Table.selectedRow.ATTRIBUTE1 ? Select_Record_To_Change_Table.selectedRow.ATTRIBUTE1 : 'NULL' }}'
The state shows the query as:
"SELECT * FROM dbo.Table_1 WHERE ATTRIBUTE1 = 'Test Value'"
I am not really sure why retool is getting a different result for the same query but maybe someone here has a good idea.
It seems like the issue you're facing is related to how Retool is executing the query and how it's handling the returned result set.
Here are a few things you can try to troubleshoot the issue:
Make sure the Select_Record_To_Change_Table component is properly configured and the selectedRow property contains the expected values. You can add a Text component to display the value of {{ Select_Record_To_Change_Table.selectedRow.ATTRIBUTE1 }} to verify it.
Instead of using the ternary operator in the WHERE clause, try using the COALESCE function in SQL to handle null values. Modify your query to:
SELECT * FROM dbo.Table_1
WHERE ATTRIBUTE1 = COALESCE('{{ Select_Record_To_Change_Table.selectedRow.ATTRIBUTE1 }}', 'NULL')
This will ensure that if the ATTRIBUTE1 value is null, it will be replaced with the string 'NULL'.
If the query is still not returning the expected results, try executing the query directly in the SQL Query component without using any Retool variables. This will help determine if the issue is related to the variable substitution or the query itself.
Check the network tab in your browser's developer tools to see the actual request being sent to the server and the response received. This can provide insights into what data is being sent and received by Retool.
If the issue persists, try creating a simplified version of your app with just the SQL Query component and the necessary components to test the query. This will help isolate the problem and determine if it's specific to your current app setup.
If none of the above steps resolve the issue, I recommend reaching out to Retool support with a detailed description of your app setup, the query you're using, and the expected vs. actual behavior. They should be able to provide further assistance in troubleshooting the issue.
I went through the different troubleshooting steps you have mentioned but was not able to resolve the issue:
The preview of the retool variable when hovering over it in the query component shows the selected value already. Also, the query component under state shows that it is correctly being replaced. I used a text component to test it again and the text component shows the value selected. So I would assume this is not the cause of the issue.
Using COALESCE instead of the ternary operator made unfortunately no difference and the query still returns an empty result.
I copied the query directly from the state of the query component under 'query' and when executing it now without any retool variables it returned the expected results.
I checked the Chrome network tab and I saw that a request is being sent. Interesting enough it shows the correct ATTRIBUTE1 value in the payload tab under userParams. Unfortunately, it does not show the query itself under payload. Also, it shows that the server returned 0 rows. But as the hard coded query returns all results in retool the connection should be fine.
As the app is indeed fairly complex I have deleted all components apart from the table itself, the query for the table and the query with the very simple select statement for testing. Again no errors but still no results when using the placeholder.
As retool seems to correctly read the parameter (as otherwise it could not be shown under state - query) and Chrome also shows it as a user parameter it must somehow get lost in the request itself.
As retool does not seem to provide technical support anymore by email I hope a retool team member can help me in the forum then
