incorrect determining column type from mysql server

Hello,

I have problem with determining of column type. My database engine is MySQL version 5.6.16. In my database for example, I have column declared as decimal(7.2). In my app I have commonly declared database as MySQLCommunityServer. I load table from mysql server to the recordset and if I try to determine column type by ColumnType method, the result is -1 for this column. I expected 6 or 7 but -1 is not in contained in datatype table. Where is problem, I suppose that xojo cannot rightly determine column type from mysql.

i have the same problem with DECIMAL, sometime it returns -1, or 3 or 5 … no one had an idea how to fix that ?

It does indeed return -1. For a DECIMAL field the correct value is 13.

Workaround: Database.FieldSchema returns the correct value.

This is the source code from Extras/Database Plugin Resources/MySQL/source/MySQLCursor.cpp:

[code]/*
** Returns the REALbasic type id of the given column.
*/
int MySQLCommonCursor::ColumnType(int index) {
if (mFields == nullptr) return -1;

switch (mFields[index].type) {
	case MYSQL_TYPE_TINY:
		return dbTypeByte;
		
	case MYSQL_TYPE_SHORT:
		return dbTypeShort;
	
	case MYSQL_TYPE_INT24:
	case MYSQL_TYPE_LONG:
		return dbTypeLong;

	case MYSQL_TYPE_LONGLONG:
		return dbTypeInt64;
		
	case MYSQL_TYPE_FLOAT:
		return dbTypeFloat;
		
	case MYSQL_TYPE_DOUBLE:
		return dbTypeDouble;
		
	case MYSQL_TYPE_TIMESTAMP:
		return dbTypeTimeStamp;
		
	case MYSQL_TYPE_DATE:
		return dbTypeDate;
		
	case MYSQL_TYPE_TIME:
		return dbTypeTime;
		
	case MYSQL_TYPE_DATETIME:
		return dbTypeTimeStamp;
		
	case MYSQL_TYPE_TINY_BLOB:
	case MYSQL_TYPE_MEDIUM_BLOB:
	case MYSQL_TYPE_LONG_BLOB:
	case MYSQL_TYPE_BLOB:
		return dbTypeBinary;
		
	case MYSQL_TYPE_VAR_STRING:
	case MYSQL_TYPE_STRING:
		return dbTypeText;
		
	default:
		return -1;
};

}[/code]
There is no MYSQL_TYPE_DECIMAL branch, so -1 is returned.

Interestingly the field type enum contains the Decimal data type, but it is not used anywhere in the code:

enum dbFieldType { dbTypeNull = 0, // 0 dbTypeByte, // 1 ... dbTypeDecimal, // 13 ... }

There is also no dbTypeDecimal branch in

void MySQLCursor::ColumnValue(int column, Ptr *value, unsigned char *type, int *length)

which is the function which is used for getting the values out of the record set.

I use:

  rs = db.SQLSelect("SHOW COLUMNS FROM " + myTable)

then cycle through the fields

<https://xojo.com/issue/40304>