Procedure for _PrivilegedIP

saxz · Jan 10, 2015

well i never release anything at all
but let me release small thing here
no idea if it help anyone .. but usefull

Code:

USE [SRO_R_Accountdb]
GO
/****** Object:  StoredProcedure [dbo].[_AddIP]    Script Date: 01/10/2015 18:06:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_AddIP]
@ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint,@ip5 bigint , @ip6 bigint , @ip7 bigint ,@ip8 bigint
as
	declare [USER=9977]long[/USER]_ip bigint , [USER=9977]long[/USER]_ip1 bigint
	set [USER=9977]long[/USER]_ip = @ip1 + (@ip2 * 256) + (@ip3 * 256 * 256) + (@ip4 * 256 * 256 * 256)
	set [USER=9977]long[/USER]_ip1 = @ip5 + (@ip6 * 256) + (@ip7 * 256 * 256) + (@ip8 * 256 * 256 * 256)
	if( [USER=9977]long[/USER]_ip > 0x7FFFFFFF)
	begin
		set [USER=9977]long[/USER]_ip =  [USER=9977]long[/USER]_ip - 0x7FFFFFFF - 2) - 0x7FFFFFFF
		
	end
	if( [USER=9977]long[/USER]_ip1 > 0x7FFFFFFF)
	begin
		set [USER=9977]long[/USER]_ip1 =  [USER=9977]long[/USER]_ip1 - 0x7FFFFFFF - 2) - 0x7FFFFFFF
		
	end
	insert into _PrivilegedIP values [USER=9977]long[/USER]_ip, [USER=9977]long[/USER]_ip1)

btw : this only will work with the original _PrivilegedIP table
i will leave you to know how to use it ^^
have fun

denise456 · Jan 10, 2015

Msg 155, Level 15, State 2, Procedure _AddIP, Line 4
'long' is not a recognized CURSOR option.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 7
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 12
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 17
Incorrect syntax near 'MENTION=9977'.

saxz · Jan 10, 2015

denise456 said:
Msg 155, Level 15, State 2, Procedure _AddIP, Line 4
'long' is not a recognized CURSOR option.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 7
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 12
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 17
Incorrect syntax near 'MENTION=9977'.

i edited the topic with the working one , copy it

123we · Jan 10, 2015

Better is manual adding. But easy to add a IP via EXEC _AddIP xxx.xxx.xxx.xxx

saxz · Jan 10, 2015

123we said:
Better is manual adding. But easy to add a IP via EXEC _AddIP xxx.xxx.xxx.xxx

well i am working with the original _PrivilegedIP , not the edited released one over the net .

RenePunik · Jan 10, 2015

I think it will be useful for who uses csro files, thanks and keep going.

saxz · Jan 10, 2015

RenePunik said:
I think it will be useful for who uses csro files, thanks and keep going.

well it can be used with any Account_DB, since any Account_DB released with the original _PrivilegedlP table

RenePunik · Jan 10, 2015

saxz said:
well it can be used with any Account_DB, since any Account_DB released with the original _PrivilegedlP table

It has any new benefits?

tt1:

saxz · Jan 10, 2015

RenePunik said:
It has any new benefits? tt1:

maybe xD

NourAyman · Jan 10, 2015

" @ip1 + (@ip2 * 256) " -- int :/
IP Converter
umm . . Thank's.

saxz · Jan 10, 2015

NourAyman said:
" @ip1 + (@ip2 * 256) " -- int :/
IP Converter
umm . . Thank's.

just tried to help ^^

ilowe · Jan 10, 2015

cSro r ?

BadFist · Jan 10, 2015

RenePunik said:
I think it will be useful for who uses csro files, thanks and keep going.

like i say on cro-r topic we can sue old privilageip

yxc001 · Jan 11, 2015

1st why dont simply use the IP1 - IP8 table.

2nd 1 IP block have a possible number from 0-255 why use BIGINT for each block? TINYINT is way enough!

and... there is a function in EVERY silkroad db called "make_ip"

lemoniscool · Jan 12, 2015

yxc001 said:
1st why dont simply use the IP1 - IP8 table.

2nd 1 IP block have a possible number from 0-255 why use BIGINT for each block? TINYINT is way enough!

and... there is a function in EVERY silkroad db called "make_ip"

thats where he copied the calculation from:

Code:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  UserDefinedFunction [dbo].[make_ip]    Script Date: 2015-01-13 00:26:12 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[make_ip] (@ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint)
returns int
as
begin
	declare  [USER=9977]long[/USER]_ip bigint
	set  [USER=9977]long[/USER]_ip = @ip1 + (@ip2 * 256) + (@ip3 * 256 * 256) + (@ip4 * 256 * 256 * 256)
	if(  [USER=9977]long[/USER]_ip > 0x7FFFFFFF)
	begin
		set  [USER=9977]long[/USER]_ip =   [USER=9977]long[/USER]_ip - 0x7FFFFFFF - 2) - 0x7FFFFFFF
	end
	return  [USER=9977]long[/USER]_ip
end

123we said:
Better is manual adding. But easy to add a IP via EXEC _AddIP xxx.xxx.xxx.xxx

i took the time to write a sp that will do what you suggested
usage: exec [dbo].[_AddIPFromString] '127.0.0.1', '127.0.255.255'

code:

Code:

USE [SRO_R_Accountdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[_AddIPFromString]
  [USER=2000069846]Sip[/USER]1 varchar(max),
  [USER=2000069846]Sip[/USER]2 varchar(max)
as
begin
	declare  [USER=9977]long[/USER]_ip1 bigint,
		  [USER=9977]long[/USER]_ip2 bigint,
			@ip1 bigint, @ip2 bigint,
			@ip3 bigint, @ip4 bigint,
			@ip5 bigint, @ip6 bigint,
			@ip7 bigint, @ip8 bigint,
			@Split char(1), @X xml

	select @ip1 = cast(PARSENAME  [USER=2000069846]Sip[/USER]1, 4) as int),
		   @ip2 = cast(PARSENAME  [USER=2000069846]Sip[/USER]1, 3) as int),
		   @ip3 = cast(PARSENAME  [USER=2000069846]Sip[/USER]1, 2) as int),
		   @ip4 = cast(PARSENAME  [USER=2000069846]Sip[/USER]1, 1) as int),
		   @ip5 = cast(PARSENAME  [USER=2000069846]Sip[/USER]2, 4) as int),
		   @ip6 = cast(PARSENAME  [USER=2000069846]Sip[/USER]2, 3) as int),
		   @ip7 = cast(PARSENAME  [USER=2000069846]Sip[/USER]2, 2) as int),
		   @ip8 = cast(PARSENAME  [USER=2000069846]Sip[/USER]2, 1) as int)

	exec  [USER=9977]long[/USER]_ip1 = dbo.make_ip @ip1, @ip2, @ip3, @ip4
	exec  [USER=9977]long[/USER]_ip2 = dbo.make_ip @ip4, @ip6, @ip7, @ip8
	
	insert into _PrivilegedIP values   [USER=9977]long[/USER]_ip1,  [USER=9977]long[/USER]_ip2)
end

have fun, use at your own risk, i will not give support

greetz

yxc001 · Jan 13, 2015

lemoniscool said:

thats where he copied the calculation from:

Code:

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  UserDefinedFunction [dbo].[make_ip]    Script Date: 2015-01-13 00:26:12 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[make_ip] (@ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint)
returns int
as
begin
    declare  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip bigint
    set  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip = @ip1 + (@ip2 * 256) + (@ip3 * 256 * 256) + (@ip4 * 256 * 256 * 256)
    if(  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip > 0x7FFFFFFF)
    begin
        set  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip =   @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip - 0x7FFFFFFF - 2) - 0x7FFFFFFF
    end
    return  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip
end

i took the time to write a sp that will do what you suggested
usage: exec [dbo].[_AddIPFromString] '127.0.0.1', '127.0.255.255'

code:

Code:

USE [SRO_R_Accountdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[_AddIPFromString]
  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]1 varchar(max),
  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]2 varchar(max)
as
begin
    declare  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip1 bigint,
          @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip2 bigint,
            @ip1 bigint, @ip2 bigint,
            @ip3 bigint, @ip4 bigint,
            @ip5 bigint, @ip6 bigint,
            @ip7 bigint, @ip8 bigint,
            @Split char(1), @X xml

    select @ip1 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]1, 4) as int),
           @ip2 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]1, 3) as int),
           @ip3 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]1, 2) as int),
           @ip4 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]1, 1) as int),
           @ip5 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]2, 4) as int),
           @ip6 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]2, 3) as int),
           @ip7 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]2, 2) as int),
           @ip8 = cast(PARSENAME  @[I][B][URL="http://forum.ragezone.com/members/2000069846.html"]Sip[/URL][/B][/I]2, 1) as int)

    exec  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip1 = dbo.make_ip @ip1, @ip2, @ip3, @ip4
    exec  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip2 = dbo.make_ip @ip4, @ip6, @ip7, @ip8
    
    insert into _PrivilegedIP values   @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip1,  @[I][B][URL="http://forum.ragezone.com/members/9977.html"]long[/URL][/B][/I]_ip2)
end

have fun, use at your own risk, i will not give support

greetz

nice query but i don't like your datatypes

lemoniscool · Jan 13, 2015

yxc001 said:
nice query but i don't like your datatypes

as you can see, the function make_ip requires bigint as parameters.. well you could of course use tinyint and hope for correct implicit conversion or convert/cast them yourself but thats unnecessary .. even with bigint the query executes in literally no time and due to the querys simplicity it doesnt really matter wheather you use tinyint or bigint or anything else to store stuff.

also i could have changed the make_ip function to require tinyint instead of bigint but (sorry to say so but its the truth) 99% of the people that will even think about using my query to add their ip to the table wouldnt understand it anymore if they had 2 function/procedure altering/adding queries, let alone if i would have combined both into one. and that while 50% of those trying to use the query will fail because they dont get that they have to remove the bbcode RZ adds to

Code:

 tags, see for yourself:

[QUOTE="denise456, post: 8313402, member: 1333398481"]Msg 155, Level 15, State 2, Procedure _AddIP, Line 4
'long' is not a recognized CURSOR option.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 7
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 12
Incorrect syntax near 'long'.
Msg 102, Level 15, State 1, Procedure _AddIP, Line 17
Incorrect syntax near '[COLOR="#FF0000"]MENTION=9977[/COLOR]'.[/QUOTE]

yxc001 · Jan 15, 2015

lemoniscool said:
as you can see, the function make_ip requires bigint as parameters.. well you could of course use tinyint and hope for correct implicit conversion or convert/cast them yourself but thats unnecessary .. even with bigint the query executes in literally no time and due to the querys simplicity it doesnt really matter wheather you use tinyint or bigint or anything else to store stuff.

also i could have changed the make_ip function to require tinyint instead of bigint but (sorry to say so but its the truth) 99% of the people that will even think about using my query to add their ip to the table wouldnt understand it anymore if they had 2 function/procedure altering/adding queries, let alone if i would have combined both into one. and that while 50% of those trying to use the query will fail because they dont get that they have to remove the bbcode RZ adds to

Code:

tags, see for yourself:[/QUOTE] I didnt noticed that the function requires bigint. I removed it since its useless for me since i use the IP1 - IP8 table which should also work on CSRO-R

Procedure for _PrivilegedIP

Supreme Arcanarch

Skilled Illusionist

Supreme Arcanarch

Newbie Spellweaver

Supreme Arcanarch

No avatar

Supreme Arcanarch

No avatar

Supreme Arcanarch

(⌐■_■)

Supreme Arcanarch

Newbie Spellweaver

kaju

Newbie Spellweaver

Master Summoner

Newbie Spellweaver

Master Summoner

Newbie Spellweaver

About Us

Online statistics

Forum statistics

RaGEZONE Sponsor