aspose file tools*
The moose likes JDBC and the fly likes SQLException error: Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQLException error: "Invalid Cursor position"" Watch "SQLException error: "Invalid Cursor position"" New topic
Author

SQLException error: "Invalid Cursor position"

bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
my code goes somewhat like this:

queryx ="Select * From cleanTableA order by START_DATE";
ResultSet rsAcd = stmtA.executeQuery(queryx);

while(rsAcd.next())
{
String parseDrugname = rsAcd.getString(4);
int index = parseDrugname.indexOf(" ");
String result = parseDrugname.substring(0,index);
if(!confounderA.equals(result))
{
rsAcd.deleteRow();
//deleting a specific row
}
else
{
System.out.println("do not delete the row");
}

System.out.println(parseDrugname);
}
stmtA.close();
con.close();

Now when i run this code it is not showing the drugname(column data) from the row it is supposed to delete but it is not actually deleting from the the database.

And it also giving an exception error like this:

do not delete the row
IBUPROFEN 600MG TAB
do not delete the row
IBUPROFEN 400MG TAB
do not delete the row
IBUPROFEN 600MG TAB
...(omiting the the name that is deleted)
SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid cursor position;
no keyset defined
Press any key to continue...
Ram Krish
Greenhorn

Joined: Nov 30, 2004
Posts: 23
Hi,

How many records are there in the table?

I guess the problem is in the result set usage...

Thanks
Ram


Thanks,<br />Ram<br />SCEA-1,SCBCD,SCJP 1.4
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
5
out of which 2 are to be deleted....
though i found out a partial solution by now...

like i have made resultset as scrollable and updatable BY:

stmtA = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

...and it is indeed deleting the required rows actually from the table

but i am still gettting the SQLException Error

bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
is it something to do with commit()/rollback each time you delete and update the table....please help

Thanks in advance
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
Is there a reason you aren't using a delete statement instead of looping through each row?

For example,


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
thanks for replying though...,

but i can't do that way because drugnam in thre database is like "IBUPROFEN 200MG TAB or 400MG etc." whereas i have just part of the string i.e. (just the actual drug name "IBUPROFEN")to compare with....

so i have to select each one of them from the table parse the string name (drug name) and then compare with the drug name that user had entered...And if it doesnt match i delete the row and vice versa....


but i now managed to delete the row from the DB, but i am getting an invalid Cursor state error....Can you tel me why? Thanks once again
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
please please ....help
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
You can still have the database do more of the work. The % sign is a wildcard match. So you can tell the database to delete any records that begin with the drug name, a space and then anything else. Essentially, this is what you are doing in your Java program.


The reason I'm harping on this is because letting the database do the work is cleaner, faster and easier to maintain.

Jeanne

Just a note: It's good to give people 24 hours for a reply before bumping a thread. A lot of people, like me, only go on once a day.
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
I followed your Idea of deleting a row by an sql command instead of using a delete method of ResultSet - [DeleteRow() methode]. the idea worked very well..
but in the else portion i again have to delete a specific row based on some criteria and i can't use the same way for it (i.e. SQL command) and i have to use the deleteRow methode. The problem is though it is working but it is givin an SQL error/warning "invalid cursor state". can anyone tell me why?

Thanks
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
What about the "other criteria" can't be turned into SQL?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
I agree with Carol. Almost anything can be represented in SQL. There are a few exceptions, but let's see if yours is one of them before troubleshooting more complex code.
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
But i wanna know why is that whenever i use DeleteRow() method of ResultSet class i gives me "invalid cursor state" even though it is deleting the row from the Db.

i am guessing in the last loop in the while Loop, when it deletes a row Say the last row of the Table and as we know whenver it deletes a row it increments by one position. now when cursor again goes back to
---> while(rsAcd.next()) position, it may tries to locate the next available row in which case it is already ahead of last row.... i believe thats when it fires the exception "invalid cursor state".

am i guessing rt or is there something else??

Thanks for any help!!
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
To be honest, I have no idea. It would depend on the implementation of your driver. I've never encountered that problem because I consider it poor design to delete rows in that fashion.
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
Jeanne, i agree because i am getting this kind of warning:

SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

do you know how to handle this exception
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
i have another question: 1) is JDBC DBC driver does allow scrolling feature because whenever i am using DeleteRow() methode of ResultSet class i am getting and exception specified above. 2)and is there any place i can download this driver or does it come with JDK1.4.2 version....i am basically using Jcreator PRO as my development IDE.

thanks
Bijoy
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
1) This would be in your driver documentation.
2) The driver comes with your database, not with the JDK.
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
HI jeanne

thanks for help first... but do you think I will achieve scrolling feature if i use Rowset or CachedRowSet??

thanks for any help in advance
bijoy bose
Ranch Hand

Joined: Mar 27, 2004
Posts: 32
Thanks FOlKs ATLAST I got it running....actually it was the problem in the driver itself, I was using J2SE 1.4.1 version whereas JDBC 2.0 and above only handles/extends scrollabilty feature in it. so all i have to do is to change my JDK from 1.4.1 to J2SE 1.4.2 version


THANKS to all of you!!!

Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Bijoy,
Thanks for sharing the solution!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQLException error: "Invalid Cursor position"