Re: Retrieving stored functions return value

[Hasani Blackwell]

To retrieve the return values of functions, you have to call it in a select statement or store it in a session variable. Functions always return a sql data type e.x.: VARCHAR(X), TEXT, DATETIME, INT, BIGINT, TINYINT, etc..

E.x.:
NOW() is a built-in function that returns the current datetime.

You can either do
SELECT NOW();
or
SET @returnValue:=NOW(); SELECT @returnValue;

A function always returns a 1x1 rowset.



Here's sample C code: (http://www.estorecorp.com/mysql/apisample1.html)


#include "stdafx.h"
#include <stdio.h>
#include <winsock.h>
#include <mysql.h>

int main(int argc, char* argv[])
{
MYSQL handle;
mysql_init(&handle);

if(&handle == NULL)
return -1;

//connect to the server
if(!mysql_real_connect(&handle, "127.0.0.1", "root", "h4s4ni", "test", 0, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS))
{
mysql_close(&handle);
return -1;
}

//execute the query
const char* sqlQuery = "SELECT NOW();";
if(mysql_real_query(&handle, sqlQuery, (unsigned long) strlen(sqlQuery)))
{
mysql_close(&handle);
return -1;
}
printf("mysql_real_query: %s\n", sqlQuery);

//store the resultset
MYSQL_RES* result = mysql_store_result(&handle);
if(result == NULL)
{
mysql_close(&handle);
return 0;
}
//retrieve the first row
MYSQL_ROW row = mysql_fetch_row(result);
if(row != NULL)
{
//get the name of the first column in the resultset.
MYSQL_FIELD *mysqlFields = mysql_fetch_fields(result);
const char* field_name = mysqlFields[0].name;

//get the length of the data of the first row in the 1st column of the resultset.
unsigned long* field_lengths = mysql_fetch_lengths(result);
unsigned long field_length = field_lengths[0];

//copy the data to a null terminated string buffer.
char* fieldData = new char[field_length + 1];
memset(fieldData, '\0', field_length + 1);
memcpy(fieldData, row[0], field_length);

//output the results.
printf("%s: %s.\n", field_name, fieldData);

//perform memory cleanup.
delete[] fieldData;
}
//free the resultset.
mysql_free_result(result);

//close the connection.
mysql_close(&handle);
return 0;
}