Lack of support for unsigned integers

[szymonwieloch]

soci fails to make correct queries with unsigned integers. In the tested scenario, we had a table with a column of unsigned integer type:

CREATE TABLE 'users' (
    'userid' INT(11) UNSIGNED NOT NULL
) ENGINE InnoDB

Both insert and select queries fail:

Insert

uint32_t userid = 3000000000;
...
use(userid)

soci internally converts the number into a signed integer - then it becomes negative. Inserting a negative value into an unsigned column obviously fails.

Select

uint32_t userid;
...
into(userid);

This works fine as long the number in the database is small enough to fit into the signed int32_t. Once it is too big (i.e. 3000000000) it throws an exception with a parameter conversion error.

The problems have been found with the latest mariadb. I am not sure if this is a general soci problem or just a problem with MySQL backend.

[vadz]

Sorry, I can only confirm that this is a known issue. Generally speaking, C++ numeric types don't really map 1-to-1 to the database types and something needs to be done about it, but it's not totally clear what nor when/who will have time to do it.

[zann1x]

Regarding the "what to do", I guess looking at what poco does could be a step in the right direction as it also has a very similar API to SOCI. But that's only my two cents.

[vadz]

I'm not familiar with POCO, unfortunately, and I also don't really have time to do much else than minimal maintenance of SOCI currently, so the best course of action for people interested in improving this aspect of SOCI would be to try to make a PR doing this.

[willw-git]

For the benefit of fellow Googlers who arrived here seeking a fix for this problem:

I have inherited code which just encountered this bug, again with a Maria back end, and Microsoft Visual C++ 2017. An unsigned 32-bit key just exceeded the signed positive integer limit.

I have hacked a work-around as follows:

• To retrieve values, I read them into a signed 32-bit int and static_cast them to unsigned.
• To write values, I passed signed 32-bit values and ANDed them with 0xffffffff in the SQL expression. This promotes the input to 64 bits and then removes the sign extension in one hit.

Ugly, but it got the application back on its feet.