Home > A Tiny MySQL++ Tutorial; C++ and MySQL; MySQL++ Example

A Tiny MySQL++ Tutorial; C++ and MySQL; MySQL++ Example

I wrote a post about how to install MySQL++ and I thought I will write a quick tutorial on how to use it too.

This is a very basic MySQL++ program and it’s very self explanatory:

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    try {
        Connection conn(false);
        conn.connect("DB NAME", "DB HOST probably localhost", "DB USER", "DB PASS");
        Query query = conn.query();
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }
 
    return (EXIT_SUCCESS);
}

This will connect to your database and creates a query object ready to go. Save this as test.cpp

To compile this, you will have to create a Makefile in the same folder, so create a file and name it “Makefile” (no quotes) and add these to it:

CXX := g++
CXXFLAGS := -I/usr/include/mysql -I/usr/local/include/mysql++
LDFLAGS := -L/usr/local/lib -lmysqlpp -lmysqlclient -lnsl -lz -lm
EXECUTABLE := main
 
all: test
 
clean:
	rm -f $(EXECUTABLE) *.o

If you get funny errors, make sure there are no extra spaces in this file, and there is only one “tab” behind the line: rm -f $(EXECUTABLE) *.o

Now, you can make and run this by doing:
make
./test

Now, I’m going to show you how to execute some queries and you can go ahead and experiment on your own tables.

Here is a INSERT query followed by a SELECT; this will hopefully cover a lot, because INSERT is a type of query that doesn’t return anything and you need to escape values in order to INSERT.
SELECT on the other hand returns rows, although there are 3 ways that they can be done but here is a simple way that works for me.

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    try {
        Connection conn(false);
        conn.connect("DB NAME", "DB HOST probably localhost", "DB USER", "DB PASS");
        Query query = conn.query();
 
        /* To insert stuff with escaping */
        query << "INSERT INTO some_table " <<
                     "VALUES (" <<
                     "'', " << /* This is left empty because the column is AUTO_INCREMENT */
                     "\"" << escape << some_var_that_contains_some_value << "\"" <<
                     ");";
        query.execute();
        /* That's it for INSERT */
 
        /* Now SELECT */
        query << "SELECT * FROM biz LIMIT 10";
        StoreQueryResult ares = query.store();
        for (size_t i = 0; i < ares.num_rows(); i++)
           cout << "Name: " << ares[i]["name"] << " - Address: " << ares[i]["address"] << endl;
 
        /* Let's get a count of something */
        query << "SELECT COUNT(*) AS row_count FROM biz";
        StoreQueryResult bres = query.store();
        cout << "Total rows: " << bres[0]["row_count"];
 
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }
 
    return (EXIT_SUCCESS);
}

Again, whenever you want to run your code do:
make
./test

I wrote this because something like this would help myself a lot.

Good Luck

Update

query.reset();

Will reset the query object; this is useful when you are (in your program) generating a MySQL query but must discard it and generate another query (based on some condition).

Filed under: C/C++,MySQL,Programming Posted by: Hamid

19 Comments »

jonathan:

Thanks. This is easier to understand than the tutorial in the mysql++ documentation.

Comment
Shikhar:

Thanks. This makes everything a lot quicker to migrate from another language.

I get Segmentation fault at runtime when using this code structure… ?

Comment
Codehead:

Hey, that’s so strange, I just striped this piece out of my own code to just show how you can do various things, I will have to try it and see what the problem is…

Comment
maximus:

Thanks man.
It was quick and really helpful.

Comment
rcman:

man u hellped me ALOT
just did it in java but C++ looked like chinise befor
TY TY

Comment
jno:

Great tutorial! A lot better than off. documentation.

Thanks.

Comment
FuseBox:

Great tutorial!
But when i try to run i get the same segmentation fault Shikhar gets. Has anyone found a fix?

Comment
Codehead:

I will fix this segmentation fault and update my post, it’s probably because you are trying to access bres[0]["row_count"]…

Comment
misiek:

I am trying get this working but I ‘ve stocked on error

error: ‘StoreQueryResult’ is not a member of ‘mysqlpp’

I’ve included
-lmysqlpp -lmysqlclient -lnsl -lz -lm

not sure what is wrong
please heeeelp
thanks

Comment
misiek:

So I have tried Result

Result ares = query.store();

that worked out, not sure why this should be replaced to StoreQueryResults, but any way now have different error :

error: no match for ‘operator[]’ in ‘ares[0]’

what is going on ?!?!, wrrrrr

Please help

Comment
misiek:

Using Result instead of StoreQueryResult I got working this:

Result result = query.store();

if (result) {
mysqlpp::Row row;
mysqlpp::Row::size_type i;

for (i = 0; i append(row.raw_data(1));

}
}

I still investigate why StoreQueryResult is not enabled.

Comment
misiek:

uh something went wrong with the post above, please moderate this post, the pasted code looks different.

Comment
misiek:

sorry for bombarding this post here my need more help. I was able to solve my above issue with missing StoreQueryResult thing. I was using 2.3.2 of mysql++ lib had to reemerge to unstable 3.0.9 (gentoo OS) my netbeans still highlight the StoreQueryResult as “Unable to resolve identifier” but its g++ compile just fine.

I have another problem, I us qt gui and need to display results from DB in text area so use widget to make this happen but see the error please on below code

StoreQueryResult ares = query.store();
for (size_t i = 0; i append(ares[i]["login"]);
}

error: no matching function for call to ‘QTextEdit::append(const mysqlpp::String&)’

how to convert that ??

Comment
misiek:

just letting you know how to convert mysqlpp::string to QString
```
StoreQueryResult ares = query.store();

for (size_t i = 0; i append("sss" + QString::fromLatin1(ares[i]["login"]));
}
```
hope above code will be formatted correctly this time.

Comment
Craig:

Thanks, its really very clear how to use the MYSQL++ libs.

Thank you

Comment
Floyd:

Thank you!

This was more helpful than the documentation on MySQL++’s site.

How would you store a field’s value into a std::string variable though? I passed it through a stringstream since I couldn’t figure it out.

Thanks again

Comment
Tyler:

Whats the parameter on the Connection object for?

Comment
shahidnx:

very nice….

Comment
Jose:

Hi! when i try co execute the code GDB give me

Program received signal: “EXC_BAD_ACCESS”.

i’m using Xcode 3.2 – 64-bits

Comment