Transactions or stored procedures or custom SQL

Wappler General How To
#1

Hi,

My use case is as follows: I want to update a main table and multiple linked sub tables (which may have multiple inserts each) in MySQL.

The way I would logically think to do this is to write a custom transaction SQL insert query, with a repeat loop based on the number of items there are in each of the form repeats, so that there is no risk of one of the inserts not happening correctly.

For example, if I had 1 main form with 1 sub table containing 2 items within a form repeat, I would write the custom transaction as follows:
at the start of the API I would start the custom SQL as follows:
"START TRANSACTION

INSERT INTO main_form_table main_form_id VALUES({{POST_main_form_id}});"

Then using a loop of the form repeat, it would then continue the transaction to insert those 2 sub items into the sub table, then commit the transaction outside of the loop of the form repeat.

Sadly, this is not possible using the custom SQL logic. I also can’t think of a way to make this work by calling stored procedures, as there are multiple form repeats stored within the main form. If I called the stored procedures individually, it would still execute individually instead of in a transaction as a whole as intended.

Is there a way to get around this limitation??? Or am I missing something? Would love some help here - driving me up the wall and I can’t find another forum post that actually solves this issue.

#2

I would first have a look at Wapplers built-in ability to update sub-tables: Using App Connect Form Repeater with Update Record Forms

For many use cases, you can insert/update parent child tables in a single action.

1 Like
#3

Thanks @mebeingken, there’s no way to make that custom is there? So I would be stuck with the standard Wappler sql database query, and not able to write anything custom/dynamic in the query?

Is there any way to write something completely custom with nested queries as a transaction or stored procedure? Even if there was a workaround such as storing the custom sql code itself in a variable or something like that, and adding to it incrementally until it’s ready to go, then just executing the variable value into sql. No way I’m aware of though…

#4

It’s possible to call a stored procedure from a server connect. The syntax can be executed as a custom query. Parameters can be passed like any other custom query.

1 Like
#5

Thanks @umadmin, the issue here is that if I call multiple stored procedures (because I have form repeats of nested tables) they would all execute separately and not as a transaction, which is what I want. Not sure if I could maybe write to temp tables and then at the end commit that, but I’m worried of the connection changing and the temp table automatically being dropped. Think a transaction is a lot safer, which I can’t figure out how to do all at once using a stored procedure in my current case where I’ll be inserting multiple sets of data from form repeats.

#7

Anything I am missing here to solve this problem? Or should I raise a feature request?

1 Like
#8

After much google searching I have discovered the existence of custom modules… OMG the possibilities are endless now lol, will try to write my own module to make this work!

1 Like
#9

You can achieve it by using custom query option with START TRANSACTION.
and place the multiple queries in Try and COMMIT IN TRY itself again with Custom Query.

IN CATCH again use custom query to ROLLBACK!

Transaction|690x388

1 Like
#10

Hi @aidev.vishal! You put the “Start Transaction” on the end of try? This is right? Thanks for repply!

#11

My experience with “start transaction”:

#12

Where? Please??

#13

Assuming you are talking of custom modules you can start here @AlexandreMoreira

1 Like
#14

@aidev.vishal, i try do the same, but in MS SQL it is not possible! You know if this works in MS SQL?