Usage

From Apache OpenOffice Wiki
Jump to: navigation, search



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] 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:

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>, <code>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).
Personal tools
In other languages