I can connect to MSSQL Server 2000 using the example mssql-service.xml and the MS JDBC driver.
Within my code I can enter data. I can then retrieve some data into a recordSet showing the data I have entered. But when I try a do another noddy SQL statement it fails with :-
2002-11-22 11:01:12,568 INFO [STDOUT] select * from TblJLIDSEntry
2002-11-22 11:01:12,584 ERROR [STDERR] java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid operation for the current cursor position.
2002-11-22 11:01:12,584 ERROR [STDERR] at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
2002-11-22 11:01:12,584 ERROR [STDERR] at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
2002-11-22 11:01:12,584 ERROR [STDERR] at com.microsoft.jdbc.base.BaseResultSet.validateCursorPosition(Unknown Source)
2002-11-22 11:01:12,584 ERROR [STDERR] at com.microsoft.jdbc.base.BaseResultSet.getString(Unknown Source)
2002-11-22 11:01:12,584 ERROR [STDERR] at jlids.jlidSession.jlidStateBean.getJLID(jlidStateBean.java:1180)
2002-11-22 11:01:12,584 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2002-11-22 11:01:12,599 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
It is as if there has been nothing returned from the 'select * from TblJLIDSEntry' statement due to TblJLIDSEntry being empty.
But there is data in it as inspecting the table in SQLServer, running it in the SQL Query analyser, MS Access and previously in my code verifies this.
I have a few of questions:
Has anbody else seen this?
How can I debug this to see what is going on?
Do I have to reset the cursor position?
BTW, The same code works fine using MySQL
The problem is now fixed:
The solution was to put the recordset code within a while loop.
Incidentally the documentation for the MS JDBC states that it supports some methods which it does not - for example first().