demo.cpp

一个通用的oracle OCI开发程序包

#include <iostream.h>

#ifndef sparc
#include <process.h>
#endif

#include <stdlib.h>
#include <stdio.h>

#include <math.h>
#include <time.h>
#include <memory>

#include <stdinc.h>

// For DDL see end of page

// Create demo data
void demo_create();
// Query demo data
void demo_query();

int main(int argc, char ** argv)
{
  try
  {
    for(int i = 0; i < 10; i++)
    {
      cout << "Count : " << i << endl;
      demo_create();
      demo_query();
      
    }
  }
  // All exceptions come back through CBaseException
  catch(CBaseException& exc)
  {
	  char* buff = exc.GetError();
 	  cout << "Errortext: " << buff << endl;
    long e = exc.GetErrorCode();
    cout << "Errorcode: " << e << endl;
  }
  return 0;
}

void demo_create()
{
  // Create a session with the server
  COCISession s1;
  s1.connect("ociwrap/ociwrap@wriggler",OCI_OBJECT | OCI_THREADED);

  // Start a transaction
  // N.B. All objects are alive inside a transaction, don't use
  // them outside of one. The transaction is a unit of database work.
  COCITransaction t(s1);
  t.start();

  // Create the objects in our system.
  COCIObject a(s1, COCITable(s1, "A"), COCIType(s1, "A_T"));
  COCIObject b(s1, COCITable(s1, "B"), COCIType(s1, "B_T"));
  COCIObject c(s1, COCITable(s1, "C"), COCIType(s1, "C_T"));
  COCIObject d(s1, COCITable(s1, "D"), COCIType(s1, "D_T"));
  COCIObject e(s1, COCITable(s1, "E"), COCIType(s1, "E_T"));

  // Create a nested table and append 2 elements
  // All nested tables are effectively composed of objects, 
  // therefore this method is quite suitable
  // Version 1
  COCINestedTable<COCIObject> embed(s1, COCIType(s1, "E_T_1"));
  COCIObject n1(s1, COCIType(s1, "E_T"));
  n1.set("ID",1);
  COCIObject n2(s1, COCIType(s1, "E_T"));
  n2.set("ID",2);
  embed.append(n1); 
  embed.append(n2);

  // As the nested table is made up of numbers, we can choose an 
  // alternative method to construct the nested table, 
  // i.e. construct a nested table of numbers (COCINumber)
  // Version 2
  /*COCINestedTable<COCINumber> embed(s1, COCIType(s1, "E_T_1"));
  COCINumber n1(s1);
  n1 = 1;
  COCINumber n2(s1);
  n2 = 2;
  embed.append(n1); 
  embed.append(n2);*/



  // Set up the A objects' attributes
  a.set("E",embed);
  a.set("B",b.get_ref());
  a.set("ID",10);

  // Set up the B objects' attributes
  b.set("A",a.get_ref());
  b.set("NAME","Hello World");

  // Create a date and store it in the C object
  // This date will be used in a calculation of the number of
  // days alive (DAYS_ALIVE()) function (see demo_query())
  // Set all the other attributes in C
  COCIDate dob(s1);
  dob.to_date("1-APR-1999");
  c.set("DOB",dob);
  c.set("E",embed);
  c.set("B",b.get_ref());
  c.set("ID",20);


  // D has a reference to C, simply set that using the 
  // reference obtained from C
  d.set("C",c.get_ref());

  // To perform work on a lob we need to give it to the server
  // (in so doing, it will give us the locator (synonymous with file pointer)
  // for this lob).
  // We must give it a non null attribute (otherwise when we flush and
  // refresh we will still have a null lob!!).
  d.set_attr_ind("LOB",OCI_IND_NOTNULL);
  d.flush();
  d.refresh();
  COCICLob clob(s1);
  // Get our lob from the D object after flushing and refreshing the
  // D object to and from the server
  clob = d.get("LOB");
  // Stream into the lob (effectively a data sink in this case, 
  // i.e. we can write to it.)
  clob << "Hello " << "World";

  e.set("ID",30);

  t.commit();

}

