Writing Object Retrieval Oracle Database Tips by Donald Burleson

Writing a relational interface to a C++ application 

When we look at writing a relational interface to a C++ application we need to address the method to use  for getting objects from the relational database.  Essentially, the retrieval process is very straightforward.  It includes: 

            1. See if the object is in memory with a physical address.
            2. If the object is not in memory retrieve the object with a logical pointer.

The logical pointer was created for the purpose of establishing a primary key for the table.  The logical pointer was created when the object was stored into the relational database, and it will be used to navigate between orders.

In our example, we have a method called ordersForCustomers that accepts a "customer" object address and displays the orders for that customer.  The logical pointer is called orderKey and it is the primary key for our order table.  The code is:

 customer::ordersForCustomer() {  // list all orders for a customer 

       cout << "\n\nOrder summary for customer " << custName << " \n";
       int i;
       order * x;
       cout <<  "\n   Order = " << (x = orderPtr)->orderNum;
       for(i=0;i<orderCount-1;i++) {
          x = x->nextOrder;
          cout <<  "\n   Order = " << x->orderNum;
       };
       return 0;
    };

Since this is a method of the customer class we already have "currency" on the customer object and we assume that the "customer" object is already in memory.  We can see from the code example that the first "order" object is retrieved by checking the orderPtr in the customer object.  From the first order, subsequent orders are retrieved by chasing the nextOrder pointer within the order objects.

If we were to write this retrieval IN SQL, the query might look like this:

SELECT        orderNum
FROM   order
WHERE order.custName = "IBM";

Of course, this retrieval would return many rows from the order table, and if this query were embedded into a C program, a cursor would need to be declared to fetch each row, one at a time.

EXEC SQL declare c1  cursor for
SELECT orderNum
FROM order o
WHERE
       order.custName = :custname; 

EXEC SQL open c1; 

EXEC SQL fetch c1 into :orddate; 

while (sqlca.sqlcode == 0){

       EXEC SQL fetch c1 into :ordnum;
} 

EXEC SQL close c1;

We can now look at how these two retrieval methods can be combined into a single C++ routine:

 customer::ordersForCustomer() {  // list all orders for a customer 

       cout << "\n\nOrder summary for customer " << custName << " \n";
       int i;
       order * x; 

       if (orderPtr == NULL)      getOrder();  // if order not in memory, fetch RDBMS
       else  
            cout <<  "\n   Order = " << (x = orderPtr)->orderNum;  // get via addr 

       for(i=0;i<orderCount-1;i++) {
       if (nextOrder == NULL) getOrder();  // get object from RDBMS
            else {                                                        // if already in-memory,
          x = x->nextOrder;                   // chase physical address
          cout <<  "\n   Order = " << x->orderNum;
           }
       };
       return 0;
    };

getOrder() {
       // fetch the desired object from the RDBMS...
       EXEC SQL SELECT * from order into :orderstruct
       where orderKey = order.orderKey; 

       //call the object constructor...
       order o123(IBM,:orderNum);
       // Note that the ORDER constructor establishes the in-memory
       // pointers to the customer object and related order objects
}

The basic premise here is that the in-memory pointers are only established after the object has been retrieved from the RDBMS, and the in-memory pointers have been created by the object constructor.

Note that we first check to see if the object is in memory with the NULL test.  If the in memory pointer is NULL, we then call the getOrder() function to retrieve the object from the relational database, calling the object constructor to instantiate the object to the run-time system.  The constructor will make the object exist in memory, and establish all necessary pointers to owner objects (i.e. those that already exist in memory).  Other pointers to objects that have not yet been retrieved from the relational database will have NULL pointers. 

In summary, the paradigms of object-oriented programming and relational databases technology are not as far apart as they appear.  If a C++ application is designed with well isolated I/O functions and linked-list navigation, it can be very simple to back-end the C++ application to utilize a relational database.

Appendix: original C++ application

#include <iostream.h>
#include <string.h>
#include <math.h>
#include <stdlib.h>
#include <time.h>

class customer;
class order;
class orderline;
class item;
class component;

class customer { 

  public:
    char * custName;
    order ** orderList;    // pointers to orders for this customer
    int orderCount;
    customer(char * p) {     // constructor for customer
       cout << "creating customer " << p << "\n";
       int l;
       l = strlen(p);   //  get length of string

       custName = new char[l+1];        // get space for customer name
       if (!custName) { cout << "allocation error!\n"; exit(1);} 

       strcpy(custName,p);   // copy in the customer name
       orderCount = 0;
    }; 

