Accessors for class IliSQLTable

Properties

Methods

Description

The IliSQLTable class defines a two-tier table that manages rows located in a remote relational database management system (DBMS).

Query

An SQL table object is defined both in terms of its schema (inherited from class IliSchema) and in terms of the SQL SELECT statement that it must submit to the database system to obtain the rows it manages. The SQL SELECT statement is contained in the query property.
It is necessary that the SQL SELECT statement return a result set having the same number of columns as that of the SQL table object and the corresponding columns of the data type must match.
In addition to the query property, the SQL table object has a queryConjunct property. This property optionally contains a conditional expression (formulated using SQL syntax) that, if present, will be combined with the WHERE clause of the query using an AND logical operator just before the query is sent to the database.
The queryConjunct property can be used to further restrict the set of rows computed at select time.

SQL Session

The SQL table object also has an SQL session object through which it communicates with the database server. When you design an SQL data source (and its associated SQL table) in Rogue Wave Views Studio, you are given two choices:
Using an application-defined session allows you to share sessions among SQL tables. This is useful for two reasons:

Row Management

The methods that let you manage rows through an SQL table object are described in class IliTable. The most important of these methods are:
When applied to an instance of the IliSQLTable class, the select method first clears the rows in local row cache and then submits the SQL SELECT statement to the remote database server. Then, if successful, it either fetches all rows that resulted from evaluation of the SQL SELECT statement immediately or it keeps an SQL cursor open on the result-set and stays prepared to fetch rows when they are later needed. The fetchPolicy property specifies whether all rows must be fetched at this time or if they must be fetched later.
The local row cache can be managed independently of remote rows using the following methods:
When you sort the rows of an SQL table object as in the following code:
     table.GRADE.sort();

the rows in the local row cache are sorted but the corresponding remote rows located in a database system are left unchanged.
Similarly, the moveRow method moves rows in the local row cache only.
Note that it is also possible to obtain a sorted order by using the SQL ORDER BY clause in the SQL SELECT statement of the SQL table object. In this case, the sorting is done remotely by the database system instead of locally.

Row Locking

The refreshRow method allows you to re-fetch a given row. This is done by issuing an SQL SELECT statement that retrieves the row being refreshed. The WHERE clause of the SQL SELECT statement is sensitive to the value of the concurrencyControl property.
The refreshAndLockRow method re-fetches a given row. In addition the FOR UPDATE SQL clause is used in the SQL statement to retrieve the row so that its underlying database row(s) is(are) locked in the database server.
Note that some database systems may impose restrictions on which SQL SELECT statements can use the FOR UPDATE clause, particularly in case where there are more than tables in the FROM section. These restrictions apply as well to the refreshAndLockRow method.
For SYBASE, the holdlock keyword is used instead of the FOR UPDATE clause.

Asynchronous Mode

If the SQL session of an SQL table is in asynchronous mode, the SQL table will operate in asynchronous mode.

TypeNameDescriptionNotes
StringaugmentedQueryContains the SQL SELECT statement that is the combination of the query and the queryConjunct properties.read-only
BooleanautoCommitContains true if the SQL table is in auto-commit mode. In this mode, the SQL table commits the transaction immediately after any INSERT, UPDATE,or DELETE operations. When auto-commit is disabled, the user must commit the transaction explicitly when appropriate. Initially, this property is set to true.
Note that auto-commit must be disabled for the fetch policy to be "as needed".
BooleanautoRefreshContains true if the SQL table is in auto-refresh mode. In this mode, any INSERT or UPDATE operation is immediately followed by a call to the refreshRow method. This is sometimes useful when the database contains triggers that can alter inserted or modified rows. Initially, this property is set to false.
IliTablecacheContains null if the IliSQLTable object manages its local row cache itself. Otherwise, this property contains another IliTable object (it may be an IliMemoryTable object, for instance) that should have the same number and type of columns as this IliSQLTable object. All local row cache management calls are redirected to the cache. Initially, this property is null.
BooleanconcurrencyControlContains true if concurrency control is enabled for the SQL table. When concurrency control is enabled, the updating and deletion of rows will succeed only if the rows concerned have not changed in the database since the last time they were fetched and stored in the local row cache. In other words, concurrency control obliges the SQL table to protect, after the fact, the work carried out through it from the work of other users on the database. Initially, this property is set to false.
For example, if you have an EMP(ID, NAME, SALARY) database table where ID is the primary key of the table. You also have an SQL table object that selects all columns from this table and you can edit the values through a table gadget. If you change the salary of an existing employee, the following can occur:
  • If the concurrencyControl property is false, the SQL table will synthesize the following SQL UPDATE statement: 

         UPDATE EMP
        SET SALARY = :newSalary
      WHERE ID     = :fetchedId

  • But, if the concurrencyControl property is true, the SQL table will synthesize the following SQL UPDATE statement: 

         UPDATE EMP
        SET SALARY = :newSalary
      WHERE ID     = :fetchedId
        AND NAME   = :fetchedName
        AND SALARY = :fetchedSalary

