MySQL Forums
Forum List  »  Connector/C++

C API Support for calling mysql stored procedure - Not working in case of string argument
Posted by: Prakash Gibbs
Date: March 22, 2016 05:42AM

Below code works fine in case of integer argument like CREATE PROCEDURE p1(IN p_in INT)but not for varchar arguments.
varchar input parameter type is MYSQL_TYPE_STRING and output parameter type is MYSQL_TYPE_VAR_STRING
Problem: Getting empty result. As far my analysis, status = mysql_stmt_fetch(stmt); function returns 100(MYSQL_NO_DATA) though the entry present in the table.
NOTE: I tried calling the procedure manually into mysql like call p1('6666600222'); which results
mysql> call p1('6666600222');
+----------+
| password |
+----------+
| 1234 |
+----------+
1 row in set (0.00 sec)
Anyone help me to short out this?



I'm in the phase of developing a C project which interacts with mysql. So i planned to use mysql stored procedure and found a sample program in this link I just changed the program according to the requirements of my project. Here is the table structure

id | int(10) unsigned | NO | PRI | NULL | auto_increment |
username | varchar(64) | NO | MUL | NULL | |
password | varchar(25) | NO | | NULL | |


int main()
{
MYSQL_RES *result;
MYSQL *mysql=mysql_init(NULL);

/* connect to server with the CLIENT_MULTI_STATEMENTS option */
if (mysql_real_connect (mysql, "localhost", "root", "root123","DONT_USE", 0, NULL , CLIENT_MULTI_STATEMENTS) == NULL)
{
printf("mysql_real_connect() failed\n");
mysql_close(mysql);
}


MYSQL_STMT *stmt;
MYSQL_BIND ps_params[1]; /* input parameter buffers */
long int int_data[3]; /* input/output values */
my_bool is_null[3]; /* output value nullability */
int status;
char own_buf[25],input_buf[64];
memset(own_buf, 0, 25);
memset(input_buf, 0, 64);

/* set up stored procedure */
status = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1");
test_error(mysql, status);

status = mysql_query(mysql,
"CREATE PROCEDURE p1("
" IN p_in VARCHAR(64)) "
"BEGIN "
" SELECT password from data where username=p_in; "
"END");
test_error(mysql, status);

/* initialize and prepare CALL statement with parameter placeholders */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
printf("Could not initialize statement\n");
}
status = mysql_stmt_prepare(stmt, "CALL p1(?)", 10);
test_stmt_error(stmt, status);

/* initialize parameters: p_in, p_out, p_inout (all INT) */
memset(ps_params, 0, sizeof (ps_params));

ps_params[0].buffer_type = MYSQL_TYPE_STRING;
ps_params[0].buffer = (void *) &input_buf;
ps_params[0].buffer_length = sizeof(input_buf);
ps_params[0].is_null = 0;

/* bind parameters */
status = mysql_stmt_bind_param(stmt, ps_params);
test_stmt_error(stmt, status);

/* assign values to parameters and execute statement */
int_data[0]= 2; /* p_in */
int_data[1]= 20; /* p_out */

strcpy(input_buf,"'6666600222'");
int_data[0] = strlen(input_buf);
input_buf[int_data[0]] = '\0';
ps_params[0].length = &int_data[0];


status = mysql_stmt_execute(stmt);
test_stmt_error(stmt, status);

/* process results until there are no more */
do {
int i;
int num_fields; /* number of columns in result */
MYSQL_FIELD *fields; /* for result set metadata */
MYSQL_BIND *rs_bind; /* for output buffers */

/* the column count is > 0 if there is a result set */
/* 0 if the result is only the final status packet */
num_fields = mysql_stmt_field_count(stmt);

if (num_fields > 0)
{
/* there is a result set to fetch */
printf("Number of columns in result: %d\n", (int) num_fields);

/* what kind of result set is this? */
printf("Data: ");
if(mysql->server_status & SERVER_PS_OUT_PARAMS)
printf("this result set contains OUT/INOUT parameters\n");
else
printf("this result set is produced by the procedure\n");

MYSQL_RES *rs_metadata = mysql_stmt_result_metadata(stmt);
test_stmt_error(stmt, rs_metadata == NULL);

fields = mysql_fetch_fields(rs_metadata);

rs_bind = (MYSQL_BIND *) malloc(sizeof (MYSQL_BIND) * num_fields);
if (!rs_bind)
{
printf("Cannot allocate output buffers\n");
}
memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields);

/* set up and bind result set output buffers */
for (i = 0; i < num_fields; ++i)
{
rs_bind.buffer_type = fields.type;
rs_bind.is_null = &is_null;

switch (fields.type)
{
case MYSQL_TYPE_LONG:
rs_bind.buffer = (char *) &(int_data);
rs_bind.buffer_length = sizeof (int_data);
break;
case MYSQL_TYPE_VAR_STRING:
rs_bind.buffer = (char *) own_buf;
rs_bind.buffer_length = sizeof(own_buf);
rs_bind.length = &int_data[1];
break;

default:
fprintf(stderr, "ERROR: unexpected type: %d.\n", fields.type);
}
}

status = mysql_stmt_bind_result(stmt, rs_bind);
test_stmt_error(stmt, status);

/* fetch and display result set rows */
while (1)
{
status = mysql_stmt_fetch(stmt);

if (status == 1 || status == MYSQL_NO_DATA)
break;

for (i = 0; i < num_fields; ++i)
{
switch (rs_bind.buffer_type)
{
case MYSQL_TYPE_LONG:
if (*rs_bind.is_null)
printf(" val[%d] = NULL;", i);
else
printf(" val[%d] = %ld;",
i, (long) *((int *) rs_bind.buffer));
break;
case MYSQL_TYPE_VAR_STRING:
printf(" val[%d] = %s;",i,(char*)rs_bind.buffer);
break;

default:
printf(" unexpected type (%d)\n",
rs_bind.buffer_type);
}
}
printf("\n");
}

mysql_free_result(rs_metadata); /* free metadata */
free(rs_bind); /* free output buffers */
}
else
{
/* no columns = final status packet */
printf("End of procedure output\n");
}

/* more results? -1 = no, >0 = error, 0 = yes (keep looking) */
status = mysql_stmt_next_result(stmt);
if (status > 0)
test_stmt_error(stmt, status);
} while (status == 0);

mysql_stmt_close(stmt);
}

OUTPUT:
Number of columns in result: 1
Data: this result set is produced by the procedure
End of procedure output

Options: ReplyQuote


Subject
Views
Written By
Posted
C API Support for calling mysql stored procedure - Not working in case of string argument
3148
March 22, 2016 05:42AM


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.