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
What am I missing? what am I Misunderstanding?
Any help is greatly appreciated.