oekroek oekroek - 22 days ago 6
C Question

Html file not inserting in Mysql table

I'm trying to insert a local html file 'test.html' into a table in mysql,where I'm working on a mysql server through embedded c code. however I'm getting an access denied error, when trying to insert that html file.

void clrstr(char *buf){
buf[0] = '\0';
}
int main(int argc, char *argv[]) {
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
MYSQL_FIELD *field;
char query[MAX_QUERY];
int x;
int i = 0;

char *records[] = {
"LOAD DATA INFILE 'test.html' INTO TABLE students"};

mysql_init(&mysql);
mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "mydb");
if (!mysql_real_connect(&mysql, HOSTNAME, USERNAME, PASSWORD,
DATABASE, 0, NULL, 0)) {
error("Could not connect to host.",&mysql);
}

clrstr(query);
/* varchar max*/
strcat(query, "create table students (id int not null auto_increment, fileaa text(65535),primary key(id))");

printf("Creating students table.\n");

/*
Create students table
*/
if(mysql_query(&mysql, query)){
error("Could not create table!",&mysql);
}

clrstr(query);

/*
Insert a records into the students table
*/

printf("Inserting students.\n");

for(x = 0; x < 1; x++){
if(mysql_query(&mysql, records[x])){
printf("Failure to insert: %s\n",records[x]);
error("Could not insert record",&mysql);
}
}

clrstr(query);

/*
Let us look at what we inserted
*/
strcpy(query, "select * from students order by last_name");

if(mysql_query(&mysql, query))
error("failed select 1",&mysql);

/*
Store results from query into res structure.
*/
if (!(res = mysql_store_result(&mysql))){
error("failed store 1",&mysql);
}

/*
print all results
*/
while ((row = mysql_fetch_row(res))) {
for (i=0; i < mysql_num_fields(res); i++){
printf("%s ", row[i]);
}
printf("\n");
}

/*
Drop the students table
*/

strcpy(query, "drop table students");

if(mysql_query(&mysql,query))
error("fail drop 1",&mysql);

/*
Finally close connection to server
*/
mysql_close(&mysql);

printf("All done\n");

return 0;
}


I tried adding this line :
GRANT FILE ON *.* TO 'james'@'localhost'
on this line:

strcat(query, "GRANT FILE ON *.* TO 'james'@'localhost',create table students (id int not null auto_increment, fileaa text(65535),primary key(id))");


But then I end up getting a syntax error on that line instead. I'm not sure how to go about inserting this file into my database when I keep getting permission errors.

Answer

Go into mysqladmin and run

mysql> use mysql;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO james@localhost IDENTIFIED BY 'yourpassword';
mysql> FLUSH PRIVILEGES;
mysql> \q

You should have permissions to all the tables in your db as long as you are connecting as james from localhost.