Index2

void Join(Database& d)
{
     Table tb = d.GetTable("products");
     Table tc = d.GetTable("orders");
     Table td = d.GetTable("product_types")
     Select s(tb);
      s += tc;
      s += td;                      // create join of three tables

      s << tb["prod_desc"] << tb["prod_price"] << tc["prod_id"] // selected columns
        << tc["ord_qty"] << td["type_desc"] << td["type_id"];

      s >> tb["prod_id"] == tc["prod_id"];                                   // join condition
      s >> and_ >> td["type_id"] == tb["prod_type"];                   // join condition
      s >> and_ >> td["type_id] != 3;                                         // query condition

       while (s.Row())
              cout << s;           // display all selected columns in line format
}

void ObjectTable(Database& d)
{
    Table t = d.GetTable("customers");
    Object o = t.GetObject("address");
    Select s(t);     // select based on table
    s += o;          // add object columns to select

    while(s.Row())
       cout << s; // show table and object data
}

void NestedTable(Database& d)
{
     int evt;
     TRowid r;                     // rowid object
     Table t = d.GetTable("schedules");
     Object o = t.GetObject("evt_lineup");
     Select s(t);                     // select based on table
     s << t[r] << evt;             // columns to be selected
    Select so(o);                 // select based on object
     so >> o[r] == ":";           // set placeholder

     while(s.Row()) {
         s << r << evt;           // load rowid, evt
         so >> r;                   // fill placeholder
         while (so.Row())
               cout << so;        // show object data
      }
}

void UnionMinusIntersect(Database& d)
{
     Table tb = d.GetTable("products");
     Table tc = d.GetTable("deleted_products");
     Table td = d.GetTable("new_products");

     Select s1(tb);
     s1 << tb["prod_id"] << tb["prod_desc"];

    Select s2(tc);
     s2 << tc["prod_id"] << tc["prod_desc"];

     Select s3(td);
     s3 << td["prod_id"] << td["prod_desc"];

    Union u(s1, s2);
     u += s3;      // union of 3 selects
     while (u.Row()) cout <<u;

     Intersect i(s1, s2);
     while (i.Row()) cout << i;

     Minus m(s1, s2);
     while (m.Row()) cout << m;
}

int main()
{   // use server connection
     try {
        Connect c("data1.localdomain");
        Database db = c.OpenDatabase("demo","demo");

        Join(db);
        ObjectTable(db);
        NestedTable(db);
        UnionMinusIntersect(db);
        QueryLob(db);
        StoredProcedure(db);
        UserSql(db);

        db.Close();
     }
     catch (OException& e) {
          cerr << e.Show() << endl;
     }
     catch ( ... ) {
          cerr << "Error ..." << endl;
     }
     return 0;                 prev page / home page
}

#include <orac++.h>

Example of a C++ program to query joined tables, nested table, table with embedded object, and process
union, intersect and minus statements. Outer joins and non-equi joins are also supported. Columns in nested tables and tables with embedded objects can be accessed by entering column name in appropriate object operator << and >>. Example of accessing CLOB column, executing stored procedure and using free style sql is also included. In the program below a server connection is used (requires running listener on client )

void QueryLob(Database& d)
{
      char buff[80];
      TInt i;
      TClob tc("clob_data");
      tc.SetFetchSize(80); // set 'chunk' to fetch

      Table t = d.GetTable("lob_data");
      Select s(t);
      s << t["id"] << t[tc]; // selected columns
      while (s.Row()) {
          s << i << tc;      // get LOB locator
           while (tc.Next()) {// check if more data in LOB
                tc << buff;      // load buff from LOB
                cout << buff << endl;
           }
       }
}

void StoredProcedure(Database& d)
{
      TInt pid;
      TStr n(30);
      pid = 100001;    // set prod_id
      Procedure p = d.GetProcedure("p_get_desc");
      p >> pid >> n; // load all variables
      p.Execute();
      p << pid << n; // and reload from procedure
      cout << " id " << pid << " name " << n << endl; }

void UserSql(Database& d)
{
      TStr id(10), name(30), price(10);
      Sql st = d.GetSql("select * from products");
      st += "where prod_id = :1";
      st << id << name << price; // define output
      st >> 100001;                   // load bind data
      while (st.Execute()) {
          st << id << name << price; // reload
          cout << id << ":" << name
             << ":" << price << endl;
      }
}