LibreOffice Developer's Guide: Chapter 12 - LibreOffice 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:
- 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 The RowSet Service.
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 = UnoRuntime.queryInterface(XPropertySet.class, xRowSet);
// the DataSourceName can be a data source registered with [PRODUCTNAME], among other possibilities
xProp.setPropertyValue("DataSourceName", "Bibliography");
// the CommandType must be TABLE, QUERY or COMMAND - here we use COMMAND
xProp.setPropertyValue("CommandType", Integer.valueOf(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 = UnoRuntime.queryInterface(
com.sun.star.sdbc.XRow.class, xRowSet);
// XColumnLocate finds columns by name
com.sun.star.sdbc.XColumnLocate xLoc = 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 = UnoRuntime.queryInterface(
com.sun.star.lang.XComponent.class, xRowSet);
xComp.dispose();
}Data Sources in LibreOffice
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 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 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 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 LibreOffice GUI.
Since OpenOffice.org 2.0.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:
// 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]);
}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 com.sun.star.sdb.DataSource service includes all the features of a database defined in LibreOffice API. DataSource provides the following properties for its knowledge about how to connect to a database and which tables to display:
| Name | [readonly] string - The name of the data source.
|
| URL | string - Indicates a database URL. Valid URL formats are:
|
| Info | sequence< com.sun.star.beans.PropertyValue >. A list of arbitrary string tag or value pairs as connection arguments.
|
| User | string - The login name of the current user.
|
| Password | string - The password of the current user. It is not stored with the data source.
|
| IsPasswordRequired | boolean - Indicates that a password is always necessary and might be interactively requested from the user by an interaction handler.
|
| IsReadOnly | [readonly] boolean - Determines if database contents may be modified.
|
| NumberFormatsSupplier | [readonly] XNumberFormatsSupplier. Provides an object for number formatting.
|
| TableFilter | sequence< string >. A list of tables the data source should display. If empty, all tables are hidden. Valid placeholders are % and ?.
|
| TableTypeFilter | 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.
|
| SuppressVersionColumns | 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, 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 com.sun.star.document.OfficeDocument service supports.
Adding and Editing Datasources
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.
// 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 = 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 = UnoRuntime.queryInterface(XNamingService.class, xFac);
XStorable store = UnoRuntime.queryInterface(XStorable.class, xDs);
XModel model = 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 = UnoRuntime.queryInterface(XPropertySet.class, xDs);
// Adabas D URL
xDsProps.setPropertyValue("URL", "sdbc:adabas::MYDB1");
// force password dialog
xDsProps.setPropertyValue("IsPasswordRequired", Boolean.TRUE);
// suggest dsadmin as user name
xDsProps.setPropertyValue("User", "dsadmin");
store.store();
}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 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.
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 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 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 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 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 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.
 |