void demo_query()
{

  // Create a session with the server
  COCISession s2;
  s2.connect("ociwrap/ociwrap@wriggler",OCI_OBJECT | OCI_THREADED);

  // Create a transaction.
  COCITransaction t(s2);
  t.start();


  // Create a statement
  COCIStatement stmt(s2);
  // Get a reference to the A object
  // i.e. after executing (assuming a reference exists)
  // r->A  (i.e. r (the reference) points to an A object)
  // The philosophy is then to dereference this reference in order 
  // to retrieve the actual A object (i.e. *r = A). In so doing
  // the A object will be brought from the server into memory.
  stmt = "select ref(r) from a r";
  COCIRef a_ref(s2);
  stmt.define(1,a_ref);
  // Have we any data?
  if(stmt.execute())
  {
    // Yes...
    do
    {
      // Retrieve the A object by dereferencing the reference
      COCIObject a(s2, COCIType(s2, "A_T"));
      a = *a_ref;
      // Get the attributes...
      int id = a.get("ID");
      cout << "A.ID = " << id << endl;

      // Create a nested table and assign it the value from E in the A
      // object
      COCINestedTable<COCIObject> embed(s2, COCIType(s2, "E_T_1"));
      embed = (COCINestedTable<COCIObject>)a.get("E");

      int index = 0;
      // Iterate through the nested table, displaying the elements of it
      // N.B. iteration is based upon STL iteration of collections 
      // (A nested table and VArrays are effectively collections)
      for(COCINestedTable<COCIObject>::iterator current = embed.begin();
          current != embed.end();
          ++current)
      {
          COCIObject o(s2, COCIType(s2, "E_T"));
          o = *current;
          int id = 0;
          id = o.get("ID");
          cout << "A.E.ID[" << index << "] = " << id << endl;
          index++;
      }

      COCIRef b_ref(s2);
      b_ref = a.get("B");

      COCIObject b(s2, COCIType(s2, "B_T"));
      b = *b_ref;
      cout << "A.B.NAME = " << (char*)b.get("NAME") << endl;
    }while(stmt.fetch()); // ...while there is more data, fetch some more...
  }

  // Create another statement to retrieve the reference to a D object
  COCIStatement stmt2(s2);
  stmt2 = "select ref(r) from d r";
  COCIRef d_ref(s2);
  stmt2.define(1,d_ref);

  if(stmt2.execute())
  {
    do
    {
      COCIObject d = *d_ref;

      // Get the lob from the D object
      COCICLob clob(s2);
      clob = d.get("LOB");
      char bin[20];
      memset(bin,0,20);
      // Here the lob acts as a data source (i.e. a we can read from it)
#ifdef ORACLE8i
      clob.read((unsigned int*)bin,20);
#else
      clob.read((unsigned long*)bin,20);
#endif
      cout << "D.LOB = " <<  bin << endl;

      COCIRef c_ref(s2);
      c_ref = d.get("C");

      COCIObject c = *c_ref;
      // Here we are going to execute a method on the C object (DAYS_ALIVE())
      // We've previously set a data member with a DOB (1-APR-1999). Now, assuming
      // todays date is the 17-APR-1999, we can determine how long say, this object
      // has been alive (simple calculation (held on the server) SYSDATE - DOB). So,
      // in this case, this will be 16 days.
      // N.B. All variables are input variables.
      COCIStatement stmt3(s2);
      stmt3 = "declare\
                this_obj C_T;\
              begin\
                this_obj := :1;\
                :2 := this_obj.DAYS_ALIVE();\
              end;";

      int result = 0;
      stmt3.bind(1,c);
      stmt3.bind(2,result);
      if(stmt3.execute())
      {
        cout << "C.DAYS_ALIVE() = " << result << endl;
      }


      COCIDate dob(s2);
      dob = (COCIDate)c.get("DOB");
      cout << "D.C.DOB = " << dob.to_text().c_str() << endl;
    }while(stmt2.fetch());
  }

  t.commit();
}

// DDL for demo

/*
CREATE TYPE A_T
/
CREATE TYPE B_T
/
CREATE TYPE C_T
/
CREATE TYPE E_T
/


CREATE OR REPLACE TYPE D_T AS OBJECT
 (C REF C_T
 ,LOB CLOB
 )
/

CREATE OR REPLACE TYPE B_T AS OBJECT
 (A REF A_T
 ,NAME VARCHAR2(240)
 )
/

CREATE OR REPLACE TYPE E_T AS OBJECT
 (ID NUMBER
 )
/

CREATE TYPE E_T_1 AS TABLE OF E_T
/

CREATE OR REPLACE TYPE C_T AS OBJECT
 (DOB DATE
 ,E E_T_1
 ,B REF B_T
 ,ID NUMBER
 ,MEMBER FUNCTION DAYS_ALIVE
 RETURN NUMBER

 )
/

CREATE OR REPLACE TYPE A_T AS OBJECT
 (E E_T_1
 ,B REF B_T
 ,ID NUMBER
 )
/


CREATE OR REPLACE TYPE BODY C_T IS
MEMBER FUNCTION DAYS_ALIVE
 RETURN NUMBER
 IS
BEGIN
return (sysdate - DOB);
END DAYS_ALIVE;

END;
/


CREATE TABLE D OF D_T
/

CREATE TABLE E OF E_T
/

CREATE TABLE A OF A_T
 NESTED TABLE E STORE AS A_NT2
/

CREATE TABLE B OF B_T
/

CREATE TABLE C OF C_T
 NESTED TABLE E STORE AS C_NT14
/

*/