Loading data from MSSQL using dhtmlxscheduler

Hi ,
I am trying to retrieve the data from MSSQL database using the dhtmlxScheduler.I did take a look at the sample code

scheduler.init(‘scheduler_here’,new Date(2009,10,1),“month”);
scheduler.setLoadMode(“month”)
scheduler.load(“php/events.php”);

var dp = new dataProcessor(“php/events.php”);
dp.init(scheduler);

But this doesnt point to where iam reading the data dynamically.
I am using JSP and not PHP

To establish a database connection i looked the code
$res=mssql_connect(’.\MSSQLSERVER’,"","",false);
mssql_select_db(“sampleDB”);

$gridConn = new GridConnector($res,“MsSQL”);

How do i change this to a Java code ?

Do i have to use the following line ?
scheduler.load(“codebase/connector/db_mssql.php”);

Can i call a servlet which makes a DB connection and renders the xml data? I even tried that

import java.sql.Connection;

import com.dhtmlx.connector.ConnectorServlet;
import com.dhtmlx.connector.SchedulerConnector;

// TODO: Auto-generated Javadoc
/**

  • The Class SchedulerBasicConnector.
    */
    public class Scheduler_Database_EventsConnector extends ConnectorServlet {

    /* (non-Javadoc)

    • @see com.dhtmlx.connector.ConnectorServlet#configure()
      */
      @Override
      protected void configure() {
      Connection conn= ( new DataBaseConnection()).getConnection();

      SchedulerConnector c = new SchedulerConnector(conn);
      c.render_table(“events_rec”,“event_id”,“start_date,end_date,text,rec_type,event_pid,event_length”,"","");

    }

}

I am callling this servlet from a JSP and there is also a Servlet -mapping corresponding to this in web.xml of the root application. i tried calling scheduler.load(“servlet name”) ; but no luck

The events_rec is created and there is a entry for a corresponding startdate and enddate .When i run this using this code i do not get any error on the server side But looks like it was not able to generate the appropriate xml and there fore throws LOAD XML Error some time STACK OVER FLOW line 11

What do i need to do in events.PHP on a JSP side? Do i need to use this to dynamically load the data from the database ? Any help is appreciated

Thanks in Advance

I am using JSP and not PHP
Client side code is the same for any platform, so you can use existing samples, just use JavaConnectors at server side.

Connection conn= ( new DataBaseConnection()).getConnection();
Did you change DB connection string in DataBaseConnection class?
This class used only for sample purposes and return JDBC connection, you can update its , or use any other logic to get correct DB connection.

To get more info about server side errors ( which most probably are result of mis-configuration ) , you can enable logs

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

Thanks Stanislav
Yes i did use the appropriate DB String and from what you say
I am bit confused here
gridConn.enable_log(“path to log file”);
What is gridConn ? here i am using dhtmlxScheduler .How should i be using it?

gridConn.enable_log(“path to log file”,true);

LogManager.getInstance().log(“any text here”);

Can you please explain them i did not find any documentation on them?

Thanks in Advance

Hi Stanislav,
here is my init() code
function init() {
scheduler.config.xml_date="%Y-%m-%d %H:%i";
scheduler.config.details_on_create=true;
scheduler.config.details_on_dblclick=true;
scheduler.config.multi_day = true;

	scheduler.init('scheduler_here',new Date(2010,2,1),"month");
			scheduler.load("scheduler/events1.do");

	
	var dp = new dataProcessor("scheduler/events1.do");
	dp.init(scheduler);

}

This invokes a servlet
and i have put log statements just to make sure it is working

public class Scheduler_Database_EventsConnector extends ConnectorServlet {

/* (non-Javadoc)
 * @see com.dhtmlx.connector.ConnectorServlet#configure()
 */
@Override
protected void configure() {
	Connection conn= ( new DataBaseConnection()).getConnection();
	System.out.println("Executing Statement ..");
	SchedulerConnector c = new SchedulerConnector(conn);
	//c.render_table("events_rec","event_id","start_date,end_date,text,rec_type,event_pid,event_length","","");
	c.render_table("select * from events_rec","event_id","start_date,end_date,text");
	System.out.println("Executing Statement Complete...");
}

}

On the server side is see the logs Executing Statement Complete…printed

The events_rec has the following records in the table

event_id start_date end_date text


1 2010-06-13 00:00:00.000 2010-06-16 00:00:00.000 Second Friday
2 2010-06-28 10:00:00.000 2010-06-30 12:00:00.000 Test build

I have turned on the debugscript as well i dont see anything it just pops blank
Looks like it failed to build xml response. Something wrong with the query ? i dont see the event on the scheduler for this please let me know
Thanks

If you are using

SchedulerConnector c = new SchedulerConnector(conn);
It will be

c.enable_log("path to log file");

Any connector object has such method.

It will be enough to log info about request processing

c.render_table(“select * from events_rec”,“event_id”,“start_date,end_date,text”);

You need to use render_sql, not render_table, when need to render data based on custom sql query

Thanks Stanislav,

i even tried using render_sql statement
c.render_sql(“select * from events_rec”,“event_id”,“text,rec_type,event_pid,event_length”,"","");

The server side logs after using c.enable_log (“path to file”);

