I want to run a sql script like this:
select sum(objekt.obj_kauf_brutto) into @obj_brutto from objekt where objekt.obj_kpi = 0;
DROP temporary table if exists brutto_miet_rendite;
CREATE temporary TABLE brutto_miet_rendite(
SELECT format(sum(bu_betrag)*100/@obj_brutto, 2,'de_DE') as 'bmr'
from `buchung` where bu_art = 29 and bu_jahr > '2016' group by bu_obj);
select AVG(bmr) from brutto_miet_rendite;
COMMIT;
but it looks like EXECUTE($sql) statement allows only one select statement at same time.What is the best way to run connected / combined sql statements in one transaction in phpmaker 2020?BR
G
I don’t think PHPMaker has such function that will combine several statements in one transaction. You need to run a single Execute global function for each statement.
If you only meant transaction, old and new versions have it.If you want to use transaction, you should use START TRANSACTION first and then COMMIT.Since you retieive a value in the last statement, you cannot do it in one time, you need to call ExecuteScalar() at last, e.g.
ExecuteStatement("START TRANSACTION;
DROP temporary table if exists brutto_miet_rendite;
CREATE temporary table brutto_miet_rendite(...);
COMMIT;");
echo ExecuteScalar("select SUM(ID) from brutto_miet_rendite;");
Note: The above example code works for v2022. For v2020, you may try it yourself by replacing ExecuteStatement() to Execute().
Hi,
in V2020.16 → “Execute()” proceed exacly only one statement. So no possibility to run transaction inside Execute().Mysqlconnection has some transaktion methodes like