Transaction control

Scriptcase 8 Suggestions
#1

Hi,
in scriptcase we have the macros sc_begin_trans(), sc_commit_trans() and sc_rollback_trans() which are pretty straight forward to use. The problem arises inside a transaction to check if a query finished as it was suppposed to.
As far as I know you can use the sc_exec_sql() macro to alter the table(s) but to check the success of the query you have to run a sc_lookup(). Well,… imagine transactions with up to 15 tables involved. You can type lookup statements until your fingers are sore.
So right now I’m using the plain php functions to get a true/false value of an operation but for the sake of consistency it would be nice if the sc_exec_sql() macro would return a boolean value.
If there is a possibility to retrieve such a value or mabe an easy way to intercept the error before it is displayed, please let me know.

Otherwise I would greatly appreciate (and hopefully others too) if the sc_exec_sql() macro could be extended in such a way.

jsb

BTW: Happy New Year to everybody!

#2

I 2nd this proposal. A happy new year too.

#3

Hello JSB,

I will get in touch with our development team to see if such improvement on the current macro is possible.

Happy new year to you too!

regards,
Bernhard Bernsmann

#4

Well, well, well, …
I have to admit that sometimes it does make sense to read the manual by the letter. :slight_smile:

Just in case somebody else needs transactions in i.e. a blank application and wants to stay with the scriptcase macros. (sc_begin_trans, sc_commit_trans, sc_rollback_trans)
Here is a little workaround.

Instead of using the sc_exec_sql()-macro for database operations you have to use the sc_select()-macro. //read the help text
With this macro you can do something like:

sc_begin_trans();
…;
sc_select(ergebnis,“DELETE FROM my_table WHERE my_delete = 1”);
$results[] = $ergebnis;
sc_select(ergebnis,“UPDATE my_table2 SET total = price * weight WHERE my_id IN ($updates)”);
$results[] = $ergebnis;
…;
$success = true;
foreach($results as $ok)
{
if(!$ok)
{
$success = false;
}
}

if(!$success)
{
sc_rollback_trans();
echo “Something went wrong!”;
}
else
{
sc_commit_trans();
echo “We have been successful!”;
}

Hope this helps others

jsb

#5

mmm, an update statement in a sc_select macro. Doesn’t make sence to me, but it’s nice that it works. I would recommend renaming this macro. But it’s a great work-around. Need to read the docs on macro’s more carefully. What does the $result[] returns? The number of records touched?

#6

When you read the doc to the macro, it says ‘SQL-Command’, not just ‘SELECT’, that’s what brought me into this.

Concerning the return values, it’s pretty simple. When everthing is OK you basically get an empty array. In the example above it would be $ergebnis and it shows _NumberOfRows = 0.
In case something went wrong the macro returns actually nothing, which is ‘false’. But in addition to this you get the error message in a variable, here it would be $ergebnis_erro. (this is not a typo, there is no ‘r’ at the end.)
I’ve used the $results-array to catch all the return values of the db-operations and check on them at the end.

jsb