A database is incorporated into OpenOffice.org by creating what is commonly referred to as a data source. The user interface provides a corresponding option for creating data sources in the Extras menu. You can also create data sources and work with them using OpenOffice.org Basic.
A database context object that is created using the createUnoService function serves as the starting point for accessing a data source. This based on the com.sun.star.sdb.DatabaseContext service and is the root object for all database operations.
The following example shows how a database context can be created and then used to determine the names of all data sources available. It displays the names in a message box.
Dim DatabaseContext As Object Dim Names Dim I As Integer DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") Names = DatabaseContext.getElementNames() For I = 0 To UBound(Names()) MsgBox Names(I) Next I
The individual data sources are based on the com.sun.star.sdb.DataSource service and can be determined from the database context using the getByName method:
Dim DatabaseContext As Object Dim DataSource As Object DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") DataSource = DatabaseContext.getByName("Customers")
The example creates a DataSource object for a data source called Customers.
Data sources provide a range of properties, which in turn provide general information about the origin of the data and information about access methods. The properties are:
- Name (String)
- name of data source
- URL (String)
- URL of data source in the form of jdbc: subprotocol : subname or sdbc: subprotocol : subname
- Settings (Array)
- array containing PropertyValue-pairs with connection parameters (usually at least user name and password)
- User (String)
- user name
- Password (String)
- user password (is not saved)
- IsPasswordRequired (Boolean)
- the password is needed and is interactively requested from user.
- IsReadOnly (Boolean)
- permits read-only access to the database
- NumberFormatsSupplier (Object)
- object containing the number formats available for the database (supports the com.sun.star.util.XNumberFormatsSupplier interface)
- TableFilter (Array)
- list of table names to be displayed
- TableTypeFilter (Array)
- list of table types to be displayed. Values available are TABLE, VIEW and SYSTEM TABLE
- SuppressVersionColumns (Boolean)
- suppresses the display of columns that are used for version administration
Predefined queries can be assigned to a data source. OpenOffice.org notes the SQL commands of queries so that they are available at all times. Queries are used to simplify working with databases because they can be opened with a simple mouse click and also provide users without any knowledge of SQL with the option of issuing SQL commands.
An object which supports the com.sun.star.sdb.QueryDefinition service is concealed behind a query. The queries are accessed by means of the QueryDefinitions method of the data source.
The following example lists the names of data source queries can be established in a message box.
Dim DatabaseContext As Object Dim DataSource As Object Dim QueryDefinitions As Object Dim QueryDefinition As Object Dim I As Integer DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") DataSource = DatabaseContext.getByName("Customers") QueryDefinitions = DataSource.getQueryDefinitions() For I = 0 To QueryDefinitions.Count() - 1 QueryDefinition = QueryDefinitions(I) MsgBox QueryDefinition.Name Next I
In addition to the Name property used in the example, the com.sun.star.sdb.QueryDefinition provides a whole range of other properties. These are:
- Name (String)
- query name
- Command (String)
- SQL command (typically a SELECT command)
The following example shows how a query object can be created in a program-controlled manner and can be assigned to a data source.
Dim DatabaseContext As Object Dim DataSource As Object Dim QueryDefinitions As Object Dim QueryDefinition As Object Dim I As Integer DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") DataSource = DatabaseContext.getByName("Customers") QueryDefinitions = DataSource.getQueryDefinitions() QueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition") QueryDefinition.Command = "SELECT * FROM Customer" QueryDefinitions.insertByName("NewQuery", QueryDefinition)
The query object is first created using the createUnoService call, then initialized, and then inserted into the QueryDefinitions object by means of insertByName.
|Content on this page is licensed under the Public Documentation License (PDL).|