New Dilema: Item Duplication

[phiber]

yea, the extra option with my query, its you get data to make a log about who try to login two times.

[Cath22]

sir phiber can you give the proper code for this because it makes error in my world server.

[Moonfly]

SHORT CODE:


IF (SELECT COUNT(CHARACTERIDX) FROM GAMEDB..CABAL_CHARACTER_TABLE WHERE LOGIN=1 AND CHARACTERIDX/8=@CHARACTERIDX/8)>0
BEGIN
COMMIT TRAN
END




ALSO INSERT THAT TO THE FIRST LINE OF CABAL_SP_DELCHAR

if i insert that into get_cabal_character_ex, then i get error in my DBAgent log
I know it has something to do witn BEGIN and END but i cant realy get it to work without syntax check fail if i add them.

[Tue Apr 7 2009 02:37:53.151651 3086724000]: [##ERROR##] DB ERROR(MSG) : [FreeTDS][SQL Server]The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
[Tue Apr 7 2009 02:37:53.151719 3086724000]: [##ERROR##] DB ERROR(CODE) : 3902
[Tue Apr 7 2009 02:37:53.151772 3086724000]: [##ERROR##] DB ERROR(VAR_INFO) :
[Tue Apr 7 2009 02:37:53.151843 3086724000]: [##ERROR##] DB ERROR(SQLSTATE) : 25000
[Tue Apr 7 2009 02:37:53.151917 3086724000]: [##ERROR##] DB ERROR(STM_TEXT) : {call get_cabal_character_ex( ? , ? , ? )}

[Cath22]

yes me too im having that error

[cypher]

If (select count(characteridx) from gamedb.cabal_character_table where login=1 and

[Cath22]

mr.cypher can you give the full code how to do it its messing up my server. So kindly help us.

[cypher]

IF (SELECT COUNT(CHARACTERIDX) FROM GAMEDB.[character table name in here] WHERE LOGIN=1 AND CHARACTERIDX/8=@CHARACTERIDX/8)>0
BEGIN
COMMIT TRAN
END

replace [character table name in here] with your table name

[Cath22]

so like this?


/****** Object: Stored Procedure dbo.get_cabal_character_ex Script Date: 2008-4-14 5:52:47 ******/
CREATE PROCEDURE [dbo].[get_cabal_character_ex](@CHARACTERIDX int, @CHANNELIDX int, @USERIP char(16)) AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
-- CHARACTER
DECLARE @logout_time datetime, @login_time datetime, @OldRestTime AS int, @RestTime AS int
DECLARE
@CH_WORLDIDX INT,
@CH_POSITION INT,
@CH_LEV INT,
@CH_EXP BIGINT,
@CH_STR INT,
@CH_DEX INT,
@CH_INT INT,
@CH_PNT INT,
@CH_RANK INT,
@CH_ALZ BIGINT,
--@CH_ALZ_XORED BIGINT,
@CH_STYLE INT,
@CH_FLAGS INT,
@CH_HP INT,
@CH_MP INT,
@CH_SP INT,
@CH_SWDPNT INT,
@CH_MAGPNT INT,
@CH_RANKEXP INT,
@CH_PENALTYEXP INT,
@CH_WARPBFIELD INT,
@CH_MAPSBFIELD INT,
@CH_NAME VARCHAR(50),
@CH_RP INT,
@CH_REPUTATION INT,
@CH_PKPENALTY INT,
@CH_NATION TINYINT,
@CRAFTLEVEL0 INT,
@CRAFTLEVEL1 INT,
@CRAFTLEVEL2 INT,
@CRAFTLEVEL3 INT,
@CRAFTLEVEL4 INT,
@CRAFTEXP0 INT,
@CRAFTEXP1 INT,
@CRAFTEXP2 INT,
@CRAFTEXP3 INT,
@CRAFTEXP4 INT,
@CRAFTFLAGS VARBINARY(16),
@RESERVED1 BIGINT

IF (SELECT COUNT(CHARACTERIDX) FROM GAMEDB.[cabal_character_table] WHERE LOGIN=1 AND CHARACTERIDX/8=@CHARACTERIDX/8)>0
BEGIN
COMMIT TRAN
END


SELECT
@CH_WORLDIDX = WORLDIDX,
@CH_POSITION = POSITION,
@CH_LEV = LEV,
@CH_EXP = EXP,
@CH_STR = STR,
@CH_DEX = DEX,
@CH_INT = INT,
@CH_PNT = PNT,
@CH_RANK = RANK,
@CH_ALZ = ALZ,
--@CH_ALZ_XORED = RESERVED1,
@CH_STYLE = STYLE,
@CH_FLAGS = FLAGS,
@CH_HP = HP,
@CH_MP = MP,
@CH_SP = SP,
@CH_SWDPNT = SWDPNT,
@CH_MAGPNT = MAGPNT,
@CH_RANKEXP = RANKEXP,
@CH_PENALTYEXP = PENALTYEXP,
@CH_WARPBFIELD = WARPBFIELD,
@CH_MAPSBFIELD = MAPSBFIELD,
@CH_NAME = NAME,
@CH_RP = isnull(RP, 0),
@CH_REPUTATION = REPUTATION,
@CH_PKPENALTY = PKPENALTY,
@CH_NATION = NATION,
@logout_time = LOGOUTTIME,
@RESERVED1 = RESERVED1
FROM CABAL_CHARACTER_TABLE with(nolock)
WHERE CHARACTERIDX = @CHARACTERIDX
IF @@ROWCOUNT = 0
BEGIN
SELECT -1
COMMIT TRAN
RETURN
END
IF @logout_time is NULL
BEGIN
SET @logout_time = getdate()
END
SET @login_time = getdate()
SET @RestTime = DATEDIFF( minute, @logout_time, @login_time)
SET @CH_RP = @CH_RP + @RestTime
IF(@CH_RP > 10080)
BEGIN
SET @CH_RP = 10080
END
UPDATE CABAL_CHARACTER_TABLE SET
RP =@CH_RP,
LOGINTIME=@login_time,
LOGOUTTIME=@login_time,
CHANNELIDX=@CHANNELIDX
WHERE CHARACTERIDX = @CHARACTERIDX

-- EQUIPMENT
DECLARE
@EQ_DATA VARBINARY(512)

SELECT
@EQ_DATA = DATA
FROM CABAL_EQUIPMENT_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_EQUIPMENT_TABLE
VALUES(@CHARACTERIDX, 0X)
SET @EQ_DATA = 0X
END
-- INVENTORY
DECLARE
@IV_DATA VARBINARY(4096)

SELECT
@IV_DATA = DATA
FROM CABAL_INVENTORY_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_INVENTORY_TABLE
VALUES(@CHARACTERIDX, 0X)
SET @IV_DATA = 0X
END
-- SKILLLIST
DECLARE
@SK_DATA VARBINARY(512)

SELECT
@SK_DATA = DATA
FROM CABAL_SKILLLIST_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_SKILLLIST_TABLE
VALUES(@CHARACTERIDX, 0X)
SET @SK_DATA = 0X
END
-- QUICKSLOT
DECLARE
@QK_DATA VARBINARY(256)

SELECT
@QK_DATA = DATA
FROM CABAL_QUICKSLOT_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_QUICKSLOT_TABLE
VALUES(@CHARACTERIDX, 0X)
SET @QK_DATA = 0X
END

-- Blessing Bead
DECLARE
@BBEAD_DATA VARBINARY(512)

SELECT
@BBEAD_DATA = DATA
FROM CABAL_BBEAD_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_BBEAD_TABLE
VALUES(@CHARACTERIDX, 0X)
SET @BBEAD_DATA = 0X
END

-- QUESTDATA
DECLARE
@QE_DATA VARBINARY(128),
@QE_FLAGS VARBINARY(128)

SELECT
@QE_DATA = DATA,
@QE_FLAGS = FLAGS
FROM CABAL_QUESTDATA_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_QUESTDATA_TABLE
VALUES(@CHARACTERIDX, 0X, CONVERT(BINARY(64), 0))
SET @QE_DATA = 0X
SET @QE_FLAGS = CONVERT(BINARY(64),0)
END
-- QDDATA
DECLARE
@QD_FLAGS VARBINARY(128)

SELECT
@QD_FLAGS = FLAGS
FROM CABAL_QDDATA_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_QDDATA_TABLE
VALUES(@CHARACTERIDX,CONVERT(BINARY(32),0))


SET @QD_FLAGS = CONVERT(BINARY(32),0)
END

SELECT @CRAFTLEVEL0 = LEVEL0,
@CRAFTLEVEL1 = LEVEL1,
@CRAFTLEVEL2 = LEVEL2,
@CRAFTLEVEL3 = LEVEL3,
@CRAFTLEVEL4 = LEVEL4,
@CRAFTEXP0 = EXP0,
@CRAFTEXP1 = EXP1,
@CRAFTEXP2 = EXP2,
@CRAFTEXP3 = EXP3,
@CRAFTEXP4 = EXP4,
@CRAFTFLAGS = FLAGS
FROM CABAL_CRAFT_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO CABAL_CRAFT_TABLE ( CHARACTERIDX ) VALUES ( @CHARACTERIDX )

SELECT @CRAFTLEVEL0 = LEVEL0,
@CRAFTLEVEL1 = LEVEL1,
@CRAFTLEVEL2 = LEVEL2,
@CRAFTLEVEL3 = LEVEL3,
@CRAFTLEVEL4 = LEVEL4,
@CRAFTEXP0 = EXP0,
@CRAFTEXP1 = EXP1,
@CRAFTEXP2 = EXP2,
@CRAFTEXP3 = EXP3,
@CRAFTEXP4 = EXP4,
@CRAFTFLAGS = FLAGS
FROM CABAL_CRAFT_TABLE
WHERE CHARACTERIDX = @CHARACTERIDX
END

SELECT
@CH_WORLDIDX,
@CH_POSITION,
@CH_LEV ,
@CH_EXP,
@CH_STR,
@CH_DEX,
@CH_INT,
@CH_PNT,
@CH_RANK,
@CH_ALZ,
--@CH_ALZ_XORED,
@CH_STYLE,
@CH_FLAGS,
@CH_HP,
@CH_MP,
@CH_SP,
@CH_SWDPNT,
@CH_MAGPNT,
@CH_RANKEXP,
@CH_PENALTYEXP,
@CH_WARPBFIELD,
@CH_MAPSBFIELD,
@CH_NAME,
@CH_RP,
@CH_REPUTATION,
@CH_PKPENALTY,
@CH_NATION,
@EQ_DATA,
@IV_DATA,
@SK_DATA,
@QK_DATA,
@BBEAD_DATA,
@QE_DATA,
@QE_FLAGS,
@QD_FLAGS,
@CRAFTLEVEL0,
@CRAFTLEVEL1,
@CRAFTLEVEL2,
@CRAFTLEVEL3,
@CRAFTLEVEL4,
@CRAFTEXP0,
@CRAFTEXP1,
@CRAFTEXP2,
@CRAFTEXP3,
@CRAFTEXP4,
@CRAFTFLAGS,
@RESERVED1
COMMIT TRAN
SET NOCOUNT OFF
END

GO