Problems with offsets in SQL plugin

I have a MySQL database that has a lot of data in it and my client would like to be able to see more than 200 values. My idea was to use OFFSET in MySQL to dynamically show a different set of 200 rows. I am however having trouble getting the OFFSET to work in bubble, even when it is static.

Right now I have this:

SELECT *, 6371 * 2 * ASIN(SQRT(
POWER(SIN((LAT - abs(?)) * pi()/180 / 2),
2) + COS(LAT * pi()/180 ) * COS(abs(?) *
pi()/180) * POWER(SIN((LNG - ?) *
pi()/180 / 2), 2) )) AS distance
FROM fullData
WHERE COMPANY = ? AND License_Line LIKE ?
ORDER BY distance
LIMIT 200
OFFSET ?;

When I take out the line with OFFSET, this works perfectly. But if I do it this way, bubble tells me there is a MySQL error despite other MySQL Syntax checkers showing that it is functional. I get the same error when I use a static value and I get the “please limit to 200” error when I try to use the “Limit ?, 200” syntax. If anyone has any advice, thoughts, or questions, please let me know. Thank you in advance for any help.

I am hoping this brings the topic back up. I sent a bug request and was told it isn’t a bubble side error and that they can’t help me. If anyone has any thoughts on this I could really use some help.

1 Like

Hi - posted a stored mysql procedure solution to this issue here:
http://forum.bubble.io/t/sql-plugin-offset-error/24304/7?u=hmguy

Another way to get chunks of 200 rows at a time is to add a “> primary-key” condition to the WHERE clause. This works well if you have a primary key set to auto-increment. If you don’t have that in your MySql DB, it would be simple to add. So for Ben’s SQL above, let’s assume an auto-incrementing primary key called “idFullData”. The SQL connector code would then look like this (only change is to the WHERE clause):

SELECT *, 6371 * 2 * ASIN(SQRT(
POWER(SIN((LAT - abs(?)) * pi()/180 / 2),
2) + COS(LAT * pi()/180 ) * COS(abs(?) *
pi()/180) * POWER(SIN((LNG - ?) *
pi()/180 / 2), 2) )) AS distance
FROM fullData
WHERE idFullData > ? AND COMPANY = ? AND License_Line LIKE ?
ORDER BY distance
LIMIT 200
OFFSET ?;

Each time you want to get another 200, you use the same connector query and pass the last idFullData (just sort the list in Bubble desc on idFullData and grab the 1st value) as a parameter.