 Illustration 13.1: The Dialog "Database Registration" |
[ Previous document | Content Table | Next document ]
The goal of the OpenOffice.org API database integration is to provide platform independent database connectivity for OpenOffice.org API. Well 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 OpenOffice.org 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 OpenOffice.org Calc into the current version of OpenOffice.org API.
Since SDBC drivers are UNO components, it is possible to write drivers for data sources and thus extend the database connectivity of OpenOffice.org API.
The OpenOffice.org API database integration is based on SQL. This section discusses how the OpenOffice.org API handles various SQL dialects and how it integrates with data sources that do not understand SQL.
OpenOffice.org 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.
OpenOffice.org 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 9.3.5 Spreadsheet Documents - Working with Spreadsheets - Database Operations and 14 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 OpenOffice.org 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 OpenOffice.org API environment.
The OpenOffice.org 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.
The following example queries the bibliography database that is delivered with the OpenOffice.org distribution. The basic steps are:
Create a com.sun.star.sdb.RowSet.
Configure com.sun.star.sdb.RowSet to select from the table "biblio" in the data source "Bibliography".
Execute it.
Iterate over its rows.
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 13.3.1 Database Access - Manipulating Data - The RowSet Service. (Database/OpenQuery.java)
protected void openQuery() throws com.sun.star.uno.Exception, java.lang.Exception {
xRemoteServiceManager = this.getRemoteServiceManager(
"uno:socket,host=localhost,port=2083;urp;StarOffice.ServiceManager");
// first we create our RowSet object and get its XRowSet interface
Object rowSet = xRemoteServiceManager.createInstanceWithContext(
"com.sun.star.sdb.RowSet", xRemoteContext);
com.sun.star.sdbc.XRowSet xRowSet = (com.sun.star.sdbc.XRowSet)
UnoRuntime.queryInterface(com.sun.star.sdbc.XRowSet.class, rowSet);
// set the properties needed to connect to a database
XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xRowSet);
// the DataSourceName can be a data source registered with OpenOffice.org, among other possibilities
xProp.setPropertyValue("DataSourceName", "Bibliography");
// the CommandType must be TABLE, QUERY or COMMAND – here we use COMMAND
xProp.setPropertyValue("CommandType", new Integer(com.sun.star.sdb.CommandType.COMMAND));
// the Command could be a table or query name or a SQL command, depending on the CommandType
xProp.setPropertyValue("Command", "SELECT IDENTIFIER, AUTHOR FROM biblio");
// if your database requires logon, you can use the properties User and Password
// xProp.setPropertyValue("User", "JohnDoe");
// xProp.setPropertyValue("Password", "mysecret");
xRowSet.execute();
// prepare the XRow and XColumnLocate interface for column access
// XRow gets column values
com.sun.star.sdbc.XRow xRow = (com.sun.star.sdbc.XRow)UnoRuntime.queryInterface(
com.sun.star.sdbc.XRow.class, xRowSet);
// XColumnLocate finds columns by name
com.sun.star.sdbc.XColumnLocate xLoc = (com.sun.star.sdbc.XColumnLocate)UnoRuntime.queryInterface(
com.sun.star.sdbc.XColumnLocate.class, xRowSet);
// print output header
System.out.println("Identifier\tAuthor");
System.out.println("----------\t------");
// output result rows
while ( xRowSet.next() ) {
String ident = xRow.getString(xLoc.findColumn("IDENTIFIER"));
String author = xRow.getString(xLoc.findColumn("AUTHOR"));
System.out.println(ident + "\t\t" + author);
}
// insert a new row
// XResultSetUpdate for insertRow handling
com.sun.star.sdbc.XResultSetUpdate xResultSetUpdate = (com.sun.star.sdbc.XResultSetUpdate)
UnoRuntime.queryInterface(
com.sun.star.sdbc.XResultSetUpdate.class, xRowSet);
// XRowUpdate for row updates
com.sun.star.sdbc.XRowUpdate xRowUpdate = (com.sun.star.sdbc.XRowUpdate)
UnoRuntime.queryInterface(
com.sun.star.sdbc.XRowUpdate.class, xRowSet);
// move to insertRow buffer
xResultSetUpdate.moveToInsertRow();
// edit insertRow buffer
xRowUpdate.updateString(xLoc.findColumn("IDENTIFIER"), "GOF95");
xRowUpdate.updateString(xLoc.findColumn("AUTHOR"), "Gamma, Helm, Johnson, Vlissides");
// write buffer to database
xResultSetUpdate.insertRow();
// throw away the row set
com.sun.star.lang.XComponent xComp = (com.sun.star.lang.XComponent)UnoRuntime.queryInterface(
com.sun.star.lang.XComponent.class, xRowSet);
xComp.dispose();
}
In the OpenOffice.org graphical user interface (GUI), define Open Office database files using the database application OpenOffice.org Base, and register them in the Tools – Options – OpenOffice.org Database – Databasesdialog 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 com.sun.star.sdb.DatabaseContext service. The database context is a container for data sources. It is a singleton, that is, it may exist only once in a running OpenOffice.org API instance and can be accessed by creating it at the global service manager of the office.
|
Illustration 13.1: The Dialog "Database Registration" |
The database context is the entry point for applications that need to connect to a data source already defined in the OpenOffice.org API. Additionally, it is used to create new data sources and add them to OpenOffice.org 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 com.sun.star.sdb.XCompletedConnection interface.
Existing data sources are obtained from the database context at its interfaces com.sun.star.container.XNameAccess and com.sun.star.container.XEnumeration. Their methods getByName() and createEnumeration() deliver the com.sun.star.sdb.DataSource services defined in the OpenOffice.org GUI.
Since OpenOffice.org 2.0, getByName() 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: (Database/CodeSamples.java)
// prints all data sources
public static void printDataSources(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// retrieve the DatabaseContext and get its com.sun.star.container.XNameAccess interface
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
XNameAccess.class, _rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// print all DataSource names
String aNames [] = xNameAccess.getElementNames();
for (int i=0;i<aNames.length;++i)
System.out.println(aNames[i]);
}
The com.sun.star.sdb.DataSource service includes all the features of a database defined in OpenOffice.org API. DataSource provides the following properties for its knowledge about how to connect to a database and which tables to display:
|
Properties of com.sun.star.sdb.DataSource | |
|
[readonly] string ─ The name of the data source. | |
|
string ─ Indicates a database URL. Valid URL formats are: | |
|
sequence< com.sun.star.beans.PropertyValue >. A list of arbitrary string tag or value pairs as connection arguments. | |
|
String ─ The login name of the current user. | |
|
string ─ The password of the current user. It is not stored with the data source. | |
|
boolean ─ Indicates that a password is always necessary and might be interactively requested from the user by an interaction handler. | |
|
[readonly] boolean ─ Determines if database contents may be modified. | |
|
[readonly] com.sun.star.util.XNumberFormatsSupplier. Provides an object for number formatting. | |
|
sequence< string >. A list of tables the data source should display. If empty, all tables are hidden. Valid placeholders are % and ?. | |
|
sequence< string >. A list of table types the DataSource should display. If empty, all table types are rejected. Possible type strings are TABLE, VIEW, and SYSTEM TABLE. | |
|
boolean ─ Indicates that components displaying data obtained from this data source should suppress columns used for versioning. | |
All other capabilities of a DataSource,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, 13.2.2 Database Access - Data Sources in OpenOffice.org API - DataSources - Queries.
com.sun.star.sdb.XCompletedConnection connects to a database. It asks the user to supply necessary information before it connects. The section 13.2.3 Database Access - Data Sources in OpenOffice.org API - Connections - 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 OpenOffice.org API documents containing form components. Although it is optional, it is implemented for all data sources in OpenOffice.org API. The section 13.2.2 Database Access - Data Sources in OpenOffice.org API - DataSources - 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 Open Office database file format.com.sun.star.sdb.XFormDocumentsSupplier provides access to forms stored inside the Open Office database file.
com.sun.star.sdb.XReportDocumentsSupplier provides access to reports stored inside the Open Office database file.
com.sun.star.sdb.OfficeDatabaseDocument provides all interfaces which the com.sun.star.document.OfficeDocument service supports.
New data sources have to be created by the com.sun.star.lang.XSingleServiceFactory interface of the database context. A new data source can be registered with the database context at its com.sun.star.uno.XNamingService interface and the necessary properties set.
The lifetime of data sources is controlled through the interfaces com.sun.star.lang.XSingleServiceFactory, com.sun.star.uno.XNamingService and com.sun.star.container.XContainer of the database context.
The method createInstance() of XSingleServiceFactory creates new generic data sources. They are added to the database context using registerObject() at the interface com.sun.star.uno.XNamingService. The XNamingService allows registering data sources, as well as revoking the registration. The following are the methods defined for XNamingService:
void registerObject( [in] string Name, [in] com::sun::star::uno::XInterface Object)
void revokeObject( [in] string Name)
com::sun::star::uno::XInterface getRegisteredObject( [in] string Name)
Before data sources can be registered at the database context, they have to be stored with the com.sun.star.frame.XStorable interface. The method storeAsURL 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 URL property has to be present, and for Adabas D the property IsPasswordRequired should be true, otherwise no interactive connection can be established. The password dialog requests a user name by setting the User property. (Database/CodeSamples.java)
// creates a new DataSource
public static void createNewDataSource(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// the XSingleServiceFactory of the database context creates new generic
// com.sun.star.sdb.DataSources (!)
// retrieve the database context at the global service manager and get its
// XSingleServiceFactory interface
XSingleServiceFactory xFac = (XSingleServiceFactory)UnoRuntime.queryInterface(
XSingleServiceFactory.class, _rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// instantiate an empty data source at the XSingleServiceFactory
// interface of the DatabaseContext
Object xDs = xFac.createInstance();
// register it with the database context
XNamingService xServ = (XNamingService)UnoRuntime.queryInterface(XNamingService.class, xFac);
XStorable store = ( XStorable)UnoRuntime.queryInterface(XStorable.class, xDs);
XModel model = ( XModel)UnoRuntime.queryInterface(XModel.class, xDs);
store.storeAsURL(“file:///c:/test.odb”,model.getArgs());
xServ.registerObject("NewDataSourceName", xDs);
// setting the necessary data source properties
XPropertySet xDsProps = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xDs);
// Adabas D URL
xDsProps.setPropertyValue("URL", "sdbc:adabas::MYDB1");
// force password dialog
xDsProps.setPropertyValue("IsPasswordRequired", new Boolean(true));
// suggest dsadmin as user name
xDsProps.setPropertyValue("User", "dsadmin");
store.store();
}
The various possible database URLs are discussed in the section 13.2.3 Database Access - Data Sources in OpenOffice.org API - Connections - Driver Specifics.
To edit an existing data source, retrieve it by name or by file URL from the com.sun.star.container.XNameAccess interface of the database context and use its com.sun.star.beans.XPropertySet interface to configure it, as required. To store the newly edited data source, you must use the com.sun.star.frame.XStorable interface.
A com.sun.star.sdb.QueryDefinition encapsulates a definition of an SQL statement stored in OpenOffice.org 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 QueryDefinition against a different database by changing the underlying DataSource properties. It can also be created without being connected to a database.
The purpose of the query services available at a DataSource is to define and edit queries. The query services by themselves do not offer methods to execute queries. To open a query, use a com.sun.star.sdb.RowSet service or the com.sun.star.sdb.XCommandPreparation interface of a connection. See the sections 13.3.1 Database Access - Manipulating Data - The RowSet Service and 13.3.6 Database Access - Manipulating Data - PreparedStatement From DataSource Queries for additional details.
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 com.sun.star.sdb.XQueryDefinitionsSupplier interface of the data source, which has a single method for this purpose:
com::sun::star::container::XNameAccess getQueryDefinitions()
The DefinitionContainer is not only an XNameAccess, but a com.sun.star.container.XNameContainer, that is, add new query definitions by name (see 2 First Steps). Besides the name access, obtain query definitions through com.sun.star.container.XIndexAccess and com.sun.star.container.XEnumerationAccess.
 Illustration 13.3: DefinitionContainer And QueryDefinition |
New query definitions are created by the com.sun.star.lang.XSingleServiceFactory interface of the query definitions container. Its method createInstance() provides an empty QueryDefinition to configure, as required. Then, the new query definition is added to the DefinitionContainer using insertByName()at the XNameContainer interface.
 |
The optional interface com.sun.star.util.XRefreshable is not supported by the DefinitionContainer implementation. |
A QueryDefinition is configured through the following properties:
|
Properties of com.sun.star.sdb.QueryDefinition | |
|
string ─ The name of the queryDefinition. | |
|
string ─ The SQL SELECT command. | |
|
boolean ─ If true, determines that the query must not be touched by the built-in SQL parser of OpenOffice.org API. | |
|
string ─ The name of the update table catalog used to identify tables, supported by some databases. | |
|
string ─ The name of the update table schema used to identify tables, supported by some databases. | |
|
string The name of the update table catalog used to identify tables, supported by some databases The name of the table which should be updated. This is usually used for queries based on more than one table and makes such queries partially editable. The property UpdateTableName must contain the name of the table with unique rows in the result set. In a 1:n join this is usually the table on the n side of the join. | |
The following example adds a new query definition Query1 to the data source Bibliography that is provided with OpenOffice.org API. (Database/CodeSamples.java)
// creates a new query definition named Query1
public static void createQuerydefinition(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
XNameAccess xNameAccess = (XNameAccess) UnoRuntime.queryInterface(
XNameAccess.class, _rMSF.createInstance( "com.sun.star.sdb.DatabaseContext") );
// we use the datasource Bibliography
XQueryDefinitionsSupplier xQuerySup = (XQueryDefinitionsSupplier) UnoRuntime.queryInterface(
XQueryDefinitionsSupplier.class, xNameAccess.getByName( "Bibliography" ));
// get the container for query definitions
XNameAccess xQDefs = xQuerySup.getQueryDefinitions();
// for new query definitions we need the com.sun.star.lang.XSingleServiceFactory interface
// of the query definitions container
XSingleServiceFactory xSingleFac = (XSingleServiceFactory)UnoRuntime.queryInterface(
XSingleServiceFactory.class, xQDefs);
// order a new query and get its com.sun.star.beans.XPropertySet interface
XPropertySet xProp = (XPropertySet) UnoRuntime.queryInterface(
XPropertySet.class, xSingleFac.createInstance());
// configure the query
xProp.setPropertyValue("Command","SELECT * FROM biblio");
xProp.setPropertyValue("EscapeProcessing", new Boolean(true));
// insert it into the query definitions container
XNameContainer xCont = (XNameContainer) UnoRuntime.queryInterface(
XNameContainer.class, xQDefs);
try{
if ( xCont.hasByName("Query1") )
xCont.removeByName("Query1");
}catch(com.sun.star.uno.Exception e){}
xCont.insertByName("Query1", xProp);
XStorable store = ( XStorable)UnoRuntime.queryInterface(XStorable.class, xQuerySup);
store.store();
}
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 13.2.3 Database Access - Data Sources in OpenOffice.org API - Connections - 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.
 Illustration 13.4: Connection, QueryComposer And Query in the sdb Module |
The Connection gives you a com.sun.star.sdbcx.Container of com.sun.star.sdb.Query services. These Query objects are different from QueryDefinitions.
The com.sun.star.sdb.Query service inherits both the properties from com.sun.star.sdb.QueryDefinition service described previously, and the properties defined in the service com.sun.star.sdb.DataSettings. Use DataSettings to customize the appearance of the query when used in the OpenOffice.org API GUI or together with a com.sun.star.sdb.RowSet.
|
Properties of com.sun.star.sdb.DataSettings | |
|
string ─ An additional filter for the data object, WHERE clause syntax. | |
|
boolean ─ Indicates if the filter should be applied, default is FALSE. | |
|
string ─ Is an additional sort order definition. | |
|
struct com.sun.star.awt.FontDescriptor. Specifies the font attributes for displayed data. | |
|
long ─ Specifies the height of a data row. | |
|
long ─ Specifies the text color for displayed text in 0xAARRGGBB notation | |
In addition to these properties, the com.sun.star.sdb.Query 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 com.sun.star.sdbcx.XAppend interface. This is an alternative to the connection-independent method to create new queries as discussed above. The section 13.4.3 Database Access - Database Design - Using SDBCX to Access the Database Design - The Descriptor Pattern explains how to use descriptors to append new elements to database objects.
The com.sun.star.sdbcx.XRename interface is used to rename a query. It has one method:
void rename( [in] string newName)
The interface com.sun.star.sdbcx.XColumnsSupplier grants access to the column settings of the query through its single method getColumns():
com::sun::star::container::XNameAccess getColumns()
The columns returned by getColumns() are com.sun.star.sdb.Column services that provide column information and the ability to improve the appearance of columns. This service is explained in the section 13.2.2 Database Access - Data Sources in OpenOffice.org API - DataSources - Tables and Columns.
The following code sample connects to Bibliography, and prints the column names and types of the previously defined query Query1. (Database/CodeSamples.java)
public static void printQueryColumnNames(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
XNameAccess.class,_rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// we use Bibliography
XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName("Bibliography"));
// simple way to connect
XConnection con = xDS.getConnection("", "");
// we need the XQueriesSupplier interface of the connection
XQueriesSupplier xQuerySup = (XQueriesSupplier)UnoRuntime.queryInterface(
XQueriesSupplier.class, con);
// get container with com.sun.star.sdb.Query services
XNameAccess xQDefs = xQuerySup.getQueries();
// retrieve XColumnsSupplier of Query1
XColumnsSupplier xColsSup = (XColumnsSupplier) UnoRuntime.queryInterface(
XColumnsSupplier.class,xQDefs.getByName("Query1"));
XNameAccess xCols = xColsSup.getColumns();
// Access column property TypeName
String aNames [] = xCols.getElementNames();
for (int i=0;i<aNames.length;++i) {
Object col = xCols.getByName(aNames[i]);
XPropertySet xColumnProps = (XPropertySet)UnoRuntime.queryInterface(
XPropertySet.class, col);
System.out.println(aNames[i] + " " + xColumnProps.getPropertyValue("TypeName"));
}
}
The service com.sun.star.sdb.SingleSelectQueryComposer is a tool that analyzes and composes single select statement strings. It is a replacement for the service com.sun.star.sdb.SQLQueryComposer. The query composer is divided into two parts. The first part defines the analyzing of the single select statement. The service com.sun.star.sdb.SingleSelectQueryAnalyzer 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 com.sun.star.sdb.SingleSelectQueryComposer extends the service com.sun.star.sdb.SingleSelectQueryAnalyzer 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 com.sun.star.lang.XMultiServiceFactory interface of a com.sun.star.sdb.Connection:
com::sun::star::uno::XInterface createInstance( [in] string aServiceSpecifier )
The interface com.sun.star.sdb.XSingleSelectQueryAnalyzer is used to supply the SingleSelectQueryComposer with the necessary information. It has the following methods:
// provide SQL string
void setQuery( [in] string command)
string getQuery()
// filter
string getFilter()
sequence< sequence< com::sun::star::beans::PropertyValue > > getStructuredFilter()
// GROUP BY
string getGroup();
com::sun::star::container::XIndexAccess getGroupColumns();
// HAVING
string getHavingClause();
sequence< sequence<com::sun::star::beans::PropertyValue> > getStructuredHavingFilter();
// control the ORDER BY clause
string getOrder()
com::sun::star::container::XIndexAccess getOrderColumns();
The example below shows a simple test case for the com.sun.star.sdb.SingleSelectQueryComposer:
public void testSingleSelectQueryComposer() {
log.println("testing SingleSelectQueryComposer");
try
{
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(XNameAccess.class,
((XMultiServiceFactory)param.getMSF()).createInstance("com.sun.star.sdb.DatabaseContext"));
// we use the first datasource
XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(XDataSource.class,
xNameAccess.getByName( "Bibliography" ));
log.println("check XMultiServiceFactory");
XMultiServiceFactory xConn = (XMultiServiceFactory)UnoRuntime.queryInterface(XMultiServiceFactory.class, xDS.getConnection(new String(),new String()));
log.println("check getAvailableServiceNames");
String[] sServiceNames = xConn.getAvailableServiceNames();
assure("Service 'SingleSelectQueryComposer' not supported" ,sServiceNames[0].equals("com.sun.star.sdb.SingleSelectQueryComposer"));
XSingleSelectQueryAnalyzer xQueryAna = (XSingleSelectQueryAnalyzer)
UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class,xConn.createInstance( sServiceNames[0]));
log.println("check setQuery");
xQueryAna.setQuery("SELECT * FROM \"biblio\"");
assure("Query not identical", xQueryAna.getQuery().equals("SELECT * FROM \"biblio\""));
// XSingleSelectQueryComposer
XSingleSelectQueryComposer xComposer = (XSingleSelectQueryComposer)
UnoRuntime.queryInterface(XSingleSelectQueryComposer.class,xQueryAna);
log.println("check setFilter");
// filter
xComposer.setFilter("\"Identifier\" = 'BOR02b'");
assure("Query not identical:" + xQueryAna.getFilter() + " -> \"Identifier\" = 'BOR02b'", xQueryAna.getFilter().equals("\"Identifier\" = 'BOR02b'"));
log.println("check setGroup");
// group by
xComposer.setGroup("\"Identifier\"");
assure("Query not identical:" + xQueryAna.getGroup() + " -> \"Identifier\"", xQueryAna.getGroup().equals("\"Identifier\""));
log.println("check setOrder");
// order by
xComposer.setOrder("\"Identifier\"");
assure("Query not identical:" + xQueryAna.getOrder() + " -> \"Identifier\"", xQueryAna.getOrder().equals("\"Identifier\""));
log.println("check setHavingClause");
// having
xComposer.setHavingClause("\"Identifier\" = 'BOR02b'");
assure("Query not identical:" + xQueryAna.getHavingClause() + " -> \"Identifier\" = 'BOR02b'", xQueryAna.getHavingClause().equals("\"Identifier\" = 'BOR02b'"));
log.println("check getOrderColumns");
// order by columns
XIndexAccess xOrderColumns = xQueryAna.getOrderColumns();
assure("Order columns doesn't exist -> \"Identifier\"", xOrderColumns != null && xOrderColumns.getCount() == 1 && xOrderColumns.getByIndex(0) != null);
log.println("check getGroupColumns");
// group by columns
XIndexAccess xGroupColumns = xQueryAna.getGroupColumns();
assure("Group columns doesn't exist -> \"Identifier\"", xGroupColumns != null && xGroupColumns.getCount() == 1 && xGroupColumns.getByIndex(0) != null);
log.println("check getColumns");
// XColumnsSupplier
XColumnsSupplier xSelectColumns = (XColumnsSupplier)
UnoRuntime.queryInterface(XColumnsSupplier.class,xQueryAna);
assure("Select columns doesn't exist", xSelectColumns != null && xSelectColumns.getColumns() != null && xSelectColumns.getColumns().getElementNames().length != 0);
log.println("check structured filter");
// structured filter
xQueryAna.setQuery("SELECT \"Identifier\", \"Type\", \"Address\" FROM \"biblio\" \"biblio\"");
xComposer.setFilter(complexFilter);
PropertyValue[][] aStructuredFilter = xQueryAna.getStructuredFilter();
xComposer.setFilter("");
xComposer.setStructuredFilter(aStructuredFilter);
assure("Structured Filter not identical" , xQueryAna.getFilter().equals(complexFilter));
log.println("check structured having");
// structured having clause
xComposer.setHavingClause(complexFilter);
PropertyValue[][] aStructuredHaving = xQueryAna.getStructuredHavingFilter();
xComposer.setHavingClause("");
xComposer.setStructuredHavingFilter(aStructuredHaving);
assure("Structured Having Clause not identical" , xQueryAna.getHavingClause().equals(complexFilter));
}
catch(Exception e)
{
assure("Exception catched: " + e,false);
}
}
In the previous code example, a query command is passed to setQuery(), then the criteria for WHERE, and GROUP BY, and HAVING, and ORDER BY is added. The WHERE expressions are passed without the WHERE keyword to setFilter(), and the method setOrder(), with comma-separated ORDER BY columns or column numbers, is provided.
As an alternative, add WHERE conditions using appendFilterByColumn(). This method expects a com.sun.star.sdb.DataColumn service providing the name and the value for the filter. Similarly, the method appendOrderByColumn() adds columns that are used for ordering. The same applies to appendGroupByColumn() and appendHavingFilterByColumn(). These columns can come from the RowSet.
The Orignal property at the service com.sun.star.sdb.SingleSelectQueryAnalyzer holds the original single select statement.
The methods getQuery(), getFilter() and getOrder() return the complete SELECT, WHERE and ORDER BY part of the single select statement as a string.
The method getStructuredFilter() 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 com.sun.star.sdbcx.XTablesSupplier provides access to the tables that are used in the FROM part of the SQL-Statement:
com::sun::star::container::XNameAccess getTables()
The interface com.sun.star.sdbcx.XColumnsSupplier provides the selected columns, which are listed after the SELECT keyword:
com::sun::star::container::XNameAccess getColumns()
The interface com.sun.star.sdb.XParametersSupplier provides the parameters, which are used in the where clause:
com::sun::star::container::XIndexAccess getParameters()
The service com.sun.star.sdb.SQLQueryComposer is a tool that composes SQL SELECT strings. It hides the complexity of parsing and evaluating SQL statements, and provides methods to configure an SQL statement with filtering and ordering criteria.
|
|
The com.sun.star.sdb.SQLQueryComposer service is deprecated. Though you can still use it in your programs, you are encouraged to replace it with the SingleSelectQueryComposer service. |
A query composer is retrieved over the com.sun.star.sdb.XSQLQueryComposerFactory interface of a com.sun.star.sdb.Connection:
com::sun::star::sdb::XSQLQueryComposer createQueryComposer()
Its interface com.sun.star.sdb.XSQLQueryComposer is used to supply the SQLQueryComposer with the necessary information. It has the following methods:
// provide SQL string
void setQuery( [in] string command)
string getQuery()
string getComposedQuery()
// control the WHERE clause
void setFilter( [in] string filter)
void appendFilterByColumn( [in] com::sun::star::beans::XPropertySet column)
string getFilter()
sequence< sequence< com::sun::star::beans::PropertyValue > > getStructuredFilter()
// control the ORDER BY clause
void setOrder( [in] string order)
void appendOrderByColumn( [in] com::sun::star::beans::XPropertySet column, [in] boolean ascending)
string getOrder()
In the above method, a query command, such as "SELECT Identifier, Address, Author FROM biblio" is passed to setQuery(), then the criteria for WHERE and ORDER BY is added. The WHERE expressions are passed without the WHERE keyword to setFilter(), and the method setOrder() with comma-separated ORDER BY columns or column numbers is provided.
As an alternative, add WHERE conditions using appendFilterByColumn(). This method expects a com.sun.star.sdb.DataColumn service providing the name and the value for the filter. Similarly, the method appendOrderByColumn() adds columns that are used for ordering. These columns could come from the RowSet.
Retrieve the resulting SQL string from getComposedQuery().
The methods getQuery(), getFilter() and getOrder() return the SELECT, WHERE and ORDER BY part of the SQL command as a string.
The method getStructuredFilter() 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.
// prints the structured filter
public static void printStructeredFilter(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
XNameAccess.class, _rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// we use the first datasource
XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName("Bibliography"));
XConnection con = xDS.getConnection("", "");
XQueriesSupplier xQuerySup = (XQueriesSupplier)UnoRuntime.queryInterface(
XQueriesSupplier.class, con);
XNameAccess xQDefs = xQuerySup.getQueries();
XPropertySet xQuery = (XPropertySet) UnoRuntime.queryInterface(
XPropertySet.class,xQDefs.getByName("Query1"));
String sCommand = (String)xQuery.getPropertyValue("Command");
XSQLQueryComposerFactory xQueryFac = (XSQLQueryComposerFactory) UnoRuntime.queryInterface(
XSQLQueryComposerFactory.class, con);
XSQLQueryComposer xQComposer = xQueryFac.createQueryComposer();
xQComposer.setQuery(sCommand);
PropertyValue aFilter [][] = xQComposer.getStructuredFilter();
for (int i=0; i<aFilter.length; ) {
System.out.println("( ");
for (int j=0; j<aFilter[i].length; ++j)
System.out.println("Name: " + aFilter[i][j].Name + " Value: " + aFilter[i][j].Value);
System.out.println(")");
++i;
if (i<aFilter.length )
System.out.println(" OR ");
}
}
}
The interface com.sun.star.sdbcx.XTablesSupplier provides access to the tables that are used in the “FROM” part of the SQL-Statement:
com::sun::star::container::XNameAccess getTables()
The interface com.sun.star.sdbcx.XColumnsSupplier provides the selected columns, which are listed after the SELECT keyword:
com::sun::star::container::XNameAccess getColumns()
Since OpenOffice.org 2.0, you can not only link to documents that belong to a data source, but you can store your forms and reports within the Open Office database file.
The interface com.sun.star.sdb.XFormDocumentsSupplier, supplied by the DataSource, provides access to the forms stored in the database file of the data source. It has one method:
com::sun::star::container::XNameAccess getFormDocuments()
The interface com.sun.star.sdb.XReportDocumentsSupplier provides access to the reports stored in the database file of the data source. It has one method:
com::sun::star::container::XNameAccess getReportDocuments()
The returned service is a com.sun.star.sdb.DocumentContainer. The DocumentContainer is not only an XNameAccess, but a com.sun.star.container.XNameContainer, which means that new forms or reports are added using insertByName() as described in the 2 First Steps chapter. To support the creation of hierarchies, the service com.sun.star.sdb.DocumentContainer additionally supplies the interfaces com.sun.star.container.XHierarchicalNameContainer and com.sun.star.container.XHierarchicalNameAccess. The interfaces com.sun.star.container.XHierarchicalNameContainer and com.sun.star.container.XHierarchicalNameAccess can be used to create folder hierarchies and to organize forms or reports in different sub folders.
Along with the name access, forms and reports are obtained through com.sun.star.container.XIndexAccess, and com.sun.star.container.XEnumerationAccess.
The interface com.sun.star.lang.XMultiServiceFactory is used to create new forms or reports. The method createInstanceWithArguments() of XMultiServiceFactory creates a new document definition. Whether the document is a form or a report depends on the container where this object is inserted.
 Illustration 13.5: Relation design of Reports and Forms |
