[RELEASE] Inventory Viewer Query v1

[ismokedrow]

Today I release to all smart enough to use, a inventory viewing query that can be embedded into a program or simply used in SSMS. It displays the following info:

Owner_ID, Owner_Name, Account_Name, Item_Name, Item_Code, Item_Amount, Item_level, Item_enhance, Item_wear_info, Item_sockets0-3, item_time_remaining, item_last_updated_time

/*
Query Created by: iSmokeDrow
Purpose: Viewing User Inventory
Version: 1

(Warning this query requires your SQL Server to be altered: to do so simply open a new query:
[PASTE-ME INTO NEW QUERY]
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
[END-PASTE-ME] then execute)

Tables Used:
- dbo.Item //Telecaster
- dbo. Character // Telecaster
- dbo.ItemResource // Arcadia
- dbo.StringResource // Arcadia

To use, simply edit:
WHERE p.owner_id = 3 below!

WHERE extensions:p.owner_id, c.owner_name,c.account e.g. WHERE c.owner_name = '' 

Legend:
p. = dbo.Item
c. = dbo.Character
iR. = dbo.ItemResource
sR. = dbo.StringResource
*/
/*Select Info*/
SELECT P.owner_id, C.owner_name, C.account, sR.name, p.Code, p.amount, p.level, p.enhance, p.wear_info,
p.socket_0, p.socket_1, p.socket_2, p.socket_3, p.remain_time, p.update_time,
iR.icon_file_name 
FROM 
/*Select Info From dbo.Item*/
OPENROWSET('SQLNCLI11', 'Server=127.0.0.1;Database=Telecaster;Trusted_Connection=yes;',
                       'SELECT owner_id, code, cnt as amount, level, enhance, wear_info,
					    socket_0, socket_1, socket_2, socket_3,remain_time, update_time 
						FROM dbo.Item WHERE owner_id > 0') AS P
/* Select info from dbo.Character*/
INNER JOIN  OPENROWSET('SQLNCLI11', 'Server=127.0.0.1;Database=Telecaster;Trusted_Connection=yes;',
                       'SELECT sid, account, name as owner_name FROM dbo.Character') AS C
/*Base select off Character SID = Item Owner ID */
ON C.sid = P.owner_id
/*Select info from dbo.ItemResource*/
INNER JOIN  OPENROWSET('SQLNCLI11', 'Server=127.0.0.1;Database=Arcadia;Trusted_Connection=yes;',
                       'SELECT id, name_id, tooltip_id, icon_file_name FROM dbo.ItemResource') AS iR
/*Base select off ItemResource Id = Item ID */
ON iR.id = P.Code
/*Select info from dbo.StringResource */
INNER JOIN  OPENROWSET('SQLNCLI11', 'Server=127.0.0.1;Database=Arcadia;Trusted_Connection=yes;',
                       'SELECT code, value as name  FROM dbo.StringResource') AS sR
/*Base select off StringResource code = ItemResource name_id*/
ON sR.code = iR.name_id 
/*Base full SELECT clause WHERE credentials*/
WHERE p.owner_id = 3
/*Order by Owner ID Ascending*/
ORDER BY owner_id ASC

Screenshot below

[marekrndr]

.. and that's how it's done mofokers.

[Superbuu.]

Thanks.
This was useful

[glandu2]

you can also use db_name.dbo.table_name like Arcadia.dbo.ItemResource and Telecaster.dbo.Character instead of using OLEDB
so your query would be like this:

SELECT P.owner_id, C.owner_name, C.account, sR.name, P.Code, P.amount, P.level, P.enhance, P.wear_info,
       P.socket_0, P.socket_1, P.socket_2, P.socket_3, P.remain_time, P.update_time, iR.icon_file_name 
FROM       Telecaster.dbo.Item            P
/* Select info from dbo.Character, Base select off Character SID = Item Owner ID */
INNER JOIN Telecaster.dbo.Character       C ON C.sid   = P.owner_id  
/* Select info from dbo.ItemResource, Base select off ItemResource Id = Item ID */
INNER JOIN    Arcadia.dbo.ItemResource   iR ON iR.id   = P.Code
/* Select info from dbo.StringResource, Base select off StringResource code = ItemResource name_id */
INNER JOIN    Arcadia.dbo.StringResource sR ON sR.code = iR.name_id  
/* Base full SELECT clause WHERE credentials */
WHERE P.owner_id = 3
/* Order by Owner ID Ascending */
ORDER BY owner_id ASC

[ismokedrow]

+1 Glandu Nice job buddy, hope you find a use for the query

[glandu2]

well, the last time i ran my server was more than 3 months ago ..., i am more a forum ghost now with sometimes some small releases. Now i am just fiddling with the client sometimes, so server side releases are not for me ^^

[haxti]

You could also make further optimizations for all teh nubs aut ther!
Like a little place to declare all the things. e.g.

USE Telecaster
GO
DECLARE @id int;

/*Enter your **** rite here nao!*/
Select @id = account_id From Character Where "name" = 'ENTER CHAR NAME HERE'

/*You wont understand what the part below this line will do anyways, so why reading it?*/
Select sid,name,lv From Character Where @id = account_id ORDER BY name
Use Auth
Select account_id,login_name From Accounts Where "account_id" = @id

Ofc this isnt the best example, it's just the one I found first place. But you will get the idea

[c1ph3r]

Do we really need something like this? There is an inventory+warehouse viewer in the official gm tool isn't it?

But anyways something nice for all of the lazy people hanging around in this section.

[ismokedrow]

Originally Posted by c1ph3r

Do we really need something like this? There is an inventory+warehouse viewer in the official gm tool isn't it?

But anyways something nice for all of the lazy people hanging around in this section.

Actually me and Marek were ******** around with a control panel we're developing and an inventory viewer came into the conversation and I said I'll try to make a query that can do that, two hours later I had created a query that could do it. It sadly requires a dreadful amount of resources to execute (my poor sony vaio **** near died w/ an execution time of 1:00 for 77 results) I didn't have anything else to do with it, so I threw it to those who might have a use.

I have never used the GM-Tool because I just don't really care to set it up, i'd rather just make my own -- then I'm already familiar.

[glandu2]

maybe adding primary key constraint on the id column of each table in Arcadia could help for your performance issue, as by default there is no primary key in any table in Arcadia (and foreign key for columns like name_id)

[5405045]

have you heard about GM Tool ????

[ismokedrow]

Originally Posted by 5405045

have you heard about GM Tool ????

Have you heard? Some of us like to create our own ****. But you wouldn't know anything about that would ya

[TheOnlyOneRaskim]

Originally Posted by 5405045

have you heard about GM Tool ????

I have heard there are people outside with some brain, wich create thing´s on their own.

I have heard not every ****** is leeching his thing´s.


Did you heared that there are people wich have an brain and they are using it?

[5405045]

i have created many tools and many things
but i've never created any thing that i have already
why would i need a new gm tool while i'm having a working one ( BETTER ONE )
that's stupidity

and i don't know you're script is very complex you can have the same results with a much lighter script

if you wanna create anything create a better version of an existing one or a new useful thing
and you never publish it that's my life

[TheOnlyOneRaskim]

Originally Posted by 5405045

i have created many tools and many things
but i've never created any thing that i have already
why would i need a new gm tool while i'm having a working one ( BETTER ONE )
that's stupidity

and i don't know you're script is very complex you can have the same results with a much lighter script

if you wanna create anything create a better version of an existing one or a new useful thing
and you never publish it that's my life

Its the same with rdb´s.


You can open them with an hex editor and get all infos.


OR

You create an program to do it, much faster.


The best on your program is, you can modify it, like you want. I never used this gm tool, but im sure you cant modify it.

Got it now?