mysql max integer value

  1. 4 months ago

    Alexandre C

    Mar 31 Pre-Release Testers, Xojo Pro Brazil

    Hi!

    Here is part of my code:
    (it's on desktop app, button action)

    dim rs as RecordSet
    dim i, fim as integer
    
    rs=app.g_db.SQLSelect("select max(id) id from zendesk")
    fim=rs.field("id").IntegerValue+1

    here is the "debug of fim"

    fim = -14163

    and, of course, this is the "select max(id) id from zendesk" result from my database…
    id= 51372

    … any idea what's going on here??

    Best regards,

    Alex

  2. Dave S

    Mar 31 San Diego, California USA

    51372 and -14163 have the same lower 16 bits
    so my question would be... what is the mySQL datatype for ID?
    if it is a 16bit Integer than that would explain it. as the maximum POSTIVE value would be 32767 (51372 overflows and rolls over to -14163

  3. Alexandre C

    Apr 1 Pre-Release Testers, Xojo Pro Brazil

    It's UNSIGNED SMALLINT 2-byte (65,535)

  4. Dave S

    Apr 1 San Diego, California USA
    Edited 4 months ago

    @Alexandre C It's UNSIGNED SMALLINT 2-byte (65,535)

    then that is you answer.. it is doing exactly what I described above.

    SMALLINT is "a 16bit Integer"

    but I bet Xojo converts it to a SIGNED Integer thus the issue you see

  5. Alexandre C

    Apr 1 Pre-Release Testers, Xojo Pro Brazil

    ok… but:

    XOJO int Range

    • 2,147,483,648 to 2,147,483,647 (32-bit apps)
    • 9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 (64-bit apps)

    Just to be clear.
    on mysql, my id is unsigned small int: 65,535
    on xojo, my id is signed int on 64-bit app: +9,223,372,036,854,775,807

    So, i really didn't figure out how it's possible!

  6. Dave S

    Apr 1 San Diego, California USA

    It all depends on what Xojo does to convert a SMALLINT from mySQL to an INTEGER via the database interface.
    Since Database field doesn't have the equivalent of UINT16 it ends up a INT32
    and if you take the 16bits of -14163 and map them into a 32bit space, you end up with a POSITIVE number
    the most significant bit indicates SIGN.... the most significant bit of a UINT16 is bit #15, but it is bit#31 in an INT32

    so none of this suprised me in the slightest.

    1100 1000 1010 1100 = 0xC8AC as a 16bit number, but it is 0x0000C8AC as a 32bit number, drastically changing the "meaning" of that first bit

  7. Alexandre C

    Apr 1 Pre-Release Testers, Xojo Pro Brazil
    Edited 4 months ago

    i changed the XOJO variable from rs.field("xpto").integerValue to rs.field("xpto").Int64Value
    same problem

    changed the mysql from smallint to integer, and rollback on xojo, from int64 to integer.
    solved.

    i really don't understand why this is happening.

    so "unsigned smallint" from mysql does not work correctly on xojo?

  8. Dave S

    Apr 1 San Diego, California USA
    Edited 3 months ago

    why would you have expected the results to change.... mapping a 16bit value to a 32bit or 64bit results in the same end result

    this is a "Hail Mary"... but try something like this

    dim rs as RecordSet
    dim i as integer
    dim fim as UINT16
    
    rs=app.g_db.SQLSelect("select max(id) id from zendesk")
    fim=rs.field("id").IntegerValue+1
  9. Michelangelo G

    is not verified Apr 2 Casciana Terme, Pisa, Italy

    Try to use nativetype and memory block:

    Dim data As MemoryBlock
    data = rs.Field("id").NativeValue
    dim tmp as UInt16 = data.UInt16Value(0)

  10. Christian S

    Apr 3 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    You could fix this by casting to integer type in sql.

    And report bug in Feedback app that uint16 handling is broken.

  11. Alexandre C

    Apr 3 Pre-Release Testers, Xojo Pro Brazil

    @ChristianSchmitz You could fix this by casting to integer type in sql.

    Yes i can, sure!

    But it's crazy to change all columns i have set to small int to int because this problem.
    I have about 97 tables with at least 1 small int column per table… and indexes also.

    I do not want to modify mysql structure…

    And i reported another problem like this on 21 Oct 2015…

    "41294 - Using DownTo with a For loop is buggy with unsigned loop counters"


    dim i as UInt8 // or Uint16
    for i=2 downto 0
    i=i
    next

    Expected Result: do not enter anymore after i=0
    Actual Result: enter after i=0
    Workarounds: need to put an "if i=0 then exit"

    So… 3 years and the problem persists…

  12. Alexandre C

    Apr 5 Pre-Release Testers, Xojo Pro Brazil

    No one on Xojo will comment on this?

  13. Dave S

    Apr 5 San Diego, California USA

    @Alexandre C No one on Xojo will comment on this?

    What is there to comment on.... I think the above posts explain it all....

  14. Alexandre C

    Apr 5 Pre-Release Testers, Xojo Pro Brazil

    Yes. Well explained in fact!

    But it's a bug or not?

    I'll need to change my columns on mysql to fix this?

  15. Dave S

    Apr 5 San Diego, California USA

    did you attempt any of the suggested work arounds?
    I don't have a mySQL setup to test with.

    This wouldn't be an issue with SQLite since it doesn't really have strict datatypes

  16. Alexandre C

    Apr 5 Pre-Release Testers, Xojo Pro Brazil

    yes. changing the datatype works perfectly.

    but i don't want to change the datatypes on mysql

    and i also don't want to do what Michelangelo Giacomelli suggested, because i'll need to change it on a lot of places on my app

  17. Alexandre C

    Apr 5 Pre-Release Testers, Xojo Pro Brazil

    @ChristianSchmitz report bug in Feedback app that uint16 handling is broken.

    i did…

  18. 3 months ago

    Alexandre C

    May 13 Pre-Release Testers, Xojo Pro Brazil

    No one on xojo.inc will try to fix this?

  19. Dave S

    May 13 San Diego, California USA

    @Alexandre C No one on xojo.inc will try to fix this?

    And who has said it wasn't be considered? You filed the feedback less than a month ago... it may be a year or more before it makes it into a production release... if then.

  20. Derk J

    May 14 Pre-Release Testers, Xojo Pro
    Edited 3 months ago

    You’r expecting 0 not to be included.
    But “Down to 0” does define 0 to be included, in english (correct me if i’m wrong). I expect “down to the last detail” to do the same, but not to leave the last detail.

    Perhaps you should change your code to DownTo 1 (is inclusive)

    Also your expected result says:
    Do not enter any more after i=0

    I see it enters 0 still but nothing after that?

  21. Newer ›

or Sign Up to reply!