Note:
|
A QueryDefinition is configured through the following properties:
| Name | string - The name of the queryDefinition.
|
| Command | string - The SQL SELECT command.
|
| EscapeProcessing | boolean - If true, determines that the query must not be touched by the built-in SQL parser of LibreOffice API.
|
| UpdateCatalogName | string - The name of the update table catalog used to identify tables, supported by some databases.
|
| UpdateSchemaName | string - The name of the update table schema used to identify tables, supported by some databases.
|
| UpdateTableName | 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 LibreOffice API.
// 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 = 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 = UnoRuntime.queryInterface(
XSingleServiceFactory.class, xQDefs);
// order a new query and get its com.sun.star.beans.XPropertySet interface
XPropertySet xProp = UnoRuntime.queryInterface(
XPropertySet.class, xSingleFac.createInstance());
// configure the query
xProp.setPropertyValue("Command","SELECT * FROM biblio");
xProp.setPropertyValue("EscapeProcessing", Boolean.TRUE);
// insert it into the query definitions container
XNameContainer xCont = 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 = UnoRuntime.queryInterface(XStorable.class, xQuerySup);
store.store();
}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 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 LibreOffice API GUI or together with a com.sun.star.sdb.RowSet.
| Filter | string - An additional filter for the data object, WHERE clause syntax.
|
| ApplyFilter | boolean - Indicates if the filter should be applied, default is FALSE.
|
| Order | string - Is an additional sort order definition.
|
| FontDescriptor | struct com.sun.star.awt.FontDescriptor. Specifies the font attributes for displayed data.
|
| RowHeight | long - Specifies the height of a data row.
|
| TextColor | 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 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 Tables and Columns.
The following code sample connects to Bibliography, and prints the column names and types of the previously defined query Query1.
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 = 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 = UnoRuntime.queryInterface(
XQueriesSupplier.class, con);
// get container with com.sun.star.sdb.Query services
XNameAccess xQDefs = xQuerySup.getQueries();
// retrieve XColumnsSupplier of Query1
XColumnsSupplier xColsSup = 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 = UnoRuntime.queryInterface(
XPropertySet.class, col);
System.out.println(aNames[i] + " " + xColumnProps.getPropertyValue("TypeName"));
}
}The SingleSelectQueryComposer
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 = UnoRuntime.queryInterface(XDataSource.class,
xNameAccess.getByName( "Bibliography" ));
log.println("check XMultiServiceFactory");
XMultiServiceFactory xConn = 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 SQLQueryComposer
The service XSQLQueryComposerFactory 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.
|
Note:
|
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 = UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName("Bibliography"));
XConnection con = xDS.getConnection("", "");
XQueriesSupplier xQuerySup = UnoRuntime.queryInterface(
XQueriesSupplier.class, con);
XNameAccess xQDefs = xQuerySup.getQueries();
XPropertySet xQuery = UnoRuntime.queryInterface(
XPropertySet.class,xQDefs.getByName("Query1"));
String sCommand = (String)xQuery.getPropertyValue("Command");
XSQLQueryComposerFactory xQueryFac = 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()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 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 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 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 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.
The following are the allowed properties for the document definition:
| PropertyValue | Name: Name
Value: |
| PropertyValue | Name: URL
Value: |
| PropertyValue | Name: ActiveConnection
Value: com.sun.star.sdbc.XConnection - The connection to be used by the document. |
| PropertyValue | 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:
| PropertyValue | Name: Name
Value: |
| PropertyValue | 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 subfolder inside the form’s or report’s 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:
- 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)
- PropertyValue
// 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 = UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName( "Bibliography" ));
XConnection con = xDS.getConnection("","");
XFormsSupplier xSup = UnoRuntime.queryInterface(XFormsSupplier.class, xDS);
XNameAccess xForms = xSup.getFormDocuments();
if ( xForms.hasByName("Form1") ) {
Object form = xForms.getByName("Form1"); // to hold ref
{
XComponentLoader loader = 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 Forms
|
Note:
|
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.
| Name | string - Defines the name of the document.
|
| AsTemplate | 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.
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.
|
Note:
|
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 a XNameAccess, but a com.sun.star.container.XNameContainer, that is, new links are added using insertByName() 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:
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 = 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 = 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 Office Development.
Tables and Columns
A com.sun.star.sdb.Table encapsulates tables in a LibreOffice 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 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 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 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:
| Filter | string - An additional filter for the data object, WHERE clause syntax.
|
| ApplyFilter | boolean - Indicates if the filter should be applied. The default is FALSE.
|
| Order | string - Is an additional sort order definition.
|
| FontDescriptor | struct com.sun.star.awt.FontDescriptor. Specifies the font attributes for displayed data.
|
| RowHeight | long - Specifies the height of a data row.
|
| TextColor | long - Specifies the text color for displayed text in 0xAARRGGBB notation
|
Basic table information is included in the properties included with com.sun.star.sdbcx.Table:
| Name | [readonly] string - Table name.
|
| CatalogName | [readonly] string - Catalog name.
|
| SchemaName | [readonly] string - Schema name.
|
| Description | [readonly] string - Table Description, if supported by the driver.
|
| Type | [readonly] string - Table type, possible values are TABLE, VIEW, SYSTEM TABLE or an empty string if the driver does not support different table types.
|
The service com.sun.star.sdb.Table is an extension of the service com.sun.star.sdbcx.Table. It introduces an additional property called Privileges. The Privileges property indicates the actions the current user may carry out on the table.
| Privileges | [readonly] long, constants group com.sun.star.sdbcx.Privilege. The property contains a bitwise AND combination of the following privileges:
|
The appearance of single columns in a table can be changed. The following illustration depicts the service com.sun.star.sdb.Column and its relationship with the com.sun.star.sdb.Table service.
For this purpose, com.sun.star.sdb.Table supports the interface com.sun.star.sdbcx.XColumnsSupplier. Its method getColumns() returns a com.sun.star.sdbcx.Container with the additional column-related interface com.sun.star.sdbc.XColumnLocate that is useful to get the column number for a certain column in a table:
long findColumn( [in] string columnName)The service com.sun.star.sdb.Column 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.
| FormatKey | long - Contains the index of the number format that is used for the column. The proper value can be determined using the com.sun.star.util.XNumberFormatter interface. If the value is void, a default number format is used according to the data type of the column.
|
| Align | long - Specifies the alignment of column text. Possible values are:
0: left If the value is void, a default alignment is used according to the data type of the column. |
| Width | long - Specifies the width of the column displayed in a grid. The unit is 10th mm. If the value is void, a default width should be used according to the label of the column.
|
| Position | long - The ordinal position of the column within a grid. If the value is void, the default position should be used according to their order of appearance in com.sun.star.sdbc.XResultSetMetaData.
|
| Hidden | boolean - Determines if the column should be displayed.
|
| ControlModel | com.sun.star.beans.XPropertySet. May contain a control model that defines the settings for layout. The default is NULL. |
| HelpText | string - Describes an optional help text that can be used by UI components when representing this column.
|
| ControlDefault | string - Contains the default value that should be displayed by a control when moving to a new row.
|
The Properties of com.sun.star.sdbcx.Column are readonly and can be used for information purposes:
| Name | [readonly] string - The name of the column.
|
| Type | [readonly] long - The com.sun.star.sdbc.DataType of the column.
|
| TypeName | [readonly] string - The type name used by the database. If the column type is a user-defined type, then a fully-qualified type name is returned. May be empty.
|
| Precision | [readonly] long - The number of decimal digits or chars.
|
| Scale | [readonly] long - Number of digits after the decimal point.
|
| IsNullable | [readonly] long, constants group com.sun.star.sdbc.ColumnValue. Indicates if values may be NULL in the designated column. Possible values are:
NULLABLE: column allows NULL values. |
| IsAutoIncrement | [readonly] boolean - Indicates if the column is automatically numbered.
|
| IsCurrency | [readonly] boolean - Indicates if the column is a cash value.
|
| IsRowVersion | [readonly] boolean - Indicates whether the column contains a type of time or date stamp used to track updates.
|
| Description | [readonly] string - Keeps a description of the object.
|
| DefaultValue | [readonly] string - Keeps a default value for a column, and is provided as a string.
|
Connections
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 com.sun.star.sdb.Connection has three main functions: communication, data definition and operation on the LibreOffice API application level. The service:
- 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 com.sun.star.sdbcx.DatabaseDefinition. 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 com.sun.star.sdb.XQueriesSupplier and com.sun.star.sdb.XSQLQueryComposerFactory. 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.
Connections are central to all database activities. The connection interfaces are discussed later.
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:
// general connection control
void close()
boolean isClosed()
void setReadOnly( [in] boolean readOnly)
boolean isReadOnly()
// commands and statements
// - generic SQL statement
// - prepared statement
// - stored procedure call
com::sun::star::sdbc::XStatement createStatement()
com::sun::star::sdbc::XPreparedStatement prepareStatement( [in] string sql)
com::sun::star::sdbc::XPreparedStatement prepareCall( [in] string sql)
string nativeSQL( [in] string sql)
// transactions
void setTransactionIsolation( [in] long level)
long getTransactionIsolation()
void setAutoCommit( [in] boolean autoCommit)
boolean getAutoCommit()
void commit()
void rollback()
// database metadata
com::sun::star::sdbc::XDatabaseMetaData getMetaData()
// data type mapping (driver dependent)
com::sun::star::container::XNameAccess getTypeMap()
void setTypeMap( [in] com::sun::star::container::XNameAccess typeMap)
// catalog (subspace in a database)
void setCatalog( [in] string catalog)
string getCatalog()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:
any getWarnings()
void clearWarnings()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.
com::sun::star::sdbc::XPreparedStatement prepareCommand( [in] string command, [in] long commandType)If the value of the parameter com.sun.star.sdb.CommandType is TABLE or QUERY, pass a table name or query name that exists in the com.sun.star.sdb.DataSource of the connection. The value COMMAND makes prepareCommand() 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 com.sun.star.container.XChild accesses the parent com.sun.star.sdb.DataSource of the connection, if available.
com::sun::star::uno::XInterface getParent()
void setParent( [in] com::sun::star::uno::XInterface Parent)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 com.sun.star.sdbc.XDataSource interface to connect. It consists of:
// establish connection
com::sun::star::sdbc::XConnection getConnection(
[in] string user, [in] string password)
// timeout for connection failure
void setLoginTimeout( [in] long seconds)
long getLoginTimeout()If a database does not support logins, pass empty strings to getConnection(). For instance, use getConnection() against dBase data sources like Bibliography:
XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
XNameAccess.class, _rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
// we use the Bibliography data source
XDataSource xDS = UnoRuntime.queryInterface(
XDataSource.class, xNameAccess.getByName("Bibliography"));
// simple way to connect
XConnection xConnection = xDS.getConnection("", "");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:
com::sun::star::sdbc::XConnection connectWithCompletion(
[in] com::sun::star::task::XInteractionHandler handler)When you call connectWithCompletion(), LibreOffice API shows the common login dialog to the user if the data source property IsPasswordRequired is true. The login dialog is part of the com.sun.star.sdb.InteractionHandler provided by the global service factory.
// logs into a database and returns a connection
// expects a reference to the global service manager
com.sun.star.sdbc.XConnection logon(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"));
// get an Adabas D data source Ada01 generated in the GUI
Object dataSource = xNameAccess.getByName("Ada01");
// create a com.sun.star.sdb.InteractionHandler and get its XInteractionHandler interface
Object interactionHandler = _rMSF.createInstance("com.sun.star.sdb.InteractionHandler");
XInteractionHandler xInteractionHandler = UnoRuntime.queryInterface(
XInteractionHandler.class, interactionHandler);
// query for the XCompletedConnection interface of the data source
XCompletedConnection xCompletedConnection = UnoRuntime.queryInterface(
XCompletedConnection.class, dataSource);
// connect with interactive login
XConnection xConnection = xCompletedConnection.connectWithCompletion(xInteractionHandler);
return XConnection;
}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:
// establish connection
com::sun::star::sdbc::XConnection getConnection( [in] string url)
com::sun::star::sdbc::XConnection getConnectionWithInfo( [in] string url,
[in] sequence < com::sun::star::beans::PropertyValue > info)
// timeout for connection failure
void setLoginTimeout( [in] long seconds)
long getLoginTimeout()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:
Connection xConnection = DriverManager.getConnection(url);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 getConnectionWithInfo() to provide this information. The method getConnectionWithInfo() 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.
// create the DriverManager
Object driverManager = xMultiServiceFactory.createInstance("com.sun.star.sdbc.DriverManager");
// query for the interface XDriverManager
com.sun.star.sdbc.XDriverManager xDriverManager;
xDriverManager = UnoRuntime.queryInterface(
XDriverManager.class, driverManager);
if (xDriverManager != null) {
// first create the database URL
String adabasURL = "sdbc:adabas::MYDB0";
// create the necessary sequence of PropertyValue structs for user and password
com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[] {
new com.sun.star.beans.PropertyValue("user", 0, "Scott",
com.sun.star.beans.PropertyState.DIRECT_VALUE),
new com.sun.star.beans.PropertyValue("password", 0, "huutsch",
com.sun.star.beans.PropertyState.DIRECT_VALUE)
};
// now create a connection to Adabas
XConnection xConnection = xDriverManager.getConnectionWithInfo(adabasURL, adabasProps);
if (adabasConnection != null) {
System.out.println("Connection was created!");
// now we dispose the connection to close it
XComponent xComponent = (XComponent)UnoRuntime.queryInterface(
XComponent.class, xConnection );
if (xComponent != null) {
// connection must be disposed to avoid memory leaks
xComponent.dispose();
System.out.println("Connection disposed!");
}
} else {
System.out.println("Connection could not be created!");
}
}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:
// create the Driver using the implementation name
Object aDriver = xMultiServiceFactory.createInstance("com.sun.star.comp.sdbcx.adabas.ODriver");
// query for the XDriver interface
com.sun.star.sdbc.XDriver xDriver;
xDriver = UnoRuntime.queryInterface(XDriver.class, aDriver);
if (xDriver != null) {
// first create the needed url
String adabasURL = "sdbc:adabas::MYDB0";
// second create the necessary properties
com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[] {
new com.sun.star.beans.PropertyValue("user", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE),
new com.sun.star.beans.PropertyValue("password", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE)
};
// now create a connection to adabas
XConnection adabasConnection = xDriver.connect(adabasURL,adabasProps);
if (xConnection != null) {
System.out.println("Connection was created!");
// now we dispose the connection to close it
XComponent xComponent = (XComponent)UnoRuntime.queryInterface(XComponent.class, xConnection);
if (xComponent != null) {
xComponent.dispose();
System.out.println("Connection disposed!");
}
} else {
System.out.println("Connection could not be created!");
}
}Driver Specifics
Currently, there are nine driver implementations. Some support only the simple com.sun.star.sdbc.Driver 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.
| Driver | URL | Solaris | Linux | Windows |
|---|---|---|---|---|
| JDBC | jdbc:subprotocol: | |||
| ODBC 3.5 | sdbc:odbc:datasource name | |||
| Adabas D | sdbc:adabas:database name | |||
| ADO | sdbc:ado:ADO specific | |||
| dBase | sdbc:dbase:Location of folder or file | |||
| Flat file format (csv) | sdbc:flat:Location of folder or file | |||
| LibreOffice Calc | sdbc:calc:Location of LibreOffice Calc file | |||
| Mozilla addressbook (Mozilla, Outlook, Outlook Express and LDAP) | sdbc:address:Kind of addressbook | |||
| Embedded HSQLDB | sdbc:embedded:hsqldb |
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 JavaDriverClass 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.
// first create the needed url
String url = "jdbc:mysql://localhost:3306/TestTables";
// second create the necessary properties
com.sun.star.beans.PropertyValue [] props = new com.sun.star.beans.PropertyValue[] {
new [ com.sun.star.beans.PropertyValue]("user", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE),
new com.sun.star.beans.PropertyValue("password", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE),
new com.sun.star.beans.PropertyValue("JavaDriverClass", 0, "org.gjt.mm.mysql.Driver",
com.sun.star.beans.PropertyState.DIRECT_VALUE)
};
// now create a connection to adabas
xConnection = xDriverManager.getConnectionWithInfo(url, props);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 com.sun.star.sdbc.ODBCConnectionProperties. These properties are set while creating a connection:
| Silent | boolean - If True, the ODBC driver will not be asked for completion. This may happen if the username and password are already known. Otherwise False.
|
| Timeout | int - A value corresponding to the number of seconds to wait for any request on the connection to complete before returning to the application.
|
| UseCatalog | boolean - If False, the SDBC driver should not use catalogs. Otherwise True.
|
| SystemDriverSettings | string - Settings that are submitted to the ODBC driver directly.
|
| Charset | string - Converts data from the ODBC driver into the corresponding text encoding. The value must be a value of the list from www.iana.org/assignments/character-sets. Only a few character sets are supported
|
| ParameterNameSubstitution | boolean - If True, all occurrences of "?" as a parameter name will be replaced by a valid parameter name. This is for some drivers that mix the order of the parameters.
|
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 com.sun.star.sdbcx.Driver. 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:<ADO specific connection string>
Possible connection strings are:
sdbc:ado:PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\northwind.mdbsdbc:ado:Provider=msdaora;data source=testdb
The SDBC Driver for dBase
The dBase driver is one of the basic driver implementations and supports the service com.sun.star.sdbcx.Driver. 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:<folder or file url>
For instance:
sdbc:dbase:file:///d:/user/database/biblio
Similar to the SDBC driver for ODBC, this driver supports the connection info property CharSet to set different text encodings. The second possible property is ShowDeleted. 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.
| Object | create | alter |
|---|---|---|
| table | ||
| column | ||
| key | ||
| index | ||
| group | ||
| user |
The driver has the following conditions in its support for SQL statements:
- The
SELECTstatement can not contain more than one table in theFROMclause. - For comparisons the following operators are valid:
=,<,>,<>,>=,<=,LIKE,NOT LIKE,IS NULL,IS NOT NULL. - Parameters are allowed, but must be denoted with a leading colon (
SELECT * FROM biblio WHERE Author LIKE :MyParam) or with a single question mark (SELECT * FROM biblio WHERE Author LIKE ?). - The driver provides a
ResultSetthat 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:<folder or file url >
For instance:
sdbc:flat:file:///d:/user/database/textbase1
Properties that can be set while creating a new connection.
| Extension | string - Flat file formats are formats such as:
|
| CharSet | string - Converts data from the ODBC driver into the corresponding text encoding. The value must be a value of the list from www.iana.org/assignments/character-sets. Only some are supported, but a new one can be added.
|
| FixedLength | boolean - If true, all occurrences of "?" as a parameter name will be replaced by a valid parameter name. This is necessary, because some drivers mix the order of the parameters.
|
| HeaderLine | boolean - If true, the first line is used for column generation.
|
| FieldDelimiter | string - Defines a character which should be used to separate fields and columns.
|
| StringDelimiter | string - Character to identify strings.
|
| DecimalDelimiter | string - Character to identify decimal values.
|
| ThousandDelimiter | string - Character to identify the thousand separator. Must be different from DecimalDelimiter.
|
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:<file url to a LibreOffice Calc file or any other extension known by this application>
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.
| Addressbook | Windows | Unix | URL |
|---|---|---|---|
| Mozilla | sdbc:address:mozilla | ||
| LDAP | sdbc:address:ldap | ||
| Outlook Express | sdbc:address:outlookexp | ||
| Outlook | sdbc:address:outlook |
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 ConnectionPool. 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 com.sun.star.sdbc.XPooledConnection 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 close() 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 com.sun.star.sdbc.ConnectionPool service calling getConnectionWithInfo() at its interface com.sun.star.sdbc.XDriverManager 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 ConnectionPool object:
- The client obtains an instance of the com.sun.star.sdbc.ConnectionPool from the global service manager and calls the same methods on the
ConnectionPoolobject as on theDriverManager. - The application server providing the
ConnectionPoolimplementation checks its connection pool for a suitablePooledConnectionobject, a physical database connection, that is available. Determining the suitability of a givenPooledConnectionobject 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. - If there are no suitable
PooledConnectionobjects available, the application server creates a new physical connection and returns thePooledConnection. TheConnectionPoolis not driver specific. It is implemented in a service called com.sun.star.sdbc.ConnectionPool. - Regardless if the
PooledConnectionhas been retrieved from the pool or created, the application server does internal recording to indicate that the physical connection is now in use. - The application server calls the method
PooledConnection.getConnection()to get a logicalConnectionobject. This logicalConnectionobject is a handle to a physicalPooledConnectionobject. This handle is returned by theXDriverManagermethodgetConnectionWithInfo()when connection pooling is in effect. - The logical
Connectionobject is returned to the SDBC client that uses the same Connection API as in the standard situation without aConnectionPool. Note that the underlying physical connection cannot be reused until the client calls theXConnectionmethodclose().
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 DriverManager.
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 ActiveConnection that returns a connection.
|
Note:
|
Manipulating Data
There are two possibilities to manipulate data in a database with the LibreOffice database connectivity.
- Use the com.sun.star.sdb.RowSet 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 com.sun.star.sdb.RowSet 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 DataSource, the URL of a data access component, or a previously instantiated connection. Depending on the property ResultSetConcurrency, 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 execute() on the RowSet, move through the result set, or update and delete rows.
Usage
To use a row set, create a RowSet instance at the global service manager through the service name com.sun.star.sdb.RowSet. Next, the RowSet needs a connection and a command before it can be executed. These have to be configured through RowSet properties.
Connection
- There are three different ways to establish a connection:
- Setting DataSourceName to a data source from the database context. If the
DataSourceNameis not a URL, then theRowSetuses the name to get theDataSourcefrom theDatabaseContextto create a connection to that data source. - Setting 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 ActiveConnection makes a row set ready for immediate use. The row set uses this connection.
- Setting DataSourceName to a data source from the database context. If the
- The difference between the two properties is that in the first case the
RowSetowns the connection. TheRowSetdisposes the connection when it is disposed. In the second case, theRowSetonly uses the connection. The user of aRowSetis responsible for the disposition of the connection. For a simpleRowSet, use DataSourceName, but when sharing the connection between different row sets, then use ActiveConnection.
- If there is already a connection, for example, the user opened a database form, open another row set based upon the property ActiveConnection of the form. Put the
ActiveConnectionof the form into theActiveConnectionproperty of the new row set.
Command
- With a connection and a command, the row set is ready to be executed calling
execute()on the com.sun.star.sdbc.XRowSet interface of the row set. For interactive logon, useexecuteWithCompletion(), see Connecting Through a DataSource. If interactive logon is not feasible for your application, the propertiesUserandPasswordcan be used to connect to a database that requires logon.
- Once the method for how
RowSetcreates it connections has been determined, the properties Command and CommandType have to be set. TheCommandTypecan beTABLE,QUERYorCOMMANDwhere theCommandcan be a table or query name, or an SQL command.
The following table shows the properties supported by com.sun.star.sdb.RowSet.
| ActiveConnection | com.sun.star.sdbc.XConnection. The active connection is generated by a DataSource or by a URL. It could also be set from the outside. If set from outside, the RowSet is not responsible for disposition of the connection.
|
| DataSourceName | string - The name of the DataSource to use. This could be a named DataSource or the URL of a data access component.
|
| Command | string - The Command is the command that should be executed. The type of command depends on the com.sun.star.sdb.CommandType.
|
| CommandType | com.sun.star.sdb.CommandType Command type:
|
| ActiveCommand | [readonly] string - The command which is currently used. com.sun.star.sdb.CommandType
|
| IgnoreResult | boolean - Indicates if all results should be discarded.
|
| Filter | string - Contains a additional filter for a RowSet.
|
| ApplyFilter | boolean - Indicates if the filter should be applied. The default is false.
|
| Order | An additional sort order definition for a RowSet.
|
| Privileges | [readonly] long, constants group com.sun.star.sdbcx.Privilege. Indicates the privileges for insert, update, and delete.
|
| IsModified | [readonly] boolean - Indicates if the current row is modified.
|
| IsNew | [readonly] boolean - Indicates if the current row is the InsertRow and can be inserted into the database.
|
| RowCount | [readonly] long - Contains the number of rows accessed in a the data source.
|
| IsRowCountFinal | [readonly] boolean - Indicates if all rows of the RowSet have been counted.
|
| UpdateTableName | string - The name of the table that should be updated. This is used for queries that relate to more than one table.
|
| UpdateSchemaName | string - The name of the table schema.
|
| UpdateCatalogName | string - The name of the table catalog.
|
The com.sun.star.sdb.RowSet includes the service com.sun.star.sdbc.RowSet and its properties. Important settings such as User and Password come from this service:
| DataSourceName | string - Is the name of a named datasource to use.
|
| URL | string - The connection URL. Can be used instead of the DataSourceName.
|
| Command | string - The command that should be executed.
|
| TransactionIsolation | long - Indicates the transaction isolation level that should be used for the connection, according to com.sun.star.sdbc.TransactionIsolation
|
| TypeMap | com::sun::star::container::XNameAccess. The type map that is used for the custom mapping of SQL structured types and distinct types.
|
| EscapeProcessing | boolean - Determines if escape processing is on or off. If escape scanning is on (the default), the driver does the escape substitution before sending the SQL to the database. This is only evaluated if the CommandType is COMMAND.
|
| QueryTimeOut | long - Retrieves the number of seconds the driver waits for a Statement to execute. If the limit is exceeded, a SQLException is thrown. There is no limitation if set to zero.
|
| MaxFieldSize | long - Returns the maximum number of bytes allowed for any column value. This limit is the maximum number of bytes that can be returned for any column value. The limit applies only to DataType::BINARY<ode>, |
| MaxRows | long - Retrieves the maximum number of rows that a ResultSet can contain. If the limit is exceeded, the excess rows are silently dropped. There is no limitation if set to zero.
|
| User | string - Determines the user to open the connection for.
|
| Password | string - Determines the user to open the connection for.
|
| ResultSetType | long - Determine the result set type according to com.sun.star.sdbc.ResultSetType
|
If the command returns results, that is, it selects data, use XRowSet to manipulate the data, because XRowSet is derived from XResultSet. For details on manipulating a com.sun.star.sdb.ResultSet, see Result Sets.
The code fragment below shows how to create a RowSet.
public static void useRowSet(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// first we create our RowSet object
XRowSet xRowRes = UnoRuntime.queryInterface(XRowSet.class,
_rMSF.createInstance("com.sun.star.sdb.RowSet"));
System.out.println("RowSet created!");
// set the properties needed to connect to a database
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
xProp.setPropertyValue("DataSourceName", "Bibliography");
xProp.setPropertyValue("Command", "biblio");
xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.TABLE));
xRowRes.execute();
System.out.println("RowSet executed!");
XComponent xComp = UnoRuntime.queryInterface(XComponent.class, xRowRes);
xComp.dispose();
System.out.println("RowSet destroyed!");
}The value of the read-only RowSet properties is only valid after the first call to execute() on the RowSet. This snippet shows how to read the privileges out of the RowSet:
public static void showRowSetReadOnlyProps(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// first we create our RowSet object
XRowSet xRowRes =
UnoRuntime.queryInterface(XRowSet.class_rMSF.createInstance(
"com.sun.star.sdb.RowSet"));
System.out.println("RowSet created!");
// set the properties needed to connect to a database
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
xProp.setPropertyValue("DataSourceName", "Bibliography");
xProp.setPropertyValue("Command", "biblio");
xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.TABLE));
xRowRes.execute();
System.out.println("RowSet executed!");
Integer aPriv = (Integer)xProp.getPropertyValue("Privileges");
int nPriv = aPriv.intValue();
if ((nPriv & Privilege.SELECT) == Privilege.SELECT) System.out.println("SELECT");
if ((nPriv & Privilege.INSERT) == Privilege.INSERT) System.out.println("INSERT");
if ((nPriv & Privilege.UPDATE) == Privilege.UPDATE) System.out.println("UPDATE");
if ((nPriv & Privilege.DELETE) == Privilege.DELETE) System.out.println("DELETE");
XComponent xComp = UnoRuntime.queryInterface(XComponent.class, xRowRes);
xComp.dispose();
System.out.println("RowSet destroyed!");
}The next example reads the properties IsRowCountFinal and RowCount.
public static void showRowSetRowCount(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// first we create our RowSet object
XRowSet xRowRes = UnoRuntime.queryInterface(XRowSet.class,
_rMSF.createInstance("com.sun.star.sdb.RowSet"));
System.out.println("RowSet created!");
// set the properties needed to connect to a database
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,xRowRes);
xProp.setPropertyValue("DataSourceName","Bibliography");
xProp.setPropertyValue("Command","biblio");
xProp.setPropertyValue("CommandType",Integer.valueOf(com.sun.star.sdb.CommandType.TABLE));
xRowRes.execute();
System.out.println("RowSet executed!");
// now look if the RowCount is already final
System.out.println("The RowCount is final: " + xProp.getPropertyValue("IsRowCountFinal"));
XResultSet xRes = UnoRuntime.queryInterface(XResultSet.class,xRowRes);
xRes.last();
System.out.println("The RowCount is final: " + xProp.getPropertyValue("IsRowCountFinal"));
System.out.println("There are " + xProp.getPropertyValue("RowCount") + " rows!");
// now destroy the RowSet
XComponent xComp = UnoRuntime.queryInterface(XComponent.class,xRowRes);
xComp.dispose();
System.out.println("RowSet destroyed!");
}Occasionally, it is useful for the user to be notified when the RowCount is final. That is accomplished by adding a com.sun.star.beans.XPropertyChangeListener for the property IsRowCountFinal.
Events and Other Notifications
The RowSet supports a number of events and notifications. First, there is the com.sun.star.sdb.XRowSetApproveBroadcaster interface of the RowSet that allows the user to add or remove objects derived from the interface com.sun.star.sdb.XRowSetApproveListener. The interface com.sun.star.sdb.XRowSetApproveListener defines the following methods:
| approveCursorMove() | Called before a RowSet's cursor is moved.
|
| approveRowChange() | Called before a row is inserted, updated, or deleted. |
| approveRowSetChange() | Called before a RowSet is changed or before a RowSet is re-executed.
|
All three methods return a boolean value that allows the RowSet to continue when it is true, otherwise the current action is stopped.
Additionally, the RowSet supports com.sun.star.sdbc.XRowSet that allows the user to add objects which are notified when the RowSet has changed. This has to be a com.sun.star.sdbc.XRowSetListener. The methods are:
| cursorMoved | Called when a RowSet's cursor has been moved.
|
| rowChanged | Called when a row has been inserted, updated, or deleted. |
| rowSetChanged | Called when the entire row set has changed, or when the row set has been re-executed. |
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 RowSet 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.
| Method Call | Event Call (before) | Event Call (after) |
|---|---|---|
beforeFirst()
|
approveCursorMove()
|
cursorMoved(), only when the movement was successful
|
updateRow()
|
approveRowChange()
|
rowChanged()
|
execute()
|
approveRowSetChange()
|
rowSetChanged()
|
Consider a simple class which implements the two listener interfaces described above.
import com.sun.star.sdb.XRowSetApproveListener;
import com.sun.star.sdbc.XRowSetListener;
import com.sun.star.sdb.RowChangeEvent;
import com.sun.star.lang.EventObject;
public class RowSetEventListener implements XRowSetApproveListener,XRowSetListener {
// XEventListener
public void disposing(com.sun.star.lang.EventObject event) {
System.out.println("RowSet will be destroyed!");
}
// XRowSetApproveBroadcaster
public boolean approveCursorMove(EventObject event) {
System.out.println("Before CursorMove!");
return true;
}
public boolean approveRowChange(RowChangeEvent event) {
System.out.println("Before row change!");
return true;
}
public boolean approveRowSetChange(EventObject event) {
System.out.println("Before RowSet change!");
return true;
}
// XRowSetListener
public void cursorMoved(com.sun.star.lang.EventObject event) {
System.out.println("Cursor moved!");
}
public void rowChanged(com.sun.star.lang.EventObject event) {
System.out.println("Row changed!");
}
public void rowSetChanged(com.sun.star.lang.EventObject event) {
System.out.println("RowSet changed!");
}
}The following method uses the listener implementation above.
public static void showRowSetEvents(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
// first we create our RowSet object
XRowSet xRowRes = UnoRuntime.queryInterface(
XRowSet.class, _rMSF.createInstance("com.sun.star.sdb.RowSet"));
System.out.println("RowSet created!");
// add our Listener
System.out.println("Append our Listener!");
RowSetEventListener pRow = new RowSetEventListener();
XRowSetApproveBroadcaster xApBroad = UnoRuntime.queryInterface(
XRowSetApproveBroadcaster.class, xRowRes);
xApBroad.addRowSetApproveListener(pRow);
xRowRes.addRowSetListener(pRow);
// set the properties needed to connect to a database
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,xRowRes);
xProp.setPropertyValue("DataSourceName", "Bibliography");
xProp.setPropertyValue("Command", "biblio");
xProp.setPropertyValue("CommandType", Integer.valueOf(com.sun.star.sdb.CommandType.TABLE));
xRowRes.execute();
System.out.println("RowSet executed!");
// do some movements to check if we got all notifications
XResultSet xRes = UnoRuntime.queryInterface(XResultSet.class, xRowRes);
System.out.println("beforeFirst");
xRes.beforeFirst();
// this should lead to no notifications because
// we should stand before the first row at the beginning
System.out.println("We stand before the first row: " + xRes.isBeforeFirst());
System.out.println("next");
xRes.next();
System.out.println("next");
xRes.next();
System.out.println("last");
xRes.last();
System.out.println("next");
xRes.next();
System.out.println("We stand after the last row: " + xRes.isAfterLast());
System.out.println("first");
xRes.first();
System.out.println("previous");
xRes.previous();
System.out.println("We stand before the first row: " + xRes.isBeforeFirst());
System.out.println("afterLast");
xRes.afterLast();
System.out.println("We stand after the last row: " + xRes.isAfterLast());
// now destroy the RowSet
XComponent xComp = UnoRuntime.queryInterface(XComponent.class, xRowRes);
xComp.dispose();
System.out.println("RowSet destroyed!");
}Clones of the RowSet Service
Occasionally, a second or third RowSet that operates on the same data as the original RowSet, 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 RowSet can be used to do this task.
The new clone is an object that supports the service com.sun.star.sdb.ResultSet if it was created using the interface com.sun.star.sdb.XResultSetAccess of the original RowSet. It is interoperable with the RowSet that created it, for example, bookmarks can be exchanged between both sets. If the original RowSet 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:
- Get a connection object.
- Ask the connection for a statement.
- The statement executes a query or an update command. Use the appropriate method to execute the command.
- 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 createStatement() at the com.sun.star.sdbc.XConnection interface of the connection. It returns a com.sun.star.sdbc.Statement service. This Statement 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 com.sun.star.sdbc.XStatement:
com::sun::star::sdbc::XResultSet executeQuery( [in] string sql)
long executeUpdate( [in] string sql)
boolean execute( [in] string sql)
com::sun::star::sdbc::XConnection getConnection()Once a Statement is obtained, choose the appropriate execution method for the SQL command. For a SELECT statement, use the method executeQuery(). For UPDATE, DELETE and INSERT statements, the proper method is executeUpdate(). To have multiple result sets returned, use execute() together with the interface com.sun.star.sdbc.XMultipleResults of the statement.
 |
