Get item plus from DB

Mesiaar · 11/12/2023, 11:12

Hello,

I'd like to get specific item plus from Database. I use this query:

Code:

Select INV.CharID, CH.CharName16, INV.ItemID, IT.OptLevel, IT.RefItemID, INV.Slot, REFC.CodeName128, MagParam1, REF.ReqGender, REFC.AssocFileICon128, REF.Dur_L, REF.Dur_U, REF.PD_L, REF.PD_U, REF.MD_L, REF.MD_U, REF.ER_L, REF.ER_U, REF.PDStr_L, REF.PDStr_U, REF.MDInt_L, REF.MDInt_U, REF.MaxMagicOptCount, REF.PAttackMin_L, REF.PAttackMin_U, REF.PAttackMax_L, REF.PAttackMin_U, REF.MAttackMin_L, REF.MAttackMin_U, REF.MAttackMax_L, REF.MAttackMin_U, REF.Range, REF.HR_L, REF.HR_U, REF.CHR_L, REF.CHR_U, REF.PAStrMin_L, REF.PAStrMin_U, REF.PAStrMax_L, REF.PAStrMax_U, REF.MAInt_Min_L, REF.MAInt_Min_U, REF.MAInt_Max_L, REF.MAInt_Max_U, REF.PAR_L, REF.PAR_U, REF.MAR_L, REF.MAR_U, REFC.ReqLevel1 From _Inventory As INV
Right Join _Items As IT On INV.ItemID = IT.ID64
Right Join _RefObjCommon As REFC On REFC.ID = IT.RefItemID
Right Join _RefObjItem As REF On REFC.Link = REF.ID
Right Join _Char As CH On CH.CharID = INV.CharID 
WHERE CharName16 = 'ERGERG'
AND ItemID = 184659
Order By OptLevel desc

It gives me useful information, but where can I find how many plus this weapon has ?

#HB · 11/12/2023, 18:37

It's already there, OptLevel.

Quote:

Originally Posted by Mesiaar

If you want to include advanced elixir in your query, then you should include _BindingOptionWithItem as well.

