Hi,
I am trying to prototype a pie-chart based on a query.
The query is generated by a reference cursor like:
SQL>
var rc refcursor
SQL>
exec :rc := chart_demo.get_data
SQL>
print rc
DEPT_NAME COUNT_EMP
-------------- ----------
ACCOUNTING 3
RESEARCH 5
SALES 6
I wish to create a pi-chart based on such a two-column query (description + value).
I create for this a base-java source in the database as:
create or replace and compile java source named "SourceDemoPieChart"
as
package demo;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.AxisLocation;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.general.PieDataset;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class ChartDemo {
private static PieDataset getDataset(Connection conn) {
DefaultPieDataset dataset = new DefaultPieyDataset();
final String rowKey = "1";
try {
OracleCallableStatement stmt = (OracleCallableStatement) conn.prepareCall("{? = call chart_demo.get_data}");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = stmt.getCursor(1);
while (rs.next()) {
dataset.addValue(rs.getDouble(2), rowKey, rs.getString(1));
}
rs.close();
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return dataset;
}
private static JFreeChart createChart(PieDataset dataset) {
/* 1: title; 2: legend displayed true/false; 3: tooltip displayed true/false; 4: urls true/false */
JFreeChart chart = ChartFactory.createPieChart("My title", dataset, true, true, false);
return chart;
}
public static Blob getChartImage(int height, int width) throws SQLException, IOException {
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
Blob image = conn.createBlob();
OutputStream os = image.setBinaryStream(1L);
ChartUtilities.writeChartAsPNG(os, createChart(getDataset(conn)), width, height);
os.close();
return image;
}
}
/
I used the information found at:
http://www.jfree.org/jfreechart/api/javadoc/index.html
I have limited knownledge of Java, and when I try to create the Java source, I get the following errors:
Warning: Java created with compilation errors.
SQL> show errors
Errors for JAVA SOURCE "SourceDemoPieChart":
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 2 errors
0/0 ^
0/0 dataset.addValue(rs.getDouble(2), rowKey, rs.getString(1));
0/0 location: class org.jfree.data.general.DefaultPieDataset
0/0 symbol : method
addValue(double,java.lang.String,java.lang.String)
0/0 SourceDemoPieChart:31: cannot find symbol
0/0 ^
0/0 DefaultPieDataset dataset = new DefaultPieyDataset();
0/0 location: class demo.ChartDemo
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 symbol : class DefaultPieyDataset
0/0 SourceDemoPieChart:41: cannot find symbol
SQL>
Can someone explain me what is wrong?
Is is a missing library or the wrong code passing the SQL as parameter?
Thanks by advance for any tip or indications.
Kind Regards