saving form data to database as transaction

[pkjoshi]

I am not aware what method fabrik uses to store data into database. What I want is that there are some forms which involves financial transaction and hence is it possible to store the data in the database using mysql transaction.
for example:

start transaction;

-- get latest reciept number
select @receiptNumber := max(receiptNUmber) 
from receipts;

set @receiptNumber = @receiptNumber  + 1;

......

commit;

 

[cheesegrits]

Nope, we don't support "transactions".

-- hugh

 

[rob]

you would need to code that by hand. The Joomla database driver seems to have various functions to help with commits such as transactionStart() , transactionRollback() etc but I have never used them

 

[pkjoshi]

Rob, Could you please elaborate on "code by hand", Do you mean editing the source code of fabrik or alternatively say

I can set the option for the form
"save to database=No"

and then insert the code in php form plugin for the transaction process on form submission.

 

[cheesegrits]

What part of Fabrik are you thinking about?

For instance, is it in regular form processing?

I was a little confused by the example you gave, which seemed to be some unrelated custom code.

The only time transactions would be of benefit in regular form submission would be if they involved joined tables. At the moment, we commit the change to the main table (as a single, atomic INSERT or UPDATE), then we check for any joined rows (repeat groups) which have to be deleted and delete them if there are any, then we insert or update any joined rows, then we update the main table if there are any FK's that need setting (i.e. one-to-one joins, where the FK is on the "parent" pointing to the PK on the child).

As things stand, if any of those steps fails, we don't 'undo' the other steps.

Rob ...

I've used MySQL transactions before, and I really don't think it would be hard to implement. Basically, at the start of processToDb() we would just fire off a transactionStart(), and if there are no database write errors in the main row and joined row handling, at the end we would fire off a transactionCommit(). If anything errors out, we do a transactionRollback().

Most of the work would probably be in adding some more error checking to our database writes, probably by throwing exceptions in either the updateObject()/insertObject() methods, or in storeRow(), and catching them in processToDb().

This wouldn't be a bad idea to do anyway, as with the code as is, it is possible for our tables to get out of whack, if one of those steps fails.

- hugh

 

[richy_u@live.com]

What part of Fabrik are you thinking about?

The only time transactions would be of benefit in regular form submission would be if they involved joined tables. At the moment, we commit the change to the main table (as a single, atomic INSERT or UPDATE), then we check for any joined rows (repeat groups) which have to be deleted and delete them if there are any, then we insert or update any joined rows, then we update the main table if there are any FK's that need setting (i.e. one-to-one joins, where the FK is on the "parent" pointing to the PK on the child).

- hugh

I tried to execute at onBeforeLoad (form) event:

$db =& FabrikWorker::getDBO();
$db->setQuery("START TRANSACTION");
$db->query();

then type some data field and execute submit joomla process (using standard button).

Then I executed at onAfterProcess (form) event:

$db =& FabrikWorker::getDBO();
$db->setQuery("ROLLBACK");
$db->query();

but it doesnt work and the change made by submit joomla process are in database.

Probably in same part of the fabrik submit code COMMIT is execute.

It's possible change this part of the code and remove the COMMIT command ?

This would be really usefull to me cause the modifies that the user made on join-tables during the input-phase should be deleted in certain circustances (as for exemple if user press a custom discarge modify button).

 

[richy_u@live.com]

Can I conclude it's impossible ?

 

[cheesegrits]

Nope, just that I didn't see your post.

It won't work doing it onBeforeLoad and then onAfterProcess, because onBeforeLoad happens when you load the form, not when submitting it. So it's on a completely separate page load / session.

You might try it onBeforeProcess and onAfterProcess. I'd be interested in seeing if it works. Although I suspect it might be tricky working out when you need to rollback and when you need to commit, for the reasons I outlined above, about the way we write out the main table and an joined tables. I'm not sure at the moment you would be able to tell from an onAfterProcess plugin if there had been a failure in writing one of the joined rows.

But it'd certainly be an interesting experiment.

-- hugh

 

[richy_u@live.com]

Nope, just that I didn't see your post.

It won't work doing it onBeforeLoad and then onAfterProcess, because onBeforeLoad happens when you load the form, not when submitting it. So it's on a completely separate page load / session.

You might try it onBeforeProcess and onAfterProcess. I'd be interested in seeing if it works. Although I suspect it might be tricky working out when you need to rollback and when you need to commit, for the reasons I outlined above, about the way we write out the main table and an joined tables. I'm not sure at the moment you would be able to tell from an onAfterProcess plugin if there had been a failure in writing one of the joined rows.

But it'd certainly be an interesting experiment.

-- hugh

Hi hugh,

I have done the experiment you suggest and it works

, but it is complete userless to me, as certain you know

. This is because only putting a "START TRANSACTION" in event before typing data and putting "COMMIT / ROLLBACK" before submiting this data can be usefull to me.

But you wrote that this two events belong to two separate session and this is obviously the reason because it cannot work, as "START TRANSACTION" and "COMMIT/ROLLBACK" work only in the same session.

So I suspect working with transaction means a lot of modifies at the fabrik code. It is necessary that between onBeforeLoad/onLoad events and the submit events the session doesnt change and no commit is execute.
It is strictly necessary use two different sessions ?

 

[cheesegrits]

I'm not quite sure what you are getting at.

The onBeforeLoad happens when the form is loading. When you load the page with the form on it, either with existing data if you are editing a row, or creating a new form from default data.

So that's a page load. You hit the web server, it fires up J! through PHP, J! instantiates itself, opens the main J! database session, Fabrik gets loaded, and (if we're editing a row) we create our List/Form specific database session and read your form's data from the table. We load and render the form. Fabrik and J! finish their processing, J! exits, PHP scripting terminates, and the J! / Fabrik database sessions get killed off.

Then you submit your form. This starts another page load, J! and Fabrik fire up, create the database sessions, and Fabrik writes out the data through our database session.

During form load, we don't write anything to your data table. So there is nothing to commit or roll back. It's only during the submission processing that data is actually written to the tables. So that's the only session you care about starting and completing a transaction on.

So if you do a transaction start onBeforeProcess, during the submission phase, that will happen BEFORE any changes have been made to your table by Fabrik. You can then either commit or rollback in the onAfterProcess.

-- hugh

 

[richy_u@live.com]

I'm not quite sure what you are getting at.

The onBeforeLoad happens when the form is loading. When you load the page with the form on it, either with existing data if you are editing a row, or creating a new form from default data.

So that's a page load. You hit the web server, it fires up J! through PHP, J! instantiates itself, opens the main J! database session, Fabrik gets loaded, and (if we're editing a row) we create our List/Form specific database session and read your form's data from the table. We load and render the form. Fabrik and J! finish their processing, J! exits, PHP scripting terminates, and the J! / Fabrik database sessions get killed off.

Then you submit your form. This starts another page load, J! and Fabrik fire up, create the database sessions, and Fabrik writes out the data through our database session.

During form load, we don't write anything to your data table. So there is nothing to commit or roll back. It's only during the submission processing that data is actually written to the tables. So that's the only session you care about starting and completing a transaction on.

So if you do a transaction start onBeforeProcess, during the submission phase, that will happen BEFORE any changes have been made to your table by Fabrik. You can then either commit or rollback in the onAfterProcess.

-- hugh

I really thank'you for this fast replay.

You are 100% right and probably what i wanted to do in my application is not compatible with web-programming. Thinking about the data-read, data typing and data-submit as one single phase/session as I did is probably wrong and I will change my code.