Create a LibreOffice (LO) Base document and connect to your database as usual.
Let LO register it for you.
Create a query ( I named mine CBO) that gets the data you need, for instance:
SELECT "Display Name", "Job Title", "Phone (Work)", "E-mail" FROM "LDAP Directory" WHERE "Job Title" LIKE 'Chief%'
Save the query and the database.
Next, create (or use an existing one) a Writer doc and create some variables like this:
- Press ‘CTRL+F2’ to open the ‘Fields’ property window
- Next, click the ‘Variables’ tab
- Now, in the listbox named ‘Type’, select ‘User Field’
- In the 'Format" list box choose ‘text’
- Next, towards the bottom in ‘Name’ type the name you want to call your variable…lets use ‘Name’. I would advise against spaces in your variable names.
- In the ‘Value’ box, type in something like ‘’. This is just placeholder text. You can type anything, but it’s best not to leave it blank, lest you not see it displayed on the page before the macro runs. I will stick with the variable name enclosed in angle brackets.
- Press the blue arrow to save variable definition
- Do the same to define variables for title, phone, and email.
We will use these variables later in LO Basic Macro code.
Assign the database you first created above to this document.
- Click ‘Edit’ in the main toolbar and choose ‘Exchange Database’.
- Under ‘Available Databases’ choose the name of the database that you created earlier.
- Click the plus sign next to it and choose the query from earlier (CBO).
- Now click ‘Define’.
Save your document.
Create the macro that will update these variables.
Click ‘Tools’->‘Macros’->‘Organize Macros’->‘Libre Office Basic’.
Click on the plus sign next to your document name, then click on ‘Standard’. Click the ‘New’ button and name it ‘dataToFields’. Now click edit. Copy and paste this code:
Sub main
dbContext = CreateUnoService(“com.sun.star.sdb.DatabaseContext”)
DB = dbContext.getByName(“ldapDB”) ‘This should be the registered name of your db’
connection = DB.getConnection("","") ‘get connection’
query = connection.Queries.getByName(“CBO”).command ‘get the sql query text (command) from the query object stored in the db called CBO’
statement = connection.createStatement() ’ create a statement object to run the query’
res = statement.executeQuery(query) ’ execute the query and store it in res, result set’
doc = ThisComponent ‘get a var set to this doc’
fm = doc.getTextFieldMasters() ‘get the field ( doc variables) names’
context = “com.sun.star.text.fieldmaster.User.” ‘the context in the LO api for user defined fields…saves us typing’
Do While res.next ()
meta = res.getMetaData() ’ ? dunno what this does’
cur_row = res.getRow () ’ get the current row in the resultset’
field = fm.getByName(context & “Name”) ’ get the doc variable name (by name) and save as field’
field.Content = res.getString(1) ‘set the field value, the doc variable, to the first column of the result set’
field = fm.getByName(context & “Title”)
field.Content = res.getString (2)
field = fm.getByName(context & “Phone”)
field.Content = res.getString (3)
field = fm.getByName(context & “Email”)
field.Content = res.getString (4)
doc.TextFields.refresh() ‘refresh the page to show updates’
Loop
End Sub
Save the macro and exit the macro editor.
Now goto ‘Tools’->‘Customize’ and click on the ‘Events’ tab, then choose ‘Activate Document’.
Now, towards the top, under ‘Assign’, click ‘Macro’.
Click the plus sign next to the documents name, then ‘Standard’ then ‘dataToFields’.
Click ‘ok’.
Save the document.
For a connection that doesn’t require an .odb file:
Sub main
doc = ThisComponent 'get a var set to this doc'
fm = doc.getTextFieldMasters() 'get the field ( doc variables) names'
context = "com.sun.star.text.fieldmaster.User." 'the context in the LO api for user defined fields....saves us typing'
Dim manager,connection,statement as Object
Dim query$ as string
Dim properties(1) As New com.sun.star.beans.PropertyValue
Dim quotes as String
quote = """"
manager = CreateUnoService("com.sun.star.sdbc.DriverManager")
properties(0).Name = "HostName"
properties(0).Value = "server"
properties(1).Name = "BaseDN"
properties(1).Value = "ou=Users,ou=bldg,dc=medinaco,dc=net"
connection = manager.getConnectionWithInfo("sdbc:address:ldap",properties())
statement = connection.createStatement()
query = "SELECT " & quote & "Display Name" & quote
query = query & "," & quote & "Job Title" & quote
query = query & "," & quote & "Phone (Work)" & quote
query = query & "," & quote & "E-mail" & quote
query = query & " FROM " & quote & "LDAP Directory" & quote
query = query & " WHERE " & quote & "Job Title" & quote & " LIKE 'Chief'"
print query
res = statement.executeQuery(query)
Do While res.next ()
meta = res.getMetaData() ' ? dunno what this does'
cur_row = res.getRow () ' get the current row in the resultset'
field = fm.getByName(context & "Name") ' get the doc variable name (by name) and save as field'
field.Content = res.getString(1) 'set the field value, the doc variable, to the first column of the result set'
field = fm.getByName(context & "Title")
field.Content = res.getString (2)
field = fm.getByName(context & "Phone")
field.Content = res.getString (3)
field = fm.getByName(context & "Email")
field.Content = res.getString (4)
doc.TextFields.refresh() 'refresh the page to show updates'
Loop
End Sub