The parameters fetchedId, fetchedName, and fetchedSalary take the values that were initially obtained when the row was fetched.
As you can see, the second UPDATE statement above would fail if the salary of the employee has changed in the database since the row was fetched.
This technique is sometimes called "optimistic concurrency control". It is "optimistic" because it defers the locking of rows in the database until the update takes place. The advantage of this technique is that it maximizes the responsiveness of the database server and the degree of concurrency since it minimizes the number of row locks. The drawback is that the update can fail because of concurrent updates.
Another option is to set the concurrencyControl property to false and to use instead the refreshAndLockRow method to acquire a lock on the row in the database. This technique is known as "pessimistic concurrency control" because it acquires locks much earlier and the locks remain in place a longer period of time if the end user has to type the updates after the lock was obtained but before the update takes place.
The following diagram depicts the difference between concurrency control techniques.

IntcursorBufferedRowsCountContains the number of buffered rows. The value of this property is assigned to the bufferedRowsCount of any SQL cursor used to select and fetch the result set of the SQL table object. (See IliSQLCursor::bufferedRowsCount for more information.) Note that this property bears no relationship with the local row cache of the SQL table. It refers to a lower level buffering mechanism.
IliFetchPolicyfetchPolicyContains the fetch policy. Its string value can be either IliFP_AsNeeded or IliFP_Immediate.
A value of IliFP_AsNeeded means that the SQL table object only fetches rows from the database when necessary. A value of IliFP_Immediate means that all rows are fetched at select time. Initially, this property is set to IliFP_AsNeeded.
Note that if autoCommit is set to true, the fetch policy will be immediate whatever the value of the fetchPolicy property. This is because when you commit an SQL session all SQL cursors lose their context. If the fetch policy was "as needed", you would no longer be able to fetch the remaining rows. As a consequence, if you want to use an "as needed" fetch policy, you need to set both the fetchPolicy to IliFP_AsNeeded and autoCommit to false.
In addition, remember that as soon that you commit the SQL session of the SQL table either explicitly (by calling the commit method on the SQL session) or implicitly (for example, if the SQL session is shared among many SQL tables and at least one of these SQL tables has autoCommit set to true), any rows to be fetched will not be fetched because at commit time the SQL sessions lose all fetch context.
BooleaninsertingNullsContains true if the SQL table null columns are included in the SQL INSERT statements that are used to insert rows. When null columns are included, any default values defined in the database will not be taken into account since a null is explicitly specified. Initially, this property is set to true.
For example, you have an EMP(ID, NAME, SALARY) database table where SALARY has a default value in the database. If you insert a row in this table without specifying the salary, the following can occur:
  • If the insertingNulls property is false, the SQL table will synthesize the following SQL INSERT statement: 

         INSERT INTO EMP(ID, NAME)
              VALUES(:id, :name)

  • But, if the insertingNulls property is true, the SQL table will synthesize the following SQL INSERT statement: 

         INSERT INTO EMP(ID, NAME, SALARY)
              VALUES(:id, :name, :salary)
BooleannoWaitOnLockRowThis property is only meaningful when the SQL session connects to an Oracle database server. In such cases, if this property is true, the SQL SELECT statements used to lock rows will include the NOWAIT clause. The effect will be that calls to refreshAndLockRow will immediately fail if the given row is locked by another session in the database, instead of waiting until the lock is released. This property is useful in implementing the "pessimistic concurrency control" policy. Initially, this property is false.
StringqueryContains the SQL SELECT statement that will be submitted to the database system the next time the select method is called. Note that the query property is combined with the queryConjunct property before it is sent to the database system.
StringqueryConjunctContains a conditional expression (formulated using SQL syntax) that, if present, will be combined with the WHERE clause of the query using an AND logical operator just before the query is sent to the database system.
IntrowsCountLimitContains the maximum number of rows that will be fetched and stored in the local row cache. When this limit is reached, no more rows will be fetched from the database and insertion through the SQL table will not be allowed. To obtain more rows, you need to increase the value of rowsCountLimit and then call the select method again. When this property is -1, the number of fetched rows is unlimited.
IliSQLSessionsqlSessionContains the SQL session object used to communicate with a relational database system.
InttableAliasCountContains the number of table aliases in the FROM clause.read-only
BooleanusingBoundVariablesContains true if the bind-vars mode of the SQL table is enabled. In this mode, any column values that need to be sent to the database are included in SQL statements in the form of bound variables instead of literally. This setting combined with dynamic-SQL mode (disabled) and insert-nulls mode (enabled), results in better performance at the database server side since SQL statements will tend to be identical. Initially, this property is set to true.
For example, you have an EMP(ID, NAME, SALARY). If you delete a row, then the following can occur:
  • If the usingBoundVariables property is false, the SQL table will synthesize the following SQL DELETE statement: 

         DELETE EMP
      WHERE ID = 427

  • But, if the usingBoundVariables property is true, the SQL table will synthesize the following SQL DELETE statement: 

         DELETE EMP
      WHERE ID = :fetchedId
