Bug on unsigned values from MYSQL. CAST is buggy too

Databases
1

EDIT: THE CAST OF UNSIGNED BIGINT IS BUGGY ON MYSQL, NOT ON XOJO

Hi!

(updated my feedback case 51805)

I’m still using xojo 2017r3
(because 2018r1.1 does not run on CentOS 7.x with older kernel, and it’s not documented on xojo minimal requirements)

It’s a webapp. running on debug mode on my mac and running from a compiled cgi on site.

http://alemac.com.br/unsignedbug.jpg

SIGNED table…

[code]CREATE TABLE msi_test_signed (
id tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’,
tinyint tinyint(3) NOT NULL,
smallint smallint(5) NOT NULL,
mediumint mediumint(8) NOT NULL,
int int(10) NOT NULL,
bigint bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO msi_test_signed (id, tinyint, smallint, mediumint, int, bigint) VALUES
(1, -128, -32768, -8388608, -2147483648, -9223372036854775808),
(2, 127, 32767, 8388607, 2147483647, 9223372036854775807);
[/code]

UNSIGNED table…

[code]CREATE TABLE msi_test_unsigned (
id tinyint(3) UNSIGNED NOT NULL,
u_tinyint tinyint(3) UNSIGNED NOT NULL,
u_smallint smallint(5) UNSIGNED NOT NULL,
u_mediumint mediumint(8) UNSIGNED NOT NULL,
u_int int(10) UNSIGNED NOT NULL,
u_bigint bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO msi_test_unsigned (id, u_tinyint, u_smallint, u_mediumint, u_int, u_bigint) VALUES
(1, 127, 32767, 8388607, 2147483647, 9223372036854775807),
(2, 128, 32768, 8388608, 2147483648, 9223372036854775808),
(3, 255, 65535, 16777215, 4294967295, 18446744073709551615);[/code]

Is there an easy way to “select * from…” and cast all columns??

2

You can’t cast 9223372036854775808 or 18446744073709551615 as these overflow the MySQL signed type.
Running select cast(9223372036854775808 as signed) in MySQL Workbench provides the same result.

Have you tried casting those as char? It might mean that you have to retrieve string values from the recordset in Xojo and then convert them back to numbers?

3

[quote=394087:@Kevin Gale]You can’t cast 9223372036854775808 or 18446744073709551615 as these overflow the MySQL signed type.
Running select cast(9223372036854775808 as signed) in MySQL Workbench provides the same result.

Have you tried casting those as char? It might mean that you have to retrieve string values from the recordset in Xojo and then convert them back to numbers?[/quote]

Yes, i noticed this after created the conversation.

The big problem for me is, how to “select * from…” and cast all columns?

look at the results: http://alemac.com.br/unsignedbug.jpg

4

Unfortunately, I don’t think you can do cast on a select *. You will probably have to select each column and wrap it in a cast.