LOAD DATA in C The used command is not allowed

Question

I have a problem with LOAD DATA in C.

When I execute the LOAD DATA command in the Command line it works just fine, but when I try the same thing in C it says:

The used command is not allowed with this MySQL version.

Here's my code:

    #include <my_global.h>
    #include <mysql.h>
    #include <stdio.h>

void finish_with_error(MYSQL *con)
{
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

int main(int argc, char **argv)
{
    MYSQL *con = mysql_init(NULL);

    if (con == NULL)
    {
        fprintf(stderr, "%s\n", mysql_error(con));
        exit(1);
    }

    if (mysql_real_connect(con, "localhost", "", "", "testdb", 0, NULL, 0) == NULL)
    {
        finish_with_error(con);
    }else{
    printf("MySQL client version: %s\n", mysql_get_client_info());
    }



    if (mysql_query(con, "LOAD DATA LOCAL INFILE '/home/lien/Dropbox/StageTolsma/1eOpdracht/TxtBestanden/01_0021_200912100000.txt' INTO TABLE input FIELDS TERMINATED BY ';=' LINES TERMINATED BY '\n;'"))
    {
        finish_with_error(con);
    }else{
        printf("File was uploaded to the database\n");
    }

    mysql_close(con);
    exit(0);
}

see [previous SO post on this](http://stackoverflow.com/questions/7715494/load-data-local-infile-mysql-php-issue). does this address your issue — amdixon, Jun 16 '15 at 09:52
nope. my query is working when i use it in the command line, but not in my c code. So I guess there is something else wrong, but I can't figure out what : — Lienwyn, Jun 16 '15 at 10:09
what mysql version ? [per this](http://stackoverflow.com/questions/14348257/using-mysql-load-statment-in-php-fails-but-doing-it-via-command-line-works), can you set MYSQL_OPT_LOCAL_INFILE.. — amdixon, Jun 16 '15 at 10:14
the mysql version is 5.5.43, i already did some stuff like putting local-infile = 1 in /etc/mysql/my.cnf — Lienwyn, Jun 16 '15 at 10:18
try adding `mysql_options(con,MYSQL_OPT_LOCAL_INFILE, 1);` before connecting — amdixon, Jun 16 '15 at 12:29
replicated this error, and adding `mysql_options(con,MYSQL_OPT_LOCAL_INFILE, 0);` it succeeds ( note: 0 not 1 as suggested above ). try this out and see if it solves.. — amdixon, Jun 16 '15 at 13:29
Thank you very much it worked will update the code so if anybody else has this problem he can check it out :3 — Lienwyn, Jun 18 '15 at 07:25
@JessicaSchlapbach Do not change the code that you gave in your question. Instead, create a new answer and add the changed code there. Otherwise, any visitors here may be very confused about what IS working and what's NOT working. — gustafbstrom, Jun 18 '15 at 07:37
awesome - ill edit the answer to put in the links to the mysql docs around this — amdixon, Jun 18 '15 at 08:11

score 0 · Accepted Answer · edited Jun 18 '15 at 08:49

Now it's working with change to explicitly set MYSQL_OPT_LOCAL_INFILE option.

Useful links

MYSQL_OPT_LOCAL_INFILE option info

MYSQL_OPT_LOCAL_INFILE (argument type: optional pointer to unsigned int)

If no pointer is given or if pointer points to an unsigned int that has a nonzero value, the LOAD LOCAL INFILE statement is enabled.

so issue fixed by giving a null value ( equivalent to no pointer given ) :

mysql_options(con, MYSQL_OPT_LOCAL_INFILE, 0);

working code

#include <my_global.h>
    #include <mysql.h>
    #include <stdio.h>

void finish_with_error(MYSQL *con)
{
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

int main(int argc, char **argv)
{
    MYSQL *con = mysql_init(NULL);
    mysql_options(con, MYSQL_OPT_LOCAL_INFILE, 0);  

    if (con == NULL)
    {
        fprintf(stderr, "%s\n", mysql_error(con));
        exit(1);
    }

    if (mysql_real_connect(con, "localhost", "", "", "testdb", 0, NULL, 0) == NULL)
    {
        finish_with_error(con);
    }else{
    printf("MySQL client version: %s\n", mysql_get_client_info());
    }



    if (mysql_query(con, "LOAD DATA LOCAL INFILE '/home/lien/Dropbox/StageTolsma/1eOpdracht/TxtBestanden/01_0021_200912100000.txt' INTO TABLE input FIELDS TERMINATED BY ';=' LINES TERMINATED BY '\n;'"))
    {
        finish_with_error(con);
    }else{
        printf("File was uploaded to the database\n");
    }

    mysql_close(con);
    exit(0);
}

LOAD DATA in C The used command is not allowed

1 Answers1