Does anybody have any experience with Bubble SQL Database Connector sending dynamic numerical values in queries as text values?
This is incorrect SQL syntax and so obviously my SQL database returns an error. It means I can’t use the plugin for lots of things, ie OFFSET, LIMIT, DATE/TIME queries, INT columns.
As an example, say in my Bubble plugin query I want to allow users to filter salaries above a certain threshold, and I use this:
select salary from my_table
where salary > ?;
Let’s say the user selects 50,000. Bubble sends the query to my database like this:
select salary from my_table
where salary > ‘50000’;
When it should be sent as:
select salary from my_table
where salary > 50000;
Has anyone found any workarounds or other plugins that allow you to use numbers in dynamic queries?
Have you tried changing the parameter from type “text”? These queries are achievable in the bubble sql connector plugin. See image below (note in this query I am using text as the parameter type):
Ah, you’re right. It does work for INT columns. Unfortunately, my example was incorrect.
My issue is actually with the OFFSET clause. Here the plugin is not working, and this is because Bubble is sending the dynamic number value wrapped in quotes.
Ie. where the dynamic value for offset is 10 in my plugin:
select customers from my_table
limit 10 offset ?
Bubble sends the request like this
select customers from my_table
limit 10 offset ‘10’
This is incorrect SQL syntax.
So the first example I gave can work either with the numerical value in quotes or without quotes, as you point out, but it doesn’t work for the above.
Ahh yes, well that is definitely not going to work (the dynamic offset).
You can read the full thread below for a more thorough explanation. This is one reason we built our SQL plugin (not free). As apparently Bubble doesn’t plan to support this type of functionality.
@wwweb2401 yes I saw that from your post a while back. I was simply linking to your post here for the benefit of @Louis-1832 (as he ran into the same issue that we did).
