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 » Sql Avg delivers String instead of double
Sql Avg delivers String instead of double [message #43439] Wed, 30 July 2014 23:38 Go to next message
masu is currently offline  masu
Messages: 378
Registered: February 2006
Senior Member
Hi,

I noticed in one of my programs that with MySql the Avg() function on a field delivers a String type and not a double type result in response Value.

Here is the sql query I use:

SQL *	Select(	field1,
			Avg(field2), Count(field2),
			Avg(field3), Count(field3))
		.From(table1);


The DB schema is defined in a .sch file and all fields have sql type INT.
Surprisingly Count() in SQL[2] and SQL[4] delivers an expected numeric type (double) in this case which is converted to int automatically when using Value.

But I get a String in SQL[1] and SQL[3] which should also contain double type Value.

What is the reason for it to have String type instead of double?
Can somebody point me to the code location where SQL[] Value are assigned from DB response?

Regards
Matthias


931b81e7ea53320dccc37375b34b38c3
Re: Sql Avg delivers String instead of double [message #43440 is a reply to message #43439] Thu, 31 July 2014 15:27 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13984
Registered: November 2005
Ultimate Member
The problem might be in bool MySqlConnection::Execute();

See the switch there, I suspect that Avg returns some type that is not recognized there, so 'default' non-conversion to String takes the place.

After brief googling, could you please try to add FIELD_TYPE_NEW_DECIMAL to double section?

		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:
			case FIELD_TYPE_NEW_DECIMAL: // <<<<<<<<<<<<<<<<<<<<<<<<  THIS IS NEW...
				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;
		}
Re: Sql Avg delivers String instead of double [message #43443 is a reply to message #43440] Thu, 31 July 2014 19:47 Go to previous messageGo to next message
masu is currently offline  masu
Messages: 378
Registered: February 2006
Senior Member
Hi Mirek,

adding a line with FIELD_TYPE_NEWDECIMAL (with MariaDB) did the trick.
Can you add it to MySql.cpp?

Thanks!

Regards
Matthias


931b81e7ea53320dccc37375b34b38c3
Re: Sql Avg delivers String instead of double [message #43446 is a reply to message #43443] Fri, 01 August 2014 08:16 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13984
Registered: November 2005
Ultimate Member
Excellent, applied.

Mirek
Re: Sql Avg delivers String instead of double [message #43447 is a reply to message #43446] Fri, 01 August 2014 09:22 Go to previous messageGo to next message
masu is currently offline  masu
Messages: 378
Registered: February 2006
Senior Member
Hi Mirek,

you missed in my reply that the type is

FIELD_TYPE_NEWDECIMAL

and not

FIELD_TYPE_NEW_DECIMAL

Regards
Matthias


931b81e7ea53320dccc37375b34b38c3
Re: Sql Avg delivers String instead of double [message #43448 is a reply to message #43447] Fri, 01 August 2014 17:09 Go to previous message
mirek is currently offline  mirek
Messages: 13984
Registered: November 2005
Ultimate Member
Ops, thanks.

Mirek
Previous Topic: Bugfix: MySqlConnection::GetRowsProcessed() returns correct value after an sql update
Next Topic: MySql bitwise and
Goto Forum:
  


Current Time: Sun Jun 16 06:49:40 CEST 2024

Total time taken to generate the page: 0.02381 seconds