How to save records of a mysql query in C++

Question

I’m using a C++ library that does the query in the database. My goal is to make a query, and save the data in a txt file, perhaps even manipulating to stay in json format. The problem is that I cannot recover the value of the records when saving the file. When I add the Row[i] value, it saves a binary value instead of ASCII characters. Apparently, even if it was in ASCII, I doubt it would return the field content, since the function mysql_fetch_row probably doesn’t do this. Can’t find a fetchArray function if it exists. Follow the code

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

using namespace std;

#define HOST "localhost"
#define USER "root"
#define PASS ""
#define DB "estoque"


MYSQL *conn;


int main()
{

    conn = mysql_init(NULL);



    if(mysql_real_connect(conn, HOST, USER, PASS, DB, 0, NULL, 0))
    {
        cout << "conectado \n";

    }
    //Select all table
    mysql_query(conn,"SELECT * FROM estoque"); 

    snprintf(buf, sizeof buf, "SELECT %s from %s", value, table);

    mysql_real_query(conn, buf, strlen(buf)); 

    // pointer of result
    MYSQL_RES *result; 

    MYSQL_ROW row; 
    unsigned int i;


    result = mysql_store_result(conn); 
    row = mysql_fetch_row(result);


    while ((row = mysql_fetch_row(result)) != NULL) 
    {   
    for (i=0; i<mysql_num_fields(result); i++) 

        cout << row[i] << endl;

    //  ############### ESCREVER EM ARQUIVO DE TEXTO O RESULTADO ############################

        std::ofstream Hypnos_FILE;
std::string TEXTO = row[i];
Hypnos_FILE.open("resultado.txt", std::ios::app);
if (Hypnos_FILE.is_open())
{
   std::cout << "Arquivo de texto aberto com sucesso!\n";

   Hypnos_FILE << TEXTO;

}
else
   std::cout << "Erro ao abrir arquivo de texto.";

Hypnos_FILE.close();                

    //  ############### ESCREVER EM ARQUIVO DE TEXTO O RESULTADO ############################

    } 
    // mysql_query(conn,"SELECT varchar1, varchar2 FROM estoque;");
    return 0;
}

Answer 1

First you need to check the return values properly, starting with mysql_init() and mysql_real_connect(), and abort the program if necessary. If this does not happen the result may be unexpected and may result in writing invalid data to your file as it is already happening.

Second you are discarding the first row of the table as there is a call to the function mysql_fetch_row() out.

Lastly, and perhaps the real reason of your problem, you are using the index (variable i) with an invalid value, resulting in a buffer overflow. Just put the writing of the file within the scope of your loop for to solve this problem.

Below is a simple code that works as you probably want:

#include <iostream>
#include <fstream>

#include <string.h>

#include <mysql/mysql.h>

using namespace std;

#define HOST "localhost"
#define USER "root"
#define PASS ""
#define DB "estoque"

MYSQL *conn;

int main()
{
    MYSQL *conn = mysql_init(NULL);
    if (!conn) {
        cout << "falha no mysql_init()" << endl;
        return 1;
    }

    conn = mysql_real_connect(conn, HOST, USER, PASS, DB, 0, NULL, 0);
    if (!conn) {
        cout << "falha no mysql_real_connect()" << endl;
        return 1;
    }

    cout << "conectado" << endl;

    const char *value = "*";
    const char *table = "banana";
    char buf[128];
    snprintf(buf, sizeof buf, "SELECT %s from %s", value, table);
    mysql_real_query(conn, buf, strlen(buf));

    MYSQL_ROW row;
    MYSQL_RES *result = mysql_store_result(conn);

    std::ofstream Hypnos_FILE;
    Hypnos_FILE.open("resultado.txt", std::ios::app);
    if (!Hypnos_FILE.is_open()) {
        std::cout << "Erro ao abrir arquivo de texto.";
        return 1;
    }
    std::cout << "Arquivo de texto aberto com sucesso!\n";

    unsigned int j = 0;
    while (((row = mysql_fetch_row(result)) !=NULL)) {
        cout << j << ":";
        Hypnos_FILE << j << ":";;
        j++;
        for (unsigned int i = 0; i < mysql_num_fields(result); i++) {
            cout << " " << row[i];
            Hypnos_FILE << " " << row[i];
        }
        cout << endl;
        Hypnos_FILE << endl;
    }

    Hypnos_FILE.close();

    mysql_close(conn);

    return 0;
}