First, I’d like to apologize to our good friend SQLLIB. Those of you who have been working with the Oracle Database for some time will notice that, while it too is a common data access library, I’ve omitted it from this series of posts.
No, it’s not because of some personal vendetta against SQLLIB. In fact, I like SQLLIB (primarily for internals-related reasons), but I’ve excluded it because it’s not a user-oriented data access library and as such, I don’t consider it a first class citizen. And, for those of you who may find yourself asking, “what the hell is this SQLLIB thing Jonah’s going on about?”, it’s a library commonly used by the Oracle Precompiler suite, not something people develop directly against. Though, if there’s enough interest, I may do a post on SQLLIB in the future.
Regardless, in this series of articles, I’m going to discuss and demonstrate the evolution of Oracle’s UPI and OCI data access APIs. The articles are arranged as follows:
- Article 1: The User Program Interface (UPI)
- Article 2: The Oracle Call Interface Version 7 (OCI7)
- Article 3: The Oracle Call Interface Version 8 (OCI8)
But, before we get too deep into UPI, we need to start at the beginning.
What is a Data Access Layer?
A data access layer is a programming interface which allows developers to write programs capable of interacting with a database. So, whether you’re using Toad, SQL Developer, SQL*Plus, or a custom application, each relies on a data access layer to talk to the Oracle Database. However, in this set of articles, we won’t be discussing standard data access APIs like ODBC or JDBC. Instead, we’ll be taking a low-level look at proprietary Oracle APIs.
Oracle-supported APIs can be classified into two sets, public and internal-only.
Public Oracle Data Access APIs
These are APIs which Oracle has documented, supported, and wants customers to use.
Public APIs include:
- Oracle Call Interface (OCI)
- Oracle C++ Call Interface (OCCI)
- Standard APIs (ODBC, JDBC, .NET, OLE DB, …)
Private, internal-only Oracle Data Access APIs
These are APIs which Oracle uses internally, does not provide external documentation for, and does not want customers using for various reasons.
Private APIs include:
- User Program Interface (UPI)
- SQL Library Runtime (SQLLIB)
- Even older stuff than this…
In this article, we’ll focus on the User Program Interface, as it has been the core of Oracle’s data access for some time.
The User Program Interface
While UPI was once a popular internal-only API used within Oracle, it is in a constant state of being phased out in favor of the Oracle Call Interface (OCI). Now, you may be asking, “why are you doing a blog on something that’s being phased out?” Well, I’m doing it for three reasons: 1. Over the years, quite a few people have asked me about it, 2. UPI is still in use, and 3. I think it’s good for people to understand the evolutionary progress of how things got to where they are now.
So, how are we going to see some basic UPI in action? Bring on the example!
A UPI-based Example Program
Because I strongly believe concepts are best demonstrated using an applied method, I’ve taken an old Oracle OCI demonstration program and turned it into a very basic interactive SQL utility that allows you to execute basic SQL queries. The code kinda sucks because I spent very little time cleaning up the original, merged using a different style, don’t do great error checking, and could do a few things more correctly. But, it’s still a basic working example.
However, before we get into the fun stuff, we have to deal with the legal issues.
The Inevitable Disclaimer
It must be noted that I haven’t (yet) worked for Oracle, had access to the source code, or can guarantee proper execution of this on your database version or platform. The following is based on years of personal observation and research and while I’ve tested it on 8i, 9i, 10g, and 11g, I make no guarantees. THE SOFTWARE AND EXAMPLES PROVIDED ON THIS PAGE ARE PROVIDED “AS IS” AND WITHOUT WARRANTY OF ANY KIND. THE AUTHOR DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES. THIS SOFTWARE IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY AND IS NOT ENDORSED, VERIFIED, OR SUPPORTED BY ORACLE CORPORATION. THIS SOFTWARE SHOULD NOT BE USED IN A PRODUCTION ENVIRONMENT UNDER ANY CIRCUMSTANCES.
OK, now we can get to the fun stuff :-)
Prerequisite Knowledge
This blog entry assumes that you have a basic understanding of the C programming language as well as a rough idea of data access concepts such as parsing, binding, defining, and execution.
OK, let’s start!
Including the Oracle C Data Types
Before we can use UPI, we must first include the Oracle platform type definitions, oratypes.h. If you’ve done any OCI programming, you’ve no doubt run across this file. This file, located in $ORACLE_HOME/rdbms/public, can easily be included using:
#include "oratypes.h" /* Oracle-declared Platform Types */
Next, we need the C complier to know something about UPI.
Including the UPI Definitions
In order for our program to make use of UPI, we must now include the UPI function prototypes and definitions, which are contained in a file called upidef.h.
While I’ve made several requests for a copy of Oracle’s official upidef.h, they have been true to their internal-only mission and have never graced me with a copy. Consequently, it has taken me almost ten years to collect and research only a subset of the entire API. However, looking (optimistically) toward the future, I’d like to avoid changing anything in my code should I eventually get a copy of the real upidef.h (hint, hint). Accordingly, I’ve named my version of the definitions identically, and included the header file as follows:
#include "upidef.h" /* Jonah's Spiffy UPI Definitions */
Now that we’ve included the UPI definitions and function prototypes, we’re ready to start building our program. The first step is to define an HDA.
Defining UPI’s Host Data Area Structure
Similar to the concept of opaque handles, UPI uses an old technique of having the developer allocate a block of data which is passed back-and-forth to UPI. Internally, UPI maps offsets within this block to members of a C structure. As for definition, we’re going to define an array containing 512 elements of type ub1 (unsigned char/uint8_t). Depending on the platform and alignment-related issues, the HDA structure may also be allocated using an array of ub1, ub2, or ub4 with array-length changes being made accordingly.
static ub1 hda[512] = { 0 }; /* Host Data Area for UPI */
Defining a Cursor Using UPI
In UPI, cursors are represented simply by a number. Accordingly, we define a cursor as an unsigned word.
static uword cnum; /* Cursor Number */
Logon to Oracle
Next, we need to make a connection to the Oracle database. UPI provides a couple different logon functions, but we’ll use upilog. upilog will connect to the orcl database and create a session using scott/tiger. During this process, the HDA structure will be updated to contain some information about this connection.
if (upilog(&hda[0],
(text *) "scott/tiger@orcl",
-1, 0,
-1, 0,
-1, UPILFDEF, UPILMDEF))
Opening a Cursor
Now that we’re connected to the server, we need to get ready to process a statement. However, before we can process a statement, we must open a cursor. UPI provides the upiopn call for this.
if (upiopn(&hda[0], &cnum, -1))
Parsing a SQL Statement
Now that the user has passed-in a query for processing, we need to parse the query. UPI provides several functions for parsing, including the upipse and upiosd functions. In our example, we’ll use upiosd, which parses the SQL statement and associates it with our cursor. In our usage, we’re telling UPI to defer the actual parse [using (ub4) 1] until we call a describe/execution function. Similarly, rather than specific version 6/7 mode, we’re telling the system to parse the statement in standard (default) mode [via (sword) 1].
if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
(ub4) 1, (sword) 1))
Describing the Select-List
As we’re allowing the user to enter dynamic SQL queries, we need to describe the select-list prior to defining the output variables and fetching the data. UPI provides several functions for describing the select list, including the upidpr, upidqr, and upidsc functions. In our example, we’ll rely on upidsc, which returns the maximum size, datatype, column name, length of the column name, maximum display size, precision of numeric items, scale of numerics, whether null values are allowed, and a couple other things.
ret = upidsc(&hda[0], cnum, pos,
&pmxl[pos], &pvll[pos],
&perc[pos], &podt[pos], (text *)&coln[pos],
&coll[pos], &pmxdisl[pos],
&ppre[pos], &pscl[pos],
&pnul[pos]);
Defining Output Variables
The next step is to define the output variables for each select-list item in the query. To define output variables, UPI provides the upidfc, upidfn, and upidfps functions. In the example, we’ll use upidfn, which is a non-piecewise define for a select-list item by position. In our call, we’re making sure to pass reference to our indicator value for this item as well. Failure to do so will result in errors when attempting to fetch a column containing a NULL value.
if (upidfn(&hda[0], cnum, (sword) i,
obuf[i], coll[i],
(sword) SQLT_STR, (b2 *) &oind[i],
(text *) 0, (size_t) 0,
(ub2 *) 0, (ub2 *) 0,
(sword) 0))
Executing the Query
Now that we’ve defined our output variables, we’re ready to execute the query. The order of describe/define/execution can be different in different situations. Regardless, UPI provides several calls for execution including, upiefn, upiexe, and upiexn. In our example, we’ll use upiexe.
if (upiexe(&hda[0], cnum))
Fetching Data
So, we’ve defined our output variables and executed the statement. If all has gone well so-far, we can start fetching data. UPI provides several different calls including upifch and upifcn. We could also use upiefn (execute+fetch), but are instead going to use the simplest version, upifch. The upifch function performs a row-at-a-time fetch on our cursor.
while (upifch(&hda[0], cnum) == 0)
Conclusion
This has been a brief example of writing a simple application using Oracle’s UPI data access API. In the next two articles, you’ll see how this same application can be improved and made more descriptive using OCI7 and the current version of OCI, OCI8.
Full Program Listing
The following is the entire source code to the extremely simple interactive SQL client built using the User Program Interface. Given that Oracle still considers UPI proprietary, I will not be attaching nor sending my UPI definitions header file, upidef.h. If you’d like to actually run the program below, you can fairly easily recreate the UPI function prototypes based on the data types and argument ordering provided.
/* ========================================================================= */
/* INTERACTIVE SQL EXAMPLE */
/* Oracle User Program Interface (UPI) */
/* */
/* Jonah H. Harris <jonah.harris@gmail.com> */
/* http://www.oracle-internals.com/ */
/* ========================================================================= */
/* Standard Headers */
#include <stdio.h> /* Standard C Input/Output */
#include <stdlib.h> /* Standard C Library */
/* Oracle-specific Headers */
#include <oratypes.h> /* Oracle-declared Platform Types */
#include "upidef.h" /* My UPI Definitions */
/* ========================================================================= */
/* << DEFINITIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */
#define MAXATTR 1024 /* Max number of attributes in select-list */
/* ========================================================================= */
/* << STATIC VARIABLES >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */
static ub1 hda[512] = { 0 }; /* Host Data Area for UPI */
static uword cnum; /* Cursor Number */
/* ========================================================================= */
/* << FORWARD DECLARATIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */
static void upi_error (void);
/* ========================================================================= */
/* << PUBLIC FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */
int
main (int argc, char **argv)
{
char sql_statement[256];
sword i, pos;
sword ret;
ub1 podt[MAXATTR], ppre[MAXATTR], pnul[MAXATTR];
ub2 pmxl[MAXATTR], coll[MAXATTR],
pmxdisl[MAXATTR], pvll[MAXATTR];
sb1 pscl[MAXATTR];
sb2 perc[MAXATTR];
text coln[MAXATTR][31];
text *obuf[MAXATTR];
sb2 oind[MAXATTR];
/* Logon */
if (upilog(&hda[0],
(text *) "scott/tiger@orcl",
-1, 0,
-1, 0,
-1, UPILFDEF, UPILMDEF))
{
printf("Cannot connect as scott. Exiting...\n");
exit(1);
}
/* Open a cursor */
if (upiopn(&hda[0], &cnum, -1))
{
printf("couldn't open cursor!\n");
upi_error();
exit(1);
}
/* Main loop */
for (;;)
{
ub4 rnum = 0; /* A generic record number counter */
printf("\nEnter a query or \"exit\"> ");
fgets(sql_statement, sizeof(sql_statement), stdin);
/* If user typed, "exit", bail! */
if (strncmp(sql_statement, "exit", 4) == 0)
break;
/* Parse the statement */
if (upiosd(&hda[0], cnum, (text *) sql_statement, -1,
(ub4) 1, (sword) 1))
{
upi_error();
continue;
}
/* Iterate over each attribute in the select-list */
for (pos = 1; pos < MAXATTR; pos++)
{
coll[pos] = sizeof(coln[pos]);
/* Describe the select-list item */
ret = upidsc(&hda[0], cnum, pos,
&pmxl[pos], &pvll[pos],
&perc[pos], &podt[pos], (text *)&coln[pos],
&coll[pos], &pmxdisl[pos],
&ppre[pos], &pscl[pos],
&pnul[pos]);
if (coll[pos] > sizeof(coln[pos]))
coll[pos] = sizeof(coln[pos]);
/* Make sure this string is null-terminated */
coln[pos][coll[pos]] = '\0';
if (ret)
{
if (ret == 1007)
break;
upi_error();
continue;
}
}
/*
* Print out the total count and the names of the select-list
* items, column sizes, and datatype codes.
*/
pos--;
printf("\nThere were %d select-list items.\n", pos);
printf("Item name Length Datatype\n");
printf("\n");
for (i = 1; i <= pos; i++)
{
printf("%*.*s", coll[i], coll[i], coln[i]);
printf("%*c", 31 - coll[i], ' ');
printf("%6d %8d\n", coll[i], podt[i]);
/* Allocate the output */
obuf[i] = (text *) malloc(coll[i]+1);
if (obuf[i] == NULL)
{
fprintf(stderr, "Failed to allocate %u bytes!\n",
coll[i]+1);
exit(EXIT_FAILURE);
}
/* Define the output parameter */
if (upidfn(&hda[0], cnum, (sword) i,
obuf[i], coll[i],
(sword) SQLT_STR, (b2 *) &oind[i],
(text *) 0, (size_t) 0,
(ub2 *) 0, (ub2 *) 0,
(sword) 0))
{
upi_error();
break;
}
}
/* Execute */
if (upiexe(&hda[0], cnum))
{
upi_error();
continue;
}
/* Fetch & Display */
while (upifch(&hda[0], cnum) == 0)
{
printf("\n-- RECORD %04u -------------------------\n", ++rnum);
for (i = 1; i <= pos; i++)
{
printf("%s", coln[i]);
printf("%*c ", 31 - coll[i], ':');
printf("%s\n", (oind[i]) ? "NULL" : obuf[i]);
}
}
/* Display the summary */
printf("\n%u record%s returned.\n", rnum, (rnum == 1) ? "" : "s");
/* Cleanup */
for (i = 1; i <= pos; i++)
if (obuf[i] != NULL)
free(obuf[i]);
} /* for (;;) */
/* Close the cursor */
upicls(&hda[0], cnum);
/* Logoff */
upilof(&hda[0]);
/* Bail! */
exit(EXIT_SUCCESS);
} /* main() */
/* ========================================================================= */
/* << PRIVATE FUNCTIONS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* ========================================================================= */
static void
upi_error (void)
{
text msg[512] = { 0 };
fprintf(stderr, "\nOracle ERROR\n");
upigem(&hda[0], (text *) &msg);
fprintf(stderr, "%s", msg);
fflush(stderr);
} /* upi_error() */
UPI is fun! :)