Using Native API/Native Code vs SourcePro DB

Fast access to data is more important now than ever before. Native applications often can produce that extra bit of performance that makes the difference. But the push to leverage lower level APIs comes with its own set of challenges and costs. SourcePro has been designed to allow you to focus on solving your business problems instead of getting bogged down in unclear and error prone low-level API coding.

In this article, we will assume a fictional scenario where you and your team are responsible for developing and maintaining the backend C++ applications that interact with databases. For years, your team has written their application in the native low-level API calls. This is a critical long-lived application and as years go by and the team churns, you must constantly train up or find new developers who are familiar with the ins, outs, and esoterica of these APIs. It has not happened yet, but you have also had a few instances where upper management had your team estimating the cost of switching to a different database vendor. The significant effort of rewriting all that code in a new native API has killed the initiative before, but they keep asking...

What if there was a different way of writing your application that insulated your team from low-level APIs, gave you almost drop-in portability to other databases and was portable to other operating systems and architectures as well should either need ever arise.

Coding Comparison

For the purposes of this parable, we will assume that the application in question manages the accounts for a credit card (or similar) system.

First, we will examine the first application, that returns a list of accounts in the system:

int main() {
   auto connection = establish_connection();
   unsigned int count = 0;
   std::cout << "ID" << std::setw(11) << "Owner" << std::endl;
   list_accounts(connection, [&count](auto id, auto name) {
      std::cout << id << "\t" << name << std::endl;
      ++count;
   });
   std::cout << "Returned " << count << " accounts(s)." << std::endl;
   close_connection(connection);
}

Now we can dive deeper into the important lines of that example, the 3 functions:

1. Establishing a Connection to the Databaseestablish_connection()
2. Listing All Accountslist_accounts()
3. Resource Cleanupclose_connection().

Establishing a Connection to the Database

Establishing a connection to the database is a universal operation that every database based application will need to perform. Now, we will compare how this is accomplished using the MySQL native C API and SourcePro DB.

Using MySQL Native C API

All MySQL applications need to initialize the MySQL client library before using any other functions in the MySQL API. Additionally, for the application to be robust it should check for any errors:

if (mysql_library_init(0, NULL, NULL)) {
    fprintf(stderr, "%s\n", "could not initialize MySQL client library");
    exit(EXIT_FAILURE);
}

Once the client library has been initialized, a MYSQL object needs to be initialized for use with the rest of the API. Again, for robustness, we will check for and handle any errors. We also must remember to call a method to de-initialize the MySQL library:

MYSQL* mysql = mysql_init(NULL);
if (mysql == NULL || mysql_errno(mysql)) {
    mysql_library_end();
    fprintf(stderr, "%s\n", "MySQL Initialization Failed");
    exit(EXIT_FAILURE);
}

Having successfully initialized the MYSQL object, we can now make our connection to the database. Note that our error handling check has again grown in responsibility as the developer needs to remember to now deallocate the MYSQL object and de-initialize the MySQL Library:

if (!mysql_real_connect(mysql, "<server>", "<username>", "<password>",
                        "<db>", 3306, NULL, 0)) {
    fprintf(stderr, "%s\n%s\n", "MySQL Connection Failed", mysql_error(mysql));
    mysql_close(mysql);
    mysql_library_end();
    exit(EXIT_FAILURE);
}

At this point assuming no errors have occurred, a connection (encapsulated by the MYSQL object) would be returned from the establish_connection() method.

Now contrast this sequence of calls using SourcePro DB.

Using SourcePro DB

SourcePro DB has two available error handling mechanisms. The easiest to use is based on registering a callback function. We will leverage the callback mechanism for this example.

First, we will define the error handler method as a free function:

void
outputStatus(const RWDBStatus& aStatus) {
    std::cout << "Error code:       " << aStatus.errorCode() << std::endl
              << "Error message     " << aStatus.message() << std::endl
              << "Vendor error 1:   " << aStatus.vendorError1() << std::endl
              << "Vendor error 2:   " << aStatus.vendorError2() << std::endl
              << "Vendor message 1: " << aStatus.vendorMessage1() << std::endl
              << "Vendor message 2: " << aStatus.vendorMessage2() << std::endl;
}

