Description:
I'm trying to insert data into table using C API prepared statements, but cannot get data in the column. Record is added with correct auto-increment, but my data is missing - just empty field.
I've checked with debugger - after bind statement handle has pointer set to my data, type is correct.
Query log shows:
--
37 Connect ilya@localhost on testdb
37 Prepare [1] INSERT INTO foo VALUES(0, ?)
37 Execute [1] INSERT INTO foo VALUES(0, ?)
--
so it doesn't substitute '?' with my data in exec.
No new messages in mysqld.log
Client program runs without errors and even returns correct id of inserted record, but no data:
--
[ilya@mandriva test]$ ./mytest
Required param count - 1
Returned id 1
[ilya@mandriva test]$ ./mysql testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 4.1.12-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from foo;
+-----+-------+
| pid | pname |
+-----+-------+
| 1 | |
+-----+-------+
1 row in set (0.01 sec)
Table was just created before running a client, so there was no data.
gcc version 4.0.1 (4.0.1-5mdk for Mandriva Linux release 2006.0)
I also tried your sample client from http://dev.mysql.com/doc/refman/4.1/en/mysql-stmt-execute.html, but I hit another problem with it - it caused mysqld crash, so gave up with this sample
How to repeat:
Table creation script:
----------------------
CREATE TABLE foo (
pid INTEGER NOT NULL AUTO_INCREMENT,
pname VARCHAR(255),
PRIMARY KEY(id)
);
Client code:
------------
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "mysql.h"
#define FAIL 1
MYSQL_STMT *handle;
MYSQL_BIND bind_var[3];
MYSQL *mysql;
char str_data[50];
unsigned long str_length;
my_bool is_null = 0;
int param_count;
char *statement = "INSERT INTO foo VALUES(0, ?)";
long id;
int main(void) {
mysql=mysql_init(0);
if (!mysql_real_connect(mysql, NULL, "ilya", NULL, "testdb",
0, "/var/lib/mysql/mysql.sock", 0)) {
printf("Cannot connect to DB - %s\n", mysql_error(mysql));
return FAIL;
}
handle = mysql_stmt_init(mysql);
if (!handle) {
printf("Cannot create handle - %s\n", mysql_error(mysql));
return FAIL;
}
if (0 != mysql_stmt_prepare(handle, statement, strlen(statement))) {
printf("Error preparing statement %s - %s\n", statement,
mysql_stmt_error(handle));
mysql_stmt_close(handle);
return FAIL;
}
printf("Required param count - %d\n", mysql_stmt_param_count(handle));
bzero(bind_var, sizeof(bind_var));
bind_var[0].buffer_type = MYSQL_TYPE_STRING;
bind_var[0].buffer = str_data;
bind_var[0].buffer_length = sizeof(str_data);
bind_var[0].length = &str_length;
bind_var[0].is_null = &is_null;
is_null = 0;
strcpy(str_data, "foo");
str_length = strlen(str_data);
if (0 != mysql_stmt_bind_param(handle, bind_var)) {
printf("Cannot bind the variables - %s\n", mysql_stmt_error(handle));
return FAIL;
}
if (0 != mysql_stmt_execute(handle)) {
printf("Exec failed - %s\n", mysql_stmt_error(handle));
return FAIL;
}
id = mysql_insert_id(mysql);
if (0 == id) {
printf("Invalid ID returned by INSERT\n");
return FAIL;
} else
printf("Returned id %ld\n", id);
return 0;
}
Compilation command:
--------------------
gcc -o mytest mytest.c `mysql_config --cflags` `mysql_config --libs`
Description: I'm trying to insert data into table using C API prepared statements, but cannot get data in the column. Record is added with correct auto-increment, but my data is missing - just empty field. I've checked with debugger - after bind statement handle has pointer set to my data, type is correct. Query log shows: -- 37 Connect ilya@localhost on testdb 37 Prepare [1] INSERT INTO foo VALUES(0, ?) 37 Execute [1] INSERT INTO foo VALUES(0, ?) -- so it doesn't substitute '?' with my data in exec. No new messages in mysqld.log Client program runs without errors and even returns correct id of inserted record, but no data: -- [ilya@mandriva test]$ ./mytest Required param count - 1 Returned id 1 [ilya@mandriva test]$ ./mysql testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 to server version: 4.1.12-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from foo; +-----+-------+ | pid | pname | +-----+-------+ | 1 | | +-----+-------+ 1 row in set (0.01 sec) Table was just created before running a client, so there was no data. gcc version 4.0.1 (4.0.1-5mdk for Mandriva Linux release 2006.0) I also tried your sample client from http://dev.mysql.com/doc/refman/4.1/en/mysql-stmt-execute.html, but I hit another problem with it - it caused mysqld crash, so gave up with this sample How to repeat: Table creation script: ---------------------- CREATE TABLE foo ( pid INTEGER NOT NULL AUTO_INCREMENT, pname VARCHAR(255), PRIMARY KEY(id) ); Client code: ------------ #include <stdio.h> #include <stdlib.h> #include <string.h> #include "mysql.h" #define FAIL 1 MYSQL_STMT *handle; MYSQL_BIND bind_var[3]; MYSQL *mysql; char str_data[50]; unsigned long str_length; my_bool is_null = 0; int param_count; char *statement = "INSERT INTO foo VALUES(0, ?)"; long id; int main(void) { mysql=mysql_init(0); if (!mysql_real_connect(mysql, NULL, "ilya", NULL, "testdb", 0, "/var/lib/mysql/mysql.sock", 0)) { printf("Cannot connect to DB - %s\n", mysql_error(mysql)); return FAIL; } handle = mysql_stmt_init(mysql); if (!handle) { printf("Cannot create handle - %s\n", mysql_error(mysql)); return FAIL; } if (0 != mysql_stmt_prepare(handle, statement, strlen(statement))) { printf("Error preparing statement %s - %s\n", statement, mysql_stmt_error(handle)); mysql_stmt_close(handle); return FAIL; } printf("Required param count - %d\n", mysql_stmt_param_count(handle)); bzero(bind_var, sizeof(bind_var)); bind_var[0].buffer_type = MYSQL_TYPE_STRING; bind_var[0].buffer = str_data; bind_var[0].buffer_length = sizeof(str_data); bind_var[0].length = &str_length; bind_var[0].is_null = &is_null; is_null = 0; strcpy(str_data, "foo"); str_length = strlen(str_data); if (0 != mysql_stmt_bind_param(handle, bind_var)) { printf("Cannot bind the variables - %s\n", mysql_stmt_error(handle)); return FAIL; } if (0 != mysql_stmt_execute(handle)) { printf("Exec failed - %s\n", mysql_stmt_error(handle)); return FAIL; } id = mysql_insert_id(mysql); if (0 == id) { printf("Invalid ID returned by INSERT\n"); return FAIL; } else printf("Returned id %ld\n", id); return 0; } Compilation command: -------------------- gcc -o mytest mytest.c `mysql_config --cflags` `mysql_config --libs`