JDBCChartAdapter problem
JDBCChartAdapter problem
Can the JDBCChartAdapter cope with any type of database field?
I've got eight fields in a mySQL database. The first being a datetime field, and the rest declared as int(10).
I've tried using JDBCChartAdapter in both a servlet, and an application, but always displays "Unknown Data" for each field, and then bails out with a java.lang.NullPointerException, in the executeQuery function.
Does it recognise datetime and int(x) columns, or do they have to be a specific type?
Many thanks
Steve
I've got eight fields in a mySQL database. The first being a datetime field, and the rest declared as int(10).
I've tried using JDBCChartAdapter in both a servlet, and an application, but always displays "Unknown Data" for each field, and then bails out with a java.lang.NullPointerException, in the executeQuery function.
Does it recognise datetime and int(x) columns, or do they have to be a specific type?
Many thanks
Steve
Re: JDBCChartAdapter problem
I have tested/developed against an oracle DB, where it does support dates and int columns. I have not utilsied mySQL.
The adapter relies on the types returned by the jdbc driver and supports the following.
java.sql.Types.NUMERIC:
java.sql.Types.REAL:
java.sql.Types.INTEGER:
java.sql.Types.DOUBLE:
java.sql.Types.FLOAT:
java.sql.Types.BIT:
java.sql.Types.DATE:
java.sql.Types.TIMESTAMP:
I have just made some changes to the adapter to make the use of these types more explicit, which may solve your issue.
I see that sourceforge hosts a mySQL server. I will try against this in the next week. If you send me your table defs, a small bit of table data, and the sql you are trying, I will try with your example.
The adapter relies on the types returned by the jdbc driver and supports the following.
java.sql.Types.NUMERIC:
java.sql.Types.REAL:
java.sql.Types.INTEGER:
java.sql.Types.DOUBLE:
java.sql.Types.FLOAT:
java.sql.Types.BIT:
java.sql.Types.DATE:
java.sql.Types.TIMESTAMP:
I have just made some changes to the adapter to make the use of these types more explicit, which may solve your issue.
I see that sourceforge hosts a mySQL server. I will try against this in the next week. If you send me your table defs, a small bit of table data, and the sql you are trying, I will try with your example.
Re: JDBCChartAdapter problem
I'm wondering a similar thing.
I installed JFreeChart last night, got the servlet demo to display the chart area but no plotted data regardless of the values I selected from the 3 examples. I then modified the params to point to my own database where I know I can get a result - I'm using the pure Java hsql from Soureforge - and still no luck.
My thoughts are that for the sample tables as well as my own schema, I had to import all numeric values as datatypes 'Integer' or 'Double', as hsql does not support 'Number'.
Logs show that the connection to the database works fine, but no values are returned. I read in the docs that the range for a CategoryDataset is numerical, with values represented by Number objects. Could it therefore be that I will have to cast the returned values into type Number in order to get a valid display?
I don't have the actual error output available now, but can post it later if that will help.
I installed JFreeChart last night, got the servlet demo to display the chart area but no plotted data regardless of the values I selected from the 3 examples. I then modified the params to point to my own database where I know I can get a result - I'm using the pure Java hsql from Soureforge - and still no luck.
My thoughts are that for the sample tables as well as my own schema, I had to import all numeric values as datatypes 'Integer' or 'Double', as hsql does not support 'Number'.
Logs show that the connection to the database works fine, but no values are returned. I read in the docs that the range for a CategoryDataset is numerical, with values represented by Number objects. Could it therefore be that I will have to cast the returned values into type Number in order to get a valid display?
I don't have the actual error output available now, but can post it later if that will help.
Re: JDBCChartAdapter problem
Steve / Wanda
I have now populated a mySQL database on sourceforge with the demo data included in the war file. I will start testing against this on Monday and let you know.
Bryan
I have now populated a mySQL database on sourceforge with the demo data included in the war file. I will start testing against this on Monday and let you know.
Bryan
Re: JDBCChartAdapter problem
Bryan /Wanda,
I've successfully managed to get the JDBCChartAdapter to pull data from the mySQL database.
The null exception I was getting was due to a null value, hidden in the database - doh!
I know null values are usually handled by the DBMS, but perhaps some handling code would be appropriate here?
As for the "Unknown Data" problem, I recompiled the JDBCChartAdapter source code with some trace lines, and the problem seemed to vanish.
for (column = 0; column < numberOfColumns; column++) {
if (validColumns[column]) {
xObject = resultSet.getObject(column + 1);
if (xObject instanceof Number) { newRow.addElement((Number) xObject);
} else if (xObject instanceof java.util.Date) {
newRow.addElement(new Long(((java.util.Date)xObject).getTime()));
} else {
System.out.println("Unknown Data");
newRow.addElement(xObject);
}
}
}
I've got a feeling it has something to do with the instanceof operator in the section of code above. Before recompiling the source, this always returned false, hence the "Unknown Data" message. Since recompiling the source it seems to behave. I'm not to sure if this is to do with different SDK versions?
I'm using J2SDK v1.4.0
Hope this helps
Steve
I've successfully managed to get the JDBCChartAdapter to pull data from the mySQL database.
The null exception I was getting was due to a null value, hidden in the database - doh!
I know null values are usually handled by the DBMS, but perhaps some handling code would be appropriate here?
As for the "Unknown Data" problem, I recompiled the JDBCChartAdapter source code with some trace lines, and the problem seemed to vanish.
for (column = 0; column < numberOfColumns; column++) {
if (validColumns[column]) {
xObject = resultSet.getObject(column + 1);
if (xObject instanceof Number) { newRow.addElement((Number) xObject);
} else if (xObject instanceof java.util.Date) {
newRow.addElement(new Long(((java.util.Date)xObject).getTime()));
} else {
System.out.println("Unknown Data");
newRow.addElement(xObject);
}
}
}
I've got a feeling it has something to do with the instanceof operator in the section of code above. Before recompiling the source, this always returned false, hence the "Unknown Data" message. Since recompiling the source it seems to behave. I'm not to sure if this is to do with different SDK versions?
I'm using J2SDK v1.4.0
Hope this helps
Steve
Re: JDBCChartAdapter problem
I've created the tables and data in the CloudscapeDB database that comes with the J2EE RI from sun and have modified the web.xml context parameters to look like this:
DBdriver=COM.cloudscape.core.JDBCDriver
DBuser=blank
DBpwd=blank
DBschema=blank
DBurl=jdbc:cloudscape:CloudscapeDB
But i keep getting the error:
Servlet ImageSelectSQL - Cannot get database connection when instantiating class - SQL Exception: Database 'CloudscapeDB' not found.
Servlet ImageSelectSQL - Cannot connect to database using URL jdbc:cloudscape:CloudscapeDB as user
when I try to run the servlets that use the database.
any ideas?
Thanks,
Jim
DBdriver=COM.cloudscape.core.JDBCDriver
DBuser=blank
DBpwd=blank
DBschema=blank
DBurl=jdbc:cloudscape:CloudscapeDB
But i keep getting the error:
Servlet ImageSelectSQL - Cannot get database connection when instantiating class - SQL Exception: Database 'CloudscapeDB' not found.
Servlet ImageSelectSQL - Cannot connect to database using URL jdbc:cloudscape:CloudscapeDB as user
when I try to run the servlets that use the database.
any ideas?
Thanks,
Jim
Re: JDBCChartAdapter problem
Steve
I agree with your analysis of the JDBCChartAdapter. I mentioned I had done a couple of changes, as you are compiling here they are:
public void executeQuery(String query) {
Object xObject = null;
int column = 0;
int currentColumn = 0;
int numberOfColumns = 0;
int numberOfValidColumns = 0;
int columnTypes[] = null;
if (connection == null || statement == null) {
System.err.println("There is no database to execute the query.");
return;
}
try {
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
numberOfColumns = metaData.getColumnCount();
columnTypes = new int[numberOfColumns];
for (column = 0; column < numberOfColumns; column++) {
try {
int type = metaData.getColumnType(column + 1);
switch (type) {
case Types.NUMERIC:
case Types.REAL:
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
case Types.BIT:
case Types.DATE:
case Types.TIMESTAMP:
++numberOfValidColumns;
columnTypes[column] = type;
break;
default:
System.err.println("Unable to load column " + column + "(" + type + ")");
columnTypes[column] = Types.NULL ;
break;
}
} catch (SQLException e) {
e.printStackTrace();
columnTypes[column] = Types.NULL ;
}
}
/// First colum is X data
columnNames = new String[numberOfValidColumns - 1];
/// Get the column names and cache them.
//System.out.println("Starting column names");
currentColumn = 0;
for (column = 1; column < numberOfColumns; column++) {
if (columnTypes[column] != Types.NULL) {
columnNames[currentColumn] = metaData.getColumnLabel(column + 1);
++currentColumn;
}
}
//System.out.println("Finished column names");
/// Might need to add, to free memory from any previous result sets
if ( rows != null) {
for (column = 0; column < rows.size(); column++) {
Vector row = (Vector) rows.get(column);
row.removeAllElements();
}
rows.removeAllElements();
}
// Get all rows.
rows = new Vector();
while (resultSet.next()) {
Vector newRow = new Vector();
for (column = 0; column < numberOfColumns; column++) {
xObject = resultSet.getObject(column + 1);
switch (columnTypes[column]) {
case Types.NUMERIC:
case Types.REAL:
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
case Types.BIT:
newRow.addElement((Number) xObject);
break ;
case Types.DATE:
case Types.TIMESTAMP:
newRow.addElement(new Long(((java.util.Date) xObject).getTime()));
break;
case Types.NULL:
break ;
default:
System.err.println("UnKnown Data");
columnTypes[column] = Types.NULL ;
break;
}
}
rows.addElement(newRow);
}
/// A Kludge to make everything work when no rows returned
if (rows.size() == 0) {
Vector newRow = new Vector();
for (column = 0; column < numberOfColumns; column++) {
if (columnTypes[column] != Types.NULL) {
newRow.addElement(new Integer(0));
}
}
rows.addElement(newRow);
}
/// Determine max and min values.
if (rows.size() < 1) {
maxValue = 0.0;
minValue = 0.0;
} else {
Vector row = (Vector) rows.elementAt(0);
double test;
maxValue = ((Number) row.get(1)).doubleValue();
minValue = maxValue;
for (int rowNum = 0; rowNum < rows.size(); ++rowNum) {
row = (Vector) rows.elementAt(rowNum);
for (column = 1; column < numberOfColumns; column++) {
test = ((Number) row.get(column)).doubleValue();
if (test < minValue) {
minValue = test;
}
if (test > maxValue) {
maxValue = test;
}
}
}
}
fireDatasetChanged();// Tell the listeners a new table has arrived.
} catch (SQLException ex) {
System.err.println(ex);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
}
}
}
}
I agree with your analysis of the JDBCChartAdapter. I mentioned I had done a couple of changes, as you are compiling here they are:
public void executeQuery(String query) {
Object xObject = null;
int column = 0;
int currentColumn = 0;
int numberOfColumns = 0;
int numberOfValidColumns = 0;
int columnTypes[] = null;
if (connection == null || statement == null) {
System.err.println("There is no database to execute the query.");
return;
}
try {
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
numberOfColumns = metaData.getColumnCount();
columnTypes = new int[numberOfColumns];
for (column = 0; column < numberOfColumns; column++) {
try {
int type = metaData.getColumnType(column + 1);
switch (type) {
case Types.NUMERIC:
case Types.REAL:
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
case Types.BIT:
case Types.DATE:
case Types.TIMESTAMP:
++numberOfValidColumns;
columnTypes[column] = type;
break;
default:
System.err.println("Unable to load column " + column + "(" + type + ")");
columnTypes[column] = Types.NULL ;
break;
}
} catch (SQLException e) {
e.printStackTrace();
columnTypes[column] = Types.NULL ;
}
}
/// First colum is X data
columnNames = new String[numberOfValidColumns - 1];
/// Get the column names and cache them.
//System.out.println("Starting column names");
currentColumn = 0;
for (column = 1; column < numberOfColumns; column++) {
if (columnTypes[column] != Types.NULL) {
columnNames[currentColumn] = metaData.getColumnLabel(column + 1);
++currentColumn;
}
}
//System.out.println("Finished column names");
/// Might need to add, to free memory from any previous result sets
if ( rows != null) {
for (column = 0; column < rows.size(); column++) {
Vector row = (Vector) rows.get(column);
row.removeAllElements();
}
rows.removeAllElements();
}
// Get all rows.
rows = new Vector();
while (resultSet.next()) {
Vector newRow = new Vector();
for (column = 0; column < numberOfColumns; column++) {
xObject = resultSet.getObject(column + 1);
switch (columnTypes[column]) {
case Types.NUMERIC:
case Types.REAL:
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
case Types.BIT:
newRow.addElement((Number) xObject);
break ;
case Types.DATE:
case Types.TIMESTAMP:
newRow.addElement(new Long(((java.util.Date) xObject).getTime()));
break;
case Types.NULL:
break ;
default:
System.err.println("UnKnown Data");
columnTypes[column] = Types.NULL ;
break;
}
}
rows.addElement(newRow);
}
/// A Kludge to make everything work when no rows returned
if (rows.size() == 0) {
Vector newRow = new Vector();
for (column = 0; column < numberOfColumns; column++) {
if (columnTypes[column] != Types.NULL) {
newRow.addElement(new Integer(0));
}
}
rows.addElement(newRow);
}
/// Determine max and min values.
if (rows.size() < 1) {
maxValue = 0.0;
minValue = 0.0;
} else {
Vector row = (Vector) rows.elementAt(0);
double test;
maxValue = ((Number) row.get(1)).doubleValue();
minValue = maxValue;
for (int rowNum = 0; rowNum < rows.size(); ++rowNum) {
row = (Vector) rows.elementAt(rowNum);
for (column = 1; column < numberOfColumns; column++) {
test = ((Number) row.get(column)).doubleValue();
if (test < minValue) {
minValue = test;
}
if (test > maxValue) {
maxValue = test;
}
}
}
}
fireDatasetChanged();// Tell the listeners a new table has arrived.
} catch (SQLException ex) {
System.err.println(ex);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
}
}
}
}
Re: JDBCChartAdapter problem
Jim
It would appear that the driver cannot find the database.
When you start your servlet container (eg tomcat) do you have the -D option set? As in:
java -Dcloudscape.system.home=d:/databases
Alternatively you could try setting DBurl to the full path of your DB
eg
from : DBurl=jdbc:cloudscape:CloudscapeDB
to : DBurl=jdbc:cloudscape:d:/databases/CloudscapeDB
Hope this helps
Bryan
It would appear that the driver cannot find the database.
When you start your servlet container (eg tomcat) do you have the -D option set? As in:
java -Dcloudscape.system.home=d:/databases
Alternatively you could try setting DBurl to the full path of your DB
eg
from : DBurl=jdbc:cloudscape:CloudscapeDB
to : DBurl=jdbc:cloudscape:d:/databases/CloudscapeDB
Hope this helps
Bryan
Re: JDBCChartAdapter problem
Thanks Bryan, I fixed it a different way just moments after posting the message (isn't that how it typically goes?). I changed the URL to
jdbc:cloudscape:rmi:CloudscapeDB
Then i started up cloudscape, then the J2ee server and voila. it runs. (the graphs are empty though, but that's probably a data selected issue).
Grateful though,
Jim
jdbc:cloudscape:rmi:CloudscapeDB
Then i started up cloudscape, then the J2ee server and voila. it runs. (the graphs are empty though, but that's probably a data selected issue).
Grateful though,
Jim
Re: JDBCChartAdapter problem
Steve / Wanda
I have just found out that the sourceforge mysql servers are not available externally. Bummer. Well at least a mysql script will be included with WAR in future to make it easier. But it might take a little longer.
Um, steve if you could let me know if the code I sent works it would be appreciated.
thanks
Bryan
I have just found out that the sourceforge mysql servers are not available externally. Bummer. Well at least a mysql script will be included with WAR in future to make it easier. But it might take a little longer.
Um, steve if you could let me know if the code I sent works it would be appreciated.
thanks
Bryan
Re: JDBCChartAdapter problem
Bryan/Steve,
I got the JDBCChartAdapter to correctly retrieve both my Data and Int values from the databasem using your modified code. However, they still don't display the data correctly.
If I select to use the date as X-axis values, it doesn't work. I've tried converting the Date objects to other datatypes, but to no avail. What form does the XYDataset need these values in in order to work?
I got the JDBCChartAdapter to correctly retrieve both my Data and Int values from the databasem using your modified code. However, they still don't display the data correctly.
If I select to use the date as X-axis values, it doesn't work. I've tried converting the Date objects to other datatypes, but to no avail. What form does the XYDataset need these values in in order to work?
Re: JDBCChartAdapter problem
Please excuse my ignorance - I've had a very bad weekend resulting in a hard drive corruption meaning that I've lost my 4 days' work on this.
Anyway, further reading tells me I should not be trying to insert dates into a LineChart but rather use a TimeSeries chart. Can you just point me quickly to the part of the code that I need to modify in order to generate a TimeSeries chart within the servlet demo?
Thanks very much
Wanda
Anyway, further reading tells me I should not be trying to insert dates into a LineChart but rather use a TimeSeries chart. Can you just point me quickly to the part of the code that I need to modify in order to generate a TimeSeries chart within the servlet demo?
Thanks very much
Wanda
Re: JDBCChartAdapter problem
Wanda
Happy that the mods helped you, though not all the way. I will submit to DG shortly for inclusion.
What code are you using JDBCChartAdapter in? Are you extending the demo.jdbc.BaseImageServlet? If you are then, BaseImageServlet does actually create a timeseries chart (see createChart method). If you are extending BaseImageServlet then overriding createChart, you can create any type of chart.
Out of interest, are your servlets which do not display the data correctly available to have a look at?
Bryan
Happy that the mods helped you, though not all the way. I will submit to DG shortly for inclusion.
What code are you using JDBCChartAdapter in? Are you extending the demo.jdbc.BaseImageServlet? If you are then, BaseImageServlet does actually create a timeseries chart (see createChart method). If you are extending BaseImageServlet then overriding createChart, you can create any type of chart.
Out of interest, are your servlets which do not display the data correctly available to have a look at?
Bryan
Re: JDBCChartAdapter problem
Bryan,
Sorry it has taken me so long to reply.
I've just tried you new code, and it seems to work fine.
Your decision to remove the instanceof operator, and replace it with a column type check is much better.
Thanks for your time Bryan.
Steve
Sorry it has taken me so long to reply.
I've just tried you new code, and it seems to work fine.
Your decision to remove the instanceof operator, and replace it with a column type check is much better.
Thanks for your time Bryan.
Steve