Ulf Wendel

MySQL Connector/C++: working with Stored Procedures

2009/05/12 by admin

Stored Procedures have been a MySQL 5.0 Key Feature. We have marketed them as an Enterprise Feature. Of course, the MySQL Connector/C++ supports them as far as possible. Using them is easy as long as you respect some limitations that still exist. The MySQL driver for C++ inherits those limitations from its underlying C-API. Which in turn cannot offer features not supported by the MySQL Client Server protocol. However, good news is: most things work just fine!

Setting up a connection for Stored Procedures

More good news first: no extra set up required with Connector/C++.

Stored Procedures can be invoked using the SQL command CALL. Unlike most other SQL statements, CALL statements can return more than one result set. CALL returns at least one result set to indicate the call status. In addition it can return further result sets returned by the statements executed by the stored procedure that has been invoked by CALL. To be able to process the additional result sets that may be returned by a stored procedure, you must set the CLIENT_MULTI_RESULTS flag upon connect. If you forget this step, the MySQL Server will bail at you when you use CALL to execute a stored procedure that returns a result set in addition to the CALL status code: "Error 1312 (0A000): PROCEDURE proc_name can’t return a result set in the given context" .

The MySQL Connector/C++ will set this connection property implicitly for every connection. As a result you can call all kinds of stored procedures without having to take care of connection flags. However, with other MySQL drivers, you may have to take care.

Beware of the differences between CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS. CLIENT_MULTI_STATEMENTS enables the execution of statement strings which contain multiple statements separated by semicolons, for example, " SELECT * FROM t1; SELECT * FROM t2". Setting the CLIENT_MULTI_STATEMENTS connection flag will automatically enable CLIENT_MULTI_RESULTS as well. The MySQL driver for C++ does not set CLIENT_MULTI_STATEMENTS by default. CLIENT_MULTI_STATEMENTS should be used with a grain of salt as it makes SQL injection attacks a little bit easier.

Calling a Stored Procedure

From an application programmers view there are six different cases need to be discussed. As you will see, the basic syntax is the same for statements and prepared statements, which makes things easier.

  • Using a statement to execute CALL to invoke…
    • … a stored procedure that does not return a result set on its own
    • … a stored procedure that executes statements return result sets
    • … a stored procedure with OUT parameter
  • Using a prepared statement to execute CALL to invoke…
    • … a stored procedure that does not return a result set on its own
    • … a stored procedure that executes statements return result sets
    • … a stored procedure with OUT parameter

Code examples

I will give code examples for all cases. To make the examples shorter and more readable, I only show the "hearth" of the the complete program code. You can copy the quot;hearth" into try-catch block of the complete code example to get a working sample program. I have tested my snippets on Linux and run them against HEAD (the latest development sources).

Please note that we found and fixed a bug related to prepared statements and stored procedures while writing this blog posting. The bug may be in the GA release but it is fixed in the development tree. 

/**
* Basic example of creating a stand alone program linked against Connector/C++
*
* This example is not integrated into the Connector/C++ build environment.
* You must run "make install" prior to following the build instructions
* given here.
*
* To compile the standalone example on Linux try something like:
*
* /usr/bin/c++
*   -o standalone
*   -I/usr/local/include/cppconn/
*   -Wl,-Bdynamic -lmysqlcppconn
*    examples/standalone_example.cpp
*
* To run the example on Linux try something similar to:
*
*  LD_LIBRARY_PATH=/usr/local/lib/ ./standalone localhost root root
*
*/

/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>

/*
  Include directly the different
  headers from cppconn/ and mysql_driver.h + mysql_util.h
  (and mysql_connection.h). This will reduce your build time!
*/
#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

#define EXAMPLE_HOST "localhost"
#define EXAMPLE_USER "root"
#define EXAMPLE_PASS ""
#define EXAMPLE_DB "test"

using namespace std;

/**
* Usage example for Driver, Connection, (simple) Statement, ResultSet
*/
int main(int argc, const char **argv)
{
	string url(argc >= 2 ? argv[1] : EXAMPLE_HOST);
	const string user(argc >= 3 ? argv[2] : EXAMPLE_USER);
	const string pass(argc >= 4 ? argv[3] : EXAMPLE_PASS);
	const string database(argc >= 5 ? argv[4] : EXAMPLE_DB);

	cout << endl;
	cout << "Connector/C++ standalone program example..." << endl;
	cout << endl;

	try {

	/* Insert code snippet here! */

	} catch (sql::SQLException &e) {
		/*
		The MySQL Connector/C++ throws three different exceptions:

		- sql::MethodNotImplementedException (derived from sql::SQLException)
		- sql::InvalidArgumentException (derived from sql::SQLException)
		- sql::SQLException (derived from std::runtime_error)
		*/
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		/* Use what() (derived from std::runtime_error) to fetch the error message */
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;

		return EXIT_FAILURE;
	}

	cout << endl;
	cout << "... find more at http://www.mysql.com" << endl;
	cout << endl;
	return EXIT_SUCCESS;
}

