2

I have a MySQL UDF that returns unsigned long long value. When i call this function in MySQL and the Data has been saved in database, MySQL returns warning:
"BIGINT UNSIGNED value is out of range".
How can i save large unsigned long long numbers in database?

MySQL version: 5.5.43
OS : Ubuntu 14.04

please help.

Ghasem Pahlavan
  • 581
  • 6
  • 19
  • What is your column definition? A bigint is 8 bytes and an unsigned long long is (usually) also 8 bytes. It should fit. Also, it would help to show us the code and exactly where the error occurs. – Ted Hopp Jun 08 '15 at 07:11

1 Answers1

1

Assuming your unsigned long long data type is 64-bit, it should fit quite well into the MySQL BIGINT UNSIGNED type, which is also 64 bits.

I would suggest checking your C implementation limits.h ( or climits for C++) file for the macro ULONG_MAX to ensure it is only 64 bits.

You should be aware however that there are certain operations you can perform on an unsigned value that will also give you this error. It may not be applicable if you're simply storing the exact value returned from the UDF but, if you're doing something like:

select my_udf() - 22 from somewhere

and my_udf() returns 15 (for example), you'll see that error message because -7 cannot be represented in an unsigned type.

If that is the case, the solution may be as simple as casting the return value to a signed type, but we would need more information on what it can return to advise correctly.

paxdiablo
  • 772,407
  • 210
  • 1,477
  • 1,841
  • 1
    thanks paxdiablo for reply.I found out that when i create UDF in MySQL and set return type to INTEGER, the MySQL considers this type as "long long" not "unsigned long long" and this is the problem. how can i fix this? – Ghasem Pahlavan Jun 08 '15 at 09:53
  • @Ghasem, from the doco, it appears you can only do integer, real and string. You may have to return the value as a string (such as with `sprintf` in the C code) and coerce it into the correct type back in SQL. – paxdiablo Jun 08 '15 at 10:35
  • 1
    I used casting the return value to a unsigned type and this solved my problem. thanks paxdiablo :) – Ghasem Pahlavan Jun 08 '15 at 14:06