Programming for Oracle on Linux, Part 2
Previously, I wrote about interacting with an Oracle database using Perl and the DBI Perl module using the DBD::Oracle DBI driver. For some applications, Perl may not offer the speed to provide the necessary performance, especially for high throughput database applications. If your application becomes a performance bottleneck, you may need to turn to a compiled language such as C. In this article, I explain how to write the necessary code to implement our application from Part 1 in C.
In the first part of this series, I explained how to install the necessary Perl modules to get us up and running. As a part of that process, the Oracle client software should have been installed as well. If this is not the case, see the links in Resources for more information on installing the Oracle client software.
Next, we need to install the libsqlora8 library. This library, a wrapper to Oracle's OCI libraries, simplifies the interaction through OCI. libsqlora8 is developed on SuSE Linux, so Linux support is a given. I also have used the library on Solaris, and it should compile on other UNIX flavors as well. I have used libsqlora8 with Oracle client versions from 8.1.6 to 10.1 with no problems. The only issue seems to be with using the 10.1 client with an 8.1.6 database, but that is an Oracle/OCI issue and doesn't seem to be an issue with the library itself.
To install libsqlora8, download the latest version from the Web site listed in Resources. The current stable release is 2.2.12. Once you have completed the download, run the following commands:
tar -xvzpf libsqlora8-2.2.12.tar.gz cd libsqlora8-2.2.12 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/oracle-home/lib (or setenv LD_LIBRARY_PATH $LD_LIBRARY_PATH:/path/to/oracle-home/lib for csh) ./configure --with-oraclehome=/path/to/oracle-home --enable-shared=yes make make install
As in the first part of this series, here we want to be able to query some data from a database containing some customer information. We are going to use the same customer_data table that we used in our original examples, containing the username, first and last name, address, city, state, zip code, phone number, age and an account status code. An example of the table definition is shown in Listing 1. A sample SQL script for generating the table is shown in Listing 2.
Listing 1. Example Table Definition
Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(16) FIRST_NAME NOT NULL VARCHAR2(255) LAST_NAME NOT NULL VARCHAR2(255) ADDRESS_LINE1 NOT NULL VARCHAR2(255) ADDRESS_LINE2 VARCHAR2(255) CITY NOT NULL VARCHAR2(255) STATE NOT NULL VARCHAR2(2) ZIPCODE NOT NULL VARCHAR2(255) PHONE VARCHAR2(12) AGE NUMBER STATUS NUMBER
Listing 2. SQL Script for Generating the Table
CREATE TABLE customer_data ( username VARCHAR2(16) NOT NULL, first_name VARCHAR2(255) NOT NULL, last_name VARCHAR2(255) NOT NULL, address_line1 VARCHAR2(255) NOT NULL, address_line2 VARCHAR2(255), city VARCHAR2(255) NOT NULL, state VARCHAR2(2) NOT NULL, zipcode VARCHAR2(255) NOT NULL, phone VARCHAR2(12), age NUMBER, status NUMBER, PRIMARY KEY (username));
The code for our simple data manipulation utility, cdata.c, is in Listing 3. A Makefile to assist in compiling it is shown in Listing 4.
Listing 3. cdata.c
#include <stdio.h>
#include <sysexits.h>
#include <stdlib.h>
#include <unistd.h>
#define DOXYGEN_SHOULD_SKIP_THIS
#include <sqlora.h>
//////////////////////////////////////////////////////////////////////////////
// Variables
//////////////////////////////////////////////////////////////////////////////
// Verbosity
int verbose = 0;
// Operating mode flags
int query_mode = 0;
int create_mode = 0;
// Globals for creating a new record
char username[32];
char first_name[256];
char last_name[256];
char address_line1[256];
char address_line2[256];
char city[256];
char state[3];
char zipcode[256];
char phone[256];
int age;
// Global database and statement handles
sqlo_db_handle_t dbh;
sqlo_stmt_handle_t sth;
// Database variables
char db_sid[128];
char db_user[128];
char db_pass[128];
//////////////////////////////////////////////////////////////////////////////
// Function Prototypes
//////////////////////////////////////////////////////////////////////////////
void print_usage(void);
void create_customer_record(void);
int dbconnect(sqlo_db_handle_t);
int db_insert(char *, sqlo_db_handle_t);
void dbdisconnect(sqlo_db_handle_t);
int query_customer_database(sqlo_db_handle_t);
void error_exit(sqlo_db_handle_t, const char *);
//////////////////////////////////////////////////////////////////////////////
// Function: main()
//
// The main function for cdata. Parse command line options,
// determine the operating mode, and act accordingly.
//
//////////////////////////////////////////////////////////////////////////////
int main(int argc, char *argv[])
{
int opt;
extern char *optarg;
extern int optind, opterr, optopt;
while((opt = getopt(argc, argv, "A:cC:F:hL:p:P:qs:S:u:U:vZ:1:2:")) != -1)
{
switch (opt)
{
case 'A':
age = atoi(optarg);;
break;
case 'c':
create_mode = 1;
break;
case 'C':
sprintf(city, "%s", optarg);
break;
case 'F':
sprintf(first_name, "%s", optarg);
break;
case 'L':
sprintf(last_name, "%s", optarg);
break;
case 'h':
print_usage();
exit(EX_USAGE);
break;
case 'p':
sprintf(db_pass, "%s", optarg);
break;
case 'P':
sprintf(phone, "%s", optarg);
break;
case 'q':
query_mode = 1;
break;
case 's':
sprintf(db_sid, "%s", optarg);
break;
case 'S':
sprintf(state, "%s", optarg);
break;
case 'u':
sprintf(db_user, "%s", optarg);
break;
case 'U':
sprintf(username, "%s", optarg);
break;
case 'v':
verbose = 1;
break;
case 'Z':
sprintf(zipcode, "%s", optarg);
break;
case '1':
sprintf(address_line1, "%s", optarg);
break;
case '2':
sprintf(address_line2, "%s", optarg);
break;
case ':':
fprintf(stderr, "Option %c requires a value.\n", opt );
exit(EX_USAGE);
break;
case '?':
fprintf(stderr, "Unknown option: %c\n", optopt );
exit(EX_USAGE);
break;
default:
fprintf(stderr, "Unknown option: %c\n", optopt );
exit(EX_USAGE);
break;
}
}
// Make sure that we select an operating mode (create or query)
if ((create_mode && query_mode) || (!create_mode && !query_mode)) {
fprintf(stderr, "Must select either create or query mode.\n");
print_usage();
exit(EX_USAGE);
}
// Make sure we have our user, password and SID
if (db_sid[0] == '\0' || db_user[0] == '\0' || db_pass[0] == '\0') {
fprintf(stderr, "Must provide user, password and SID.\n");
print_usage();
exit(EX_USAGE);
}
// Connect to the database
if (dbconnect(dbh) < 0) {
fprintf(stderr, "Connection to %s failed.\n", db_sid);
exit(EX_OSERR);
}
// Query the contents of the customer database
if (query_mode) {
query_customer_database(dbh);
}
// Insert a new record into the customer database
else if (create_mode) {
// Make sure that the required fields exist
if (username[0] == '\0' || first_name[0] == '\0' ||
last_name[0] == '\0' || address_line1[0] == '\0' ||
city[0] == '\0' || state[0] == '\0' || zipcode[0] == '\0') {
printf("Missing a required field.\n");
print_usage();
exit(EX_USAGE);
}
if (verbose) {
printf("User: %s\n", username);
printf("Name: %s %s\n", first_name, last_name);
printf("Address: %s\n", address_line1);
printf(" %s\n", address_line2);
printf("City: %s\n", city);
printf("State: %s\n", state);
printf("Zip: %s\n", zipcode);
printf("Phone: %s\n", phone);
printf("Age: %d\n\n", age);
}
// Create the record
create_customer_record();
}
else {
fprintf(stderr, "Unknown operating mode.\n");
}
// Disconnect and exit
dbdisconnect(dbh);
exit(EX_OK);
}
//////////////////////////////////////////////////////////////////////////////
// Function: print_usage()
//
// Print the usage information.
//
//////////////////////////////////////////////////////////////////////////////
void print_usage(void)
{
printf("Usage:\n");
printf("Query mode:\n");
printf(" cdata [-v] -q -u <user> -p <pass> -s <oracle SID>\n\n");
printf("Create mode:\n");
printf(" cdata [-v] -c -u <user> -p <pass> -s <oracle SID> \\\n");
printf(" -U <username> -F <first name> -L <last name> \\\n");
printf(" -1 <addr line 1> -2 <addr line 2> -C <city> \\\n");
printf(" -S <state> -Z <zip code> -P <phone> -A <age>\n");
}
//////////////////////////////////////////////////////////////////////////////
// Function: create_customer_record()
//
// put together our SQL INSERT statement and pass it off to
// db_insert().
//
//////////////////////////////////////////////////////////////////////////////
void create_customer_record(void)
{
char sql_string[1024];
int records;
sprintf(sql_string, "INSERT into customer_data VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', %d, 1)",
username, first_name, last_name, address_line1, address_line2,
city, state, zipcode, phone, age);
records = db_insert(sql_string, dbh);
if (records > 0) {
printf("Inserted %d records.\n", records);
}
else {
fprintf(stderr, "Customer record creation failed.\n");
}
return;
}
//////////////////////////////////////////////////////////////////////////////
// Function: db_insert()
//
// Execute a SQL INSERT query and return the number of affected
// rows.
//
//////////////////////////////////////////////////////////////////////////////
int db_insert(char *query, sqlo_db_handle_t handle)
{
int stat;
if ( 0 > (stat = sqlo_exec(handle, query)))
error_exit(handle, "sqlo_run");
return stat;
}
//////////////////////////////////////////////////////////////////////////////
// Function: dbconnect()
//
// Connect to the database.
//
//////////////////////////////////////////////////////////////////////////////
int dbconnect(sqlo_db_handle_t handle)
{
char server_version[1024];
char login[1024];
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100)) {
printf("Failed to init libsqlora8.\n");
return -1;
}
sprintf(login, "%s/%s@%s", db_user, db_pass, db_sid);
if (verbose) {
printf("Connecting to %s...\n", login);
}
if (SQLO_SUCCESS != sqlo_connect(&handle, login)) {
printf("Cannot login with %s\n", login);
return -1;
}
if (SQLO_SUCCESS != sqlo_server_version(handle, server_version, sizeof(server_version))) {
printf("Failed to get the server version: %s\n", sqlo_geterror(handle));
return 1;
}
printf("Connected to: %s\n\n", server_version);
return 0;
}
//////////////////////////////////////////////////////////////////////////////
// Function: dbdisconnect()
//
// Disconnect from the database
//
//////////////////////////////////////////////////////////////////////////////
void dbdisconnect(sqlo_db_handle_t handle)
{
int ret;
ret = sqlo_server_detach(handle);
if (ret != 0) {
fprintf(stderr, "An error occured while disconnecting from DB.\n");
exit(EX_OSERR);
}
}
//////////////////////////////////////////////////////////////////////////////
// Function: query_customer_database()
//
// Query the customer database, format and print the contents.
//
//////////////////////////////////////////////////////////////////////////////
int query_customer_database(sqlo_db_handle_t handle)
{
char sql_string[1024];
sqlo_stmt_handle_t sth;
int status;
const char ** v;
sprintf(sql_string, "SELECT username, first_name, last_name, address_line1, address_line2, city, state, zipcode, phone, age FROM customer_data WHERE status=1");
// Execute the query and create a statement handle in sth.
if (0 > (sth = (sqlo_open(handle, sql_string, 0, NULL)))) {
error_exit(handle, "sqlo_open");
}
// Fetch the data
while (SQLO_SUCCESS == (status = (sqlo_fetch(sth, 1)))) {
// get one record
v = sqlo_values(sth, NULL, 1);
printf("---------------------------\n");
printf("User: %s\n", v[0]);
printf("Name: %s ", v[1]);
printf("%s\n", v[2]);
printf("Address: %s\n", v[3]);
printf(" %s\n", v[4]);
printf("City: %s\n", v[5]);
printf("State: %s\n", v[6]);
printf("Zip: %s\n", v[7]);
printf("Phone: %s\n", v[8]);
printf("Age: %d\n\n", atoi(v[9]));
}
return 0;
}
//////////////////////////////////////////////////////////////////////////////
// Function: error_exit()
//
// SQL error handling function.
//
//////////////////////////////////////////////////////////////////////////////
void error_exit(sqlo_db_handle_t dbh, const char * msg)
{
printf("%s:\n%s\n",
msg,
sqlo_geterror(dbh));
sqlo_rollback(dbh);
sqlo_finish(dbh);
exit(EX_OSERR);
}
Listing 4. Makefile
############################################################################## CC = gcc DEBUG = -ggdb3 INC = -I/usr/local/include C_FLAGS = -O2 -Wall $(DEBUG) $(DEFINES) $(INC) L_FLAGS = -L/usr/local/lib LIBS = -lsqlora8 TARGET = cdata O_FILES = cdata.o ############################################################################ # Targets ############################################################################ all: $(TARGET) clean: @rm -f *.o $(TARGET) $(TARGET): $(O_FILES) Makefile $(CC) $(L_FLAGS) -o $(TARGET) $(O_FILES) $(LIBS) .c.o: $(CC) $(C_FLAGS) -c $<
The first 50 lines of the program include the necessary header files and declare global variables and prototypes for functions that show up in the rest of the program.
Line 61 starts the main() function. Lines 67 through 136 handle the command-line options for the program. As with the Perl program from the first part of this series, the cdata program uses command-line options either to query the customer database or create a new database record. The getopt() function parses the short single character style options, which were used for simplicity. To use the GNU style options (--verbose, rather than -v), take a look at getopt_long() or getopt_long_only() in getopt(3).
Once we have passed our command-line options to our program, we verify that we're using either create or query mode and not both modes together in lines 139-143.
Next, we try to connect to our database now that we know we have a valid operating mode. First, in lines 146-150, we make sure that we have given our database connection options. Then, in lines 153-156 we make our database connection.
After our database connection is established, if we are using query mode, we call query_customer_data() to perform our SELECT query. Otherwise, if we are using create mode, we verify that we have all of the required data to create our new record. If we've asked for verbose output with the -v flag, we print the data that has been input. Otherwise, we simply create the new customer record silently with create_customer_record(). If no operating mode is given, we then print an error to stderr.
Finally, we call dbdisconnect() to close our database connection, and we exit successfully.
One of the major functions of the program create_customer_record() starts at line 224. This function assembles the INSERT SQL query, passes it off to db_insert() and then prints the results.
The db_insert() function looks pretty simple, but it does the majority of the real work when in create mode. The sqlo_exec() function call executes the SQL query string in the query variable using the connection created and pointed to by the database handle passed. If unsuccessful, the error_exit() function is called to print the Oracle error information and exit. If successful, the number of affected rows is returned.
Another database related function, dbconnect(), surprisingly enough, connects us to the database using the user, password and SID information passed to us on the command line. If successful, it prints the server version and return 0. If the connection or server version detection fails, it returns -1 and 1 respectively. The aptly named dbdisconnect() function similarly disconnects our established database connection.
The last of the major functions is query_customer_database(). This function assembles the SELECT SQL query to retrieve each of the active records from the customer_data table. At line 328, sqlo_open() is called using our given database handle and the SQL string that we created. sqlo_open() opens a new cursor for a query statement. If successful, it returns our statement handle; otherwise, we die using error_exit().
Next, we use sqlo_fetch() with our statement handle to fetch one row at a time. The second argument determines the number of rows to fetch. Using a while() loop, we iterate through each returned row and use sqlo_values() to return an array (v[]) of each of the columns in our given row. Each array member contains a column of the returned row. We then format and print the data to stdout using printf(). Once we have completed our while() statement, when sqlo_fetch() is no longer returning us rows, we return 0.
Our last utility function, error_exit(), simply prints the message it receives to stdout using printf(), along with the error returned from Oracle. It performs a rollback on the database using sqlo_rollback() and then uses sqlo_finish() to complete and close our database session and exits unsuccessfully.
With the included Makefile, you should be able to compile the cdata.c program simply by running make. The Makefile assumes that you have installed libsqlora8 either in /usr or /usr/local, so you may need to update add entries to $(INC) and $(L_FLAGS) for the locations of your libsqlora8 headers and libraries.
Finally, the simplest way to link against libsqlora8 is by using shared libraries, unless you are able to link the Oracle client OCI libraries statically to the libsqlora8 library. You may need to add several dependency libraries to $(LIBS) and $(LDFLAGS) if you decide to link your program statically. More information about this can be found on the libsqlora8 Web site (see Resources).
As I pointed out in the first part of this series, interaction with Oracle databases can be as simple as a few hundred lines of code. Between our C source and Makefile, we've written a mostly complete application with a decent amount of exception handling and comments in a total of 394 lines of code. To be more complete, we could add signal handling and some additional database consistency checks, but for our modest application these things can be done in a small amount of code as well. Additionally, as with Perl, it would be possible to link against other database connection libraries, such as the libmysqlclient library for MySQL or PostgreSQL's libpq, and use wrapper functions to make our interfaces more generic. But that's another topic for another time.
