values of UInt8 data types get converted to 125 about crystal-mysql HOT 8 OPEN

Oh, I see, my bad. SQL is not my best language... thanks for your help

from crystal-mysql.

Ok so I played around with implementing this a bit, and I don't see any way to store an unsigned integer via the interface provided to Crystal. See the documentation for basic types and for response value types in the binary interface. Neither offer any means of differentiating between Int and UInt.

I recommend we add some error checking which simply says "use the next size bigger signed values, unsigned values don't work" but otherwise leave things as they are. If that's ok I'll submit a PR as soon as I get the chance.

from crystal-mysql.

There must be a flag in the protocol that reports the integer value as unsigned, and it's probably not supported/overlooked by the current implementation.

One should dig the MySQL protocol documentation, implement support and declare the UIntX encoder/decoder types.

from crystal-mysql.

thanks for your time, @ysbaddaden. i did some digging, and found:
https://dev.mysql.com/doc/dev/mysql-server/8.0.4/page_protocol_basic_dt_integers.html

would this help?
edit: i switched to just a signed tinyint and worked around this issue so no rush at all. ya'll prob got better things to do. but i guess some other dev might run into this.

from crystal-mysql.

The fixed and and length encoded integers are implemented in read_packet.cr. I've just noticed that read_fixed_int is returning Int, but read_lenenc_int is returning UInt. Maybe the former is should be UInt also.

Nevertheless the mapping from the types of the database to the types in crystal is in types.cr and there, the read_fixed_int/read_lenenc_int are used as helper for returning dates. For integer numbers the bytes are read from the stream in LittleEndian order as described in https://dev.mysql.com/doc/internals/en/binary-protocol-value.html .

Maybe the sign information comes in the column definition and that should be used somewhere. Ref: read_column_definitions/ColumnSpec.

When reading values there needs to be two implementation one for binary protocol (prepared statements) and one for text protocol (non prepared statements).

The lack of UInt support is probably a consequence of trying to favor Int for general purpose and leaving UInt for protocol things. That is why UInt8 does not appear in DB::TYPES.

A PR is welcome as long as a sample_value entry is added in db_spec.cr ;-)

The great reference for protocol implementation can be found in https://dev.mysql.com/doc/internals/en/

from crystal-mysql.

It doesn't appear that unsigned is a standard SQL feature. It is not supported by my version of MySQL (MariaDB 10.1.34). For example, the query

create table tbl (
  col tinyint,
  col2 unsigned tinyint
);

results in an error like:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'unsigned tinyint)' at line 1

Could you verify that the above query works in your version of MySQL? If so, which version of MySQL are you running?

from crystal-mysql.

nah it doesn't, wtf
edit: version 5.6.24

from crystal-mysql.

@dscottboggs
try this;

CREATE TABLE `example_table` (
  `example_col` tinyint(3) unsigned NOT NULL
);

this works for me! in adminer.php, i set it to 255

from crystal-mysql.