Is RecordSet.ColumnType completely wrong for mySQL?

Databases
1

In trying to automate database access, I have run into Columntype giving wildly different results than the field definitions.
I can work around this by using the full column information from the mySQL database itself, but is this a known bug or did I get something wrong?
Just a few fields read from the recordset and their real values. They are not even the same for the same field types, and what is a -1 columntype?

{ | Fieldname | ColumnType | mySQL-FieldType | "RowID": 1, INT(11) "Deleted": 5, TINYINT(1) "Anrede": 5, VARCHAR "Titel": 5, VARCHAR "Name": 5, VARCHAR "Vorname": 10, VARCHAR "GeburtsDatum": 19, DATETIME "RowIDPLZP": 3, INT(11) "RowIDPLZF": 1, INT(11) "AddrPrimPrivat": 5, TINYINT(1) "Ersteller": 5, VARCHAR "GeandertVon": 10, VARCHAR "ErstelltAm": -1 TIMESTAMP }

2

@Ulrich Bogun — Maybe an SQLdump would give different results because it is aimed at being exchangeable (sort of)?

Column information would probably give you only information about the current DB, e.g. RowID uses only 11 bits at the moment.

3

That’s what I meant with the workaround. I am doing a SQLSelect(“Show Full columns From” theTable) and that gives reliable and correct results. But ColumnType is meant to show them too, only in a different way, right? If so, I would consider this a bug and wonder if someone else ran into it.

4

I am writing code that DEPENDS on that being right for all the DBs Xojo supports but so far I have been only dealing with SQLite which is problematic with column types anyway…

It looks like I am making big mistake depending com ColumnType at all!!!

BTW for the general case to be able to handle any recordset, it’s more complicated than getting individual table definitions… There can be multiple-table selects and we as views at least, so not so simple… ColumnTypes really need to me more accurate, and I would appreciate Xojo providing the size info if defined in the column definition…

5

Why ?
There’s nothing a Xojo type can do with the size
There’s no Int(11), no String(10), Decimal(15,2) etc
Unless you pull everything into a string and then manipulate it that way the size defined in the DB is pretty useless

6

Please ignore the size information. See that rowID (an int) gives a ColumnType value of 1 – Byte; Deleted (TinyInt) 5 (VarChar), the first Row… field is an integer (3) but the next one has a column type value of 1 although it is an integer, and then there is the -1 value which is not listed as a column type at all.

EDIT: A table would have made it much clearer. Please imagine a header
| Fieldname | ColumnType | mySQL-FieldType |

7

[quote=440131:@Norman Palardy]Why ?
There’s nothing a Xojo type can do with the size
There’s no Int(11), no String(10), Decimal(15,2) etc
Unless you pull everything into a string and then manipulate it that way the size defined in the DB is pretty useless[/quote]

For the code I am writing it would help knowing sizes for character types. For a real number it would help knowing if it should be a single or a double.

In the above example it looks like Int(11) = Currency… If that is “official” (and not just being used that way) then that is what should be returned… If not the closest to to a lossless type for what I assume Int(11) means would be int64 … and I would expect Xojo be smart enough to return that! (though I suspect that Xojo DB code was written before Int64 was available).

But in any case a TinyInt should not be classified as text!

1 Like
8

Never mind

9

Sorry - Forget all that!
idxfield is 1-based while column type is 0-based. ItÂ’s so time for API 2.0!

10

IDXField is a real problem. I agree that this will be one of the most welcome additions in API 2.0.