Chapter 10 Database Access

StarOffice has an integrated database interface (independent of any systems) called Star Database Connectivity (SDBC). The objective of developing this interface was to provide access to as many different data sources as possible.

To make this possible, data sources are accessed by drivers. The sources from which the drivers take their data is irrelevant to a SDBC user. Some drivers access file-based databases and take the data directly from them. Others use standard interfaces such as JDBC or ODBC. There are, however, also special drivers which access the MAPI address book, LDAP directories or StarOffice spreadsheets as data sources.

Since the drivers are based on UNO components, other drivers can be developed and therefore open up new data sources. You will find details about this in the StarOffice Developer's Guide.

---

Note –

In terms of its concept, SDBC is comparable with the ADO and DAO libraries available in VBA. It permits high level access to databases, regardless of the underlying database backends.

---

---

Note –

The database interface of StarOffice has grown through the launch of StarOffice 8. Although in the past, databases were primarily accessed using a range of methods of the Application object, the interface in StarOffice 7 sub-divides into several objects. A DatabaseContext is used as the root object for the database functions.

---

SQL: a Query Language

The SQL language is provided as a query language for users of SDBC. To compare the differences between different SQL dialects, the SDBC components from StarOffice have their own SQL parser. This uses the query window to check the SQL commands typed and corrects simple syntax errors, such as those associated with uppercase and lowercase characters.

If a driver permits access to a data source that does not support SQL, then it must independently convert the transferred SQL commands to the native access needed.

---

Note –

SQL implementation from SDBC is oriented towards the SQL-ANSI-Standard. Microsoft-specific extensions, such as the INNER JOIN construct are not supported. These should be replaced with standard commands (INNER JOIN, for example should be replaced with a corresponding WHERE clause).

---

Types of Database Access

The database interface from StarOffice is available in the StarOffice Writer and StarOffice Calc applications, as well as in the database forms.

In StarOffice Writer, standard letters can be created with the assistance of SDBC data sources and these can then be printed out. There is also an option for moving data from the database window into text documents using the drag-and-drop function.

If the user moves a database table into a spreadsheet, StarOffice creates a table area which can be updated at the click of the mouse if the original data has been modified. Conversely, spreadsheet data can be moved to a database table and a database import performed.

Finally, StarOffice provides a mechanism for forms based on databases. To do this, the user first creates a standard StarOffice Writer or StarOffice Calc form and then links the fields to a database.

All the options specified here are based on the user interface from StarOffice. No programming knowledge is needed to use the corresponding functions.

This chapter, however, provides little information about the functions specified, but instead concentrates on the programming interface from SDBC, which allows for automated database querying and therefore permits a much greater range of applications to be used.

Basic knowledge of the way in which databases function and the SQL query language is however needed to fully understand the following sections.

Data Sources

A database is incorporated into StarOffice by creating what is commonly referred to as a data source. The user interface provides a corresponding option for creating data sources in the Extras menu. However, you also can create data sources and work with them using StarOffice Basic.

A database context object that is created using the createUnoService function serves as the starting point for accessing a data source. This based on the com.sun.star.sdb.DatabaseContext service and is the root object for all database operations.

The following example shows how a database context can be created and then used to determine the names of all data sources available. It displays the names in a message box.

Dim DatabaseContext As Object
Dim Names
Dim I As Integer

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

Names = DatabaseContext.getElementNames()
For I = 0 To UBound(Names())
   MsgBox Names(I)
Next I

The individual data sources are based on the com.sun.star.sdb.DataSource service and can be determined from the database context using the getByName method:

Dim DatabaseContext As Object
Dim DataSource As Object

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")

The example creates a DataSource object for a data source called Customers.

Data sources provide a range of properties, which in turn provide general information about the origin of the data and information about access methods. The properties are:

• Name (String) – name of data source.

• URL (String) – URL of data source in the form of jdbc: subprotocol : subname or sdbc: subprotocol : subname.

• Info (Array) – array containing PropertyValue-pairs with connection parameters (usually at least user name and password).

• User (String) – user name.

• Password (String) – user password (is not saved).

• IsPasswordRequired (Boolean) – the password is needed and is interactively requested from user.

• IsReadOnly (Boolean) – permits read-only access to the database.

• NumberFormatsSupplier (Object) – object containing the number formats available for the database (supports the com.sun.star.util.XNumberFormatsSupplier interface, refer to Number, Date and Text Format section).

• TableFilter (Array) – list of table names to be displayed.

• TableTypeFilter (Array) – list of table types to be displayed. Values available are TABLE, VIEW and SYSTEM TABLE.

• SuppressVersionColumns (Boolean) – suppresses the display of columns that are used for version administration.

