mysql max integer value

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

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

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

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!

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

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?

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

Try to use nativetype and memory block:

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

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

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

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”

[i]
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”[/i]

So… 3 years and the problem persists…

No one on Xojo will comment on this?

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

Yes. Well explained in fact!

But it’s a bug or not?

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

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

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

i did…

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.

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?