Cool. If you have a minute, please mark it as a solution on your other post so others can find it.
Thanks,
Joey
Just a quick update - I have submitted a bug ticket for this issue and the response has been that there seems to be a bug in the plugin. They are now in the process of creating a fix. Once I hear back from them I’ll update this post.
Wow! I love that their support told me there’s nothing wrong with it and the problem is on my end, great to know I wasted how many hours trying to make it work only for them to now say it is actually a bug.
I also faced an issue with the SQL plugin. The solution they provided was that the name of the SQL should not contain any special character like , @ _
and it worked then.
Hey @joeyg
Since bubble allow only 200 entries per request from external database how do you do pagination?
Bubble shows syntax error when I use this
SELECT * FROM table where country=? limit 200 offset ? ;
So, since it send quotes where I put question mark I cannot use offset because it works without quotes.
I also tried to rewrite the query like
SELECT * FROM table where country=? limit ?,200;
and again bubble stops me from using it stating limit 200 needs to be applied.
I couldn’t get it to work using OFFSET. There were syntax issues if I remember correctly. To get around it, I used this workaround:
-
I have a sequential key (date type INT) in every one of my database tables in MySQL, set as primary key, not null, unique, and auto-increment in MySQL. On every insert, MySQL will generate the next sequential value in this column. FWIW, this is a pretty standard DB design practice. In my table called event, the first column is idEvent, which I’ll use in the examples below.
-
In bubble, retrieve the first 200 rows in your select statement using limit 200 and have this in your WHERE clause (combined with any other conditions you might have):
where idEvent > ?
To start with in your workflow, pass the value 0 to the API query. It will then retrieve the first 200 rows starting with the lowest in sequence.
3) Use the Bubble count: function to determine if there’s more rows in the MySQL database for pagination. IF count: = 200, you know there might be more. You can also have a different query using the MySQL count(*) function to get the total # of rows. I’ve done this and it’s not a very expensive query at all.
4) Only display a pagination button if count =200
5) When user clicks the button, kick off a workflow that passes the last idEvent in the repeating group to the API query. It will then get the next 200, starting from the last place.
This works like a charm for me and its very fast. Not sure of your implementation, but hopefully
this can get you where you need to go.
1 Like
Please start a new thread if you’re going to take over this one and change the subject. This one was about parameters in the query, not about the 200 record limit. Not sure why people think it’s acceptable to just take someone else’s post and just make it your own. Some people are just so rude it’s mind blowing.
1 Like
Great tip, thanks a lot!
[quote=“joeyg, post:20, topic:107330”]
i am having the same sintax problem … but did not understand when u say “Then use %% (no quotes) as the parameter when you call the API” … can u help me on that???
@leogandra Can you start a new thread on this specific topic? I’ll reply there. Thanks.
Joey
Hi Joey … yes i have opened a new thread.
tks