The following are the allowed properties for the document definition:
|
Arguments of createInstanceWithArguments method with com.sun.star.sdb.DocumentDefinition as service name | |
|
Name: Name Value: string ─ Defines the name of the document. | |
|
Name: URL Value: string ─ Points to a extern document. | |
|
Name: ActiveConnection Value: com.sun.star.sdbc.XConnection ─ The connection to be used by the document. | |
|
Name: EmbeddedObject Value: com.sun.star.sdb.DocumentDefinition ─ The document definition that is to be copied. | |
To create a new document definition, only the Name and the ActiveConnection 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 EmbeddedObject must be filled with the document definition to be copied.
The following are the allowed properties for the document container:
|
Arguments of createInstanceWithArguments method with com.sun.star.sdb.Forms or com.sun.star.sdb.Reports as service name | |
|
Name: Name Value: string ─ Defines the name of the document. | |
|
Name: EmbeddedObject Value: com.sun.star.sdb.DocumentDefinition or com.sun.star.sdb.DocumentContainer ─ The document definition (form or report object) or a document container (form container or report container) which is to be copied. | |
When creating a sub folder inside the forms or reports hierarchy, it is enough to set the Name property. If the EmbeddedObject property is set, then it is copied. If the EmbeddedObject supports the XHierarchicalNameAccess, the children are also copied. The EmbeddedObject can be a document definition or a document container.
The service com.sun.star.sdb.DocumentContainer additionally defines the interface com.sun.star.frame.XComponentLoader that is used to get access to the contained document inside the DocumentDefinition and it has one method:
com::sun::star::lang::XComponent loadComponentFromURL(
[in] string URL,
[in] string TargetFrameName,
[in] long SearchFlags,
[in] sequence<com::sun::star::beans::PropertyValue> Arguments)
raises( com::sun::star::io::IOException,
com::sun::star::lang::IllegalArgumentException );
URL: describes the name of the document definition to load,
TargetFrameName: is not used.
SearchFlags: is not used.
Arguments:
1. PropertyValue
Name = ActiveConnection
Value = com.sun.star.sdbc.XConnection The connection that is used when opening the text document.
2. 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)
// opens a form in design mode
public static void openFormInDesignMode(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception
{
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(XNameAccess.class,
_rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// we use the first datasource
XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName( "Bibliography" ));
XConnection con = xDS.getConnection("","");
XFormsSupplier xSup = (XFormsSupplier)UnoRuntime.queryInterface(XFormsSupplier.class, xDS);
XNameAccess xForms = xSup.getFormDocuments();
if ( xForms.hasByName("Form1") ){
Object form = xForms.getByName("Form1"); // to hold ref
{
XComponentLoader loader = (XComponentLoader)UnoRuntime.queryInterface(XComponentLoader.class, xForms);
PropertyValue[] args = new PropertyValue[]{PropertyValue("OpenMode",0,"openDesign")
,PropertyValue("ActiveConnection",0,con)};
XComponent formdocument = loader.loadComponentFromURL("Form1","",0,args);
}
}
}
The returned object is a com.sun.star.text.TextDocument service. For forms, see 14 Forms
|
|
The document definition object is the owner of the accessed com.sun.star.text.TextDocument. When the document definition is released (last reference gone), the text document is also closed. |
The returned form or report documents are com.sun.star.sdb.DocumentDefinition services. These are the properties of the com.sun.star.sdb.DocumentDefinition service.
|
Properties of com.sun.star.sdb.DocumentDefinition | |
|
string ─ Defines the name of the document. | |
|
boolean ─ Indicates if the document is to be used as template, for example, if a report is to be filled with data. | |
In addition to these properties, the com.sun.star.sdb.DocumentDefinition service offers a com.sun.star.sdbcx.XRename to rename a DocumentDefinition.
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.
|
|
This feature is highly deprecated and should not be used anymore. Since OpenOffice.org 2.0, documents are stored within a database file, and not only linked from a data source. |
The links are available at the com.sun.star.sdb.XBookmarksSupplier interface of a data source that has one method:
com::sun::star::container::XNameAccess getBookmarks()
The returned service is a com.sun.star.sdb.DefinitionContainer. The DefinitionContainer is not only an XNameAccess, but a com.sun.star.container.XNameContainer, that is, new links are added using insertByName() as described in the chapter 2 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 are stored at file:/// URLs. The following example adds a new document to the data source Bibliography:
public static void addDocumentLink(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
XNameAccess.class,_rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// we use the predefined Bibliography data source
XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName("Bibliography"));
// we need the XBookmarksSupplier interface of the data source
XBookmarksSupplier xBookmarksSupplier = ( XBookmarksSupplier )UnoRuntime.queryInterface(
XBookmarksSupplier.class, xDS);
// get container with bookmark URLs
XNameAccess xBookmarks = xBookmarksSupplier.getBookmarks();
XNameContainer xBookmarksContainer = (XNameContainer)UnoRuntime.queryInterface(
XNameContainer.class, xBookmarks);
// insert new link
xBookmarksContainer.insertByName("MyLink", "file:///home/ada01/Form_Ada01_DSADMIN.Table1.odt");
}
To load a linked document, use the bookmark URL with the method loadComponentFromUrl() at the com.sun.star.frame.XComponentLoader interface of the com.sun.star.frame.Desktop singleton that is available at the global service manager. For details about the Desktop, see 7 Office Development.
A com.sun.star.sdb.Table encapsulates tables in a OpenOffice.org API data source. The com.sun.star.sdb.Table 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 com.sun.star.sdb.Table service. This is discussed in the section 13.4 Database Access - Database Design below.
The table related services in the database context are unable to access the data in a database table. Use the com.sun.star.sdb.RowSet service, or to establish a connection to a database and use its com.sun.star.sdb.XCommandPreparation interface to manipulate table data. For details, see the sections 13.3.1 Database Access - Manipulating Data - The RowSet Service and 13.3.6 Database Access - Manipulating Data - 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.
 Illustration 13.6: Connection and Tables |
The com.sun.star.sdbcx.XTablesSupplier interface of a Connection supplies a com.sun.star.sdbcx.Container of com.sun.star.sdb.Table services through its method getTables(). 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:
|
Properties of com.sun.star.sdb.DataSettings | |
|
string ─ An additional filter for the data object, WHERE clause syntax. | |
|
boolean ─ Indicates if the filter should be applied. The default is FALSE. | |
|
string ─ Is an additional sort order definition. | |
|
Struct com.su | |
