[SR-Users] Negative value returned when using sql_pvquery

[Daniel-Constantin Mierla]

Hello,

the negative integers have the first bit 1 in the 32bit representation
of a signed integer. So practically 2^31 (2 147 483 648) to 2^32 - 1 ( 4
294 967 296 - 1) are the negative integers, which for unsigned int
representation are the higher values. As the other Daniel said, those
numbers are converted to negative value as: N - 2^32 (where N is the
number greater than 2^31).

For your case, the best is to use mysql cast function in sql_pvquery()
and get it as string inside kamailio.cfg, no matter what is the type of
the column in the database table. See cast/convert operators/functions
in the mysql manual.

Cheers,
Daniel

On 06/01/16 11:48, Jonathan Hunter wrote:
> Hi to both Daniels and thank you for your responses.
>
> I understand what you  are both saying.
>
> Currently the field I am extracting the value from in the database is
> set to type;
>
> bigint(20) unsigned   
>
> If I change to varchar for example it returns fine, my only issue is
> that the overflow only occurs on 10 digit numbers starting with 2 or 3.
>
> If I add a value for example 1785702370 or 7785702370 they are
> returned without issue without changing the DB value type.
>
> Thanks
>
> Jon
>
> > To: sr-users at lists.sip-router.org
> > From: miconda at gmail.com
> > Date: Tue, 5 Jan 2016 19:49:02 +0100
> > Subject: Re: [SR-Users] Negative value returned when using sql_pvquery
> >
> >
> >
> > On 05/01/16 17:51, Daniel Tryba wrote:
> > > On Tue, Jan 05, 2016 at 03:38:58PM +0000, Jonathan Hunter wrote:
> > >> sql_pvquery("cd","select DestinationMsisdn,SourceMsisdn from
> MsisdnPoolAllocations where
> PoolMsisdn='$rU'","$var(MOdest),$var(NewSourceMSISDN)");
> > >> However this returns a value of -509264926 for $var(MOdest) which
> should just be the 3785702370 number.
> > >> What can cause kamailio to interpret this as a negative value?
> Has anyone seen this before?
> > > What you are seeing is an integer overflow, in this case you are
> trying
> > > to store a number greater than 2^31 in a signed 32bit int. -509264926
> > > (3785702370-2^32) is the correct answer if the var is a signed 32bit
> > > int.
> > >
> > > I treat phonenumbers as strings (both in the database and kamailio)
> > > since I store them as E.164 with a leading + (which results in a bit
> > > more diskspace)
> > >
> > > If you don't need the number as int in kamailio, try casting it to a
> > > string in the query.
> > >
> > To complete, as just looked at the source -- if the bigint number
> > returned does not fit in 32bit size, then it is stored as string. If it
> > fits in 32bit, then is stored also as int. I see the code was added in
> > 2011 by Alex Hermann.
> >
> > Maybe the behavior is not that coherent, hard to predict if not knowing
> > what is in the db, and should be changed to be always stored as string,
> > then use {s.int} in config if wanted as int.
> >
> > Cheers,
> > Daniel
> >
> > --
> > Daniel-Constantin Mierla
> > http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
> > Book: SIP Routing With Kamailio - http://www.asipto.com
> > http://miconda.eu
> >
> >
> > _______________________________________________
> > SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
> > sr-users at lists.sip-router.org
> > http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- 
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Book: SIP Routing With Kamailio - http://www.asipto.com
http://miconda.eu

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160106/7f6ed5e8/attachment.html>