Putting a ResultSet into an Array

[George Larry]

Hi...
I have a query that returns 1 field from my db. Like last name...
query = "select distinct name from table";
rs = stmt.executeQuery( query );
Now... I want to create an array of all the names... How can I do that?
I thought I could do this...
String[] listNames = rs.getArray( "name" );
But that's not working...
Thank you for your help,
-GL

[Zac Roberts]

Maybe you need to iterate through your ResultSet, something like this...

 
String[] listNames;
int i = 0;
query = "select distinct name from table";
rs = stmt.executeQuery( query );
while(rs.next()) {
 listNames[i] = rs.getString("name");
 i++;
}

[Hartmut Ludwig]

Originally posted by George Larry:
I thought I could do this...
String[] listNames = rs.getArray( "name" );
But that's not working...

It would work only for SQL3 Datatypes, if the Database column type is SQL ARRAY, which means each field contains an actual array.
But in your case each field contains a String.
To convert your resultset into an array you have to step through the resultset row by row to fetch the values and write them to the array.
So your code should look somehow like this:
query = "select distinct name from table";
rs = stmt.executeQuery(query);
int i = 0;
while (rs.next()) {
listNames[i] = rs.getString(1);
i++
}
make shure the array listNames is large enough. If you are not shure how many names the result contains you can use a "count distinct name from table" first to find it out and size the array correctly, or you could collect the data into a vector and copy the vector into the array.
BTW: This question should actually be posted in the JDBC-forum.
sl
Hartmut

[George Larry]

What if I'm not sure on the length? It could be anywhere from 0 to 100...
I tried this:

String[] listNames;
int i = 0;
query = "select distinct name from table";
rs = stmt.executeQuery( query );
while( rs.next() ) {
  listNames[i] = rs.getString( "name" );
  i++
}
rs.close();
for ( i=0; i < listNames.length; i++ ) {
  out.println( listNames[i] + "<br>" );
}

I'm getting an error:
Variable listDept may not have been initialized (both when I'm trying to set it and when displaying it).
How do I initialize it... I thought I did. Also- Do I have to set the size when I initialize it? If I do that- and set it to 100, for example, and the query returns 4 names... will I get 96 ( null + "<br>" )'s?
Thanks,
-GL

[Thomas Paul]

You didn't initialize listNames. All you did was identify it as a pointer to an array of Strings. You didn't create an array of Strings though.
You need to do something like this before you can add anything to it:
String[] listnames = new String[100];
If you don't know the right size to use, you can do what one of the other posters suggested.

[Jamie Robertson]

You might want to use a Collection of some sort ( probably arraylist if not familiar with them ).

ArrayList listNames = new ArrayList();
query = "select distinct name from table";
rs = stmt.executeQuery( query );
while(rs.next()) 
{ 
   listNames.add(rs.getString("name")); 
}
return listNames; 
// or return listNames.toArray() if necessary

Jamie
[ September 13, 2002: Message edited by: Jamie Robertson ]

[Romin Irani]

1. Use a Vector to hold your resultset
2. Use the method toArray() on the vector. The method returns a Object[].
-Romin

[Jamie Robertson]

Originally posted by Romin Irani:
1. Use a Vector to hold your resultset
2. Use the method toArray() on the vector. The method returns a Object[].
-Romin

Vector's are not really recommended anymore. The Vector class has been retrofitted into the Collections Framework hierarchy to implement the List interface. However, if you are using the new framework, you should use ArrayList, instead. If you need a synchronized list, then synchronize the arraylist ( see
here for more details on synchronizing Lists/Collections )
Jamie

[Ali Hassaan]

You cant use getArray method with JDBC Type 1 Driver. You have to use any driver which implements javax.sql.Array Interface like Oracle 8i JDBC Driver.

[Thomas Paul]

Originally posted by Jamie Robertson:
If you need a synchronized list, then synchronize the arraylist ( see
here for more details on synchronizing Lists/Collections )

Jamie is now officially my buddy!

[Sean MacLean]

Jamie's provided the most common technique of using an ArrayList. This would be the most robust implementation.

public List getNames() throws SQLException { 
  Connection c = null;
  Statement stmt = null;
  ResultSet rset = null;
  try {
    c = getConnectionSomehow();
    // use a List or Collection to better hide your implementation.
    List listNames = new ArrayList();
    String query = "select distinct name from table";
    rset = stmt.executeQuery( query );
    while( rs.next() ) {    
      listNames.add(rs.getString("name")); 
    }
    return listNames; 
    // or return listNames.toArray() if necessary
  }
  finally {
    if( rset != null ) { 
      try { rset.close() }
      catch( SQLException logThis ) {} 
    }
    if( stmt != null ) {     
      try { stmt.close() }
      catch( SQLException logThis ) {}
    } 
    if( c != null ) {
      try { c.close() }
      catch( SQLException logThis ) {}
    } 
  }
  return null;
}