Programming for Oracle on Linux, Part 2

When Perl isn't fast enough, you can have applications interact with Oracle databases by using C.

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.

Prerequisites

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

Code Examination

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).

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Pls. break the long line in PRE-tag into two lines

Anonymous's picture

RyanOrdway, if you break this following line:

sprintf(sql_string, "SELECT username, first_name, last_name, address_line1, address_line2,
city, state, zipcode, phone, age FROM customer_data WHERE status=1");

into 2 lines, as in:

sprintf(sql_string, "SELECT username, first_name, last_name, address_line1, address_line2, "
"city, state, zipcode, phone, age FROM customer_data WHERE status=1");

you would avoid the horizontal scroll.

Re: Programming for Oracle on Linux, Part 2

Anonymous's picture

Seconding the previous post on bind variables...

What does your code do when one of the fields contains a single apostrophe (e.g. a last name of "O'Hara")? What about buffer overflow attacks (i.e. why aren't you checking for overflow when sprintf'ing into them)?

Beyond that, do you initialize your variables to zero-length strings? Why not make a struct to hold the customer record, something like:

typedef struct stCustomer {
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;
} CUSTOMER ;

and do something like the following when using it?

// ...get the command-line args
CUSTOMER cust ;
memset(&cust, 0, sizeof(CUSTOMER)) ;
...
case 'L':
snprintf(cust.last_name, sizeof(cust.last_name), "%s", optarg) ;
break ;
...

// ...create the record
create_customer_record(&cust) ;

?

BIND VARIABLES!

Anonymous's picture

When Perl is not fast enough...

The kind of SQL statements you have in your code will become a performance bottleneck significantly before Perl does (if that ever is an issue...)

You still have not heard about bind variables, have you?

In addition to making your code invulnerable to SQL injection attacks, it will be faster, especially on Oracle.

In any case, when you are developing on Oracle, you should move all your SQL into stored procedures and not have them in the application code. Much easier to maintain, faster to develop, better reusabilty, runs faster.

Just my two cents.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState