Forum Academy Marketplace Showcase Pricing Features

SQL Plugin - Offset Error

I am trying to create a way to read more than 200 records from a MySQL db. However the plugin throws an error unless I include a LIMIT statement like: “LIMIT 200”.
In MySQL you can combine an offset into limit like so: “LIMIT 0, 200” however I get an error when I write this as mentioned above.
You can also use OFFSET in MySQL - however it seems that when I use a variable it is being entered into the SQL statement as text - even though I have specified the variable is a number. As a result MySQL throws an error. So this works: SELECT * FROM table_name WHERE owner_id = 11 LIMIT 100 OFFSET 0 but this doesn’t: SELECT * FROM table_name WHERE owner_id = ? LIMIT 100 OFFSET ?where the two parameters are defined.

Any ideas about how to solve this?

1 Like

Hi there,

did you find a solution or workaround ?

1 Like

Hey Arnold! Were you able to dynamically set up offset?

1 Like

No I never did. I ended up building my own API for the data and hosting it on another server to act as an interface.

Hi Arnold
IS your plugin available for purchase or use at all to overcome this?

Sorry for the delay. I just saw this. No it is not a plugin. I just built an API on my external server to run the database queries - Select, Inset, Update etc and then used the Bubble API connector to query the API.

Hi, I had this same issue. Seems to be in some versions of MySQL and MariaDB. Solution I found was to create a stored procedure on your mysql server like this:

    CREATE DEFINER=`bubble`@`%` PROCEDURE `offsetstoredproc`(IN `myoffset` INT)
        COMMENT 'Executes the statement'
      SET @myoffset=myoffset;
      SET @sqlv=concat('SELECT * FROM `table` ORDER BY `table`.`id` DESC limit 100 offset ', CAST(@myoffset AS SIGNED));
      PREPARE stmt FROM @sqlv;
      EXECUTE stmt;

and then from bubble sql connector use an sql command like
call offsetstoredproc(?)
and then include your offset as a number parameter.

Before someone jumps down my throat, this might be at some risk of an sql injection attack… I’m not sure if they can inject anything other than a number however. So I guess it should be ok?

By the way one issue I have using this method is that I get a weird empty row at the end of the sql result, so a 100 row request includes a row #101 with nothing in it. This doesn’t show up when I run the same stored procedure outside of bubble. Some weird quirk of how bubble is processing the result of the stored procedure I guess.

Hello @hmguy ,

Could you show me how you did it?

I’m trying but I’m not succeeding…