Documentation/DevGuide/Database Access

Platform Independence
The goal of the LibreOffice API database integration is to provide platform independent database connectivity for LibreOffice API. While it is necessary to access database abstraction layers, such as JDBC and ODBC, it is also desirable to have direct access to arbitrary data sources, if required.

The LibreOffice API database integration reaches this goal through an abstraction above the abstractions with the Star Database Connectivity (SDBC). SDBC accesses data through SDBC drivers. Each SDBC driver knows how to get data from a particular source. Some drivers handle files themselves, others use a standard driver model, or existing drivers to retrieve data. The concept makes it possible to integrate database connectivity for MAPI address books, LDAP directories and LibreOffice Calc into the current version of LibreOffice API.

Since SDBC drivers are UNO components, it is possible to write drivers for data sources and thus extend the database connectivity of LibreOffice API.

Functioning of the LibreOffice API Database Integration
The LibreOffice API database integration is based on SQL. This section discusses how the LibreOffice API handles various SQL dialects and how it integrates with data sources that do not understand SQL.

LibreOffice API has a built-in parser that tests and adjusts the syntax to be standard SQL. With the parser, differences between SQL dialects, such as case sensitivity, can be handled if the query composer is used. Data sources that do not understand SQL can be treated by an SDBC driver that is a database engine of its own, which translates from standard SQL to the mechanisms needed to read and write data using a non-SQL data source.

Integration with LibreOffice API
LibreOffice API employs SDBC data sources in Writer, Calc and Database Forms. In Writer, use form letter fields to access database tables, create email form letters, and drag tables and queries into a document to create tables or lists.

If a table is dragged into a Calc spreadsheet, the database range that can be updated from the database, and data pilots can be created from database connections. Conversely, drag a spreadsheet range onto a database to import the spreadsheet data into a database.

Another area of database connectivity are database forms. Form controls can be inserted into Writer or Calc documents, or just created in the database file with Base, to connect them to database tables to get data aware forms.

While there is no API coverage for direct database integration in Writer, the database connectivity in Calc and Database Forms can be controlled through the API. Refer to the corresponding chapters Database operations and Forms for more information. In Writer, database connectivity can be implemented by application programmers, for example, by accessing text field context. No API exists for merging complete selections into text.

Using the LibreOffice API database integration enhances or automates the out-of-box database integration, creates customized office documents from databases, or provides simple, platform-independent database clients in the LibreOffice API environment.

Architecture
The LibreOffice API database integration is divided into three layers: SDBC, SDBCX, and SDB. Each layer extends the functionality of the layer below.


 * Star Database (SDB) is the highest layer. This layer provides an application-centered view of the databases. Services, such as the database context, data sources, advanced connections, persistent query definitions and command definitions, as well as authentication and row sets are in this layer.
 * Star Database Connectivity Extension (SDBCX) is the middle layer which introduces abstractions, such as catalogs, tables, views, groups, users, columns, indexes, and keys, as well as the corresponding containers for these objects.
 * Star Database Connectivity (SDBC) is the lowest layer. This layer contains the basic database functionality used by the higher layers, such as drivers, simple connections, statements and result sets.

Example: Querying the Bibliography Database
The following example queries the bibliography database that is delivered with the LibreOffice distribution. The basic steps are:


 * 1) Create a com.sun.star.sdb.RowSet.
 * 2) Configure com.sun.star.sdb.RowSet to select from the table "biblio" in the data source "Bibliography".
 * 3) Execute it.
 * 4) Iterate over its rows.
 * 5) Insert a new row.

If the database requires login, set additional properties for user and password, or connect using interactive login. There are other options as well. For details, refer to the section The RowSet Service.

DatabaseContext
In the LibreOffice graphical user interface (GUI), define OpenOffice database files using the database application LibreOffice Base, and register them in the dialog in order to access them in the database browser. A data source has five main aspects. It contains the following:


 * The general information necessary to connect to a data source.
 * Settings to control the presentation of tables, and queries.
 * SQL query definitions.
 * Database forms.
 * Database reports.

From the API perspective, these functions are mirrored in the service. The database context is a container for data sources. It is a singleton, that is, it may exist only once in a running LibreOffice API instance and can be accessed by creating it at the global service manager of the office.



The database context is the entry point for applications that need to connect to a data source already defined in the LibreOffice API. Additionally, it is used to create new data sources and add them to LibreOffice API. The following figure shows the relationship between the database context, the data sources and the connection over a data source.



The database context is used to get a data source that provides a com.sun.star.sdb.Connection through its interface.

Existing data sources are obtained from the database context at its interfaces and. Their methods  and   deliver the  services defined in the LibreOffice GUI.

Since OpenOffice.org 2.0.0,  can also be used to obtain data sources that are not registered. You only need to pass a URL pointing to a valid database file, which is then automatically loaded by the context.

The code below shows how to print all available registered data sources:

DataSources
Data Sources can be broken down into five parts:
 * The service that establishes database connections
 * Queries that can be used, executed and altered by the the user in the GUI
 * Database forms and reports
 * Document links to a collection of database forms (deprecated)
 * Tables and columns in the database

The DataSource Service
The service includes all the features of a database defined in LibreOffice API. provides the following properties for its knowledge about how to connect to a database and which tables to display:

All other capabilities of a, such as query definitions, forms, reports, and the actual process of establishing connections are available over its interfaces.


 * com.sun.star.sdb.XQueryDefinitionsSupplier provides access to SQL query definitions for a database. The definition of queries is discussed in the next section, Queries.
 * com.sun.star.sdb.XCompletedConnection connects to a database. It asks the user to supply necessary information before it connects. The section Connecting Through a DataSource shows how to establish a connection.
 * com.sun.star.sdb.XBookmarksSupplier provides access to bookmarks pointing at documents associated with the DataSource, primarily LibreOffice API documents containing form components. Although it is optional, it is implemented for all data sources in LibreOffice API. The section Forms and Other Links explains database bookmarks.
 * com.sun.star.util.XFlushable forces the data source to flush all information including the properties above to the Open Office database file. However, changes work immediately and are stored in the OpenOffice database file format. com.sun.star.sdb.XFormDocumentsSupplier provides access to forms stored inside the OpenOffice database file.
 * com.sun.star.sdb.XReportDocumentsSupplier provides access to reports stored inside the OpenOffice database file.
 * com.sun.star.sdb.OfficeDatabaseDocument provides all interfaces which the service supports.

Adding and Editing Datasources
New data sources have to be created by the interface of the database context. A new data source can be registered with the database context at its interface and the necessary properties set.

The lifetime of data sources is controlled through the interfaces, and  of the database context.

The method  of   creates new generic data sources. They are added to the database context using  at the interface. The  allows registering data sources, as well as revoking the registration. The following are the methods defined for :

Before data sources can be registered at the database context, they have to be stored with the interface. The method  should be used for that purpose.

In the following example, a data source is created for a previously generated Adabas D database named MYDB1 on the local machine. The  property has to be present, and for Adabas D the property   should be true, otherwise no interactive connection can be established. The password dialog requests a user name by setting the User property.

The various possible database URLs are discussed in the section Driver Specifics.

To edit an existing data source, retrieve it by name or by file URL from the interface of the database context and use its  interface to configure it, as required. To store the newly edited data source, you must use the interface.

Queries
A com.sun.star.sdb.QueryDefinition encapsulates a definition of an SQL statement stored in LibreOffice API. It is similar to a view or a stored procedure, because it can be reused, and executed and altered by the user in the GUI. It is possible to run a  against a different database by changing the underlying   properties. It can also be created without being connected to a database.

The purpose of the query services available at a  is to define and edit queries. The query services by themselves do not offer methods to execute queries. To open a query, use a service or the  interface of a connection. See the sections The RowSet Service and PreparedStatement From DataSource Queries for additional details.

Adding and Editing Predefined Queries
The query definitions container com.sun.star.sdb.DefinitionContainer is used to work with the query definitions of a data source. It is returned by the interface of the data source, which has a single method for this purpose:

The  is not only an , but a com.sun.star.container.XNameContainer , that is, add new query definitions by name (see First Steps). Besides the name access, obtain query definitions through com.sun.star.container.XIndexAccess and com.sun.star.container.XEnumerationAccess.



New query definitions are created by the interface of the query definitions container. Its method  provides an empty   to configure, as required. Then, the new query definition is added to the  using   at the   interface.

A  is configured through the following properties:

The following example adds a new query definition  to the data source Bibliography that is provided with LibreOffice API.

Runtime Settings For Predefined Queries
The queries in the user interface have a number of advanced settings concerning the formatting and filtering of the query and its columns. For the API, these settings are available as long as the data source is connected with the underlying database. The section Connecting Through a DataSource discusses how to get a connection from a data source. When the connection is made, its interface com.sun.star.sdb.XQueriesSupplier returns query objects with the advanced settings above.



The  gives you a com.sun.star.sdbcx.Container of  services. These  objects are different from.

The service inherits both the properties from  service described previously, and the properties defined in the service. Use  to customize the appearance of the query when used in the LibreOffice API GUI or together with a com.sun.star.sdb.RowSet.

In addition to these properties, the service offers a com.sun.star.sdbcx.XDataDescriptorFactory to create new query descriptors based on the current query information. Use this query descriptor to append new queries to the com.sun.star.sdbcx.Container using its interface. This is an alternative to the connection-independent method to create new queries as discussed above. The section The Descriptor Pattern explains how to use descriptors to append new elements to database objects.

The interface is used to rename a query. It has one method:

