Error while plotting data from the database

[deeptic]

FusionCharts Database Example

<%

/*

In this example, we show how to connect FusionCharts to a database.

For the sake of ease, we've used a database which contains two tables, which are linked to each

other.

*/



//Database Objects - Initialization

Statement st1,st2;

ResultSet rs1,rs2;



String strQuery="";



//strXML will be used to store the entire XML document generated

String strXML="";





//Generate the chart element

strXML = "";



//Construct the query to retrieve data

strQuery = "select Emp_Month,Stickabililty from Sample_Data";





st1=oConn.createStatement();

rs1=st1.executeQuery(strQuery);



String factoryId=null;

String factoryName=null;

String totalOutput="";

//Iterate through each employee

while(rs1.next()) {



factoryId=rs1.getString("Emp_Month");

factoryName=rs1.getString("Stickabililty");

//Now create second recordset to get details for this sample_data

strQuery = "select Stickabililty as TotOutput from Sample_Data where Emp_Month="+factoryId;

st2=oConn.createStatement();

rs2 = st2.executeQuery(strQuery);

if(rs2.next()){

totalOutput=rs2.getString("TotOutput");

}

//Generate

strXML += "";

//Close resultset

try {

if(null!=rs2){

rs2.close();

rs2=null;

}

}catch(java.sql.SQLException e){

//do something

System.out.println("Could not close the resultset");

}

try{

if(null!=st2) {

st2.close();

st2=null;

}

}catch(java.sql.SQLException e){

//do something

System.out.println("Could not close the statement");

}

} //end of while

//Finally, close element

strXML += "";

//close the resulset,statement,connection

try {

if(null!=rs1){

rs1.close();

rs1=null;

}

}catch(java.sql.SQLException e){

//do something

System.out.println("Could not close the resultset");

}

try {

if(null!=st1) {

st1.close();

st1=null;

}

}catch(java.sql.SQLException e){

//do something

System.out.println("Could not close the statement");

}

try {

if(null!=oConn) {

oConn.close();

oConn=null;

}

}catch(java.sql.SQLException e){

//do something

System.out.println("Could not close the connection");

}



//Create the chart - Pie 3D Chart with data from strXML

%>





























Unable to see the chart above?

« Back to list of examples

[srividya_sharma]

Hi Deepti

Welcome to the wonderful world of FusionCharts!

Since you have changed the resource name in the context.xml,

you would need to change the lookup Data Source Name in DBConn.jsp.

Please change the following statement in Includes/DBConn.jsp:

from

DataSource ds = (DataSource)envContext.lookup("jdbc/FactoryDB");

to

DataSource ds = (DataSource)envContext.lookup("jdbc/test");

The remaining code for database connectivity seems to be fine.

 

Hope this helps.

Srividya

[deeptic]

Hi Srividya,

 

 

 

The error has got resolved. But there's a new one coming up. I think it's the way I have syntaxed the SQL query. This is the error

 

 

 

type Exception report

 

 

 

message

 

 

 

description The server encountered an internal error () that prevented it from fulfilling this request.

 

 

 

exception

 

 

 

org.apache.jasper.JasperException: An exception occurred processing JSP page /DBExample/BasicDBExample.jsp at line 73

 

 

 

70: //Now create second recordset to get details for this sample_data

 

71: strQuery = "select Stickabililty as TotOutput from Sample_Data where Emp_Month=" + factoryId;

 

72: st2=oConn.createStatement();

 

73: rs2 = st2.executeQuery(strQuery);

 

74: if(rs2.next()){

 

75: totalOutput=rs2.getString("TotOutput");

 

76: }

 

 

 

 

 

Stacktrace:

 

org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:505)

 

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398)

 

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)

 

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)

 

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

 

 

 

 

root cause

 

 

 

javax.servlet.ServletException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'March' in 'where clause'

 

org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:852)

 

org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)

 

org.apache.jsp.DBExample.BasicDBExample_jsp._jspService(BasicDBExample_jsp.java:245)

 

org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

 

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)

 

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)

 

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)

 

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

 

 

 

 

root cause

 

 

 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'March' in 'where clause'

 

sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

 

sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

 

sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

 

java.lang.reflect.Constructor.newInstance(Unknown Source)

 

com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

 

com.mysql.jdbc.Util.getInstance(Util.java:381)

 

com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)

 

com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

 

com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)

 

com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)

 

com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)

 

com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)

 

com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)

 

com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)

 

com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)

 

org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

 

org.apache.jsp.DBExample.BasicDBExample_jsp._jspService(BasicDBExample_jsp.java:170)

 

org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

 

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)

 

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)

 

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)

 

javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

 

 

 

 

My table (Sample_Data) is structured as follows:

 

 

 

Field Type

 

Emp_Name varchar(20)

 

Stickabililty tinyint(3)

 

Proficiency tinyint(3)

 

ClientProfile tinyint(3)

 

Referenciability tinyint(3)

 

OverallEffectiveness tinyint(3)

 

Emp_Month varchar(15)

 

Emp_Year smallint(4)

 

 

 

In my pie chart, I have to display the Stickabililty and Emp_Month (for March & April)

 

 

 

Can you please guide how do I syntax this:

 

 

 

strQuery = "select Stickabililty as TotOutput from Sample_Data where Emp_Month=" + factoryId;

[srividya_sharma]

Hi Deepti

Please put single quotes around any varchar field value. So, your syntax will change to

strQuery = "select Stickabililty as TotOutput from Sample_Data where Emp_Month='" + factoryId +"'";

Hope this works out for you.

Srividya

[deeptic]

Hi Srividya,

 

 

 

Yes, that's what I have done and now it works correctly.

[Guest Rajroop]

You are always welcome.

 

 

 

Happy FusionCharting. :hehe: