Checking for No Value

[Steve Dyke]

Why won't this work? It never fires any thing but the okay condition. What am I missing that would catch a null/empty resultset?

 
String sql = "Select SUM(hours) As hours From webprddt6.resmngt3 Join webprddt6.resmngt3b On webprddt6.resmngt3.recseq = webprddt6.resmngt3b.taskindex Where assigned = '" + sarray[i][0] + "' And ftdatem >= '" + sDate + "' And ftdatem <= '" + fDate + "' And comdate Is Null";
System.out.println(sql);
ResultSet rsa = stmt.executeQuery(sql);
if(rsa.next()){
System.out.println(rsa.getString(1));

if(rsa.getString(1).trim().equals(null)){
System.out.println("Here at null");
sarray[i][1] = "0";
}
else if(rsa.getString(1).trim().equals("")){
System.out.println("Here at equals empty");
sarray[i][1] = "0";
}
else if(rsa.getString(1).trim() == ""){
System.out.println("Here at empty");
sarray[i][1] = "0";
}
else if(rsa.getString(1).trim() == null){
System.out.println("Here at null2");
sarray[i][1] = "0";
}
else if(rsa.getString(1).equals("0")){
System.out.println("Here at 0");
sarray[i][1] = "0";
}
else if(rsa.getString(1) == "0"){
System.out.println("Here at 0b");
sarray[i][1] = "0";
}
else{
System.out.println("Here at ok");
sarray[i][1] = rsa.getString(1);
}

}
else{
sarray[i][1] = "0";

}
 

[Ernest Friedman-Hill]

Well, perhaps stating the obvious: if you have an empty (no rows) result set, that first "if (rsa.next())" will return false, and you'll get to that one condition that doesn't print anything. Several of those other conditions would throw a NullPointerException if getString() were ever to return null, as you're anticipating.

[Steve Dyke]

Wouldn't the if else which I have take care of that?

if(rsa.next()){
}
else{
sarray[i][1] = "0";

}

[Bill Shirley]

Why doesn't it work for what?

Are you trying to only check the first item in the first column?
That's what you're doing.

There's no loop here.

[Steve Dyke]

Then why do I get an output of:

System.out.println("Here at ok");

If there is no loop?

What would you suggest to fix this?

[Ernest Friedman-Hill]

Originally posted by Steve Dyke:
Then why do I get an output of:

System.out.println("Here at ok");

If there is no loop?

What would you suggest to fix this?

You see that output if that column in the first row of the result set contains valid data. The loop referred to would be need to process any additional rows.

As far as how to fix it: depends what the code is trying to do, exactly. If all you need is one value from one row, then just get rid of all the "null" stuff, and have 'if (rsa.next()) { /* THE OK PART */ } else { /* THE EMPTY-RESULT-SET PART */}'

[Steve Dyke]

If you look at my code and disreguard the null stuff don't I have exactly what you are suggesting?

The good part is running but the return feild value is null/empty.
[ May 15, 2008: Message edited by: Steve Dyke ]

[Bill Shirley]

You haven't shown us an entire method, or a return statement. (Or shown us where sarray came from, or stated what it is supposed to contain.)

More information is needed (by us to help you).

[Steve Dyke]

