C program cannot get result value from MySQL procedure

Question

I am practicing with getting a value back from a MySQL stored procedure So I first created the following procedure

USE testdb;
DROP PROCEDURE IF EXISTS `testdb`.`get_return_value_test`;

DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS `testdb`.`get_return_value_test`(IN a INT(30), IN b INT, OUT result INT)
BEGIN
    SET result = a+b;
    SELECT result;
END $$
DELIMITER ;

and successfully tested it from MariaDB console

MariaDB [testdb]> call get_return_value_test(2, 3, @out_value);
+--------+
| result |
+--------+
|      5 |
+--------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

But when I have to use it within a C program I don't get the result.

• mysql_stmt_fetch function returns 101 value, which I have never seen in MySQL documentation
• mysql_stmt_errno is 0 Do you know where I went wrong? Thank you

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>
#include "utils.c"

static MYSQL *conn;

static void get_return_value_test(MYSQL *conn)
{
    int a = 3;
    int b = 4;
    int result = -1;
    int error_number;

    MYSQL_STMT *prepared_stmt;
    MYSQL_BIND param[3];
    
    // Prepare stored procedure call
    if(!setup_prepared_stmt(&prepared_stmt, "call get_return_value_test(?, ?, @out_value)", conn))
    {
        printf("Unable to run setup_prepared_stmt\n");
        mysql_stmt_close(prepared_stmt);
        mysql_close(conn);
        exit(EXIT_FAILURE);
    }

    // Prepare parameters
    memset(param, 0, sizeof(param));
    
    param[0].buffer_type = MYSQL_TYPE_LONG;
    param[0].buffer = &a;
    param[0].buffer_length = sizeof(a);

    param[1].buffer_type = MYSQL_TYPE_LONG;
    param[1].buffer = &b;
    param[1].buffer_length = sizeof(b);
    

    param[2].buffer_type = MYSQL_TYPE_LONG;
    param[2].buffer = &result;
    param[2].buffer_length = sizeof(result);
    


    if (mysql_stmt_bind_param(prepared_stmt, param) != 0)
    {
        printf("Could not bind parameters\n");
        mysql_stmt_close(prepared_stmt);
        mysql_close(conn);
        exit(EXIT_FAILURE);
    }

    // Run procedure
    if ((error_number = mysql_stmt_execute(prepared_stmt)) != 0)
    {
        printf("%d", error_number);
        printf("mysql_stmt_execute error.");
        mysql_stmt_close(prepared_stmt);
        mysql_close(conn);
        exit(EXIT_FAILURE);
    }
    else
    {
        printf("mysql_stmt_execute correctly executed\n");
    }

    memset(param, 0, sizeof(param));
    if((error_number = mysql_stmt_bind_result(prepared_stmt, param)) != 0)
    {
        printf("%d", error_number);
        printf("Could not retrieve output parameter");
        mysql_stmt_close(prepared_stmt);
        mysql_close(conn);
        exit(EXIT_FAILURE);
        
    }
    

    //FAILS HERE
    if((error_number = mysql_stmt_fetch(prepared_stmt)) != 0 )
    {
        printf("%d\n", error_number);
        printf("mysql_stmt_errno is %d\n", mysql_stmt_errno(prepared_stmt));
        
        finish_with_stmt_error(conn, prepared_stmt, "Could not buffer results\n", true);
    }
    
    printf("Result is %d\n", result);
    mysql_stmt_close(prepared_stmt);
    
}


int main()
{

    conn = mysql_init (NULL);
    if (conn == NULL)
    {
        fprintf (stderr, "mysql_init() failed (probably out of memory)\n");
        exit(EXIT_FAILURE);
    }

    if (mysql_real_connect(conn, "localhost", "login", "login", "testdb", 3306, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS) == NULL)
    {
        fprintf (stderr, "mysql_real_connect() failed\n");
        printf(mysql_error(conn));
        mysql_close (conn);
        exit(EXIT_FAILURE);
    }
    
    get_return_value_test(conn);
    mysql_close (conn);

    return 0;
}

utils.h

Answer 1

101 is defined as the value for MYSQL_DATA_TRUNCATED in mysql.h

https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-fetch.html says:

MYSQL_DATA_TRUNCATED is returned when truncation reporting is enabled. To determine which column values were truncated when this value is returned, check the error members of the MYSQL_BIND structures used for fetching values. Truncation reporting is enabled by default, but can be controlled by calling mysql_options() with the MYSQL_REPORT_DATA_TRUNCATION option.

You may not have seen this before because you were using older versions of MySQL that did not enable this reporting option by default.

You probably should return the value from your function as a 64-bit integer, because the sum of two 32-bit integers may overflow.

---

Okay I am taking a closer look at your code, and I see you reuse the params array for the result binding as well as the parameter binding. You memset the params array to zeroes before binding it for results.

But I see in https://dev.mysql.com/doc/c-api/8.0/en/c-api-prepared-call-statements.html that the array used for results binding needs some values initialized, according to the result set metadata. It looks like your array is just going to be all zeroes since you did memset. I'm guessing that your buffer_length being zero is a problem which could result in the error you saw.

So I suggest reviewing the code example in the manual that shows getting results from the CALL statement, and do similar steps for initializing your result buffers.