Execute Stored Procedure (msssql) from logic hook

[richarddixonfl]

I am trying to execute a stored procedure from a logic hook in order to be able to set a field in CRM based on some criteria that may change from time to time. The best way we saw fit was to call the stored procedure from the logic hook, pass it the ID Guid and then modify the fields on the database backend via the stored procedure.

So far I have modified the /custom/modules/phones/logic_hook.php to look like this:

<?php
// Do not store anything in this file that is not part of the array or the hook version. This file will
// be automatically rebuilt in the future.
$hook_version = 1;
$hook_array = Array();
// position, file, function
$hook_array['before_save'] = Array();
$hook_array['before_save'][] = Array(1, 'workflow', 'include/workflow/WorkFlowHandler.php','WorkFlowHandler', 'WorkFlowHandler');
$hook_array['after_save'][] = Array(2, void, 'custom/modules/phone_Phones/phones_cstm.php', 'Phones_cstm', 'phone_after_save_1');
?>

and the custom logic hook file, /custom/modules/phones/phones_cstm.php:

<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class Phones_cstm
{
function phone_after_save_1()
{
//sleep(5);
global $currentModule;

//$query = "EXEC sp_phoneSaveHandler '$this->id';";
$query = "exec sp_phoneSaveHandler '35D9FD03-C09D-493A-9A3D-D3D8F3FF0485';";
$GLOBALS['log']->fatal($query);
$result = $bean->db->query($query, true);
//$db->query($query, true);

}
}

?>

I have tried all sorts of things and all I get upon save is this:

(the values from the page, then below

Fatal error: Call to a member function query() on a non-object in E:\SugarCRM\custom\modules\phone_Phones\phones_cst m.php on line 14

and in the log:
04/28/11 14:18:05 [916][4eeb5a12-9668-6122-189f-47bb36dd9632][FATAL] exec sp_phoneSaveHandler '35D9FD03-C09D-493A-9A3D-D3D8F3FF0485';

any quick solutions on how to do this correctly??

[eggsurplus]

Try using the global db var:

 global $db;
 
$db->query(....); 
  

[Angel]

Have you verified that the SP is stored within the Sugar database, as opposed to master (or other database)?

Also, what happens if you try to execute SELECT @@version as your query?

[richarddixonfl]

@eggsurplus,

Your solution worked perfectly.

To answer Angel, the Stored procedure is in the sugarcrm database. we have modified the database for backend integration with our telephony system as well as our predictive dialer system so we have numerous stored procedures as well as additional columns and additional tables that feed our business solution. We created the stored procedure right in the same database and the call to it works perfectly! Thanks for the help and the prompt answer. I am a php newbie, I am straining to understand php as i am so used to visual basic and it is a totally different animal. I just cant seem to grasp the php, but with some sample code I can usually run with it and get it to work!
As soon as i added the global for the db it ran without a hitch!