The interface grants access to the column settings of the query through its single method  :

The columns returned by  are  services that provide column information and the ability to improve the appearance of columns. This service is explained in the section Tables and Columns.

The following code sample connects to Bibliography, and prints the column names and types of the previously defined query Query1.

The SingleSelectQueryComposer
The service is a tool that analyzes and composes single select statement strings. It is a replacement for the service. The query composer is divided into two parts. The first part defines the analyzing of the single select statement. The service hides the complexity of parsing and evaluating a single select statement, and provides methods for accessing a statements filter, group by, having and order criteria, as well as the corresponding select columns and tables. If supported, the service gives access to the parameters contained in the single select statement.

The second part of the query composer modifies the single select statement. The service extends the service  and provides methods for expanding a statement with filter, group by, having and order criteria. To get the new, extended statement, the methods from com.sun.star.sdb.SingleSelectQueryAnalyzer have to be used.

A query composer com.sun.star.sdb.SingleSelectQueryComposer is retrieved over the interface of a com.sun.star.sdb.Connection :

The interface is used to supply the   with the necessary information. It has the following methods:

The example below shows a simple test case for the com.sun.star.sdb.SingleSelectQueryComposer :

In the previous code example, a query command is passed to, then the criteria for  , and  , and  , and   is added. The  expressions are passed without the   keyword to , and the method  , with comma-separated   columns or column numbers, is provided.

As an alternative, add  conditions using. This method expects a service providing the name and the value for the filter. Similarly, the method  adds columns that are used for ordering. The same applies to  and. These columns can come from the.

The  property at the service  holds the original single select statement.

The methods,   and   return the complete  ,   and   part of the single select statement as a string.

The method  returns the filter split into OR levels. Within each OR level, filters are provided as AND criteria, with the name of the column and the filter condition string.

The interface provides access to the tables that are used in the FROM part of the SQL-Statement:

The interface provides the selected columns, which are listed after the SELECT keyword:

The interface provides the parameters, which are used in the where clause:

The SQLQueryComposer
The service com.sun.star.sdb.XSQLQueryComposerFactory is a tool that composes SQL  strings. It hides the complexity of parsing and evaluating SQL statements, and provides methods to configure an SQL statement with filtering and ordering criteria.

A query composer is retrieved over the interface of a com.sun.star.sdb.Connection :

Its interface com.sun.star.sdb.XSQLQueryComposer is used to supply the  with the necessary information. It has the following methods:

In the above method, a query command, such as "SELECT Identifier, Address, Author FROM biblio" is passed to, then the criteria for WHERE and ORDER BY is added. The WHERE expressions are passed without the WHERE keyword to, and the method   with comma-separated   columns or column numbers is provided.

As an alternative, add WHERE conditions using. This method expects a service providing the name and the value for the filter. Similarly, the method  adds columns that are used for ordering. These columns could come from the.

Retrieve the resulting SQL string from.

The methods,   and   return the  ,   and   part of the SQL command as a string.

The method  returns the filter split into OR levels. Within each OR level, filters are provided as AND criteria with the name of the column and the filter condition string.

The following example prints the structured filter.

The interface provides access to the tables that are used in the "FROM" part of the SQL-Statement:

The interface provides the selected columns, which are listed after the SELECT keyword:

Forms and Reports
Since OpenOffice.org 2.0.0, you can not only link to documents that belong to a data source, but you can store your forms and reports within the OpenOffice database file.

The interface, supplied by the com.sun.star.sdb.DataSource , provides access to the forms stored in the database file of the data source. It has one method:

The interface provides access to the reports stored in the database file of the data source. It has one method:

The returned service is a com.sun.star.sdb.DocumentContainer. The  is not only an , but a com.sun.star.container.XNameContainer , which means that new forms or reports are added using   as described in the First Steps chapter. To support the creation of hierarchies, the service additionally supplies the interfaces  and. The interfaces and  can be used to create folder hierarchies and to organize forms or reports in different subfolders.

Along with the name access, forms and reports are obtained through com.sun.star.container.XIndexAccess, and com.sun.star.container.XEnumerationAccess.

The interface is used to create new forms or reports. The method  of   creates a new document definition. Whether the document is a form or a report depends on the container where this object is inserted.



The following are the allowed properties for the document definition:

To create a new document definition, only the  and the   must be set. If an existing document from the file system is to be included, the URL property must be filled with the file URL. To copy document definitions, the  must be filled with the document definition to be copied.

The following are the allowed properties for the document container:

When creating a subfolder inside the form’s or report’s hierarchy, it is enough to set the Name property. If the  property is set, then it is copied. If the  supports the com.sun.star.container.XHierarchicalNameAccess, the children are also copied. The  can be a document definition or a document container.

The service additionally defines the interface  that is used to get access to the contained document inside the   and it has one method:


 * URL: describes the name of the document definition to load,
 * TargetFrameName: is not used.
 * SearchFlags: is not used.
 * Arguments:
 * PropertyValue
 * Name = ActiveConnection
 * Value = com.sun.star.sdbc.XConnection The connection that is used when opening the text document.
 * PropertyValue
 * Name = OpenMode
 * Value = string, "open" if the document is to be opened in live mode (editing is not possible), "openDesign" if the document is to be opened in design mode (editing is possible)

The returned object is a service. For forms, see Forms

The returned form or report documents are com.sun.star.sdb.DocumentDefinition services. These are the properties of the service.

In addition to these properties, the service offers a com.sun.star.sdbcx.XRename to rename a.

Document Links
Each data source can maintain an arbitrary number of document links. The primary purpose of this function is to provide a collection of database forms used with a database.

The links are available at the interface of a data source that has one method:

The returned service is a com.sun.star.sdb.DefinitionContainer. The  is not only a , but a com.sun.star.container.XNameContainer , that is, new links are added using   as described in the chapter First Steps. Besides the name access, links are obtained through com.sun.star.container.XIndexAccess and com.sun.star.container.XEnumerationAccess.

The returned bookmarks are simple strings containing URLs. Usually forms are stored at file:/// URLs. The following example adds a new document to the data source Bibliography:

To load a linked document, use the bookmark URL with the method  at the  interface of the com.sun.star.frame.Desktop singleton that is available at the global service manager. For details about the Desktop, see Office Development.

Tables and Columns
A com.sun.star.sdb.Table encapsulates tables in a LibreOffice API data source. The service changes the appearance of a table and its columns in the GUI, and it contains read-only information about the table definition, such as the table name and type, the schema and catalog name, and access privileges.

It is also possible to alter the table definition at the service. This is discussed in the section Database Design below.

The table related services in the database context are unable to access the data in a database table. Use the service, or to establish a connection to a database and use its  interface to manipulate table data. For details, see the sections The RowSet Service and PreparedStatement From DataSource Queries.

The following illustration shows the relationship between the com.sun.star.sdb.Connection and the Table objects it provides, and the services included in com.sun.star.sdb.Table.



The interface of a Connection supplies a com.sun.star.sdbcx.Container of com.sun.star.sdb.Table services through its method. The container administers Table services by name, index or as enumeration.

Just like queries, tables include the display properties specified in com.sun.star.sdb.DataSettings :

Basic table information is included in the properties included with com.sun.star.sdbcx.Table :

The service is an extension of the service. It introduces an additional property called. The  property indicates the actions the current user may carry out on the table.

The appearance of single columns in a table can be changed. The following illustration depicts the service and its relationship with the  service.



For this purpose, supports the interface. Its method  returns a com.sun.star.sdbcx.Container with the additional column-related interface  that is useful to get the column number for a certain column in a table:

The service combines com.sun.star.sdbcx.Column and the com.sun.star.sdb.ColumnSettings to form a column service with the opportunity to alter the visual appearance of a column.

The Properties of com.sun.star.sdbcx.Column are readonly and can be used for information purposes:

Understanding Connections
A connection is an open communication channel to a database. A connection is required to work with data in a database or with a database definition. Connections are encapsulated in Connection objects in the LibreOffice API. There are several possibilities to get a Connection:


 * Connect to a data source that has already been set up in the database context of LibreOffice API.
 * Use the driver manager or a specific driver to connect to a database without using an existing data source from the database context.
 * Get a connection from the connection pool maintained by LibreOffice API.
 * Reuse the connection of a database form which is currently open in the GUI.

With the above possibilities, a com.sun.star.sdb.Connection is made or at least a com.sun.star.sdbc.Connection :



The service has three main functions: communication, data definition and operation on the LibreOffice API application level. The service:

Connections are central to all database activities. The connection interfaces are discussed later.
 * Handles the communication with a database including statement execution, transactions, database metadata and warnings through the simple connection service of the SDBC layer com.sun.star.sdbc.Connection.
 * Handles database definition tasks, primarily table definitions, through the service . Optionally, it manages views, users and groups.
 * Organizes query definitions on the application level and provides a method to open queries and tables defined in LibreOffice API. Query definitions are organized by the interfaces and . Queries and tables can be opened using com.sun.star.sdb.XCommandPreparation . In case the underlying data source is needed, com.sun.star.container.XChild provides the parent data source. This is useful when using an existing connection, for instance, of a database form, to act upon its data source.

Communication
The main interface of com.sun.star.sdbc.Connection is com.sun.star.sdbc.XConnection. Its methods control almost every aspect of communication with a database management system:

The use of commands and statements are explained in the sections Manipulating Data and Using DDL to Change the Database Design. Transactions are discussed in Using DBMS Features. Database metadata are covered in Retrieving Information about a Database.

The com.sun.star.sdbc.XWarningsSupplier is a simple interface to handle SQL warnings:

