Documentation/BASIC Guide/Database Access

BASIC Guide

{{{parttitle}}}

[[{{{PrevPart}}}|< Previous Section ]]

[[{{{NextPart}}}|Next Section >]]

• The Language of Apache OpenOffice Basic

• Overview of a Basic Program
• Working With Variables
• Strings
• Numbers
• Boolean Values
• Date and Time
• Arrays
• Scope and Life Span of Variables
• Constants
• Operators
• Branching
• Loops
• Procedures and Functions
• Error Handling
• Other Instructions

• Runtime Library

• Conversion Functions
• Strings
• Date and Time
• Files and Directories
• Message and Input Boxes
• Other Functions

• Introduction to the API

• Universal Network Objects (UNO)
• Properties and Methods
• Modules, Services and Interfaces
• Tools for Working with UNO
• Overview of Central Interfaces

• Working with Documents

• The StarDesktop
• Styles and Templates

• Text Documents

• The Structure of Text Documents
• Editing Text Documents
• More than Just Text

• Spreadsheet Documents

• The Structure of Spreadsheets
  • Rows and Columns
  • Cells and Ranges
  • Formatting Spreadsheet Documents
• Editing Spreadsheet Documents

• Drawings and Presentations

• The Structure of Drawings
• Editing Drawing Objects
• Presentations

• Charts (Diagrams)

• Using Charts in Spreadsheets
• The Structure of Charts
• Chart Types

• Databases

• SQL: a Query Language
• Types of Database Access
• Data Sources
• Database Access

• Dialogs

• Working With Dialogs
• Properties
• Events
• Dialog Control Elements

• Forms

• Working With Forms
• Control Element Forms
• Database Forms

< Previous Page

Next Page >

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.

Apache OpenOffice 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 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.

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.

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

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