I have been trying to set a variable to the same name as one of the fields in my database table (status_attn). The variable is called cell_value. I then try to run a query that is:
SELECT *
FROM shipping
WHERE {{ cell_value.value }} = TRUE
When the query runs, I get an error message "syntax error at or near "$1" in retool". I keep reading the the forum that it has to do with prepared statements and to disable them. I can't find where to do that. Can it only be disabled when setting up a resource connection? If so, do I have to go through and change all my queries?
I just tried to run a page that was working fine and I didn't make any changes to and today I am getting the error "operator does not exist: date = integer". Any idea what would cause that. Here is the query that is returning that error;
SELECT *
FROM shipping
WHERE ({{ !calendarInput1.value }} OR ship_date = {{ calendarInput1.value }})
ORDER BY ship_date ASC, dealer, load_num
Could that have something to do with me turning off the prepared statements?
UPDATE: turning off the prepared statements is what caused it. So I guess I can't use this other query:
SELECT *
FROM shipping
WHERE {{ cell_value.value }} = TRUE
I've seen where you can make a duplicate resource and turn off the prepared statements and use that for things like I want, but it appears that you can't make a duplicate connection to the retool database.
OK, here I go being an idiot again. How do you bring up that settings window in the actual database? I tried the dropdown next to the table name and didn't find anything.
I apologize for being so ignorant of these things.
Hey @MiguelOrtiz, I found how to access the settings, but when I tried to set up a new PostgreSQL resource and I put in the Connection URL for the host name, it told me that the host was not found.
Another approach, if the options for cell_value.value are not too numerous, might be to set up a series of conditions like
WHERE
( field_1 = true
AND cell_value.value = ‘field_1’ )
OR
( field_2 = true
AND cell_value.value = ‘field_2’ )
/*Etc.*/
So effectively the only condition that would evaluate as true would be the one where the cell_value matches the field_name, and those are the records you would get back.
@jg80, forgive my ignorance here, but I guess I'm not understanding. Where does the SELECT come into play? I'm trying to UPDATE the table by having the user select a cell in a table followed by a button. Selecting the cell sets a variable. Clicking the button triggers the UPDATE query. That's when I get the error message "syntax error at or near "$1".
UPDATE shipping SET field_name = TRUE will update every single record in the shipping table. I was assuming that you wanted to update a subset of records, which you would identify with a WHERE condition, like:
UPDATE
some_table
SET
some_field = {{ variable_name.value }}
WHERE
( field_1 = true
AND {{cell_value.value}} = 'field_1')
OR
( field_2 = true
AND {{cell_value.value}} = 'field_2' )
/*Add more conditions if there are more than 2 fields possible*/
The assumption in my proposed approach is that cell_value.value indicates the field that you want to check to be true and (new assumption) that variable_name.value is the variable that is set when the user selects a cell, so correct me if that is not the case. Do you actually meant to set the value of a field = TRUE for all records in the shipping table?
Sorry for the confusion. I meant to display this query:
UPDATE shipping
SET {{ cell_value.value }} = TRUE
WHERE id = {{ table1.selectedRow.id }}
You were correct that the objective was to just change the value of one cell, not all the cells. However, your assumption about there being a variable_name.variable is incorrect. There is only the one variable (cell_value.value) which will contain the name of the field I want to update once the user has clicked the correct cell.
OK - now I get it, and that's trickier...you would need to disable prepared statements, or (again, depending on complexity) you could create an update statement for each field you want to update and trigger the appropriate one in a JS query depending on the cell_value.value.
One other (half-formed) thought would be to create a "Bulk Update/Upsert" query in the GUI and send it an array with a single object made up of only the id and field to be updated in the additional scope. Something like:
I'd have to fiddle around with it to see if it actually worked, but basically the idea is to pass the prepared "record" to the DB in the bulk update/upsert which would take care of the rest.
I was afraid of that. I don't really want to disable prepared statements because it breaks my other queries. Any help you could provide with trying your method would be most appreciated.