The exception com.sun.star.sdbc.SQLWarning is usually not thrown, rather it is transported silently to objects supporting com.sun.star.sdbc.XWarningsSupplier. Refer to the API reference for more information about SQL warnings.

Data Definition
The interfaces of com.sun.star.sdbcx.DatabaseDefinition are explained in the section Using SDBCX to Access the Database Design.

Operation on Application Level
Handling of query definitions through com.sun.star.sdb.XQueriesSupplier and com.sun.star.sdb.XSQLQueryComposerFactory is discussed in the section Queries.

Through com.sun.star.sdb.XCommandPreparation get the necessary statement objects to open predefined queries and tables in a data source, and execute arbitrary SQL statements.

If the value of the parameter com.sun.star.sdb.CommandType is  or , pass a table name or query name that exists in the com.sun.star.sdb.DataSource of the connection. The value  makes   expect an SQL string. The result is a prepared statement object that can be parameterized and executed. For details and an example, refer to section PreparedStatement From DataSource Queries.

The interface accesses the parent com.sun.star.sdb.DataSource of the connection, if available.

Connecting Through a DataSource
Data sources in the database context of LibreOffice API offer two methods to establish a connection, a non-interactive and an interactive procedure. Use the interface to connect. It consists of:

If a database does not support logins, pass empty strings to. For instance, use  against dBase data sources like Bibliography:

However, if the database expects a login procedure, hard code the user and password, although this is not advisable. Data sources support an advanced login concept. Their interface com.sun.star.sdb.XCompletedConnection starts an interactive login, if necessary:

When you call, LibreOffice API shows the common login dialog to the user if the data source property   is true. The login dialog is part of the com.sun.star.sdb.InteractionHandler provided by the global service factory.

Connecting Using the DriverManager and a Database URL
The database context and establishing connections to a database even if there is no data source for it in LibreOffice API can be avoided.

To create a connection ask the driver manager for it. The com.sun.star.sdbc.DriverManager manages database drivers. The methods of its interface com.sun.star.sdbc.XDriverManager are used to connect to a database using a database URL:

Additionally, the driver manager enumerates all available drivers, and is used to register and deregister drivers. A URL that identifies a driver and contains information about the database to connect to must be known. The DriverManager chooses the first registered driver that accepts this URL. The following line of code illustrates it generally:

The structure of the URL consists of a protocol name, followed by the driver specific sub-protocol. The data source administration dialog shows the latest supported protocols. Some protocols are platform dependent. For example, ADO is only supported on Windows.

The URLs and conditions for the various drivers are explained in section Driver Specifics below.

Frequently a connection needs additional information, such as a user name, password or character set. Use the method  to provide this information. The method  takes a sequence of com.sun.star.beans.PropertyValue structs. Usually user and password are supported. For other connection info properties, refer to the section Driver Specifics.

Connecting Through a Specific Driver
The second method to create an independent, data-source connection is to use a particular driver implementation, such as writing a driver. There are also several implementations. Create an instance of the driver and ask it for a connection to decide what driver is used:

Driver Specifics
Currently, there are nine driver implementations. Some support only the simple service, some additionally the more extended service from com.sun.star.sdbcx.Driver that includes the support for tables, columns, keys, indexes, groups and users. This section describes the capabilities and the missing functionality in some database drivers. Below is a list of all available drivers.

The SDBC Driver for JDBC
The SDBC driver for JDBC is a mapping from SDBC API calls to the JDBC API, and vice versa. Basically, this driver is a direct bridge to JDBC. The SDBC driver for JDBC requires a special property called  to know which JDBC driver should be used. The expected value of this property should be the complete class name of the JDBC driver. The following code snippet uses a MySQL JDBC driver to connect.

Other properties that require setting during the connect process depend on the JDBC driver that is used.

The SDBC Driver for ODBC
This driver is comparable to the SDBC driver for JDBC described above. It maps the ODBC functionality to the SDBC API, but not completely. However, some functionality the SDBC API supports may not work with ODBC, because an ODBC driver may not support this feature and throws an SQL Exception to indicate this. To create a new connection, the driver uses the following URL format:

sdbc:odbc: Name of a datasource defined in the system

Additionally, this driver supports several properties through the service. These properties are set while creating a connection:

The SDBC Driver for Adabas D
This driver was the first driver to support the extended service com.sun.star.sdbcx.Driver, that offers access to the structure of a database. The Adabas D driver implementation extends the Adabas ODBC driver through knowledge about database structure. The URL should look like this:

sdbc:adabas::DATABASENAME

or

sdbc:adabas:HOST:DATABASENAME

To find the correct database name of an Adabas D database in the LibreOffice API, create a new database file and select Adabas D as type. On the next page you can browse for valid local database names. Find the database folders in sql/wrk in the Adabas installation folder.

The SDBC Driver for ADO
The SDBC driver for ADO supports the service. ADO does not allow modification on the database structure unless the database is a Jet Engine. Information about the limitations for ADO are available on the Internet. The URL for SDBC driver for ADO looks like this:

sdbc:ado:

Possible connection strings are:



The SDBC Driver for dBase
The dBase driver is one of the basic driver implementations and supports the service. This driver has a number of limitations concerning its abilities to modify the database structure and the extent of its SQL support. The URL for this driver is:

sdbc:dbase:

For instance:

sdbc:dbase:file:///d:/user/database/biblio

Similar to the SDBC driver for ODBC, this driver supports the connection info property  to set different text encodings. The second possible property is. When it is set to true, deleted rows in a table are still visible. In this state, it is not allowed to delete rows.

The following table shows the shortcomings of the SDBCX part of the dBase driver.

The driver has the following conditions in its support for SQL statements:


 * The  statement can not contain more than one table in the   clause.
 * For comparisons the following operators are valid:,  ,  ,  ,  ,  ,  ,  ,  ,.
 * Parameters are allowed, but must be denoted with a leading colon or with a single question mark.
 * The driver provides a  that supports bookmarks to records.
 * The first instance of LibreOffice API that accesses a dBase database locks the files for exclusive writing. The lock is never released until the LibreOffice API instance, which has obtained the exclusive write access, is closed. This severely limits the access to a dBase database in a network.

The SDBC Driver for Flat File Formats
This driver is another basic driver available in LibreOffice API. It can only be used to fetch data from existing text files, and no modifications are allowed, that is, the whole connection is read-only. The URL for this driver is:

sdbc:flat:

For instance:

sdbc:flat:file:///d:/user/database/textbase1

Properties that can be set while creating a new connection.

The SDBC Driver for LibreOffice Calc Files
This driver is a basic driver for LibreOffice Calc files. It can only be used to fetch data from existing tables and no modifications are allowed. The connection is read-only. The URL for this driver is:

sdbc:calc:

For instance:

sdbc:calc:file:///d:/calcfile.odt

The SDBC driver for address books
This driver allows LibreOffice API to connect to a system addressbook available on the local machine. It supports four different kinds of addressbooks.

All address book variants support read-only access. The driver itself is a wrapper for the Mozilla API.

The SDBC driver for embedded HSQL databases
This driver allows to connect to a database document which contains an embedded HSQL database. Since HSQLDB is a Java database, it requires a Java Runtime Environment to operate.

Connection Pooling
In a basic implementation, there is a 1:1 relationship between the com.sun.star.sdb.Connection object used by the client and physical database connection. When the Connection object is closed, the physical connection is dropped, thus the overhead of opening, initializing, and closing the physical connection is incurred for each client session. A connection pool solves this problem by maintaining a cache of physical database connections that can be reused across client sessions. Connection pooling improves performance and scalability, particularly in a three-tier environment where multiple clients can share a smaller number of physical database connections. In LibreOffice API, the connection pooling is part of a special service called the. This service manages newly created connections and reuses old ones when they are currently unused.

The algorithm used to manage the connection pool is implementation-specific and varies between application servers. The application server provides its clients with an implementation of the interface that makes connection pooling transparent to the client. As a result, the client gets better performance and scalability. When an application is finished using a connection, it closes the logical connection using  at the connection interface com.sun.star.sdbc.XConnection. This closes the logical connection, but not the physical connection. Instead, the physical connection is returned to the pool so that it can be reused. Connection pooling is completely transparent to the client: A client obtains a pooled connection from the service calling   at its interface  and uses it just the same way it obtains and uses a non-pooled connection.

The following sequence of steps outlines what happens when an SDBC client requests a connection from a  object:


 * 1) The client obtains an instance of the com.sun.star.sdbc.ConnectionPool from the global service manager and calls the same methods on the   object as on the.
 * 2) The application server providing the   implementation checks its connection pool for a suitable   object, a physical database connection, that is available. Determining the suitability of a given   object includes matching the client's user authentication information or application type, as well as using other implementation-specific criteria. The lookup method and other methods associated with managing the connection pool are specific to the application server.
 * 3) If there are no suitable   objects available, the application server creates a new physical connection and returns the  . The   is not driver specific. It is implemented in a service called com.sun.star.sdbc.ConnectionPool.
 * 4) Regardless if the   has been retrieved from the pool or created, the application server does internal recording to indicate that the physical connection is now in use.
 * 5) The application server calls the method   to get a logical   object. This logical   object is a handle to a physical   object. This handle is returned by the   method   when connection pooling is in effect.
 * 6) The logical   object is returned to the SDBC client that uses the same Connection API as in the standard situation without a  . Note that the underlying physical connection cannot be reused until the client calls the   method.

