Developing a c++ solution that uses mysql

maplesyrup · 2019-07-30 04:55:36

I've been trying to figure out how to put all the pieces together to compile a program that connects and reads from a mysql (mariadb) database. I was wondering what the best practices were and if you guys had some example code, as the one on the MySQL C++ Connector page use some libraries I haven't been able to find on Arch. Here's some code I made:

#include <iostream>
#include <mysql/mysql.h>

using namespace std;

int main() {
    MYSQL *Connection;
    Connection = mysql_init(NULL);
    Connection = mysql_real_connect(Connection, "customip", "customusername", "custompasswd", "customdbname", 0, NULL, 0);
    
    if(Connection){
        cout << "Passed!";
    }else{
        cout << "Not passed!";
    }
    
    MYSQL_RES *res_set;
    MYSQL_ROW row;

    mysql_query(Connection, "SELECT * FROM Users");   //<---- Segmentation fault as the connection object is not correctly initialized with mysql_real_connect

    res_set = mysql_store_result(Connection);

    return 0;
}

I compile it with the following line:

g++ *.cpp -o exec -L/usr/include/mysql -lmysqlclient

I can't even seem to connect to my server, while everything else does (vscode extension, mysql workbench, etc). Is this the best way to interact with mariadb? Is this code even close to being relevant?

Trilby · 2019-07-30 12:40:52

maplesyrup wrote:

I can't even seem to connect to my server

"seem to"? What does this mean? What is the result, error, etc? Do you mean real_connect returns NULL?

Note that by using the same MYSQL * for both the init and real_connect calls you mask your ability to actually do any error checking with mysql_error. Don't do that. If the real_connect fails, you still want a pointer to your mysql object to get the error info from. That could tell you what is actually happening rather than just speculating.

EDIT: I see in your comment you say the query results in a seg faut, but you likely also get the "not passed" output, right? If you check a result like you do in that 'if' block, do not let the program proceed if there was a failure. Using mysql_error could tell us what resulted in the NULL pointer being returned from real_connect, but if you don't bother and just try to push on anyways, of course things will go to crap.

EDIT: tangential note - I'd not name your program after a common shell command.

Try this:

#include <iostream>
#include <mysql/mysql.h>

using namespace std;

int main() {
    MYSQL *db, *conn;
    MYSQL_RES *res_set;
    MYSQL_ROW row;

    db = mysql_init(NULL);
    if (!db) return 1;

    conn = mysql_real_connect(db, "customip", "customusername", "custompasswd", "customdbname", 0, NULL, 0);
    if (!conn) {
        fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&db));
        mysql_close(conn);
        return 2;
    }

    mysql_query(conn, "SELECT * FROM Users");
    res_set = mysql_store_result(conn); 
    /* TODO: do something with res_set */
    mysql_free_result(res_set);
    mysql_close(conn);
    return 0;
}

Last edited by Trilby (2019-07-30 13:03:42)

maplesyrup · 2019-07-30 16:15:50

Indeed my output was "Not passed!". Thanks for the advice on the exec name, I'll keep that in mind for the next time! Your code makes sense, I'll try and debug tonight to see what goes wrong when connecting to the server and I'll post another response.

maplesyrup · 2019-07-31 01:09:22

Your answer worked for me! Although I did receive an error when putting the port in my hostname. 192.168.0.178:3306 didn't work while 192.168.0.178 did. Also, just for the record,

fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&db));

should be replaced by

fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(db));

Last edited by maplesyrup (2019-07-31 01:11:23)

Trilby · 2019-07-31 01:22:53

Yes, sorry, the & came from an example that was using a MYSQL type rather than a pointer. But there was no difference in the functioning of my code and yours, mine would only give information about the error. But of course putting the port name in the hostname parameter will not work. If you want to specify a port, use the port parameter.

Note I don't use C++ and have never done any mariadb/mysql programming before (other than in PHP); I just read the man pages which are readily available. E.g.:
https://dev.mysql.com/doc/refman/5.7/en … nnect.html

In any case, if it's working now, please remember to edit your first post to prepend [SOLVED] to the title.

Last edited by Trilby (2019-07-31 01:23:34)

maplesyrup · 2019-07-31 02:10:44

Actually, I was also trying to see if this code could work: https://dev.mysql.com/doc/connector-cpp … ple-1.html

#include <stdlib.h>
#include <iostream>


#include "mysql_connection.h"

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

using namespace std;

int main(void)
{
cout << endl;
cout << "Running 'SELECT 'Hello World!' »
   AS _message'..." << endl;

try {
  sql::Driver *driver;
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;

  /* Create a connection */
  driver = get_driver_instance();
  con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
  /* Connect to the MySQL test database */
  con->setSchema("test");

  stmt = con->createStatement();
  res = stmt->executeQuery("SELECT 'Hello World!' AS _message");
  while (res->next()) {
    cout << "\t... MySQL replies: ";
    /* Access column data by alias or column name */
    cout << res->getString("_message") << endl;
    cout << "\t... MySQL says it again: ";
    /* Access column data by numeric offset, 1 is the first column */
    cout << res->getString(1) << endl;
  }
  delete res;
  delete stmt;
  delete con;

} catch (sql::SQLException &e) {
  cout << "# ERR: SQLException in " << __FILE__;
  cout << "(" << __FUNCTION__ << ") on line " »
     << __LINE__ << endl;
  cout << "# ERR: " << e.what();
  cout << " (MySQL error code: " << e.getErrorCode();
  cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}

cout << endl;

return EXIT_SUCCESS;
}

I haven't found a post yet that explains how you would go about compiling that... I also searched for the libraries they included in that example but I couldn't find anything else than mysql-connector-c++.

The error I'm getting is the following:

fatal error: mysql_connection.h: No such file or directory

I tried looking online for the library I'm missing but I didn't find much.

Last edited by maplesyrup (2019-07-31 02:16:57)

Trilby · 2019-07-31 02:41:22

WTF? Well that has nothing to do with anything else in this thread.

But I don't know how you can fail to find the library. Again I have zero experience with any of this, but I just read the very page you linked to, and followed a link within it to get to the source for mysql connector/c++ here:
https://dev.mysql.com/downloads/connector/cpp/

And a search of the AUR immediately returned an aur package that's already been prepared for it:
https://aur.archlinux.org/packages/mysq … r-c%2B%2B/

So now that I've solved your original problem, and your follow up completely unrelated problem, both of which by simply reading some brief and clear documentation and doing a package search ... well, given that, I don't think I'll help any more here. Don't become a help vampire:
http://slash7.com/2006/12/22/vampires/

Arch Linux

#1 2019-07-30 04:55:36

Developing a c++ solution that uses mysql

#2 2019-07-30 12:40:52

Re: Developing a c++ solution that uses mysql

#3 2019-07-30 16:15:50

Re: Developing a c++ solution that uses mysql

#4 2019-07-31 01:09:22

Re: Developing a c++ solution that uses mysql

#5 2019-07-31 01:22:53

Re: Developing a c++ solution that uses mysql

#6 2019-07-31 02:10:44

Re: Developing a c++ solution that uses mysql

#7 2019-07-31 02:41:22

Re: Developing a c++ solution that uses mysql

Board footer