[RELEASE] VSRO v1.188 improved Log queries

[Syloxx]

_AddLogChar

/**
author : syloxx
created date : 2015-03-11
description : Log players activities.
return value :
0 = There is no error.
1 = The transaction is in an uncommittable state. Rolling back transaction.
**/


CREATE PROCEDURE dbo._AddLogChar
      @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]ID int
    , @[I][B][URL="http://forum.ragezone.com/members/1333442518.html"]even[/URL][/B][/I]tID tinyint
    , @[I][B][URL="http://forum.ragezone.com/members/1333446700.html"]DaT[/URL][/B][/I]a1 int
    , @[I][B][URL="http://forum.ragezone.com/members/1333446700.html"]DaT[/URL][/B][/I]a2 int
    , @strPos varchar(64)
    , @[I][B][URL="http://forum.ragezone.com/members/275999.html"]des[/URL][/B][/I]c varchar(128)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;


DECLARE @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value int;


/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1
BEGIN
    SET @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value = 1;
    GOTO ErrorHandler;
END


BEGIN TRY
    /**_# Log players activities.*/
    INSERT dbo._LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos, strDesc)
    VALUES  @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]ID, GETDATE(), @[I][B][URL="http://forum.ragezone.com/members/1333442518.html"]even[/URL][/B][/I]tID, @[I][B][URL="http://forum.ragezone.com/members/1333446700.html"]DaT[/URL][/B][/I]a1, @[I][B][URL="http://forum.ragezone.com/members/1333446700.html"]DaT[/URL][/B][/I]a2, CASE WHEN @strPos = '' THEN NULL ELSE @strPos END, CASE WHEN @[I][B][URL="http://forum.ragezone.com/members/275999.html"]des[/URL][/B][/I]c = '' THEN NULL ELSE @[I][B][URL="http://forum.ragezone.com/members/275999.html"]des[/URL][/B][/I]c END);
END TRY
BEGIN CATCH
    GOTO ErrorHandler;
END CATCH;


RETURN 0;


ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

RETURN @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value

_AddLogItem

/**
author : syloxx
created date : 2015-03-11
description : Log players item interactions.
return value :
0 = There is no error.
1 = The transaction is in an uncommittable state. Rolling back transaction.
**/


CREATE PROCEDURE dbo._AddLogItem
      @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]ID int
    , @ItemRefID int
    , @ItemSerial bigint
    , @dwData int
    , @[I][B][URL="http://forum.ragezone.com/members/1333461823.html"]Target[/URL][/B][/I]Storage tinyint
    , @[I][B][URL="http://forum.ragezone.com/members/2000154287.html"]operation[/URL][/B][/I] tinyint
    , @[I][B][URL="http://forum.ragezone.com/members/558012.html"]slot[/URL][/B][/I]_From tinyint
    , @[I][B][URL="http://forum.ragezone.com/members/558012.html"]slot[/URL][/B][/I]_To tinyint
    , @[I][B][URL="http://forum.ragezone.com/members/1333442518.html"]even[/URL][/B][/I]tPos varchar(64)
    , @strDesc varchar(128)
    , @[I][B][URL="http://forum.ragezone.com/members/496618.html"]Gold[/URL][/B][/I] bigint
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;


DECLARE @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value int;


/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1
BEGIN
    SET @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value = 1;
    GOTO ErrorHandler;
END


BEGIN TRY
    /**_# Log players item interactions.*/
    INSERT dbo._LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, EventPos, strDesc, Serial64, Gold)
    VALUES (GETDATE(), @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]ID, @ItemRefID, @dwData, @[I][B][URL="http://forum.ragezone.com/members/1333461823.html"]Target[/URL][/B][/I]Storage, @[I][B][URL="http://forum.ragezone.com/members/2000154287.html"]operation[/URL][/B][/I], @[I][B][URL="http://forum.ragezone.com/members/558012.html"]slot[/URL][/B][/I]_From, @[I][B][URL="http://forum.ragezone.com/members/558012.html"]slot[/URL][/B][/I]_To, CASE WHEN @[I][B][URL="http://forum.ragezone.com/members/1333442518.html"]even[/URL][/B][/I]tPos = '' THEN NULL ELSE @[I][B][URL="http://forum.ragezone.com/members/1333442518.html"]even[/URL][/B][/I]tPos END, CASE WHEN @strDesc = '' THEN NULL ELSE @strDesc END, @ItemSerial, @[I][B][URL="http://forum.ragezone.com/members/496618.html"]Gold[/URL][/B][/I])


    /**_# Log purchased cash items.*/
    IF @[I][B][URL="http://forum.ragezone.com/members/2000154287.html"]operation[/URL][/B][/I] = 35
        INSERT dbo._LogCashItem (RefItemID, CharID, Cnt, EventTime, Serial64)
        VALUES (@ItemRefID, @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]ID, @dwData, GETDATE(), @ItemSerial)
END TRY
BEGIN CATCH
    GOTO ErrorHandler;
END CATCH;


RETURN 0;


ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

RETURN @[I][B][URL="http://forum.ragezone.com/members/850422.html"]return[/URL][/B][/I]Value

 

[Dragonzee]

is the way u paste it the same one in ur procedure ?

 

[Syloxx]

Sorry about the formating, i had JS disabled so ragezone ignore "enter"

leme fix that

 

[Dragonzee]

what about SRO_VT_SYLOXX.dbo._AddErrorLog

is it ur LOG db name or shard?

 

[Syloxx]

SRO_VT_SYLOXX is my custom db name
_AddErrorLog is a custom procedure (logs every error)

Images:

removed the execution now

 

[anbelcay2]

SRO_VT_SYLOXX is my custom db name
_AddErrorLog is a custom procedure (logs every error)

Images:

Please must share procedures and tables ..I would greatly appreciate it

 

[RenePunik]

What kinds of improvements?

 

[Syloxx]

What kinds of improvements?

-Included Errorhandling (in case you add poop in this procedure) -no useless len checks

 

[mohamedm00n]

copy and past in my addlogchar or just the new lines to add > same for addlogitem ??

 

[0x88]

this query what will help us?