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 
