Querying a Data Grid Table
Application programs have the following options to query tables in a data grid.
- Table iterator
A table iterator is used when you have created a table object and need to iterate over all of the rows or a specific subset of the rows in the table.
You can create an iterator to query the contents of the table and then iterate over the query results. The contents of the query results for the iterator are controlled by the use of a filter string when the iterator is first created. Using a NULL filter string when creating the iterator returns all of the rows of the table.
To restrict the query results to a subset of the rows of a table, provide a non-NULL filter string which contains a filter expression as described in Query Language.
For more information, see Table Iterator.
- Session statement
A statement is created from a session object and is not tied to any one particular table. A session statement is created using a SQL SELECT string and the table for the query is determined by parsing the SELECT string. The SQL SELECT string supported has the form:
SELECT * FROM table_name WHERE filter
The syntax for a filter string is the same as for table iterators with the exception that parameter markers using '?' are supported. Parameter markers allow you to optimize the scenario where you want to run the same query several times with different values in a filter expression. For example,SELECT * FROM mytable WHERE key > ?
Before a query is run for the session statement, you must provide values for all of the parameter markers in the SQL SELECT string by calling the appropriate tibdgStatement_SetXXX method for each parameter value depending on the data type of the value.
The parameter number is required when calling the SetXXX method with the first parameter in the statement being numbered 1.
For more information, see Session Statement.
The advantages of using a session statement over a table iterator:
- The same query can be run multiple times using a single statement object.
- You can use parameter markers to vary the results of your query each time it is run.
- You do not have to create a table object before you can query a table.
- You can specify a subset of the columns of a table to be returned for the query results.
- To aid with data security, you can decouple the query from the data values used in the query.
Statements and result sets must be created on a non-transacted session because they do not interact with the commit and rollback APIs on a transacted session.