Itemshop Problem

[Curious]

Hello, I used the sql query that janebug published for the item shop, I don't know why, but the menus got mixed up, I think I missed something, but I couldn't understand what it was. Is there a simple way to get back the items added to the item shop with this query? Or how can I fix it.

You must be registered to see links

You must be registered to see links

 

[Janebug]

try to rerun my query it should truncate everything .... and fix your issue!!

 

[Curious]

Thank you for your attention, but the problem was still there and I deleted the db and reinstalled the clean backup db. I'm trying to understand the logic of the SQL query, I will try to prepare it myself.



By the way, does anyone know which db file is related to server ranking? For web page.

 

[Curious]

I think I found the cause of the problem.

You must be registered to see links

When I removed the two minus signs in the last line of the query and activated the line, the problem disappeared.

You must be registered to see links

 

[zipper20032]

The "--" is the SQL comment. (Everything in green is commented).
Removing the "--" on that line, you'll INSERT those values into your database, in your case "Queen Puppet" values.

Usually is should work. The problem of SQL Server errors are most likely from the Query Analyzer module from the SQL Server.
If the query format is not similar to what the Query Analyzer wants or expects when the query is analyzed before execution, it can throw errors even if your query is perfectly fine.

I would blame the Query Analyzer for false-positive errors.

 

[Curious]

Don't get me wrong, I'm not blaming anyone. I just wanted to share the problem and experience I had, that's all.

 

[zipper20032]

Don't get me wrong, I'm not blaming anyone. I just wanted to share the problem and experience I had, that's all.

Query Analyzer is a SQL Server module - the module which analyze your query for errors. It's not a person, lol xDDDD

The problem you've experienced shouldn't exist at all actually because your query has nothing wrong, typos or anything like that. (What can we say.. Microsoft...)
Sometimes the query analyzer doesn't like the comments being everywhere.

 

[Curious]

I have enough experience to understand what a query analyzer is, my friend . I meant that I don't blame the person who made this query.You seem like a seasoned person, then this question is for you.

Can you explain exactly how the items in these lines are placed in the place I specified in the item shop? I couldn't figure out the logic. In the item definition line, I couldn't see anything about which part of the item shop it will be placed in.

You must be registered to see links

 

[Janebug]

When i made that shop i created a guide to help me later if i needed it ... here it is

not sure who gave me the image but all thanks to the community for this one ...

You must be registered to see links

You must be registered to see links

Janebug
And these tables took me months to do while i was working. so the original release does have duplicates but its all fixed when i truncated it. check out the original post, Here https://forum.ragezone.com/f885/shop-wheel-release-1194557/

 

[zipper20032]

I have enough experience to understand what a query analyzer is, my friend . I meant that I don't blame the person who made this query.You seem like a seasoned person, then this question is for you.

Can you explain exactly how the items in these lines are placed in the place I specified in the item shop? I couldn't figure out the logic. In the item definition line, I couldn't see anything about which part of the item shop it will be placed in.

You must be registered to see links

I don't see any references for entry positions in the queries. Probably they are actually displayed by the entry order/row number, not necessarily by a position stored in a field in the tables. Would make sense, because, if you see the order in the INSERT query for the items in the TP & XP tab, those are matching the one from the in-game shop.

Try and play with the order of the values, see if there's any change in in-game shop.

The in-game tab's item generation is most likely an SQL fetch which it retrieves the items in exactly the same order as the table's records order.

 

[Lifefire]

The colored Excel sheet Janebug has in her message above was made by me, a full size image is in This_Post

The explanation she provided in her post is a very good way to explain the layout of the items you are loading into your ItemShop.

I will try to simplify the Excel image though (if i even can)...
- [SellType], [SellType1], [SellType2] are set as shown in Columns N,O,P
- StringDB contains the ItemShop button order as values in Columns K,L

The game uses the [KeyStr],[Level],[Sort] values in StringDB to align to the 3 [[SellTypes]] (Column Q). if you use the Excel image as your guide then with a little practice, you will quickly get the hang of designing your ItemShop.

