Ask Your Question

Macro (Basic) with Base Table Questions [closed]

asked 2015-10-12 12:28:57 +0100

peterwt gravatar image

updated 2020-07-21 06:22:56 +0100

Alex Kemp gravatar image

Extract from Basic Macro

oConnexion = ThisDatabasedocument.CurrentController.ActiveConnection
sSQL = "(Table Select Query)"
oQuery = oConnexion.createStatement()
oResult = oQuery.executeQuery(sSQL)
for i = 0 to oResult.columns.count - 1 sMsg = sMsg & " - " & oResult.columns.getByIndex(i).string
next I
msgbox sMsg

This shows in the Message Box the columns from the second row (record).

1) If this is changed to


get error "ResultSet set to forward only" How do you get forward/back navigation on the ResultSet?

2) oResult.columns.count gives the number of columns in the ResultSet and oResult.columns.getByIndex(n) selects column n in the ResultSet. How do you do the same for the rows (records) i.e. the equivalent of oResult.colums for rows?

3) Where is there documentation on these features?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-17 02:43:49.510435

1 Answer

Sort by » oldest newest most voted

answered 2015-10-16 17:21:34 +0100

peterwt gravatar image

I have found some answers to my questions so I will share them here.

1) I have found some documentation relating to Result Sets from SQL queries. There are three scrolling options for them. Forward only allows scrolling forward through the rows (records). Scroll Insensitive allows scrolling forwards and backwards but not sensitive to changes made by others. Scroll Sensitive allows scrolling forwards and backwards and is sensitive to changes made by others.

The scroll type is set by an attribute in

oQuery = oConnexion.createStatement(SCROLL ATTRIBUTE)

The scroll attributes are

If no attribute is entered it defaults to the Forward only.


oQuery = oConnexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE)

should set the Result Set to allow scrolling forwards and backwards but not sensitive to changes made by others. This does not work in LO. I found in the OpenOffice Forums how to do this in LO.

oQuery = oConnexion.createStatement()
oQuery.ResultSetType =

Adding the ResultSetType setting. The values for the ResultSetType are long 1003, 1004, and 1005 for the three scroll Types. You can use the shorter statement

oQuery.ResultSetType = 1004

You can find the ResultSetType

r (long) = oResult.ResultSetType

It appears that ResultSetType 1005 is not implemented in LO as if set to 1005 it returns type 1004 without any error. If set to an invalid number it gives invalid argument error.

With the scroll Type set to Scroll Insensitive you can scroll to any record using


absolute(n) where n is a record number.

2) I have not found a direct method for the equivalent of oResult.columns for rows but you can find the number of records in the result Set using

size = oResult.getRow()

which gets the Row number of the last Row, which is the number of Rows, providing ResultSetType is set to Scroll Insensitive.

3) There is documentation at but I find it is difficult to understand.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-10-12 12:28:57 +0100

Seen: 1,647 times

Last updated: Oct 16 '15