Documentation/DevGuide/Database/Usage

Apache OpenOffice Developer's Guide

Database Access

< Previous Section

Next Section >

• Architecture
• Example: Querying the Bibliography Database

• Data Sources in Apache OpenOffice API

• DataSources

• The DataSource Service
• Queries

• Adding and Editing Predefined Queries
• Runtime Settings For Predefined Queries
• The SingleSelectQueryComposer
• The SQLQueryComposer

• Forms and Reports
• Document Links
• Tables and Columns

• Connections

• Connecting Through a DataSource
• Connecting Using the DriverManager and a Database URL
• Connecting Through a Specific Driver
• Driver Specifics

• The SDBC Driver for JDBC
• The SDBC Driver for ODBC
• The SDBC Driver for Adabas D
• The SDBC Driver for ADO
• The SDBC Driver for dBase
• The SDBC Driver for Flat File Formats
• The SDBC Driver for Apache OpenOffice Calc Files
• The SDBC Driver for address books
• The SDBC Driver for embedded HSQL databases

• Connection Pooling
• Piggyback Connections

• Manipulating the Data

• The RowSet Service

• Usage
• Events and Other Notifications
• Clones of the RowSet Service

• Statements

• Creating Statements
• Inserting and Updating Data
• Getting Data from a Table

• Result Sets

• Retrieving Values from Result Sets
• Moving the Result Set Cursor
• Using the getXXX Methods
• Scrollable Result Sets
• Modifiable Result Sets
• Update
• Insert
• Delete
• Seeing Changes in Result Sets

• ResultSetMetaData
• Using Prepared Statements
• PreparedStatement From DataSource Queries

• Database Design

• Using DDL to Change the Database Design
• Using SDBCX to Access the Database Design

• Catalog Service
• Table Service
• Column Service
• Index Service
• Key Service
• View Service
• Group Service
• User Service
• The Descriptor Pattern
• Adding an Index
• Creating a User
• Adding a Group

• Using DBMS Features

• Stored Procedures

• Writing Database Drivers

• SDBC Driver
• Driver Service
• Connection Service
• XDatabaseMetaData Interface
• Statements
• Support Scalar Functions

• Open Group CLI Numeric Functions
• Open Group CLI String Functions
• Open Group CLI Time and Date Functions
• Open Group CLI System Functions
• Open Group CLI Conversion Functions

• Handling Unsupported Functionality
• Extending Database Drivers

< Previous Page

Next Page >

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 DataSourceName is not a URL, then the RowSet uses the name to get the DataSource from the DatabaseContext to 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.

The difference between the two properties is that in the first case the RowSet owns the connection. The RowSet disposes the connection when it is disposed. In the second case, the RowSet only uses the connection. The user of a RowSet is responsible for the disposition of the connection. For a simple RowSet, 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 ActiveConnection of the form into the ActiveConnection property 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, use executeWithCompletion(), see Connecting Through a DataSource. If interactive logon is not feasible for your application, the properties User and Password can be used to connect to a database that requires logon.

Once the method for how RowSet creates it connections has been determined, the properties Command and CommandType have to be set. The CommandType can be TABLE, QUERY or COMMAND where the Command can be a table or query name, or an SQL command.

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

Properties of 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: TABLE: indicates the command contains a table name that results in a command like "select * from tablename". QUERY: indicates the command contains a name of a query component that contains a certain statement. COMMAND: indicates the command is an SQL-Statement.
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] boolean - 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:

Properties of com.sun.star.sdbc.RowSet
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>, DataType::VARBINARY, DataType::LONGVARBINARY, DataType::CHAR, DataType::VARCHAR, and DataType::LONGVARCHAR columns. If the limit is exceeded, the excess data is silently discarded. There is no limitation if set to zero.
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 = (XRowSet)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 = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
      xProp.setPropertyValue("DataSourceName", "Bibliography");
      xProp.setPropertyValue("Command", "biblio");
      xProp.setPropertyValue("CommandType", new Integer(com.sun.star.sdb.CommandType.TABLE));
      xRowRes.execute();
      System.out.println("RowSet executed!");
      XComponent xComp = (XComponent)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 =
          (XRowSet)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 = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xRowRes);
      xProp.setPropertyValue("DataSourceName", "Bibliography");
      xProp.setPropertyValue("Command", "biblio");
      xProp.setPropertyValue("CommandType", new Integer(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 = (XComponent)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 = (XRowSet)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 = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,xRowRes);
      xProp.setPropertyValue("DataSourceName","Bibliography");
      xProp.setPropertyValue("Command","biblio");
      xProp.setPropertyValue("CommandType",new Integer(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 = (XResultSet)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 = (XComponent)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.

Content on this page is licensed under the Public Documentation License (PDL).