I would also recommend, writing Excel formulas to create your Insert statement and ItemShop layout so that you can make easy changes then just copy the SQL statements and run them to do future ItemShop changes

 

[Curious]

I understood that part, but the other thing I understood is; it adds the items in the list until it sees the empty line in the subtitle. I'm underlining "Blank line"... If you understand what I mean, and I would appreciate it if you could tell me if I understood it correctly or not..If I give an example
N'SYS_ACCOUNTSTORE_TYPE_01_01', N'TP & XP', 2, 1),
the following list under the heading
(1, 1, 1, -1, 280, 0, 0, 203574, 1), /* High Quality Experience Potion */
(1, 1, 1, -1, 140, 0, 0, 201618, 1), /* Master skill potion */
(1, 1, 1, -1, 160, 0, 0, 201460, 1), /* Party Experience Potion */

The blank line determines that the list of items to be added is completed and starts adding it to the other heading. In other words, it continues to add items in the list until it sees the empty line.
The other point is for lunch to be added to the ruby ​​shop
[Sell_Cost_Bonus], we are using the title of this column or what the technical name is, I don't know.
We use [Sell_Cost_Free], column header for items to be added to Phirius Shop.

According to the 4th column title we use, noon is added to the diamond, ruby ​​or phirius pages.

Here's something I just realized.

When adding to the ruby ​​shop section(N'SYS_ACCOUNTSTORE_TYPE_01_02', N'Transportation', 2, 2),skipped this subheading.

How does StringDB decide whether to skip a specified header or not?

 

[Curious]

I understood that part, but the other thing I understood is; it adds the items in the list until it sees the empty line in the subtitle. I'm underlining "Blank line"... If you understand what I mean, and I would appreciate it if you could tell me if I understood it correctly or not..If I give an example

N'SYS_ACCOUNTSTORE_TYPE_01_01', N'TP & XP', 2, 1),
the following list under the heading
(1, 1, 1, -1, 280, 0, 0, 203574, 1), /* High Quality Experience Potion */
(1, 1, 1, -1, 140, 0, 0, 201618, 1), /* Master skill potion */
(1, 1, 1, -1, 160, 0, 0, 201460, 1), /* Party Experience Potion */

The blank line determines that the list of items to be added is completed and starts adding it to the other heading. In other words, it continues to add items in the list until it sees the empty line.
The other point is for lunch to be added to the ruby ​​shop
[Sell_Cost_Bonus], we are using the title of this column or what the technical name is, I don't know.
We use
[Sell_Cost_Free], column header for items to be added to Phirius Shop.According to the 4th column title we use, noon is added to the diamond, ruby ​​or phirius pages.

Here's anotherthing I just realized.
(N'SYS_ACCOUNTSTORE_TYPE_01_02', N'Transportation', 2, 2),
When adding to the ruby ​​shop section (N'SYS_ACCOUNTSTORE_TYPE_01_02', N'Transportation', 2, 2),skipped this subheading.
How does StringDB decide whether to skip a specified header or not?

 

[Curious]

I understood that part, but the other thing I understood is; it adds the items in the list until it sees the empty line in the subtitle. I'm underlining "Blank line"... If you understand what I mean, and I would appreciate it if you could tell me if I understood it correctly or not..If I give an example<br>
<br>
N'SYS_ACCOUNTSTORE_TYPE_01_01', N'TP & XP', 2, 1),<br>
the following list under the heading<br>
(1, 1, 1, -1, 280, 0, 0, 203574, 1), /* High Quality Experience Potion */<br>
(1, 1, 1, -1, 140, 0, 0, 201618, 1), /* Master skill potion */<br>
(1, 1, 1, -1, 160, 0, 0, 201460, 1), /* Party Experience Potion */<br>
<br>
The blank line determines that the list of items to be added is completed and starts adding it to the other heading. In other words, it continues to add items in the list until it sees the empty line.<br>
The other point is for lunch to be added to the ruby ​​shop<br>
[Sell_Cost_Bonus], we are using the title of this column or what the technical name is, I don't know.<br>
We use <br>
[Sell_Cost_Free], column header for items to be added to Phirius Shop.According to the 4th column title we use, noon is added to the diamond, ruby ​​or phirius pages.<br>
<br>
Here's anotherthing I just realized.<br>
(N'SYS_ACCOUNTSTORE_TYPE_01_02', N'Transportation', 2, 2),<br>
When adding to the ruby ​​shop section (N'SYS_ACCOUNTSTORE_TYPE_01_02', N'Transportation', 2, 2),skipped this subheading.<br>
How does StringDB decide whether to skip a specified header or not?

 

