Is RecordSet.ColumnType completely wrong for mySQL?

  1. 6 months ago

    Ulrich B

    5 Jun 2019 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...
    Edited 6 months ago

    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
    }

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

  2. @Ulrich B — 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. Ulrich B

    5 Jun 2019 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...

    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. Karen A

    5 Jun 2019 Pre-Release Testers

    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. Norman P

    5 Jun 2019 Pre-Release Testers, Xojo Pro under THE bus

    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. Ulrich B

    5 Jun 2019 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...
    Edited 6 months ago

    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. Karen A

    5 Jun 2019 Pre-Release Testers

    @Norman P 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

    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!

  8. Karen A

    5 Jun 2019 Pre-Release Testers
    Edited 6 months ago

    Never mind

  9. Ulrich B

    5 Jun 2019 Pre-Release Testers, Xojo Pro Answer Europe (Germany, Berlin) · xo...

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

  10. Bob K

    5 Jun 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Ulrich B idxfield is 1-based while column type is 0-based. It’s so time for API 2.0!

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

or Sign Up to reply!