Insert multiple dynamic rows into external DB

Need help APIs
1

Hello, I am doing an API call to get multiple items and then formatting as text in a format that is MySQL friendly:

  ("(638) 833-5496","enim.curabitur@hotmail.couk","Spain"),
  ("(293) 742-0229","odio.semper@yahoo.net","Belgium"),
  ("1-265-156-4304","tincidunt.dui.augue@outlook.net","Ireland"),
  ("1-833-780-2553","scelerisque.scelerisque@aol.com","France"),
  ("(619) 691-0656","ac.risus.morbi@icloud.org","Costa Rica")

I have a MySQL database connected via the SQL Database Connector. I am having trouble inserting the string of text above into my external DB using the SQL Database Connector. Ideally, I would just run an insert statement on the fly such as

INSERT INTO `myTable` (`phone`,`email`,`country`)
VALUES
  ("(638) 833-5496","enim.curabitur@hotmail.couk","Spain"),
  ("(293) 742-0229","odio.semper@yahoo.net","Belgium"),
  ("1-265-156-4304","tincidunt.dui.augue@outlook.net","Ireland"),
  ("1-833-780-2553","scelerisque.scelerisque@aol.com","France"),
  ("(619) 691-0656","ac.risus.morbi@icloud.org","Costa Rica");

However, when I attempt to do this from the SQL Database Connector, I can’t figure out how to pass my full “values” statement as a parameter. I tried the below with declaring a parameter but it doesn’t like the syntax because it’s expecting “email” and “country” as well.

INSERT INTO `myTable` (`phone`,`email`,`country`)
VALUES
  ?

Any ideas? Is it possible to pass the insert statement a different way?

This will always be static

INSERT INTO `myTable` (`phone`,`email`,`country`)
VALUES

This will always be dynamic

  ("(638) 833-5496","enim.curabitur@hotmail.couk","Spain"),
  ("(293) 742-0229","odio.semper@yahoo.net","Belgium"),
  ("1-265-156-4304","tincidunt.dui.augue@outlook.net","Ireland"),
  ("1-833-780-2553","scelerisque.scelerisque@aol.com","France"),
  ("(619) 691-0656","ac.risus.morbi@icloud.org","Costa Rica");
2

You likely have a parentheses problem. In my app, I put “(” and “)” in the SQL connector like this:
…
Values (?)

Then, the parameter is just 3 strings, separated by a comma.

Step one would be to break it down so you are doing just a single insert. Once that works, you can then figure out how to either 1) Construct the string for multiple inserts (likely with a looping approach within Bubble) or 2) Move forward with the single row Insert statement in the connector and use a looping approach within Bubble that calls it for each row insert.

Hope that’s helpful in some way.

4

Hey Joey, appreciate the response! I ended up taking a different route because I want to avoid looping in Bubble. The API call + DB insertion was using too much capacity.

5

I solved this use case by creating a stored procedure on my external database that uses the EXECUTE command. The EXECUTE command allows you to execute a string of data.

The query in the DB Connector looks like this:
CALL insert_string_storedprocedure (?);

Then the stored procedure complies the insert statement along with the values passed from bubble and creates a valid SQL statement and executes it.

CREATE DEFINER=`admin`@`%` PROCEDURE `insert_string_storedprocedure`(bubble_variable text)
BEGIN

set @insert_string = "INSERT INTO `myTable` 
(`phone`,`email`,`country`)
VALUES
(insert_statement)
AS new
ON DUPLICATE KEY UPDATE
email = new.email;";

set @fullcommand = REPLACE(@insert_string, '(insert_statement)', bubble_variable);

PREPARE stmt FROM @fullcommand;
EXECUTE stmt;

END
6

Nice! Great solution.