My "For" loop returns empty list

[Bojan]

Hello,
I have a JAVA function that has two lists (Companies and Applications) as input parameters. Based on those input parameters, I have two "for" loops - one nested in another. That is because I want to check every input Application in every Company. Inside the "for" loop, I read from a database, and the result (that can be only one element) is put in a list called "list". So basically, the list gets filled with every iteration of the for cycles (assuming that the database check returned some value, otherwise, the list stays as it is, and i check the next element from the loops). My problem is: Every time i have more than one element in each of the "for" cycles, I get an empty list as a result. But if the input lists (Companies and Applications) are each filled with only one element, then the result returns a list with one element (which is correct). It seems like when I have more elements in the input lists, the result list gets overwritten and returns empty. Why is that?
Thank you in advance. Now here is the code:


@Function
public String[] GetEmployeeApplication(ServiceContext sc,
@Parameter String SQLServer, @Parameter String DatabaseName,
@Parameter String UserName, @Parameter String Password,
@Parameter @UserDataType String SourceUser,
@Parameter String[] Applications,
@Parameter String[] Companies,
@Parameter java.sql.Timestamp From_Date,
@Parameter java.sql.Timestamp To_Date) {
Connection con = getConnection("jdbc:sqlserver://"+SQLServer+";databaseName=" +DatabaseName+ ";user="+UserName+";password="+Password);

String Application = "";
String Employees = SourceUser;
String[] mApplication = Applications;
String[] mCompany = Companies;
java.sql.Timestamp from_date = From_Date;
java.sql.Timestamp to_date = To_Date;
java.util.ArrayList<String> list = new ArrayList<String>();
try {

for (int i = 0; i < mCompany.length; i++) {

for (int j = 0; j < mApplication.length; j++) {


Statement stm = con.createStatement();
ResultSet result = stm
.executeQuery("SELECT a_id,sourceuser,alternativeuser,fromdate,todate,ap plication,company FROM alternativeusers1 where sourceuser = '"
+ Employees + "' and application = '" + mApplication[j] + "' and company = '" + mCompany[i]
+ "' and (('" + from_date + "' between fromdate and todate) or ('" + to_date + "' between fromdate and todate)) ");


Application = new String();
Application = "";

while (result.next())
{
Application = result.getString("application");
}

if (Application != "")
{
String App = new String();
App = Application;
list.add(App);
}

result = null;
stm.close();
}
}
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String[] returns = new String[list.size()];

for(int m=0;m<list.size();m++)
{
returns[m] = (String)list.get(m);
}


return returns;

}

[SurfMan]

You should make it a habit to test Strings with the equals method (or equalsIgnoreCase()), or any other utility method. So checking for an empty String is best done by application.isEmpty(). Checking application.equals("") is also valid to test for an empty String.

[Bojan]

Thank you so much for the suggestion. I tried it, but it didn't work. I still get an empty list. And the biggest problem is that I can not debug the function because I use it like a JAR file...I don't have access to the database from this server (but the SQL rule is OK, because it returns correctly - that is if the lists are each filled only with one element). If you have any other suggestions, please write them.
Regards

[SurfMan]

Your loop assigns a value to Application all the time, which basically does nothing and only the last value of Application is used. Move your testing code inside the loop.

while (result.next()) {
   application = result.getString("application");

   if (application != null && !application.isEmpty()) {
      list.add(application);
   } 
}

[Bojan]

I have tried that also. The reason why I left it outside is because I know that the result has only one element. Even if I put the testing code inside the loop, I still get an empty list. I don't know what I am doing wrong...
Thank you for all your effort. Any idea will be very appreciated.

[SurfMan]

Post the code you currently have so we can see the modifications you made. And put them in [ code ][ /code ] tags.

[Tolls]

Can I just say that that query is crying out to be a PreparedStatement.
Are fromdate and todate in the database DATE columns?

[Bojan]

The version that I currently have is the one that I posted, but I tested many versions before (i tried one where testing code was inside the loop...i tried the function with only one "for" loop...) And every time, the function works only if the input lists are filled with one element. Otherwise I get an empty list as a result.
The "fromdate" and "todate" in the database are DATE columns. I know that the SQL rule works, because it returns the correct element, but when I use the loop and when I have multiple elements in the input list, I always get empty list as a result.
What is a "PreparedStatement"?
I very much appreciate your suggestions. Thank you.

[Tolls]

PreparedStatement.
It removes any possible errors with quoting, or (as could be happening in your case) datatypes.

As for your problem, what does "the function works only if the input lists are filled with one element" mean?
What "input lists"?

[SurfMan]

If the current version is the code you first posted, then why didn't you make any changes? Writing if (Application != "") is wrong. It might work, but I wouldn't bet on it. For the sake of proving my point, run this code and tell me what you see:

        String a = new String("");
        System.out.println(a == "");
        System.out.println(a.equals(""));
        System.out.println(a.isEmpty());

[Bojan]

I have two input lists (Companies and Applications). I have to check for every application in every company, so that's why I have two "for" loops - one nested in another. And in the loop, I have a SQL statement that gives me a result with only one element-if the statement is true, or no element-if the statement is false. After that, I store that result in a list, and the loop iterates to the next element in the input list until every element from the lists is checked.
if the input lists are each filled with only one element, then the result-list gives me back one element which is correct.
If the input lists have multiple values in them, then the result-list is empty. That is not correct and is very frustrating :).
Please HELP :)

