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
我正在开发一个与mysql交互的C项目。所以我打算使用mysql存储过程并在此链接中找到一个示例程序我只是根据我的项目要求更改了程序。这是表结构
id | int(10) unsigned | NO | PRI | NULL | auto_increment |
username | varchar(64) | NO | MUL | NULL | |
password | varchar(25) | NO | | NULL | |
And here is my program.
这是我的计划。
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[i].buffer_type = fields[i].type;
rs_bind[i].is_null = &is_null[i];
switch (fields[i].type)
{
case MYSQL_TYPE_LONG:
rs_bind[i].buffer = (char *) &(int_data[i]);
rs_bind[i].buffer_length = sizeof (int_data);
break;
case MYSQL_TYPE_VAR_STRING:
rs_bind[i].buffer = (char *) own_buf;
rs_bind[i].buffer_length = sizeof(own_buf);
rs_bind[i].length = &int_data[1];
break;
default:
fprintf(stderr, "ERROR: unexpected type: %d.\n", fields[i].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[i].buffer_type)
{
case MYSQL_TYPE_LONG:
if (*rs_bind[i].is_null)
printf(" val[%d] = NULL;", i);
else
printf(" val[%d] = %ld;",
i, (long) *((int *) rs_bind[i].buffer));
break;
case MYSQL_TYPE_VAR_STRING:
printf(" val[%d] = %s;",i,(char*)rs_bind[i].buffer);
break;
default:
printf(" unexpected type (%d)\n",
rs_bind[i].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
Above code works fine in case of integer argument like CREATE PROCEDURE p1(IN p_in INT) but not for varchar arguments.
上面的代码在整数参数的情况下工作正常，如CREATE PROCEDURE p1（IN p_in INT），但不适用于varchar参数。
link as mentioned in the link for varchar input parameter type is MYSQL_TYPE_STRING and output parameter type is MYSQL_TYPE_VAR_STRING
varchar输入参数类型的链接中提到的链接是MYSQL_TYPE_STRING，输出参数类型是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.
问题：获得空结果。就我的分析而言，status = mysql_stmt_fetch（stmt）;函数返回100（MYSQL_NO_DATA）虽然表中存在条目。
NOTE: I tried calling the procedure manually into mysql like call p1('6666600222'); which results
注意：我尝试手动调用程序进入mysql，如调用p1（'6666600222'）;结果
mysql> call p1('6666600222');
+----------+
| password |
+----------+
| 1234 |
+----------+
1 row in set (0.00 sec)
Anyone help me to short out this?
有人帮我缩短了吗？
1 个解决方案
#1
0
In order to set a varchar param, ps_params should take pointer to char not pointer to pointer to char.
为了设置varchar参数，ps_params应该将指针指向char而不指向指向char的指针。
Try change:
尝试更改：
ps_params[0].buffer = (void *) &input_buf;
to:
至：
ps_params[0].buffer = (void *)&(input_buf[0]); // or just (void *)input_buf
And after set string value to input_buf set ps_params[0].buffer_length as below:
设置字符串值到input_buf之后设置ps_params [0] .buffer_length如下：
ps_params[0].buffer_length = strlen(input_buf);
Hope this helps
希望这可以帮助
#1
0
In order to set a varchar param, ps_params should take pointer to char not pointer to pointer to char.
为了设置varchar参数，ps_params应该将指针指向char而不指向指向char的指针。
Try change:
尝试更改：
ps_params[0].buffer = (void *) &input_buf;
to:
至：
ps_params[0].buffer = (void *)&(input_buf[0]); // or just (void *)input_buf
And after set string value to input_buf set ps_params[0].buffer_length as below:
设置字符串值到input_buf之后设置ps_params [0] .buffer_length如下：
ps_params[0].buffer_length = strlen(input_buf);
Hope this helps
希望这可以帮助