# incorrect determining column type from mysql server

**URL:** <https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240>\
**Category:** Databases\
**Created:** [May 4, 2014, 4:10pm UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240 "2014-05-04T16:10:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jaroslav\_Pavlu](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jaroslav\_Pavlu](https://forum.xojo.com/u/Jaroslav_Pavlu)\
**Post date:** [May 4, 2014, 4:10pm UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/1 "2014-05-04T16:10:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Etienne\_Pilon1](https://forum.xojo.com/user_avatar/forum.xojo.com/etienne_pilon1/32/4362_2.png) [@Etienne\_Pilon1](https://forum.xojo.com/u/Etienne_Pilon1)\
**Post date:** [August 4, 2015, 2:46am UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/2 "2015-08-04T02:46:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Eli\_Ott](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/5f8ce5/32.png) [@Eli\_Ott](https://forum.xojo.com/u/Eli_Ott)\
**Post date:** [August 4, 2015, 5:57am UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/3 "2015-08-04T05:57:50Z")

</div>

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

Workaround: Database.FieldSchema returns the correct value.

---

<div class="post-metadata">

**Author:** ![Eli\_Ott](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/5f8ce5/32.png) [@Eli\_Ott](https://forum.xojo.com/u/Eli_Ott)\
**Post date:** [August 4, 2015, 6:15am UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/4 "2015-08-04T06:15:06Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![David\_Cox](https://forum.xojo.com/user_avatar/forum.xojo.com/david_cox/32/227_2.png) [@David\_Cox](https://forum.xojo.com/u/David_Cox)\
**Post date:** [August 4, 2015, 10:46am UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/5 "2015-08-04T10:46:44Z")

</div>

I use:

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

then cycle through the fields

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [August 4, 2015, 1:48pm UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/6 "2015-08-04T13:48:49Z")

</div>

\<[https://xojo.com/issue/40304](https://xojo.com/issue/40304)\>

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:12pm UTC](https://forum.xojo.com/t/incorrect-determining-column-type-from-mysql-server/16240/7 "2020-10-29T17:12:39Z")

</div>