mysql prepared statement : Update query

Question

I am trying to execute a C program, using mysql C API, connecting to mysql with an update query and I am not getting any compilation or linking errors , but rows are not getting updated in the db table.

When I run this code I am getting empty values updated in emp. status field

#define STRING_SIZE 256

char* eStatus,myeStatus;

int myempid,empid;


int i;
for(i = 0; i < 5 ; i++){
const char* sqlQuery = "update employee_info set estatus = ? where empID = ?";
    if (mysql_stmt_prepare(stmt, sqlQuery, strlen(sqlQuery))) {
            fprintf(stderr, " mysql_stmt_prepare(), update failed\n");
            fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
            return -1;
          }

memset(param, 0, sizeof(param)); /* zero the structures */

if (info.state == 2)

      eStatus = "present";

else
      eStatus = "absent";

empid = i;
// Init param structure
            // Select
            param[0].buffer_type = MYSQL_TYPE_STRING;
            param[0].buffer = (void *) &eStatus;
            param[0].buffer_length = STRING_SIZE;
            param[0].is_null = 0;
            param[0].length = &str_length;

            param[1].buffer_type = MYSQL_TYPE_SHORT;
            param[1].buffer = (void *) &myempID;
            param[1].buffer_length = STRING_SIZE;

            param[1].is_null = 0;
            param[1].length = 0;

        myeStatus = eStatus;
    myempid = empid;
               if (mysql_stmt_bind_param(stmt, param) != 0) {
            fprintf(stderr, " mysql_stmt_bind_param() failed\n");
            fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
            return -1;
        }
               /* Execute the statement */
        if (mysql_stmt_execute(stmt)) {
            fprintf(stderr, " mysql_stmt_execute(), failed\n");
            fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
                return -1;
        }

} // end of for loop

Table schema in mysql

empid INT(11)

estatus varchar(10)

I am not able to figure out why status is not getting updated in mysql table. Is it a mismatch of datatypes, or values are not binded properly to sqlquery?
Any clue? Thanks.

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

You can find here : Writing into mysql database from a single board computer using c a complete example on how to use MYSQL C API to perform queries, if you still have some trouble, please post the whole code.

edited May 23 '17 at 12:03

Community

1
1

answered Sep 05 '12 at 22:02

TOC

4,326
18
21

1

The OP is asking for prepared statements. Where do they occur in the example you've linked? – Leo Feb 05 '13 at 12:18

score 0 · Answer 2 · edited Feb 20 '19 at 01:02

0

Why are you trying to use "where empID = ?". If you want it to run for every employee simply omit the where clause. If it is for a specific employee, then his id should be there.

There might be more issues, but this was the first one i found.

You might verify by trying to execute the same query on mysql command line prompt.

Edit: I also don't see any database connection being established and any info related to that. Some thing like

MYSQL *conn = mysql_init(NULL);

*conn = mysql_real_connect(*conn, DB_HOST, DB_USER, DB_PASS, DB_NAME, 0, NULL, flags);

if (*conn != NULL)
{
    printf("Connection Successfull\n");
    status = 0;
}

edited Feb 20 '19 at 01:02

jww

97,681
90
411
885

answered Sep 05 '12 at 18:52

fkl

5,412
4
28
68

I have those database connection code properly written and tested...i wrote where clause because I am updating multiple rows for empid 0,1,2,3,4 ... and there respective status I am getting in a C variable constant... based on that value I have to update status – Kerol Karper Sep 05 '12 at 19:05
Do you have an init statement called? Since you are using prepared statements, that would be stmt = mysql_stmt_init(mysql); before any other call? http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-execute.html – fkl Sep 05 '12 at 19:17
yes..everything is present...and same code is working with static queries like select from table , create table ... both are working properly.... just this update is giving the problem as I have to bind values to query dynamically – Kerol Karper Sep 05 '12 at 19:19
@Kerol so you mean an actual where clause in your case has a empid = 1 or 2 (some number) instead of the question mark? – fkl Sep 05 '12 at 19:20
1

param[1] fields will tie the value to 2nd '?' in the query during bind and param[1] will bind the estatus to it. – Kerol Karper Sep 05 '12 at 19:21

mysql prepared statement : Update query

2 Answers2