JDBC difference: no CallableStatement

Although the MySQL Connector/C++ follows the JDBC 4.0 in great detail it does not offer each and every class available with JDBC. The JDBC class CallableStatement is not implemented in the C++ driver because neither the MySQL Workbench team has asked for it nor is it required by Connector/OpenOffice.org. If you think it is urgently needed, please let us know. On the other hand, as far as I know, the primary purpose of the extra level of abstraction in the JDBC standard is to have a common interface for all kinds of databases. But the MySQL Connector/C++ supports only one database – the MySQL Server. Therefore is no need for abstraction.

The most basic case: using statement, no extra result set

The most basic case is to execute a stored procedure that returns no extra result set using CALL. No extra result set, no result set generated by any statements executed within the stored procedure, means no extra work for you. Calling a stored procedure the the one shown below, is in no way different from, for example, executing an INSERT statement. You can use the execute() method from the statement class to perform the CALL statement.

sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());
		
stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT)");
stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p() BEGIN INSERT INTO test(id) VALUES (123); END;");

stmt->execute("CALL p()");

std::auto_ptr< sql::ResultSet > res(stmt->executeQuery("SELECT id FROM test"));
			
cout << "\t... running 'SELECT id FROM test'" << endl;
while (res->next()) {
	cout << "\t... id: " << res->getInt("id") << endl;
}

When executing a stored procedure with CALL the client will get at least one result set. Even the basic procedure above has returned a result set. The first result set always holds the return status of the CALL statement itself. As you saw, no special API calls are needed to process it. Everything is done implicitly for you.

Using statement, extra result sets returned by the stored procedure

When dealing with Stored Procedures that execute statements which return result sets, you must use additional API calls to process the extra result sets. If you do not use the two additional API calls, you will most likely get Commands out of sync; you can’t run this command now error messages once you try to run other statements on the connection.

However, the "pattern" the "phrase" you need to use is “do – stmt::getResultSet() – process result – while stmt::getMoreResults()”. You will find similar calls in other MySQL drivers. The pattern is always the same. 


sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());

stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
stmt->executeUpdate("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')");

stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id, label FROM test ORDER BY id ASC; END;");

stmt->execute("CALL p()");
std::auto_ptr< sql::ResultSet > res;
do {
	res.reset(stmt->getResultSet());
	while (res->next()) {
		cout << "\t... id: " << res->getInt("id");
		cout << ", label: " << res->getString("label") << endl;
	}
	cout << endl;
} while (stmt->getMoreResults());

Waiting for OUT parameter

Five years after the introduction of stored procedure in MySQL, there is still no explicit API support for OUT parameter of stored procedures in any GA version of MySQL. Neither for statements nor for prepared statements. Future versions of MySQL 5.4 will hopefully fix this by backporting the feature from MySQL 6.0.8. Robin gives an outlook on output parameters in prepared statements in his article A Quick Look at MySQL 5.4.

For the time being, the best you can do piggy-back OUT values on a MySQL user variable. 


sql::Driver * driver = get_driver_instance();
std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());

stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p(IN ver_in VARCHAR(25), OUT ver_out VARCHAR(25)) BEGIN SELECT ver_in INTO ver_out; END;");

stmt->execute("CALL p('input', @output)");
std::auto_ptr< sql::ResultSet > res(stmt->executeQuery("SELECT @output AS _reply"));
while (res->next())
	cout << "... @output = " << res->getString("_reply") << endl;

Prepared statement, no extra result set

CALL can be prepared as of MySQL 5.0 (October 2005). It cannot be prepared with MySQL 4.1. However, MySQL 4.1 is a bit dated. The MySQL Lifecycle Calendar shows that active support for MySQL 4.1 has ended more than two years ago and the extended support ends this year. As of MySQL 5.0 there is no magic when invoking a stored procedure that returns no extra result sets using CALL and prepared statements. The basic syntax is the same as for the "normal" statements.

Although the CALL statement can be prepared as of MySQL 5.0, there are still others that cannot be in MySQL 5.1 or MySQL 6.0: {CREATE | DROP | RENAME} PROCEDURE are among them. The full list of supported statements can be found the MySQL Reference Manual. 


sql::Driver * driver = get_driver_instance();