[zipper20032]

Break lines don't do anything. It just add an easier readability in case your SQL statement is having tons of values to be inserted.

To answer your question: You have the insert

INSERT INTO dbo.NewShopInfo VALUES
(1, 1, 1, -1, 280, 0, 0, 203574, 1), /* High Quality Experience Potion */
(1, 1, 1, -1, 140, 0, 0, 201618, 1), /* Master skill potion */
(1, 1, 1, -1, 160, 0, 0, 201460, 1), /* Party Experience Potion */


You have the fields SellType, SellType1, SellType2

SellType = Main Category
SellType1 = Sub Category in Main Category
SellType2 = Sub Category in Sub Category in Main Category

Let's take your first value to be inserted into the table:

(1, 1, 1, -1, 280, 0, 0, 203574, 1)

1 = World (probably index)
1 = SellType = Main Category => Consumables
1 = SellType1 = Sub Category in Main Category => TP & XP
-1 = SellType2 = Sub Category in Sub Category in Main Category => Not existing


And yes, the order of the items in the dbo.NewShopInfo is the order of your items in your in-game shop.


E.g:

INSERT INTO dbo.NewShopInfo VALUES
(1, 1, 1, -1, 280, 0, 0, 203574, 1), /* High Quality Experience Potion */
(1, 1, 1, -1, 140, 0, 0, 201618, 1), /* Master skill potion */
(1, 1, 1, -1, 160, 0, 0, 201460, 1), /* Party Experience Potion */

In this case, first item is 203574 => High Quality Experience Potion
Second item is 201618 => Master Skill Potion
And finally, 201460, last item => Party Experience Potion.

The order of the entry is the order of your items in that category.

For example, to place it in the Consumables -> Sigils tab, the values would be like this:

(1, 1, 8, -1 .... etc)

1 = Sub Type => Consumables => Main Category ID = 1
8 = Sub Type 1 => Sigils => Sub Category ID in the Main Category = 8

 

[Lifefire]

I understood that part, but the other thing I understood is; it adds the items in the list until it sees the empty line in the subtitle. I'm underlining "Blank line"... If you understand what I mean, and I would appreciate it if you could tell me if I understood it correctly or not..If I give an example

As Zipper and Janebug have mentioned, the line breaks are irrelevant. for example the 3 following code blocks are perfectly valid and will complete without any issues... are you sure you are not running into a SQL time-out by running long queries remotely?

Also, make sure your last line in your Insert statement does not end with a comma, ending a line with a comma tells SQL to expect more lines in the statement. You can either end the last line with no punctuation (as seen in my code blocks) or with a semi-colon, both are valid in newer versions of MSSQL.

