connecting to openoffice.org base from a form via macro
Base is the database package that comes with openoffice.org 2.x. It seems quite OK as a desktop database package. But, I’ve found that resources for macro editing in basic for base are quite scarce. This is quite a problem since the editor it comes with doesn’t have intellisense/autocomplete. The uno documentation is way too big to be of any real use as a reference document. So I thought I’ll just write a beginners tutorial for connecting to the base database that is currently open from a form using openoffice.org basic macro.
The syntax itself is quite similar to VBA, so it should appear familiar to excel and access macro coders.
Example Scenario
In the example below, we will simply get a reference to our database, execute a simple select query, and retrieve the results.
For this example, we need a database table called MY_TABLE which will need an integer column and a varchar column in that order. We also need a form with a button to invoke the method we will write.
The macro code
We will create a macro to handle the button click from the form above. It will contain a function which requires an event object as its parameter as shown below:
Sub DoSomething(evt as Object)
// code goes here
End Sub
To run our queries we need a reference to our current database. This can be obtained from the form object, which can be obtained through the event parameter passed to our function.
Dim frm As object
frm = evt.Source.Model.Parent
This form object will also come in handy, if you have text boxes etc. you wish to populate with your sql results.
Now that we have a function which gets our form object, we can get on with a few examples.
Making a sql query:
First we create an object to store the results.
dim rows
rows = createUnoService("com.sun.star.sdb.RowSet")
We then get the connection for the database:
rows.ActiveConnection = frm.ActiveConnection
and, set the command type, create a sql statement, and execute it:
rows.CommandType = com.sun.star.sdb.CommandType.COMMAND
rows.Command = "select * from MY_TABLE"
rows.execute()
This puts all the rows from MY_TABLE into our rows object, and we can go through them using the following:
dim col1, col2
while rows.next()
col1 = rows.getInt(1)
col2 = rows.getString(2)
wend
The while loop goes through the records, which you can use to get your data.
This has been a basic intro into running custom queries from a form. Hopefully this will help you get started with your macros.
Nenhum comentário:
Postar um comentário