std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());
std::auto_ptr< sql::PreparedStatement >  pstmt;
std::auto_ptr< sql::ResultSet > res;

stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT)");
stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p() BEGIN INSERT INTO test(id) VALUES (123); END;");

pstmt.reset(con->prepareStatement("CALL p()"));
for (int i = 1; i <= 3; i++) {
	cout << "Loop " << i << " - should return " << i << " time[s] '123'" << endl;
	pstmt->execute();
	res.reset(stmt->executeQuery("SELECT id FROM test"));
	while (res->next()) {
		cout << "\t... id: " << res->getInt("id") << endl;
	}
}

Prepared statement, extra result sets

Using prepared statements and CALL to invoke a stored procedure which runs statements that return result sets to the caller is not possible before MySQL 6.0.8. The MySQL Connector/C++ is implemented as a wrapper of the MySQL C API. The C API does not support this scenario before MySQL 6.0.8. The MySQL driver for C++ inherits this limitation. The limitation is also true for every other MySQL driver that wraps the MySQL C API.

As an aside note: PHP users, which use the MySQL native driver for PHP (mysqlnd) can use multiple result sets with prepared statement. On the contrary, PHP won’t be able to use multi results with PHP no matter which MySQL C API version will be used.

When I says that it is not possible I mean that you either cannot fetch all result sets or your connection becomes unusable: Commands out of sync; you can't run this command now (MySQL error code: 2014, SQLState: HY000 ). The following example will compile. And it will fetch the result set. But the connection becomes unusable. This is because of the wrong usage of the API. It would be correct to use the phrase shown when demonstrating this case for "normal" statement: "do – stmt::getResultSet() – process result – while stmt::getMoreResults()". Though, you cannot use it because the underlying C API calls for implementing this in Connector/C++ are not available before MySQL 6.0.8.

WRONG API-usage, connection becomes unusable.

sql::Driver * driver = get_driver_instance();

std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());
std::auto_ptr< sql::PreparedStatement >  pstmt;
std::auto_ptr< sql::ResultSet > res;

stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p(IN retval VARCHAR(25)) BEGIN SELECT retval AS _answer; END;");
pstmt.reset(con->prepareStatement("CALL p(\"Crash me!\")"));
res.reset(pstmt->executeQuery());
while (res->next()) {
	cout << "\t... _answer: " << res->getString("_answer") << endl;
}
stmt->execute("DROP PROCEDURE IF EXISTS p");



nixnutz@ulflinux:~/src/connector-cpp-bzr/trunk> rm standalone ;  /usr/bin/c++ -o standalone -I/usr/local/include/cppconn/ -Wl,-Bdynamic -lmysqlcppconn examples/standalone_example_sp.cpp &&  ./standalone tcp://127.0.0.1 root root test

Connector/C++ standalone program example...

        ... _answer: Crash me!
# ERR: SQLException in examples/standalone_example_sp.cpp(main) on line 102
# ERR: Commands out of sync; you can't run this command now (MySQL error code: 2014, SQLState: HY000 )

Prepared statement, OUT parameter

As discussed above, explicit API support for OUT parameter is not available in the C-API before MySQL 6.0.8. You can, however, use the user variable trick to work around this limitation. 

std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);
std::auto_ptr< sql::Statement > stmt(con->createStatement());
std::auto_ptr< sql::PreparedStatement >  pstmt;
std::auto_ptr< sql::ResultSet > res;

stmt->execute("DROP PROCEDURE IF EXISTS p");
stmt->execute("CREATE PROCEDURE p(OUT retval VARCHAR(50)) BEGIN SELECT \"Ulf joined MySQL more than 5 years ago\" INTO retval; END;");

pstmt.reset(con->prepareStatement("CALL p(@output)"));
pstmt->execute();

pstmt.reset(con->prepareStatement("SELECT @output AS _answer"));
res.reset(pstmt->executeQuery());
while (res->next()) {
	cout << "\t... _answer: " << res->getString("_answer") << endl;
}
stmt->execute("DROP PROCEDURE IF EXISTS p");

Stored Procedures? Yes, but beware of Prepared Statement before MySQL 6.0.8…

MySQL Connector/C++ wraps the C API. All limitations of the C API apply to the C++ driver. Combining CALL, Prepared Statements and Stored Procedures may or may not work depending on the type of the Stored Procedure, the MySQL Server version and the version of the C API used. When using "normal" statements you are on the safe side and everything works. Although not using Prepared Statements may be easier it also means giving up some of the advantages of Prepared Statements.

The majority of the existing limitations are not a matter of the C API. It is the MySQL Server and its MySQL Client Server protocol that does not yet support each and every feature when using Prepared Statements.

Comments are closed.