Test Value with MySQL Query not working

Need help Database
1

Hi Bubble Forum,

I am playing around with Bubble for a couple of days now to realize an idea I have, but now I have a problem that someone hopefully can help me with.

For the project I connected an external MySQL database using the SQL Database Connector. Simple querys are going great, as long as I have all the information hardcoded into the query.
When I want to use parameters and give them a test value, the value is not correctly placed into the query.

Since I use MySQL, I use ? as placeholder for the parameters. So that shouldn’t be the problem.

Is this a bug or am I doing something wrong here?

Best,
Dennis

2

I agree with above, but it does matter where in the query you put the placeholder, and syntax matters too. For example WHERE MYCOLUMN = '?' is wrong because the parameter binding doesn’t need the single quotes. Nor does the value being put in.

The SQL Connector sucks has limitations, consider ditching it in favour of the API Connector, and using a REST API to access the DB.

1 Like
3

Yes, i’m aware of this. When I provoke an error I see that the way I did it was correct.

But thank you for your input. I will give the API Connector a try.
I hope it is as easy to connect to a MySQL database as with the SQL Connector.

4

To be clear, MySQL doesn’t install by default with a REST API, you may need an additional service, for which there are a bunch of competing products.

What sort of help are you asking for?

1 Like
5

I wanted to know, if there is a known bug with the SQL Connector in combination with MySQL databases and using test values. Or if I do something wrong.

I attach a screenshot to make sure, I don’t make a mistake.

SQL
SQL795×337 20.9 KB

With the first query I receive data from the DB and can use it.
With the second one I don’t receive any data.

Another idea I had was switching to a PostgresSQL DB. Would that make any difference?

6

Your screenshot looks okay. Some things to try:

  • Recreate the query from scratch (instead of editing an existing one, they can get invisibly corrupted)
  • Double quotes or square brackets instead of backticks (probably wont help)
  • Text parameter (with a varchar column)
  • Ensure no newlines in the query (your examples look ok)
  • Ensure no spaces in the parameter value
7

I already tried all of that and now tried it again.
Does not work.

Here one example I tried:

Bildschirmfoto 2023-09-24 um 10.03.09
Bildschirmfoto 2023-09-24 um 10.03.091554×268 25.7 KB

And what absolutely confuses me is the error message in which it the test value is inserted correctly.

Bildschirmfoto 2023-09-24 um 10.03.21
Bildschirmfoto 2023-09-24 um 10.03.21988×522 39.6 KB

Could switching to a PostgresSQL DB a difference?
Otherwise I need to try the API Connector instead.

8

Well that is interesting, it is treating the test value as a text. Maybe it has too many digits for the default number type in the SQL plugin?

What happens if you change the parameter to text and have a query (with backticks)

SELECT * FROM `Ads KPIs` WHERE CAST(`Account ID` AS CHAR(20)) = ? LIMIT 200

Edit - the maximum number of a signed int in MySQL is 2147483647, MEDIUMINT max is 8388607. Try some different number sizes …

What is the data type of the column set to?

9

In this exact error screenshot I tried the test value as text instead of number. Maybe because of this?

In the DB the value is saved as INT.

The same issue with using the test value also happens when using a date. So I don’t think it has something to do with the format or size of the value.

But I am on the road and will test char later.

10

I tried that. Not working :-/

I think I will try a PostgresSQL DB next and when that is not working either, I will try the API Connector. But therefore I have to create an API for the DB first and that is quite a challenge.

Thank you @mishav for your help so far.

1 Like
11

I ran into the same issue using a PostgresSQL database.
Also when I leave the test value empty I get the following error.

Bildschirmfoto 2023-09-24 um 20.42.56
Bildschirmfoto 2023-09-24 um 20.42.56976×396 17.7 KB

I have no idea what else to try. Maybe uninstall and reinstall the plugin.

Does anybody know a service that helps creating an API for a MySQL database? I was not able to find something like that.

12

AWS data api with aurora serverless mysql database if you like AWS
Azure data api builder mysql if you like Microsoft
Oracle mysql rest service if … you get the pattern
IBM’s rest api on DB2 not exactly mysql
Google probably do one too
pipedream mysql api
dreamfactory
mysql router idk about the hosting

1 Like
13

Great, thank you @mishav!

1 Like
15

This looks to me like a parsing issue with the space in the column name. Have to tell you, I’ve put the SQL Connector through the ringer with all sorts of queries and have always found a way to get it to do what I need. Would it be possible to rename your table to AdsKPIs and your column to AccountID? I bet if you did that and removed the quotes, it would work fine:

select * from AdsKPIs where AccountID = ? limit 200

Joey

17

Hey @joeyg

thank you for your reply. Sadly I can not change the column names. I receive the data from an external source and for automation purposes I have to use the same wording.

I found a way I can work with it. I just have to take the values from some source on my page.

Works for me.