Hi all, I am playing with SuiteCRM 7.8.8 LTS. I need to call a stored procedure from a process-record logic hook. This stored proc starts with 3 arguments, and returns with a single value. My code looks like that:
$sql = "CALL sp_test(1, 1, 1)";
$ret = strtolower($GLOBALS['db']->getOne($sql));
The result is a crashing-page on process-record event. If I edit the sql command in this way
$sql = "SELECT @@VERSION";
$ret = strtolower($GLOBALS['db']->getOne($sql));
in $ret I find MySQL setup version. If I call the stored proc from MySQL command line, it works fine. I think that there is something wrong while calling getOne() with stored procedures.
Any advice?
Thanks in advance!
Try $GLOBALS->query($sql)
Thanks for your reply. Tried that method before posting here, with no luck. This morning I will try to write a fresh PHP page to test mysqli module with stored procs.
I would like to use them to avoid lot of php code: MySQL joins are much more elegant.
I found the way of calling a stored procedure from logic hook.
global $sugar_config;
$conn = new mysqli(
$sugar_config['dbconfig']['db_host_name'],
$sugar_config['dbconfig']['db_user_name'],
$sugar_config['dbconfig']['db_password'],
$sugar_config['dbconfig']['db_name']);
$sql = "CALL my_stored_proc(...);";
$res = $conn->query($sql);
...
$conn->close();
Database object available from SuiteCRM doesnβt work with stored proc at all.
1 Like
Try this code to connect stored procedure from logic hook,
global $sugar_config;
// Mysql service name (instance)
$mysql_db_host = $sugar_config['dbconfig']['db_host_name'] ; // Here enter the databse host name.
$mysql_db_user = $sugar_config['dbconfig']['db_user_name'] ; // Here enter the username to access your database.
$mysql_db_pass = $sugar_config['dbconfig']['db_password'] ; // Here enter the password to access your database.
$mysql_db_name = $sugar_config['dbconfig']['db_name'] ; // Here enter the database name, of which you want to take the backup.
$mysqlconn = mysqli_connect($mysql_db_host,$mysql_db_user,$mysql_db_pass,$mysql_db_name);
$result = mysqli_query($mysqlconn,"CALL my_stored_proc");
1 Like
Thanks kanagaraj, but I have already found the same solution (look to my previous post).

Thanks. You posted your work around code was not working.
My code was working in logic hook. You have found any alternative solution.
My code works fine. I use it on SuiteCRM 7.8.8 LTS, on PHP7 (withing Apache + Ubuntu 16.04 Server).