This is my class. What is happening is invalid value is being generated for the sarray[i][1] either a null or empty something and the dataset creater cannot read:

 
public class DepartmentWorkloadChart {
public JFreeChart chart;
private String[][] array1;
private String whatDept;
private String whatDeptMod;
private String whatOTP;
private String whatSD;
private Date nd = null;
private String sDate;
private String fDate;


public DepartmentWorkloadChart(String arg0, String arg1, String arg2){

if(arg0.endsWith("+")){
this.whatDept = arg0.substring(0,arg0.length()-1);
}
else{
this.whatDept = arg0;
}
this.whatDeptMod = arg0;
this.whatOTP = arg1;
//arg2 = "40";
//this.whatSD = arg2;

//try{
//nd = new SimpleDateFormat("MM/dd/yyyy").parse(this.whatSD);
//}
//catch(ParseException pe){}

Date d = new Date();
Calendar cal = Calendar.getInstance();
cal.setTime(d);
cal.add(Calendar.DATE, Integer.parseInt(arg2));
Date newDate = cal.getTime();

Date date = new Date();
//Date date2 = new Date(date.);
Format formatter;
Format formatter2;
formatter = new SimpleDateFormat("MMddyy");
formatter2 = new SimpleDateFormat("MM/dd/yyyy");
String dateString = formatter.format(date);
this.sDate = formatter2.format(date);
this.fDate = formatter2.format(newDate);

chart = createOverlaidChart();


      
}

private JFreeChart createOverlaidChart() {
 
        // create plot ...
this.array1 = scheduledarray();
        final CategoryDataset data1 = createDataset1();
        final CategoryItemRenderer renderer1 = new BarRenderer();
        //final ClusteredXYBarRenderer renderer1 = new ClusteredXYBarRenderer(0.5, true);
        
        //renderer1.setToolTipGenerator(
            //new StandardXYToolTipGenerator(
                //StandardXYToolTipGenerator.DEFAULT_TOOL_TIP_FORMAT,
                //new SimpleDateFormat("d-MMM-yy"), new DecimalFormat("0.00")
            //)
        //);
        
        //final DateAxis domainAxis = new DateAxis("Month");
        final CategoryAxis domainAxis = new CategoryAxis("Individual");
        //domainAxis.setTickMarkPosition(DateTickMarkPosition.START);
        Font f = domainAxis.getTickLabelFont();
        String fName = f.getFontName();
        Font newFont = new Font(fName, f.getStyle(), (f.getSize() - 2));
        domainAxis.setTickLabelFont(newFont);
 
        //domainAxis.setDateFormatOverride(new SimpleDateFormat("MMM-yy"));
        final ValueAxis rangeAxis = new NumberAxis("Hours");
        final CategoryPlot plot = new CategoryPlot();
        plot.setDomainAxis(domainAxis);
        plot.setRangeAxis(new NumberAxis("Hours"));
        plot.setDataset(data1);
        plot.setRenderer(renderer1);
        
        // add a second dataset and renderer...
        //final XYDataset data2 = createDataset2();
        //final XYItemRenderer renderer2 = new StandardXYItemRenderer();
        //renderer2.setToolTipGenerator(
           // new StandardXYToolTipGenerator(
                //StandardXYToolTipGenerator.DEFAULT_TOOL_TIP_FORMAT,
               // new SimpleDateFormat("d-MMM-yy"), new DecimalFormat("0.00")
            //)
        //);
        
        //renderer2.setSeriesPaint(0,Color.GREEN.darker());
        //plot.setDataset(1, data2);
        
        //plot.setRenderer(1, renderer2);
        
        //if(!this.whatOTP.equals("")){
        // add a third dataset and renderer...
        //final XYDataset data3 = createDataset3();
        //final XYItemRenderer renderer3 = new StandardXYItemRenderer();
        //renderer3.setToolTipGenerator(
        //new StandardXYToolTipGenerator(
        //StandardXYToolTipGenerator.DEFAULT_TOOL_TIP_FORMAT,
//new SimpleDateFormat("d-MMM-yy"), new DecimalFormat("0.00")
        //)

        //);
        //renderer3.setSeriesPaint(0,Color.red);
        //renderer3.setSeriesStroke(
        //0, new BasicStroke(
        //2.0f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_ROUND,
//1.0f, new float[] {2.0f, 6.0f}, 0.0f
        //)
            //);
 
        //plot.setDataset(2, data3);
        //plot.setRenderer(2, renderer3);
        //}
        
        plot.setDatasetRenderingOrder(DatasetRenderingOrder.FORWARD);
                
        // return a new chart containing the overlaid plot...
        return new JFreeChart("Individual Workload for  :(   " + whatDeptMod + ")", JFreeChart.DEFAULT_TITLE_FONT, plot, true);
 
    }

private String[][] scheduledarray(){

AS400JDBCDataSource datasource = new AS400JDBCDataSource("gvas400");
datasource.setTranslateBinary(true);
datasource.setUser("***");
datasource.setPassword("***");

GetCertificationConnection gdlConn = new GetCertificationConnection();
String[] getDeptList = gdlConn.userIdList.split(";");


String[][] sarray = new String[getDeptList.length][3];

int i =0;
for(i=0;i<getDeptList.length;i++){
sarray[i][0] = getDeptList[i].trim();

try{

Connection connection = datasource.getConnection();
Statement stmt = connection.createStatement();

String sql = "Select SUM(hours) As hours From webprddt6.resmngt3 Join webprddt6.resmngt3b On webprddt6.resmngt3.recseq = webprddt6.resmngt3b.taskindex Where assigned = '" + sarray[i][0] + "' And ftdatem >= '" + sDate + "' And ftdatem <= '" + fDate + "' And comdate Is Null";
ResultSet rsa = stmt.executeQuery(sql);

if(rsa.next()){
sarray[i][1] = rsa.getString(1);
}
else{
sarray[i][1] = "0";
}

rsa.close();

stmt.close();
connection.close();

}
catch (SQLException sqle){
System.out.println(sqle);

}

}

return sarray;

}

private DefaultCategoryDataset createDataset1() {

final DefaultCategoryDataset series1 = new DefaultCategoryDataset();

int i;
for(i=0;i<this.array1.length;i++){
series1.addValue(Integer.parseInt(this.array1[i][1]), "Scheduled", this.array1[i][0]);
}

        
        return series1;
 
    }

//private XYDataset createDataset2() {
 
