Documentation/BASIC Guide/Data Sources

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 >

Data Sources

A database is incorporated into Apache OpenOffice 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 Apache OpenOffice 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 [html/p24.html#fapcj 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.

Queries

Predefined queries can be assigned to a data source. Apache OpenOffice 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, Apache OpenOffice 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

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