BooleanusingDynamicSQLContains true if the SQL table is in dynamic-SQL mode. This mode affects the way SQL UPDATE statements are constructed when a row needs to be modified in the database. Such an UPDATE statement may set the values of all columns or it may only set those columns whose values have been changed. When dynamic-SQL mode is on, the amount of data exchanged with the database is reduced since not all column values need to be sent each time a row is updated.
Alternatively, when dynamic-SQL is disabled, the database server processing time may be reduced if the bind-vars mode is enabled. This is because the SQL UPDATE statements sent to the database are always identical and do not depend on which columns are updated or values are changed. In this way the database server can parse the SQL UPDATE only once instead of at each update. Initially, this property is set to true.
For example, you have an EMP(ID, NAME, SALARY). If you change the salary of an existing employee, the following can occur:
  • If the usingDynamicSQL property is false, the SQL table will synthesize the following SQL UPDATE statement: 

         UPDATE EMP
        SET ID     = :fetchedId,
            NAME   = :fetchedName,
            SALARY = :newSalary
      WHERE ID     = :fetchedId

  • But, if the usingDynamicSQL property is true, the SQL table will synthesize the following SQL UPDATE statement: 

         UPDATE EMP
        SET SALARY = :newSalary
      WHERE ID     = :fetchedId
BooleancancelAsyncCall()Cancels any noncompleted asynchronous call currently in progress. Returns true if successful.
BooleancontinueAsyncCall()If there is a noncompleted asynchronous call in progress, this method calls the asynchronous-enabled method that is not completed again. It returns true if the call is successful and false otherwise. The isAsyncCallCompleted method can then be called to check whether the call has completed.
IliSQLTableAliasgetTableAlias(String name)Returns the table alias named name or null if no such alias exists in the FROM clause. The name of a table alias is usually the name of the corresponding database table, but it can be different if the same database table appears more than once in the FROM clause In such cases, the table alias may have an underscore and a number prepended.
IliSQLTableAliasgetTableAliasAt(Int idx)Returns the table alias positioned at idx or null if idx < 0 or idx > = tableAliasCount.
BooleanisAsyncCallCompleted()Returns true if the last call to one of the asynchronous-enabled methods (select, fetchNext, and fetchAll) has completed. If this method returns false, it is necessary to call the asynchronous-enabled method (with the same parameter values) periodically until the isAsyncCallCompleted method returns true.
BooleanisInAsyncMode()Returns true if the SQL table is in asynchronous mode. This occurs when the SQL session of the SQL table is itself in asynchronous mode.
BooleanmakeQuery()Recomputes the SQL SELECT statement that will be submitted to the database server by the select method. Returns true if successful and false otherwise.
BooleanrefreshAndLockRow(Int rowno)Re-reads the row positioned at rowno from the remote database. In addition, a lock on the row is acquired. This method returns true if successful.
If the concurrencyControl property is set to true, this method will only succeed if the remote row has not changed since the last time it was fetched. In other words, this method is sensitive to the value of the concurrencyControl property. This contrasts with the refreshRow method which is not sensitive to the concurrencyControl property.
The technique used to lock the row is as follows. An SQL SELECT statement that retrieves the row is sent to the database server. In addition, the FOR UPDATE clause is added to the SQL statement used to retrieve the row so that its underlying database row(s) is(are) locked in the database server.
Note that some database systems may impose restrictions on which SQL SELECT statements can use the FOR UPDATE keywords, particularly in the case where there is more than one table in the FROM section. In the case of SYBASE, the holdlock keyword is used instead of FOR UPDATE.
BooleanrefreshRow(Int rowno)Re-reads the row positioned at rowno from the remote database. Returns true if successful.
This method is not sensitive to the value of the concurrencyControl property. In other words, it will attempt to re-read the remote row whether or not it has been changed by another user.