<!--#include virtual="header.shtml" -->

<h3 align="center"><font color="#AOAO99">
A Wrapper for the Oracle Call Interface
</font></h3>
<hr>

<p>This article was contributed by 
   <a href="mailto:bcbryant@firstobject.com">Ben Bryant</a>.</p>

<p><img src="Osql10.gif" alt="Sample Image"></p>

<p><u>Environment:</u> Oracle8, VC6, NT4, WIN95/98</p>

<I><P>Osql.exe is an SQL client that runs on Windows NT/95/98 with Oracle Net8. It is built using the COdb class which requires Oracle8 and can be added to any MFC project. This OCI wrapper class is in a single convenient  .cpp/.h pair. The Osql project is in Visual C++ 6.0, and uses a statically linked MFC library available in the Enterprise version of Visual Studio 6.0.</P>
</I>
<P><h3>Introduction</h3></P>
<P>When I first approached the Oracle Call Interface OCI.DLL, I looked in vain for a free MFC wrapper for it. I wanted some source code both to get started programming the OCI and perhaps to be able to quickly start accessing the Oracle database without having to figure out everything first. I did later discover a C++ wrapper by John Hatcher, but I think it requires an even deeper understanding of Oracle and the OCI than just plain writing straight to the OCI. A wrapper should make things more accessible than the underlying technology. I wanted a wrapper (like I ended up writing) that could do the following sorts of things:</P>
<P>
<pre>COdb db;
db.Open( &quot;scott/tiger@host2&quot; );
db.Select( &quot;select * from tab1&quot; );
while ( ! db.IsEOS() )
{
	Output( db.GetField(&quot;column1&quot;) );
	db.FetchNext();
}

</pre>
</P>
<P><h3>Magic Door</h3></P>
<P>The OCI is the magic door to the Oracle database. Unlike ODBC drivers, it is very reliable and fast because it is based directly on the ORAxxx.DLL which is fundamental to the Oracle product. I think that if the OCI didn't work, almost nothing would work. The Oracle Call Interface also supports "external procedures" for linking C code into a stored procedure.</P>
<P>With the OCI, Oracle has provided a powerful way to use the database from C. You can query column attributes after performing a select, bind placeholders into an executed pl/sql block, and convert between all the Oracle data types. While the OCI is powerful, it is also very complex to use from C/C++, requiring an good understanding of pointers, memory, and database issues. The source code here allows you to immediately start accessing the database, and provides examples for you to look at to expand and modify the wrapper to fit your needs.</P>
<P><h3>Osql Client</h3></P>
<P>To illustrate the COdb class I have included Osql.exe 1.0 and its complete Visual C++ 6.0 source project. Osql does some of the things SQL*Plus does, with a better interface for entering, modifying, and re-using SQL chunks (SQL*Plus was designed for a UNIX shell not a Windows dialog). You could easily extend Osql into a better Windows program than SQL*Plus. I have started to do this myself, and I am making available Osql 1.1 and an Odb.lib as freeware on the <A HREF="http://www.firstobject.com/">www.firstobject.com</A> web site. Osql 1.1 has an extended feature set comparable to SQL*Plus, and it has less than half the executable size of SQL*Plus 8.0 (a 350k exe file), <I>despite the use in Osql of statically linked MFC</I>.</P>
<P>Like other Oracle products, the OCI.DLL is resource intensive. It loads a lot of other DLLs. I noticed in debug mode that to load up the OCI.DLL there are over 20 Oracle DLL relocations due to collisions with OCI.DLL. Whether or not that is my error in the Osql project settings, I do not know.</P>
<P><h3>Using Osql</h3></P>
<P>You probably want to see it in action before delving into the code so I'll talk first about using Osql.exe. You need Oracle8 to run it (the Oracle installation puts the OCI.DLL's location (e.g. d:\orant\bin) in the path). You don't need anything else! You can immediately connect to an Oracle database, select from tables and execute SQL statements, much as you would in SQL*Plus <I>without the semi-colons</I>.</P>
<P>As in SQL*Plus, there are also some non-SQL commands. Type a question mark to see a list of the commands available in Osql. Each command is listed with its named arguments and defaults if any. Only use semi-colons where they are required in PL/SQL blocks. If you enter something that does not start with any of these Osql commands, Osql tries to execute it as an SQL statement.</P>
<P>The interface for re-using and modifying chunks of commands and pl/sql blocks is handy. If you right click in the output area you can copy text to the command entry box or immediately run it. The command entry box resizes to fit multiple lines of text when you paste in a large pl/sql block or use ctrl-enter to start a new line. The escape key exits the program (closing the session if open).</P>
<P>The first command you will need is the connect command. It works like SQL*Plus 8.0 and uses your Net80 configuration to connect to the Oracle database. No host name is necessary if the database is on the same machine.</P>
<P>
<pre>connect scott/tiger@host
</pre></P>
<P>One of the differences from SQL*Plus is that semi-colons are not allowed except after anonymous blocks. This corresponds to what the OCI expects in an Execute statement. If you have the necessary permissions, you can try copying and pasting the following commands into Osql, one create command at a time:</P>
<P>
<pre>create table table1 (n NUMBER(22), d date, c VARCHAR2(80) )

