Base programming

Sub Main
‘ assumption: the database is registered, if it is not the code will not work
‘ to check if it registered: tools – options – openoffice.org base – databases
Dim RowSet
RowSet = createUnoService(“com.sun.star.sdb.RowSet”)
RowSet.DataSourceName = “test01″
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
RowSet.Command = “SELECT “”firstname”", “”lastname”" from “”users”"”
RowSet.execute()
‘ loop through the resultset until no record is left
while RowSet.next()
MsgBox “User: ” + rowSet.getString(1) + ” ” + + rowSet.getString(2)
wend
End Sub

I have had a hard time finding stuff on the Internet explaining how to do database manipulation programmatically, but here is a start.

Running a select query and displaying the result

In the database test01 I have a table called users with fields firstname and lastname.

Sub Main
    ' assumption: the database is registered, if it is not the code will not work
    ' to check if it registered: tools - options - openoffice.org base - databases
    Dim RowSet
    RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = "test01"
    RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
    RowSet.Command = "SELECT ""firstname"", ""lastname"" from ""users"""
    RowSet.execute()

    ' loop through the resultset until no record is left
    while RowSet.next()
        MsgBox "User: " + rowSet.getString(1) + " " + + rowSet.getString(2)
    wend
End Sub

IsNull(RowSet) is true if no records were returned, but it would be nice to know how to find in general the number of records returned. One option is to run this query first:

RowSet.Command = "select count(*) from users"

and then use

   while RowSet.next()

        MsgBox "#records: " + rowSet.getInt(1)

    wend

I found help here.

Running an insert query

In the database test01 I have a table called users with fields firstname and lastname. I want to insert a record for Paul McCartney.

Sub main3
' assumption: the database is registered
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("test01")
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL="INSERT INTO ""users"" (""firstname"",""lastname"") VALUES('Paul', 'McCartney')"
Stmt.executeUpdate(strSQL)
Conn.close()
msgbox "fin"
End Sub

Help found here.

Running an update query

In the database test01 I have a table called users with fields firstname and lastname. I want to update the lastname of the record with id=2 to ‘Jones’.

Sub main4
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("test01")
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL="UPDATE ""users"" SET ""lastname""='Jones' WHERE ""id"" = 2"
Stmt.executeUpdate(strSQL)
Conn.close()
msgbox "fin"
End Sub

Help found as above.

—

Note that table and field names have to be in quotes and since they appear in a string double quotes are used (“”lastname”").

Note also that queries created with the GUI can be used instead of sql versions. I am not a big fan of this, however, since I want the code together where I can see it.

The next step is to understand Context and Stmt and see if the first example can be written more like the last two

FORMS

combo box
When you use the arrow keys to change the value or when you type a value the event ‘Item status changed’ fires. When you click the down arrow and click an item the event ‘Key pressed’ fires. Conclusion: if you want a macro to run when the user uses the combo box, put the code in the two events mentioned.


Leave a Reply