I modified your query, should look something like this: (there's no point of using right join btw here, left or inner should be more reasonable)

Code:

SELECT INV.CharID, CH.CharName16, INV.ItemID, CASE WHEN bnd.nOptValue IS NOT NULL THEN IT.OptLevel + bnd.nOptValue ELSE IT.OptLevel END OptLevel, IT.RefItemID, INV.Slot, REFC.CodeName128, MagParam1, REF.ReqGender, REFC.ASsocFileICON128, REF.Dur_L, REF.Dur_U, REF.PD_L, REF.PD_U, REF.MD_L, REF.MD_U, REF.ER_L, REF.ER_U, REF.PDStr_L, REF.PDStr_U, REF.MDInt_L, REF.MDInt_U, REF.MaxMagicOptCount, REF.PAttackMin_L, REF.PAttackMin_U, REF.PAttackMax_L, REF.PAttackMin_U, REF.MAttackMin_L, REF.MAttackMin_U, REF.MAttackMax_L, REF.MAttackMin_U, REF.Range, REF.HR_L, REF.HR_U, REF.CHR_L, REF.CHR_U, REF.PAStrMin_L, REF.PAStrMin_U, REF.PAStrMax_L, REF.PAStrMax_U, REF.MAInt_Min_L, REF.MAInt_Min_U, REF.MAInt_Max_L, REF.MAInt_Max_U, REF.PAR_L, REF.PAR_U, REF.MAR_L, REF.MAR_U, REFC.ReqLevel1
FROM _Inventory AS INV
RIGHT JOIN _Items AS IT ON INV.ItemID = IT.ID64
RIGHT JOIN _RefObjCommon AS REFC ON REFC.ID = IT.RefItemID
RIGHT JOIN _RefObjItem AS REF ON REFC.Link = REF.ID
RIGHT JOIN _Char AS CH ON CH.CharID = INV.CharID
LEFT JOIN _BindingOptionWithItem bnd ON bnd.nItemDBID = INV.ItemID AND bnd.bOptType = 2
WHERE INV.ItemID = 184659
ORDER BY IT.OptLevel DESC

JellyBitz · 11/13/2023, 19:48

Quote:

Originally Posted by Mesiaar

It gives me useful information, but where can I find how many plus this weapon has ?

First, download the attachment "TextData.rar" and execute it, this .sql contains a table with all those texts from the game.

By using that, you'll able to recognize items by name easier, take a look:

PHP Code:


			
USE SRO_VT_SHARD

SELECT CONCAT(_Inventory.Slot,') ',
TextData.TextValue,
CASE -- Check plus from Equipables
    WHEN _RefObjCommon.TypeID2 = 1 AND _BindingOptionWithItem.bOptType = 2 THEN CONCAT(' (+',_Items.OptLevel+_BindingOptionWithItem.nOptValue,' with Adv. Elixir +',_BindingOptionWithItem.nOptValue,')')
    WHEN _RefObjCommon.TypeID2 = 1 THEN CONCAT(' (+',_Items.OptLevel,')')
    ELSE ''
END,
CASE -- Check seal type
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 1 THEN ' [Seal of Star]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 2 THEN ' [Seal of Moon]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 0 THEN ' [Seal of Sun]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 3 THEN ' [Set]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 1 THEN ' [Set - Seal of Star]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 2 THEN ' [Set - Seal of Moon]'
    WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 0 THEN ' [Set - Seal of Sun]'
    ELSE ''
END) AS 'Item', * FROM _Char
INNER JOIN _Inventory ON _Inventory.CharID = _Char.CharID
INNER JOIN _Items ON _Items.ID64 = _Inventory.ItemID
INNER JOIN _RefObjCommon ON _RefObjCommon.ID = _Items.RefItemID
INNER JOIN _RefObjItem ON _RefObjItem.ID = _RefObjCommon.Link
LEFT JOIN _BindingOptionWithItem ON _BindingOptionWithItem.nItemDBID = _Inventory.ItemID
INNER JOIN TextData ON TextData.CodeName = _RefObjCommon.NameStrID128
WHERE _Char.CharName16 LIKE 'Jelly' -- Charname
AND 
    ( _Inventory.Slot < 13 -- Equiped
    OR _Inventory.Slot >= 13) -- Inventory
ORDER BY _Inventory.Slot

Mesiaar · 11/13/2023, 20:06

Quote:

Originally Posted by JellyBitz

First, download the attachment "TextData.rar" and execute it, this .sql contains a table with all those texts from the game.

By using that, you'll able to recognize items by name easier, take a look:

PHP Code:

USE SRO_VT_SHARD SELECT CONCAT(_Inventory.Slot,') ', TextData.TextValue, CASE -- Check plus from Equipables WHEN _RefObjCommon.TypeID2 = 1 THEN CONCAT(' (+',_Items.OptLevel,')') ELSE '' END, CASE -- Check seal type WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 1 THEN ' [Seal of Star]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 2 THEN ' [Seal of Moon]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 2 AND _RefObjItem.ItemClass % 3 = 0 THEN ' [Seal of Sun]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 3 THEN ' [Set]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 1 THEN ' [Set - Seal of Star]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 2 THEN ' [Set - Seal of Moon]' WHEN _RefObjCommon.TypeID2 = 1 AND _RefObjCommon.Rarity = 6 AND _RefObjItem.ItemClass % 3 = 0 THEN ' [Set - Seal of Sun]' ELSE '' END) AS 'Item', * FROM _Char INNER JOIN _Inventory ON _Inventory.CharID = _Char.CharID INNER JOIN _Items ON _Items.ID64 = _Inventory.ItemID INNER JOIN _RefObjCommon ON _RefObjCommon.ID = _Items.RefItemID INNER JOIN _RefObjItem ON _RefObjItem.ID = _RefObjCommon.Link INNER JOIN TextData ON TextData.CodeName = _RefObjCommon.NameStrID128 WHERE _Char.CharName16 LIKE 'Jelly' -- Charname AND ( _Inventory.Slot < 13 -- Equiped OR _Inventory.Slot >= 13) -- Inventory ORDER BY _Inventory.Slot

Awesome ! How do you get all stats from item ? For example some servers has item upload to website which contains absolutely all information about equiped item:

Because now Im thinking of getting Item ID, then search from here to get +, then calculate the stats. Seems like a lot of work which might lead nowhere and just uses resources by query database 10 times for a single item.

JellyBitz · 11/14/2023, 17:57

Quote:

Originally Posted by Mesiaar

How do you get all stats from item ?

All Stats from the item is into _RefObjItem but their current Stats % are encoded into _Items.Variance depending on the item type.
Check this out:

Magic Options are in _Items.MagParam1~12 encoded also.
First 4 bytes are the ID linked to _RefMagOpt, left bytes are the flat value it contains.
You may understand more by taking a look here:

From SQL is possible to display all that info but breaklines gonna be a problem. I think some programming will be required, not just by query.