Hi everybody,
These days I was writing a program with C API for MYSQL in Linux,and I encountered a lot of problems .And I begin to understand what Java and .NET bring to we programmers.
first I want to thank our authors for writing this great book,it was excellent. No doubt it was one of the best book I have ever read. Well done :)
Anyway,it can not cover all the details of the subjects it discuss, so I am asking a question here:
It was about Prepared Statements for updating using MYSQL_BIND:
Code:
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
int main(int argc, char *argv[])
{
MYSQL *conn; // connection to MySQL server
MYSQL_STMT *stmt; // data structure for the prepared statement
MYSQL_BIND bind[3]; // description of the parameters
char *update = "UPDATE Roles SET name=? , description=? WHERE RoleId=?";
char name_buf[32];
char description_buf[256];
unsigned long name_len, description_len;
int roleId;
int roleId_is_null;
// connection to MySQL
conn = mysql_init(NULL);
mysql_options(conn, MYSQL_READ_DEFAULT_FILE, "");
if(mysql_real_connect(
conn, hostName, userName, passwd,
dbName, PortNumber,UnixSocketName,Flags) == NULL) {
fprintf(stderr, "sorry, no database connection ...\n");
return 1;
}
// create statement structure
stmt = mysql_stmt_init(conn);
// initialize prepared statement
mysql_stmt_prepare(stmt, update, strlen(update));
// define parameters of the prepared statement
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = FIELD_TYPE_VAR_STRING;
bind[0].buffer = name_buf;
bind[0].buffer_length = 32;
bind[0].length = &name_len;
bind[1].buffer_type = FIELD_TYPE_VAR_STRING;
bind[1].buffer = description_buf;
bind[1].buffer_length = 256;
bind[1].length = &description_len;
bind[2].buffer_type = FIELD_TYPE_LONG;
bind[2].buffer = (gptr) &roleId;
bind[2].is_null = &roleId_is_null;
mysql_stmt_bind_param(stmt, bind);
// execute command for the first time
strcpy(name_buf, "roleName");
name_len = strlen(name_buf);
strcpy(description_buf, "test prepared statements");
description_len = strlen(description_buf);
roleId=1;
roleId_is_null = 0;
int error=mysql_stmt_execute(stmt);
if(error){
printf("error occur: %s\n",mysql_error(conn));
exit(1);
}
/*
printf("new title with titleId=%d has been inserted\n",
(int) mysql_insert_id(conn));
*/
printf("%d rows affected \n",mysql_affected_rows(conn));
// close statement and connection
mysql_stmt_close(stmt);
mysql_close(conn);
return 0;
}
The Result is : 0 rows affected .
But When I change the program for Inserting and Deleting,it works.
Code:
INSERTING: char *insert = "INSERT INTO Roles (RoleId,Name,Description) VALUES(?,?,?)";
UPDATING: char *delete = "DELETE FROM Roles WHERE RoleId=?";
I am rather confused,and helpless,for the resource for the C API for MYSQL is so little. Hope someone can help.Thanks.
------------------------------------------------------------------------
We learn from the history that we do not learn from the history
MYSQL Prepared Statements for updating failed!?
