Ask Your Question
2

Macro (Basic) with Base Table Questions [closed]

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

peterwt gravatar image

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?

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
1

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

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

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 1,065 times

Last updated: Oct 16 '15