Forum Academy Marketplace Showcase Pricing Features

Not able to define SQL parameter on bubble - SQL query for dynamic search (multiple optional parameters)

Hi,
I have a MySQL database and I am not able to define different parameter values in my SQL query on bubble. Because of this I am not able to write a dynamic search query in SQL that will allow users to search on my website. Bubble only accepts ? as the parameter value in the query.
@ParameterName is not accepted as a parameter value in SQL query in bubble. This type of parameter naming is accepted everywhere else except bubble. Is it just me or this is the way bubble works?
If it’s the normal way of processing SQL queries in Bubble can someone please tell me how do I write a dynamic search query by using only ? as a parameter value for all parameters?

Thanks a lot.

Below given query format is the only query format that works in Bubble:

@NigelG @mishav @emmanuel
Could you please help me solve this error? I would really appreciate some help on this. My project is stuck because bubble doesn’t read parameters that are named starting @parametername or $parametername. Is there any other definitive way of naming a parameter other than by ?

Thank you for your help.

I think that this is DBMS related and that query string is sent to the server as it is. In query string, parameters can be ? (in this case the order is important), $n (wnere n is 1, 2 …) or @paramname. The last format works with MS SQL Server.

You should provide test values for the parameters, which are used when you initialize the query. Maybe this is the reason that nothing works in your setup as in picture.

Just to be exact, PostgreSQL uses $n, MySQL uses ? and SQLServer uses @param.

4 Likes

Thanks a lot @eftomi !
This really helps a lot. Thank you!

1 Like

How do I use dynamic values in the SQL database connector plugin?

If I explicitly don’t write “LIMIT 200”, I get a popup dialog asking me to limit to 200.

If I explicitly write “LIMIT 200”, I get "Query issue syntax error at or near “$1"”.

I am so confused as to how this works so any help would be great :slight_smile: (I am using postgres if that matters)

@gbannister I’m pretty sure that column and table names can’t be replaced by bound parameters. What effect do you want to achieve?

I would like to know what exactly to what extent the dynamic parameters can be used. I first assumed that it can make the entire query a dynamic value, which would be amazing, but now I see that it is very limited. Now I would like to know exactly the limits and how they should be used

Try Postgres documentation. This limitation is pretty standard across SQL interfaces for queries and parameter binding, its not a Bubble-specific limitation.

What I meant was that I was wondering if the dynamic parameters are ways for me to dynamically add in queries to pass to the Bubble SQL plugin.

But are you saying the query parameters are SQL dynamic parameters?

The way Bubble has implemented it is that a query is predefined, and parameters (dynamic or static) are bound to the query by placeholders in the query. This setup is pretty safe from SQL injection attacks, but it doesn’t give us flexibility in programmatically constructing queries.

If you need more flexibility, perhaps look into the options you do have, such as conditional values, views, joins, and you can even have different queries setup inside a stored procedure.

@mishav could you please help to figure out the following:

I’m trying to write a query to PostgreSQL and create a table from bubble workflow, and use the table name as for example as bubble’s user name(or ID). But as I understand you can’t do it that way. I google and postgre has a trick to do that, however it feels like bubble doesn’t support
plpgsql syntax.

Here is what I found:

they use the format() function. But the thing is that I can’t pass parameter from bubble ($1… $n) into query. I’m keep getting an error:

SQL Database Connector issue: bind message supplies 1 parameters, but prepared statement "create_db" requires 0

Maybe you can help us to crack this up?