    ordersForCustomer() {  // list all orders for a customer 

       cout << "\n\nOrder summary for customer " << custName << " \n";
       int i;
       for(i=0;i<orderCount;i++) {
          cout <<  "\n   Order = " << orderList[i]->orderNum;
       };
       return 0;
    };
}; 

class order { 

   public:
   customer * custOwner;   // pointer to customer placing this order
   orderline ** itemList; // pointers to line items in the order
   int orderNum, itemCount; 

   order(customer &cust, int ordnum)   {  // order constructor
       cout << "\nCreating order " << ordnum << "\n";
       itemCount = 0;
       orderNum = ordnum; // set the order number
       cust.orderList[cust.orderCount++] = this; // set cust -> order
       custOwner = &cust;  // set order-> customer pointer
   };
   itemsInOrder()  { //   {  // list all items for an order
       // get the items and count the item names...
       cout << "\n\n Item list for order number " << orderNum << "\n";
       int i, quant;
       float pr, totCost;
       for(i=0;i<itemCount;i++) {
           // get the orderline object ...
           item * temp = itemList[i]->itemOwner;
           // get the item object ...
           pr = temp->itemPrice;
           // save the quantity ordered
           quant = itemList[i]->quantity;
           totCost = pr * quant;
           cout << "   Item " << temp->itemName;
           cout << " cost "   << pr;
           cout << " quant "  << quant;
           cout << " total "  << totCost << "\n";
       };
       return 0;
   };

}; 

class item { 

   public:
   orderline ** orderList; // pointers to orderlines
   component ** hasParts;  // pointers to has-parts
   component ** isAPart;   // pointers to is-a-part
   char * itemName;
   int inventoryLevel, price, orderCount, hasPartCount, isAPartCount;
   float itemPrice; 

   item(char * p, int quantityReceived, float price ) { // item constructor

       cout << "\nCreating item " << p << "\n";
       orderCount=0;
       itemPrice = price; // set item price;
       inventoryLevel = quantityReceived;  // set quantity
       int l;
       l = strlen(p);                      // get length of string
 
       itemName = new char[l+1];           // get space for name
       if (!itemName) { cout << "allocation error!\n"; exit(1);}
       strcpy(itemName,p);                 // copy in item name
 
   };
  };
 
class orderline {
 
   public:
   order * orderOwner; // pointer to order
   item  * itemOwner;  // pointer to item
   int quantity;
   orderline(order &ord, item &it, int qty) {        // orderline constructor
 
       cout << "     creating orderline\n";
       ord.itemList[ord.itemCount++] = this; // set cust -> order
       it.orderList[it.orderCount++] = this;
       orderOwner = &ord;                        // set pointer to order
       itemOwner  = &it;                         // set pointer to item
       quantity = qty;                           // set quantity ordered
       };
};
 
class component {
 
   public:
   item * hasAPart;  // pointer to owner item
   item * isAPart;   // pointer to owner item
   int quantity;
   component(item &has, item  &isA, int quant) {  // BOM constructor
       has.hasParts[has.hasPartCount++] = this;  // set has-part pointer
       isA.isAPart[isA.isAPartCount++]  = this;  // set is-a-part
       quantity = quant;                         // set quantity
   }
};
 
main() {
 
customer IBM("IBM");
customer ATnT("ATnT");
 
item pen("pen",300,1.95);
item pencil("pencil",400,1.30);
item pad("pad",100,2.95);
 
order o123(IBM,123);
   orderline i1(o123,pen,3);
   orderline i2(o123,pencil,4);
 
order o456(ATnT,456);
   orderline i3(o456,pen,6);
   orderline i4(o456,pencil,16);
   orderline i5(o456,pad,12); 

order o789(ATnT,789);
   orderline i6(o789,pencil,50);
   orderline i7(o789,pad,10);
   orderline i8(o789,pen,2);
 
IBM.ordersForCustomer();
 
ATnT.ordersForCustomer();
 
o123.itemsInOrder();
 
o456.itemsInOrder();
 
o789.itemsInOrder();
 
//ATnT.ordersForCustomer();
//IBM.ordersForCustomer();

return 0;
 
}
 

---