In LibreOffice API, connection pooling is enabled by default and can be controlled through. If a connection from a data source defined in LibreOffice API is returned, this setting applies to your connection, as well. To take advantage of the pool independently of LibreOffice API data sources, use the com.sun.star.sdbc.ConnectionPool instead of the.

Piggyback Connections
Occasionally, there may already be a connected database row set and you want to use its connection. For instance, if a user has opened a database form. To access the same database as the row set of the form, use the connection the form is working with, not opening a second connection. For this purpose, the com.sun.star.sdb.RowSet has a property  that returns a connection.

Manipulating Data
There are two possibilities to manipulate data in a database with the LibreOffice database connectivity.


 * Use the service that allows using data sources defined in LibreOffice through their tables or queries, or through SQL commands.
 * Communicate with a database directly using a Statement object.

This section describes both possibilities.

The RowSet Service
The service is a high-level client side row set that retrieves its data from a database table, a query, an SQL command or a row set reader, which does not have to support SQLl. It is a com.sun.star.sdb.ResultSet.

The connection of the row set is a named, the URL of a data access component, or a previously instantiated connection. Depending on the property, the row set caches all data or uses an optimized method to retrieve data, such as refreshing rows by their keys or their bookmarks. In addition, it provides events for row set navigation and row set modifications to approve the actions, and to react upon them.

The row set can be in two different states, before and after execution. Before execution, set all the properties the row set needs for its work. After calling  on the , move through the result set, or update and delete rows.

Usage
To use a row set, create a  instance at the global service manager through the service name com.sun.star.sdb.RowSet. Next, the  needs a connection and a command before it can be executed. These have to be configured through  properties.

Connection


 * There are three different ways to establish a connection:


 * Setting com.sun.star.sdb.RowSet:DataSourceName to a data source from the database context. If the  is not a URL, then the   uses the name to get the   from the   to create a connection to that data source.
 * Setting com.sun.star.sdb.RowSet:DataSourceName to a database URL. The row set tries to use this URL to establish a connection. Database URLs are described in Connecting Using the DriverManager and a Database URL.
 * Setting com.sun.star.sdb.RowSet:ActiveConnection makes a row set ready for immediate use. The row set uses this connection.


 * The difference between the two properties is that in the first case the  owns the connection. The   disposes the connection when it is disposed. In the second case, the   only uses the connection. The user of a   is responsible for the disposition of the connection. For a simple , use com.sun.star.sdb.RowSet:DataSourceName , but when sharing the connection between different row sets, then use com.sun.star.sdb.RowSet:ActiveConnection.


 * If there is already a connection, for example, the user opened a database form, open another row set based upon the property com.sun.star.sdb.RowSet:ActiveConnection of the form. Put the  of the form into the   property of the new row set.

Command


 * With a connection and a command, the row set is ready to be executed calling  on the  interface of the row set. For interactive logon, use , see Connecting Through a DataSource. If interactive logon is not feasible for your application, the properties   and   can be used to connect to a database that requires logon.


 * Once the method for how  creates it connections has been determined, the properties com.sun.star.sdb.RowSet:Command and com.sun.star.sdb.RowSet:CommandType have to be set. The   can be ,   or   where the   can be a table or query name, or an SQL command.

The following table shows the properties supported by com.sun.star.sdb.RowSet.

The com.sun.star.sdb.RowSet includes the service and its properties. Important settings such as  and   come from this service:

If the command returns results, that is, it selects data, use  to manipulate the data, because   is derived from. For details on manipulating a com.sun.star.sdb.ResultSet, see Result Sets.

The code fragment below shows how to create a.

The value of the read-only  properties is only valid after the first call to   on the. This snippet shows how to read the privileges out of the :

The next example reads the properties  and.

Occasionally, it is useful for the user to be notified when the  is final. That is accomplished by adding a com.sun.star.beans.XPropertyChangeListener for the property.

Events and Other Notifications
The  supports a number of events and notifications. First, there is the interface of the   that allows the user to add or remove objects derived from the interface. The interface defines the following methods:

All three methods return a boolean value that allows the  to continue when it is true, otherwise the current action is stopped.

Additionally, the  supports com.sun.star.sdbc.XRowSet that allows the user to add objects which are notified when the   has changed. This has to be a com.sun.star.sdbc.XRowSetListener. The methods are:

When an event occurs, the appropriate listener method is called to notify the registered listener(s). If a listener is not interested in a particular kind of event, it implements the method for that event as no-op. All listener methods take a com.sun.star.lang.EventObject struct that contains the  object which is the source of the event.

The following table lists the order of events after a specific method call on the RowSet. First the movements.

Consider a simple class which implements the two listener interfaces described above.

The following method uses the listener implementation above.

Clones of the RowSet Service
Occasionally, a second or third  that operates on the same data as the original , is required. This is useful when the rows should be displayed in a graphical representation. For the graphical part a clone can be used which only moves through the rows and displays the data. When a modification occurs on one specific row, the original  can be used to do this task.

The new clone is an object that supports the service if it was created using the interface  of the original. It is interoperable with the  that created it, for example, bookmarks can be exchanged between both sets. If the original  has not been executed before, null is returned.



Statements
The basic procedure to communicate with a database using an SQL statement is always the same:


 * 1) Get a connection object.
 * 2) Ask the connection for a statement.
 * 3) The statement executes a query or an update command. Use the appropriate method to execute the command.
 * 4) If the statement returns a result set, process the result set.

Creating Statements
A Statement object is required to send SQL statements to the Database Management System (DBMS). A Statement object is created using  at the  interface of the connection. It returns a service. This  is generic, that is, it does not contain any SQL command. It can be used for all kinds of SQL commands. Its main interface is :

Once a  is obtained, choose the appropriate execution method for the SQL command. For a  statement, use the method. For,   and   statements, the proper method is. To have multiple result sets returned, use  together with the interface  of the statement.

Consider how an  is used to create an   in the following example:

The remainder of this section discusses how to enter data into a table and retrieving the data later, using  and   commands with a com.sun.star.sdbc.Statement.

Inserting and Updating Data
The following examples use a sample Adabas D database. Generate an Adabas D database in the LibreOffice API installation and define a new table named SALESMAN.



The illustration above shows the definition of the SALESMAN table in the LibreOffice API data source administrator. The description column shows the lengths defined for the text fields of the table. After all the fields are defined, right-click the row header of the column SNR and choose Primary Key to make SNR the primary key. Afterwards a small key icon in the row header shows that SNR is the primary key of the table SALESMAN. When completed, save the table as SALESMAN. It is important to use uppercase letters for the table name, otherwise the example SQL code will not work.

The table does not contain any data. Use the following  command to insert data into the table one row at a time:

The following code sample inserts one row of data with the value  in the column , ' ' in  , ' ' in  , with other information in the following columns of the table SALESMAN. To issue the command against the database, create a  object and then execute it using the method  :

The next call to  inserts more rows into the table. Note the Statement object  is reused, rather than creating a new one for each update.

Updating tables is basically the same process. The SQL command:

writes a new street and state entry for Frank Jones who has SNR=2. The corresponding  call looks like this:

The return value of  is an int that indicates how many rows of a table were updated. Our update command affected one row, so n is equal to 1.

Getting Data from a Table
Now that the table  has values in it, write a   statement to access those values. The asterisk  in the following SQL statement indicates that all columns should be selected. Since there is no  clause to select less rows, the following SQL statement selects the whole table:

The result contains the following data:

The following is another example of a  statement. This statement gets a list with the names and addresses of all the salespersons. Only the columns,   and   were selected.

The result of this query only contains three columns:

The  statement above extracts all salespersons in the table. The following SQL statement limits the SALESMAN  to salespersons who were born before 01/01/1950:

The resulting data is:

When a database is accessed through the LibreOffice API database integration, the results are retrieved through  objects. The next section discusses how to use result sets. The following  call executes the SQL command above. Note that the Statement is used again:

Result Sets
The  objects represent the output of an   command in data rows and columns to retrieve the data using a row cursor that points to one data row at a time. The following illustration shows the inheritance of com.sun.star.sdb.ResultSet. Each layer of the LibreOffice API database integration adds capabilities to LibreOffice API result sets.

The fundamental is the most powerful of the three result set services. Basically this result set is sufficient to process SELECT results. It is used to navigate through the resulting rows, and to retrieve and update data rows and the column values in a row.



The com.sun.star.sdbcx.ResultSet can add bookmarks through com.sun.star.sdbcx.XRowLocate and allows row deletion by bookmarks through com.sun.star.sdbcx.XDeleteRows.

The service extends the  service by the additional interface  that allows the user to access information about the appearance of the selected columns in the application. The interface  returns a com.sun.star.sdbcx.Container of.



The service inherits the properties of the services  and.

The following table explains the properties introduced with com.sun.star.sdb.ResultColumn. For the inherited properties, refer to the section Tables and Columns.

Retrieving Values from Result Sets
A call to  on a com.sun.star.sdb.RowSet or a call to   on a Statement produces a com.sun.star.sdb.ResultSet.

Moving the Result Set Cursor
The  stored in the variable   contains the following data after the call above:

To access the data, go to each row and retrieve the values according to their types. The method  is used to move the row cursor from row to row. Since the cursor is initially positioned just above the first row of a  object, the first call to   moves the cursor to the first row and makes it the current row. For the same reason, use the method  to access the first row even if there is only one row in a result set. Subsequent invocations of  move the cursor down one row at a time.

The interface offers methods to move to specific row numbers, and to positions relative to the current row, in addition to moving the cursor back and forth one row at a time:

