Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[Database] Unbind Inventory Item (SQL Script Only)

Initiate Mage
Joined
Oct 21, 2022
Messages
4
Reaction score
15
Unbinding Inventory Items (SQL Script Only)

Unbinding Inventory Items:

The purpose of this script is to unbind the item from the inventory using an SQL command. I did this while studying SQLand, although I haven't tested it extensively, feedback is appreciated.


Step 1.
Create a table to store the IDs and names of game items. It will be used to list items in the player's inventory, as well as to indicate if the item can be unbound or not.

Code:
USE [Server01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE IF EXISTS [dbo].[Cabal_ItemList]
GO
CREATE TABLE [dbo].[Cabal_ItemList](
  [ID] [int] NULL,
  [Name] [varchar](100) NULL,
  [CanUnbound] [bit] NULL
) ON [PRIMARY]
GO

Step 2.
After building the table, add all of your server items to it. To add them one by one, you can use this command:

Code:
INSERT INTO [Server01].[dbo].[Cabal_ItemList] VALUES (itemID, N'itemName', 0 or 1)

Here's a tip to quickly insert all lines at once!:
1. Copy all items and paste into a blank document (all items are in the cabal_msg file, below the <item_msg> tag), use notepad++, vscode, or any other editor that is regex compatible.
2. Press CTRL + H and check "Regular Expression".
3. In the "Find what" field, write this: (.*id="item|cont="|/>)
4. Let the "Replace with" field with line blank (empty) and click Replace All.

Let's do this a few more times...

5. Replace "[\s\t]+? with \t
6. Replace ^\d+[\s\t]+$ with line blank (empty)
7. Replace ^[\n\r]+ with line blank (empty)
8. Replace [\s\t]+$ with \t0

You must have something like this:

s5J9CWF - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums


Copy everything with CTRL + A and CTRL + C, go to
[Server01].[dbo].[Cabal_ItemList] table and paste with CTRL + V.

mrjLtK - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums

Step 3.
Set the [CanUnbound] column to 1 for items that can be unbound. If you don't mind, you can just set everything to 1.
Code:
UPDATE [Server01].[dbo].[Cabal_ItemList] SET [CanUnbound] = 1;

I only want to make these items unbindable (it wouldn't make sense to unbind something like an HP potion, afterall):
Code:
UPDATE [Server01].[dbo].[Cabal_ItemList]
SET [CanUnbound] = 1
WHERE ID BETWEEN 15 AND 284 
  OR ID BETWEEN 978 AND 1001
  OR ID BETWEEN 1314 AND 1337
  OR ID = 1442
  OR ID = 1443
  OR ID BETWEEN 1613 AND 1656
  OR ID BETWEEN 2230 AND 2235
  OR ID BETWEEN 2710 AND 2733
  OR ID BETWEEN 2738 AND 2809
  OR ID = 2941
  OR ID BETWEEN 3128 AND 3131
  OR ID BETWEEN 3467 AND 3555
  OR ID BETWEEN 3698 AND 3699
  OR ID = 3867
  OR ID BETWEEN 3904 AND 3933
  OR ID BETWEEN 3937 AND 3966;

Step 4.
Execute this query:

Code:
USE [Server01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[cabal_tool_GetInventoryInfo]
GO

CREATE PROCEDURE [dbo].[cabal_tool_GetInventoryInfo]
(
  @[I]characterIdx[/I] int,
  @[I]invType[/I] TINYINT=NULL /* 2 = equipped items, 3 = warehouse */
)

AS
BEGIN

DECLARE
  @[I]dataVariableLength[/I] INT,
  @[I]expireDate[/I] VARCHAR(16),
  @[I]itemData[/I] VARBINARY(18),
  @[I]invTypeMsg[/I] VARCHAR(15),
  @[I]itemName[/I] VARCHAR(200),
  @[I]data[/I] VARBINARY(8000),
  @[I]inventorySlotNum[/I] INT,
  @[I]charName[/I] VARCHAR(50),
  @[I]itemDurationIdx[/I] INT,
  @[I]itemEnhant[/I] INT,
  @[I]itemOption[/I] INT,
  @[I]itemKind[/I] INT,
  @[I]itemIdx[/I] INT,
  @[I]count[/I] INT=1;

DECLARE @[I]output[/I] TABLE 
(
  itemData VARBINARY(18),
  expireDate VARCHAR(16),
  itemName VARCHAR(200),
  inventorySlotNum INT,
  itemDurationIdx INT,
  itemKind INT,
  itemIdx INT,
  itemOpt INT
);

IF (@[I]invType[/I]=2)
BEGIN
  SET @[I]data[/I]=(SELECT [Data] FROM [Server01].[dbo].[cabal_equipment_table] WHERE [CharacterIdx]=@[I]characterIdx[/I]);
  SET @[I]invTypeMsg[/I]='Equipped items';
END
ELSE IF (@[I]invType[/I]=3)
BEGIN
  SET @[I]data[/I]=(SELECT [Data] FROM [Server01].[dbo].[cabal_warehouse_table] WHERE [UserNum]=@[I]characterIdx[/I]/8);
  SET @[I]invTypeMsg[/I]='Warehouse items';
END
ELSE
BEGIN
  SET @[I]data[/I]=(SELECT [Data] FROM [dbo].[cabal_inventory_table] WHERE [CharacterIdx]=@[I]characterIdx[/I]);
  SET @[I]invTypeMsg[/I]='Inventory items';
END;

WHILE (@[I]count[/I] < DATALENGTH(@[I]data[/I]))
BEGIN
  SET @[I]itemData[/I] = SUBSTRING(@[I]data[/I], @[I]count[/I], 18);
  SET @[I]itemKind[/I] = [dbo].[BinToInt](SUBSTRING(@[I]data[/I], @[I]count[/I], 5));
  SET @[I]itemEnhant[/I] = ([dbo].[BinToInt](@[I]itemData[/I]) & 0x0003E000) / 8192;
  SET @[I]itemOption[/I] = [dbo].[BinToInt](SUBSTRING(@[I]data[/I], @[I]count[/I] + 8, 4));
  SET @[I]itemIdx[/I] = [dbo].[BinToInt](SUBSTRING(@[I]data[/I], @[I]count[/I], 5)) & 0xFFF;
  SET @[I]itemDurationIdx[/I] = CONVERT(INT, SUBSTRING(@[I]data[/I], @[I]count[/I] + 17, 1)) / 8;
  SET @[I]inventorySlotNum[/I] = [dbo].[BinToWord](SUBSTRING(@[I]data[/I], @[I]count[/I] + 12, 2));

  SET @[I]expireDate[/I]=CAST((CONVERT(INT, SUBSTRING(@[I]data[/I], @[I]count[/I] + 14, 1)) % 128) + 2000 AS VARCHAR(4)) + '-' +
    RIGHT('0' + CAST(([dbo].[binToWord](SUBSTRING(@[I]data[/I], @[I]count[/I] + 14, 2)) / 128) % 16 AS VARCHAR(2)), 2) + '-' + 
    RIGHT('0' + CAST(CONVERT(INT, SUBSTRING(@[I]data[/I], @[I]count[/I] + 15, 1)) / 8 AS VARCHAR(2)), 2) + ' ' +
    RIGHT('0' + CAST(CONVERT(INT, SUBSTRING(@[I]data[/I], @[I]count[/I] + 16, 1)) % 32 AS VARCHAR(2)), 2) + ':' +
    RIGHT('0' + CAST((([dbo].[binToWord](SUBSTRING(@[I]data[/I], @[I]count[/I] + 16 , 2))) / 32) % 64 AS VARCHAR(2)), 2);

  IF (CONVERT(INT, SUBSTRING(@[I]expireDate[/I], 1, 4)) <= 2000) BEGIN SET @[I]expireDate[/I] = 'Permanent'; END;

  SET @[I]itemName[/I]='Unnamed';
  IF EXISTS(SELECT [Name] FROM [dbo].[Cabal_ItemList] WHERE [ID]=@[I]itemIdx[/I])
  BEGIN
    SELECT @[I]itemName[/I]=[Name] FROM [dbo].[Cabal_ItemList] WHERE [ID]=@[I]itemIdx[/I];
    IF(@[I]itemEnhant[/I] != 0) BEGIN SET @[I]itemName[/I]=CONCAT(@[I]itemName[/I], ' +', @[I]itemEnhant[/I]) END;
  END;

  INSERT INTO @[I]output[/I]
  (
    itemData, 
    itemName,
    itemIdx,
    itemKind, 
    itemOpt, 
    itemDurationIdx, 
    expireDate, 
    inventorySlotNum
  ) 
  VALUES
  (
    @[I]itemData[/I], 
    @[I]itemName[/I], 
    @[I]itemIdx[/I], 
    @[I]itemKind[/I], 
    @[I]itemOption[/I], 
    @[I]itemDurationIdx[/I], 
    @[I]expireDate[/I], 
    @[I]inventorySlotNum[/I]
  );

  SET @[I]count[/I] += 18;
END;

SELECT [Name] 'Nickname', [Alz] 'Alz', @[I]count[/I]/18 'itemCount', @[I]invTypeMsg[/I] 'Section'
FROM [dbo].[cabal_character_table]
WHERE [CharacterIdx]=@[I]characterIdx[/I];

SELECT
  [itemData] 'itemData',
  [itemName] 'itemName', 
  [itemIdx] 'itemIdx', 
  [itemKind] 'itemIdx (full)', 
  [itemOpt] 'itemOption', 
  [itemDurationIdx] 'durationIdx',
  [expireDate] 'expireDate',
  [inventorySlotNum] 'inventorySlotNum' 
FROM @[I]output[/I]
ORDER BY [inventorySlotNum];

END;

Step 5.
Execute this query:

Code:
USE [Server01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[cabal_unbound_InventoryItem]
GO

CREATE PROCEDURE [dbo].[cabal_unbound_InventoryItem]
(
  @[I]characterIdx[/I] INT,
  @[I]itemData[/I] VARBINARY(18)
)

AS
BEGIN
  DECLARE @[I]r_result[/I] SMALLINT;
  DECLARE @[I]r_OK[/I] SMALLINT = 1;
  DECLARE @[I]r_FAILED[/I] SMALLINT = 0;

  DECLARE @[I]login[/I] TINYINT;
  SELECT @[I]characterIdx[/I]=CharacterIdx, @[I]login[/I]=Login FROM [dbo].[cabal_character_table] WHERE CharacterIdx=@[I]characterIdx[/I];
  IF (@@[I]ROWCOUNT[/I] <= 0) BEGIN SELECT 'ERROR (-1): character not found' RETURN; END;
  IF (@[I]login[/I] != 0) BEGIN SELECT 'ERROR (-2): character online' RETURN; END;

  DECLARE @[I]userNum[/I] INT;
  SELECT @[I]userNum[/I]=UserNum, @[I]login[/I]=Login FROM [Account].[dbo].[cabal_auth_table] WHERE UserNum=@[I]characterIdx[/I]/8;
  IF (@@[I]ROWCOUNT[/I] <= 0) BEGIN SELECT 'ERROR (-3): usernum not found' RETURN; END;
  --IF (@[I]login[/I] != 0) BEGIN SELECT 'ERROR (-4): account online' RETURN; END;

  IF (LEN(@[I]itemData[/I]) != 18) BEGIN SELECT 'ERROR (-5): check itemData' RETURN; END;

  DECLARE @[I]itemDuration[/I] INT = CONVERT(INT, SUBSTRING(@[I]itemData[/I], 18, 1)) / 8;
  IF (@[I]itemDuration[/I] != 0 AND @[I]itemDuration[/I] != 31) BEGIN SELECT 'ERROR (-13): item is temporary' RETURN; END;

  DECLARE @[I]itemKind[/I] INT = [dbo].[BinToInt](SUBSTRING(@[I]itemData[/I], 1, 5));
  DECLARE @[I]itemOpt[/I] INT = [dbo].[BinToInt](SUBSTRING(@[I]itemData[/I], 9, 4));
  DECLARE @[I]itemId[/I] INT = @[I]itemKind[/I] & 0xFFF;
  IF (@[I]itemKind[/I] < 1 OR @[I]itemKind[/I] > 2097152) BEGIN SELECT 'ERROR (-6): invalid itemKind' RETURN; END;
  IF (@[I]itemKind[/I] < 4096) BEGIN SELECT 'ERROR (-14): item is already free' RETURN; END;
  IF (@[I]itemKind[/I] < 131072) BEGIN SELECT 'ERROR (-15): item is binded to account' RETURN; END;
  IF ((@[I]itemKind[/I] & 0x180000) = 1572864) BEGIN SELECT 'ERROR (-16): item is already free' RETURN; END;
  IF (@[I]itemId[/I] < 1 OR @[I]itemId[/I] > 4095) BEGIN SELECT 'ERROR (-7): invalid itemKind' RETURN; END;
  IF (@[I]itemOpt[/I] < 0 OR @[I]itemOpt[/I] > 2147483647) BEGIN SELECT 'ERROR (-8): invalid itemOption' RETURN; END;

  DECLARE @[I]ID[/I] INT;
  SELECT @[I]ID[/I]=ID FROM [dbo].[Cabal_ItemList] WHERE ID=@[I]itemId[/I] AND CanUnbound=1;
  IF (@@[I]ROWCOUNT[/I] <= 0) BEGIN SELECT 'ERROR (-9): not found in Cabal_ItemList table or CanUnbound is 0' RETURN; END;

  DECLARE @[I]inventoryData[/I] VARBINARY(8000);
  SELECT @[I]inventoryData[/I]=[Data] FROM [dbo].[cabal_Inventory_table] WHERE CharacterIdx=@[I]characterIdx[/I];
  IF (@@[I]ROWCOUNT[/I] <= 0) BEGIN SELECT 'ERROR (-10): inventory data not found' RETURN; END;
  IF (@[I]inventoryData[/I] = 0x) BEGIN SELECT 'ERROR (-11): inventory data is empty' RETURN; END;

  DECLARE @[I]count[/I] INT = 1;
  DECLARE @[I]itemFound[/I] INT = 0;
  DECLARE @[I]itemData2[/I] VARBINARY(18);
  WHILE (@[I]count[/I] < LEN(@[I]inventoryData[/I]))
  BEGIN
    SET @[I]itemData2[/I] = SUBSTRING(@[I]inventoryData[/I], @[I]count[/I], 18);
    IF (@[I]itemData[/I] = @[I]itemData2[/I]) BEGIN SET @[I]itemFound[/I]=1 BREAK END;
    SET @[I]count[/I] += 18;
  END
  IF (@[I]itemFound[/I] <= 0) BEGIN SELECT 'ERROR (-12): item not found' RETURN; END;

  DECLARE @[I]itemEnhantCode[/I] INT = [dbo].[BinToInt](SUBSTRING(@[I]itemData[/I], 1, 4)) & 0x0003E000;

  DECLARE @[I]newItemKind[/I] INT = @[I]itemId[/I] + @[I]itemEnhantCode[/I];
  
  DECLARE @[I]isExt[/I] INT = CASE
    WHEN [dbo].[BinToInt](SUBSTRING(@[I]itemData[/I], 1, 5)) & 0x1000 != 0 THEN 1
    ELSE 0
  END;
  
  SET @[I]newItemKind[/I] = CASE
    WHEN @[I]isExt[/I] = 0 THEN @[I]newItemKind[/I] + 1572864 /* 1572864 = bind when equip */
    WHEN @[I]isExt[/I] = 1 THEN @[I]newItemKind[/I] + 4096    /* 4096 = bind to account */
  END;

  SET @[I]inventoryData[/I] = CONVERT(VARBINARY(8000), REPLACE(@[I]inventoryData[/I], @[I]itemData[/I], 0x))

  UPDATE [Account].[dbo].[cabal_auth_table] SET AuthType=2 WHERE UserNum=@[I]userNum[/I];

  BEGIN TRAN updt;
    UPDATE [dbo].[cabal_Inventory_table] SET [Data] = @[I]inventoryData[/I] WHERE CharacterIdx = @[I]characterIdx[/I]
    IF (@@[I]ROWCOUNT[/I] <= 0)
    BEGIN
      ROLLBACK TRAN updt;
      SELECT @[I]r_result[/I] = @[I]r_FAILED[/I];
      GOTO finish;
    END
    
    INSERT INTO [CabalCash].[dbo].[MyCashItem] (UserNum, TranNo, ServerIdx, ItemKindIdx, ItemOpt, DurationIdx)
    VALUES (@[I]userNum[/I], 0, 1, @[I]newItemKind[/I], @[I]itemOpt[/I], 0);
    IF (@@[I]ROWCOUNT[/I] <= 0)
    BEGIN
      ROLLBACK TRAN updt;
      SELECT @[I]r_result[/I] = @[I]r_FAILED[/I];
      GOTO finish;
    END

    COMMIT TRAN updt;

    SELECT @[I]r_result[/I] = @[I]r_OK[/I];
    GOTO finish;

  finish:
    UPDATE [Account].[dbo].[cabal_auth_table] SET AuthType=1 WHERE UserNum=@[I]userNum[/I];
    SELECT @[I]r_result[/I] as result;
    RETURN;
END;

Step 6.
Put the item you'd like to unbind into your character's inventory, log off, and run the following command, specifying the character's ID as an argument:

Code:
EXEC [Server01].[dbo].[cabal_tool_GetInventoryInfo] [B][COLOR=#FF0000]characterIdHere[/COLOR][/B];

xKUWVUA - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums


Copy the itemData of the item that you would like to unbind.

Step 7.
Run the command below to unbind an item - pass the character ID and the code of the itemData (copied in step 6) as arguments:

Code:
EXEC [Server01].[dbo].[cabal_unbound_InventoryItem] [B][COLOR=#FF0000]characterIdHere[/COLOR][/B], [B][COLOR=#FF0000]itemDataHere[/COLOR][/B];

If all went according to plan, you should have received an 'OK' message, and your old item has been removed from your inventory. A new item should now be in your cash inventory.

HVjh345 - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums

Before:
hWnnmZh - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums


After:
z3E8LYt - [Database] Unbind Inventory Item (SQL Script Only) - RaGEZONE Forums


I have a few more insights to share with you, but I need time to adapt them before posting. It's a real pity that the cabal area is full of leeches and scammers, but I don't see any issue with sharing some of my study findings.

Don't pose queries such as "Which files do you utilize?" and don't start extraneous topics. If you have any difficulties with the information posted here, state your inquiry right here.

Credits: w3rnis, RoozeV.
 

Attachments

You must be registered for see attachments list
Last edited:
Newbie Spellweaver
Joined
Nov 16, 2018
Messages
84
Reaction score
8
EXEC [Server01].[dbo].[cabal_unbound_InventoryItem] 9, 0x7F6E090025000004F4000020000000000000

The result value shows -4 and the unbind does not work.
 
Initiate Mage
Joined
Oct 21, 2022
Messages
4
Reaction score
15
EXEC [Server01].[dbo].[cabal_unbound_InventoryItem] 9, 0x7F6E090025000004F4000020000000000000

The result value shows -4 and the unbind does not work.

You must be offline

Code:
IF (@login != 0) BEGIN SELECT -4 RETURN; END;
 
Newbie Spellweaver
Joined
Nov 16, 2018
Messages
84
Reaction score
8
You must be offline

Code:
IF (@login != 0) BEGIN SELECT -4 RETURN; END;


thanks!
You will need to log out of your account.
-4 result when character is logged out.
 
Initiate Mage
Joined
Nov 9, 2022
Messages
1
Reaction score
0
this command in Step 6 it show only 29 rows that's my item is more than 29 rows. how to show all ?
help me please It's hard for me.
 
Last edited:
Initiate Mage
Joined
Oct 21, 2022
Messages
4
Reaction score
15
this command in Step 6 it show only 29 rows that's my item is more than 29 rows. how to show all ?
help me please It's hard for me.

Thanks for the feedback. The problem is that the variable that holds the inventory data must be declared as VARBINARY(8000), not VARBINARY(512). I updated the thread to fix this.
 
Junior Spellweaver
Joined
Dec 19, 2013
Messages
183
Reaction score
37
yes you can . but u need alot work need to do

It isn't "a lot" of work at all.
Filter the results a little just print the results into the character/account page preferably using cache.
Adding this to an admin panel including the unbind function only requires minor additions as well.

You have everything you need here, the php knowledge needed is so minimalistic, you may as well just use OpenAI if you are lazy to do it, it'll literally spill the code for you and even explain it.
 
Newbie Spellweaver
Joined
Nov 16, 2018
Messages
84
Reaction score
8
It isn't "a lot" of work at all.
Filter the results a little just print the results into the character/account page preferably using cache.
Adding this to an admin panel including the unbind function only requires minor additions as well.

You have everything you need here, the php knowledge needed is so minimalistic, you may as well just use OpenAI if you are lazy to do it, it'll literally spill the code for you and even explain it.


Can you give me some tips?
 
Newbie Spellweaver
Joined
Apr 6, 2020
Messages
69
Reaction score
13
nice release, question have anyone done it with account binded items?
 
Elite Diviner
Joined
Jun 18, 2023
Messages
420
Reaction score
233
How can I use it for an item to use for unbind the char bind, like other servers uses unbinding stone.
 
Newbie Spellweaver
Joined
Jul 19, 2012
Messages
19
Reaction score
0
It's only staying in SQL up to 215, can anyone explain?

Can anyone help me? The other queries don't run, only until
UPDATE [Server01].[dbo].[Cabal_ItemList]
SET [CanUnbound] = 1
WHERE ID BETWEEN 15 AND 284
OR ID BETWEEN 978 AND 1001
OR ID BETWEEN 1314 AND 1337
OR ID = 1442
OR ID = 1443
OR ID BETWEEN 1613 AND 1656
OR ID BETWEEN 2230 AND 2235
OR ID BETWEEN 2710 AND 2733
OR ID BETWEEN 2738 AND 2809
OR ID = 2941
OR ID BETWEEN 3128 AND 3131
OR ID BETWEEN 3467 AND 3555
OR ID BETWEEN 3698 AND 3699
OR ID = 3867
OR ID BETWEEN 3904 AND 3933
OR ID BETWEEN 3937 AND 3966;
and then when I run the others I get an error
 

Attachments

You must be registered for see attachments list
Last edited:
Back
Top