Can’t get the "render_complex_sql" running

Suite and Optimus Suite 5
#1

Whenever I try to use the “render_complex_sql” method (java connector ver. 15_120612) Tomcat returns the error. For the sake of clarity I run the “render_complex_sql” method on your sample which comes with java connector v1.5 download. I ran the sample substituting “render_sql” method to “render_complex_sql” in “tree_03_sql_connector” class and got the error

27.08.2012 19:12:44 org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet tree_03_sql_config_connector threw exception
java.lang.StackOverflowError
at com.mysql.jdbc.Util.handleNewInstance(Util.java:429)
at com.mysql.jdbc.ResultSetImpl.getInstance(ResultSetImpl.java:382)
at com.mysql.jdbc.MysqlIO.buildResultSetWithRows(MysqlIO.java:2604)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:487)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2582)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1758)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2172)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:783)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:626)
at com.dhtmlx.connector.DBDataWrapper.query(DBDataWrapper.java:362)
at com.dhtmlx.connector.DBDataWrapper.select(DBDataWrapper.java:214)
at com.dhtmlx.connector.TreeRenderStrategy.render_set(TreeRenderStrategy.java:37)
……………………
at com.dhtmlx.connector.TreeRenderStrategy.render_set(TreeRenderStrategy.java:37)

Two original files were changed (“tree_03_sql_connector.java”- as said above, and “DataBaseConnection.java”- to connect to MySql database) all the rest were left untouched.
What is the problem? I need to run stored procedure somehow.
Best regards

#2

Problem confirmed. It is a limitation of render_complex_sql

By design, render_complex_sql do not modifies the sql. But to fetch data for tree - it must add some filtering to each sub-queue, to fetch only related level of the tree. Because it can’t do it in case of render_complex_sql, data fetching fails.

The same will be for TreeGrid connector
Plain dataset connectors ( all others ) are not affected.

Basically, you can’t use render_complex_sql to fetch hierarchical dataset.

#3

“…you can’t use render_complex_sql to fetch hierarchical dataset…”
This is apply for the complex sql queries as well, as I understand. But your manuals claims contrary assertion:
“…You are allowed to use any SQL statements to populate a dhtmlx component through dhtmlxConnector. For complex SQL queries we strongly encourage you to use the render_complex_sql() method…”
(docs.dhtmlx.com/doku.php?id=dhtm … ex_queries)

You likely either refute my conclusion or correct your manuals, then.

What purpose of use of “render_complex_sql” if it doesn’t handle hierarchical datasets? Do I have any chance to run stored procedures to build the “tree” component using DHTMLX tools?

Best regards

#4

We will correct documentation.

render_complex_sql will work for all other connectors except of tree and treeGrid - so there is still a place where they can be used.

As for stored procedures for tree data generation - unfortunately I don’t see how it can be done with existing connector.

#5

Many thanks for assistance

#6

I create a stored procedure in my database. The stored procedure comes from two tables by joining them. I was trying to load the data from the stored procedure to the grid. However, it will only let me load the data from the first table, but won’t let me load the data from the second table and it will give me the error message" invalid argument’’. Here are the two php files. The first one will work, but the second one won’t.\

file 1.

<?php require("../dhtmlxConnector/php/codebase/grid_connector.php"); $conn = mysql_connect("localhost: 3306","root","javlin"); mysql_select_db("world"); $grid = new GridConnector($conn,"MySQL"); $grid->render_complex_sql("call world.get_countries();"," ","name, region, population"); ?>

file 2.

<?php require("../dhtmlxConnector/php/codebase/grid_connector.php"); $conn = mysql_connect("localhost: 3306","root","javlin"); mysql_select_db("world"); $grid = new GridConnector($conn,"MySQL"); $grid->render_complex_sql("call world.get_countries();"," ","name, region, population, language"); ?>
#7

here is the sql code for joining the tables.
select c.code, c.name, c.region, c.population, cl.language, cl.percentage from country c
left join countrylanguage cl on c.code = cl.countrycode;

#8

Try to enable server side logs, they may contain useful details about the error

docs.dhtmlx.com/doku.php?id=dhtm … tor:errors