Get table stucture of MySQL and ADO

[Marc Vanzegbroeck]

Hi,

I'm converting my program from tMySQL to ADO and have still some convertion-problems.
I tMySQL I had oQry:fieldname() , oQry:fieldtype() , oQry:fieldlen() and oQry:fielddec() to get the structure of the table.

I found that I can use oRs:fields(1):name to get the name, but how can I get the type, len en dec?

Thanks,
Marc

[Enrico Maria Giordano]

Hi,

I'm converting my program from tMySQL to ADO and have still some convertion-problems.
I tMySQL I had oQry:fieldname() , oQry:fieldtype() , oQry:fieldlen() and oQry:fielddec() to get the structure of the table.

I found that I can use oRs:fields(1):name to get the name, but how can I get the type, len en dec?

Thanks,
Marc

Code: Select all  Expand view

oRs:Fields( 1 ):Type
oRs:Fields( 1 ):DefinedSize //for character types
oRs:Fields( 1 ):Precision //for numeric types
oRs:Fields( 1 ):NumericScale //for numeric types

EMG

[Marc Vanzegbroeck]

Thanks Enrico,

For a type CHAR in the database oRs:Fields( 1 ):Type returns 129. Where can I find what number is what type?


Do you know also how I know the number of fields in the table?
I tested it with len(oRs:Fields()), but the result is always 4

[Enrico Maria Giordano]

Thanks Enrico,

For a type CHAR in the database oRs:Fields( 1 ):Type returns 129. Where can I find what number is what type?

http://www.w3schools.com/ado/ado_datatypes.asp

Do you know also how I know the number of fields in the table?
I tested it with len(oRs:Fields()), but the result is always 4

The method Count() is supported by any collections:

Code: Select all  Expand view

oRs:Fields:Count()

EMG

[Marc Vanzegbroeck]

Enrico,

Thanks for the usefull link to http://www.w3schools.com/ado/ado_datatypes.asp

oRs:Fields( 1 ):DefinedSize is giving me the correct sise of a CHAR-field, but for numeric fields Precision gives not a correct value and NumericScale is always 255.

I think it's not possible to get the correct size via ADO for numeric fields.
Maybe I have to use a QUERY, mut I didn't fount one yet...

[Enrico Maria Giordano]

oRs:Fields( 1 ):DefinedSize is giving me the correct sise of a CHAR-field, but for numeric fields Precision gives not a correct value and NumericScale is always 255.

Works fine for me. Please note that you have to define the field as NUMERIC.

I think it's not possible to get the correct size via ADO for numeric fields.

Yes, it is.

EMG

[Marc Vanzegbroeck]

Works fine for me. Please note that you have to define the field as NUMERIC.

Sorry, I tested it on a fieldtype 'Double' and 'SmallInt'.
According to http://www.w3schools.com/ado/ado_datatypes.asp, numerics are only available on Access 2000 (OLEDB) , type 131.
Tmysql created 'Double' and 'SmallInt'. Thats wy I now tested it on my current database.

[Enrico Maria Giordano]

Sorry, I tested it on a fieldtype 'Double' and 'SmallInt'.

You can't get the size of that types because they have fixed sizes.

According to http://www.w3schools.com/ado/ado_datatypes.asp, numerics are only available on Access 2000 (OLEDB) , type 131.

This is not true. I tested NUMERIC with MSSQL and MySQL and found no problem.

EMG

[Marc Vanzegbroeck]

You can't get the size of that types because they have fixed sizes.

Enrico,

If I check the SQL-database with a program like HeidiSQL, I see that de size is not fixed, but just like I defined when I created them with tMySQL. I have type double, with Length 10 , Set 3 or Lengte 4, Set 1,...

Also the smallint have different sizes.

[Enrico Maria Giordano]

If I check the SQL-database with a program like HeidiSQL, I see that de size is not fixed, but just like I defined when I created them with tMySQL. I have type double, with Length 10 , Set 3 or Lengte 4, Set 1,...

Also the smallint have different sizes.

No, as an example, smallint is 2 bytes fixed length (-32768 to 32767). You have no way to extend its size.

EMG

[Marc Vanzegbroeck]

Enrico,

As you can see here, I have double of 4,1 and 15,5 and SMALLINT of 2 and 1.

[Marc Vanzegbroeck]

Enrico,

I have change my type from double to decimal, and now Precision and NumericScale are working fine!!!

Thanks

[Enrico Maria Giordano]

Enrico,

As you can see here, I have double of 4,1 and 15,5 and SMALLINT of 2 and 1.

I think that the tool you are using is not reporting the correct informations.

EMG

[Enrico Maria Giordano]

Enrico,

I have change my type from double to decimal, and now Precision and NumericScale are working fine!!!

Thanks

Great!

EMG

[avista]

Hi,
This is for Informix but may be help ...

If type is >= 256 mean that field dont allow NULLS
i use:

Code: Select all  Expand view

IF nType >= 256
   nType  := nType - 256
   lNulls := .f.
  ELSE
   nType  := nType
   lNulls := .t.
ENDIF
 

Detecting decimals

Code: Select all  Expand view

IF nType =  5
   nDec  := nLength - (INT(nLen/256)*256)
   nLen  := INT(nLength/256)
ENDIF