create sequence sequence1

create or replace package package1 is
	function add_entry (c in VARCHAR2 ) return NUMBER;
end package1;

create or replace package body package1 is
	function add_entry (c in VARCHAR2 ) return NUMBER is
	n NUMBER(22);
	begin
		select sequence1.nextval into n from dual;
		insert into table1 values ( n, sysdate, c );
		return n;
	end add_entry;
end package1;

</pre>

<P>After you have created these objects, you can try them out using the following commands:</P>
<P>
<pre>declare n number(22); begin n := package1.add_entry( 'something' ); end;

select * from table1

</pre>
<P><h3>Wrapping the Power</h3></P>
<P>I had some trouble getting going because of confusion about versions and lack of new C++ samples. Oracle 8.0 came out with a new set of OCI functions all starting with &quot;OCI&quot; such as OCIInitialize() and OCIHandleAlloc() while Oracle 7 had functions like olog() and oopen(). Most of the Oracle 7 functions still work and most C samples still use the old functions despite the fact that the documentation is for the new functions. The oci.h header file would not even work in C++ without a #define __STDC__ 1 workaround.</P>
<P>When I began to design the COdb wrapper I instinctively followed the familiar ODBC style. The purpose was to provide simple string argument methods that would take care of my common needs. I do not attempt to provide all OCI capabilities, but the nice thing about accessing it directly from this class is that you are free to implement anything you want in the class. The wrapper class has many of the capabilities of simple ODBC, and yet it is as fast as you can get.</P>
<P>One other thing to mention is that COdb uses all values as strings. I chose to have the OCI convert all types like numbers and dates to strings to simplify matters greatly. In my opinion this does not mean a performance penalty because I think internally Oracle stores BCD numbers which are quick to convert to strings. The lack of type strictness reduces the time to prototype any database application. In addition, it produces an empty string for nulls (if you want you can delve into the class to see where null values are signaled by the OCI).</P>
<P>The COdb class can be either linked directly into a program and used as-is, or as sample code illustrating the use of the OCI interface's APIs.</P>
<P><h3>Using the COdb Class</h3></P>
<P>The COdb class has been reliable in daily usage in an NT development environment for several months (but no guarantee!). This also reflects well on the Oracle products involved. The wrapper class has also been easy for other developers to use. To link COdb to your application, follow these steps:</P>
<OL>

<LI>add oci.lib to your Project Settings, Link, General, Object/library modules.</LI>
<LI>add your orant\oci80\include path to your Tools Options, Directories, Include files</LI>
<LI>add your orant\oci80\lib\msvc path to your Tools Options, Directories, Library files</LI>
<LI>add Odb.cpp and Odb.h to your Project Add to Project..., Files</LI>
<LI>#include &quot;Odb.h&quot; in your source code that uses the COdb class</LI></OL>


