Schema API supports unsigned integers, but Database API does not allow inserting/updating unsigned integers.

I think it's because there's %u placeholder. Any chance to get this fixed for D6?

Please note that one could workaround this building the SQL statement by hand, but this is not an option when using a CCK field that implements an unsigned integer because CCK uses db_type_placeholder(). If %u was added in D6, then CCK would have to be fixed as well (to use db_type_placeholder properly for unsigneds).

Comments

markus_petrux’s picture

Oops! typo: I tried to mean "there's NOT %u placeholder".

Damien Tournoud’s picture

PHP does not support unsigned integers. What exactly do you want to do?

markus_petrux’s picture

Integer size in PHP is platform dependent, which means DB unsigned ints are supported by PHP on x64 CPUs. But on x32 CPUs, numbers beyond builtin integer bounds are interpreted as a float instead, so you can still use 4294967295 + 1.

Please, test the following on x32:

print gettype(4294967295) ."\n"; // output: (double)
print sprintf("%u", 4294967295) ."\n"; // output: 4294967295
print sprintf("%d", 4294967295) ."\n"; // output: -1

When using %d for an unsigned int in db_query(), 4294967295 is converted to -1 by the %d placeholder, and updating a INT UNSIGNED field in the DB with -1 you get 0. :(

But, if we could use %u, then it would work. :)

Example of what we need:

// This does not work as expected.
db_query("INSERT INTO {foo} (my_unsigned_int) VALUES(%d)", 4294967295);

// This is not supported by Database API due to lack of %u placeholder.
db_query("INSERT INTO {foo} (my_unsigned_int) VALUES(%u)", 4294967295);

We also need to support unsigned integers for this:
http://drupal.org/project/formatted_number

markus_petrux’s picture

Please note that Schema API supports unsigned integers, and even supports bigint and unsigned bigint, but ....how do you insert data into such fields?

bigint support is much more complex, because it might be necessary to use BCMath/GMP extensions. That's another war I would not like to mix here.

But... support for unsigned integers? Please, consider adding this to D6. Otherwise we are restricted to -2147483648 / 2147483647 with both PHP and DB Layers able to deal with bigger numbers.

Chris Charlton’s picture

subscribing.

Damien Tournoud’s picture

Ok, we should replace:

    case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
      return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe

by

    case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
      return round(array_shift($args)); // We don't need db_escape_string as numbers are db-safe

Right?

As a side note, the %d modifier is the dumbest modifier ever. sprintf("%d", 4294967295) == "-1"? Come on, PHP is supposed to be loosely typed!

phpdiva’s picture

Title: Cannot insert/update unsigned integers » Cannot insert/update unsigned integers or numbers bigger than an integer

Ran into the bigint issue while using drupal_write_record().
Apparently the problem lies in db_type_placeholder() function, which uses '%d' as the placeholder for all 'int' type columns, regardless of size.

case 'serial':
case 'int':
  return '%d';
adzio’s picture

I am, too, affected by the discrepancy between the ability to create 'unsigned int' columns in the db and the absence of the '%u' modifier equivalent in database.inc.

Substituting the int cast with round() is far from ideal, because of how the result of round() depends on the fraction (clearly seen in the examples in the php manual) and PHP_ROUND_HALF_DOWN is not implemented in versions of PHP earlier than 5.3. Also, augmenting the '%d' modifier in database.inc is also not advisable as this would constitute a departure from the printf convention which seems to be followed here, where '%d' signifies a signed integer. Both approaches could potentially break existing code that universally relies on those modifiers returning expected values within their appropriate ranges.

eaton’s picture

Just ran into this due to Twitter module's issues with the number of total twitter statuses rolling over the signed int limit. To reiterate: we must do one of the following:

  1. Make db_placeholders() smart enough to do something other than '%d' if the column is specified as a bigint or an unsigned int.
  2. Remove support for unsigned ints and bigints from SchemaAPI.
  3. Remove the db_write_record() function, as it prevents developers from using %n or similar workaround placeholders in hand-written queries.

Currently, we're giving developers a data type that amounts to a trap.

Dave Reid’s picture

Yikes. Subscribing.

mikeytown2’s picture

I would opt for option 1.
http://api.drupal.org/api/function/db_type_placeholder
http://drupal.org/node/159605

Pass the size variable.

    case 'int':
      return $size == 'big' ? '%n' : '%d';

I don't see an easy way to handle unsigned ints right now.

EDIT:
Scratch the above idea... create a new type called bigint

Chris Johnson’s picture

I advocate changing the priority of this ticket to critical and that once a reasonable fix is found, it be pushed into the next release of Drupal 6.

This is a multitude of problems just waiting to happen as the number of "things" get beyond signed 32 bit integers -- possibly including security holes.

Gábor Hojtsy’s picture

Status: Active » Closed (duplicate)

Duplicate of #499254: BIGINT handling which is more recent, but have a patch under discussion.

markus_petrux’s picture

Status: Closed (duplicate) » Active

Gàbor: The other issue seems to be more focussed on partial BIGINT support, so this is not a dup. This one is mostly focussed on unsigned int support.

markus_petrux’s picture

Status: Active » Closed (duplicate)

I posted a patch to the other issue to avail this opportunity to also add support for unsigned ints. It looks like just one issue is enough.

alexanderpas’s picture

Status: Closed (duplicate) » Active

not a dup.

Anjuanoo’s picture

I am trying to add a user and insert the user details to the drupal database.
Can anyone help me with the php code for the same.?

xurizaemon’s picture

Issue summary: View changes
Status: Active » Closed (duplicate)

No input on this in a long time, marking dupe of #2205277: Twitpocalypse issues redux.