[Bojan]

I mentioned before that I implemented your suggestion, but that it didn't work. Here is my code:

@Function
	public String[] GetEmployeeApplication(ServiceContext sc,
			@Parameter String SQLServer, @Parameter String DatabaseName,
			@Parameter String UserName, @Parameter String Password,
			@Parameter @UserDataType String SourceUser,
			@Parameter String[] Applications,
			@Parameter String[] Companies, 
			@Parameter java.sql.Timestamp  From_Date,
			@Parameter java.sql.Timestamp  To_Date) {
	Connection con = getConnection("jdbc:sqlserver://"+SQLServer+";databaseName=" +DatabaseName+ ";user="+UserName+";password="+Password);
		
		String Application = "";
		String Employees = SourceUser;		
		String[] mApplication = Applications; 
		String[] mCompany = Companies; 
		java.sql.Timestamp  from_date = From_Date;
		java.sql.Timestamp  to_date = To_Date;
		java.util.ArrayList<String> list = new ArrayList<String>(); 		
		try {			
			for (int i = 0; i < mCompany.length; i++) {

				for (int j = 0; j < mApplication.length; j++) {	
					
					Statement stm = con.createStatement();				
					ResultSet result = stm
						.executeQuery("SELECT a_id,sourceuser,alternativeuser,fromdate,todate,application,company FROM alternativeusers1  where sourceuser = '"
								+ Employees + "' and application = '" + mApplication[j] + "' and company = '" + mCompany[i]
								+ "'   and (('" + from_date + "' between fromdate and todate) or ('" + to_date + "' between fromdate and todate)) ");
			
				
					Application = new String();	
					Application = "";
					while (result.next())
					{					
						Application = result.getString("application");							
					}						
					
					if (Application.isEmpty()== false)
					{				
						String App = new String();
						App = Application;	
						list.add(App);
					}	
					result = null;
					stm.close();
					}
				}
			con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				}

		String[] returns = new String[list.size()];
		for(int m=0;m<list.size();m++)
		{
			returns[m] = (String)list.get(m);
		}
	
		
		return returns;
		 
	}

[Tolls]

Please wrap your code in [code] tags [/code] so it retains its formatting.
It's very hard to read unformatted code (which is why I couldn't see what lists you were talking about).

How big are these two lists likely to get?
Because this should really be a single select statement.

[Bojan]

The first list (Companies) can have maximum 2 elements, and the second one (Applications) can have more - I don't know the exact number.
I modified the function to see if it will work with only one "for" loop, but it didn't help.
The SQL select is a single select - I only get one element and after that I put it in a output list... If I put one element in each of the input lists, the output list is shown good (with only one element that is a result from the SELECT statement in the one iteration made). But if I put multiple elements in the input lists, I get an empty output list. Here lies the rabbit :)

[Tolls]

No it's not.
You are calling the SELECT, inside that method, companies.size() * application.size() times.
Since you are putting all these results into a single List, this should be a single SELECT call.

Since the numbers are less than a thousand I would build a single SELECT statement with two IN clauses covering the Company list and the Application List.

Do the contents of these Lists change much between calls?

And if you think I'm not answering your problem, that is partly because I still cannot read your code, and partly because your code is not going about this in the best way, so the error quite likely lies in the route you have chosen to take.

[SurfMan]

Is there supposed to be a space in "ap plication" in your query?

[SurfMan]

I couldn't bear reading this, so I cleaned it up a little.
- Renamed variables to start lowercase
- Changed the application.isEmpty() == false to !application.isEmtpy()
- The ResultSet and Statement are now closed
- The returned array is generated by Java

Now, for the code, to me it looks weird and wrong that result.getString("application") is inside a while loop by itself. That is only useful for getting the last record only in a very expensive way. And then it still doesn't make sense. Add some debug lines to see what the actual query is, some System.out.println's to see how many records you are looping through, etc.

Connection con = getConnection("jdbc:sqlserver://" + SQLServer + ";databaseName=" + DatabaseName + ";user=" + UserName + ";password=" + Password);

        String application = "";
        String employees = sourceUser;
        String[] mApplication = Applications;
        String[] mCompany = Companies;
        java.sql.Timestamp from_date = From_Date;
        java.sql.Timestamp to_date = To_Date;

        Statement stm = null;
        ResultSet result = null;

        List<String> list = new ArrayList<String>();
        try {
            for (int i = 0; i < mCompany.length; i++) {
                for (int j = 0; j < mApplication.length; j++) {
                    stm = con.createStatement();
                    result = stm.executeQuery("SELECT a_id,sourceuser,alternativeuser,fromdate,todate,ap plication,company FROM alternativeusers1 where sourceuser = '"
                            + employees + "' and application = '" + mApplication[j] + "' and company = '" + mCompany[i]
                            + "' and (('" + from_date + "' between fromdate and todate) or ('" + to_date + "' between fromdate and todate)) ");

                    while (result.next()) {
                        application = result.getString("application");
                    }

                    if (! application.isEmpty() ) {
                        list.add(application);
                    }
                    result.close();
                    stm.close();
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            if (result != null) {
                try {
                    result.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            try {
                con.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list.toArray(new String[list.size()]);

[Tolls]

Is there supposed to be a space in "ap plication" in your query?

Forum software seems to cut long lines like that for some odd reason.
I've seen it in posted stack traces before.