<P>The class definition for release 1.0 is very simple. Here is a list of the main public operations:</P>
<P>
<pre>
HRESULT Open( CString csConnect );
HRESULT Close();
HRESULT Exec( CString csStatement );
HRESULT Select( CString csStatement );
HRESULT FetchNext();
BOOL IsEOS();
CString GetErrorDescription();
CString ProcessQuotes( CString csValue );
CString GetField( int iField );
CString GetField( CString csName );
BOOL GetField( int iField, CString&amp; csName, CString&amp; csValue, BOOL bQuotesIfValueRequires = FALSE );
CString GetResults() { return m_csResults; };
void StartLog() { m_csLog.Empty(); };
CString GetLog() { return m_csLog; };

</pre>
<P>If you are familiar with ODBC, you will understand the purpose of most of the methods. Instantiate a COdb object right where you need to use it or as a member of a class. In the Osql program, m_db is a member of the COsqlDlg class. To open two different sessions, create two COdb objects. The destructor will close an open session and clean things up, if you don't do it explicitly, so don't worry.</P>
<P>Use SUCCEEDED() or FAILED() to test an HRESULT. For example:</P>
<P>
<pre>if FAILED( db.Open(&quot;scott/tiger@host2&quot;) )
	AfxMessageBox( db.GetErrorDescription() );
</pre>
<P>One handy function is ProcessQuotes() for SQL when you are putting together a statement containing strings. When you put quotes around a string in a statement, there is the risk that the string might contain a quote, in which case you are supposed to double up the quote. The ProcessQuotes() function will do this for you so you can put the result directly in the statement. Notice that you don't need quotes around numerical values.</P>
<P>
<pre>CString csSQL;
LPCTSTR szValue = "John's book";
int n = 2;
csSQL.Format( "insert into table1 values(%d,'%s')", n, ProcessQuotes(szValue) );
db.Exec( csSQL );
</pre>

<P>When you perform a SELECT statement, use the Select() method instead of Exec(). The results are in tabular form with rows and columns, accessed one row (i.e. record) at a time. Check IsEOS() immediately; it will be FALSE if any rows were returned by the query. You can specify either the zero-based column number or the column name to GetField() to get a value. Then go to the next row using FetchNext() until IsEOS() is TRUE.</P>
<P><h3>Implementation Notes</h3></P>
<P>In the interest of isolating other C++ modules from Oracle's oci.h, I defined the OdbField, OdbRecordset, and OdbContext structures (classes actually) in the COdb.cpp file rather than the COdb.h header. The advantages are arguable, but the COdb class definition and header appears simpler to the outside world. The OdbContext contains the OCI handles needed during the session, and the OdbRecordset maintains an array of OdbFields after a SELECT statement is performed.</P>
<P>The source for the command line interface in this project is like a bonus in addition to the COdb class. CCommand does command line parsing, and manages the sizing and referencing of the input edit box, output richedit control, and the Run button. The CCmdOutCtrl can copy text directly to the command entry CCmdInCtrl and trigger the run button from its popup menu.</P>
<P>The commands with their syntax are listed in an array of strings (szaCommandDefns) allowing for rapid modification and implementation of commands. The Output control is a richedit control supporting different fonts and styles. To use this command line interface with your own application, look closely at the use of the CalcSize() function and don't forget to put AfxInitRichEdit() in your InitInstance().</P>
<P>Have Fun!!!</P>
<P><h3>Disclaimer</h3></P>
<P>The Osql program, and all source code is provided 'as-is', without warranty. Use it at your own risk. In no event will the author be held liable for any damages arising from any use of this software. The OCI, SQL*Net, SQL*Plus, and the PL/SQL extension of SQL are products of Oracle Corporation.</P>
<P><h3>Download</h3></P>
<P><a href="Osql10.zip">Download complete project - 107 Kb</a></P>
<P><a href="Osql10_src.zip">Download source only - 36 Kb</a></P>
<P>Copyright &copy; 1999 Ben Bryant</P>
<P><h3>History</h3></P>
<P>Date Posted: June 29, 1999</P>

<!--#include virtual="footer.shtml" -->