====================================
Log started, Tue Jun 15 10:36:17 EDT 2010

DB query
SELECT * FROM nipa.dbo.events_rec

Error during data selecting
Invalid SQL: SELECT * FROM events_rec
Result set type is TYPE_FORWARD_ONLY

Is there a a way to set the Resultset Type ? Can this be resulting in a overflow ? I am using MSSQL DateTime field which stores date and time as well for Scheduler Events startime and endtime. Would that be having a problem?

Please let me know
Thanks
Percy

Thanks Stanislav,

i even tried using render_sql statement
c.render_sql(“select * from events_rec”,“event_id”,“text,rec_type,event_pid,event_length”,"","");

The server side logs after using c.enable_log (“path to file”);

====================================
Log started, Tue Jun 15 10:36:17 EDT 2010

DB query
SELECT * FROM events_rec

Error during data selecting
Invalid SQL: SELECT * FROM events_rec
Result set type is TYPE_FORWARD_ONLY

Is there a a way to set the Resultset Type ? Can this be resulting in a overflow ? I am using MSSQL DateTime field which stores date and time as well for Scheduler Events startime and endtime. Would that be having a problem?

Please let me know
Thanks
Percy

Hi Stanislav,
Any suggestion on the script that i sent earlier and based on your recommendations.Please let me know
Thanks
Prao

Problem with render_sql and JAVA connectors in case of MSSQL connection is confirmed.
The code of connector need to be updated to support such use-case. ( current version will work with render_table, but has problems with render_sql for MSSQL DB )
Updated version of connector will be available in few days

Hi Stanislav,
Any update on the availability of the dhtmlConnector for MSSQl based on the issue that you confirmed that it is from your side?
Please let me know
Thanks

Hi Stanislav,

Any update for us on the new release of dhtmlConnector which fixes the issues we are having with MSSQL database?

Please let me know
Thank You

MSSQL has a datatype field called DateTime.Can dhtmlxScheduler/dhtmlxConnector pass values of Date and time in Long datatype?
Please let me know
Thanks

Connector generates sql command where date is presented as string in default date format. Normally SQL server is capable to convert it to the datetime field value.

If you need a more precise control over DB operations, you can define your custom logic instead of auto-generated calls.

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

Hello Stanislav,
Do we have an updated Connector like you said you would release it within a few days.I am trying to use MSSql DB and you said that the issue which i mentioned in the email chain would be fixed and made available.Any update on that ?
Please let me know
Thank You

Updated version of connector.jar is attached.
dhtmlxconnector.zip (109 KB)

Many Thanks to you Stanislav.
I will let you know how it goes

Hello Stanislav
I tried with the new dtmlxConnector.jar .But still no luck. I have attached a screenshot (Events_rec.png) of the database table and what iam actually querying.I am looking to render this data on the browser using dhtmlxscheduler .The appropriate methods are used to render xml.We really like this application and would like to move on but we are stuck here with sql query not able to execute successfully.Please help

Executing Statement …
com.dhtmlx.connector.ConnectorOperationException: Invalid SQL: SELECT event_id,start_date,end_date,text,rec_type,event_pid,event_length FROM events_rec
Result set type is TYPE_FORWARD_ONLY
at com.dhtmlx.connector.DBDataWrapper.query(DBDataWrapper.java:364)
at com.dhtmlx.connector.DBDataWrapper.select(DBDataWrapper.java:214)
at com.dhtmlx.connector.BaseConnector.render(BaseConnector.java:308)
at com.dhtmlx.connector.BaseConnector.render_table(BaseConnector.java:210)
at Scheduler_LoadConnector.configure(Scheduler_LoadConnector.java:28)
at com.dhtmlx.connector.ConnectorServlet.doGet(ConnectorServlet.java:28)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:868)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:663)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:619)
Executing Statement Complete…

Here the log is enabled and this is what i see

Log started, Wed Jul 28 10:30:54 EDT 2010

DB query
SELECT event_id,start_date,end_date,text,rec_type,event_pid,event_length FROM events_rec

Error during data selecting
Invalid SQL: SELECT event_id,start_date,end_date,text,rec_type,event_pid,event_length FROM events_rec
Result set type is TYPE_FORWARD_ONLY


a) you are connecting to DB by using

Class.forName ("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance (); conn = DriverManager.getConnection(...);
or you are using some other way to access data ? ( odbc or similar )

b) When creating connector you are using second parameter as

SchedulerConnector c = new SchedulerConnector(conn, DBType.MSSQL);

or it is differ ?

The updated code of connector clearly creates

createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

so there must not be problem with result set type, and locally it works fine against MSSQL db ( MSSQL Server 2005 )

That worked now i am able to read from DB and display on the scheduler
Now how do i add a new event when i press save on the lightbox event ?

scheduler.attachEvent(“onEventSave”,function(id,ev){
scheduler._roll_back_dates(ev); //can be added
scheduler.isCollision(ev.rec_type, ev.event_length);
});

Where can i add the attachEvent to the scheduler ? Is it in the init method? Let me know the sample code .

Also on the scheuler when i view in the browser IE the title of the event is sligltly truncated meaning some letter for eg P appears to be truncated and looks a D more than a P .How do we increase the size.

Appreaciate all the help and thanks in advance ,
Looking forward to your response