retuning values from a db

[vomhaus]

I developed a method to query a database and return the specific values that I'm looking for except that the value being returned is the last value. In my while loop I can do a system.out.println(answers)and I can see all of the values, my return answers only returns the last value.

Any ideas?

public String getAnswers() throws DAOException, SQLException{
		//This still needs work
		String testName = "Test", formName = "Form";
		String sql = "select item, itemkey from dbo.TestMaster " + 
        "where TestName = ? and FormName = ? " + 
        "order by item ";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String answers = null;
		try {
			//Define the db to connect to
			String dbConnect = "jdbc:sqlserver://MPSQL8B\\INST2;DatabaseName=iTest_Qa_UT;"  + " integratedSecurity=false;";
			//Fetch the sql class
			String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
			//Create new instance of class
			try {
				Class.forName(driverClass).newInstance();
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//Define User name and password...
			String uName = "asdasd";
			String pWord = "asdasd";
			//Connection result = null;
			//Login to the db
			conn = DriverManager.getConnection(dbConnect, uName, pWord);
			//Statement to submit sql
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, testName);
			ps.setString(2, formName);
			
			rs = ps.executeQuery();			
	      
	      	while ( rs.next() ) {
				answers = rs.getString("itemKey");
				System.out.println(answers);
	      	}   
	      	
		}finally {
			release(rs, ps, conn);
		}
		
		return answers;
		
	}

[NormR1]

How many different values are assigned to the answers variable?

When multiple values are assigned to a variable, the variable will contain only the last one assigned. The previous values are gone.
int i = 1;
i = 2;
i = 3;
// Here i is 3, the values 1 and 2 are gone.

[msi_333]

answers = rs.getString("itemKey");

You have to use arraylist of strings .

[murali_quest]

//Here I have edited little in your code.. Instead of string i have used string array.

public String[] getAnswers() throws DAOException, SQLException{

		//This still needs work

		String testName = "Test", formName = "Form";

		String sql = "select item, itemkey from dbo.TestMaster " + 

        "where TestName = ? and FormName = ? " + 

        "order by item ";

		Connection conn = null;

		PreparedStatement ps = null;

		ResultSet rs = null;

		String answers[] = null;

		try {

			//Define the db to connect to

			String dbConnect = "jdbc:sqlserver://MPSQL8B\\INST2;DatabaseName=iTest_Qa_UT;"  + " integratedSecurity=false;";

			//Fetch the sql class

			String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

			//Create new instance of class

			try {

				Class.forName(driverClass).newInstance();

			} catch (InstantiationException e) {

				// TODO Auto-generated catch block

				e.printStackTrace();

			} catch (IllegalAccessException e) {

				// TODO Auto-generated catch block

				e.printStackTrace();

			} catch (ClassNotFoundException e) {

				// TODO Auto-generated catch block

				e.printStackTrace();

			}

			//Define User name and password...

			String uName = "asdasd";

			String pWord = "asdasd";

			//Connection result = null;

			//Login to the db

			conn = DriverManager.getConnection(dbConnect, uName, pWord);

			//Statement to submit sql

			ps = conn.prepareStatement(sql);

 

			ps.setString(1, testName);

			ps.setString(2, formName);

 

			rs = ps.executeQuery();			

         int row_count=0; 
			while ( rs.next() ){            //To fix the length for String array.
				
				row_count++;
			}

     	rs = ps.executeQuery();  // To reset the cursor into begining.			

             answers=new String[row_count];    

	          int array_index=0;

	      	while ( rs.next() ) {

				answers[i] = rs.getString("itemKey");

				System.out.println(answers[i]);

				i++;


	      	}   

 

		}finally {

			release(rs, ps, conn);

		}

 

		return answers;         //Here string array has been returned. so recieve the value using string array while calling.

 	}

[murali_quest]

Please use array_index instead of variable i. Wrongly i have used it.

[JamesCherrill]

Previous code is a good example of why an ArrayList is a better choice than an array for returning an unknown number of Objects - there's no need to size it, just keep adding Objects until they are all added.

[murali_quest]

Yes james, ArrayList is best.