Difference between revisions of "Documentation/BASIC Guide/Database Access"

Latest revision as of 14:19, 17 May 2022

>]]

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.

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.

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

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().