MySQL Forums
Forum List  »  Quality Assurance

C API PREPARED STATEMENTS- Error in inserting values to a table in the mysql database
Posted by: senthil kumar
Date: February 10, 2012 03:05AM

I got the below piece of code from the mysql website. It is meant to insert values to a database.
But the below code inserts only null instead of inserting the intended values, in the (mysql 5.5) database.

I tried several times with different approaches. But i am getting the same result.


#define STRING_SIZE 50

#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"
#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,\
col2 VARCHAR(40))"
#define INSERT_SAMPLE "INSERT INTO \
test_table(col1,col2) \
VALUES(?,?)"
#include<stdio.h>
#include<conio.h>
#include <windows.h>
#include <C:\Program Files\MySQL\MySQL Server 5.5\include\mysql.h>
void main()
{
MYSQL *mysql;
mysql=mysql_init(NULL);
mysql_real_connect(mysql, "localhost", "root","root","senthil",3306 , NULL, 0);
MYSQL_STMT *stmt;
MYSQL_BIND bind[2];
my_ulonglong affected_rows;
int param_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
unsigned long str_length;
my_bool *is_null;

if (mysql_query(mysql, DROP_SAMPLE_TABLE))
{
fprintf(stderr, " DROP TABLE failed\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}

if (mysql_query(mysql, CREATE_SAMPLE_TABLE))
{
fprintf(stderr, " CREATE TABLE failed\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}

stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, " prepare, INSERT successful\n");

param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, " total parameters in INSERT: %d\n", param_count);

if (param_count != 2)
{
fprintf(stderr, " invalid parameter count returned by MySQL\n");
exit(0);
}

memset(bind, 0, sizeof(bind));

bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= 0;
bind[0].length= 0;

bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= 0;
bind[1].length= &str_length;

if (mysql_stmt_bind_param(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_param() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}

int_data= 10; /* integer */
strncpy(str_data, "MySQL", STRING_SIZE); /* string */
str_length= strlen(str_data);


is_null= 1;


if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}

affected_rows= mysql_stmt_affected_rows(stmt);
fprintf(stdout, " total affected rows(insert 1): %lu\n",
(unsigned long) affected_rows);

if (affected_rows != 1) /* validate affected rows */
{
fprintf(stderr, " invalid affected rows by MySQL\n");
exit(0);
}

int_data= 1000;
strncpy(str_data, "The most popular Open Source database",
STRING_SIZE);
str_length= strlen(str_data);
small_data= 1000; /* smallint */
is_null= 0; /* reset */


if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}


affected_rows= mysql_stmt_affected_rows(stmt);
fprintf(stdout, " total affected rows(insert 2): %lu\n",
(unsigned long) affected_rows);

if (affected_rows != 1) /* validate affected rows */
{
fprintf(stderr, " invalid affected rows by MySQL\n");
exit(0);
}

/* Close the statement */
if (mysql_stmt_close(stmt))
{
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
//exit(0);
}
system("pause");
getch();
}


It creates a table with the attributes, but nulls the values!!
I kindly request you to look into this...

Options: ReplyQuote


Subject
Views
Written By
Posted
C API PREPARED STATEMENTS- Error in inserting values to a table in the mysql database
1890
February 10, 2012 03:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.