SQLite Result Set problem

[nikosa]

Hello everybody and best wishes for the upcoming new year.

I would like to ask you because I came accross a huge problem for me and I am trying to figure out a way to get rid of it..

I want to execute a simple query or a joint query

String query = "SELECT * FROM Movie";
ResultSet rs = stmt.executeQuery(query);

so far so good but now i want to perform two test to the result set

if(!rs.next()){ //test if the result set is null
return false;
}
else{
rs.last();
int size = rs.getRow(); //get the lines selected from this query

String [] myResult = new String[size];
rs.beforeFirst();
while(rs.next()){
//do something
}
return true;
}
}

I get an error "SQLite only supports TYPE_FORWARD_ONLY cursors"

it is a pretty clear error and there is nothing i can do to fix it.
What I want to ask is for ideas to work aroung this problem!!!

Thank you in advance...

[travishein]

its possible the forward only cursor doesn't like the "result.beforeFirst()", so instead of doing that jump to last rec thing you do with rs.last() first off to get the size, and then rewind it with result.beforeFirst(), and then try to read things, why not do it with just one query, which requires something dynamic to shove the lines in, because we can't intialize the static array.

String query = "SELECT * FROM Movie";
ResultSet rs = stmt.executeQuery(query);

List<String> myResult = new ArrayList<String>();

while(rs.next()) {
  //do something
// e.g.  myResult.add(rs.getString(0));
  }
 
// now, if you want to return true when 1 or more rows found,
return (myResult.size() > 0);

But I don't get why you would want to select things from the table and then return boolean true if there are things and false if not, like wouldn't you want to return the things you selected ?

I guess this could also likely be done in two separate queries, where the first query does a "select count(*) from...." query, which just gets us the size variable, that we could then use for initializing the array of String[] , and then use a second query to fetch the contents.

[nikosa]

hello travishein!!

I would like to thank you for your help. Your idea with the List was very good because I don't have to specify the length of the list in advance. My example was written in the fly and I did not pay enough attention to what I want to return so as you say I should return the selected values rather than a boolean value.

I though for the idea of the two queries using the count but I believe that this approach increases the traffic between the db and the system so I don't wanna go for it.

Thank you very much!!
Have a nice year...

[flaquitqm]

Hello everybody:
I have a problem with a result set too. ResultSet doesn't get all data store in my table, the cursor start in the third row, so I miss rows 1 and 2. How can I put the cursor in the first row in a TYPE_FORWARD_ONLY result set? Here is my code:

TDepartamento dpto = new TDepartamento();
ArrayList<TDepartamento> listaDptos = new ArrayList<TDepartamento>();
try {
sql = "Select * from Departamento order by idDpto";
statement = TConexion.getConexion().prepareStatement(sql);

ResultSet rs = statement.executeQuery();
while (rs.next()){
dpto.setCodigo(rs.getInt("CodigoDpto"));
dpto.setDpto(rs.getString("Dpto"));
listaDptos.add(dpto);
}


return listaDptos;
}

sorry for my english and thank you

[Tolls]

I very much doubt that's the case from the code you've just posted. There is no way that that would result in skipping of rows.

So, either you're not lookin at the db you think you are, or the query isn't the same, or somesuch thing...or that's not the actual code.