Creating the Definition in C++

All of the steps described in the previous section can also be carried out by coding in C++. This following example shows how this can be done:

  • Create the IliSQLTable object

IlvDisplay* display;

...

IliSQLTable* sqlTbl = new IliSQLTable(display);

sqlTbl->lock();

  • Specify the database tables

IliSQLTableRef refEMP(“EMP”, “SCOTT”);

IliSQLTableRef refDEPT(“DEPT”, “SCOTT”);

IlInt tblEMP = sqlTbl->addTable(refEMP);

IlInt tblDEPT = sqlTbl->addTable(refDEPT);

  • Specify the joins

sqlTbl->addJoin(tblEMP, “DEPTNO”, tblDEPT, “ID”);

  • Specify the columns

IlInt cID = sqlTbl->appendColumn(“ID”, IliIntegerType);

sqlTbl->setColumnPartOfKey(cID, IlvTrue);

sqlTbl->setColumnSQLText(cID, “ID”);

sqlTbl->setColumnTable(cID, tblEMP);

 

IlInt cNAME = sqlTbl->appendColumn(“NAME”, IliStringType);

sqlTbl->setColumnSQLText(cNAME, “NAME”);

sqlTbl->setColumnTable(cNAME, tblEMP);

 

IlInt cDEPT = sqlTbl->appendColumn(“DEPT”, IliStringType);

sqlTbl->setColumnSQLText(cDEPT, “NAME”);

sqlTbl->setColumnTable(cDEPT, tblDEPT);

A computed column could be defined in the following way (note that the following code excerpt is not part of the example).

IlInt cTOTAL = sqlTbl->appendColumn(“TOTAL”, IliIntegerType);

sqlTbl->setColumnSQLText(cTOTAL, “PRICE * QTY”);

  • Specify the criteria

IlInt where = 0;

sqlTbl->insertConjunct(where,IlvTrue);

sqlTbl->setColumnPredicat(cNAME, where, “<> ‘Smith’”, IlvTrue);

  • Specify the sort

sqlTbl->setColumnOrderBy(cNAME, IliSQLAscending);

  • Specify the updatable table

sqlTbl->setTableUpdatable(tblEMP, IlTrue);

  • Generate the SQL SELECT statement

sqlTbl->makeQuery();

At this point, the IliSQLTable object is defined and ready for use. Calling the getQuery member function generates the following SQL statement:

SELECT EMP.ID, EMP.NAME, DEPT.NAME

FROM SCOTT.EMP, SCOTT.DEPT

WHERE EMP.DEPTNO = DEPT.ID

AND EMP.NAME <> ‘Smith’

ORDER BY 2