Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » [BUG] MySQL error 2014 while reading output from stored procedure.
[BUG] MySQL error 2014 while reading output from stored procedure. [message #39107] Fri, 15 February 2013 17:20 Go to next message
Klugier is currently offline  Klugier
Messages: 1077
Registered: September 2012
Location: Poland, Kraków
Senior Contributor
Hello,

First of all, I have created following stored procedure on MySQL server:

DELIMITER //

CREATE PROCEDURE selectCurrentUserInformation ()
LANGUAGE SQL
DETERMINISTIC
BEGIN
	DECLARE userID INT;
	
	SET userID = (SELECT `ID` FROM `LoginInfo`);
	
	IF !ISNULL (userID) THEN
		SELECT `Rights`, `FirstName`, `Surname` FROM Users WHERE `ID`=(SELECT userID);
	END IF;
END;

//
DELIMITER ;


Next I have created method that can read the select output(C++):

User DatabaseUsers::getCurrentUserInformation () {
	User user;
	String query = "CALL selectCurrentUserInformation()";
		
	if (SQL.Execute (query)) {
		while (SQL.Fetch () && SQL.GetColumns () == 3) {
		      /* READ THE SELECT TABLE - Works! */
		}
        }

        return User;
}


The method works fine, but after executing query program lost synchronization with MySQL server. (MySQL 2014 error - Commands out of sync).

How to solve the problem? Let's look more closely to the following upp method (MySQL -> MySQL.cpp):

bool MySqlConnection::Execute() {
	String query;
	int pi = 0;
	const char *s = statement;
	while(s < statement.End())
		if(*s == '\'' || *s == '\"')
			s = MySqlReadString(s, query);
		else {
			if(*s == '?')
				query.Cat(param[pi++]);
			else
				query.Cat(*s);
			s++;
		}
	Cancel();
	if(!MysqlQuery(query))
		return false;
	result = mysql_store_result(mysql);
	rows = (int)mysql_affected_rows(mysql);
	if(result) {
		int fields = mysql_num_fields(result);
		info.SetCount(fields);
		convert.Alloc(fields, false);
		for(int i = 0; i < fields; i++) {
			MYSQL_FIELD *field = mysql_fetch_field_direct(result, i);
			SqlColumnInfo& f = info[i];
			f.name = field->name;
			switch(field->type) {
			case FIELD_TYPE_TINY:
			case FIELD_TYPE_SHORT:
			case FIELD_TYPE_LONG:
			case FIELD_TYPE_INT24:
				f.type = INT_V;
				break;
			case FIELD_TYPE_LONGLONG:
			case FIELD_TYPE_DECIMAL:
			case FIELD_TYPE_FLOAT:
			case FIELD_TYPE_DOUBLE:
				f.type = DOUBLE_V;
				break;
			case FIELD_TYPE_DATE:
				f.type = DATE_V;
				break;
			case FIELD_TYPE_DATETIME:
			case FIELD_TYPE_TIMESTAMP:
				f.type = TIME_V;
				break;
			case FIELD_TYPE_VAR_STRING:
			case FIELD_TYPE_STRING:
				convert[i] = true;
			default:
				f.type = STRING_V;
				break;
			}
			f.width = field->length;
			f.scale = f.precision = 0;
		}
	}
	else {
		lastid = (int)mysql_insert_id(mysql);
		if(lastid) {
			SqlColumnInfo& f = info.Add();
			f.width = f.scale = f.precision = 0;
			f.binary = false;
			f.type = DOUBLE_V;
			f.name = "LAST_INSERT_ID";
			rows = 1;
		}
	}
	return true;
}


What's wrong with this method? It can stored only one result, but my procedure produces more than one result. This is what i have done to fix this issue:

bool MySqlConnection::Execute() {
	String query;
	int pi = 0;
	const char *s = statement;
	while(s < statement.End())
		if(*s == '\'' || *s == '\"')
			s = MySqlReadString(s, query);
		else {
			if(*s == '?')
				query.Cat(param[pi++]);
			else
				query.Cat(*s);
			s++;
		}
	Cancel();
	if(!MysqlQuery(query))
		return false;
	result = mysql_store_result(mysql);
	
	/* FIX - we can read all the results from query */
	while (mysql_more_results (mysql)) {
		int tempResult = mysql_next_result (mysql); // <- Temporary solution: we are losing rest of results
	}
	
	rows = (int)mysql_affected_rows(mysql);
	if(result) {
		int fields = mysql_num_fields(result);
		info.SetCount(fields);
		convert.Alloc(fields, false);
		for(int i = 0; i < fields; i++) {
			MYSQL_FIELD *field = mysql_fetch_field_direct(result, i);
			SqlColumnInfo& f = info[i];
			f.name = field->name;
			switch(field->type) {
			case FIELD_TYPE_TINY:
			case FIELD_TYPE_SHORT:
			case FIELD_TYPE_LONG:
			case FIELD_TYPE_INT24:
				f.type = INT_V;
				break;
			case FIELD_TYPE_LONGLONG:
			case FIELD_TYPE_DECIMAL:
			case FIELD_TYPE_FLOAT:
			case FIELD_TYPE_DOUBLE:
				f.type = DOUBLE_V;
				break;
			case FIELD_TYPE_DATE:
				f.type = DATE_V;
				break;
			case FIELD_TYPE_DATETIME:
			case FIELD_TYPE_TIMESTAMP:
				f.type = TIME_V;
				break;
			case FIELD_TYPE_VAR_STRING:
			case FIELD_TYPE_STRING:
				convert[i] = true;
			default:
				f.type = STRING_V;
				break;
			}
			f.width = field->length;
			f.scale = f.precision = 0;
		}
	}
	else {
		lastid = (int)mysql_insert_id(mysql);
		if(lastid) {
			SqlColumnInfo& f = info.Add();
			f.width = f.scale = f.precision = 0;
			f.binary = false;
			f.type = DOUBLE_V;
			f.name = "LAST_INSERT_ID";
			rows = 1;
		}
	}
	return true;
}


This is only temporary solution (We can read only first select output from procedure, but we don't lose synchronization with MySQL server).


U++ - one framework to rule them all.

[Updated on: Sat, 16 February 2013 13:30]

Report message to a moderator

Re: [BUG] MySQL error 2014 while reading output from stored procedure. [message #42543 is a reply to message #39107] Sun, 23 March 2014 16:28 Go to previous message
mirek is currently offline  mirek
Messages: 13984
Registered: November 2005
Ultimate Member
Makes sense, thank you. Sorry for the long delay.

Mirek
Previous Topic: SqlArray view update
Next Topic: Schema description documentation not matching code behavior
Goto Forum:
  


Current Time: Sun Jun 16 06:58:28 CEST 2024

Total time taken to generate the page: 0.02683 seconds