adding rows to multiple tables in Apiexpress

[maithili]

Hi,
I have a requirement where we need to add rows to multiple tables.
Say 1 row to Table A and 1 row to table B. The row in table B is dependent on the row table A - foreign key relation.

I have created the flow in the API express ( PUT) - to add row to Table A - take the data(id) added to Table A and add row to table B,
but getting this error "Cannot add or update a child row: a foreign key constraint fails "

Facing 2 problems.

1. The row in Table A is getting added.. but probably not getting committed by the time row in Table B is getting added. So the insert to Table B fails.
2. Also another thing is that after the error, if you see the tables - Table A has the row added and Table B is empty.. so the whole transaction is not getting rolled back on an error.

Is there concept of transactions and commit?
How can i resolve this in api express.

Thanks a lot
maithili

[Serhii Kulibaba]

Hello,

Do you use a custom SQL query here? How do you put these values into the database?

[Thomasmup]

It Would be nice to have ...copying rows from the grid and pasting to other application. Currently we can copy single row but if we can copy multiple row it would be nice.

Thanks

Rasi

[Galyna Abramovych]

Dear Rasi,

Have you considered our private plugins feature: https://docs.appery.io/docs/private-plugins? This might be something you are looking for.

[maithili]

Hi Serhii,

I am using Custom query.
I use the one SQL component to Insert values in Table A.
I take User_ID generated from Table A and use it to insert values to Table B using another SQL component- user_id is foreign key in table B

1. But the 2nd insert give error of Foregin key error.. That User Id does not exist .. maybe because the Table A insert is not committed.
2. Also noticed is that the after giving this error.. Table A has row inserted.. So ROLLBACK does not take place on error..

Not sure how to resolve this error.
Also one more question

In the API express, the output of one SQL component is available only in the ONLY the next SQL component.. not in the components after that...
For eg if it am Creating a user in user table - with output as user_Id in SQLComponent1
This user id can be used in INSERTS for only the next SQLComponent2.. any further SQLcomponentN .. The BODY does not retain the value in subsequent SQL Components. cannot access this value.. and have to do run a SELECT query each time afterwards.
---- Can we make a variable in API Express?? where we can store such values for further use.

[Oleg Solodiuk]

Hello Maithili,

Unfortunately, we don't have support for the transaction mechanism.