MySQL datatype-fieldtype

[serge_girard]

Since some time I'm using MySQl as a replacement for DBF.
When I want to known the table structure I use information_schema.COLUMNS and
information_schema.TABLES in order to get the right and original datatypes.

This, of course, involves 2 steps:
1) query for fieldnames+fieldtypes
2) my actual query

Retrieving fieldsnames is OK but fieldtypes will always return types C, L, D, M, N and U.

I need to known which datatype in MySQL is used. In MYSQL.CH I see:

Code: Select all

#define MYSQL_TYPE_DECIMAL      0
#define MYSQL_TYPE_TIMESTAMP    7
...
#define MYSQL_TYPE_VAR_STRING   253
 

Code: Select all

cQ      :=  "SELECT FIELD1 AS 'F1', "
cQ      +=  "FIELD2 AS 'F2', "
cQ      +=  "FIELD3 AS 'F3', "
cQ      +=  "CONCAT( ROUND(SUM(LENGTH(FIELD1) + LENGTH(FIELD2) + LENGTH(FIELD3) / 1024 )), 'kB') AS 'SIZE' "
cQ      +=  "FROM SOME_TABLE "
cQ      +=  "WHERE 1 "

cQ      := dbo:Query( cQ )
FOR j = 1 TO cQ:FCOUNT()   
   ? J, cQ:FieldName(J), cQ:FieldTYPE(J) // DBF FIELDTYPE BLOB-->M   TIMESTAMP-->U
   // #  ok              DBF-FIELDTYPE 
NEXT
 

It has to do with:

Code: Select all

METHOD FieldType( nNum ) CLASS TMySQLRow

How can I retrieve MySQL datatypes?
Rathi or Dragan or someone else does knwon how to get it?

Thanks, Serge

[Rathinagiri]

As you can see TMySQLQuery Class is having aFieldStruct as data.

We can use this as in your example,

Code: Select all

aFieldStruct := CQ:aFieldStruct 
for i := 1 to len( aFieldStruct )
   ? aFieldStruct[ i, 4 ] // will give you a number which is defined in mysql.ch as below

#define MYSQL_TYPE_DECIMAL      0
#define MYSQL_TYPE_TINY         1  /* NOTE: TINY is used to map clipper logical values to MySQL tables, so 0 == .F., 1 == .T. */
#define MYSQL_TYPE_SHORT        2
#define MYSQL_TYPE_LONG         3
#define MYSQL_TYPE_FLOAT        4
#define MYSQL_TYPE_DOUBLE       5
#define MYSQL_TYPE_NULL         6
#define MYSQL_TYPE_TIMESTAMP    7
#define MYSQL_TYPE_LONGLONG     8
#define MYSQL_TYPE_INT24        9
#define MYSQL_TYPE_DATE         10
#define MYSQL_TYPE_TIME         11
#define MYSQL_TYPE_DATETIME     12
#define MYSQL_TYPE_YEAR         13
#define MYSQL_TYPE_NEWDATE      14
#define MYSQL_TYPE_VARCHAR      15
#define MYSQL_TYPE_BIT          16
#define MYSQL_TYPE_NEWDECIMAL   246
#define MYSQL_TYPE_ENUM         247
#define MYSQL_TYPE_SET          248
#define MYSQL_TYPE_TINY_BLOB    249
#define MYSQL_TYPE_MEDIUM_BLOB  250
#define MYSQL_TYPE_LONG_BLOB    251
#define MYSQL_TYPE_BLOB         252
#define MYSQL_TYPE_VAR_STRING   253
#define MYSQL_TYPE_STRING       254
#define MYSQL_TYPE_GEOMETRY     255

Hope this helps.

[serge_girard]

Thanks Rathi ! I knew you could help me out!
Thanks so much!

Serge

[Rathinagiri]

You are welcome Serge.