Filtering with Connector not working

I am using JAVA DhtmlxConnector (v2.5) render_sql to load data into grid. Also using Dynamic loading with pagination which is working perfectly fine BUT facing issues with server side filtering.

Server side filtering is not working with dynamic loading, just shows blank alert box.

I found same scenario (dynamic loading +pagination + filtering) perfectly working with MySql database.

code snippet:
Server side:

GridConnector c = new GridConnector(conMgnr.getConnection(), DBType.Oracle);
String query = null;
c.servlet(req, res);

c.dynamic_loading(10);

query = “select p.identifier as ID, p.title as TITLE from DEMO p where p.activity_status != ‘Disabled’ order by TITLE asc”;

c.render_sql(query, “ID”, “ID,TITLE”);

client side:

function initGrid()
{
grid = new dhtmlXGridObject(‘grid’);
grid.setImagePath(“dhtmlx_suite/imgs/”);
grid.setHeader(" , “);
grid.setInitWidths(“0,*”);
grid.setColAlign(“center,left”);
grid.setColSorting(”,connector");
grid.setSkin(“drop”);
grid.enablePaging(true, 10, 5, ‘pagingArea’, true, ‘infoArea’);
grid.setPagingSkin(“bricks”);
grid.enableRowsHover(true, “gridHover”);
grid.enableMultiline(true);
grid.makeFilter(‘textbox’,1);
grid.init();
loadGrid();
grid.attachEvent(“onRowSelect”,onRowSelect);
}

Any help would be greatly appreciated.

Please try to enable the server side logging and provide the log result.

docs.dhtmlx.com/doku.php?id=dhtm … nd_logging

Connector uses rather complex SQL for dyn. loading in case of Oracle, which can be the reason of the problem ( your code, both client and server side, look fine )

Thank you Stanislav for replying.
Here are the logs :

====================================
Log started, Thu Oct 06 04:50:43 GMT 2011

DB query
SELECT * FROM ( select /+ FIRST_ROWS(10)/dhx_table.*, ROWNUM rnum FROM (SELECT p.identifier as ID, p.title as TITLE FROM DEMO p WHERE p.activity_status != ‘Disabled’ ORDER BY TITLE ASC) dhx_table where ROWNUM <= 10 ) where rnum >0

DB query
SELECT COUNT(*) as DHX_COUNT FROM DEMO p WHERE p.activity_status != ‘Disabled’

Done in : 124ms

====================================
Log started, Thu Oct 06 04:51:00 GMT 2011

DB query
SELECT * FROM ( select /+ FIRST_ROWS(10)/dhx_table.*, ROWNUM rnum FROM (SELECT p.identifier as ID, p.title as TITLE FROM DEMO p WHERE p.activity_status != ‘Disabled’ AND TITLE LIKE ‘%he%’ AND ID LIKE ‘%%’ ORDER BY TITLE ASC) dhx_table where ROWNUM <= 10 ) where rnum >0

Error during data selecting
Invalid SQL: SELECT * FROM ( select /+ FIRST_ROWS(10)/dhx_table.*, ROWNUM rnum FROM (SELECT p.identifier as ID, p.title as TITLE FROM DEMO p WHERE p.activity_status != ‘Disabled’ AND TITLE LIKE ‘%he%’ AND ID LIKE ‘%%’ ORDER BY TITLE ASC) dhx_table where ROWNUM <= 10 ) where rnum >0
ORA-00904: “ID”: invalid identifier

Try to change the line as

c.render_sql(query, “ID”, “p.identifier(ID),p.title(TITLE)”);

Thank you Stanislav for your reply.

That pretty much solved the problem but filter functionality looks like case sensisitve.
for example:
If I have some data in my grid say “Hello” and if I enter “h” in the filter textbox then it shows zero result for it.
Is there any workaround for it?

By default filter text converts to the next sql code

field LIKE “%value%”

it possible to change it to any other sql instruction through beforeFilter event

docs.dhtmlx.com/doku.php?id=dhtm … filtration

function custom_filter($filter_by){ $index = $filter_by->index("some_field"); if ($index!==false) //there is client side input for the filter $filter_by->rules[$index]="custom sql code goes here"; }

Hey Stanislav,

Thanks for your previous replies.I have one more similar issue :

I am trying same functionality of dynamic loading + pagination with server side filters using #connector_select_filter.

Query is :
String query = “SELECT ED.IDENTIFIER AS IDENTIFIER, CS.SHORT_TITLE AS TITLE, FORM_ID, to_char(STUDYSTARTDATE,‘MM/DD/YYYY’) as STUDYSTARTDATE,to_char(STUDYENDDATE,‘MM/DD/YYYY’) as STUDYENDDATE,FILETYPE,CU.LAST_NAME||’, '||CU.FIRST_NAME AS USERNAME,to_char(JOBSTARTTIME,‘MM/DD/YYYY HH24:MI’) as JOBSTARTTIME ,STATUS from EXPORT_DATA_DETAILS ED,CATISSUE_SPECIMEN_PROTOCOL CS,CATISSUE_USER CU WHERE CS.IDENTIFIER=ED.CS_ID AND CU.IDENTIFIER=ED.USER_IDENTIFIER AND ED.USER_IDENTIFIER= 1234 Order By ED.IDENTIFIER DESC”;

    Connector code:

connector.render_sql(query, “IDENTIFIER”, “STATUS,CS.SHORT_TITLE(TITLE),FORM_ID,FILETYPE,JOBSTARTTIME,STUDYSTARTDATE,STUDYENDDATE,CU.LAST_NAME||’, '||CU.FIRST_NAME(USERNAME)”);

Select filter works perfectly for FORM_ID ( Is this because form_id is directly mapped to column of table and not generated from join on tables ? )
while select filter is not working for CS.SHORT_TITLE(TITLE) and for CU.LAST_NAME||’, '||CU.FIRST_NAME(USERNAME).

I have been struggling with this for last few days but it’s not working.Any help will be highly appreciated.

When automatic option list building not work - you can provide specific query through an additional command.

docs.dhtmlx.com/doku.php?id=dhtm … ns_in_grid

[code]BaseConnector options = new OptionsConnector(conn);
options.render_sql(“select DISTINCT SHORT_TITLE as value, SHORT_TITLE as label from CATISSUE_SPECIMEN_PROTOCOL”,“item_id”,“SHORT_TITLE(value),SHORT_TITLE(label)”);

connector.set_options(“TITLE”,options);[/code]

Hi Stanislav,

Thanks for your reply.
I tried this but it’s still not working.

protected void configure()
{

GridConnector connector = new GridConnector(conMgnr.getConnection(), DBType.Oracle);

String query = “SELECT ED.IDENTIFIER AS IDENTIFIER, CS.SHORT_TITLE AS TITLE, FORM_ID, to_char(STUDYSTARTDATE,‘MM/DD/YYYY’) as STUDYSTARTDATE,to_char(STUDYENDDATE,‘MM/DD/YYYY’) as STUDYENDDATE,FILETYPE,CU.LAST_NAME||’, '||CU.FIRST_NAME AS USERNAME,to_char(JOBSTARTTIME,‘MM/DD/YYYY HH24:MI’) as JOBSTARTTIME ,STATUS from EXPORT_DATA_DETAILS ED,CATISSUE_SPECIMEN_PROTOCOL CS,CATISSUE_USER CU WHERE CS.IDENTIFIER=ED.CS_ID AND CU.IDENTIFIER=ED.USER_IDENTIFIER AND ED.USER_IDENTIFIER=1234 Order By ED.IDENTIFIER DESC”;

String tableColString = “STATUS,TITLE,FORM_ID,FILETYPE,JOBSTARTTIME,STUDYSTARTDATE,STUDYENDDATE,USERNAME”;

connector.dynamic_loading(15);

connector.render_sql(query, “IDENTIFIER”, tableColString);
BaseConnector options = new OptionsConnector(conMgnr.getConnection());
options.render_sql(“select DISTINCT SHORT_TITLE as value, SHORT_TITLE as label from CATISSUE_SPECIMEN_PROTOCOL”,“IDENTIFIER”,“SHORT_TITLE(value),SHORT_TITLE(label)”);

connector.set_options("TITLE",options);

options = new OptionsConnector(conMgnr.getConnection());
options.render_sql(“select DISTINCT FILETYPE as value , FILETYPE as label from EXPORT_DATA_DETAILS”,“op2”,“FILETYPE(value),FILETYPE(label)”);
connector.set_options(“FILETYPE”,options);

}

here, tried to set two options fields (TITLE, FILETYPE) but it is not working.

Change order of command connector.render_sql - must be the last command. Any configurations after it have not sense, as data is already rendered.

Hey Stanislav,

I used OptionsConnector as below and enabled logger for options.

        BaseConnector options = new OptionsConnector(conMgnr.getConnection(), DBType.Oracle);
        options.enable_log("c:\\logs\\option.txt", true);
        options.render_sql("select DISTINCT csp.SHORT_TITLE as value, csp.SHORT_TITLE as label from CATISSUE_SPECIMEN_PROTOCOL csp","csp.IDENTIFIER","csp.SHORT_TITLE(value),csp.SHORT_TITLE(label)");

        connector.set_options("CS.SHORT_TITLE(TITLE)", options);
        connector.render_sql( "SELECT CS.IDENTIFIER AS IDENTIFIER, CS.SHORT_TITLE AS TITLE, CS.IRB_IDENTIFIER as IRBIDENTIFIER from CATISSUE_SPECIMEN_PROTOCOL CS", "IDENTIFIER", "CS.SHORT_TITLE(TITLE)");
       
       
       
        Still select filter is not working for TITLE and what I found in option.txt is the query of grid and not of options.
        This is the entry from option.txt

====================================
Log started, Thu Nov 03 12:58:54 GMT 2011

DB query
SELECT * FROM ( select /+ FIRST_ROWS(15)/dhx_table.*, ROWNUM rnum FROM (SELECT CS.IDENTIFIER AS IDENTIFIER, CS.SHORT_TITLE AS TITLE, CS.IRB_IDENTIFIER as IRBIDENTIFIER FROM CATISSUE_SPECIMEN_PROTOCOL CS) dhx_table where ROWNUM <= 15 ) where rnum >0

DB query
SELECT COUNT(*) as DHX_COUNT FROM CATISSUE_SPECIMEN_PROTOCOL CS

I am dealing with this server side select filters issue quite long. Please correct me if I am missing something.

Change set_options command as

connector.set_options("CS.SHORT_TITLE(TITLE)", options);

Also, beware that it will be triggered only if you have select-type column or select-filter for that column in the grid.

Hi Stanislav,

I have used set options in exactly same way that you mentioned in your last post i.e.

connector.set_options("CS.SHORT_TITLE(TITLE)", options);

Sorry if I am wrong but I can’t find the difference for set_options in your post and my last post.

Also on client side I have mentioned:

grid.attachHeader("#connector_select_filter");

Let me know if I am missing anything.
Thank You.

Sorry for inconvenience, I have pasted the wrong code snippet.

The correct code is

connector.set_options("CS.SHORT_TITLE", options);

In set_options command, you need to use the name of field only and not the exact construction from render command.

Hi Stanislav,

Still not working :frowning:

can you send the pm with dump of db structure ( no data, only structure of tables ) - so I will be able to recreate your requests locally ?

Hi Stanislav,

Below is the java code, client side code and db structure.

public class LoadGridServlet extends ConnectorServlet
{

	private static final long serialVersionUID = 1L;
	private String jsonString;
	private String gridType;
	// Dynamic Load record count
	private int dynamicLoadRowCount=0;

	public synchronized int getDynamicLoadRowCount()
	{
		return dynamicLoadRowCount;
	}

	public synchronized void setDynamicLoadRowCount(int dynamicLoadRowCount)
	{
		this.dynamicLoadRowCount = dynamicLoadRowCount;
	}

	public synchronized String getGridType()
	{
		return gridType;
	}

	public synchronized void setGridType(String gridType)
	{
		this.gridType = gridType;
	}

	public synchronized String getJsonString()
	{
		return jsonString;
	}

	public synchronized void setJsonString(String jsonString)
	{
		this.jsonString = jsonString;
	}

	protected void configure()
	{
		IDAOFactory daoFactory = null;
		IConnectionManager conMgnr = null;
		try
		{
			AbstractGridImpl abstractGridobj = getImplObj();
			String appName = CommonServiceLocator.getInstance().getAppName();
			daoFactory = DAOConfigFactory.getInstance().getDAOFactory(appName);
			conMgnr = daoFactory.getJDBCDAO().getConnectionManager();

			GridConnector connector = new GridConnector(conMgnr.getConnection(), DBType.Oracle);
			connector.event.attach(abstractGridobj);
			
			BaseConnector options = new OptionsConnector(conMgnr.getConnection(), DBType.Oracle);
			options.enable_log("c:\\logs\\option.txt", true);
			options.render_sql("select DISTINCT csp.SHORT_TITLE as value, csp.SHORT_TITLE as label from CATISSUE_SPECIMEN_PROTOCOL csp","csp.IDENTIFIER","csp.SHORT_TITLE(value),csp.SHORT_TITLE(label)");

			connector.set_options("CS.SHORT_TITLE(TITLE)", options);
			connector.render_sql( "SELECT ED.IDENTIFIER AS IDENTIFIER, CS.SHORT_TITLE AS TITLE, FORM_ID, to_char(STUDYSTARTDATE,'MM/DD/YYYY') as STUDYSTARTDATE,to_char(STUDYENDDATE,'MM/DD/YYYY') as STUDYENDDATE,FILETYPE,CU.LAST_NAME||', '||CU.FIRST_NAME AS USERNAME,to_char(JOBSTARTTIME,'MM/DD/YYYY HH24:MI') as JOBSTARTTIME ,STATUS from EXPORT_DATA_DETAILS ED,CATISSUE_SPECIMEN_PROTOCOL CS,CATISSUE_USER CU WHERE CS.IDENTIFIER=ED.CS_ID AND CU.IDENTIFIER=ED.USER_IDENTIFIER  Order By ED.IDENTIFIER DESC", "IDENTIFIER", "STATUS,CS.SHORT_TITLE (TITLE),FORM_ID,FILETYPE,JOBSTARTTIME,STUDYSTARTDATE,STUDYENDDATE,USERNAME");			
			
			if (dynamicLoadRowCount != 0)
			{
				connector.dynamic_loading(dynamicLoadRowCount);
			}

		}
		catch (Exception ex)
		{
			Logger.getLogger(LoadGridServlet.class).error(ex.getMessage(), ex);

		}
		finally
		{
			try
			{
				conMgnr.closeConnection();
			}
			catch (DAOException e)
			{
				Logger.getLogger(LoadGridServlet.class).error(e.getMessage(), e);

			}
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException,
			IOException
	{
		setJsonString(req.getParameter("paramJson"));
		setGridType(req.getParameter("gridType"));
		if (req.getParameter("dynamicLoadRowCount") != null)
		{
			setDynamicLoadRowCount(Integer.parseInt(req.getParameter("dynamicLoadRowCount")));
		}
		
		super.doGet(req, res);

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException,
			IOException
	{
		setJsonString(req.getParameter("paramJson"));
		setGridType(req.getParameter("gridType"));
		super.doGet(req, res);

	}

	/**
	 *
	 * @return
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	protected AbstractGridImpl getImplObj() throws IOException, InstantiationException,
			IllegalAccessException, ClassNotFoundException
	{
		InputStream inputStream = LoadGridServlet.class.getClassLoader().getResourceAsStream(
				Constants.GRID_SETUP_PROP_FILE);
		Properties props = new Properties();
		props.load(inputStream);
		String className = props.getProperty(gridType);
		AbstractGridImpl abstractGridobj = (AbstractGridImpl) Class.forName(className)
				.newInstance();
		return abstractGridobj;

	}

}


javascript functions


function getExportDataGrid(userId,isAdmin){

	mygrid = new dhtmlXGridObject('downloadDataDetails');
	mygrid.setImagePath("dhtmlx_suite/imgs/");
	mygrid.setHeader("Actions,Clinical Study,Study Form,File Type,Exported On,Encounter Start Date,Encounter End Date,Exported By");
	mygrid.setInitWidths("70,100,*,70,130,100,100,150");
	mygrid.setEditable(false);
	mygrid.setColAlign("left,left,left,center,center,center,center,left");
	mygrid.setSkin("dhx_skyblue");
	mygrid.enablePaging(true, 15, 5, "exportData_PagingArea", true, "infoArea");
	mygrid.setPagingSkin("bricks");
	mygrid.attachHeader("#connector_text_filter,#connector_select_filter,,#connector_select_filter,,,,");
	mygrid.init();
	var paramsArr = {
		userId : userId,
		isAdmin : isAdmin
	};
	var gridQString = "LoadGridServlet?gridType=exportdata&dynamicLoadRowCount=15&paramJson="
			+ createJsonOnjectForGrid(paramsArr);
	mygrid.loadXML(gridQString + "&connector=true",hideDownloadImage());
//	mygrid.load(gridQString,function(){getSavedFiltersResponse();});
	mygrid.attachEvent("onPageChanged", function(ind,fInd,lInd){firstInd= fInd;lastInd= lInd;});//explain usage of the indices

}

DB Schema

CREATE TABLE “EXPORT_DATA_DETAILS”
( “IDENTIFIER” NUMBER,
“USER_IDENTIFIER” NUMBER,
“CS_ID” NUMBER,
“STUDYSTARTDATE” DATE,
“STUDYENDDATE” DATE,
“FILENAME” VARCHAR2(640 BYTE),
“JOBSTARTTIME” TIMESTAMP (6),
“JOBENDTIME” TIMESTAMP (6),
“FILETYPE” VARCHAR2(10 BYTE),
“STATUS” VARCHAR2(30 BYTE),
“ERRORMESSAGE” VARCHAR2(1024 BYTE),
“FILECONTENT” BLOB,
“TMP_ID” NUMBER(19,0),
“FORM_ID” CLOB,
PRIMARY KEY (“IDENTIFIER”)

) ;

CREATE TABLE “CATISSUE_SPECIMEN_PROTOCOL”
( “IDENTIFIER” NUMBER(19,0) NOT NULL ENABLE,
“PRINCIPAL_INVESTIGATOR_ID” NUMBER(19,0),
“TITLE” VARCHAR2(255 BYTE) NOT NULL ENABLE,
“SHORT_TITLE” VARCHAR2(255 BYTE),
“IRB_IDENTIFIER” VARCHAR2(255 BYTE),
“START_DATE” DATE,
“END_DATE” DATE,
“ENROLLMENT” NUMBER(*,0),
“DESCRIPTION_URL” VARCHAR2(255 BYTE),
“ACTIVITY_STATUS” VARCHAR2(50 BYTE),
“PARTCIPNT_MATCH_WITHIN_CSCP” NUMBER(1,0),
“IS_EMPI_ENABLE” NUMBER(1,0),
“PPI_FORMAT” VARCHAR2(255 BYTE),
“IS_TO_GENERATE_PARTICIPANTCODE” NUMBER(1,0) DEFAULT 0,
“IS_TO_STORE_PHI” NUMBER(1,0) DEFAULT 0,
PRIMARY KEY (“IDENTIFIER”)
)

CREATE TABLE “CATISSUE_USER”
( “IDENTIFIER” NUMBER(19,0) NOT NULL ENABLE,
“EMAIL_ADDRESS” VARCHAR2(255 BYTE),
“FIRST_NAME” VARCHAR2(255 BYTE),
“LAST_NAME” VARCHAR2(255 BYTE),
“LOGIN_NAME” VARCHAR2(255 BYTE) NOT NULL ENABLE,
“START_DATE” DATE,
“ACTIVITY_STATUS” VARCHAR2(50 BYTE),
“DEPARTMENT_ID” NUMBER(19,0),
“CANCER_RESEARCH_GROUP_ID” NUMBER(19,0),
“INSTITUTION_ID” NUMBER(19,0),
“ADDRESS_ID” NUMBER(19,0),
“CSM_USER_ID” NUMBER(19,0),
“STATUS_COMMENT” VARCHAR2(500 BYTE),
“FIRST_TIME_LOGIN” NUMBER(1,0) DEFAULT 1,
“IS_ADMIN” NUMBER(1,0) DEFAULT 0,
“WUSTLKEY” VARCHAR2(100 BYTE),
PRIMARY KEY (“IDENTIFIER”)
)


Thank you.

Hi stanislav,

Waiting for you reply