We will then define the first line of our SourcePro DB based establish_connection() method and register outputStatus() as our error handler callback:

RWDBManager::setErrorHandler(outputStatus);

From this point on any errors that occur will automatically be handled via the error handler. The next thing we need to do is establish a connection to the database:

RWDBDatabase db = RWDBManager::datbase("MYSQL", "<server>", "<username>", 
                                       "<password>", "<db>");

And that is it. That is the entire function.

Assuming no errors have occurred, a valid connection represented by the RWDBDatabase object will be returned from the establish_connection() method.

A couple of things to note when establishing a connection to the database using SourcePro:

• With SourcePro DB callback error handling model there is no need to repeatedly check for errors, as that is handled automatically by SourcePro DB.
• There is no need to remember to deallocate or de-initialize objects or libraries as this is will also be handled automatically.

Listing All Accounts

Using MySQL C Native Interface

Using the MySQL C native interface, the list_accounts() method will have the following function signature:

template<typename Func>
void list_accounts(MYSQL* mysql, Func f);

To achieve the best performance possible our application will leverage the MySQL prepared statement API. The first call we need to make in our list_accounts() method then is to initialize a prepared statement handle:

MYSQL_STMT* stmt;
stmt = mysql_stmt_init(mysql);
if (!stmt) {
    fprintf(stderr, " mysql_stmt_init(), out of memory\n");
}

Having initialized the statement handle, we then need to prepare a SQL query for execution. In this example, we have constructed a SQL SELECT statement, specifying the id and name columns from the account table in the SELECT clause. Lastly, the order by clause has been specified on the id column. Even though we are not using any input parameters in this example, we still need to prepare a bound statement to allow the MySQL client to be ready to bind output parameters:

