Database Access

From Apache OpenOffice Wiki
Jump to: navigation, search


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.

Apache OpenOffice provides various ways of establishing database connections. This example shows how to connect to 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 Apache OpenOffice asks the user for the required login data.

Iteration of Tables

A table is usually accessed in Apache OpenOffice 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.

This 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.

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


Documentation note.png StarOffice 5 : The database is actually accessed in Apache OpenOffice 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, Apache OpenOffice 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. Apache OpenOffice 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, 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.ResultSetType 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
Documentation note.png VBA : 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().


Content on this page is licensed under the Public Documentation License (PDL).
Personal tools