Howdy, Stranger!

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

Categories

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

  • bajangerrybajangerry Member Posts: 3
    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;
    }


  • bilderbikkelbilderbikkel Member Posts: 754
    : 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
  • bajangerrybajangerry Member Posts: 3
    : : 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.