SQL Queries to find a certain item

[mrpsycho]

well i had the need to find a certain item on my players inventories/warehouses. this brought me the need to find/develop a sql query to find me everyone with that item.

i searched ragezone forums and didnt actualy find what i was looking for, i found only the first step, after that with the few sql knowledge i have, i edited it a bit and made it work with some help from Chumpy

the code for the item you want to look for is in this example a Map Part - 4202 , the code is in reversed hex code.

this is the original query that should work

select C.characteridx, C.Name
from cabal_Inventory_table I with(nolock) inner
join cabal_character_table C with(nolock) on I.characteridx = C.characteridx
where master.dbo.fn_varbintohexstr (I.data) like '%4202%'

in case this does not work, like it happened to me, you can try and do what i also did, which fixed it for me.

go to your Enterprise Manager and go to the GAMEDB - User Defined Functions
create a new one with this code

--  System UDF fn_test
-- 	This function takes as input a varbinary
--	and returns the Hexadecimal representation string
--
CREATE function dbo.fn_test (
	@pbinin varbinary(8000) )
returns nvarchar(4000)
as
begin
	declare @pstrout nvarchar(4000)

	select @pstrout = master.dbo.fn_varbintohexsubstring(1,@pbinin,1,0)
	return @pstrout
end

after you have done this this is the query you have to use to look for the item on the players inventory

select C.characteridx, C.Name
from cabal_Inventory_table I with(nolock) inner
join cabal_character_table C with(nolock) on I.characteridx = C.characteridx
where GAMEDB.dbo.fn_test (I.data) like '%4202%'

you can also use this following query to look for the item in the players warehouse

select A.usernum, A.ID
from cabal_warehouse_table W with(nolock) inner
join account.dbo.cabal_auth_table A with(nolock) on W.usernum = A.usernum
where GAMEDB.dbo.fn_test (W.data) like '%4202%'

i have only tested the warehouse query with the edited function on the GAMEDB. so feel free to try it without adding the function to the GAMEDB if you want.

i hope it prooves as usefull for you as it did for me.
if you liked this post please press the thanks button :)

[MentaL]

[Yamachi]

This would definitely return some false results due to the fact that you're just looking for a number within a huge string. A better way to do it would be to parse each item (I think it's about 16 bytes per item?) and check the first 2 digits of each item. It would take a bit more code, yes, but it's a LOT more accurate.

[mrpsycho]

well i wont make a query to actualy edit the results automaticly, so no damage will be done to the results! i will edit most of the stuff i need manualy trough a gm tool. my sql knowledge isnt high enough atm to actualy make that accurate queries, and well i just felt like ppl would enjoy me sharing this, and perhaps someone with higher knowledge may take this as a first step like i did, and make a better query in the future :)

[chumpywumpy]

This would definitely return some false results due to the fact that you're just looking for a number within a huge string. A better way to do it would be to parse each item (I think it's about 16 bytes per item?) and check the first 2 digits of each item. It would take a bit more code, yes, but it's a LOT more accurate.

This is true but the more of the binary value you search on the more likely you are to miss some items with different options (this mostly effects gears). The more of the binary you search on the better the accuracy, but the more likely to miss items with different craft effects/slots. Personally i would prefer to just search in the ItemIdx and then manually weed through the results to be sure i hadn't missed anything.

What i did was gave myself a map part using GM tools and then used the inventory viewer to get the binary.

4202 00 000000 54 04 00 00 1F00 000000F8

0242 = 578 (ItemIdx)
0454 = 1108 (ItemOpt)
1F = 31 (DurationIdx)

I used the 4202 value to pick up every map part, i was only looking for the ItemIdx, i then ran the query and double-checked the results with the inventory viewer.

The inventory viewer is in the gm tools v2 at http://mrmagoo.psiblade.net/gmtools2 (note: char saving does NOT work yet).

[Yamachi]

I never said anything about searching for more than the item ID. I said that you should split the whole binary data up into each item (16 bytes each, I think) and then merely look at the first 2 bytes of each of the 16 byte "chunks". That way, you're ONLY searching for the item ID where it should be, not in the middle of the item data.