Calling stored procedures from logic hooks

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

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!

2

Try $GLOBALS->query($sql)

3

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.

4

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
5

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
6

Thanks kanagaraj, but I have already found the same solution (look to my previous post).

:slight_smile:

7

Thanks. You posted your work around code was not working.
My code was working in logic hook. You have found any alternative solution.

8

My code works fine. I use it on SuiteCRM 7.8.8 LTS, on PHP7 (withing Apache + Ubuntu 16.04 Server).