[Solved] Fetch type out of range when calling ResultSet.Last

Creating tables and queries
Post Reply
htj6971
Posts: 5
Joined: Wed Jan 11, 2017 11:07 pm

[Solved] Fetch type out of range when calling ResultSet.Last

Post by htj6971 »

Let's start with some pertinent facts:
Running Microsoft SQL Server 2016 on a Windows 10 (x64) and LibreOffice 5.3.3.2 (x64)

Trying to run a Macro from Calc (code snippet below) to get content of a database table called "MyTable"

I need to know how many records/rows are contained in the ResultSet before I start processing.
The issue I am having is that ResultSet.Last() and ResultSet.Previous() are generating an error. Otherwise, the macro works perfectly fine.

All the documentation on OpenOffice and LibreOffice, as well as forum posts on both sites,
indicate that by doing ResultSet.Last() followed by ResultSet.Previous, and then ResultSet.GetRow()
should get me the number of total records/rows in the ResultSet.

Instead, I am getting "Fetch type out of range" error, as indicated in the code comments below.

Code: Select all


ResultFound = 0                                        ' Line 1:
Statement = Connection.createStatement()               ' Line 2: 
ResultSet = Statement.executeQuery(QueryStatement)     ' Line 3: QueryStatement = "Select * from MyTable". Excecutes with NO errors
                                                       '                           MyTable contains 10 Rows. Proof in while loop at line 19


Statement = Connection.createStatement()                                          ' Line 4:
Dummy = Statement.ResultSetType                                                   ' Line 5: Retuns 1003 (Correct)
Statement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE      ' Line 6:
Dummy = Statement.ResultSetType                                                   ' Line 7: Returns 1004 (Correct)

		
If Not IsNull(ResultSet) Then                    ' Line 8:

	Dummy = ResultSet.isBeforeFirst              ' Line 9: Returns True
	Dummy = ResultSet.isFirst                    ' Line 10: Returns False
	Dummy = ResultSet.isLast                     ' Line 11: Returns True (ok. I'll buy it)
	Dummy = ResultSet.isAfterLast                ' Line 12: Returns False
	                                             ' Line 13:
	Dummy = ResultSet.Absolute (-1)              ' Line 14: Theoratically it should point to last row. But generates error "Fetch type out of range"
	Dummy = ResultSet.Relative (-1)              ' Line 15: Theoratically it should point to row before last. But generates error "Fetch type out of range"
	Dummy = ResultSet.Last                       ' Line 16: Should point to last row. But generates error "Fetch type out of range"
	Dummy = ResultSet.Previous                   ' Line 17: Theoratically it should point to row before last. But generates error "Fetch type out of range"
	TotalNumOfRecs = ResultSet.GetRow()          ' Line 18: Works fine, returns -1 (correct)
	Dummy = ResultSet.first                      ' Line 19: Should point to first row. But generates error "Fetch type out of range"



	While ResultSet.Next            ' Line 20:
		                            ' This while loop generates 10 records (rows) when lines 13-18 are commented out

		
	Wend
End If

The macro works perfectly well and returns me the correct result (10 records/rows) when it executes, given that lines 14 through 19 are commented out.


What am I missing? what am I Misunderstanding?

Any help is greatly appreciated.
Last edited by htj6971 on Tue Jun 20, 2017 6:48 pm, edited 2 times in total.
LibreOffice 5.3.3.2 (x64) on windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Fetch type out of range when calling ResultSet.Last

Post by UnklDonald418 »

Using the MRI tool I found that the ResultSet.ResultSetType after the query is 1003 (FORWARD_ONLY), which doesn't allow cursor movement commands first(), last(), relative(), absolute().
You do have the line

Code: Select all

Statement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
however, that line appears after

Code: Select all

ResultSet = Statement.executeQuery(QueryStatement)
If you adjust the ResultSetType before the executeQuery() then the cursor movement commands should work.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
htj6971
Posts: 5
Joined: Wed Jan 11, 2017 11:07 pm

Re: Fetch type out of range when calling ResultSet.Last

Post by htj6971 »

Of course.... It makes all the sense in the world.... How could I have missed it.... :knock:
It works...
Thank you UnklDonald for your time and your help....
LibreOffice 5.3.3.2 (x64) on windows 10
Post Reply