Seems like a dumb question I know, since I know you can use parameters, but for some reason I can’t get this update query to work no matter what I do. What’s weird though is it will work ONCE but then never again:
That’s one variation of it. I’ve tried with the ’ ’ quote marks added or not, but nothing seems to work. The query says it’s successful but nothing updates in SQL server. What’s weird though is as mentioned it will work once. Like sometimes if I edit the query and manually put the record ID to update into it, while leaving the other fields as parameters it will work once, but then try again and never a second time.
I was hoping to create a single SQL action where I can via workflow enter the table name, the field name, and then the value I want it to update to but not having any luck. Should this be possible? Just weird how it says success, but nothing updates in the SQL server, or you make a small change and it works, but then never again. So can’t tell if it’s a bug, or if pressing initialize query only works the first time (where changing parameter test values and pressing re-initialize query just ignores those or what).
Not sure if this will help, but all my paramaters are without ’ ’ and not sure why ID is in Brackets either. You can also try leaving [dbo]. out and see what happens.
Same with field [Competitor_DSL ] . Just a shot in the dark, but it something I would have tried.
Thank you, I tried so many ways, at first it was like you said no brackets etc. Then I tried with them and so on but it’s weird as it works once but not again so more just wanted to post to be sure that this is even possible. Thanks!
The user is db_owner and the admin user I use for everything with SQL so definitely has the permissions. I want to point out also the DB connector works fine, I have it connected and I can do delete and insert queries, but I wanted to try update using parameters so that I could have 1 query that does multiple functions (as right now my delete and insert ones I have 1 query for every table coded into the query).
Then it works, and opens a popup showing all fields from the SQL table where I can pick text, numeric, etc. That’s why I was putting the quotes in my forumas in my OP since it seemed like it needed them.
So it’s really strange, if I actually type in table names, values etc it always works fine but when using parameters it always complains about syntax. That’s why I wasn’t sure if I needed quotes or brackets or something else.
I use the plugin extensively and it works for me. I have however never tried to dynamically find a filed or table with parameters and think this might be your issue. You may need to create a seperate query per table.
Try: SELECT TOP 10 * FROM dbo.Buildings WHERE id = @id
also make sure you are using the table and field names in the same case as they are in the actual dB as that might matter too.
Here is a Gif showing many different queries for the same connection (none of them use a parameter to reference the Table though):
Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.
You cannot use ALTER TABLE statements to modify the design of table-valued parameters.
Bubble stores the last query. To force it to do another query add a false paramater, not referenced in the query iteself, called refresh or any name you’d like.
In your Bubble editor fill the refresh field with current date/time formatted as ISO or any other time parameter that would be different each time you run it. This is purely to trick Bubble and has no relationship with your data.
This way, Bubble is forced to access the DB each time it is run as the refresh “parameter” is different to the last run
And then I set it to be current date / time in ISO but tried other formats as well, it still only shows the first retrieved value and never updates. I’ve refreshed the page but same old.
No I gave up and we went with a different solution as I couldn’t make it work. Pretty sure it is a bug though as it would work for me once, and then never again. I’d literally do a test to have it update a record and it would work as you’d expect, then I change a value to have it change the record back and it wouldn’t ever work again.
It looks like you are putting the SQL results into a text field. Instead, put the results into a repeating group (hidden somewhere on page). Then have the text field point at 's list of SQL_Building_Details: first item’s Competitor_DSL. Each time the user enters a new value into the search Field, have a workflow 1) clear the Repeating Group and then 2) display the Repeating Group.
If that doesn’t work, try forcing the Repeating Group to refresh using the date/time technique but instead of formatting the date, do extract: Unix Timestamp.
Disclaimer: I work with MySQL, not MS SQL, but I’ve gotten good results using the steps above. Hope it works!
No that’s not the issue, I can’t even get the plugin to initialize and allow usage in a workflow, the plugin just always fails or gives syntax errors. You’ll only use one parameter, like @table in the query, with the rest of the values all hard coded. That will work fine. You then tweak it to have @table and also @id as parameters, try it and it fails. You remove the @id parameter, put it back 100% the way it was a moment ago where only @table is a parameter and it fails giving syntax error. Copy that query, create a new one, paste it into the new one, works first time you try it, then syntax error every time after that.
From searching the forums on here it seems like this plugin isn’t really made for MS SQL as almost all the issues people have with it are always relating to MS SQL.
Then use %% (no quotes) as the parameter when you call the API. You can test the syntax by entering %% in the Test value and pressing the “Initialize this query” button.