Using the getXXX Methods
To get column values from the current row, use the interface. It offers a large number of get methods for all SDBC data types, or rather getXXX methods. The XXX stands for the type retrieved by the method.

Usually, the getXXX method is used for the appropriate type to retrieve the value in each column. For example, the first column in each row of  is. It is the first column and contains a value of SQL type. The appropriate method to retrieve a  value is. It should be used for the second column, as well. The third column  stores DATE values, the method for date types is. SDBC is flexible and allows a number of type conversions through getXXX. See the table below for details.

The following code accesses the values stored in the current row of  and prints a line with the column values separated by tabs. Each time  is invoked, the next row becomes the current row, and the loop continues until there are no more rows in.

The output looks like this:

Joseph   Smith        7/2/1946 Frank    Jones        12/24/1963 Jane     Esperanza    4/1/1972 George   Flint        2/13/1953 Bob      Meyers       9/7/1949

In this code, how the  methods work are shown and the two   calls are examined.

The method  is invoked on , that is,   gets the value stored in column no. 1 in the current row of  , which is. The value retrieved by  has been converted from a   to a   in the Java programming language, and assigned to the   object.

The situation is similar with the method. It retrieves the value stored in column no. 3 (BIRTHDATE), which is an SQL, and converts it to a com.sun.star.util.Date before assigning it to the variable.

Note that the column number refers to the column number in the result set, not in the original table.

SDBC is flexible as to which  methods can be used to retrieve the various SQL types. For example, the method  can be used to retrieve any of the numeric or character types. The data it retrieves is converted to an int; that is, if the SQL type is, SDBC attempts to parse an integer out of the. To be sure that no information is lost, the method  is only recommended for SQL   types, and it cannot be used for the SQL types ,  ,  ,  ,  , or.

Although  is recommended for the SQL types   and , it is possible to retrieve any of the basic SQL types with it. The new SQL3 data types can not be retrieved with it. Getting values with  can be useful, but has its limitations. For instance, if it is used to retrieve a numeric type,  converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be used for numeric operations.

The value will be treated as a string, so if an application is to retrieve and display arbitrary column values of any standard SQL type other than SQL3 types, use.

The illustration below shows all  methods and the corresponding SDBC data types defined in com.sun.star.sdbc.DataType. The illustration above shows which methods can legally be used to retrieve SQL types, and which methods are recommended for retrieving the various SQL types.




 * x with grey background indicates that the  method is the recommended method to retrieve an SDBC data type. No data will be lost due to type conversion.
 * x indicates that the  method may legally be used to retrieve the given SDBC type. However, type conversion will take place and affect the values you obtain.

Scrollable Result Sets
The interface offers methods to move the cursor back and forth to an arbitrary row, and get the current position of the cursor. Scrollable result sets are necessary to create GUI tools that can browse result sets. It also may be required to move a specific row to work with it. Before taking advantage of these features, create a scrollable  object. The following lines of code illustrate one way to create a scrollable  object:

This code is similar to what was used earlier, except that it sets two property values at the. These properties have to be set before the statement is executed.

The value of the property  must be one of three constants defined in com.sun.star.sdbc.ResultSetType : ,   and.

The property  must be one out of the two com.sun.star.sdbc.ResultSetConcurrency constants   and. When a  is specified, it must be specified if it is read-only or modifiable.

If any constants for the type and modifiability of a  object are not specified,   and   will automatically be created.

Specifying the constant  creates a non-scrollable result set, that is, the cursor moves forward only. A scrollable  is obtained by specifying   or. Sensitive or insensitive refers to changes made to the underlying data after the result set has been opened. A  result set does not reflect changes to the underlying data, while a   result set shows changes. However, not all drivers and databases support change sensitivity.

In scrollable result sets, the counterpart to  is the method , which moves the cursor backward. Both methods return false when the cursor goes to the position after the last row or before the first row. This allows them to be used in a while loop.

The following two examples show the usage of  and   together with while:

The printout will look similar to this:

Linux          32 Beef           15.78 Orange juice   1.50

To process the rows going backward, the cursor must start out after the last row. The cursor is moved to the position after the last row with the method. Then  moves the cursor from the position after the last row to the last row, and then up to the first row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where  returns false.

The printout will look similar to this:

Orange juice   1.50 Beef           15.78 Linux          32

The column values are the same, but the rows are in the reverse order.

The cursor can be moved to a specific row in a ResultSet object. The methods,  ,  , and   move the cursor to the row indicated by the method names.

The method  moves the cursor to the row number indicated in the argument passed. If the number is positive, the cursor moves the given number from the beginning. Calling  moves the cursor to the first row. If the number is negative, the cursor moves the given number of rows from the end. Calling  sets the cursor to the last row. The following line of code moves the cursor to the fourth row of srs:

If srs has 500 rows, the following line of code moves the cursor to row 497:

The method  moves the cursor by an arbitrary number of rows from the current row. A positive number moves the cursor forward, and a negative number moves the cursor backwards. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:

The method  returns the number of the current row. For example, use  to verify the current position of the cursor in the previous example using the following code:

Note that some drivers do not support the getRow method. They always return 0.

There are four methods to verify if the cursor is at a particular position. The position is stated in their names:,  ,  , and. These methods return a boolean that can be used in a conditional statement. For example, the following code fragment tests if the cursor is after the last row before invoking the method  in a while loop. If the method  returns false, the cursor is not after the last row, so the method afterLast can be invoked. This guarantees that the cursor is after the last row and that using the method  in the while loop stop at every row in srs.

How to use the two methods from the  interface to move the cursor:   and   are discussed in the next section. There are examples illustrating why moving the cursor to certain positions may be required.

Modifiable Result Sets
Another feature of SDBC is the ability to update rows in a result set using methods in the programming language, rather than sending an SQL command. Before doing this, a modifiable result set must be created. To create a modifiable result set, supply the  constant   to the   property , so that the   object creates an modifiable   object each time it executes a query.

The following code fragment creates a modifiable  object. Note that the code also makes  scrollable. A modifiable  object does not have to be scrollable, but when changes are made to a result set, the user may want to move around in it. With a scrollable result set, there is the ability to move to particular rows that you can work with. If the type is, the new value in a row can be obtained after it has changed without refreshing the whole result set.

The  object rs may look similar to this:

The methods can now be used in the interface of the result set to insert a new row into , delete an existing row from  , or modify a column value in.

Update
An update is the modification of a column value in the current row. Suppose the price of orange juice is lowered to 0.99. Using the example above, the update would look like this:

The following code fragment shows another way to accomplish the same update, this time using SDBC:

Update operations in the SDBC API affect column values in the row where the cursor is positioned. In the first line, the  calls   to move the cursor to the last row where the column   has the value. Once the cursor is on the last row, all of the update methods that are called operate on that row until the cursor is moved to another row.

The second line changes the value of the  column to 0.99 by calling. This method is used because the column value we want to update is a float in Java programming language.

The  methods in com.sun.star.sdbc.XRowUpdate take two parameters: the number of the column to update and the new column value. There are specialized  methods for each data type, such as   and , just like the   methods discussed above.

At this point, the price in  for Orange juice is 0.99, but the price in the table   in the database is still 1.50. To ensure the update takes effect in the database and not just the result set, the com.sun.star.sdbc.XResultSetUpdate method  is called. Here is what the code should look like to update  and  :

If the cursor is moved to a different row before calling, the update is lost. The update can be canceled by calling, for instance, the price should have been 0.79 instead of 0.99. The  has to be invoked before invoking. The  does nothing when   has been called. Note that  cancels all the updates in a row, that is, if there were more than one   method in the row, they are all canceled. The following code fragment cancels the update to the price column to 0.99, and then updates it to 0.79:

In the above example, only one column value is updated, but an appropriate  method can be called for any or all of the column values in a single row. Updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to updateXXX methods, it takes only one call to the method  to update the database with all changes made in the current row.

To update the price for beef as well, move the cursor to the row containing that product. The row for beef immediately precedes the row for orange juice, so the method  can be called to position the cursor on the row for Beef. The following code fragment changes the price in that row to 10.79 in the result set and underlying table in the database:

All cursor movements refer to rows in a  object, not to rows in the underlying database. If a query selects five rows from a database table, there are five rows in the result set with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first row, and in a result set with five rows, row 5 is the last.

The order of the rows in the result set has nothing to do with the physical order of the rows in the underlying table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table physically. When a row is inserted, there is no way to know where in the table it was inserted.

Insert
The previous section described how to modify a column value using methods in the SDBC API, rather than SQL commands. With the SDBC API, a new row can also be inserted into a table or an existing row deleted programmatically.

Suppose our salesman Bob sold a new product to one of our customers, FTOP Darjeeling tea, and we need to add the new sale to the database. Using the previous example, write code that passes an SQL insert statement to the DBMS. The following code fragment, in which  is a   object, shows this approach:

The same thing can be done, without using any SQL commands, by using  methods in the SDBC API. After a  object is obtained with the results from the table , build the new row and then insert it into the result set and the table   in one step. First, build a new row in the insert row, a special row associated with every  object. This row is not part of the result set. It can be considered as a separate buffer in which a new row is composed prior to insertion.

The next step is to move the cursor to the insert row by invoking the method. Then set a value for each column in the row that should not be null by calling the appropriate  method for each value. Note that these are the same  methods used to change a column value in the previous section.

Finally, call  to insert the row that was populated with values into the result set. This method simultaneously inserts the row into the  object, as well as the database table from where the result set was selected.

The following code fragment creates a scrollable and modifiable  object rs that contains all of the rows and columns in the table  :