        // create dataset 2...

//final TimeTableXYDataset series2 = new TimeTableXYDataset();

//Date cd = new Date();
//Calendar cal = Calendar.getInstance();
//cal.setTime(nd);
 
//int i;
//for(i=0;i<12;i++){

//int nowMonth = cal.get(Calendar.MONTH) + 1;
//int nowYear = cal.get(Calendar.YEAR);
//series2.add(new Month(nowMonth, nowYear), this.array1[i][2],"Capacity");
//cal.add(Calendar.MONTH, 1);
//}

//return series2;
 
    //}

//private XYDataset createDataset3() {
 
        // create dataset 3...

//final TimeTableXYDataset series3 = new TimeTableXYDataset();

//Date cd = new Date();
//Calendar cal = Calendar.getInstance();
//cal.setTime(nd);
 
//int i;
//for(i=0;i<12;i++){

//int nowMonth = cal.get(Calendar.MONTH) + 1;
//int nowYear = cal.get(Calendar.YEAR);
//series3.add(new Month(nowMonth, nowYear), this.array1[i][2] + ((this.array1[i][2] * (Integer.parseInt(this.whatOTP))/100)),"Capacity w/" + this.whatOTP + "% Overtime");
//cal.add(Calendar.MONTH, 1);
//}
        
//return series3;
 
    //}
}
 
 

[ May 15, 2008: Message edited by: Steve Dyke ]

[Stevi Deter]

Steve,

I don't see that you're ever actually checking that the field itself is null, just that a trimmed version of a valid string returned by getString(1) isn't null.

[Steve Dyke]

If you look at my earlier post I had field checking(I thought) but it was suggested I take it out. What do I need to do to check it?

[Bill Shirley]

Is this where you're getting non-strings?

sarray[i][1] = rsa.getString(1);

Do you want to default to zero, or something else to let you know there is an error?

String sumResult = rsa.getString(1);
sarray[i][1] = (sumResult == null) ? "-1" : sumResult;