Hi,
i have the need to create a pivot table, I created a procedure that I tried in phpmyadmin, the query is very complex.
I installed two modules in SuiteCRM (7.10.20),
SQL Fiddle
Advanced Reports
in both, once installed, it allows you to directly enter SQL statements to query the DB and get the results. These modules have always worked, even for complex queries, but evidently in this case I asked too much, it gives me syntax error already on the second line related to DELIMITER.
Is there any way to achieve my goal? Does SuiteCRM need such a query to be constructed in any special way? Or is it not possible to use PROCEDURE… CALL… and other such particular keywords? Thanks
Hi Fab,
Database object available from Suite CRM doesn’t work with stored procedures at all.
If you are creating a custom report, then you can do that by using the following code:
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();
Reference Link: Calling stored procedures from logic hooks - #3 by chogori
Thank you Rolustech for your reply, I was reading what you sent and I don’t understand some things:
1 I would need an access point, i.e. create a button that on click starts the call to the store procedure (how?), I don’t know how feasible it is via logic_hook
2 why use
$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']);
and not directly
$sql="...";
global $db;
$db->query($sql);
3 should the store procedure necessarily be created on the DB and in SuiteCRM only called?
4 Suppose the store procedure is executed, how do I get the results to show? How do I create a custom page without going through the Studio form creation and paste the results as a report?
I hope you can answer these questions
Please see my highlighted responses for each query:
1 I would need an access point, i.e. create a button that on click starts the call to the store procedure (how?), I don’t know how feasible it is via logic_hook
You can add a new action via controller in any module. Then add that new action in the menu. Please see the following URL to add the new action:
Please customize the menu file: modules/Opportunities/Menu.php (This file is for Opportunities, you can do for any module)
Please note that logic hook is not feasible to add the new button
2 why use
$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’]);
and not directly
$sql="…";
global $db;
$db->query($sql);
SuiteCRM $db object does not support to execute the procedures. You need to create a helper file to create a mysql/mysqli connection and then use that function to execute your procedure.
3 should the store procedure necessarily be created on the DB and in SuiteCRM only called?
Yes, it should be created in the DB and call in the SuiteCRM.
4 Suppose the store procedure is executed, how do I get the results to show? How do I create a custom page without going through the Studio form creation and paste the results as a report?
in the point 1, where you create a controller, you can call your procedure from there and then loop the data and display the results. You can add tpl file and send the data which needs to be displayed. Please see the attached example to pass data to tpl file:
$sugar_smarty = new Sugar_Smarty();
$sugar_smarty->assign(“ITEM_DESCRIPTION”, $description);
echo $sugar_smarty->fetch(‘custom/modules/Opportunities/report.tpl’);
Please add the html in the report.tpl.
I hope this will help you to develop your solution. Please feel free to contact us if you have any query.
Thanks Rolustech, all very clear, except the last point:
I created the procedure and called it, there are no errors (and already a great thing). What I would like to know is how to create a tpl file where there are the results of the query and how to call it in my controller.php (where there is also the procedure call).
I looked for a practical example as you suggested, but in custom/modules/Opportunities there is nothing.
I was able to call the procedure, but I have problems with the tpl file. Inside the controller I entered the CALL and then the call to the tpl file which is opened in popup with unspecified error in the logs:
$sugar_smarty->display("modules/<CUSTOM_MODULE>/tpls/report.tpl");
$sql = "CALL procedure();";
$res = $conn->query($sql);
Whatever line I enter in the tpl, it fails, even with a simple one:
<table width="100%" cellpadding="0" cellspacing="0" border="0">
</table>
I also tried with a different file type (report.html), the result is the same. I also tried calling an existing tpl file, always error, even after a Q&R.
Maybe there is a specific way to create this type of file? Does it have to be “registered” somewhere?