Unable to Bind Retool Text Input Component Value to SQL Query

Queries and Resources
1

Description:

I'm building a Retool application and facing challenges while trying to bind the value from a text input component (nameTowerInput) to an SQL query. I intend to update a table (my_towers) with the value entered in the text input.

Steps Taken:

  1. Created a text input component with the ID nameTowerInput.
  2. Attempted to use the value from the text input in an SQL query to update a column in the my_towers table:

sqlCopy code

UPDATE my_towers
SET tower_name = '{{nameTowerInput.value}}'
WHERE id = {{localStorage.towerId}};
  1. Tried using parameterized SQL:

sqlCopy code

UPDATE my_towers
SET tower_name = ?
WHERE id = ?;

Binding the nameTowerInput.value to the first placeholder and localStorage.towerId to the second.

Issues Faced:

  1. Error indicating mismatch in the number of supplied parameters vs. expected: "bind message supplies 1 parameters, but prepared statement "" requires 0"
  2. Error on data type mismatch: "could not determine data type of parameter $1"
  3. When trying to directly select the value for debugging purposes:

sqlCopy code

SELECT '{{nameTowerInput.value}}' AS inputValue;

I received the "bind message" error again.

Additional Information:

  • The value from the text input (nameTowerInput.value) is a string.
  • The localStorage.towerId is an integer.
  • When hardcoding the values in the SQL query, it works as expected. The issue arises only when trying to dynamically bind the text input value.

Questions:

  1. Is there a specific format or method to correctly bind the value from a text input component to an SQL query in Retool?
  2. Are there any known issues or quirks related to SQL parameter binding in Retool that I should be aware of?
  3. How can I ensure that the nameTowerInput.value is correctly interpreted as a string and not causing the data type mismatch?
2

Same as your other post:

UPDATE my_towers
SET tower_name = {{nameTowerInput.value}}
WHERE id = {{localStorage.towerId}};
1 Like
3

Hey Scott, thanks for getting back! Tried that and looks like it ran ok but not updating the row in the table. should be updating row id 17 that is the localStorage value.

Capture
Capture779×584 38.8 KB

should be pushing in "lets try again"

Capture2
Capture2919×237 16.7 KB

4

Hello, your localStorage has the format as below
image

So to get 17 you will have to do localStorage.values.towerId[0]

1 Like
5

Thank Iamh, how do you make that dynamic? that id would change with a new user. Not sure I am asking that right. But if someone else comes into the start of the flow they would have new id. ie, 0 becomes 1 becomes 2, etc...

6

I'm not following completely.

  1. The value changed but is the structure localStorage.values.towerId[0] change? if not, it will send in whatever value in that index 0.
  2. Structure can change, ie. more properties before getting to towerId or towerId can have more than 1 index since it's an array, then the application that puts the value there must store the path to that value so it can retrievable. Unless you just want to get the last item then use towerId.pop() to get the last item

localStorage is client side for the browser...meaning the data stores in the browser so the only way possible for multiple users to have the same localStorage is they're using the same machine/virtual.

7

ok, now i see. i tried it again and that value always goes in 0. that makes sense now. however, it doesnt like that code:

Capture6
Capture61021×371 48.4 KB

8

Capture7

9

sorry, I thought you understand {{ }}. To inject values from other objects/data you must put it in {{ }}

So you need to {{ localStorage.values.towerId[0] }}

1 Like
10

awesome, that got it! thanks all!

1 Like