Procedure
1. Close all Statement objects.
2. Close all TableListener objects.
3. Close all Table objects.
4. Close all Session objects.
5. Close the data grid Connection object.
Connection
Programs begin their interactions with an ActiveSpaces data grid by first creating a Connection object.
The Connection object can then be used to retrieve grid metadata or to create Session objects.
From the Connection object, a program can create one or many Session objects.
Grid Metadata
Grid metadata is retrieved from a Connection object by calling the get grid metadata API. Each time the GridMetadata is retrieved, the information returned reflects the current table information in the data grid.
A program must destroy the GridMetadata object after it has finished using it and before making any subsequent calls to retrieve updated GridMetadata.
To learn more about grid metadata, see the section on "Metadata" in Programming with ActiveSpaces.
Table Metadata
Table information is retrieved from the GridMetadata object as a TableMetadata object. A TableMetadata object is retrieved by using the table's name.
If the application program does not know the names of the tables that have been defined in the data grid, the GridMetadata object provides a method to get an array of all table names that have been defined. This array can then be used to get a single TableMetadata object.
A column name or index name is used to get information about a particular column or index from a TableMetadata object. If the application program does not have the names of the columns or indexes of a table, the TableMetadata object provides methods to get an array of the column names or index names.
TIBCO ActiveSpaces® Concepts
A separate method to get the name of the primary index is provided by the TableMetadata object. The name of the primary index is then used to retrieve information about the columns of the primary index.
The columns of the primary index make up the primary key of the table.
The TableMetadata object and strings retrieved from it do not need to be destroyed as these objects are owned by the GridMetadata object and are destroyed when the grid metadata is destroyed.
Session
Programs use sessions to insulate data grid operations within program threads and to group operations into atomic transactions.
Sessions and Threads
It is good practice to create a separate session for each program thread that accesses the data grid.
Programs must use sessions in a thread-safe way. That is, two threads must not simultaneously access the same session. Violating this constraint can yield unpredictable results.
Sessions and Transactions
Each session can be either transacted or non-transacted. Programs determine this semantic property when creating each session.
In a transacted session, all GET, PUT, and DELETE operations occur within a transaction, which is bound to the session. The session implicitly starts the transaction. Programs explicitly call the session's commit and rollback methods. (As these methods complete, they automatically start a new transaction in the session.)
If a program operates within several open transactions simultaneously, use a separate session and thread for each transaction.
In a nontransacted session, GET, PUT, and DELETE operations are immediate: that is, when the method completes, the effect of the operation is also complete.
However, operations in a transacted session can block operations in a non-transacted session. For further explanation, see Transaction Isolation.
Only GET, PUT, and DELETE operations are affected by a transacted session, the corresponding commit, and rollback APIs. Other commands such as iterators, queries, and DDL updates do not have different behavior when running on a transacted session versus a non-transacted session.
Sessions and Defining Tables
After a session has been created, it can be used to define tables programmatically. For more information, see "Defining a Table by Using SQL DDL Commands" in the TIBCO ActiveSpaces Administration guide.
Table
Table objects represent data grid tables within an application program.
Tables and Sessions
A program opens a table object by calling a session's open table method. The program can use the table object's methods to operate on the corresponding table in the data grid.
Opening a table object does not lock the table in the data grid.
If the session is transacted, then table operations occur within the session's transaction. Within a transaction you can interact with multiple tables.
If the session is non-transacted, then table operations are not transacted.
Table Operations
Tables support the following data grid operations:
● PUT a row into the table
● GET a row of the table
● DELETE a row from the table
● Create an iterator to present the results of a table query Primary Key
Every table requires a primary key, which can consist of one or more columns. The data type of primary key columns can be long, string, or tibDateTime.
Examples of primary keys include employee number, invoice number, or MAC address.
The value of the primary key always remains unique across all the rows of the table. That is, database operations can never create two rows with the same key value; instead, they overwrite data in the existing row with that key value.
Creating Tables
Before a program can use a table or its rows for operations, the table must first be defined. A table can be defined programmatically by using a Session object or an administrator can define a table by using the ActiveSpaces administration tool. For details, see "Defining a Table" and "Defining a Table by Using SQL DDL Commands" in TIBCO ActiveSpaces Administration.
PUT
The PUT operation adds a row to a data grid table.
Before calling the put() method, your program must first create a row object and set its columns with values.
The row object must contain a value in all columns of the primary key. The value of the key is unique. If the table already contains a row with that key value, then the PUT operation replaces the existing row within the table. The PUT operation overwrites any unchanged columns in the row. The columns that are not part of the primary key can either contain data or be NULL.
GET
The GET operation retrieves a row of a data grid table.
Before calling the get() method, your program must first create a row object and set a value in all columns of the primary key. The value of the key is unique.
If the table contains a row with that key value, then the GET operation returns the contents of that row in a new row object. If the table does not contain a row with that key value, then the method returns null.
DELETE
The DELETE operation deletes a row from a data grid table.
Before calling the delete() method, your program must first create a row object and set a value in all columns of the primary key. The value of the key is unique.
If the table contains a row with that key value, then the DELETE operation deletes that row from the table.
TIBCO ActiveSpaces® Concepts
If the table does not contain a row with that key value, then the method returns without changing the table.
Iterator
A table iterator is used to iterate over all of the rows or a specific subset of the rows in the table. The create iterator operation submits a query on a data grid table and creates an iterator object to present the query results.
Supply a filter string as an argument to the create iterator operation. The filter string follows the syntax of the WHERE clause of a SQL SELECT statement excluding the WHERE keyword. All rows in the table for which the filter string evaluates to true are returned by the iterator.
An iterator object receives batches of matching rows from the data grid. The prefetch property of the iterator determines the batch size.
Properties can be set when an iterator is created thereby affecting the query behavior. For more information see Data Consistency for Queries.
The iterator object presents the program with the individual rows that match the query one at a time.
To release resources within the data grid component processes, the program must close the iterator object and close each row object retrieved by using the iterator.
An implicit timeout limits the lifespan of iterator objects. Program calls that access an iterator after that timeout elapses return an error.
Avoid queries that result in full table scans, which can be resource-intensive and time-consuming.
Table Listener
A table listener is used to monitor events corresponding to changes in a table. A table listener is created from a specific table.
When you use a table listener, you can either monitor the contents of a specified table or a subset of rows in a specific table. For example, by using a filter, your application can track a table containing customer data and more specifically, can track the activity in a particular region to know when new customers are added or deleted, or when customers move to another region.
Events
An event indicates that the data in a table has changed.
An event can be of the following types:
● PUT: Indicates that new data has been added to the table.
PUT events have a current value, which is a copy of the row that was added to the table. If the PUT operation replaces an existing row, the event additionally has a previous value, which is a copy of the row before the PUT operation.
● DELETE: Indicates that a row has been deleted from the table. DELETE events have a previous value, which is a copy of the row before the DELETE operation.
● ERROR: Indicates that something has happened in the system that indicates that the flow of events are disrupted. ERROR events have an error code and an error description. The application must destroy the table listener. Depending on the error code, it might or might not make sense for the application to re-create the table listener. The ActiveSpaces API documentation provides more details on the specific error codes that are possible.
● EXPIRED: Indicates that a row has expired. When rows are removed from a table due to expiration, table listeners on the table receive EXPIRED events when the expired rows match the table listeners’
filters.
When creating a table listener, you must specify the table that is the source of the events of interest and a callback function that is invoked when events are delivered to the application. The callback function
executes in a thread that is internal to the ActiveSpaces client library and is expected to complete in a timely fashion. The client library retains ownership of the events and the rows they contain so any data that is required outside of the callback must be copied and managed by the application itself.
Filtering Events
When the table listener is being created you can optionally specify a filter string to further narrow the scope of events received.
The filter string specifies the criteria that events must match in order for them to be delivered to the table listener. The filter is equivalent to the WHERE clause of a SQL SELECT statement, excluding the WHERE keyword, and is applied to both the current and previous values for the row that has changed.
For example, in a table containing customer data with a column called state, the filter state = “CA”
limits the events delivered to only those involving customers in California.
Listening to Specific Event Types
When the table listener is being created, you can optionally provide a Properties object containing a list of event types that restrict the listener to only receiving events of those types. This feature is commonly used to listen to expired events, but to ignore any PUT or DELETE events that occur on the table.
The property name is TIBDG_LISTENER_PROPERTY_STRING_EVENT_TYPE_LIST and the property value must be a comma separated list of string event types. The valid choices are any combination of "put",
"delete", and "expired". For example, to listen to only expired events, you would use a property value of "expired". To listen to both expired events and delete events, you would use a property value of "delete,expired".
Statement
Statement objects are used to run SQL commands on the data grid. Queries (SELECT statements) and data manipulation language (DML) SQL commands can be run by using Statement objects. Statement objects are created by invoking the createStatement() method on the Session object.
A Statement object is created for each individual SQL command. A Statement can be run multiple times and must be closed when it is no longer needed.
You can either create a query by using the SELECT statement or update rows by using an INSERT or an INSERT OR REPLACE statement. The INSERT statement is supported for both, transacted and non-transacted sessions. For details about INSERT statement, see The SQL INSERT Statement. For information about INSERT OR REPLACE statement, see The INSERT OR REPLACE Statement.
Properties
Statement properties affect the behavior of the statement. Statement properties can be set when a statement is first created or when a Statement is executed.
Examples of the properties that can be set are:
● Query prefetch - Number of rows to return in a batch when a query is first executed and each time more rows are requested while iterating through the results.
● Query fetch timeout - Number of seconds to wait for a batch of rows to be returned before the method waiting for the rows time out.
For specific information about Statement properties, see each language API's documentation for the following tasks:
● Creating a Statement from a Session object
● Executing a query by using a Statement object
● Executing a DML command by using a Statement object
TIBCO ActiveSpaces® Concepts
Parameters
Parameters serve as placeholders for values in a SQL command. Parameters are used to separate the data of a SQL command from the command itself. This can be useful when the same command can be run multiple times by just varying the data of the command thereby increasing performance of the data grid. Parameters can be used to prevent SQL injection attacks in queries.
Parameters in a SQL command are specified by using '?' (question mark). For SELECT statements, parameters are supported for the values of comparisons in WHERE clauses. For INSERT statements, parameters are supported for column values.
The Statement interface provides methods for setting the values of any parameters used in a SQL command. Separate methods for setting parameter values are provided for each data type supported by ActiveSpaces. The setNull() method is provided to specify that a parameter's value must be empty (SQL NULL). All parameter values must be specified before running the statement or an error is returned.
Parameters are numbered starting with 1.
Executing Statements
The Statement interface provides two methods for executing the statement. The executeQuery() method is used to execute statements, which have been created by using a SELECT command. The
executeQuery() method returns a ResultSet object that contains the resulting rows of a query.
The executeUpdate() method is used to execute statements that have been created by using a DML command. The executeUpdate() method returns the number of rows that were successfully processed.
If the wrong method is used to execute a statement, an error is returned. For information about the current DML commands supported, see Modifying Data in a Table.
ResultSet
A ResultSet contains the set of rows that make up the result of a query. A ResultSet object is returned when executeQuery() is invoked on a Statement created for a SELECT statement. The ResultSet object must be closed when it is no longer needed. A ResultSet object is returned even if no rows were found for the query.
The ResultSet object contains methods that allow you to iterate over the rows of the query result. A ResultSet object can be iterated over only once. The hasNext() method is used to check if there is a row that can be retrieved. The next() method is used to retrieve the next row object of the result.
Row Objects
A Row object retrieved from ResultSet contains the columns specified in the select list of a query. Each Row object retrieved from ResultSet must be closed when it is no longer needed. A Row object contains the methods to find out the data type of each of its columns, whether a column has a value, and the methods to retrieve a column's value by its data type.
The label of a column in the select list is used to access the data for each column. The columns of a row are accessed by using the label specified for the column in the select list using ActiveSpaces. For example,
SELECT col1 AS myname FROM table1
where myname is used as the column label.
If a label was not specified for a column in the select list, the column's name from the table is used as the label. For example,
SELECT col1 FROM table1
where col1 is used as the column label.
If a label was not specified and the column of the select list is an expression, the expression string is used as the label. For example,
SELECT col1, date('now') FROM table1 WHERE col1 <= 10
where the expression string, date('now'), is used as the column label.
When a label is specified for a column, or the column is an expression, the label or expression must be used exactly as it was specified in the original query string and is case-sensitive. If a label was not specified for a column and the column is from the table, the label is not case-sensitive.
ResultSet MetaData
ResultSetMetadata can be used to discover information about the columns that comprise the rows of a query result. The ResultSetMetadata for a SELECT statement can be retrieved by invoking the
getResultSetMetadata() method of the Statement object.
The ResultSetMetadata information includes the number of columns in a result row, each column's data type, the label given to the column, the name of the column from the table, and the name of the table for each column.
If a label was not specified for a column, the name of the column from the table is used as the label. If a label was not specified for a column and the column is not from a table, but is an expression that is calculated as part of the result, the entire expression string is used as the label. It is always safest to access a column of a ResultSet by its label as a ResultSet column always has a label but might not necessarily have a name from a table.