Example 1:
INSERT INTO [dbo].[NewShopInfo] ([Item_OrgObjID1],[Item_Note],[Sell_MaxCount],[Sell_Cost],[Sell_Get_Bonus],[Item_Count1],[Item_DisplayerObjID],[Item_OrgObjID2],[Item_OrgObjID3],[Item_OrgObjID4],[Item_OrgObjID5],[Item_OrgObjID6],[Item_OrgObjID7],[Item_OrgObjID8],[Item_Count2],[Item_Count3],[Item_Count4],[Item_Count5],[Item_Count6],[Item_Count7],[Item_Count8],[Gamble_Count],[Gamble_Rate1],[Gamble_Rate2],[Gamble_Rate3],[Gamble_Rate4],[Gamble_Rate5],[Gamble_Rate6],[Gamble_Rate7],[Gamble_Rate8],[SellType],[SellType1],[SellType2],[World1]) VALUES 
(201445,'Dias Hot - Megaphone',0,5,1,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
(202902,'Dias Hot - Marking Ink',0,4,0,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
(202903,'Dias Hot - Transport Rune',0,3,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
(202904,'Dias Hot - Transport Portal Rune',0,6,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1)

Example 2:
INSERT INTO [dbo].[NewShopInfo] ([Item_OrgObjID1],[Item_Note],[Sell_MaxCount],[Sell_Cost],[Sell_Get_Bonus],[Item_Count1],[Item_DisplayerObjID],[Item_OrgObjID2],[Item_OrgObjID3],[Item_OrgObjID4],[Item_OrgObjID5],[Item_OrgObjID6],[Item_OrgObjID7],[Item_OrgObjID8],[Item_Count2],[Item_Count3],[Item_Count4],[Item_Count5],[Item_Count6],[Item_Count7],[Item_Count8],[Gamble_Count],[Gamble_Rate1],[Gamble_Rate2],[Gamble_Rate3],[Gamble_Rate4],[Gamble_Rate5],[Gamble_Rate6],[Gamble_Rate7],[Gamble_Rate8],[SellType],[SellType1],[SellType2],[World1]) VALUES 

(201445,'Dias Hot - Megaphone',0,5,1,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),

(202902,'Dias Hot - Marking Ink',0,4,0,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),

(202903,'Dias Hot - Transport Rune',0,3,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),

(202904,'Dias Hot - Transport Portal Rune',0,6,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1)

Example 3:
INSERT INTO [dbo].[NewShopInfo] ([Item_OrgObjID1],[Item_Note],[Sell_MaxCount],[Sell_Cost],[Sell_Get_Bonus],[Item_Count1],[Item_DisplayerObjID],[Item_OrgObjID2],[Item_OrgObjID3],[Item_OrgObjID4],[Item_OrgObjID5],[Item_OrgObjID6],[Item_OrgObjID7],[Item_OrgObjID8],[Item_Count2],[Item_Count3],[Item_Count4],[Item_Count5],[Item_Count6],[Item_Count7],[Item_Count8],[Gamble_Count],[Gamble_Rate1],[Gamble_Rate2],[Gamble_Rate3],[Gamble_Rate4],[Gamble_Rate5],[Gamble_Rate6],[Gamble_Rate7],[Gamble_Rate8],[SellType],[SellType1],[SellType2],[World1]) VALUES
-- Note about this item
(201445,'Dias Hot - Megaphone',0,5,1,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
-- Note about this item
(202902,'Dias Hot - Marking Ink',0,4,0,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
-- Note about this item
(202903,'Dias Hot - Transport Rune',0,3,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1),
-- Note about this item
(202904,'Dias Hot - Transport Portal Rune',0,6,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,-1,1)

And yes, the order of the items in the dbo.NewShopInfo is the order of your items in your in-game shop.

Yes and No, it does affect the order but only on a "per-category" basis. The categories do not have to be in any specific order (including Diamond, Ruby, Phiruis Shops) but if you want items within each category to be listed in a specific order then it does matter which order those items are inserted in.

 

[Curious]

Thank you very much for your attention, now I understand very clearly. You are awesome.
By the way, is there a guide that tells what function the fields used in the sql table have or what they do?
Frankly, I don't want to keep people busy by constantly asking such questions..

 

[Lifefire]

Because of how many tables there are and how many columns each table can possibly have there will most likely never be a definitive guide about what the columns mean or how they are used. There are quite a few forum posts that explain certain tables in detail but not every table.

 

[zipper20032]

Thank you very much for your attention, now I understand very clearly. You are awesome.
By the way, is there a guide that tells what function the fields used in the sql table have or what they do?
Frankly, I don't want to keep people busy by constantly asking such questions..

"Function" meaning server side function/method for Item Shop?

If you are looking for the function method which generates and load into server's memory the Item Shop and getting the info out of the database, then you need to have the source code of the server for that.

 

[Curious]

"İşlev", Item Shop için sunucu tarafı işlevi/yöntemi anlamına mı geliyor?

No no, I just meant the column headers in sql.