Using Stored Function with C API & Prepared Statements

Question

I am attempting to use a stored function with a prepared statement but not having any success.

MySQL prepares the statement but then does not execute it, no error is given, it just silently fails.

This is the *function:

drop function if exists lineDistanceC;
delimiter //
CREATE FUNCTION lineDistanceC (la1 DOUBLE, lo1 DOUBLE, la2 DOUBLE, lo2 DOUBLE) RETURNS DOUBLE 
BEGIN 
SET @r = 6371;
SET @lat1 = RADIANS(la1);
SET @lon1 = RADIANS(lo1);
SET @lat2 = RADIANS(la2);
SET @lon2 = RADIANS(lo2);
SET @x = (@lon2-@lon1) * COS((@lat1+@lat2)/2);
SET @y = (@lat2 - @lat1);
RETURN (SQRT((@x*@x) + (@y*@y)) * @r);
END
//
delimiter ;

It works on the command line as expected, and substituting a hard coded double in the SELECT statement then allows it to work as expected, so the surrounding code is fine as well.

I have enabled CLIENT_MULTI_STATEMENTS as a MySQL connect parameter, I suspect that it only relates to CALLing procedures, but it was a straw worth clutching.

The statement is prepared using:

strcpy (sqlStr, "SELECT lineDistanceC(Y(pnt),X(pnt), ?, ?) FROM mytable");
MYSQL_STMT *statement;
if (mysql_stmt_prepare(statement, sqlStr, strlen(sqlStr))) {
    fprintf(stderr, "ERROR:mysql_stmt_prepare() failed. Error:%s\nsql:%s\n", mysql_stmt_error(statement), sqlStr);
}

Followed by the required bind statements for the parameters and the results, then executed using:

if (mysql_stmt_execute(statement)) {
    fprintf(stderr, "mysql_stmt_execute(), failed. Error:%s\n", mysql_stmt_error(statement));
}

As mentioned earlier if the function in the above SELECT statement is replaced by a hard code float (Eg. SELECT 2.3 .....) then it returns the hard coded number as expected.

In reality the prepared statement code is inserted using macros, there are many hundreds of statements working as required so the preparation, binding or execution syntax is not the issue (and if it is incorrect above then it is my translation from the macros that is wrong) unless it needs to be different for use with functions.

These are the log entries when one of the inputs is bound as the output:

Prepare SELECT ? FROM mytable
Execute SELECT 51.36000061035156 FROM mytable

The is the log entry when I attempt to use the function:

Prepare SELECT lineDistanceC(latitude, longitude, ?, ?) FROM mytable;

As mentioned above, it silently fails without executing the statement, no errors in the log or elsewhere.

Any clues would be appreciated.

*To give credit where it is due, this function was derived from a formula here

Answer 1

I hacked up a quick example to test and wasn't able to reproduce your issue on MySql 5.1.66. Seeing the Prepare statement in your log indicates that the query is syntactically correct and that the server is now ready to accept the parameters. If you fail to correctly bind input parameters, you won't see the Query log message.

You should probably verify again that your bind macros are producing the correct C code. The following snippet shows a functioning set of API calls which might give you some further ideas to help debugging. The only change I made to your function as to add the DETERMINISTIC clause to avoid errors with binary logging on my system.

Starting with a table like this:

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set (0.00 sec)

Then executing the following query gives these results:

mysql> SELECT lineDistanceC(1.0, 2.0, 3.0, value) FROM mytable;
+-------------------------------------+
| lineDistanceC(1.0, 2.0, 3.0, value) |
+-------------------------------------+
|                    248.609129229989 |
|                    222.389853289118 |
|                    248.609129229989 |
+-------------------------------------+
3 rows in set (0.00 sec)

The following C code attempts the same query, but using the MySql API. Note that memory management and correct error checking has not been implemented.

/* mysql_test.c */

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

#include <mysql.h>


int main(int argc, char * argv[])
{
    char sqlStr[300];


    MYSQL *mysql = mysql_init(NULL);
    if (mysql == NULL) {
        fprintf(stderr, "ERROR:mysql_init() failed.\n");
        exit(1);
    }

    const char* host = "localhost";
    const char* user = "root";
    const char* passwd = NULL;
    const char* db = "mysql_test";

    if (mysql_real_connect(mysql, host, user, passwd, db, 0, NULL, 0) == NULL) {
        fprintf(stderr, "ERROR:mysql_real_connect() failed.\n");
        exit(1);
    }


    MYSQL_STMT *statement = NULL;
    statement = mysql_stmt_init(mysql);
    if (statement == NULL) {
        fprintf(stderr, "ERROR:mysql_stmt_init() failed.\n");
        exit(1);
    }

    strcpy (sqlStr, "SELECT lineDistanceC(1.0, 2.0, ?, value) FROM mytable");

    if (mysql_stmt_prepare(statement, sqlStr, strlen(sqlStr))) {
        fprintf(stderr, "ERROR:mysql_stmt_prepare() failed. Error:%s\nsql:%s\n", mysql_stmt_error(statement), sqlStr);
        exit(1);
    }

    MYSQL_BIND input_bind[1];
    memset(input_bind, 0, sizeof(input_bind));
    float v1 = 3.0;
    unsigned long v1_len = sizeof(v1);

    input_bind[0].buffer_type = MYSQL_TYPE_FLOAT;
    input_bind[0].buffer = &v1;
    input_bind[0].buffer_length = sizeof(v1);
    input_bind[0].length = &v1_len;
    input_bind[0].is_null = (my_bool*)0;

    if (mysql_stmt_bind_param(statement, input_bind)) {
        fprintf(stderr, "ERROR:mysql_stmt_bind_param failed\n");
        exit(1);
    }

    if (mysql_stmt_execute(statement)) {
        fprintf(stderr, "mysql_stmt_execute(), failed. Error:%s\n", mysql_stmt_error(statement));
        exit(1);
    }

    /* Fetch result set meta information */
    MYSQL_RES* prepare_meta_result = mysql_stmt_result_metadata(statement);
    if (!prepare_meta_result)
    {
        fprintf(stderr,
                " mysql_stmt_result_metadata(), \
                returned no meta information\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(statement));
        exit(1);
    }

    /* Get total columns in the query */
    int column_count= mysql_num_fields(prepare_meta_result);
    if (column_count != 1) /* validate column count */
    {
        fprintf(stderr, " invalid column count returned by MySQL\n");
        exit(1);
    }

    /* Bind single result column, expected to be a double. */
    MYSQL_BIND result_bind[1];
    memset(result_bind, 0, sizeof(result_bind));

    my_bool result_is_null[1];
    double result_double;
    unsigned long result_len = 0;

    result_bind[0].buffer_type = MYSQL_TYPE_DOUBLE;
    result_bind[0].buffer = &result_double;
    result_bind[0].buffer_length = sizeof(result_double);
    result_bind[0].length = &result_len;
    result_bind[0].is_null = &result_is_null[1];

    if (mysql_stmt_bind_result(statement, result_bind)) {
        fprintf(stderr, "mysql_stmt_bind_Result(), failed. Error:%s\n", mysql_stmt_error(statement));
        exit(1);
    }

    while (!mysql_stmt_fetch(statement)) {
        printf("%.12f\n", result_double);
    }

}

Compile with:

gcc -o mysql_test mysql_test.c -lmysqlclient

Here's the output:

248.609129229989
222.389853289118
248.609129229989

This matches the output of the statement executed using the mysql command line client.

You should be able to compile and run this on your system to determine if the problem is with your usage of the API or some other problem.