---

Note –

The data sources from StarOffice are not 1:1 comparable with the data sources in ODBC. Whereas an ODBC data source only covers information about the origin of the data, a data source in StarOffice also includes a range of information about how the data is displayed within the database windows of StarOffice.

---

Queries

Predefined queries can be assigned to a data source. StarOffice notes the SQL commands of queries so that they are available at all times. Queries are used to simplify working with databases because they can be opened with a simple mouse click and also provide users without any knowledge of SQL with the option of issuing SQL commands.

An object which supports the com.sun.star.sdb.QueryDefinition service is concealed behind a query. The queries are accessed by means of the QueryDefinitions method of the data source.

The following example lists the names of data source queries can be established in a message box.

Dim DatabaseContext As Object
Dim DataSource As Object
Dim QueryDefinitions As Object
Dim QueryDefinition As Object
Dim I As Integer
   
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")
QueryDefinitions = DataSource.getQueryDefinitions()

For I = 0 To QueryDefinitions.Count() - 1
   QueryDefinition = QueryDefinitions(I)
   MsgBox QueryDefinition.Name
Next I

In addition to the Name property used in the example, the com.sun.star.sdb.QueryDefinition provides a whole range of other properties. These are:

• Name (String) – query name.

• Command (String) – SQL command (typically a SELECT command).

• UpdateTableName (String) – for queries that are based on several tables: name of table in which value modifications are possible.

• UpdateCatalogName (String) – name of update tables catalogues.

• UpdateSchemaName (String) – name of update tables diagrams.

The following example shows how a query object can be created in a program-controlled manner and can be assigned to a data source.

Dim DatabaseContext As Object
Dim DataSource As Object
Dim QueryDefinitions As Object
Dim QueryDefinition As Object
Dim I As Integer
   
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")
QueryDefinitions = DataSource.getQueryDefinitions()

QueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition")
QueryDefinition.Command = "SELECT * FROM Customer"

QueryDefinitions.insertByName("NewQuery", QueryDefinition)

The query object is first created using the createUnoService call, then initialized, and then inserted into the QueryDefinitions object by means of insertByName.

Links with Database Forms

To simplify work with data sources, StarOffice provides an option for linking the data sources with database forms. The links are available through the getBookmarks() method. This returns a named container (com.sun.star.sdb.DefinitionContainer) which contains all links of the data source. The bookmarks can either be accessed through Name or Index.

The following example determines the URL of the MyBookmark bookmark.

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Bookmarks As Object
Dim URL As String
Dim I As Integer
   
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")
Bookmarks = DataSource.Bookmarks()

URL = Bookmarks.getByName("MyBookmark")
MsgBox URL

Database Access

A database connection is needed for access to a database. This is a transfer channel which permits direct communication with the database. Unlike the data sources presented in the previous section, the database connection must therefore be re-established every time the program is restarted.

StarOffice provides various ways of establishing database connections. Here is an explanation for the method based on an existing data source.

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")

If Not DataSource.IsPasswordRequired Then
   Connection = DataSource.GetConnection("","")
Else
   InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
   Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If

The code used in the example first checks whether the database is password protected. If not, it creates the database connection required using the GetConnection call. The two empty strings in the command line stand for the user name and password.

If the database is password protected, the example creates an InteractionHandler and opens the database connection using the ConnectWithCompletion method. The InteractionHandler ensures that StarOffice asks the user for the required login data.

Iteration of Tables

A table is usually accessed in StarOffice through the ResultSet object. A ResultSet is a type of marker that indicates a current set of data within a volume of results obtained using the SELECT command.

The example shows how a ResultSet can be used to query values from a database table.

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Customers")

If Not DataSource.IsPasswordRequired Then
   Connection = DataSource.GetConnection("","")
Else
   InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
   Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If

Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT CustomerNumber FROM Customer")

If Not IsNull(ResultSet) Then
   While ResultSet.next
      MsgBox ResultSet.getString(1)
   Wend
End If

Once the database connection has been established, the code used in the example first uses the Connection.createObject call to create a Statement object. This Statement object then uses the executeQuery call to return the actual ResultSet. The program now checks whether the ResultSet actually exists and traverses the data records using a loop. The values required (in the example, those from the CustomerNumber field) returns the ResultSet using the getString method, whereby the parameter 1 determines that the call relates to the values of the first column.

---

Note –

The ResultSet object from SDBC is comparable with the Recordset object from DAO and ADO, since this also provides iterative access to a database.

---

---

Note –

The database is actually accessed in StarOffice 8 through a ResultSet object. This reflects the content of a table or the result of a SQL-SELECT command. In the past, the ResultSet object provided the resident methods in the Application object for navigation within the data, for example, DataNextRecord ).

---

Type-Specific Methods for Retrieving Values

As can be seen in the example from the previous section, StarOffice provides a getString method for accessing table contents. The method provides the result in the form of a string. The following get methods are available:

• getByte() – supports the SQL data types for numbers, characters and strings.

• getShort() – supports the SQL data types for numbers, characters and strings.

• getInt() – supports the SQL data types for numbers, characters and strings.

• getLong() – supports the SQL data types for numbers, characters and strings.

• getFloat() – supports the SQL data types for numbers, characters and strings.

• getDouble() – supports the SQL data types for numbers, characters and strings.

• getBoolean() – supports the SQL data types for numbers, characters and strings.

• getString() – supports all SQL data types.

• getBytes() – supports the SQL data types for binary values.

• getDate() – supports the SQL data types for numbers, strings, date and time stamp.

• getTime() – supports the SQL data types for numbers, strings, date and time stamp.

• getTimestamp() – supports the SQL data types for numbers, strings, date and time stamp.

• getCharacterStream() – supports the SQL data types for numbers, strings and binary values.

• getUnicodeStream() – supports the SQL data types for numbers, strings and binary values.

• getBinaryStream() – binary values.

• getObject() – supports all SQL data types.

In all instances, the number of columns should be listed as a parameter whose values should be queried.

The ResultSet Variants

Accessing databases is often a matter of critical speed. StarOffice therefore provides several ways of optimizing ResultSets and thereby controlling the speed of access. The more functions a ResultSet provides, the more complex its implementation usually is and therefore the slower the functions are.

A simple ResultSet, such as that which was presented in the "Iteration of tables" section, provides the minimum scope of functions available. It only allows iteration to be applied forward, and for values to be interrogated. More extensive navigation options, such as the possibility of modifying values, are therefore not included.

The Statement object used to create the ResultSet provides some properties which allow the functions of the ResultSet to be influenced:

• ResultSetConcurrency (const) – specifications as to whether the data can be modified (specifications in accordance with com.sun.star.sdbc.ResultSetConcurrency).

• ResultSetType (const) – specifications regarding type of ResultSets ( specifications in accordance with com.sun.star.sdbc.ResultSetType).

The values defined in com.sun.star.sdbc.ResultSetConcurrency are:

• UPDATABLE - ResultSet permits values to be modified.

• READ_ONLY – ResultSet does not permit modifications.

The com.sun.star.sdbc.ResultSetConcurrency group of constants provides the following specifications:

• FORWARD_ONLY – ResultSet only permits forward navigation.

• SCROLL_INSENSITIVE – ResultSet permits any type of navigation, changes to the original data are, however, not noted.

• SCROLL_SENSITIVE – ResultSet permits any type of navigation, changes to the original data impact on the ResultSet.

---

Note –

A ResultSet containing the READ_ONLY and SCROLL_INSENSITIVE properties corresponds to a record set of the Snapshot type in ADO and DAO.

When using the ResultSet's UPDATEABLE and SCROLL_SENSITIVE properties, the scope of function of a ResultSet is comparable with a Dynaset type Recordset from ADO and DAO.

---

Methods for Navigation in ResultSets

If a ResultSet is a SCROLL_INSENSITIVE or SCROLL_SENSITIVE type, it supports a whole range of methods for navigation in the stock of data. The central methods are:

• next() – navigation to the next data record.

• previous() – navigation to the previous data record.

• first() – navigation to the first data record.

• last() – navigation to the last data record.

• beforeFirst() – navigation to before the first data record.

• afterLast() – navigation to after the last data record.

All methods return a Boolean parameter which specifies whether the navigation was successful.

To determine the current cursor position, the following test methods are provided and all return a Boolean value:

• isBeforeFirst() – ResultSet is before the first data record.

• isAfterLast() – ResultSet is after the last data record.

• isFirst() – ResultSet is the first data record.

• isLast() – ResultSet is the last data record.

Modifying Data Records

If a ResultSet has been created with the ResultSetConcurrency = UPDATEABLE value, then its content can be edited. This only applies for as long as the SQL command allows the data to be re-written to the database (depends on principle). This is not, for example, possible with complex SQL commands with linked columns or accumulated values.

The ResultSet object provides Update methods for modifying values, which are structured in the same way as the get methods for retrieving values. The updateString method, for example, allows a string to be written.

After modification, the values must be transferred into the database using the updateRow()method. The call must take place before the next navigation command, otherwise the values will be lost.

If an error is made during the modifications, this can be undone using the cancelRowUpdates()method. This call is only available provided that the data has not be re-written into the database using updateRow().