Simultaneous record creation (within same transaction)

[stephan]

Hello everybody,

I'm new to Servoy and I'd like to acheive the following functionality:

In a form I want to put fields from multiple tables and a button that says "Create".

I want the user to fill the form. When he's done, he clicks on the "Create" button and <b><u>then and only then</u></b> the rows are created into the different tables (all within the same sql transaction).

I'm looking for an all-or-nothing behavior so that if the transaction fails the database is kept clean (ie: not the case if some tables have a record added and others tables don't).

Hope that it can be done without too much hassle.

[Harjo]

Yes that is possible, use on the form to fill in, only global-fields

than make a method that creates a new record, fill every field with the value of the global-fields and you are done!

Hope this helps

[patrick]

But you can also use transactions that you can roll back if the user does not hit "Save".

The only question I have there is how that transaction is handled if several tables are involved. But as far as I have seen transactions work, related data is also rolled back. But maybe a statement from Servoy would be helpful on this question.

Patrick

[stephan]

Thanks for the answer.

So the only way to acheive the desired result is to create a method that reads the global fields and then create the sql sentences.

Conceptually this would looks like this:

start transaction

execute sql create sentence 1
execute sql create sentence 2
execute sql create sentence 3

commit transaction (it is rolledback if the transaction is not successfull).


This is not has fast as I wished it where but that's ok.

[Odysseus]

I think if you use transactions it would go along the following lines:

on loadform event
starttransaction
end loadform event

user fills out all the form fields ("creating" new records as needed)

on saveformevent (maybe on a button)
var check = function that verifies if all conditions are met and returns OK or NOTOK
if(check == "OK")
commit
else
rollback
inform user
end if
closeform

should be very quick

HTH

[Jan Blok]

The only question I have there is how that transaction is handled if several tables are involved. But as far as I have seen transactions work, related data is also rolled back. But maybe a statement from Servoy would be helpful on this question.

An transaction encapsulates all changes done by client on any server/table. (meaning it will rollback all and any change in records/foundsets since the transaction is started)