I'm using " MariaDB Connector/C " for my homework, but I got a problem: i always get a empty string when i pass in a string parameter, the db table is:

MariaDB none@(none):test> SELECT * FROM t3
 --- ----- 
| a | b   |
 --- ----- 
| 0 | abc |
| 1 | bcd |
| 2 | af  |
 --- ----- 
3 rows in set
Time: 0.010s
MariaDB none@(none):test> DESC t3
 ------- ---------- ------ ----- --------- ------- 
| Field | Type     | Null | Key | Default | Extra |
 ------- ---------- ------ ----- --------- ------- 
| a     | int(11)  | NO   | PRI | <null>  |       |
| b     | char(10) | YES  |     | <null>  |       |
 ------- ---------- ------ ----- --------- ------- 
2 rows in set
Time: 0.011s

And the code i use to test:

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main()
{
    MYSQL *mysql;
    mysql = mysql_init(NULL);
    if (!mysql_real_connect(mysql,NULL , "none", "linux", "test", 0,"/tmp/mariadb.sock",0)){
        printf( "Error connecting to database: %s",mysql_error(mysql));
    } else
        printf("Connected...\n");
    if(mysql_real_query(mysql,"SET CHARACTER SET utf8",(unsigned int)sizeof("SET CHARACTER SET utf8"))){
        printf("Failed to set Encode!\n");
    }


    char query_stmt_2[]="select * from t3 where b=?";
    MYSQL_STMT *stmt2 = mysql_stmt_init(mysql);
    if(mysql_stmt_prepare(stmt2, query_stmt_2, -1))
    {
        printf("STMT2 prepare failed.\n");
    }
    MYSQL_BIND instr_bind;
    char instr[50]="abc";
    my_bool in_is_null = 0;
    my_bool in_error = 0;
    instr_bind.buffer_type = MYSQL_TYPE_STRING;
    instr_bind.buffer = &instr[0];
    char in_ind = STMT_INDICATOR_NTS;
    instr_bind.u.indicator = &in_ind;
    unsigned long instr_len=sizeof(instr);
    // instr_bind.length = &instr_len;
    // instr_bind.buffer_length=instr_len;
    instr_bind.is_null = &in_is_null;
    instr_bind.error = &in_error;


    MYSQL_BIND out_bind[2];
    memset(out_bind, 0, sizeof(out_bind));
    int out_int[2];
    char outstr[50];
    my_bool out_int_is_null[2]={0,0};
    my_bool out_int_error[2]={0,0};
    unsigned long out_int_length[2]={0,0};
    out_bind[0].buffer = out_int 0;
    out_bind[0].buffer_type = MYSQL_TYPE_LONG;
    out_bind[0].is_null = out_int_is_null 0;
    out_bind[0].error = out_int_error 0;
    out_bind[0].length = out_int_length 0;

    out_bind[1].buffer = outstr;
    out_bind[1].buffer_type = MYSQL_TYPE_STRING;
    out_bind[1].buffer_length = 50;
    out_bind[1].is_null = out_int_is_null 1;
    out_bind[1].error = out_int_error 1;
    out_bind[1].length = out_int_length 1;

    if(mysql_stmt_bind_param(stmt2, &instr_bind) ||
    mysql_stmt_bind_result(stmt2, out_bind)){
        printf("Bind error\n");
    }

    if(mysql_stmt_execute(stmt2))
    {
        printf("Exec error: %s",mysql_stmt_error(stmt2));
    }

    if(mysql_stmt_store_result(stmt2)){
        printf("Store result error!\n");
        printf("%s\n",mysql_stmt_error(stmt2));
    }
    while(!mysql_stmt_fetch(stmt2))
    {
        printf("%d\t%s\n", out_int[0], outstr);
    }
    mysql_stmt_close(stmt2);
end:
    mysql_close(mysql);

}

i only got a empty result:

❯ ./Exec/test/stmt_test
Connected...                                                 

I have being trouble with this for two day, and the tomorrow is the deadline, i 'm very anxious. Can you help? Thanks a lot!

CodePudding user response：

1) General

• Avoid "it was hard to write, so it should be hard to read" code
• add variable declarations at the beginning of the function, not in the middle of code (Wdeclaration-after-statement)
• don't use c comments in C
• set character set with api function mysql_set_character_set()
• write proper error handling, including mysql_error/mysql_stmt_error results and don't continue executing subsequent code after error.
• always initialize MYSQL_BIND

2) input bind buffer

• u.indicator is used for bulk operations and doesn't make sense here
• bind.is_null is not required, since you specified a valid buffer address
• buffer_length is not set (in comments)

3) Output bind buffer

• Always bind output parameters after mysql_stmt_execute(), since mysql_stmt_prepare can't always determine the number of parameters, e.g. when calling a stored procedure: In this case mysql_stmt_bind_param will return an error.
• binding an error indicator doesn't make much sense without setting MYSQL_REPORT_DATA_TRUNCATION (mysql_optionsv)

For some examples how to deal with prepared statements check the file ps.c of MariaDB Connector/C unit tests