Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Putting a ResultSet into an Array

 
George Larry
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you need to iterate through your ResultSet, something like this...
 
Hartmut Ludwig
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What if I'm not sure on the length? It could be anywhere from 0 to 100...
I tried this:

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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might want to use a Collection of some sort ( probably arraylist if not familiar with them ).

Jamie
[ September 13, 2002: Message edited by: Jamie Robertson ]
 
Romin Irani
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. Use a Vector to hold your resultset
2. Use the method toArray() on the vector. The method returns a Object[].
-Romin
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Ranch Hand
Posts: 621
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jamie's provided the most common technique of using an ArrayList. This would be the most robust implementation.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic