When the IP EXECUTE is called, the DAM_HSTMT that is passed in refers to the outer table being processed. To get the DAM_HSTMT for the next inner table, the IP calls dam_getNextJoinStmt(dam_hstmt_outer).
To get the table name and to decide if it can build the rowset, the IP can then call dam_describeTable(dam_hstmt_inner).
The IP uses dam_allocRowSet to allocate a new rowset, and dam_addRowToSetto add the inner table row to the rowset.
The rowset can be an empty rowset if there are no matching inner table records. After building the rowset, the IP calls dam_addJoinRowSetto associate the rowset with the outer row. The IP must add a rowset for each outer row processed. This row is empty if the inner table does not have a matching row. In this case, the OpenAccess SDK SQL engine either returns NULL values (when performing an OUTER JOIN) or discards the outer table row (when performing an INNER JOIN).
The OpenAccess SDK SQL engine allows the IP to get more information about the join query that is being processed. When processing a join query, OpenAccess SDK SQL engine assigns process orders to each table in the FROM list to reflect the sequence in which it queries the IP for results.
The IP should call the following methods:
• dam_getInfo(DAM_INFO_JOIN_QUERY_SIZE) to get the number of tables involved in this query.
• dam_getInfo(DAM_INFO_QUERY_PROCESS_ORDER)to get the process order of the current table.
• dam_describeJoinTable to get details of each table in the query based on the process order.
• dam_describeJoinCond to obtain the columns.
For the following query,
SELECT * FROM dept, emp WHERE emp.deptno = dept.deptno
dam_getInfo(DAM_INFO_JOIN_QUERY_SIZE) returns 2. Calling dam_getInfo(DAM_INFO_QUERY_PROCESS_ORDER) the first time IP EXECUTE is called returns 0—process order of DEPT is 0 and that of emp is 1. The join condition obtained by calling dam_describeJoinCond contains the details of the joining columns.
Processing inner table rowsets
The IP should not call dam_isTargetRow. The OpenAccess SDK SQL engine checks the search conditions on the inner table when it processes the pushdown rowset of the outer table.
The IP can get the join conditions between the outer and inner tables by calling dam_describeJoinCond. For example:
SELECT … FROM dept,emp WHERE dept.dept_id = emp.dept_id AND dept.country='USA' AND emp.salary > 1000
In this query, the IP can determine that the join condition between emp and dept is on emp.dept_id and dept.dept_id.
Example
The following C sample function shows how the IP can implement join pushdown by building a rowset of emp table while building each row of the dept table. This code example checks to see if the next join table is the emp table and, if so, builds emp rows for the specified dept_id.
int build_emp_rowset(DAM_HSTMT hstmt_outer, DAM_HROW hrow_outer, int dept_id)
{
DAM_HSTMT hstmt_inner;
char sTableName[DAM_MAX_ID_LEN+1]; /* Name of the table being queried */
int iRetCode;
DAM_HROR hrow;
DAM_HROWSET hrowset;
/* the next join table */
hstmt_inner = dam_getNextJoinStmt(hstmt_outer);
if (!hstmt_inner) return DAM_SUCCESS;
/* get the table name and return if it doesn't match */
if (stricmp(sTableName, "emp")) return DAM_SUCCESS;
/* allocate a new rowset */
hrowset = dam_allocRowSet(hstmt_inner);
/* call dam_getRestrictionList() if you want to get conditions on any of */
/* the columns in emp record to reduce the number of emp rows you build */
/* add the rows to rowset */
for each emp record {
hrow = dam_allocRow(hstmt_inner);
for each column of emp record {
dam_addValToRow(hstmt_inner, hrow,….);
}
iRetCode = dam_addRowToRowSet(hrowset, hrow);
if (iRetCode != DAM_SUCCESS) return iRetCode; /* error */
}