Tip:
|
Consider how an XConnection is used to create an XStatement in the following example:
public static void executeSelect(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"));
// connect
Object dataSource = xNameAccess.getByName("Ada01");
XDataSource xDataSource = UnoRuntime.queryInterface(XDataSource.class, dataSource);
Object interactionHandler = _rMSF.createInstance("com.sun.star.sdb.InteractionHandler");
XInteractionHandler xInteractionHandler = UnoRuntime.queryInterface(
XInteractionHandler.class, interactionHandler);
XCompletedConnection xCompletedConnection = UnoRuntime.queryInterface(
XCompletedConnection.class, dataSource);
XConnection xConnection = xCompletedConnection.connectWithCompletion(xInteractionHandler);
// the connection creates a statement
XStatement xStatement = xConnection.createStatement();
// The XStatement interface is used to execute a SELECT command
// Double quotes for identifiers in the SELECT string must be escaped in Java
XResultSet xResult = xStatement.executeQuery("Select * from \"Table1\"");
// process the result ...
XRow xRow = UnoRuntime.queryInterface(XRow.class, xResult);
while (xResult != null && xResult.next()) {
System.out.println(xRow.getString(1));
}
}The remainder of this section discusses how to enter data into a table and retrieving the data later, using INSERT and SELECT 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 INSERT command to insert data into the table one row at a time:
INSERT INTO SALESMAN (
SNR,
FIRSTNAME,
LASTNAME,
STREET,
STATE,
ZIP,
BIRTHDATE
)
VALUES (
1,
'Joseph',
'Smith',
'Bond Street',
'CA',
'95460',
'1946-07-02'
) |
Note:
|
The following code sample inserts one row of data with the value 1 in the column SNR, 'Joseph' in FIRSTNAME, 'Smith' in LASTNAME, with other information in the following columns of the table SALESMAN. To issue the command against the database, create a Statement object and then execute it using the method executeUpdate():
XStatement xStatement = xConnection.createStatement();
xStatement.executeUpdate("INSERT INTO SALESMAN (" +
"SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +
"VALUES (1, 'Joseph', 'Smith','Bond Street','CA','95460','1946-07-02')");The next call to executeUpdate() inserts more rows into the table SALESMAN. Note the Statement object stmt is reused, rather than creating a new one for each update.
xStatement.executeUpdate("INSERT INTO SALESMAN (" +
"SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +
"VALUES (2, 'Frank', 'Jones', 'Lake Silver', 'CA', '95460', '1963-12-24')");
xStatement.executeUpdate("INSERT INTO SALESMAN (" +
"SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +
"VALUES (3, 'Jane', 'Esperanza', '23 Hollywood drive', 'CA', '95460', '1972-01-04')");
xStatement.executeUpdate("INSERT INTO SALESMAN (" +
"SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +
"VALUES (4, 'George', 'Flint', '12 Washington street', 'CA', '95460', '1953-02-13')");
xStatement.executeUpdate("INSERT INTO SALESMAN (" +
"SNR, FIRSTNAME, LASTNAME, STREET, STATE, ZIP, BIRTHDATE) " +
"VALUES (5, 'Bob', 'Meyers', '2 Moon way', 'CA', '95460', '1949-09-07')");Updating tables is basically the same process. The SQL command:
UPDATE SALESMAN
SET STREET='Grant Street', STATE='FL'
WHERE SNR=2writes a new street and state entry for Frank Jones who has SNR=2. The corresponding executeUpdate() call looks like this:
int n = xStatement.executeUpdate("UPDATE SALESMAN " +
"SET STREET='Grant Street', STATE='FL' " +
"WHERE SNR=2");The return value of executeUpdate() 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.
|
Note:
|
Getting Data from a Table
Now that the table SALESMAN has values in it, write a SELECT statement to access those values. The asterisk * in the following SQL statement indicates that all columns should be selected. Since there is no WHERE clause to select less rows, the following SQL statement selects the whole table:
SELECT * FROM SALESMANThe result contains the following data:
| SNR | FIRSTNAME | LASTNAME | STREET | STATE | ZIP | BIRTHDATE |
|---|---|---|---|---|---|---|
| 1 | Joseph | Smith | Bond Street | CA | 95460 | 02/07/46 |
| 2 | Frank | Jones | Lake silver | CA | 95460 | 12/24/63 |
| 3 | Jane | Esperanza | 23 Hollywood drive | CA | 95460 | 04/01/72 |
| 4 | George | Flint | 12 Washington street | CA | 95460 | 02/13/53 |
| 5 | Bob | Meyers | 2 Moon way | CA | 95460 | 09/07/49 |
The following is another example of a SELECT statement. This statement gets a list with the names and addresses of all the salespersons. Only the columns FIRSTNAME, LASTNAME and STREET were selected.
SELECT FIRSTNAME, LASTNAME, STREET FROM SALESMANThe result of this query only contains three columns:
| FIRSTNAME | LASTNAME | STREET |
|---|---|---|
| Joseph | Smith | Bond Street |
| Frank | Jones | Lake silver |
| Jane | Esperansa | 23 Hollywood drive |
| George | Flint | 12 Washington street |
| Bob | Meyers | 2 Moon way |
The SELECT statement above extracts all salespersons in the table. The following SQL statement limits the SALESMAN SELECT to salespersons who were born before 01/01/1950:
SELECT FIRSTNAME, LASTNAME, BIRTHDATE
FROM SALESMAN
WHERE BIRTHDATE < '1950-01-01'The resulting data is:
| FIRSTNAME | LASTNAME | BIRTHDATE |
|---|---|---|
| Joseph | Smith | 02/07/46 |
| Bob | Meyers | 09/07/49 |
When a database is accessed through the LibreOffice API database integration, the results are retrieved through ResultSet objects. The next section discusses how to use result sets. The following executeQuery() call executes the SQL command above. Note that the Statement is used again:
com.sun.star.sdbc.XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE " +
"FROM SALESMAN " +
"WHERE BIRTHDATE < '1950-01-01'");Result Sets
The ResultSet objects represent the output of an SQL SELECT 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 com.sun.star.sdbc.ResultSet 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 com.sun.star.sdb.ResultSet service extends the com.sun.star.sdbcx.ResultSet service by the additional interface com.sun.star.sdbcx.XColumnsSupplier that allows the user to access information about the appearance of the selected columns in the application. The interface XColumnsSupplier returns a com.sun.star.sdbcx.Container of ResultColumns.
The com.sun.star.sdb.ResultColumn service inherits the properties of the services com.sun.star.sdbcx.Column and com.sun.star.sdb.ColumnSettings.
The following table explains the properties introduced with com.sun.star.sdb.ResultColumn. For the inherited properties, refer to the section Tables and Columns.
| IsSearchable | boolean - Indicates if the column can be used in a "WHERE" clause.
|
| IsSigned | boolean - Indicates if values in the column are signed numbers.
|
| IsCaseSensitive | boolean - Indicates if a column is case sensitive.
|
| DisplaySize | long - Indicates the column's normal, maximum width in chars.
|
| Label | string - Gets the suggested column title for use with GUI controls and printouts.
|
| IsReadOnly | boolean - If True, cannot write to the column.
|
| IsWritable | boolean - If True, an attempt to write to the column may succeed.
|
| IsDefinitelyWritable | boolean - If True, the column is writable.
|
| ServiceName | string - Returns the fully-qualified name of the service that is returned when the com.sun.star.sdbc.XRow method getObject() is called to retrieve a value from the column.
|
| TableName | string - Gets the database table name where the column comes from.
|
| SchemaName | string - Gets the schema name of the column.
|
| CatalogName | string - Gets the catalog name of the column.
|
Retrieving Values from Result Sets
A call to execute() on a com.sun.star.sdb.RowSet or a call to executeQuery() on a Statement produces a com.sun.star.sdb.ResultSet.
com.sun.star.sdbc.XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, STREET " +
"FROM SALESMAN " +
"VWHERE BIRTHDATE < '1950-01-01'");Moving the Result Set Cursor
The ResultSet stored in the variable xResult contains the following data after the call above:
| FIRSTNAME | LASTNAME | BIRTHDATE |
|---|---|---|
| Joseph | Smith | 02/07/46 |
| Bob | Meyers | 09/07/49 |
To access the data, go to each row and retrieve the values according to their types. The method next() is used to move the row cursor from row to row. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to next() moves the cursor to the first row and makes it the current row. For the same reason, use the method next() to access the first row even if there is only one row in a result set. Subsequent invocations of next() move the cursor down one row at a time.
The interface com.sun.star.sdbc.XResultSet 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:
// move one row at a time
boolean next()
boolean previous()
// move a number of rows
boolean absolute( [in] long row )
boolean relative( [in] long rows )
// move to result set borders and beyond
boolean first()
boolean last()
void beforeFirst()
void afterLast()
//detect position
boolean isBeforeFirst()
boolean isAfterLast()
boolean isFirst()
boolean isLast()
long getRow()
// refetch row from the database
void refreshRow()
// row has been updated, inserted or deleted
boolean rowUpdated()
boolean rowInserted()
boolean rowDeleted()
// get the statement which created the result set
com::sun::star::uno::XInterface getStatement() |
Note:
|
Using the getXXX Methods
To get column values from the current row, use the interface com.sun.star.sdbc.XRow. 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 xResult is FIRSTNAME. It is the first column and contains a value of SQL type VARCHAR. The appropriate method to retrieve a VARCHAR value is getString(). It should be used for the second column, as well. The third column BIRTHDATE stores DATE values, the method for date types is getDate(). 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 xResult and prints a line with the column values separated by tabs. Each time next() is invoked, the next row becomes the current row, and the loop continues until there are no more rows in xResult.
public static void selectSalespersons(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"));
//connect
Object dataSource = xNameAccess.getByName("Ada01");
XDataSource xDataSource = UnoRuntime.queryInterface(XDataSource.class, dataSource);
Object interactionHandler = _rMSF.createInstance("com.sun.star.sdb.InteractionHandler");
XInteractionHandler xInteractionHandler = UnoRuntime.queryInterface(
XInteractionHandler.class, interactionHandler);
XCompletedConnection xCompletedConnection = UnoRuntime.queryInterface(
XCompletedConnection.class, dataSource);
XConnection xConnection = xCompletedConnection.connectWithCompletion(xInteractionHandler);
// create statement and execute query
XStatement xStatement = xConnection.createStatement();
XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME, BIRTHDATE FROM SALESMAN");
// process result
XRow xRow = UnoRuntime.queryInterface(XRow.class, xResult);
while (xResult != null && xResult.next()) {
String firstName = xRow.getString(1);
String lastName = xRow.getString(2);
com.sun.star.util.Date birthDate = xRow.getDate(3);
System.out.println(firstName + "\t" + lastName + "\t\t" +
birthDate.Month + "/" + birthDate.Day + "/" + birthDate.Year);
}
}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 getXXX methods work are shown and the two getXXX calls are examined.
String firstName = xRow.getString(1);The method getString() is invoked on xRow, that is, getString() gets the value stored in column no. 1 in the current row of xResult, which is FIRSTNAME. The value retrieved by getString() has been converted from a VARCHAR to a String in the Java programming language, and assigned to the String object firstname.
The situation is similar with the method getDate(). It retrieves the value stored in column no. 3 (BIRTHDATE), which is an SQL DATE, and converts it to a com.sun.star.util.Date before assigning it to the variable birthDate.
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 getXXX methods can be used to retrieve the various SQL types. For example, the method getInt() 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 VARCHAR, SDBC attempts to parse an integer out of the VARCHAR. To be sure that no information is lost, the method getInt() is only recommended for SQL INTEGER types, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE, TIME, or TIMESTAMP.
Although getString() is recommended for the SQL types CHAR and VARCHAR, 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 getString() can be useful, but has its limitations. For instance, if it is used to retrieve a numeric type, getString() 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 getString().
The illustration below shows all getXXX() 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
getXXX()method is the recommended method to retrieve an SDBC data type. No data will be lost due to type conversion. - x indicates that the
getXXX()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 com.sun.star.sdbc.XResultSet 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 ResultSet object. The following lines of code illustrate one way to create a scrollable ResultSet object:
XStatement xStatement = xConnection.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, xStatement);
xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME FROM SALES");This code is similar to what was used earlier, except that it sets two property values at the Statementobject. These properties have to be set before the statement is executed.
The value of the property ResultSetType must be one of three constants defined in com.sun.star.sdbc.ResultSetType: FORWARD_ONLY, SCROLL_INSENSITIVE and SCROLL_SENSITIVE.
The property ResultSetConcurrency must be one out of the two com.sun.star.sdbc.ResultSetConcurrency constants READ_ONLY and UPDATABLE. When a ResultSetType is specified, it must be specified if it is read-only or modifiable.
If any constants for the type and modifiability of a ResultSet object are not specified, FORWARD_ONLY and READ_ONLY will automatically be created.
Specifying the constant FORWARD_ONLY creates a non-scrollable result set, that is, the cursor moves forward only. A scrollable ResultSet is obtained by specifying SCROLL_INSENSITIVE or SCROLL_SENSITIVE. Sensitive or insensitive refers to changes made to the underlying data after the result set has been opened. A SCROLL_INSENSITIVE result set does not reflect changes to the underlying data, while a SCROLL_SENSITIVE result set shows changes. However, not all drivers and databases support change sensitivity.
In scrollable result sets, the counterpart to next() is the method previous(), 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 next() and previous() together with while:
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
XRow row = UnoRuntime.queryInterface(XRow.class, srs);
while (srs.next()) {
String name = row.getString(1);
float price = row.getFloat(2);
System.out.println(name + " " + price);
}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 afterLast(). Then previous() 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 previous() returns false.
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,stmt);
xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
XRow row = UnoRuntime.queryInterface(XRow.class, srs);
srs.afterLast();
while (srs.previous()) {
String name = row.getString(1);
float price = row.getFloat(2);
System.out.println(name + " " + price);
}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 first(), last(), beforeFirst(), and afterLast() move the cursor to the row indicated by the method names.
The method absolute() 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 absolute(1) moves the cursor to the first row. If the number is negative, the cursor moves the given number of rows from the end. Calling absolute(-1) sets the cursor to the last row. The following line of code moves the cursor to the fourth row of srs:
srs.absolute(4);If srs has 500 rows, the following line of code moves the cursor to row 497:
srs.absolute(-4);The method relative() 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:
srs.absolute(4); // cursor is on the fourth row
...
srs.relative(-3); // cursor is on the first row
...
srs.relative(2); // cursor is on the third rowThe method getRow() returns the number of the current row. For example, use getRow() to verify the current position of the cursor in the previous example using the following code:
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
rowNum = srs.getRow(); // rowNum should be 3Note 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: isFirst(), isLast(), isBeforeFirst(), and isAfterLast(). 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 previous() in a while loop. If the method isAfterLast() 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 previous() in the while loop stop at every row in srs.
if (!srs.isAfterLast()) {
srs.afterLast();
}
while (srs.previous()) {
String name = row.getString(1);
float price = row.getFloat(2);
System.out.println(name + " " + price);
}How to use the two methods from the XResultSetUpdate interface to move the cursor: moveToInsertRow() and moveToCurrentRow() 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 ResultSetConcurrency constant UPDATABLE to the Statement property ResultSetConcurrency, so that the Statement object creates an modifiable ResultSet object each time it executes a query.
The following code fragment creates a modifiable XResultSet object rs. Note that the code also makes rs scrollable. A modifiable ResultSet 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 SCROLL_SENSITIVE, the new value in a row can be obtained after it has changed without refreshing the whole result set.
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
XRow row = UnoRuntime.queryInterface(XRow.class, rs);The ResultSet object rs may look similar to this:
| NAME | PRICE |
|---|---|
| Linux | $30.00 |
| Beef | $15.78 |
| Orange juice | $1.50 |
The methods can now be used in the com.sun.star.sdbc.XRowUpdate interface of the result set to insert a new row into rs, delete an existing row from rs, or modify a column value in rs.
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:
stmt.executeUpdate("UPDATE SALES SET PRICE = 0.99" +
"WHERE SALENR = 4");The following code fragment shows another way to accomplish the same update, this time using SDBC:
rs.last();
XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class, rs);
updateRow.updateFloat(2, (float)0.99);Update operations in the SDBC API affect column values in the row where the cursor is positioned. In the first line, the ResultSet rs calls last() to move the cursor to the last row where the column NAME has the value Orange juice. 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 PRICE column to 0.99 by calling updateFloat(). This method is used because the column value we want to update is a float in Java programming language.
The updateXXX() 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 updateXXX() methods for each data type, such as updateString() and updateInt(), just like the getXXX methods discussed above.
At this point, the price in rs for Orange juice is 0.99, but the price in the table SALES 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 updateRow() is called. Here is what the code should look like to update rs and SALES:
rs.last();
XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class, rs);
updateRow.updateFloat(2, (float)0.99);
XResultSetUpdate updateRs = UnoRuntime.queryInterface(XResultSetUpdate.class, rs);
// update the data in DBMS
updateRs.updateRow();If the cursor is moved to a different row before calling updateRow(), the update is lost. The update can be canceled by calling cancelRowUpdates(), for instance, the price should have been 0.79 instead of 0.99. The cancelRowUpdates() has to be invoked before invoking updateRow(). The cancelRowUpdates() does nothing when updateRow() has been called. Note that cancelRowUpdates cancels all the updates in a row, that is, if there were more than one updateXXX 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:
rs.last();
updateRow.updateFloat(2, (float)0.99);
updateRs.cancelRowUpdates();
updateRow.updateFloat(2, (float)0.79);
updateRs.updateRow();In the above example, only one column value is updated, but an appropriate updateXXX() 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 updateRow() 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 previous() 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:
rs.previous();
updateRow.updateFloat(2, (float)10.79);
updateRs.updateRow();All cursor movements refer to rows in a ResultSet 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 stmt is a Statement object, shows this approach:
stmt.executeUpdate("INSERT INTO SALES " +
"VALUES (4, 102, 5, 'FTOP Darjeeling tea', '2002-01-02',150)");The same thing can be done, without using any SQL commands, by using ResultSet methods in the SDBC API. After a ResultSet object is obtained with the results from the table SALES, build the new row and then insert it into the result set and the table SALES in one step. First, build a new row in the insert row, a special row associated with every ResultSet 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 moveToInsertRow(). Then set a value for each column in the row that should not be null by calling the appropriate updateXXX() method for each value. Note that these are the same updateXXX() methods used to change a column value in the previous section.
Finally, call insertRow() to insert the row that was populated with values into the result set. This method simultaneously inserts the row into the ResultSet object, as well as the database table from where the result set was selected.
The following code fragment creates a scrollable and modifiable ResultSet object rs that contains all of the rows and columns in the table SALES:
XConnection con = XDriverManager.getConnection("jdbc:mySubprotocol:mySubName");
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
XResultSet rs = stmt.executeQuery("SELECT * FROM SALES");
XRow row = UnoRuntime.queryInterface(XRow.class, rs);The next code fragment uses the XResultSetUpdate interface of rs 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 rs and SALES:
XRowUpdate updateRow = UnoRuntime.queryInterface(XRowUpdate.class, rs);
XResultSetUpdate updateRs = UnoRuntime.queryInterface(XResultSetUpdate.class, rs);
updateRs.moveToInsertRow();
updateRow.updateInt(1, 4);
updateRow.updateInt(2, 102);
updateRow.updateInt(3, 5);
updateRow.updateString(4, "FTOP Darjeeling tea");
updateRow.updateDate(5, new Date((short)1, (short)2, (short)2002));
updateRow.updateFloat(6, 150);
updateRs.insertRow();The updateXXX() methods behave differently from the way they behaved in the update examples. In those examples, the value set with an updateXXX() 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 updateXXX() 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 updateXXX() method does not affect the underlying database table. The method updateRow() must be called to have updates occur in the database. For insertions, the method insertRow() 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 NULL values, then the value assigned to that column is NULL. If a column does not accept null values, an SQLException is returned when an updateXXX() method is not called to set a value for it. This is also true if a table column is missing in the ResultSet object. In the example above, the query was SELECT * FROM SALES, 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 WHERE clause to limit the number of rows returned by the SELECT statement.
After the method insertRow() 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 first(), last(), beforeFirst(), afterLast(), and absolute(). The methods previous(), relative(), and moveToCurrentRow() can also be used. Note that only moveToCurrentRow() can be invoked as long as the cursor is on the insert row.
When the method moveToInsertRow() is called, the result set records which row the cursor is in, that is by definition the current row. As a consequence, the method moveToCurrentRow() can move the cursor from the insert row back to the row that was the current row previously. This also explains why the methods previous() and relative() 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 ResultSet object by deleting a row. The method deleteRow() is called to delete the row where the cursor is placed. For example, to delete the fourth row in the ResultSet rs, the code looks like this:
rs.absolute(4);
XResultSetUpdate updateRs = UnoRuntime.queryInterface(XResultSetUpdate.class, rs);
updateRs.deleteRow();The fourth row is removed from rs and also from the database.
The only issue about deletions is what the ResultSet 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 absolute() 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 ResultSet 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 ResultSet object is open, is dependent on the DBMS, the driver, and the type of ResultSet object.
With a SCROLL_SENSITIVE ResultSetType 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 Connection object, sets the connection's isolation level to READ_COMMITTED:
con.setTransactionIsolation(TransactionIsolation.READ_COMMITTED);With this isolation level, the ResultSet 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 REPEATABLE_READ. 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 ResultSet object that is SCROLL_INSENSITIVE, changes are not visible while it is still open. Some programmers only use this type of ResultSet object to get a consistent view of the data without seeing changes made by others.
The method refreshRow() 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 refreshRow() is called. The method refreshRow() 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 refreshRow() ensures that only up-to-date data is visible.
The following code sample illustrates how an application might use the method refreshRow() when it is critical to see the latest changes. Note that the result set should be sensitive. If the method refreshRow() with a SCROLL_INSENSITIVE ResultSet is used, refreshRow() does nothing. Getting the latest data for the table SALES 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.
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_SENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
XRow row = UnoRuntime.queryInterface(XRow.class, rs);
rs.absolute(4);
float price1 = row.getFloat(2);
// do something ...
rs.absolute(4);
rs.refreshRow();
float price2 = row.getFloat(2);
if (price2 != price1) {
// do something ...
}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 com.sun.star.sdbc.XResultSetMetaData. The following code fragment shows the use of the XResultSetMetaData interface:
XStatement stmt = con.createStatement();
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, stmt);
xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
XResultSetMetaDataSupplier xRsMetaSup = UnoRuntime.queryInterface(
XResultSetMetaDataSupplier.class, rs);
XResultSetMetaData xRsMetaData = xRsMetaSup.getMetaData();
int nColumnCount = xRsMetaData.getColumnCount();
for (int i=1 ;i <= nColumnCount; ++i) {
System.out.println("Name: " + xRsMetaData.getColumnName(i) + " Type: " +
xRsMetaData.getColumnType(i));
}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, VARCHAR has the value 12 and the type 3 is the SQL data type DECIMAL. 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 PreparedStatement 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 Statement object many times as in the example above.
The main feature of a PreparedStatement object is that it is given an SQL statement when it is created, unlike a Statement object. This SQL statement is sent to the DBMS right away where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can run the PreparedStatement's SQL statement without having to analyze and optimize it again.
The PreparedStatement 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 Statement objects, PreparedStatement objects are created using prepareStatement() on a Connection object. Using our open connection con from the previous examples, code could be written like the following to create a PreparedStatement object that takes two input parameters:
XPreparedStatement updateStreet = con.prepareStatement(
"UPDATE SALESMAN SET STREET = ? WHERE SNR = ?");The variable updateStreet now contains the SQL update statement that has also been sent to the DBMS and precompiled.
Supplying Values for PreparedStatement Parameters
Before executing a PreparedStatement object, values to replace the question mark placeholders or named parameters, such as param1 or param2 have to be supplied. This is accomplished by calling one of the setXXX() methods defined in the interface com.sun.star.sdbc.XParameters of the prepared statement. For instance, to substitute a question mark with a value that is a Java int, call setInt(). If the value is a Java String, call the method setString(). There is a setXXX() method for each type in the Java programming language.
Using the PreparedStatement object updateStreet() 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':
XParameters setPara = UnoRuntime.queryInterface(XParameters.class, updateStreet);
setPara.setString(1, "34 Main Road");The example shows that the first argument given to a setXXX() 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:
setPara.setInt(2, 1);After these values have been set for its two input parameters, the SQL statement in updateStreet is equivalent to the SQL statement in the String object updateString() used in the previous update example. Therefore, the following two code fragments accomplish the same thing:
Code Fragment 1:
String updateString = "UPDATE SALESMAN SET STREET = '34 Main Road' WHERE SNR = 1";
stmt.executeUpdate(updateString);Code Fragment 2:
XPreparedStatement updateStreet = con.prepareStatement(
"UPDATE SALESMAN SET STREET = ? WHERE SNR = ? ");
XParameters setPara = UnoRuntime.queryInterface(XParameters.class,updateStreet);
setPara.setString(1, "34 Main Road");
setPara.setInt(2, 1);
updateStreet.executeUpdate();The method executeUpdate() was used to execute the Statement stmt and the PreparedStatement updateStreet. Notice that no argument is supplied to executeUpdate() when it is used to execute updateStreet. This is true because updateStreet already contains the SQL statement to be executed.
Looking at the above examples, a PreparedStatement 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 PreparedStatement 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 clearParameters() is called. Using the PreparedStatement object updateStreet, 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:
// set the 1st parameter (the STREET column) to Maryland
setPara.setString(1, "Maryland");
// use the 2nd parameter to select George Flint, his unique identifier SNR is 4
setPara.setInt(2, 4);
// write changes to database
updateStreet.executeUpdate();
// changes STREET column back to Michigan road
// the 2nd parameter for SNR still is 4, only the first parameter is adjusted
updateStreet.executeUpdate();
setPara.setString(1, "Michigan road");
// write changes to database
updateStreet.executeUpdate();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:
com::sun::star::sdbc::XPreparedStatement prepareCommand(
[in] string command, [in] long commandType)If the value of the parameter com.sun.star.sdb.CommandType is TABLE or QUERY, pass a table name or query name that exists in the com.sun.star.sdb.DataSource of the connection. The value COMMAND makes prepareCommand() 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:
// 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"));
Object dataSource = xNameAccess.getByName("Ada01");
XDataSource xDataSource = UnoRuntime.queryInterface(XDataSource.class, dataSource);
Object interactionHandler = _rMSF.createInstance("com.sun.star.sdb.InteractionHandler");
XInteractionHandler xInteractionHandler = UnoRuntime.queryInterface(
XInteractionHandler.class, interactionHandler);
XCompletedConnection xCompletedConnection = UnoRuntime.queryInterface(
XCompletedConnection.class, dataSource);
XConnection xConnection = xCompletedConnection.connectWithCompletion(xInteractionHandler);
XCommandPreparation xCommandPreparation = UnoRuntime.queryInterface(
XCommandPreparation.class, xConnection);
XPreparedStatement xPreparedStatement = xCommandPreparation.prepareCommand(
"Query1", CommandType.QUERY);
XResultSet xResult = xPreparedStatement.executeQuery();
XRow xRow = UnoRuntime.queryInterface(XRow.class, xResult);
while (xResult != null && xResult.next()) {
System.out.println(xRow.getString(1));
}Database Design
Retrieving Information about a Database
The com.sun.star.sdbc.XDatabaseMetaData 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 XDatabaseMetaData methods to get information about a database. The com.sun.star.sdbc.XDatabaseMetaData 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 com.sun.star.sdbc.XDatabaseMetaData 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 com.sun.star.sdbc.XDatabaseMetaData 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 Connection method getMetaData(). 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.
// xConnection is a Connection object
XDatabaseMetaData dbmd = xConnection.getMetaData();
int maxLen = dbmd.getMaxTableNameLength();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:
getURL()getUserName()getDatabaseProductVersion(),getDriverMajorVersion()andgetDriverMinorVersion()getSchemaTerm(),getCatalogTerm()andgetProcedureTerm()nullsAreSortedHigh()andnullsAreSortedLow()usesLocalFiles()andusesLocalFilePerTable()getSQLKeywords()
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:
supportsAlterTableWithDropColumn()supportsBatchUpdates()supportsTableCorrelationNames()supportsPositionedDelete()supportsFullOuterJoins()supportsStoredProcedures()supportsMixedCaseQuotedIdentifiers()
Methods to describe the level of feature support include:
supportsANSI92EntryLevelSQL()supportsCoreSQLGrammar()
Database Limits
Another group of methods provides the limits imposed by a given database. Some methods in this category are:
getMaxRowSize()getMaxStatementLength()getMaxTablesInSelect()getMaxConnections()getMaxCharLiteralLength()getMaxColumnsInTable()
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 ResultSet objects in which each row describes a particular object. For example, the method getUDTs() returns a ResultSet 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:
getSchemas()andgetCatalogs()getTables()getPrimaryKeys()getColumns()getProcedures()andgetProcedureColumns()getUDTs()
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 com.sun.star.sdbc.XDatabaseMetaData interface is required:
XDatabaseMetaData dm = con.getMetaData();
XResultSet rsTables = dm.getTables(null, "%", "SALES", null);
XRow rowTB = UnoRuntime.queryInterface(XRow.class, rsTables);
while (rsTables.next()) {
String catalog = rowTB.getString(1);
if (rowTB.wasNull())
catalog = null;
String schema = rowTB.getString(2);
if (rowTB.wasNull())
schema = null;
String table = rowTB.getString(3);
String type = rowTB.getString(4);
System.out.println("Catalog: " + catalog +
" Schema: " + schema + " Table: " + table + "Type: " + type);
System.out.println("------------------ Columns ------------------");
XResultSet rsColumns = dm.getColumns(catalog, schema, table, "%");
XRow rowCL = UnoRuntime.queryInterface(XRow.class, rsColumns);
while (rsColumns.next()) {
System.out.println("Column: " + rowCL.getString(4) +
" Type: " + rowCL.getInt(5) + " TypeName: " + rowCL.getString(6) );
}
}Another method often used when creating SQL statements is the method getIdentifierQuoteString(). This method is always used when table or column names need to be quoted in the SQL statement. For example:
SELECT "Name", "Price" FROM "Sales"In this case, the identifier quotation is the character ". The combination of XDatabaseMetaData methods in the following code fragment may be useful to know if the database supports catalogs and/or schemata.
public static String quoteTableName(XConnection con, String sCatalog, String sSchema,
String sTable) throws com.sun.star.uno.Exception {
XDatabaseMetaData dbmd = con.getMetaData();
String sQuoteString = dbmd.getIdentifierQuoteString();
String sSeparator = ".";
String sComposedName = "";
String sCatalogSep = dbmd.getCatalogSeparator();
if (0 != sCatalog.length() && dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) {
sComposedName += sCatalog;
sComposedName += dbmd.getCatalogSeparator();
}
if (0 != sSchema.length()) {
sComposedName += sSchema;
sComposedName += sSeparator;
sComposedName += sTable;
} else {
sComposedName += sTable;
}
if (0 != sCatalog.length() && !dbmd.isCatalogAtStart() && 0 != sCatalogSep.length()) {
sComposedName += dbmd.getCatalogSeparator();
sComposedName += sCatalog;
}
return sComposedName;
}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, SALESMAN, contains essential information about the salespersons, including the first name, last name, street address, city, and birth date. The table SALESMAN that is described in more detail later, is shown here:
| SNR | FIRSTNAME | LASTNAME | STREET | STATE | ZIP | BIRTH DATE |
|---|---|---|---|---|---|---|
| 1 | 0 | 0 | 0 | 0 | 95460 | 02/07/46 |
| 2 | 0 | 0 | 0 | 0 | 95460 | 12/24/63 |
| 3 | 0 | 0 | 0 | 0 | 95460 | 04/01/72 |
| 4 | 0 | 0 | 0 | 0 | 95460 | 02/13/53 |
| 5 | 0 | 0 | 0 | 0 | 95460 | 09/07/49 |
The first column is the column SNR of SQL type INTEGER. 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 FIRSTNAME that holds values of the SQL type VARCHAR with a maximum length of 50 characters. The third column, LASTNAME, is also a VARCHAR with a maximum length of 100 characters. The STREET and STATE columns are VARCHARs with 50 characters. The column ZIP uses INTEGER and the column BIRTHDATE uses the type DATE. By using the type DATE instead of VARCHAR,the dates of birth can be compared with the current date.
The second table, CUSTOMER, in our database, contains information about customers:
| COS_NR | LASTNAME | STREET | CITY | STATE | ZIP |
|---|---|---|---|---|---|
| 100 | 0 | 0 | 0 | 0 | 95199 |
| 101 | 0 | 0 | 0 | 0 | 95460 |
| 102 | 0 | 0 | 0 | 0 | 93966 |
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.
| SALENR | COS_NR | SNR | NAME | DATE | PRICE |
|---|---|---|---|---|---|
| 1 | 100 | 1 | 0 | 02/12/01 | $39.99 |
| 2 | 101 | 2 | 0 | 10/18/01 | $15.78 |
| 3 | 102 | 4 | Orange juice | 08/09/01 | $1.50 |
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:
CREATE TABLE SALESMAN
(SNR INTEGER NOT NULL,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(100),
STREET VARCHAR(50),
STATE VARCHAR(50),
ZIP VARCHAR(10),
BIRTHDATE DATE,
PRIMARY KEY(SNR)
) |
Note:
|
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:
SELECT "FirstName", "LastName"
FROM "Employees"
WHERE "LastName" LIKE 'Washington'is equivalent to
select "FirstName", LastName" from "Employees" where
"LastName" like 'Washington'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 storesMixedCaseQuotedIdentifiers() returns true, and to use the string that the method getIdentifierQuoteString() returns.
The data types used in our CREATE TABLE 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 executeUpdate() at its interface com.sun.star.sdbc.XStatement. In the following code fragment, executeUpdate() is supplied with the SQL statement from the SALESMAN example above:
XStatement xStatement = con.createStatement();
int n = xStatement.executeUpdate("CREATE TABLE SALESMAN " +
"(SNR INTEGER NOT NULL, " +
"FIRSTNAME VARCHAR(50), " +
"LASTNAME VARCHAR(100), " +
"STREET VARCHAR(50), " +
"STATE VARCHAR(50), " +
"ZIP INTEGER, " +
"BIRTHDATE DATE, " +
"PRIMARY KEY(SNR) " +
")");The method executeUpdate() is used because the SQL statement contained in createTableSalesman 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 executeUpdate().
When the method executeUpdate() 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.
Using SDBCX to Access the Database Design
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 com.sun.star.sdbcx.Container. The interfaces that the container supports depend on the objects that reside in it. For instance, the container for keys does not support an com.sun.star.container.XNameAccess 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 com.sun.star.sdbcx.XDataDefinitionSupplier and an existing connection object. The following code fragment lists tables in a database.
// create the Driver with the implementation name
Object aDriver = xORB.createInstance("com.sun.star.comp.sdbcx.adabas.ODriver");
// query for the interface
com.sun.star.sdbc.XDriver xDriver;
xDriver = UnoRuntime.queryInterface(XDriver.class, aDriver);
if (xDriver != null) {
// first create the needed url
String adabasURL = "sdbc:adabas::MYDB0";
// second create the necessary properties
com.sun.star.beans.PropertyValue [] adabasProps = new com.sun.star.beans.PropertyValue[] {
new com.sun.star.beans.PropertyValue("user", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE),
new com.sun.star.beans.PropertyValue("password", 0, "test1",
com.sun.star.beans.PropertyState.DIRECT_VALUE)
};
// now create a connection to adabas
XConnection adabasConnection = xDriver.connect(adabasURL, a dabasProps);
if(adabasConnection != null) {
System.out.println("Connection could be created!");
// we need the XDatabaseDefinitionSupplier interface
// from the driver to get the XTablesSupplier
XDataDefinitionSupplier xDDSup = UnoRuntime.queryInterface(
XDataDefinitionSupplier.class, xDriver);
if (xDDSup != null) {
XTablesSupplier xTabSup = xDDSup.getDataDefinitionByConnection(adabasConnection);
if (xTabSup != null) {
XNameAccess xTables = xTabSup.getTables();
// now print all table names
System.out.println("Tables available:");
String [] aTableNames = xTables.getElementNames();
for ( int i =0; i<= aTableNames.length-1; i++)
System.out.println(aTableNames[i]);
}
}
else {
System.out.println("The driver is not SDBCX capable!");
}
// now we dispose the connection to close it
XComponent xComponent = (XComponent)UnoRuntime.queryInterface(
XComponent.class, adabasConnection);
if (xComponent != null) {
xComponent.dispose();
System.out.println("Connection disposed!");
}
}
else {
System.out.println("Connection could not be created!");
}
}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 Name, CatalogName, SchemaName, Description, and an optional Type. The properties CatalogName and SchemaName can be empty when the database does not support these features. The Description property contains any comments that were added to the table object at creation time. The optional property Type 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 Table object also supports the com.sun.star.sdbcx.XColumnsSupplier 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:
- com.sun.star.sdbcx.XDataDescriptorFactory interface that is used to copy a table object.
- com.sun.star.sdbcx.XIndexesSupplier interface that returns the container for indexes.
- com.sun.star.sdbcx.XKeysSupplier interface that returns the keys container.
- com.sun.star.sdbcx.XRename interface that allows renaming a table object.
- com.sun.star.sdbcx.XAlterTable 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.
...
XNameAccess xTables = xTabSup.getTables();
if (0 != aTableNames.length) {
Object table = xTables.getByName(aTableNames[0]);
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class, table);
System.out.println("Name: " + xProp.getPropertyValue("Name"));
System.out.println("CatalogName: " + xProp.getPropertyValue("CatalogName"));
System.out.println("SchemaName: " + xProp.getPropertyValue("SchemaName"));
System.out.println("Description: " + xProp.getPropertyValue("Description"));
// the following property is optional so we first must check if it exists
if(xProp.getPropertySetInfo().hasPropertyByName("Type"))
System.out.println("Type: " + xProp.getPropertyValue("Type"));
}The Table object contains access to the columns, keys, and indexes when the above mentioned interfaces are supported.
// print all columns of a XColumnsSupplier
// later on used for keys and indexes as well
public static void printColumns(XColumnsSupplier xColumnsSup)
{
System.out.println("Example printColumns");
// the table must at least support a XColumnsSupplier interface
System.out.println("--- Columns ---");
XNameAccess xColumns = xColumnsSup.getColumns();
String [] aColumnNames = xColumns.getElementNames();
for ( int i =0; i<= aColumnNames.length-1; i++)
System.out.println(" " + aColumnNames[i]);
}
// print all keys including the columns of a key
public static void printKeys(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception
{
System.out.println("Example printKeys");
XKeysSupplier xKeysSup = UnoRuntime.queryInterface(XKeysSupplier.class,xColumnsSup);
if(xKeysSup != null)
{
System.out.println("--- Keys ---");
XIndexAccess xKeys = xKeysSup.getKeys();
for ( int i =0; i < xKeys.getCount(); i++)
{
Object key = xKeys.getByIndex(i);
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,key);
System.out.println(" " + xProp.getPropertyValue("Name"));
XColumnsSupplier xKeyColumnsSup = UnoRuntime.queryInterface(XColumnsSupplier.class,xProp);
printColumns(xKeyColumnsSup);
}
}
}
// print all indexes including the columns of an index
public static void printIndexes(XColumnsSupplier xColumnsSup) throws com.sun.star.uno.Exception
{
System.out.println("Example printIndexes");
XIndexesSupplier xIndexesSup = UnoRuntime.queryInterface(XIndexesSupplier.class,xColumnsSup);
if(xIndexesSup != null)
{
System.out.println("--- Indexes ---");
XNameAccess xIndexs = xIndexesSup.getIndexes();
String [] aIndexNames = xIndexs.getElementNames();
for ( int i =0; i<= aIndexNames.length-1; i++)
{
System.out.println(" " + aIndexNames[i]);
Object index = xIndexs.getByName(aIndexNames[i]);
XColumnsSupplier xIndexColumnsSup = UnoRuntime.queryInterface(XColumnsSupplier.class,index);
printColumns(xIndexColumnsSup);
}
}
}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
Columnservice is used for the table object. - com.sun.star.sdbcx.KeyColumn extends the "normal" com.sun.star.sdbcx.Column service with an extra property named
RelatedColumn. This property is the name of a referenced column out of the referenced table. - com.sun.star.sdbcx.IndexColumn extends the com.sun.star.sdbcx.Column service with an extra boolean property named
IsAscending. This property is true when the index is ascending, otherwise it is false.
The Column object is defined by the following properties:
| Name | string - The name of the column.
|
| Type | com.sun.star.sdbc.DataType, long - The SDBC data type. |
| TypeName | string - The database name for this type.
|
| Precision | long - The column's number of decimal digits.
|
| Scale | long - The column's number of digits to the left of the decimal point.
|
| IsNullable | long - Indicates the nullification of values in the designated column. com.sun.star.sdbc.ColumnValue |
| IsAutoIncrement | boolean - Indicates if the column is automatically numbered.
|
| IsCurrency | boolean - Indicates if the column is a cash value.
|
| IsRowVersion | boolean - Indicates that the column contains some kind of time or date stamp used to track updates (optional).
|
| Description | string - Keeps a description of the object (optional).
|
| DefaultValue | string - Keeps a default value for a column (optional).
|
The Column object also supports the com.sun.star.sdbcx.XDataDescriptorFactory interface that creates a copy of this object.
// column properties
public static void printColumnProperties(Object column) throws com.sun.star.uno.Exception
{
System.out.println("Example printColumnProperties");
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,column);
System.out.println("Name: " + xProp.getPropertyValue("Name"));
System.out.println("Type: " + xProp.getPropertyValue("Type"));
System.out.println("TypeName: " + xProp.getPropertyValue("TypeName"));
System.out.println("Precision: " + xProp.getPropertyValue("Precision"));
System.out.println("Scale: " + xProp.getPropertyValue("Scale"));
System.out.println("IsNullable: " + xProp.getPropertyValue("IsNullable"));
System.out.println("IsAutoIncrement: " + xProp.getPropertyValue("IsAutoIncrement"));
System.out.println("IsCurrency: " + xProp.getPropertyValue("IsCurrency"));
// the following property is optional so we first must check if it exists
if(xProp.getPropertySetInfo().hasPropertyByName("IsRowVersion"))
System.out.println("IsRowVersion: " + xProp.getPropertyValue("IsRowVersion"));
if(xProp.getPropertySetInfo().hasPropertyByName("Description"))
System.out.println("Description: " + xProp.getPropertyValue("Description"));
if(xProp.getPropertySetInfo().hasPropertyByName("DefaultValue"))
System.out.println("DefaultValue: " + xProp.getPropertyValue("DefaultValue"));
}Index Service
The Index service encapsulates indexes at a table object. An index is described through the properties Name, Catalog, IsUnique, IsPrimaryKeyIndex, and IsClustered. 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 IsPrimaryKeyIndex 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:
// index properties
public static void printIndexProperties(Object index) throws com.sun.star.uno.Exception
{
System.out.println("Example printIndexProperties");
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,index);
System.out.println("Name: " + xProp.getPropertyValue("Name"));
System.out.println("Catalog: " + xProp.getPropertyValue("Catalog"));
System.out.println("IsUnique: " + xProp.getPropertyValue("IsUnique"));
System.out.println("IsPrimaryKeyIndex: " + xProp.getPropertyValue("IsPrimaryKeyIndex"));
System.out.println("IsClustered: " + xProp.getPropertyValue("IsClustered"));
}Key Service
The Key service provides the foreign and primary keys behavior through the following properties. The Name 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 ReferencedTable contains a value when the key is a foreign key and it designates the table to which a foreign key points. The DeleteRule and UpdateRule properties determine what happens when a primary key is deleted or updated. The possibilities are defined in com.sun.star.sdbc.KeyRule: CASCADE, RESTRICT, SET_NULL, NO_ACTION and SET_DEFAULT.
The following code fragment displays the properties of a given key object:
// key properties
public static void printKeyProperties(Object key) throws com.sun.star.uno.Exception
{
System.out.println("Example printKeyProperties");
XPropertySet xProp = UnoRuntime.queryInterface(XPropertySet.class,key);
System.out.println("Name: " + xProp.getPropertyValue("Name"));
System.out.println("Type: " + xProp.getPropertyValue("Type"));
System.out.println("ReferencedTable: " + xProp.getPropertyValue("ReferencedTable"));
System.out.println("UpdateRule: " + xProp.getPropertyValue("UpdateRule"));
System.out.println("DeleteRule: " + xProp.getPropertyValue("DeleteRule"));
}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 Command and if the database driver supports a check option, set it in the property CheckOption. Possible values of com.sun.star.sdbcx.CheckOption are NONE, CASCADE and LOCAL. A schema or catalog name can be provided (optional).
Group Service
The service com.sun.star.sdbcx.Group is the first of the two security services, Group and User. The Group service represents the group account that has access permissions to a secured database and it has a Name property to identify it. It supports the interface com.sun.star.sdbcx.XAuthorizable that allows current privilege settings to be obtained, and to grant or revoke privileges. The second interface is com.sun.star.sdbcx.XUsersSupplier. 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.
// print all groups and the users with their privileges who belong to this group
public static void printGroups(XTablesSupplier xTabSup) throws com.sun.star.uno.Exception,SQLException
{
System.out.println("Example printGroups");
XGroupsSupplier xGroupsSup = UnoRuntime.queryInterface(XGroupsSupplier.class,xTabSup);
if(xGroupsSup != null)
{
// the table must be at least support a XColumnsSupplier interface
System.out.println("--- Groups ---");
XNameAccess xGroups = xGroupsSup.getGroups();
String [] aGroupNames = xGroups.getElementNames();
for ( int i =0; i < aGroupNames.length; i++)
{
System.out.println(" " + aGroupNames[i]);
XUsersSupplier xUsersSup = UnoRuntime.queryInterface(XUsersSupplier.class,xGroups.getByName(aGroupNames[i]));
if(xUsersSup != null)
{
XAuthorizable xAuth = UnoRuntime.queryInterface(XAuthorizable.class,xUsersSup);
// the table must be at least support a XColumnsSupplier interface
System.out.println("\t--- Users ---");
XNameAccess xUsers = xUsersSup.getUsers();
String [] aUserNames = xUsers.getElementNames();
for ( int j =0; j < aUserNames.length; j++)
{
System.out.println("\t " + aUserNames[j] + " Privileges: " + xAuth.getPrivileges(aUserNames[j],PrivilegeObject.TABLE));
}
}
}
}
}User Service
The com.sun.star.sdbcx.User 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 Group service, the User service supports the interface com.sun.star.sdbcx.XAuthorizable. This is achieved through the interface com.sun.star.sdbcx.XUser derived from XAuthorizable. In addition to this interface, the XUser interface supports changing the password of a specific user. Similar to the Group 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:
- 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.
therefore, only create one descriptor to append more than one column.
- 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 com.sun.star.sdbcx.XDataDescriptorFactory interface. When the createDataDescriptor method is called, the com.sun.star.beans.XPropertySet interface of an object that implements the service com.sun.star.sdbcx.TableDescriptor 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 Name, and Type. 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 TableDescriptor object to its container or define some key objects, such as a primary key.
// create the table salesmen
public static void createTableSalesMen(XNameAccess xTables) throws com.sun.star.uno.Exception,SQLException
{
System.out.println("Example createTableSalesMen");
XDataDescriptorFactory xTabFac = UnoRuntime.queryInterface(XDataDescriptorFactory.class,xTables);
if(xTabFac != null)
{
// create the new table
XPropertySet xTable = xTabFac.createDataDescriptor();
// set the name of the new table
xTable.setPropertyValue("Name","SALESMAN");
// append the columns
XColumnsSupplier xColumSup = UnoRuntime.queryInterface(XColumnsSupplier.class,xTable);
XDataDescriptorFactory xColFac = UnoRuntime.queryInterface(XDataDescriptorFactory.class,xColumSup.getColumns());
XAppend xAppend = UnoRuntime.queryInterface(XAppend.class,xColFac);
// we only need one descriptor
XPropertySet xCol = xColFac.createDataDescriptor();
// create first column and append
xCol.setPropertyValue("Name","SNR");
xCol.setPropertyValue("Type",Integer.valueOf(DataType.INTEGER));
xCol.setPropertyValue("IsNullable",Integer.valueOf(ColumnValue.NO_NULLS));
xAppend.appendByDescriptor(xCol);
// 2nd only set the properties which differ
xCol.setPropertyValue("Name","FIRSTNAME");
xCol.setPropertyValue("Type",Integer.valueOf(DataType.VARCHAR));
xCol.setPropertyValue("IsNullable",Integer.valueOf(ColumnValue.NULLABLE));
xCol.setPropertyValue("Precision",Integer.valueOf(50));
xAppend.appendByDescriptor(xCol);
// 3rd only set the properties which differ
xCol.setPropertyValue("Name","LASTNAME");
xCol.setPropertyValue("Precision",Integer.valueOf(100));
xAppend.appendByDescriptor(xCol);
// 4th only set the properties which differ
xCol.setPropertyValue("Name","STREET");
xCol.setPropertyValue("Precision",Integer.valueOf(50));
xAppend.appendByDescriptor(xCol);
// 5th only set the properties which differ
xCol.setPropertyValue("Name","STATE");
xAppend.appendByDescriptor(xCol);
// 6th only set the properties which differ
xCol.setPropertyValue("Name","ZIP");
xCol.setPropertyValue("Type",Integer.valueOf(DataType.INTEGER));
xCol.setPropertyValue("Precision",Integer.valueOf(10)); // default value integer
xAppend.appendByDescriptor(xCol);
// 7th only set the properties which differ
xCol.setPropertyValue("Name","BIRTHDATE");
xCol.setPropertyValue("Type",Integer.valueOf(DataType.DATE));
xCol.setPropertyValue("Precision",Integer.valueOf(10)); // default value integer
xAppend.appendByDescriptor(xCol);
// now we create the primary key
XKeysSupplier xKeySup = UnoRuntime.queryInterface(XKeysSupplier.class,xTable);
XDataDescriptorFactory xKeyFac = UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeySup.getKeys());
XAppend xKeyAppend = UnoRuntime.queryInterface(XAppend.class,xKeyFac);
XPropertySet xKey = xKeyFac.createDataDescriptor();
xKey.setPropertyValue("Type",Integer.valueOf(KeyType.PRIMARY));
// now append the columns to key
XColumnsSupplier xKeyColumSup = UnoRuntime.queryInterface(XColumnsSupplier.class,xKey);
XDataDescriptorFactory xKeyColFac = UnoRuntime.queryInterface(XDataDescriptorFactory.class,xKeyColumSup.getColumns());
XAppend xKeyColAppend = UnoRuntime.queryInterface(XAppend.class,xKeyColFac);
// we only need one descriptor
XPropertySet xKeyCol = xKeyColFac.createDataDescriptor();
xKeyCol.setPropertyValue("Name","SNR");
// append the key column
xKeyColAppend.appendByDescriptor(xKeyCol);
// append the key
xKeyAppend.appendByDescriptor(xKey);
// the last step is to append the new table to the tables collection
XAppend xTableAppend = UnoRuntime.queryInterface(XAppend.class,xTabFac);
xTableAppend.appendByDescriptor(xTable);
}
}Adding an Index
To add an index, the same programmatic logic is followed. Create an IndexDescriptor with the com.sun.star.sdbcx.XDataDescriptorFactory 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 TableDescriptor.
The task is completed when the index object is added to the index container, unless the append() 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 com.sun.star.sdbcx.XDataDescriptorFactory interface is used from the users container. Create a user with the UserDescriptor. The com.sun.star.sdbcx.UserDescriptor has an additional property than the User service supports. This additional property is the Password property which should be set. Then the UserDescriptor object can be appended to the user container.
// create a user
public static void createUser(XNameAccess xUsers) throws com.sun.star.uno.Exception,SQLException
{
System.out.println("Example createUser");
XDataDescriptorFactory xUserFac = UnoRuntime.queryInterface(XDataDescriptorFactory.class,xUsers);
if(xUserFac != null)
{
// create the new table
XPropertySet xUser = xUserFac.createDataDescriptor();
// set the name of the new table
xUser.setPropertyValue("Name","BOSS");
xUser.setPropertyValue("Password","BOSSWIFENAME");
XAppend xAppend = UnoRuntime.queryInterface(XAppend.class,xUserFac);
xAppend.appendByDescriptor(xUser);
}
}Adding a Group
Creating a com.sun.star.sdbcx.GroupDescriptor object is the same as the methods described above. Follow the same steps:
- Set a name for the group in the
Nameproperty. - Append all the users to the user container of the group.
- Append the
GroupDescriptorobject to the group container of the catalog.
Using DBMS Features
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 com.sun.star.sdbc.XConnection interface of connections. The transaction related methods of this interface are:
// transactions
void setTransactionIsolation( [in] long level)
long getTransactionIsolation()
void setAutoCommit( [in] boolean autoCommit)
boolean getAutoCommit()
void commit()
void rollback()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 setAutoCommit(false). 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 setAutoCommit(true).
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:
| NONE | Indicates that transactions are not supported. |
| READ_UNCOMMITTED | Dirty reads, non-repeatable reads and phantom reads can occur. This level allows a row changed by one transaction to be read by another transaction before any changes in that row have been committed (a "dirty read"). If any of the changes are rolled back, the second transaction retrieves an invalid row. |
| READ_COMMITTED | Dirty reads are prevented; non-repeatable reads and phantom reads can occur. This level only prohibits a transaction from reading a row with uncommitted changes in it. |
| REPEATABLE_READ | Dirty reads and non-repeatable reads are prevented; phantom reads can occur. This level prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read"). |
| SERIALIZABLE | Dirty reads, non-repeatable reads and phantom reads are prevented. This level includes the prohibitions in REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read. |
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 prepareCall() of the interface com.sun.star.sdbc.XConnection.
com::sun::star::sdbc::XPreparedStatement prepareCall( [in] string sql)The method prepareCall() takes a an SQL statement that may contain one or more '?' in parameter placeholders. It returns a com.sun.star.sdbc.CallableStatement. A CallableStatement 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.
| registerOutParameter() | Takes the arguments long parameterIndex, long sqlType, string typeName. Registers an output parameter and should be used for a user-named or REF output parameter. Examples of user-named types include: STRUCT, DISTINCT, OBJECT, and named array types. |
| registerNumericOutParameter() | Takes the arguments long parameterIndex, long sqlType, long scale. Registers an out parameter in the ordinal position parameterIndex with the com.sun.star.sdbc.DataType sqlType; scale is the number of digits on the right-hand side of the decimal point. |
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:
Driver, a singleton which creates the connection object.Connection, createsStatement,PreparedStatementand gives access to theDatabaseMetaData.DatabaseMetaData, returns information about the used database.Statement, createsResultSets.PreparedStatement, createsResultSetsin conjunction with parameters.ResultSet, fetches the data returned by an SQL statement.ResultSetMetaData, describes the columns of aResultSet.
The relationship between these services is depicted in the illustration below.
Driver Service
The Driver 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 Driver is responsible for creating a connection object that represents the database on the client side.
The class must be derived from the interface com.sun.star.sdbc.XDriver that defines the methods needed to create a connection object. The code in the following lines shows a snippet of a driver class.
// --------------------------------------------------------------------------------
Reference< XConnection > SAL_CALL SkeletonDriver::connect( const ::rtl::OUString& url,
const Sequence< PropertyValue >& info )
{
// create a new connection with the given properties and append it to our vector
OConnection* pCon = new OConnection(this);
Reference< XConnection > xCon = pCon; // important here because otherwise the connection
// could be deleted inside (refcount goes -> 0)
pCon->construct(url,info); // late constructor call which can throw exception
// and allows a correct dtor call when so
m_xConnections.push_back(WeakReferenceHelper(*pCon));
return xCon;
}
// --------------------------------------------------------------------------------
sal_Bool SAL_CALL SkeletonDriver::acceptsURL( const ::rtl::OUString& url )
{
// here we have to look if we support this url format
// change the URL format to your needs, but please be aware that
//the first who accepts the URL wins.
return (!url.compareTo(::rtl::OUString::createFromAscii("sdbc:skeleton:"),14));
}
// --------------------------------------------------------------------------------
Sequence< DriverPropertyInfo > SAL_CALL SkeletonDriver::getPropertyInfo( const ::rtl::OUString& url, const Sequence< PropertyValue >& info )
{
// if you have something special to say, return it here :-)
return Sequence< DriverPropertyInfo >();
}
// --------------------------------------------------------------------------------
sal_Int32 SAL_CALL SkeletonDriver::getMajorVersion( )
{
return 0; // depends on you
}
// --------------------------------------------------------------------------------
sal_Int32 SAL_CALL SkeletonDriver::getMinorVersion( )
{
return 1; // depends on you
}
// --------------------------------------------------------------------------------The main methods of this class are acceptsURL and connect:
- The method
acceptsURL()is called every time a user wants to create a connection through theDriverManager, because theDriverManagerdecides theDriverit should ask to connect to the given URL. Therefore this method should be small and run very fast. - The method
connect()is called after the methodacceptsURL()is invoked and returned true. Theconnect()could be seen as a factory method that createsConnectionservices specific for a driver implementation. To accomplish this, theDriverclass must be singleton. Singleton means that only one instance of theDriverclass 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 com.sun.star.lang.XComponent that is responsible to close the connection when it is disposed.
- The interface com.sun.star.sdbc.XWarningsSupplier that controls the chaining of warnings which may occur on every call.
- The interface com.sun.star.sdbc.XConnection 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 Connection class. To understand the interface com.sun.star.sdbc.XConnection, 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 getMetaData() that returns an object which implements the interface com.sun.star.sdbc.XDatabaseMetaData. 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 getTables(), getColumns() and getTypeInfo() 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.
| createStatement() | Creates a new com.sun.star.sdbc.Statement object for sending SQL statements to the database. SQL statements without parameters are executed using Statement objects. |
| prepareStatement | Creates a com.sun.star.sdbc.PreparedStatement object for sending parameterized SQL statements to the database. |
| prepareCall | Creates a com.sun.star.sdbc.CallableStatement object for calling database stored procedures. |
Reference< XStatement > SAL_CALL OConnection::createStatement( )
{
::osl::MutexGuard aGuard( m_aMutex );
checkDisposed(OConnection_BASE::rBHelper.bDisposed);
// create a statement
// the statement can only be executed once
Reference< XStatement > xReturn = new OStatement(this);
m_aStatements.push_back(WeakReferenceHelper(xReturn));
return xReturn;
}
// --------------------------------------------------------------------------------
Reference< XPreparedStatement > SAL_CALL OConnection::prepareStatement( const ::rtl::OUString& _sSql )
{
::osl::MutexGuard aGuard( m_aMutex );
checkDisposed(OConnection_BASE::rBHelper.bDisposed);
// the pre
if(m_aTypeInfo.empty())
buildTypeInfo();
// create a statement
// the statement can only be executed more than once
Reference< XPreparedStatement > xReturn = new OPreparedStatement(this,m_aTypeInfo,_sSql);
m_aStatements.push_back(WeakReferenceHelper(xReturn));
return xReturn;
}
// --------------------------------------------------------------------------------
Reference< XPreparedStatement > SAL_CALL OConnection::prepareCall( const ::rtl::OUString& _sSql )
{
::osl::MutexGuard aGuard( m_aMutex );
checkDisposed(OConnection_BASE::rBHelper.bDisposed);
// not implemented yet :-) a task to do
return NULL;
}All other methods can be omitted at this stage. For detailed descriptions, refer to the API Reference Manual.
XDatabaseMetaData Interface
The com.sun.star.sdbc.XDatabaseMetaData 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.
| isReadOnly() | Returns the state of the database. When true, the database is not editable later in LibreOffice API. |
| usesLocalFiles() | Returns true when the catalog name of the database should not appear in the DatasourceBrowser of LibreOffice API, otherwise false is returned. |
| supportsMixedCaseQuotedIdentifiers() | When this method returns true,the quoted identifiers are case sensitive. For example, in a driver that supports mixed case quoted identifiers, SELECT * FROM "MyTable" retrieves data from a table with the case-sensitive name MyTable. |
| getTables() | Returns a ResultSet object that returns a single row for each table that fits the search criteria, such as the catalog name, schema pattern, table name pattern and sequence of table types. The correct column count and names of the columns are found at com.sun.star.sdbc.XDatabaseMetaData:getTables(). If this method does not return any rows, this driver does not work with LibreOffice API.
|
Any other getXXX() method can be implemented step by step. For the first step they return an empty ResultSet object that contains no rows. It is not allowed to return NULL here.
The skeleton driver defines empty ResultSets for these get methods.
Reference< XResultSet > SAL_CALL ODatabaseMetaData::getTables(
const Any& catalog, const ::rtl::OUString& schemaPattern,
const ::rtl::OUString& tableNamePattern, const Sequence< ::rtl::OUString >& types )
{
// this returns an empty resultset where the column-names are already set
// in special the metadata of the resultset already returns the right columns
ODatabaseMetaDataResultSet* pResultSet = new ODatabaseMetaDataResultSet();
Reference< XResultSet > xResultSet = pResultSet;
pResultSet->setTablesMap();
return xResultSet;
}Statements
Statements are used to create ResultSets or to update the database. The executeQuery() method creates new ResultSets. The following code snippet shows how the new ResultSet is created. There can be only one ResultSet at a time.
Reference< XResultSet > SAL_CALL OStatement_Base::executeQuery( const ::rtl::OUString& sql )
{
::osl::MutexGuard aGuard( m_aMutex );
checkDisposed(OStatement_BASE::rBHelper.bDisposed);
Reference< XResultSet > xRS = NULL;
// create a resultset as result of executing the sql statement
// something needs to be done here :-)
m_xResultSet = xRS; // we nedd a reference to it for later use
return xRS;
}The executeUpdate() methods only return the rows that were affected by the given SQL statement. The last method execute returns true when a ResultSet object is returned when calling the method getResultSet(), otherwise it returns false. All other methods have to be implemented.
PreparedStatement
The PreparedStatement 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 com.sun.star.sdbc.XResultSetMetaDataSupplier interface and also the com.sun.star.sdbc.XParameters interface to set values for their parameters.
Result Set
The ResultSet needs to be implemented. For the first step, only forward ResultSets could be implemented, but it is recommended to support all ResultSet 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 XdatabaseMetaData.getNumericFunctions() returns a comma separated list of the Open Group CLI names of the numeric functions supported. Similarly, the method XDatabaseMetaData.getStringFunctions() returns a list of string functions supported.
In the following table, the scalar functions are listed by category.
Open Group CLI Numeric Functions
| Numeric Function | Function Returns |
|---|---|
ABS(number)
|
Absolute value of number |
ACOS(float)
|
Arccosine, in radians, of float |
ASIN(float)
|
Arcsine, in radians, of float |
ATAN(float)
|
Arctangent, in radians, of float |
ATAN2(float1, float2)
|
Arctangent, in radians, of float2 / float1 |
CEILING(number)
|
Smallest integer >= number |
COS(float)
|
Cosine of float radians |
COT(float)
|
Cotangent of float radians |
DEGREES(number)
|
Degrees in number radians |
EXP(float)
|
Exponential function of float |
FLOOR(number)
|
Largest integer <= number |
LOG(float)
|
Base e logarithm of float |
LOG10(float)
|
Base 10 logarithm of float |
MOD(integer1, integer2)
|
Remainder for integer1 / integer2 |
PI()
|
The constant pi |
POWER(number, power)
|
number raised to (integer) power |
RADIANS(number)
|
Radians in number degrees |
RAND(integer)
|
Random floating point for seed integer |
ROUND(number, places)
|
number rounded to places places |
SIGN(number)
|
-1 to indicate number is < 0; 0 to indicate number is = 0; 1 to indicate number is > 0 |
SIN(float)
|
Sine of float radians |
SQRT(float)
|
Square root of float |
TAN(float)
|
Tangent of float radians |
TRUNCATE(number, places)
|
number truncated to places places |
Open Group CLI String Functions
| String Functions | Function Returns |
|---|---|
ASCII(string)
|
Integer representing the ASCII code value of the leftmost character in string. |
CHAR(code)
|
Character with ASCII code value code, where the code is between 0 and 255. |
CONCAT(string1, string2)
|
Character string formed by appending string2 to string1. If a string is null, the result is DBMS-dependent. |
DIFFERENCE(string1, string2)
|
Integer indicating the difference between the values returned by the function SOUNDEX for string1 and string2. |
INSERT(string1, start, length, string2)
|
A character string formed by deleting length characters from string1 beginning at the start, and inserting string2 into string1 at the start. |
LCASE(string)
|
Converts all uppercase characters in string to lowercase. |
LEFT(string, count)
|
The count leftmost characters from string. |
LENGTH(string)
|
Number of characters in string, excluding trailing blanks. |
LOCATE(string1, string2[, start])
|
Position in string2 of the first occurrence of string1, searching from the beginning of string2. If start is specified, the search begins from position start. A 0 is returned if string2 does not contain string1. Position 1 is the first character in string2. |
LTRIM(string)
|
Characters of string with leading blank spaces removed. |
REPEAT(string, count)
|
A character string formed by repeating string count times. |
REPLACE(string1, string2, string3)
|
Replaces all occurrences of string2 in string1 with string3. |
RIGHT(string, count)
|
The count rightmost characters in string. |
RTRIM(string)
|
The characters of string with no trailing blanks. |
SOUNDEX(string)
|
A character string that is data source-dependent, representing the sound of the words in string, such as a four-digit SOUNDEX code, or a phonetic representation of each word. |
SPACE(count)
|
A character string consisting of count spaces. |
SUBSTRING(string, start, length)
|
A character string formed by extracting length characters from string beginning at start. |
UCASE(string)
|
Converts all lowercase characters in string to uppercase. |
Open Group CLI Time and Date Functions
| Time and Date Functions | Function Returns |
|---|---|
CURDATE()
|
The current date as a date value. |
CURTIME()
|
The current local time as a time value. |
DAYNAME(date)
|
A character string representing the day component of the date. The name for the day is specific to the data source. |
DAYOFMONTH(date)
|
An integer from 1 to 31 representing the day of the month in date. |
DAYOFWEEK(date)
|
An integer from 1 to 7 representing the day of the week in date, where 1 represents Sunday. |
DAYOFYEAR(date)
|
An integer from 1 to 366 representing the day of the year in date. |
HOUR(time)
|
An integer from 0 to 23 representing the hour component of time. |
MINUTE(time)
|
An integer from 0 to 59 representing the minute component of time. |
MONTH(date)
|
An integer from 1 to 12 representing the month component of date. |
MONTHNAME(date)
|
A character string representing the month component of date. The name for the month is specific to the data source. |
NOW()
|
A timestamp value representing the current date and time. |
QUARTER(date)
|
An integer from 1 to 4 representing the quarter in date, where 1 represents January 1 through March 31. |
SECOND(time)
|
An integer from 0 to 59 representing the second component of time. |
TIMESTAMPADD(interval, count, timestamp)
|
A timestamp calculated by adding count interval(s) to timestamp. Interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR.
|
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
|
An integer representing the number of interval(s) by which timestamp2 is greater than timestamp1. Interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR
|
WEEK(date)
|
An integer from 1 to 53 representing the week of the year in date. |
YEAR(date)
|
An integer representing the year component of date. |
Open Group CLI System Functions
| System Functions | Function Returns |
|---|---|
DATABASE()
|
Name of the database. |
IFNULL(expression, value)
|
Value if the expression is null; expression if expression is not null. |
USER()
|
User name in the DBMS. |
Open Group CLI Conversion Functions
| Conversion Function | Function Returns |
|---|---|
CONVERT(value, SQLtype)
|
Value converted to SQLtype where SQLtype may be one of the following SQL types: BIGINT, BINARY, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY, LONGVARCHAR, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, or VARCHAR.
|
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 CallableStatement methods that deal with out parameters (registerOutParameter and the various XCallableStatement.getXXX() 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 SQLException. 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:
supportsResultSetType() - modifiable result sets:
supportsResultSetConcurrency() - batch updates:
supportsBatchUpdates() - SQL3 data types:
getTypeInfo() - storage and retrieval of Java objects:
getUDTs()returns descriptions of the user defined types in a given schemagetTypeInfo()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
- com.sun.star.sdb.tools.XViewAccess
- com.sun.star.sdb.tools.XTableRename
- com.sun.star.sdb.tools.XTableAlteration
- com.sun.star.sdb.tools.XKeyAlteration
- com.sun.star.sdb.tools.XIndexAlteration
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.
| Configuration name | Interface name which has to to be implemented |
|---|---|
| ViewAccessServiceName | com.sun.star.sdb.tools.XViewAccess |
| TableAlterationServiceName | com.sun.star.sdb.tools.XTableAlteration |
| TableRenameServiceName | com.sun.star.sdb.tools.XTableRename |
| IndexAlterationServiceName | com.sun.star.sdb.tools.XIndexAlteration |
| KeyAlterationServiceName | com.sun.star.sdb.tools.XKeyAlteration |
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 com.sun.star.sdb.DocumentContainer service. The configuration entries are named
- Forms
- Reports
additionally to the interfaces defined in the service com.sun.star.sdb.DocumentContainer, the interface com.sun.star.lang.XInitialization has to be implemented which will be called with a argument called "DatabaseDocument" containing the database document.
To allow to support command definitions from other locations, an extension may implement the com.sun.star.sdb.DefinitionContainer service. The configuration entry is named
- CommandDefinitions
A difference is that the interface com.sun.star.lang.XInitialization will be called with a "DataSource" argument.
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.
<oor:component-data oor:name="Drivers" oor:package="org.openoffice.Office.DataAccess" xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<node oor:name="Installed">
<node oor:name="jdbc:" oor:op="replace">
<prop oor:name="ParentURLPattern">
<value>jdbc:*</value>
</prop>
<prop oor:name="DriverTypeDisplayName" oor:type="xs:string">
<value xml:lang="en-US">SQL 2008 JDBC</value>
</prop>
<node oor:name="Properties">
<node oor:name="ViewAccessServiceName" oor:op="replace">
<prop oor:name="Value" oor:type="xs:string">
<value>com.sun.star.sdb.comp.SQL2008.ViewAccess</value>
</prop>
</node>
<node oor:name="TableAlterationServiceName" oor:op="replace">
<prop oor:name="Value" oor:type="xs:string">
<value>com.sun.star.sdb.comp.SQL2008.TableAlterService</value>
</prop>
</node>
</node>
</node>
</node>
</oor:component-data> |
Content on this page is licensed under the Public Documentation License (PDL).
|