Macro (Basic) with Base Table Questions

[peterwt]

Extract from Basic Macro

oConnexion = ThisDatabasedocument.CurrentController.ActiveConnection

sSQL = "(Table Select Query)"

oQuery = oConnexion.createStatement()

oResult = oQuery.executeQuery(sSQL)



oResult.next

oResult.next

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

oResult.last

or

oResult.next

oResult.next

oResult.first

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?

[peterwt]

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

ResultSet.TYPE_FORWARD_ONLY

ResultSet.TYPE_SCROLL_INSENSITIVE

ResultSet.TYPE_SCROLL_SENSITIVE

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

So

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 = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE

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

oResult.first

and

last

next

previous

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

oResult.last

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 http://www.openoffice.org/api/ but I find it is difficult to understand.

[Shaba1]

what do you mean by “sensitive to changes made by a others” What others ?