Oracle Objects for OLE OO4O

1. Oracle Objects for OLE (OO4O)

2. What is Oracle Objects for OLE (OO4O) a middleware product manufactured by Oracle Corporation that allows native access (no ODBC) to Oracle databases from client applications via Microsoft OLE (Object Linking and Embedding) and COM

3. OO4O vs. ODBC Native Access to Oracle and only Oracle databases Runs slightly faster than ODBC access Some people feel they are VERY convenient

4. Access Oracle through Microsoft Applications using OO4O Microsoft Visual Basic Microsoft Excel Microsoft MS-Access Active Server Pages (ASP) C++, etc.

5. On our system You can learn about them now in the EE121 lab using: Start -> Program -> Oracle � OraHome9 -> Application Development -> Oracle Objects for OLE Class Library Help See this handout for information on how to set up .NET for OLE

6. To begin Use the following .h file when using OLE classes #include <oracl.h> You must always invoke the OStartup routine before doing anything with the library OStartup();

7. OSession class You should instantiate an OSession to connect to the database with. This gives you more power, such as error capture. OSession session ("SESSION1");

8. ODatabase class Use ODatabase to log on to an Oracle database. ODatabase is a subclass of the base class OOracleObject. ODatabase db(session, �students", �vrbsky", �pw");

9. Execute Immediate You can execute SQL statements directly with the ExecuteSQL method. db.ExecuteSQL("insert into project values (:projectName, :projectNumber, :projectLocation, :departmentNumber)")

10. Using Host Variables The above query requires many more statements before host variables can be used See example2 for a sample program using host variables

11. ODynaset class What if you have a result from your query? You can also get records from the Oracle database by using the ODatabase object to create ODynaset objects. The ODynaset class creates, manages, and accesses records of data from the database. It corresponds to a scrollable cursor. ODynaset is a subclass of the OOracleObject base class.

12. ODynaset class An ODynaset is opened by executing a query against an Oracle database in the form of a SQL select statement. Any legal select statement is acceptable. All the database records that this query returns are referred to as the dynaset's result set. Records from the result set are fetched to the client as needed and cached locally. You then operate on the records of the dynaset one at a time. The record you are currently working with is referred to as the current record.

13. ODynaset methods You can obtain field values from the current record (GetFieldValue) edit the current record (StartEdit, SetFieldValue, Update) delete the current record (DeleteRecord) duplicate the current record (DuplicateRecord).

14. ODynaset example ODynaset results(db, "Select * from employee"); cout << "salary" << endl; cout << "========" << endl; double salary; while (!results.IsEOF()) { if (results.GetFieldValue ("salary", &salary) == OFAILURE) { cout << db.GetServerErrorText() << endl; // example of character string // char first [10]; // results.GetFieldValue("fname", first, 10); } else { cout << salary << endl; results.MoveNext(); } }

15. ODynaset move You can change the "current" record, navigating through the dynaset's result set, by using one of the "Move" methods (MoveFirst, MoveNext, etc). Execution of a Move method changes which record is current. The records in a dynaset's result set will be in some order. To specify a particular order, use an "order by" clause in your SQL statement to order the results.

16. Dynamic query To execute a dynamic query (one that the user types in interactively) you can use: char query1 [255]; ODatabase db (session, �students�, �vrbsky�, �pw�); //prompt the user for the query and read it into // the variable query1 ODynaset results (db, query1); How to know the columns requested?

17. GetFieldCount of ODyanset This method returns the number of fields in each record. Usage int GetFieldCount(void) const Return Value The number of fields in each record; 0 on error.

18. GetFieldCount example This example demonstrates GetFieldCount. // open a database ODatabase db (session, �students�, �vrbsky�, �pw�); // open a dynaset ODynaset results(db, "select lname, minit, fname, ssn from employee"); // how many fields was that? nfields = results.GetFieldCount(); // nfields is 4 and 0-based

19. GetFieldValue What if you don�t know the name? You can use the index position to retrieve values for rows: double EID; char name [10]; results.GetFieldValue (0, &EID); results.GetFieldValue (1, name, 10); How can you determine the type?

20. GetFieldServerType of ODynaset NOTE: You can read a number into a character string and it will convert it This method returns the Oracle type of the specified field in the database. int GetFieldServerType(int index) const int GetFieldServerType(const char *fieldname) const index: position of the field in the SQL query that created the current record set (0-based). fieldname: the name of the field, as expressed in the SQL query Returns an integer which identifies the type of the specified field (varchar = 1, number = 2).

21. More OLE How to determine the name of the columns? To run OLE in EE121, see this handout Sample program How to process a random query without OLE classes � using only embedded SQL See this Oracle Documentation