[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))
BEGIN
    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));
END

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],
               result;
	
	// 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


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


Sorry, you can't reply to this topic. It has been closed.

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.