The next code fragment uses the  interface of   to insert the row for FTOP Darjeeling tea, shown in the SQL code example. It moves the cursor to the insert row, sets the six column values, and inserts the new row into  and  :

The  methods behave differently from the way they behaved in the update examples. In those examples, the value set with an  method immediately replaced the column value in the result set, because the cursor was on a row in the result set. When the cursor is on the insert row, the value set with an  method is immediately set, but it is set in the insert row rather than in the result set itself.

In updates and insertions, calling an  method does not affect the underlying database table. The method  must be called to have updates occur in the database. For insertions, the method  inserts the new row into the result set and the database at the same time.

If a value is not supplied for a column that was defined to accept SQL  values, then the value assigned to that column is. If a column does not accept null values, an  is returned when an   method is not called to set a value for it. This is also true if a table column is missing in the  object. In the example above, the query was, which produced a result set with all the columns of all the rows. To insert one or more rows, the query does not have to select all rows, but it is advisable to select all columns. Additionally, if the table has many rows, use a  clause to limit the number of rows returned by the   statement.

After the method  is called, start building another insert row, or move the cursor back to a result set row. Any of the methods can be executed that move the cursor to a specific row, such as,  ,  ,  , and. The methods,  , and   can also be used. Note that only  can be invoked as long as the cursor is on the insert row.

When the method  is called, the result set records which row the cursor is in, that is by definition the current row. As a consequence, the method  can move the cursor from the insert row back to the row that was the current row previously. This also explains why the methods  and   can be used, because require movement relative to the current row.

Delete
In the previous sections, how to update a column and insert a new row was explained. This section discusses how to modify the  object by deleting a row. The method  is called to delete the row where the cursor is placed. For example, to delete the fourth row in the, the code looks like this:

The fourth row is removed from  and also from the database.

The only issue about deletions is what the  object does when it deletes a row. With some SDBC drivers, a deleted row is removed and no longer visible in a result set. Other SDBC drivers use a blank row as a placeholder (a "hole") where the deleted row used to be. If there is a blank row in place of the deleted row, the method  can be used with the original row positions to move the cursor, because the row numbers in the result set are not changed by the deletion.

Remember that different SDBC drivers handle deletions differently. For example, if an application is meant to run with different databases, the code should not depend on holes in a result set.

Seeing Changes in Result Sets
When data is modified in a  object, the change is always visible immediately. That is, if the same query is re-executed, a new result set is produced based on the data currently in a table. This result set reflects the earlier changes.

If the changes made by you or others are visible while the  object is open, is dependent on the DBMS, the driver, and the type of   object.

With a  object, the updates to column values are visible. As well, insertions and deletions are visible, but to ensure this information is returned, use the com.sun.star.sdbc.XDatabaseMetaData methods.

The amount of visibility for changes can be regulated by raising or lowering the transaction isolation level for the connection with the database. For example, the following line of code, where con is an active  object, sets the connection's isolation level to  :

With this isolation level, the  object does not show changes before they are committed, but it shows changes that may have other consistency problems. To allow fewer data inconsistencies, raise the transaction isolation level to. Note that the higher the isolation level, the poorer the performance. The database and driver also limited what is actually provided. Many programmers use their database's default transaction isolation level. Consult the DBMS manual for more information about transaction isolation levels.

In a  object that is , changes are not visible while it is still open. Some programmers only use this type of  object to get a consistent view of the data without seeing changes made by others.

The method  is used to get the latest values for a row straight from the database. This method is time consuming, especially if the DBMS returns multiple rows  is called. The method  can be valuable if it is critical to have the latest data. Even when a result set is sensitive and changes are visible, an application may not always see the latest changes that have been made to a row if the driver retrieves several rows at a time and caches them. Thus, using the method  ensures that only up-to-date data is visible.

The following code sample illustrates how an application might use the method  when it is critical to see the latest changes. Note that the result set should be sensitive. If the method  with a   is used,   does nothing. Getting the latest data for the table  is not realistic with these methods. A more realistic scenario is when an airline reservation clerk needs to ensure that the seat he is about to reserve is still available.

ResultSetMetaData
When you develop applications that allow users to create their own SQL statements, for example, through a user interface, information about the result set to be displayed is required. For this reason, the result set supports a method to examine the meta data, that is, information about the columns in the result set. This information could cover items, such as the name of the column, if it is null, if it is an auto increment column, or a currency column. For detailed information, see the interface. The following code fragment shows the use of the  interface:

The printout looks similar to this:

Name:  NAME    Type:   12 Name:  PRICE   Type:   3

Notice that the Type returned is the number for the corresponding SQL data type. In this case,  has the value   and the type   is the SQL data type. The whole list of data types can be found at com.sun.star.sdbc.DataType.

Note that the com.sun.star.sdbc.XResultSetMetaData can be requested before you move to the first row.

Using Prepared Statements
Sometimes it is convenient or efficient to use a  object to send SQL statements to the database. This special type of statement includes the more general service com.sun.star.sdbc.Statement already discussed.

When to Use a PreparedStatement Object
Using a PreparedStatement object reduces execution time, if executing a  object many times as in the example above.

The main feature of a  object is that it is given an SQL statement when it is created, unlike a   object. This SQL statement is sent to the DBMS right away where it is compiled. As a result, the  object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the  is executed, the DBMS can run the  's SQL statement without having to analyze and optimize it again.

The  objects can be used for SQL statements without or without parameters. The advantage of using SQL statements with parameters is that the same statement can be used with different values supplied each time it is executed. This is shown in an example in the following sections.

Creating a PreparedStatement Object
Similar to  objects,   objects are created using   on a   object. Using our open connection con from the previous examples, code could be written like the following to create a  object that takes two input parameters:

The variable  now contains the SQL update statement that has also been sent to the DBMS and precompiled.

Supplying Values for PreparedStatement Parameters
Before executing a  object, values to replace the question mark placeholders or named parameters, such as   or   have to be supplied. This is accomplished by calling one of the  methods defined in the interface  of the prepared statement. For instance, to substitute a question mark with a value that is a Java int, call. If the value is a Java String, call the method. There is a  method for each type in the Java programming language.

Using the  object   from the previous example, the following line of code sets the first question mark placeholder to a Java String with a value of '34 Main Road':

The example shows that the first argument given to a  method indicates which question mark placeholder should be set, and the second argument contains the value for the placeholder. The next example sets the second placeholder parameter to the Java int 1:

After these values have been set for its two input parameters, the SQL statement in  is equivalent to the SQL statement in the String object   used in the previous update example. Therefore, the following two code fragments accomplish the same thing:

Code Fragment 1:

Code Fragment 2:

The method  was used to execute the   and the. Notice that no argument is supplied to  when it is used to execute. This is true because  already contains the SQL statement to be executed.

Looking at the above examples, a  object with parameters was used instead of a statement that involves fewer steps. If a table is going to be updated once or twice, a statement is sufficient, but if the table is going to be updated often, it is efficient to use a  object. This is especially true in a situation where a for loop or while loop can be used to set a parameter to a succession of values. This is shown later in this section.

Once a parameter has been set with a value, it retains that value until it is reset to another value or the method  is called. Using the  object , the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one as is:

PreparedStatement From DataSource Queries
Use the com.sun.star.sdb.XCommandPreparation to get the necessary statement objects to open predefined queries and tables in a data source, and to execute arbitrary SQL statements:

If the value of the parameter com.sun.star.sdb.CommandType is  or , pass a table name or query name that exists in the com.sun.star.sdb.DataSource of the connection. The value  makes   expect an SQL string. The result is a prepared statement object that can be parameterized and executed.

The following fragment opens a predefined query in a database Ada01:

Retrieving Information about a Database
The interface is implemented by SDBC drivers to provide information about their underlying database. It is used primarily by application servers and tools to determine how to interact with a given data source. Applications may also use  methods to get information about a database. The interface includes over 150 methods, that are categorized according to the types of information they provide:


 * General information about the database.
 * If the database supports a given feature or capability.
 * Database limits.
 * What SQL objects the database contains and attributes of those objects.
 * Transaction support offered by the data source.

Additionally, the interface uses a resultset with more than 40 possible columns as return values in many com.sun.star.sdbc.XDatabaseMetaData methods. This section presents an overview of the interface, and provides examples illustrating the categories of metadata methods. For a comprehensive listing, consult the SDBC API specification.


 * Creating the XDatabaseMetaData objects

A com.sun.star.sdbc.XDatabaseMetaData object is created using the  method. Once created, it can be used to dynamically discover information about the underlying data source. The following code example creates a com.sun.star.sdbc.XDatabaseMetaData object and uses it to determine the maximum number of characters allowed for a table name.

Retrieving General Information
Some com.sun.star.sdbc.XDatabaseMetaData methods are used to dynamically discover general information about a database, as well as details about its implementation. Some of the methods in this category are:


 * ,  and
 * ,  and
 * and
 * and
 * and
 * and

Determining Feature Support
A large group of com.sun.star.sdbc.XDatabaseMetaData methods can be used to determine whether a given feature or set of features is supported by the driver or underlying database. Beyond this, some methods describe what level of support is provided. Some of the methods that describe support for individual features are:



Methods to describe the level of feature support include:



Database Limits
Another group of methods provides the limits imposed by a given database. Some methods in this category are:



Methods in this group return the limit as an int. A return value of zero means there is no limit or the limit is unknown.