const std::string_view sql{ 
    "SELECT t1.id, t1.name FROM account t1 ORDER BY t1.id"
};
if ( mysql_stmt_prepare(stmt, sql.data(), sql.length()) ) {
    fprintf(stderr, "Error: %s (errno: %d)\n",
            mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
}

With the statement successfully prepared we can now execute the query:

if ( mysql_stmt_execute(stmt) ) {
    fprintf(stderr, "Error: %s (errno: %d)\n",
            mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
}

Now, we need to bind our output buffers. This allows the code to retrieve the result values for each column as their native type and avoid any costly type conversions. For each result column, we need to initialize the buffers to use for binding output data. For each result column type, we will specify the column type of the buffer (with the buffer_type attribute) and other type specific fields (null indicators, etc.):

int id;
MYSQL_BIND     bind[2];
unsigned long  length[1];
char           char_buffer[256];
bool           is_null[2];
bool           error[2];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = (char*)&id;
bind[0].is_null = &is_null[0];
bind[0].error = &error[0];

bind[1].buffer_type = MYSQL_TYPE_VAR_STRING;
bind[1].buffer = (char*)&char_buffer;
bind[1].is_null = &is_null[0];
bind[1].length = &length[0];
bind[1].buffer_length = 256;
bind[1].error = &error[0];

if ( mysql_stmt_bind_result(stmt, bind) ) {
    fprintf(stderr, "Error: %s (errno: %d)\n",
            mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
}

With binding complete, we can now iterate over all the rows of results, passing the entries for each column/row to the associated function:

while (!mysql_stmt_fetch(stmt)) {
    std::string_view name(char_buffer, length[0]);
    f(id, name);
}

Finally, deallocate the statement handle to complete the query processing:

if ( mysql_stmt_close(stmt) ) {
    fprintf(stderr, "Error: %s (errno: %d)\n",
            mysql_error(mysql), mysql_errno(mysql));
}

Note that for each of the above executions we needed to constantly be checking to see if an error occurred. It is also important to remember this is a very simplified schema with just a few columns of result data, imagine how much more complex it could be if it were a full blown schema with 15 rows of result data of different types.

Now look let us contrast this with SourcePro DB.

Using SourcePro DB

Using SourcePro DB, the list_accounts() function will have the following signature:

template<typename Func>
void list_accounts(const RWDBDatabase& db, Func f);

The first call we need to make in our list_accounts() method is to obtain an RWDBSelector from the database connection. This is the SourcePro DB abstraction for the SQL SELECT operation:

auto select = db.selector();

We also will need to obtain a local representation of the account table to interact with the RWDBSelector:

const auto accounts =  db.table("account");

Using the RWDBSelector and RWDBTable instances that have been created, we will build the query to execute. The operator<< is used along with the RWDBTable instance to specify which columns to place into the SELECT clause:

select << accounts["id"] << accounts["name"];

Use the orderBy() method on RWDBSelector to specify the column in the table to order on:

select.orderBy(accounts["id"]);

Obtain an explicit RWDBConnection to use with execution:

auto cn = db.connection();

Execute the select statement and create a RWDBBulkReader. This will be used to read multiple rows of result data back at a time (dictated by the size of the smallest RWDBTBuffer):

RWDBBulkReader rdr = select.bulkReader(cn);

Declare local buffers to store the output from the query into. Initialize to a size of 10 to allow up to 10 rows of data to be returned from the query at a time. Using the RWDBBulkReader::operator<< bind each buffer to the RWDBBulkReader:

RWDBTBuffer<int> id(10);
RWDBTBuffer<RWCString> owner(10);
rdr << id << owner;

Use repeated calls to the RWDBBulkReader::operator() to retrieve the next row of data from the RWDBTBuffer up to the number of rows.

In the for loop process the number of rows returned, passing the entries for each column/row to the associated function and then allow the while loop to retrieve another batch of return values from the query:

int numRead = -1;
while ( (numRead = rdr()) ) {
    for (int i = 0; i < numRead ; i++) {
        f(id[i], owner[i]);
    }
}

And again, that is it. No explicit checks for errors because we leveraged the SourcePro DB data manipulation language and no need to even write SQL (although SourcePro DB certainly enables you to if you need to, but that is a different article). You also do not need to precisely specify output buffer information or get tangled up in the native types and structs of the MySQL API.

With all that low level code abstracted away, it is much simpler and clearer for you and your developers to focus on implementing and maintaining your business and not spend time managing all the low level API accounting.

Resource Cleanup

Just as establishing a connection to the database is a universal operation that every database based application will need to perform, so is closing the connection. Let us look at the details of each implementation of close_connection().

Using MySQL C

MySQL requires a call be made to close the connection to the database:

mysql_close(connection);

Finally, MySQL also requires that a call be made to de-allocate resources for the client library:

mysql_library_end();

Now let us examine the SourcePro DB Code.

Using SourcePro DB

Using SourcePro DB the close_connection() function is empty. There is nothing to do. SourcePro DB automatically handles all resource allocation and cleanup for the MySQL client.

Conclusion

There are a lot of differences between using native code and SourcePro DB.

Developers need to know and understand SQL. Developers need to have a deep understanding of the MySQL native API to leverage it and more advanced features to increase performance (like parameter binding).

In the callback error checking paradigm that SourcePro DB has, there is a noticeable difference in how the application is structured. Because of this callback mechanism your application does not have to explicitly check for errors after every operation (although the API does allow you to if you so desire, which is also a different article).

In the SourcePro DB code there are no calls to initialize or de-initialize the MySQL libraries and handles, this is all automatically handled by SourcePro DB, relieving another potential error scenario when these calls are omitted or made in the wrong order. 

In the scenario where management has mandated that you must migrate your application from MySQL to SQL Server (or Oracle, or DB2, etc.). Every single application you have that uses the native API will have to be re-written using the new vendors API, requiring new training or experts, and possibly restructuring of the application logic to account for the different execution flow of different client libraries.

The usage of SourcePro DB shields you from all of that. Simply build the new access module for the new vendor you are switching to, change the connection line to use that new access module (“MYSQL” -> “MS_SQL” for example) and you are done.

Let SourcePro DB do the heavy lifting and take care of the “porting” for you.

Contact Us for an Evaluation

In part 2 and 3 of this series, we review the differences in native code versus using SourcePro DB when using these more advanced concepts:

• Part 2: Input parameter binding
• Part 3: Stored procedure execution

Related Resources

• Functional API Testing: How to Do it Right