how to check sc_exec_sql executed query or not,if executed correctly commit transaction else rollback transaction.
There are several ScriptCase macros for handling SQL. These should be the ones you need:
a. sc_begin_trans
b. sc_commit_trans
c. sc_rollback_trans
See the web page that explains them:
http://www.scriptcase.net/docs/en_us/v8/scriptcase-macros/scriptcase-macros
Roll down to the section “SQL”. Click the links for more detailed information on the macros.
In general its good to look through all these macros. Chances are that you will need them at one time or other to interact with the database or application objects.
Furthermore, if there is an error while executing the SQL statement with sc_exec_sql, it will automatically rollback. But if you want to check that the changes to the database are as expected, you can use a, b and c above. After the sc_exec_sql, you can perform some selects against the database to see if the result is as planned and then either commit or rollback.
You might also be interested in this thread.
http://www.scriptcase.net/forum/showthread.php?3416-Transaction-control
jsb
Yes, that is very relevant. I have only had success with my action queries and thus I assumed that if anything went wrong, an exception would be thrown that I could catch in try-catch structure. But that is not the case then.
Another way of dealing with it, is to just use regular PDO (PHP Data Objects) separate from ScriptCase, so you can have better control. I did this for a PHP class, which I made for reading and creating settings in the database. Through the Database Variables that ScriptCase provides, one can get the connection, username and password of the existing ScriptCase database connection and use them for creating a separate PDO connection like this:
$dbadr = explode(":", [sc_glo_servidor]);
$conn_str = “mysql:host=” . $dbadr[0] .
“;port=” . $dbadr[1] .
“;dbname=” . [sc_glo_banco]
;
$db = new PDO($conn_str, [sc_glo_usuario], sc_decode([sc_glo_senha]) );
Read about the SC built in database variables used above here:
“http://www.scriptcase.net/docs/en_us/v8/scriptcase-macros/scriptcase-macros#Variables - Database”
Now you are free to use $db for any SQL work on the database, e.g.
$num_recs_affected = $db->exec(“delete from {tabname} where {field_1} = XX”)
if (false === $num_recs_affected {
{error processing}
}
As for the use of PDO exec, the PHP manual is a good reference:
https://secure.php.net/manual/en/pdo.exec.php
https://secure.php.net/manual/en/book.pdo.php
i want for sc_exec_sql.
eg:$return=sc_exec_sql(“update users set name={myname} where id=‘1’”);
how can i check if the query executed or not,wil it return any value,if so how?
1 Like
Well, if you read the forum thread that jsbinca referred to above (http://www.scriptcase.net/forum/showthread.php?3416-Transaction-control), you can see that there is no certain way to know if sc_exec_sql succeeds. That is why a change was suggested that sc_exec_sql should return number of records affected. However, that change has not been done, so you have the following options:
a. Use the sc_select as suggest by jsbinca in the forum thread referenced above.
b. Use a separate PDO connection as I suggested
c. Manually check if your query has succeeded by using a sc_lookup or sc_select after the sc_exec_sql:
i. sc_begin_trans
ii. sc_exec_sql
iii. sc_lookup or sc_select to see if changes were done in the database
iv. based on the above use sc_commit_trans or sc_rollback_trans to either commit or rollback.
So, if you wish to stay with sc macros (solution b or c), you have to study a bit on them to put your code properly together.
For solution a you have to study the PDO (PHP Data Objects).
1 Like