February 28, 2022

Parameter Binding: Native API/Native Code vs SourcePro DB

Coding Best Practices

In the previous article, we walked through an example of how to query and retrieve data from a MySQL database using the native MySQL C API and then compared that to the clean simplicity of using the SourcePro DB API.

In this article, we are going to take that a step further and look at input parameter binding. Building off the system we discussed previously that manages the accounts for a credit card system, we are now going to implement a simple application that can add transactions to an account.

Coding Comparison
Adding Transactions
Conclusion

Coding Comparison

For this example, we will define a simple struct to represent the attributes of an individual transaction:

struct Transaction {
    std::string date;
    std::string payee;
    std::string amount;
};

Using a similar framework as the one in our previous example we will define a single executable that registers transactions:

int main() {
    auto connection = establish_connection();
    int account = 22;
    std::vector<Transaction> transactions = {
        { std::string("01/01/2021"), std::string("A Store"), std::string("19.99") },
        { std::string("03/03/2021"), std::string("The Store"), std::string("4.00") },
        { std::string("03/04/2021"), std::string("B Store"), std::string(".49") }
    };
    add(connection, account, transactions);
    close_connection(connection);
}

Review Part 1 of this series or documentation on the contents of the functions establish_connection() and close_connection() as they remain unchanged in this example.

This article will focus on the add() method. For convenience, we will define the transaction detail values inline, however they could easily be passed in from a calling function, JSON document, REST request, etc.

Adding Transactions

Using MySQL Native C API

Implemented with the MySQL C native interface, the add() method will have the following function signature:

void add(MYSQL* mysql, int account,
         const std::vector<Transaction>& transactions)

The application will leverage the MySQL prepared statement API and to leverage input binding. This both can provide a performance gain but more importantly helps guard against SQL injection attacks. By using placeholders rather than literally appending the column values into the SQL statement assists in guarding against a SQL injection attack (as we are treating the columns values as data and not SQL to execute).

The first call we need to make in the add() 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 the SQL query for execution. In this application we will construct a SQL INSERT statement specifying the columns we want to insert into and listing placeholders for the 4 input parameters:

std::string_view sql = {
    "IN-SERT into transactions(date, payee, amount, account) values (?, ?, ?, ?)"
};
if ( mysql_stmt_prepare(stmt, sql.data(), sql.length()) ) {
    fprintf(stderr, "[%d] Error: %s (errno: %d)\n",
            __LINE__, mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
}

Now we need to bind our input buffers so the application can send the local data to the database. Besides the previously mentioned SQL injection protection, by using input binding, we also avoid any costly data type conversions and loss of precision for non-string data.

The first thing we will do is make sure all our buffers are initialized to 0 with memset() calls. Then assign each field of transaction data to an input buffer, specifying the buffer_type of the data:

MYSQL_BIND     ibind[4];
memset(ibind, 0, sizeof(ibind));
unsigned long  length[4];
memset(length, 0, sizeof(length));
bool           is_null[4];
memset(is_null, 0, sizeof(is_null));
MYSQL_TIME     date_buffer;
memset(&date_buffer, 0, sizeof(date_buffer));
char           payee_buffer[256];
char           amount_buffer[256];

// Bind date input
ibind[0].buffer_type = MYSQL_TYPE_DATE;
ibind[0].buffer = (char*)&date_buffer;
ibind[0].is_null = &is_null[0];

// Bind Payee input
ibind[1].buffer_type = MYSQL_TYPE_VAR_STRING;
ibind[1].buffer = payee_buffer;
ibind[1].is_null = &is_null[1];
ibind[1].length = &length[1];

// Bind amount input
ibind[2].buffer_type = MYSQL_TYPE_VAR_STRING;
ibind[2].buffer = amount_buffer;
ibind[2].is_null = &is_null[2];
ibind[2].length = &length[2];

// Bind account input
ibind[3].buffer_type = MYSQL_TYPE_LONG;
ibind[3].buffer = &account;
ibind[3].is_null = &is_null[3];
ibind[3].length = &length[3];

With the input column types and buffers successfully declared, we can now bind the object we prepared:

if ( mysql_stmt_bind_param(stmt, ibind) ) {
    fprintf(stderr, "[%d] Error: %s (errno: %d)\n",
            __LINE__, mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
}

Now we can iterate over the remaining transactions and insert them. For each set of values, we copy the input to the buffers associated with the bind struct and execute the query:

for(size_t i =1; i < transactions.size(); i++) {
    // new date
    sscanf(transactions[i].date.data(), "%2u/%2u/%4u", 
           &date_buffer.month, &date_buffer.day, &date_buffer.year);
    // new payee
    length[1] = transactions[i].payee.length();
    ibind[1].buffer = (char*)transactions[i].payee.c_str();
    // new amount
    ibind[2].buffer = (char*)transactions[i].amount.c_str();
    length[2] = transactions[i].amount.length();

    if ( mysql_stmt_bind_param(stmt, ibind) ) {
        fprintf(stderr, "[%d] Error: %s (errno: %d)\n",
                __LINE__, mysql_stmt_error(stmt), mysql_stmt_errno(stmt));
    }

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

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: 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 input data, imagine how much more complex it could be if it were a full blown schema with 15 columns or more of input data of different types.

Let us now look contrast this with SourcePro DB.

Using SourcePro DB

With SourcePro DB, we have some additional types available so we can update the Transaction struct like so:

struct Transaction {
    RWDate date;
    RWCString payee;
    RWDecimalPortable amount;
};

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

void add(const RWDBDatabase& db, int account,
         const std::vector<Transaction>& transactions)

The first call we need to make in our add() method is to obtain an RWDBTable from the database to declare what we want to insert into.

auto select = db.selector();

Obtain an explicit RWDBConnection to use with execution:

auto cn = db.connection();

We will then create a RWDBBulkInserter. This encapsulates the SQL INSERT query for allowing insertion of multiple rows of data:

auto ins = transactionsTable.bulkInserter(cn);

Now we need to declare the RWDBTBuffer instances for each column of data we want to insert. We will declare them to have a size equal to the number of transactions we want to insert.

RWDBTBuffer dateBuffer(transactions.size());
RWDBTBuffer payeeBuffer(transactions.size());
RWDBTBuffer amountBuffer(transactions.size());
RWDBTBuffer accountBuffer(transactions.size());

The code will then loop over each of the transactions and assign the values for each transaction into the buffers:

for(size_t i =0; i < transactions.size(); i++) {
    dateBuffer[i] = transactions[i].date;
    payeeBuffer[i] = transactions[i].payee;
    amountBuffer[i] = transactions[i].amount;
    accountBuffer[i] = account;
}

And finally, we will shift each buffer into the RWDBBulkInserter and execute the inserter:

ins["account"] << accountBuffer;
ins["date"] << dateBuffer;
ins["payee"] << payeeBuffer;
ins["amount"] << amountBuffer;
ins.execute();

And that is it.

No explicit checks for errors because of the callback error handling mechanism.. No need to get tangled up in the native types and structs of the MySQL API. Because we leveraged the SourcePro DB data manipulation language, no need to even write SQL, although SourcePro DB certainly enables you to if you need to.

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

Conclusion

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

In the native code example, the developers need to know and understand SQL. Additionally, they will need to have a deep understanding of the MySQL native API to leverage it and more advanced features to increase performance (like parameter binding).

SourcePro DB code handles calls to initialize and de-initialize libraries and handles. With its SQL abstraction, it helps shields you from database vendor migrations.

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

If you missed the other articles on the differences in native code versus using SourcePro DB,you can read them now:

Part 1: Query and Retrieve Data
Part 3: Stored procedure execution

2024 State of Game Technology Report