SQL Objects and their Attributes
Some methods provide information about the SQL objects that populate a given database. This group also includes methods to determine the attributes of those objects. Methods in this group return  objects in which each row describes a particular object. For example, the method  returns a   object in which there is a row for each user defined type (UDT) that has been defined in the database. Examples of this category are:


 * and
 * and
 * and
 * and
 * and

For example, to display the structure of a table that consists of columns and keys (primary keys, foreign keys), and also indexes defined on the table, the interface is required:

Another method often used when creating SQL statements is the method. This method is always used when table or column names need to be quoted in the SQL statement. For example:

In this case, the identifier quotation is the character ". The combination of  methods in the following code fragment may be useful to know if the database supports catalogs and/or schemata.

Using DDL to Change the Database Design
To show the usage of statements for data definition purposes, we will show how to create the tables in our example database using CREATE statements. The first table,, contains essential information about the salespersons, including the first name, last name, street address, city, and birth date. The table  that is described in more detail later, is shown here:

The first column is the column SNR of SQL type. This column contains a unique number for each salesperson. Since there is a different SNR for each person, the SNR column can be used to uniquely identify a particular salesman,the is, the primary key. If this were not the case, an additional column that is unique would have to be introduced, such as the social security number. The column for the first name is  that holds values of the SQL type   with a maximum length of 50 characters. The third column,, is also a   with a maximum length of 100 characters. The  and   columns are  s with 50 characters. The column  uses   and the column   uses the type. By using the type  instead of  ,the dates of birth can be compared with the current date.

The second table,, in our database, contains information about customers:

The first column is the personal number COS_NR of our customer. This column is used to uniquely identify the customers, and declare this column to be the primary key. The types of the other columns are identical to the first table, SALESMAN.

Another table to show joins is required. For this purpose, the table SALES is used. This table contains all sales that our salespersons could enter into an agreement with the customers. This table needs a column SALENR to identify each sale, a column for COS_NR to identify the customer and a column SNR for the sales person who made the sale, and the columns that defines the article sold.

To show the relationship between the three tables, consider the diagram below.

The table SALES contains the column COS_NR and the column SNR. These two columns can be used in SELECT statements to get data based on the information in this table, for example, all sales made by the salesperson Jane. The column COS_NR is the primary key in the table CUSTOMER and it uniquely identifies each of the customers. The same is true for the column SNR in the table SALESMAN. In the table SALES, the fields COS_NR and SNR are foreign keys. Note that each COS_NR and SNR number may appear more than once in the SALES table, because a third column SALENR was introduced. This is required for a primary key. An example of how to use primary and foreign keys in a SELECT statement is provided later.

The following CREATE TABLE statement creates the table SALESMAN. The entries within the outer pair of parentheses consist of the name of a column followed by a space and the SQL type to be stored in that column. A comma separates the column entries where each entry consists of a column name and SQL type. The type VARCHAR is created with a maximum length, so it takes a parameter indicating the maximum length. The parameter must be in parentheses following the type. The SQL statement shown here specifies that the name in column FIRSTNAME may be up to 50 characters long:

In the CREATE TABLE statement above, keywords are printed in capital letters, and each item is on a separate line. SQL does not require the use of these conventions, it makes the statements easier to read. The standard in SQL is that keywords are not case-sensitive, therefore, the following SELECT statement can be written in various ways:

is equivalent to

Single quotes '…' denote a string literal, double quotes mark case-sensitive identifiers in many SQL databases.

Requirements can vary from one DBMS to another for identifier names. For example, some DBMSs require that column and table names must be given exactly as they were created in the CREATE TABLE statement, while others do not. We use uppercase letters for identifiers such as SALESMAN, CUSTOMERS and SALES. Another way would be to ask the XDatabaseMetaData interface if the method  returns true, and to use the string that the method   returns.

The data types used in our  statement are the generic SQL types (also called SDBC types) that are defined in the com.sun.star.sdbc.DataType. DBMSs generally uses these standard types.

To issue the commands above against our database, use the connection con to create a statement and the method  at its interface. In the following code fragment,  is supplied with the SQL statement from the SALESMAN example above:

The method  is used because the SQL statement contained in   is a DDL (data definition language) statement. Statements that create a table, alter a table, or drop a table are all examples of DDL statements, and are executed using the method.

When the method  is used to execute a DDL statement, such as CREATE TABLE, it returns zero. Consequently, in the code fragment above that executes the DDL statement used to create the table SALESMAN, n is assigned a value of 0.

The Extension Layer SDBCX


The SDBCX layer introduces several abstractions built upon the SDBC layer that define general database objects, such as catalog, table, view, group, user, key, index, and column, as well as support for schema and security tasks. These objects are used to manage database design tasks. The ability of the SDBCX layer to define new data structures makes it an alternative to SQL DDL. The above Illustration 1 gives an overview to the SDBCX objects an their containers.

All objects mentioned previously have matching containers, except for the catalog. Each container implements the service. The interfaces that the container supports depend on the objects that reside in it. For instance, the container for keys does not support an interface. These containers are used to add and manage new objects in a catalog. The users and groups container manage the control permissions for other SDBCX objects, such as tables and views.

The illustration below shows the container specification for SDBCX DatabaseDefinition services.



Catalog Service
The Catalog object is the highest-level container in the SDBCX layer. It contains structural features of databases, like the schema and security model for the database. The connection, for instance, represents the database, and the Catalog is the database container for the tables, views, groups, and users within a connection or database. To create a catalog object, the database driver must support the interface and an existing connection object. The following code fragment lists tables in a database.

Table Service
The Table object is a member of the tables container that is a member of the Catalog object. Each Table object supports the same properties, such as,  ,  ,  , and an optional. The properties  and   can be empty when the database does not support these features. The  property contains any comments that were added to the table object at creation time. The optional property  is a string property may contain a database specific table type when supported,. Common table types are "TABLE", "VIEW", "SYSTEM TABLE", and "TEMPORARY TABLE". All these properties are read-only as long as this is not a descriptor. The descriptor pattern is described later.



The  object also supports the  interface, because a table cannot exist without columns. The other interfaces are optional, that is, they do not have to be supported by the actual table object:


 * interface that is used to copy a table object.
 * interface that returns the container for indexes.
 * interface that returns the keys container.
 * interface that allows renaming a table object.
 * interface that allows the altering of columns of a table object.

The code example below shows the use of the table container and prints the table properties of the first table in the container.

The Table object contains access to the columns, keys, and indexes when the above mentioned interfaces are supported.

Column Service
The Column object is the simplest object structure in the SDBCX layer. It is a collection of properties that define the Column object. The columns container exists for table, key, and index objects. The Column object is a different for these objects:


 * The normal  service is used for the table object.
 * com.sun.star.sdbcx.KeyColumn extends the "normal" service with an extra property named  . This property is the name of a referenced column out of the referenced table.
 * com.sun.star.sdbcx.IndexColumn extends the service with an extra boolean property named  . This property is true when the index is ascending, otherwise it is false.



The Column object is defined by the following properties:

The Column object also supports the interface that creates a copy of this object.

Index Service
The Index service encapsulates indexes at a table object. An index is described through the properties,  ,  ,  , and. All properties are read-only if an index has not been added to a tables index container. The last three properties are boolean values that indicate an index object only allows unique values, is used for the primary key, and if it is clustered. The property  is only available after the index has been created because it defines a special index that is created by the database while creating a primary key for a table object. Not all databases currently available in LibreOffice API support primary keys.



The following code fragment displays the properties of a given index object:

Key Service
The Key service provides the foreign and primary keys behavior through the following properties. The  property is the name of the key. It could happen that the primary key does not have a name. The property Type contains the kind of the key, that could be PRIMARY, UNIQUE, or FOREIGN, as specified by the constant group com.sun.star.sdbcx.KeyType. The property  contains a value when the key is a foreign key and it designates the table to which a foreign key points. The  and   properties determine what happens when a primary key is deleted or updated. The possibilities are defined in com.sun.star.sdbc.KeyRule :,  ,  ,   and.



The following code fragment displays the properties of a given key object:

View Service
A view is a virtual table created from a SELECT on other database tables or views. This service creates a database view programmatically. It is not necessary to know the SQL syntax for the CREATE VIEW statement, but a few properties have to be set. When creating a view, supply the value for the property Name, the SELECT statement to the property  and if the database driver supports a check option, set it in the property. Possible values of com.sun.star.sdbcx.CheckOption are,   and. A schema or catalog name can be provided (optional).



Group Service
The service is the first of the two security services,   and. The  service represents the group account that has access permissions to a secured database and it has a   property to identify it. It supports the interface that allows current privilege settings to be obtained, and to grant or revoke privileges. The second interface is. The word 'Supplier' in the interface name identifies the group object as a container for users. The container returned here is a collection of all users that belong to this group.



User Service
The service is the second security service, representing a user in the catalog. This object has the property Name that is the user name. Similar to the  service, the   service supports the interface. This is achieved through the interface derived from. In addition to this interface, the  interface supports changing the password of a specific user. Similar to the  service above, the User service is a container for the groups the user belongs to.



The Descriptor Pattern
The descriptor is a special kind of object that mirrors the structure of the object which should be appended to a container object. This means that a descriptor, once created, can be appended more than once with only small changes to the structure. For example, when appending columns to the columns container, we:

therefore, only create one descriptor to append more than one column.
 * Create one descriptor with com.sun.star.sdbcx.XDataDescriptorFactory.
 * Set the needed properties.
 * Add the descriptor to the container.
 * Adjust some properties, such as the name.
 * Add the modified descriptor to the container.
 * Repeat the steps, as necessary.




 * Creating a Table

