Difference between revisions of "Documentation/BASIC Guide/Data Sources"

Latest revision as of 14:06, 30 January 2021

>]]

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. You can also 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

Settings (Array)

array containing PropertyValue-pairs with connection parameters (usually at least username and password)

User (String)

username

Password (String)

user password (is not saved)

IsPasswordRequired (Boolean)

the password is needed and is interactively requested from the 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 Apache OpenOffice 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 Apache OpenOffice also includes a range of information about how the data is displayed within the database windows of Apache OpenOffice.

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)

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.