C language variable type float and integer pass in mysql

Question

I'm a beginner in C and mysql programing.For some days now I am trying to write float and integer values that i get from sensors to a database in mySQL.So far i'm just getting an error "too many arguments to function ‘mysql_query’" and " expected ‘const char *’".Below is my simple code.

int main()
{
    int var1 = 1;
    float var2 = 5.1;
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;

    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, host, user, pass, dbname,port, unix_socket, flag))
    {
        fprintf(stderr, "\nError: %s [%d]\n",mysql_error(conn),mysql_errno(conn));
        exit(1);
    }
    printf("Connection successful!\n"); 
    mysql_query(conn,"INSERT INTO variables (var1) VALUE  ('%d');",var1);   
    mysql_query(conn, mysql_query );
}

You really should read up on how the functions work before making wild expectations of them. — tadman
– tadman, Commented Aug 25, 2016 at 18:37

David Ranieri · Accepted Answer · 2016-08-25 19:03:00Z

2

It's a nice idea, but mysql_query doesn't work with variable arguments.

You need to store the query in a buffer:

char buff[1024];

snprintf(buff, sizeof buff, "INSERT INTO variables (var1) VALUES  ('%d');",var1);

and then you can call mysql_query with this buffer:

mysql_query(conn, buff);

EDIT: As pointed out by @PaulOgilvie: Notice VALUES instead of VALUE in the query.

edited Aug 25, 2016 at 19:03

answered Aug 25, 2016 at 18:44

David Ranieri

41.2k8 gold badges58 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Ogilvie Over a year ago

I also believe that VALUE should be VALUES.

Iharob Al Asimi · Accepted Answer · 2016-08-25 19:14:25Z

2

You should use a prepared statement, which would also take care of the types and all that (assuming conn is a valid connection object)

MYSQL_STMT *stmt;
MYSQL_BIND params[1];
const char *query;

// This is necessary or the program will crash
memset(params, 0, sizeof(params));

query = "INSERT INTO variables (var1) VALUES (?)";
stmt = mysql_stmt_init(conn);

params[0].buffer = &var1;
params[0].buffer_type = MYSQL_TYPE_LONG;
if (stmt == NULL)
    exit(1); // Ideally handle the error and solve the problem
             // but for simplicity ...
if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0)
    exit(1);
if (mysql_stmt_bind_param(stmt, params) != 0)
    exit(1);
if (mysql_stmt_execute(stmt) != 0) {
     // Ideally print mysql's error
    fprintf(stderr, "an error occurred\n");
}
mysql_stmt_close(stmt);

This is the good safe way to do this, and also you can reuse the prepared statement as many times as you want and they promise it will be faster and more efficient because the query is prepared so the execution plan is known and you don't need to use the snprintf() which by the way should be checked for errors and also, you should check if the query did fit into the target array, read the documentation for that.

Also, you don't need to worry about escaping strings or anything. It will all be handled transparently for you. As long as you use the correct type and of course, specify the length of strings.

Note that you can bind parameters and results too in SELECT queries.

edited Aug 25, 2016 at 19:14

answered Aug 25, 2016 at 19:08

Iharob Al Asimi

53.1k6 gold badges66 silver badges100 bronze badges

2 Comments

alukez Over a year ago

Program works like a charm, but i do not understand how do i pass more than one var integer, and also i need to pass some float values. I have tried usingquery = "INSERT INTO variables (var1,var2) VALUES (?,?)"; but with no luck. stmt = mysql_stmt_init(conn); params[0].buffer = &var1; params[0].buffer_type = MYSQL_TYPE_LONG;params[1].buffer = &var2; params1].buffer_type = MYSQL_TYPE_LONG;

Iharob Al Asimi Over a year ago

@AnžeLukežič It's not hard. Just add more params with types MYSQL_TYPE_LONG and MYSQL_TYPE_FLOAT I think. You should be very careful not to pass the wrong type because undefined behavior will occur. And also, remember to increase the size of the params array according to your needs (how many parameters total).

Kate66 · Accepted Answer · 2016-08-25 18:53:53Z

0

Are you sure you don't need "VALUES" here instead of "VALUE":

mysql_query(conn,"INSERT INTO variables (var1) VALUE  ('%d');",var1);

answered Aug 25, 2016 at 18:53

Kate66

922 bronze badges

2 Comments

David Ranieri Over a year ago

Yes, it should be VALUES instead of VALUE, but mysql_query doesn't accept variable arguments.

Kate66 Over a year ago

Yeah, guess there are lots of problems. Just mentioned the first show-stopper I saw.

Collectives™ on Stack Overflow

C language variable type float and integer pass in mysql

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related