[Solved]Getting result of query from stored procedure
Posted by: Jordan Bradley
Date: January 20, 2012 04:48PM

I'm using the C connector trying to call a stored procedure and get the result of a query atained.

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `authorize_user`(IN username CHAR (32), IN password CHAR (32))
    DECLARE secret CHAR (16);
    SELECT user_secret INTO secret FROM tah_users WHERE user_name = username;
    SELECT COUNT(user_id) as status FROM tah_users WHERE user_password = MD5(CONCAT(secret, password));

my C function:

char mysql_authorize_user (MYSQL *conn, char *username, char *password)
	int username_len = strlen (username);
	if (username_len == 0 || username_len > 32)
		return 0; // Out of range
	for (int i = 0; i < username_len; i++)
		if (!isalnum (username) && username != '_') // not alphanumeric and not underscores
			return 0; // username contained invalid characters
	char *query = "CALL authorize_user (?, ?)";
	char status;
	MYSQL_STMT *stmt;
	MYSQL_BIND params[2],
	// clear binds
	memset (&params, 0, sizeof (params));
	memset (&result, 0, sizeof (result));
	// init statement
	stmt = mysql_stmt_init(conn);
	// compile statement
	mysql_stmt_prepare(stmt, query, strlen(query));
	// prepare parameters
	params[0].buffer_type = MYSQL_TYPE_STRING;
	params[0].buffer = username;
	params[0].buffer_length = username_len;
	params[0].length = &params[0].buffer_length;
	params[1].buffer_type = MYSQL_TYPE_STRING;
	params[1].buffer = password;
	params[1].buffer_length = strlen (password);
	params[1].length = &params[1].buffer_length;
	// prepare result
	result.buffer_type = MYSQL_TYPE_TINY;
	result.buffer = &status;
	// bind params and results
	mysql_stmt_bind_param (stmt, (MYSQL_BIND *)&params);
	mysql_stmt_bind_result (stmt, (MYSQL_BIND *)&result);
	// execute
	mysql_stmt_execute (stmt);
	mysql_stmt_store_result (stmt);
	mysql_stmt_free_result (stmt);
	mysql_stmt_close (stmt);
	return status;

For some reason, the result struct cannot bind and I don't know why.

Thanks in advance.

Edited 1 time(s). Last edit at 01/20/2012 08:18PM by Jordan Bradley.

Options: ReplyQuote

Written By
[Solved]Getting result of query from stored procedure
January 20, 2012 04:48PM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.