SQL Connector bubble - how call insert into querries inside workflow

Need help Database
1

Hi

I try using sql connector database

When i insert data to mysql and want get his new id ( and be sure to other user not add new other record and i get wrong number). I wanted use for this mysql transaction like below, who work in my mysql database

START TRANSACTION;
INSERT INTO lightlog_gmp.Przeplywy
(Id, CzekaNaReakcje, DataWniosku, Uwagi, StatusPrzeplywu_Id, Proces_Id, OdKogo_Id, DoKogo_Id)
VALUES
(null, ?, ?, ?, ?, ?, ?, ?);
SELECT LAST_INSERT_ID();
COMMIT;

but i received error like this

SQL Database Connector issue: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO lightlog_gmp.Przeplywy
(Id, CzekaNaReakcje, DataWniosku, Uwagi, ’ at line 2

Do you have any idea for other solutions for this problem?

screens

mysql_baza
mysql_baza1159×505 78.8 KB

transakcje_string
transakcje_string1537×678 56.6 KB

db_connector_error
db_connector_error877×466 24.3 KB

2

Are you sure that’s correct syntax for MySQL? Also, I’m pretty sure you can only do one transaction, but you have multiple. Try a single one and see what happens.

3

Thanks for answer

Are you sure that’s correct syntax for MySQL?

Yeah you have right, I edit my post and add screens.
and I test this at mysql workbench and … work - i added screen

Also, I’m pretty sure you can only do one transaction, but you have multiple.

I am not sure - what do you think when you write “multiply transaction?” I want be sure other user cannot add next record and return me wrong number

4

So here is one of my smallest queries, but it shows you how to use the variables. First, I don’t see you using the variables. Secondly it’s telling you that it doesn’t like the second line (probably) because you have the ; which ends your query. It might not have been built for something like this, I am not sure?

image
image815×270 8.34 KB

As a alternate way of doing what you want, create another column in your database and insert a unique value for each transaction, then in a second transaction…

select id in xyz
where uniquevalue=abc123;

5

When I use $1, $2 etc.

db_error_dolar_liczba
db_error_dolar_liczba898×364 11.2 KB

which is not existed when I use sign ? - i don’t know why, but first ‘?’ is recognise as first parameter, second ‘?’ is second parameter- i checked that only at mysql. Maybe this is only except only for mysql…

https://www.youtube.com/watch?v=iN34KFben7Q 13:13 portugal guy from this yt film show how he parsed argument in query for mysql in bubble sql connector

6

Send a pic. And yes you might need the ? For MySQL, not sure exactly.

7

I explained this here: SQL Connector SQL Server Parameter Format - #2 by ben13

1 Like
8

thanks a lot :wink: , so are you using mysql as your database? How you convert tinyint (boolean at mysql) to bubble yes/no? I try a few times, and every time bubble cannot understand change 1 to yes and 0 to no

9

How about you keep it the same and just build logic around 0 and 1

10

im having the same problem

Screen Shot 2023-07-16 at 6.46.03 PM
Screen Shot 2023-07-16 at 6.46.03 PM1440×900 159 KB

Screen Shot 2023-07-16 at 6.45.50 PM
Screen Shot 2023-07-16 at 6.45.50 PM1440×898 138 KB

what is the correct syntax

11

I’m not entirely sure, but I don’t think the SQL Connector supports a block of statements. I think it might only work for one select, insert, update, etc. at a time. One idea is to convert your code into a MySQL Stored Procedure and then call the Stored Procedure from Bubble passing it the input parameters. I call MySQL Stored Procedures from my app and I know it works. Also, if you want the LAST_INSERT_ID to be available in your Bubble app you’d need to put that in a separate SQL Connector query and call it in the next workflow activity.

Also, confirming you use “?” for MySQL input parameters.

Joey