An important use of the SDBCX layer is that it is possible to programmatically create tables, along with their columns, indexes, and keys.

The method of creating a table is the same as creating a table with a graphical table design. To create it programmatically is easy. First, create a table object by asking the tables container for its interface. When the  method is called, the  interface of an object that implements the service  is returned. As described above, use this descriptor to create a new table in the database, by adding the descriptor to the Tables container. Before appending the descriptor, append the columns to the table descriptor. Use the same method as with the containers used in the SDBCX layer. On the column object, some properties need to be set, such as, and. The properties to be set depend on the SDBC data type of the column.

The column name must be unique in the columns container.

After the columns are appended, add the  object to its container or define some key objects, such as a primary key.

Adding an Index
To add an index, the same programmatic logic is followed. Create an  with the  interface from the index container. Then follow the same steps as for the table. Next, append the columns to be indexed.

Note that only an index can be added to an existing table. It is not possible to add an index to a.

The task is completed when the index object is added to the index container, unless the  method throws an com.sun.star.sdbc.SQLException. This may happen when adding a unique index on a column that already contains values that are not unique.

Creating a User
The procedure to create a user is the same. The interface is used from the users container. Create a user with the. The com.sun.star.sdbcx.UserDescriptor has an additional property than the  service supports. This additional property is the  property which should be set. Then the  object can be appended to the user container.

Adding a Group
Creating a com.sun.star.sdbcx.GroupDescriptor object is the same as the methods described above. Follow the same steps:


 * 1) Set a name for the group in the   property.
 * 2) Append all the users to the user container of the group.
 * 3) Append the   object to the group container of the catalog.

Transaction Handling
Transactions combine several separate SQL executions, so that they can be seen as a single event that is executed completely (commit) or not at all (rollback). A typical example for a transaction is a money transfer. It consists of two steps: withdrawing an amount of money from one bank account and crediting another account with it. Both steps must be successful or they must be canceled. Transactions in SDBC are handled by the interface of connections. The transaction related methods of this interface are:

Usually all transactions are in auto commit mode, that means, a commit takes place after each single SQL command. Therefore to control a transaction manually, switch auto commit off using. The first SQL command without auto commit starts a transaction that is active until the corresponding methods have been committed or rolled back.

Afterwards, the auto commit mode can be reinstated using.

Transactions bring about a synchronization problem. If data is read from a table, it is possible that the data has just been changed by a command of a transaction started by another process. If the other transaction is rolled back, there may be inconsistencies between the results and contents of the database.

Transaction isolation controls the behavior of the database in case of parallel transactions. There are several isolation levels:

Stored Procedures
Stored procedures are server-side processes execute several SQL commands in a single step, and can be embedded in a server language for stored procedures with enhanced control capabilities. A procedure call usually has to be parameterized, and the results are result sets and additional out parameters. Stored procedures are handled by the method  of the interface.

The method  takes a an SQL statement that may contain one or more ' ' in parameter placeholders. It returns a com.sun.star.sdbc.CallableStatement. A  is a com.sun.star.sdbcx.PreparedStatement with two additional interfaces for out parameters:

com.sun.star.sdbc.XOutParameters is used to declare parameters as out parameters. All out parameters must be registered before a stored procedure is executed.

The com.sun.star.sdbc.XRow is used to retrieve the values of out parameters. It consists of getXXX methods and should be well-known from the common result sets.

Writing Database Drivers
In the following sections, implementing an SDBC driver is described. The user should have some experience in the use of the SDBC API, or be familiar with the previous chapter about SDBC and SDBCX.

This section is divided into two parts. The first part describes the simple driver that includes only the SDBC layer with the PreparedStatements, Statements and ResultSets. The second part extends the simple driver from part one to a more sophisticated one. This driver provides access to Tables, Views, Groups, Users and others.

A skeleton for a C++ SDBC driver is provided in the samples folder. Some changes are necessary to create a working driver. Adjust the namespace and replace the word "skeleton" by a suitable driver name, and implement the necessary functions for the database.

An SDBC driver is simply the implementation of some SDBC services previously discussed.

SDBC Driver
The SDBC driver consists of seven services. Each service needs to be defined and are described in the next sections. Below is a list of all the services that define the driver:


 * , a singleton which creates the connection object.
 * , creates,   and gives access to the.
 * , returns information about the used database.
 * , creates.
 * , creates  in conjunction with parameters.
 * , fetches the data returned by an SQL statement.
 * , describes the columns of a.

The relationship between these services is depicted in the illustration below.



Driver Service
The  service is the entry point to create the first contact with any database. As shown in the illustration above, the class that implements the service  is responsible for creating a connection object that represents the database on the client side.

The class must be derived from the interface that defines the methods needed to create a connection object. The code in the following lines shows a snippet of a driver class.

The main methods of this class are  and connect:


 * The method  is called every time a user wants to create a connection through the , because the   decides the   it should ask to connect to the given URL. Therefore this method should be small and run very fast.
 * The method  is called after the method   is invoked and returned true. The   could be seen as a factory method that creates   services specific for a driver implementation. To accomplish this, the   class must be singleton. Singleton means that only one instance of the   class may exist at the same time.

If more information is required about the other methods, refer to com.sun.star.sdbc.Driver for a complete description.

Connection Service
The com.sun.star.sdbc.Connection is the database client side. It is responsible for the creation of the Statements and the information about the database itself. The service consists of three interfaces that have to be supported:


 * The interface that is responsible to close the connection when it is disposed.
 * The interface that controls the chaining of warnings which may occur on every call.
 * The interface that is the main interface to the database.

The first two interfaces introduce some access and closing mechanisms that can be best described inside the code fragment of the  class. To understand the interface, we must have a closer look at some methods. The others not described are simple enough to handle them in the code fragment.

First there is the method  that returns an object which implements the interface. This object has many methods and depends on the capabilities of the database. Most return values are found in the database documentation or in the first step, assuming some values match. The methods, such as,   and   are described in the next chapter.

The following methods are used to create statements. Each of them is a factory method that creates the three different kinds of statements.

All other methods can be omitted at this stage. For detailed descriptions, refer to the API Reference Manual.

XDatabaseMetaData Interface
The interface is the largest interface existing in the SDBC API. This interface knows everything about the used database. It provides information, such as the available tables with their columns, keys and indexes, and information about identifiers that should be used. This chapter explains some of the methods that are frequently used and how they are used to achieve a robust Driver.

Any other  method can be implemented step by step. For the first step they return an empty  object that contains no rows. It is not allowed to return NULL here.

The skeleton driver defines empty ResultSets for these get methods.

Statements
Statements are used to create  or to update the database. The  method creates new. The following code snippet shows how the new  is created. There can be only one  at a time.

The  methods only return the rows that were affected by the given SQL statement. The last method execute returns true when a  object is returned when calling the method , otherwise it returns false. All other methods have to be implemented.

PreparedStatement
The  is used when an SQL statement should be executed more than once. In addition to the statement class, it must support the ability to provide information about the parameters when they exist. For this reason, this class must support the interface and also the  interface to set values for their parameters.

Result Set
The  needs to be implemented. For the first step, only forward  could be implemented, but it is recommended to support all   methods.

Support Scalar Functions
SDBC supports numeric, string, time, date, system, and conversion functions on scalar values. The Open Group CLI specification provides additional information on the semantics of the scalar functions. The functions supported are listed below for reference.

If a DBMS supports a scalar function, the driver should also. Scalar functions are supported by different DBMSs with different syntax, it is the driver's job to map the functions into the appropriate syntax or to implement the functions directly in the driver.

By calling metadata methods, a user can find out which functions are supported. For example, the method  returns a comma separated list of the Open Group CLI names of the numeric functions supported. Similarly, the method  returns a list of string functions supported.

In the following table, the scalar functions are listed by category.

Handling Unsupported Functionality
Some variation is allowed for drivers written for databases that do not support certain functionality. For example, some databases do not support out parameters with stored procedures. In this case, the  methods that deal with out parameters (  and the various   methods) do not apply, and they should be implemented in such a way that they throw a com.sun.star.sdbc.SQLException.

The following features are optional in drivers for DBMSs that do not support them. When a DBMS does not support a feature, the methods that support the feature may throw a. The following list of optional features indicate if the com.sun.star.sdbc.XDatabaseMetaData methods are supported by the DBMS and driver.


 * scrollable result sets:
 * modifiable result sets:
 * batch updates:
 * SQL3 data types:
 * storage and retrieval of Java objects:
 * returns descriptions of the user defined types in a given schema
 * returns descriptions of the data types available in the DBMS.

Extending Database Drivers
In the following section, extending existing SDBC drivers by extensions is described. These section is valid from since: OpenOffice.org 3.3

Most drivers miss to support special features like to alter view definitions or to add or drop key of a table. Therefore these interfaces can be implemented by an extension.

To enable the needed feature the extension has to extend the properties entry of the configuration of the driver. The configuration entry below the properties entry will be checked and if the service name can be instantiated it will be used to do the job. Below you'll see a table of mapping from configuration entry to service name.

In favor to allow to get the definition of queries, forms and reports from other locations as the default file format specification, an extension may implement the service. The configuration entries are named additionally to the interfaces defined in the service, the interface has to be implemented which will be called with a argument called "DatabaseDocument" containing the database document.
 * Forms
 * Reports

To allow to support command definitions from other locations, an extension may implement the service. The configuration entry is named A difference is that the interface will be called with a "DataSource" argument.
 * CommandDefinitions

The configuration fragment below shows how to define which service should be created to extend the view support and to extend the table alteration support.