SQL INSERT question

Guys, i am struggling with something here that I hope you can help with... I am not a strong C programmer by any means but I need to create this program in C. I an retrieving data fro a text file, line by like, then I need to break it down into portion (using substr as there are no delimiters in the file) and enter the substrings into a MySQL database. What I am having difficulty with at the moment is getting the string entered into the database. I am starting by just trying to save the entire line to the database but I can't figure out the process, here is the program so far:

#include
#include
#include
#include
#include
using namespace std;

int main () {
string line;
string str;
ifstream myfile ("smdr.txt");
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char *server = "localhost";
char *user = "user";
char *password = "password";
char *database = "smdr";
conn = mysql_init(NULL);

/* Connect to database */
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
{
fprintf(stderr, "%s
", mysql_error(conn));
exit(0);
}
if (myfile.is_open())
{
while (! myfile.eof() )
{
getline (myfile,line);
str = line.substr (2,5);
cout << str << endl;

/* send SQL query */
if (mysql_query(conn, "INSERT INTO rawdata (rawdata) VALUES (str)"))
{
fprintf(stderr, "%s
", mysql_error(conn));
exit(0);
}
myfile.close(); }

res = mysql_use_result(conn);

/* Release memory used to store results and close connection */
mysql_free_result(res);
mysql_close(conn);
}

else cout << "Unable to open file";

return 0;
}

This compiles fine on my Ubuntu Linux box and will print the "str" value from each line but it doesn't save anything to the database other than "str" obviously I have to do something to get the "str" converted to a VALUE that the database can accept... there is my problem! Any help greatfully accepted!

Comments

  • Well, got no response from this forum (very disappointing) but I figured it out on my own eventually, for those interested please see the below code. Please note you need to compile with the mysqlclient included.

    #include
    #include
    #include
    #include
    #include
    using namespace std;

    int main () {
    string line, mon, day, stime, pm, hrs, mins, sec, callp, leaddigit, callno, callp2, transf, acccode;
    char sql[300];
    ifstream myfile ("smdr.txt");
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char *server = "localhost";
    char *user = "root";
    char *password = "G3rf13ld!";
    char *database = "smdr";
    conn = mysql_init(NULL);


    /* Connect to database */
    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
    {
    fprintf(stderr, "%s
    ", mysql_error(conn));
    exit(0);
    }
    /* Get data from test file*/
    if (myfile.is_open())
    {
    while (! myfile.eof() )
    {
    getline (myfile,line);
    mon = line.substr (1,2);
    day = line.substr (4,2);
    stime = line.substr(7,5);
    pm = line.substr(12,1);
    hrs = line.substr(14,2);
    mins = line.substr(17,2);
    sec = line.substr(20,2);
    callp = line.substr(23,4);
    leaddigit = line.substr(29,3);
    callno = line.substr(33,26);
    callp2 = line.substr(61,4);
    transf = line.substr(67,4);
    acccode = line.substr(72,12);
    // cout << hrs << endl;
    /* send SQL query */
    sprintf(sql, "INSERT INTO import (month,day,time,PM,hrs,mins,sec,callingparty,leaddigit,calledno,calledparty,transferext,accountcode)VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",mon.c_str(),day.c_str(),stime.c_str(),pm.c_str(),hrs.c_str(),mins.c_str(),sec.c_str(),callp.c_str(),leaddigit.c_str(),callno.c_str(),callp2.c_str(),transf.c_str(),acccode.c_str());
    if (mysql_query(conn,sql))
    {
    fprintf(stderr, "%s
    ", mysql_error(conn));
    exit(0);
    }
    res = mysql_use_result(conn);
    /* Release memory used to store results and close connection */

    }
    mysql_free_result(res);
    mysql_close(conn);
    myfile.close(); }

    else cout << "Unable to open file";

    return 0;
    }


  • : Well, got no response from this forum (very disappointing) but I
    I think you'd have had more response if you'd posted it on the 'normal' C/C++ Board, as the question wasn't Linux specific. (I only check out this Board when I am really really bored...)

    See ya,
    bilderbikkel
  • : : Well, got no response from this forum (very disappointing) but I
    : I think you'd have had more response if you'd posted it on the
    : 'normal' C/C++ Board, as the question wasn't Linux specific. (I only
    : check out this Board when I am really really bored...)
    :
    : See ya,
    : bilderbikkel

    Ahh... thanks, yet more useful information!!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories