JdbcCategoryDataset doesnt work... why??

[DM]

Hi,

Here is the snippet of code i use for both JdbcXYDataset and JdbcCategoryDataset. The funny thing is that it works for JdbcXYDataset but not for the other one. can someone tell me why? I would really appreciate someone's help in this.

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:reports");
stmt = con.createStatement();
data = new JdbcCategoryDataset(con);
qry = "SELECT [tbl_Stats Summed By Day].Date, Sum([tbl_Stats Summed By Day].[Chargable Call]) AS [SumOfChargable Call] ";
qry = qry + "FROM [tbl_Stats Summed By Day] ";
qry = qry + "GROUP BY [tbl_Stats Summed By Day].Date, ";
qry = qry + "[tbl_Stats Summed By Day].[Client Reference] ";
qry = qry + "HAVING ((([tbl_Stats Summed By Day].Date) Between #" + frmDate + "# And #" + toDate+"#) ";
qry = qry + "AND (([tbl_Stats Summed By Day].[Client Reference])= '" + client + "'));";
data.executeQuery(qry);
con.close();
}catch (Exception e) {System.out.println("Error" + e.toString());}

[David Gilbert]

JdbcCategoryDataset expects the first column to contain the category. Off the top of my head, I don't see why it can't be a date, as long as the dates are unique. What does your typical data look like, and what error message do you get?

Regards,

Dave Gilbert

[DM]

Hi Dave,

The table looks like this:

Date Client_Ref Rate Chargable Call
7/01/01 PAINFO_159 $0.00 63
7/01/01 PAINFO_159 $0.00 8
8/01/01 ANNETAS_247 $4.95 4
8/01/01 ASIANA_197 $4.95 34
8/01/01 INSIDEM_78 $0.00 1
8/01/01 INSIDEM_78 $0.00 1
9/01/01 HOWR_46 $0.00 100
10/01/01 HOWR_46 $0.00 121


.
.
.

and the result I get shd be something like this...

Date SumOfChargable Call Client Reference
1/06/02 529 HOWR_46
2/06/02 404 HOWR_46
3/06/02 477 HOWR_46
4/06/02 520 HOWR_46



When I run the servlet, all i get is a blank graph and in the logfile the error message that I get is : java.sql.SQLException: No data found

If there is a problem with the SQL, then JdbcXYDataset should have picked it up as well. And other thing is that the sql statement is within the try catch block. This exception is not being caught. Do you know if I am doing something wrong.

Thanks.

[DM]

Hi David,

Did you have a change to look at the above posting? Can u let me know what the problem is? or could there be a possible bug?

[David Gilbert]

I'll try to take a look tomorrow. Sorry for the delay, my test server is undergoing an upgrade, so I haven't had a database handy to test with...but it should be back online tomorrow.

Regards,

Dave Gilbert

[David Gilbert]

I took a look at this, although I'm still not sure what is causing the problem. The JdbcCategoryDataset class will read series names from the first column, and category names from the column names of all remaining columns that contain numerical data.

For your data:

Date SumOfChargable Call Client Reference
1/06/02 529 HOWR_46
2/06/02 404 HOWR_46
3/06/02 477 HOWR_46
4/06/02 520 HOWR_46

...I would expect you to get four series (with names equal to the String version of the dates in column 1) and one category ("SumOfChargable") from the second column (the third column is ignored because it doesn't contain numerical data).

I wonder if your JDBC driver is not reporting the column type correctly (maybe it thinks the second column contains strings not numbers). I'd recommend that you single-step through the code and check the column types.

Regards,

Dave Gilbert