How to read data from a text file and put it in a mysql database in c

Question

I have written a c program that I hoped would read data from a text file and put it in a database. The problem is that the code doesn't execute all iterations of a while loop in the main function as I hoped it would

#Explanation of the algorithm# The program opens the file that contains the records and reads the whole file into a buffer. Then it chops the string into records seperated by . It then gets each record and composes an sql insert statement then writes the record to the database

Here is the code:

#include <stdio.h>
#include <string.h>
#include <mysql.h>
#define MAX_LEN 1000
/*
#Explanation of the algorithm#
The program opens the file that contains the recored and reads the whole file into a buffer. Then it chops the string 
into records. it then gets each record and composes an sql insert statement then writes the record to the database
*/

// function to change the date format to the correct database format
const char* date_formater(char date[],char search_for,char new)
{
    int i;
    for(i = 0; i <= strlen(date); i++)
    {
        if(date[i] == search_for)  
        {
            date[i] = new;
        }
    }
    return date;
}

// send data to the database
void send_data_to_database(char str[],MYSQL *con)
{
    /*Get data to send to the database*/
    char data[8][100];
    char delimeter[] = "";
    char *token = strtok(str,delimeter);

    int i = 0;
    while(token != NULL)
    {
        strcpy(data[i],token);
        token = strtok(NULL,delimeter);
        i++;
    }
    char o_id[] = "0";
    strcpy(data[7],o_id);
    // generate sql insert statement
    char closingbrac[] = ")";
    char single_quote[] = "'";
    char comma[] = ",";
    char sql[200] = "INSERT INTO patient VALUES(";
    
    for(int j = 0;j<=7;j++)
    {
        switch(j)
        {
            case 0:
                strcat(sql,data[j]);
                strcat(sql,comma);
                break;
            case 2:
                strcat(sql,single_quote);
                const char* real_date = date_formater(data[j],'/','-'); // change date to databse required format
                strcat(sql,real_date);
                strcat(sql,single_quote);
                strcat(sql,comma);
                break;
            case 7:
                strcat(sql,data[j]);
                strcat(sql,closingbrac);
                printf("%s
",data[j] );
                break;
            default:
                strcat(sql,single_quote);
                strcat(sql,data[j]);
                strcat(sql,single_quote);
                strcat(sql,comma); 
        }
    }
    printf("%s
",sql);
    /*Send SQL query*/
    if(mysql_query(con,sql))
    {
        fprintf(stderr, "%s
",mysql_error(con));
        exit(1);
    }
}

/*control function*/
int main()
{
    MYSQL *conn;

    char *server = "localhost";
    char *user = "root";
    char *password = "MasterY";
    char *database = "covid";

    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(1);
    }
    // open the file containing the patient records
    FILE *fptr;
    fptr = fopen("/home/elijah/Desktop/plan.txt","r");

    if(fptr == NULL)
    {
        printf("Error!");   
        exit(1);
    }
    //open the file containing records for reading
    char string[MAX_LEN + 1];
    // get all the file contents and store them in a string
    fscanf(fptr,"%[^$]",string);
    printf("Text file contents:
%s
",string);
    //puts(string);
    printf("String: 
%s
",string);
    char delim[] = "
";

    char *ptr = strtok(string,delim);

    while(ptr != NULL)
    {
        // function call to send data to the database
        send_data_to_database(ptr,conn);
        ptr = strtok(NULL,delim);
    }

    /*close connection*/
    mysql_close(conn);

    //closing the file and clearing it
    fclose(fptr);
    FILE *file;
    file = fopen("/home/elijah/Desktop/plan.txt","w");
    fclose(file);
    printf("Execution
");
    return 0;
}

The compiler executes the main function up to the while loop. And funnily it only executes one iteration and stops after the first iteration.

Yet I hoped it would continue and finish all the different lines in the text file It executes the send_data_to_database(ptr,conn); completely.

Here is the text file. Every line is a record that has to be inserted into a database

6   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
7   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berry
8   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
9   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berr

Here is a copy of the output: It only inputs one record into the db. Yet I hoped that it would insert all. I don't know what the problem is but I know something isn't right. That's why I am here.

elijah@elijah-HP-255-G6-Notebook-PC:~/Desktop$ ./mysql
Text file contents:
6   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
7   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berry
8   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
9   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berry

String: 
6   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
7   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berry
8   Okello Ivan     2020/10/11  False Positive  Assymptomatic   M   John
9   Leku Davis  2020/02/03  Positive    Assymptomatic   M   Malik Berry

0
INSERT INTO patient VALUES(6,'Okello Ivan ','2020-10-11 ','False Positive ','Assymptomatic ','M ','John',0)
Execution

This is the database table SQL CREATE statement for a MYSQL Database.

CREATE TABLE `patient` (
  `idpatient` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) NOT NULL,
  `date-of-id` date DEFAULT NULL,
  `covid-status` varchar(45) DEFAULT NULL,
  `nature` varchar(45) DEFAULT NULL,
  `gender` varchar(30) DEFAULT NULL,
  `officer_name` varchar(45) DEFAULT NULL,
  `o-id` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idpatient`,`fullname`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

I compile the source code using this command:

gcc -o mysql -I/usr/include/mysql addtodatabase.c -L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -latomic -lssl -lcrypto -ldl Because I used #include <mysql.h>

Someone please help me.

#UPDATE

I found out that I could use a csv file to push that data to the database since it's already in tabular format. But I am finding trouble using the LOAD DATA statement.

Here is the updated code

#include <stdio.h>
#include <string.h>
#include <mysql.h>
#define MAX_LEN 1000
/*
#Explanation of the algorithm#
The program opens the file that contains the recored and reads the whole file into a buffer. Then it chops the string 
into records. it then gets each record and composes an sql insert statement then writes the record to the database
*/

// function to change the date format to the correct database format
const char* formater(char date[],char search_for,char new)
{
    int i;
    for(i = 0; i <= strlen(date); i++)
    {
        if(date[i] == search_for)  
        {
            date[i] = new;
        }
    }
    return date;
}

// send data to the database
void send_data_to_database(MYSQL *con)
{
    // SQL QUERY    
    char sql[] = "LOAD DATA LOCAL INFILE '/home/elijah/Desktop/cron_job/patients.csv' INTO TABLE patient COLUMNS TERMINATED BY ',' LINES TERMINATED BY '
' IGNORE 1 LINES (`idpatient`,`fullname`,`date-of-id`,`covid-status`,`nature`,`gender`,`officer_name`,`o-id`)";
    /*Send SQL query*/
    if(mysql_query(con,sql))
    {
        fprintf(stderr, "%s
",mysql_error(con));
        exit(1);
    }
    else
    {
        printf("Data Sent
");
    }
}

/*control function*/
int main()
{
    MYSQL *conn;

    char *server = "localhost";
    char *user = "root";
    char *password = "MasterY";
    char *database = "covid";

    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(1);
    }
    // open the file containing the patient records
    FILE *fptr;
    fptr = fopen("/home/elijah/Desktop/cron_job/patients.txt","r");

    if(fptr == NULL)
    {
        printf("Error!");   
        exit(1);
    }
    //open the file containing records for reading
    char string[MAX_LEN + 1];
    // get all the file contents and store them in a string
    fscanf(fptr,"%[^$]",string);
    printf("Text file contents:
%s
",string);
    //puts(string);
    printf("String: 
%s
",string);

    // put the data into csv file format and write it 
    const char *formated_csv_string = formater(string,'',',');
    char csv_string[] = "idpatient,fullname,date-of-id,covid-status,nature,gender,officer_name,o-id
";

    strcat(csv_string,formated_csv_string);
    printf("%s
",csv_string); 

    FILE *csv_file;

    csv_file = fopen("/home/elijah/Desktop/cron_job/patients.csv","w");

    if(csv_file == NULL)
    {
        printf("Crap
");
    }
    else
    {
        printf("Yayyyyy
");
        fprintf(csv_file, "%s",csv_string);
    }
    send_data_to_database(conn);

    /*close connection*/
    mysql_close(conn);

    //closing the file and clearing it
    fclose(fptr);
    // FILE *file;
    // file = fopen("/home/elijah/Desktop/cron_job/patients.txt","w");
    // fclose(file);
    printf("Execution
");
    return 0;
}

The LOAD DATA SQL statement does not work. What could be the problem. Here is the output

elijah@elijah-HP-255-G6-Notebook-PC:~/Desktop$ ./mysql
Text file contents:
1   Okello Ivan     2020/08/13  False Positive  Assymptomatic   M   Mary    0


String: 
1   Okello Ivan     2020/08/13  False Positive  Assymptomatic   M   Mary    0


idpatient,fullname,date-of-id,covid-status,nature,gender,officer_name,o-id
1,Okello Ivan ,2020/08/13 ,False Positive ,Assymptomatic ,M ,Mary,0


Yayyyyy
Data Sent
Execution

question from:https://stackoverflow.com/questions/65932982/how-to-read-data-from-a-text-file-and-put-it-in-a-mysql-database-in-c

Answer 1

Waitting for answers