Stored Procedures: Native API/Native Code vs SourcePro DB
March 24, 2022

Stored Procedures: Native API/Native Code vs SourcePro DB

Coding Best Practices

By

In the previous article, we walked through an example of how to use input parameter binding to send data to 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 final article, we are going to walk through building an example that can invoke a stored procedure with an input parameter of the name of a new account holder and an output parameter of the new account id assigned by the database. This example will again build off the system we discussed in part 1 that manages the accounts for a credit card system we are now going to examine code add transactions to an account.

Coding Comparison

For the purposes of this example, we are going to assume that the following stored procedure has been created in the MySQL database:

CREATE PROCEDURE `add_account`(in nameIn varchar(256), out idOut int)
begin
    insert into account(name) values (nameIn);
    SELECT id INTO idOut FROM account ORDER BY id DESC LIMIT 1;
end

Using a similar framework as in previous parts, we will have a single executable that creates a new account:

int main()
{
    const std::string newOwner("Account Owner");
    auto connection = establish_connection();
    int accountNumber = addAccount(connection, newOwner);
    std::cout << "Added account " << accountNumber 
              << " for user '" << newOwner << "'\n";
    close_connection(connection);
}

Please review part 1 of this series for documentation on the contents of the functions establish_connection() and close_connection() as they remain unchanged. For this article, we will focus on the addAccount() method.

In this example, we will define the new account name values inline however it could of course easily be passed in from a calling function, from a JSON document, REST request, etc.

Add a New Account

Using MySQL Native C API

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

int addAccount(MYSQL* mysql, const std::string& newOwner)

As we have throughout this series, we will leverage the MySQL Prepared Statement API. This means the first call we need to make in the addAccount() method 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 will be invoking a stored procedure on the MySQL database named add_account that requires an input parameter and an output parameter. Both parameters will be represented in the SQL call with the prepared statement placeholder. It is important to specify a placeholder for each parameter, regardless of the parameter input or output type:

std::string_view sql = { "call add_account(?, ?)" };
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));
}

We now need to bind buffers so the application can execute the procedure on the database. Even though the second parameter is an output parameter it still needs to be declared and bound as part of the mysql_stmt_bind_param call even though it won’t be accessed:

MYSQL_BIND ps_params[2];
memset(ps_params, 0, sizeof(ps_params));
unsigned long  length[1];
length[0] = newOwner.length();
ps_params[0].buffer_type = MYSQL_TYPE_VAR_STRING;
ps_params[0].buffer = (char*)newOwner.c_str();
ps_params[0].length = &length[0];
ps_params[0].is_null = 0;

int id;
ps_params[1].buffer_type = MYSQL_TYPE_LONGLONG;
ps_params[1].buffer = &id;
ps_params[1].length = 0;
ps_params[1].is_null = 0;

With the input column type and output type buffers successfully declared we can now bind the object we prepared:

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

Now that we have our parameter buffers bound we can execute the procedure:

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

After executing the procedure, we need to instruct the client to store the query result. This is necessary in order to determine if the output being processed is a result set or the output parameters (MySQL returns the output parameters of the procedure as the LAST result set from invoking the procedure):

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

Now we can examine the result and determine if we are processing the result set from a query within the procedure (which this example does not demonstrate) or if we are processing the output (or in/out) parameters. We also need to query if there are any fields (columns) of result data present:

        int num_fields = mysql_stmt_field_count(stmt);
        if ((mysql->server_status & SERVER_PS_OUT_PARAMS) && num_fields) {

To retrieve the output parameters, as you would with any result set, we must bind our output columns and then we can fetch the results:

MYSQL_BIND result_bind[1];
memset(result_bind, 0, sizeof(result_bind));

result_bind[0].buffer_type = MYSQL_TYPE_LONG;
result_bind[0].buffer = &id;
result_bind[0].buffer_length = sizeof(id);

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

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

The MySQL client returns one more empty result set after the output parameters are returned from the stored procedure (to indicate the end of result processing). It is important that result set at least be iterated over, or you will receive the error “Commands out of sync; you can't run this command now” from any subsequent calls on the statement handle:

// If you don't process any remaining results you will get
// Commands out of sync; you can't run this command now
// Error from the client
if ( mysql_stmt_next_result(stmt) > 0) {
    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));
}

That was a lot of effort. As has been mentioned before it is also important to remember this was a very simplified procedure with just a single input and single output parameter. Real world procedures often have many more parameters of varying data types and multiple result sets to process.

Let us now look contrast this with SourcePro DB.

Using SourcePro DB

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

int addAccount(const RWDBDatabase& db, const RWCString& newOwner)

The first call we need to make in our addAccount() method is to obtain an explicit RWDBConnection to use with execution:

auto cn = db.connection();

We will then create a RWDBStoredProc Instance. This encapsulates a database stored procedure:

auto add_account = db.storedProc("add_account");

Now that we have our representation of a stored procedure we will declare the output parameter and using the RWDBStoredProc::operator<< bind the parameters to the procedure:

int id = -1;
add_account << newOwner << &id;

That is all we must do to bind input and output parameters. We can now execute the procedure:

auto res = add_account.execute(cn);

And finally, we will retrieve the bound output parameter:

add_account.fetchReturnParams();

And that is it.

As we have seen throughout this series the abstractions provided by SourcePro DB have allowed us to avoid getting bogged down in native types and functions of the MySQL API. Because we leveraged the SourcePro DB stored procedure abstraction there is no need to write any SQL (although as has been mentioned before SourcePro DB certainly enables you to do so if you need to).

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.

Conclusion

As we have seen over this series, there are a lot of differences between using native code and SourcePro DB. Using native code requires that your 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). Additionally, they will need to be versed in all the caveats of that API. If your application needs to support multiple databases or move to a different database then your team will have to learn a new API and port your application.

As we have seen SourcePro DB code handles calls to initialize and de-initialize libraries and handles. With abstractions It helps shields you from database APIs and potential vendor migrations.

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

CONTACT US FOR AN EVALUATION

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

David Ritter

David Ritter

Principal Software Developer, SourcePro and HydraExpress at Perforce

With over 20 years of experience developing SourcePro, David continues to contribute to the ongoing stability, safety, and security of many of the products in the Perforce portfolio.