Using Procedures

Suite and Optimus Suite 5
#1

Hi,

I am new to using the dhtmlx modules so I looked at the examples and got most of it working.
The only thing I can’t get to work is communication with mySQL database based on procedures.
I used the example of the form and the mySQL connection with the render_table and render_sql statements.
Is there anyone who can show me how to do the same with Procedures?
My procedures are to complex to insert them into the php statemants.

So instead of using render_table(“contacts”,“contact_id”,“fname,lname,email”) or render_sql(“SELECT * FROM contacts”,“contact_id”,“fname,lname,email”) I would like to use
something like render_sql(“retrieve_contacts()”,“contact_id”,“fname,lname,email”)

I have read that I should switch to mySQLi to use procedures, but I don’t know how to implement it into the example (as told I am new to this).

Any help will be appreceated.

Thanks,
Alex

#2

a) use Mysqli driver ( db_mysqli.php, MySQLi as second parameter of constructor )
b) in render_sql you can use ‘CALL retrieve_contacts()’ as first param
mysqli_connector.zip (1.04 KB)

#3

Hello Stanislav,

Thanks for your reply.
I implemented your suggestion, but I still get no results.

This code is working:

<?php require("../codebase/connector/grid_connector.php"); $res=mysql_connect("localhost","root","passw"); mysql_select_db("test"); $gridConn = new GridConnector($res,"MySQL"); $gridConn->render_table("contacts","contact_id","fname,lname,email"); ?>

This code is not working:

<?php require("../codebase/connector/db_mysqli.php"); require("../codebase/connector/grid_connector.php"); $gridConn = new GridConnector($mysqli, "MySQLi"); $gridConn->render_sql("call ret_cont()","contact_id","fname,lname,email"); ?>

I checked the directories and the support for mySQLi on the Apche server and that was fine.
An XML file for tree data with mySQLi procedures is working fine, but the grid and form data is not working.

Thanks for your help,
Alex

#4

Update on the error during loading of the page:

Fatal error: Uncaught exception ‘Exception’ with message ‘Source of data can’t be empty’ in C:\WebSites\Newtree\codebase\connector\db_common.php:192
Stack trace:
#0 C:\WebSites\Newtree\codebase\connector\db_common.php(240): DataRequestConfig->set_source(’’)
#1 C:\WebSites\Newtree\codebase\connector\base_connector.php(387): DataRequestConfig->parse_sql(‘call Ret_cont(’…’)
#2 C:\WebSites\Newtree\xml\contacts.php(21): Connector->render_sql(‘call Ret_cont(’…’, ‘contact_id’, ‘fname,lname,ema…’)
#3 {main}
thrown in C:\WebSites\Newtree\codebase\connector\db_common.php on line 192

The error is not clear to me. What ‘Source’ is empty?

#5

$gridConn = new GridConnector($mysqli, “MySQLi”);
What is $mysqli in that code?
Probably you have missed

$mysqli = new mysqli($server, $user, $pass, $mysql_db);
#6

Hello Stanislav,

You are correct, I missed it in the code I placed on the forum, but is was in my code.
Sorry, the code that is not working should be:

<?php require("../codebase/connector/db_mysqli.php"); require("../codebase/connector/grid_connector.php"); $mysqli = new mysqli('localhost','root','passw','test'); $gridConn = new GridConnector($mysqli, "MySQLi"); $gridConn->render_sql("call ret_cont()","contact_id","fname,lname,email"); ?>

So the error remains, and it does not work.
I don’t understand the error about the empty source.
As far as I can see I have all information inserted.

#7

I did an additional test to look for the error.

In stead of the procedure ‘call ret_cont()’ I used the sql statement ‘select * from contacts’ and the render_sql command for mysqli.

With this statement the data is retrieved without error, so my connection is OK and so are the used js files. (that is my conclusion)

The procedure won’t work, so I wonder weather the syntax is correct, or there is a need for more variables or …

Alex

#8

Problem confirmed
Try to update db_common.php with the attached one.
db_common.zip (6.52 KB)

#9

Hello Stanislav,

I used the new db_common.php but that did not solve the problem. The error has changed to:

Fatal error: Uncaught exception ‘Exception’ with message ‘MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘call ret_cont()’ at line 1’ in C:\WebSites\Newtree\codebase\connector\db_mysqli.php:13
Stack trace:
#0 C:\WebSites\Newtree\codebase\connector\db_common.php(631): MySQLiDBDataWrapper->query(‘SELECT call ret…’)
#1 C:\WebSites\Newtree\codebase\connector\base_connector.php(434): DBDataWrapper->select(Object(DataRequestConfig))
#2 C:\WebSites\Newtree\codebase\connector\base_connector.php(388): Connector->render()
#3 C:\WebSites\Newtree\xml\contacts.php(21): Connector->render_sql(‘call ret_cont()’, ‘contact_id’, ‘fname,lname,ema…’)
#4 {main}
thrown in C:\WebSites\Newtree\codebase\connector\db_mysqli.php on line 13

As I notice it is trying to execute ‘SELECT call ret_cont()’ in stead of just the ‘call ret_cont()’ procedure.
The procedure itself is running in mySQL and is very simple:

CREATE PROCEDURE test.Ret_cont()
BEGIN
SELECT fname,lname,email
FROM contacts
ORDER BY lname;
END;

#10

Sorry for inconvenience , the valid fix is attached.
db_common.zip (6.54 KB)

#11

Stanislav,

Works like a charme!

Thanks for the support.

Kind regards,
Alex