Data Sources

BASIC Guide

{{{parttitle}}}

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

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

• The Language of OpenOffice.org 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 >

A database is incorporated into OpenOffice.org 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. You can also create data sources and work with them using OpenOffice.org 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

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

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

The data sources from OpenOffice.org 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 OpenOffice.org also includes a range of information about how the data is displayed within the database windows of OpenOffice.org.

Queries

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

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. fr:FR/Documentation/BASIC Guide/Data Sources hu:HU/Documentation/BASIC Guide/Data Sources it:IT/Documentation/BASIC Guide/Data Sources ja:JA/Documentation/BASIC Guide/Data Sources zh:ZH/Documentation/BASIC Guide/Data Sources

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