Problem with bigint datatype in user defined function
-
I've created a user defined function that converts an IP address from a char into its decimal form. It should be returning a bigint data type. If the result is bigger than an int datatype (for IP 139.55.221.81), I'm receiving the following error:
Server: Msg 8115, Level 16, State 2, Procedure udf_IPAddressToInt, Line 17
Arithmetic overflow error converting expression to data type int.
CREATE FUNCTION dbo.udf_IPAddressToInt (@IP as char(15) )
RETURNS bigint
AS
BEGIN
Declare @Octet1 as int
Declare @Octet2 as int
Declare @Octet3 as int
Declare @Octet4 as int
Declare @LongIP as bigint
Set @Octet1 = cast(substring(@IP, 1, charindex('.', @IP)-1)as int)
Set @Octet2 = cast(substring(@IP, charindex('.', @IP) + 1,charindex('.', @IP, charindex('.', @IP) + 1) - charindex('.', @IP) - 1) as int)
Set @Octet3 = cast(reverse(substring(reverse(@IP), charindex('.', reverse(@IP)) + 1, charindex('.', reverse(@IP), charindex('.', reverse(@IP)) + 1) - charindex('.', reverse(@IP)) - 1)) as int)
Set @Octet4 = cast(reverse(substring(reverse(@IP), 1, charindex('.', reverse(@IP)) - 1)) as int)
Set @LongIP = cast(((@Octet1 * 256 * 256 * 256) + (@Octet2 * 256 * 256) + (@Octet3 * 256) + (@Octet4))as bigint)
RETURN(@LongIP)
END
Does anyone have any ideas why the function is using the int data type instead of a bigint?
Wendy Schuman -
You're not telling it to use a bigint.
This: (((@Octet1 * 256 * 256 * 256
What datatype does that result in if @Octet1 is an int ? Answer: int
You need to Cast *before* multiplying. Or just declare the Octets as bigints and acept the overhead of 4 extra bytes per variables to avoid casting.
-
I got it to work. Thanks for your help. Sometimes the obvious is just too obvious.
Wendy Schuman -
Checkout the ParseName function... it was designed to parse octets in addresses and name in 4 part named database objects. Could help you to shorten your function a bit...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Can it be you're doing this exercise for some sorting reasons?
Here are several methods that will sort correctly on a VARCHAR:
SELECT
TOP 1000 n = IDENTITY(INT)
INTO
Nums
FROM
Sysobjects S1
CROSS JOIN
Sysobjects S2
SET NOCOUNT ON
-- generate table with sample data
create table T (
ipAdd varchar(15)
)
insert into T
select
rtrim(abs(binary_checksum(newid()))%256) + '.'
+ rtrim(abs(binary_checksum(newid()))%256) + '.'
+ rtrim(abs(binary_checksum(newid()))%256) + '.'
+ rtrim(abs(binary_checksum(newid()))%256)
from Northwind..Orders
go
CREATE VIEW Patterns
AS
SELECT REPLICATE('_',N1.n) + '.'
+ REPLICATE('_',N2.n) + '.'
+ REPLICATE('_',N3.n) + '.'
+ REPLICATE('_',N4.n) AS pattern,
1 AS s1, N1.n AS l1,
N1.n+2 AS s2, N2.n AS l2,
N1.n+N2.n+3 AS s3, N3.n AS l3,
N1.n+N2.n+N3.n+4 AS s4, N4.n AS l4
FROM Nums AS N1, Nums AS N2, Nums AS N3, Nums AS N4
WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3
GO
SELECT T.*
FROM T JOIN Patterns
ON ipAdd LIKE pattern
ORDER BY
CAST(SUBSTRING(ipAdd,s1,l1) AS TINYINT),
CAST(SUBSTRING(ipAdd,s2,l2) AS TINYINT),
CAST(SUBSTRING(ipAdd,s3,l3) AS TINYINT),
CAST(SUBSTRING(ipAdd,s4,l4) AS TINYINT)
GO
create function ipVal (
@ipAdd varchar(15)
) returns bigint as begin
declare @a varchar(3), @b-2 varchar(3), @C varchar(3), @d varchar(3)
set @ipAdd = replace(@ipAdd,'.',space(1))
exec master..xp_sscanf @ipAdd, '%s%s%s%s', @a output, @b-2 output, @C
output, @d output
return 256.*(256.*(256.*@a + @b-2)+ @C) + @d
end
GO
select t.*
FROM T JOIN Patterns
ON ipAdd LIKE pattern
ORDER BY
dbo.ipVal(ipAdd)
SET NOCOUNT OFF
DROP TABLE Nums
drop table T
DROP VIEW patterns
DROP FUNCTION ipVal
GO
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Best approach I have found from most angles to this problem is to use 4 tinyint columns
Just my $0.02
* Noel -
Or, you could try ParseName and Replace like this... Like NoelD suggested, TinyInt comes into play... The Str and Convert functions are to zero fill the octects.
DECLARE @MyTCPIP VARCHAR(20)
SET @MyTCPIP = '12.3.156.78'
SELECT CONVERT(BIGINT,
REPLACE(
STR(CONVERT(TINYINT,PARSENAME(@MyTCPIP,4)),3)
+ STR(CONVERT(TINYINT,PARSENAME(@MyTCPIP,3)),3)
+ STR(CONVERT(TINYINT,PARSENAME(@MyTCPIP,2)),3)
+ STR(CONVERT(TINYINT,PARSENAME(@MyTCPIP,1)),3)
,' ','0')
)
--------------------
12003156078
(1 row(s) affected)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The reason we're converting the IP Addresses to their decimal equivalent is because we are holding reports that are requested by users within certain IP address ranges. Originally, the table was set up with 8 tinyint columns (begOctet1, begOctet2, endOctet1, etc.) and our client was populating the table. We ran into problems doing it that way, so we changed to IPAddressHolds table to four columns, BegIPAddress char(15), EndIPAddress char(15) and two calculated columns that use the user defined function to store the decimal equivalent as a bigint. When the stored procedure runs that issues the reports, it calls the stored procedure that checks the IPAddressHolds table to see if the user's IP address is within a range in that table. If it is, it flags the report to hold.
Thanks for everyone's comments/help on this.
Wendy Schuman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply