Problem with actions_table

Suite and Optimus Suite 5
#1

Hi everybody,

I just had problem with actions-table in the past using render_sql or render_complex_sql and i solved using views that can be rendered with render_table

Now i can’t user views because the server that i have to use doesn’t allow them.
This is my problem:
PHP:

<?php header ("Content-Type: text / html; charset = utf-8"); require("../codebase/connector/grid_connector.php"); $res= new PDO("mysql:dbname=my_mbo;host=localhost","root",""); $conn = new GridConnector($res,"PDO"); $conn->enable_live_update('actions_table'); $conn->event->attach("beforeinsert","myInsert"); $conn->enable_log("temp.log"); if ($conn->is_select_mode()){ $conn->render_complex_sql("select my_mbo.magazzino.id AS id,my_mbo.magazzino.codice AS codice,my_mbo.magazzino.descrizione AS descrizione,my_mbo.magazzino.categoria AS categoria,my_mbo.magazzino.um AS um,my_mbo.magazzino.ubicazione AS ubicazione,my_mbo.magazzino.nota AS nota,my_mbo.saldi.quantita AS quantita,my_mbo.saldi.quantita - my_mbo.saldi.qta_prenotata - my_mbo.saldi.qta_fuori AS qta_disponibile,my_mbo.saldi.qta_prenotata AS qta_prenotata,my_mbo.saldi.qta_fuori AS qta_fuori,my_mbo.magazzino.nota AS nota2 from my_mbo.magazzino left join my_mbo.saldi on my_mbo.magazzino.codice = my_mbo.saldi.codice where my_mbo.magazzino.codice <> ''","id","codice,descrizione,categoria,um,ubicazione,nota,quantita,qta_disponibile, qta_prenotata,qta_fuori, nota2"); } else { $conn->render_table("magazzino","id","codice,descrizione,categoria,um,ubicazione,nota"); } function myInsert($action){ if($action->get_value("codice")=="") $action->set_value("codice",null); } ?>

when i call the script i’ve this error:

====================================
Log started, 21/05/2024 03:51:58

Editing = Updating = 1

Editing = Updating = 1

SELECT .*, actions_table.id, actions_table.dataId, actions_table.type as action_table_type, actions_table.user FROM actions_table LEFT OUTER JOIN ON actions_table.DATAID = .id WHERE ( actions_table.ID > ‘57’ AND actions_table.USER <> ‘1716299516345’)

!!!Uncaught Exception
Code: 42000
Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘*, actions_table.id, actions_table.dataId, actions_table.type as action_table…’ at line 1

I can’t seem to find the name of the table (select .asterisk,) that should be (magazzino.asterisk) due to the Select being built on two different tables. Where am I wrong?

Thanks for your help.
i’m sure that Sematik has the solution.

#2

after a lot of test and controlling log file i ve this situation.

PHP:
header (“Content-Type: text / html; charset = utf-8”);
require(“…/codebase/connector/grid_connector.php”);
$res= new PDO(“mysql:dbname=my_mbo;host=localhost”,“root”,“”);
$conn = new GridConnector($res,“PDO”);
$conn->enable_live_update(‘actions_table’);
$conn->event->attach(“beforeinsert”,“myInsert”);
$conn->enable_log(“temp.log”);
if ($conn->is_select_mode()){
$conn->render_sql(“select magazzino.*,saldi.quantita,saldi.quantita - saldi.qta_prenotata - saldi.qta_fuori AS qta_disponibile,saldi.qta_prenotata,saldi.qta_fuori,magazzino.nota AS nota2 from magazzino left join saldi on magazzino.codice = saldi.codice where magazzino.codice <> ‘’”,“magazzino.id”,“codice,descrizione,categoria,um,ubicazione,nota,quantita,qta_disponibile, qta_prenotata,qta_fuori, nota2”);
} else {
$conn->render_table(“magazzino”,“id”,“codice,descrizione,categoria,um,ubicazione,nota,bloccato”);
}
function myInsert($action){
if($action->get_value(“codice”)==“”)
$action->set_value(“codice”,null);
}
?>

and this are the query that appears in log file:

  1. for select and is correct.

====================================
Log started, 23/05/2024 04:37:01

Editing = Updating =

Editing = Updating =

SELECT magazzino.*,saldi.quantita,saldi.quantita - saldi.qta_prenotata - saldi.qta_fuori AS qta_disponibile,saldi.qta_prenotata,saldi.qta_fuori,magazzino.nota AS nota2 FROM magazzino left join saldi on magazzino.codice = saldi.codice WHERE ( magazzino.codice <> ‘’)

SELECT MAX(id) as VERSION FROM actions_table

Done in 0.0014209747314453s

FOR UPDATE AND THIS IS WRONG

====================================
Log started, 23/05/2024 04:37:03

Editing = Updating = 1

Editing = Updating = 1

SELECT magazzino left join saldi on magazzino.codice = saldi.codice.*, actions_table.id, actions_table.dataId, actions_table.type as action_table_type, actions_table.user FROM actions_table LEFT OUTER JOIN magazzino left join saldi on magazzino.codice = saldi.codice ON actions_table.DATAID = magazzino left join saldi on magazzino.codice = saldi.codice.magazzino.id WHERE ( magazzino.codice <> ‘’) AND ( actions_table.ID > ‘71’ AND actions_table.USER <> ‘1716475021133’)

!!!Uncaught Exception
Code: 42000
Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘left join saldi on magazzino.codice = saldi.codice.*, actions_table.id, actio…’ at line 1

I’ dont understand why CONNECTOR try to create the query for UPDATE operation with the sql insided in the RENDER_SQL command instead the sql insided in the RENDER_TABLE command.

Please help me it’s a critical part of application.

Thanks