Catch MySQL error in c++

Question

In C++, I am using mysql.h libraries and I do not manage to catch MySQL errors (for example, failure to insert due to conflict in primary key).

I have tried

#include <mysql.h>
// ...
try{
    res = mysql_perform_query(conn, sqlIn);
} catch (...) {
// ...
}

but it still does not avoid aborting with:

MySQL query error : Duplicate entry

I am running the compiled c++ program using PuTTy interface and as the program aborts, it reproduces MySQL's error (regardless of whether I use TRY CATCH or not).
I have not found any reference to specific exception codes for MySQL use with catch statement.

Apparently, C++ (using this library mysql.h) does not catch MySQL callback errors even with the "blanket" syntax (catch (...)). Since this syntax should catch ALL exceptions, yet doesn't in this case, I am at a loss to understand what is happening here. How can I catch the SQL exceptions?

Basrhin

How do you know that is the failure message if you do not catch the exception? – Concettaconcettina

zetcode.com/tutorials/mysqlcapitutorial – Septuagenarian

@Concettaconcettina i am running the compiled c++ program using PuTTy interface and as the program aborts, it reproduces MySQL's error (regardless of whether i use TRY CATCH or not). – Basrhin

Have you tried calling mysql_errno() between your calls to MySQL? If you're not handling the SQL errors (not talking exceptions here) and then go on to call more MySQL code, it's possible that MySQL just aborts the app a la exit(1). – Monogyny

@Dynguss the code works when i am NOT attempting insert a row with a duplicate value for a UNIQUE or PRIMARY key. but will examine your suggestion, nevertheless. thanks! – Basrhin

I thought the mysql interface was C. So it is probably not throwing an exception. – Lazaretto

@LokiAstari the error seems to be a callback directly from mysql. install mysql++ MIGHT be a solution. if i find anything, will let you know. – Basrhin

Looks like I wasn't the only one that couldn't figure out how you got a C api to throw exceptions. Use what you found out and edit your question to get it re-opened. You're welcome. – Septuagenarian

@tenterhook the program immediately aborts (on the first attempt) if MySQL throws an error (even something simple, like attempting to insert a duplicate record [with a unique key]). alas, there is no "between calls to MySQL". – Basrhin

@MooingDuck thanx 4 edit, makes the question more incisive. – Basrhin

Answer 1

The mysql C library does not throw any excptions; it merely, like most other C libraries, sets up error info in a common place ( like errno ) and returns a status. Its up to your client code to check the return and throw an error/exception.

If you need an easy fix for your problem, try mysql++ (AKA mysqlpp). It is a piece of software that I can vouch for; solid as a rock! http://tangentsoft.net/mysql++/

Buccinator

i am seriously considering changing libraries. i have no idea of the impact regarding necessary refactoring. i guess i'll try mysql++ - do you know for a fact that i would be able to catch mysql errors using mysql++ library instead of mysql.h? – Basrhin

Yes I am sure; 100% possible! Read section "3.4. Exceptions" on page tangentsoft.net/mysql++/doc/html/userman/tutorial.html . In fact it supports both use-cases; for example, if you do not want an exception thrown from within a part of your code, you can use NoExceptions class to turn off exceptions temporarily within its scope. good luck! – Buccinator

Answer 2

Try using some c++ wrapper around the mysql C library. e.g. http://mysqlcppapi.sourceforge.net/

C doesnt throw exceptions you have to check via mysql_errorno function.

Rotorua

you got upvoted because the concept of the wrapper is correct AND you were the first to suggest using a wrapper, in the first place. i could not find my way with this wrapper and ended up using mysql++ (therefore awarding bounty to @kingstonian). thank you VERY much. – Basrhin

Answer 3

I wont suggest Mysql C++ libraries for two reasons 1.It is difficult to get support. 2.Slow compared to Mysql C library

you can use mysql_error() and mysql_errno() API's to know the errors and manually through exceptions

Pyrophosphate

yes, performance is a key issue. the problem with mysql_error() is that i cant capture it. when i attempt the query (with duplicate unique key), the program aborts. any suggestions? – Basrhin

you can use mysql_errno() to catch error, please refer the below sample try { if(mysql_query(QUERY) != SUCCESS) { int mysql_error_num = mysql_errno(); throw mysql_error_num ; } } catch (int) { ...... ...... } – Pyrophosphate

i am using it thru mysqlpp wrapper. havent compared overhead time, but ran a simulation with 60K connections/threads per minute and didnt crash. – Basrhin

Now, are you able to catch Duplicate entries? – Pyrophosphate

Answer 4

as per kingstonian's suggestion, i used the mysql++ wrapper around mysql (for installation in ubuntu, see my recipe answering another SO question)

the code below was used to test error handling, where a duplicate value was inserted in the key (Id_Target = 9). adapt to your own needs, using the appropriate insert for your DB structure.

#include <string.h>
#include <stdlib.h>
#include <errno.h>
#include <stdio.h>
#include </usr/include/mysql++/mysql++.h>


// sudo g++ -o /temp/zdelMySQLpp01c $(mysql_config --cflags) /home/ubuntu/zdelMySQLpp01c.cpp $(mysql_config --cflags --libs) -l pthread -std=c++0x -g -L/usr/lib/mysql -lmysqlclient -lmysqlpp

using namespace mysqlpp;
using namespace std;

//MySQL type
struct connection_details {
        char *server;
        char *user;
        char *password;
        char *database;
};

int main(int argv, char** argc){

// MySQL connection (global)
struct connection_details mysqlD;
mysqlD.server = (char *)"localhost";  // where the mysql database is
mysqlD.user = (char *)"root";       // the root user of mysql   
mysqlD.password = (char *)"XXXXXX"; // the password of the root user in mysql
mysqlD.database = (char *)"test";   // the databse to pick

// connect to the mysql database
mysqlpp::Connection conn(false);
if (conn.connect(mysqlD.database, mysqlD.server, mysqlD.user, mysqlD.password)) {
    //printf("ALOALO funcionou conexao\n");
    mysqlpp::Query query = conn.query("INSERT INTO test.target (Id_Target, Ds_Target, Ds_Target_Name, Ds_Target_PWD, Ds_Target_Email, Ds_Target_Icon) VALUES ('9', 'test', 'name', 'pass', 'email', NULL)");
        if (mysqlpp::StoreQueryResult res = query.store()) {
                cout << "We have:" << endl;
                for (size_t i = 0; i < res.num_rows(); ++i) {
                    cout << '\t' << res[i][0] << endl;
                }
        } else {
                cerr << "Failed to get item list: " << query.error() << endl;
                return 1;
    }
        return 0;
}
}

Basrhin