Query Glossary

General assistance and guidelines for editing the server side of Soma.

Query Glossary

Postby Tiffany » Tue Feb 26, 2008 9:32 am

Originally by RDGhost on Pirate Forums 2007

What is a query
A query is a command Query Analyzer (refered to as QA from now on) runs in SQL.

How do I get to QA?
The easiest way is to go to SQL > drop down your DB > Go to the 'Tools' tab on the top > Select SQL Query Analyzer.
You will now see a program with a blank Notepad looking typing space. This is where you insert your Query's.

What are query's good for
Query's are good for updating thing quickly and updating alot of stuff in the same way very quickly (like increasing drops rates). It's also good for multiplying stuff, like rates etc.

That's all nice and all, but how do I write these commands? And what are the risks?

I will try and explain this as clear as I can, to do this, I will break down an existing known query and explain what each part does. For this I will be using the drop list editing query. I shortened the query a bit of obvious reasons.

Selection type queries
(Like, * Operators)
Code: Select all
select * from monster where strname like '%night%'
You will also note the * as the 2nd word in the query, this means select all columns which correspond to the where clause.

The % inside the single quotes means return/display the row when the sub-string 'night' occurs anywhere in the string of the 'strname' column.

This is useful for finding multiple entries of the sub-string and when you have forgotten the exact spelling of the entries you are looking for.

Obviously caution, as Ghost mentioned, needs to be undertaken as this will find other rows and therefore is not usually used with an update query.

SQL Query Builder
You can start to get a bit more advanced but this is better done by using the Query builder:
  1. Right-click one of the tables you want to use data from in SQL Server Enterprise.
  2. Select Open-table, then query.
  3. Right-click the mapping area (blank bit up top) and you can add other tables.
  4. Select the fields that key together from the seperate tables.
  5. Add where clauses
The following links(joins) the two tables monster and monsterset to return the spawns in monsterset which correspond to the sub-string.
Code: Select all
FROM   monsterset INNER JOIN
       MONSTER ON monsterset.sTableNum = MONSTER.sSid
WHERE  (MONSTER.strName LIKE '%stig%')

A simplified version is:
Code: Select all
SELECT * FROM monsterset WHERE sTableNum IN (SELECT sSid FROM MONSTER WHERE strName like '%stig%')

an expansion to this is to add a more normal name to the zone which currently doesnt exist in shinzon and other DB's, see Maps.csv attachment in rar file.

Code: Select all
SELECT  MONSTER.strName, Maps.sMapName, *
FROM   monsterset INNER JOIN
   MONSTER ON monsterset.sTableNum = MONSTER.sSid INNER JOIN
   Maps ON monsterset.sZone = Maps.sSid
WHERE  (MONSTER.strName LIKE '%stig%')

Update Queries
Code: Select all
update monsterset set
sitem01 = 50,
sitem01rand = 100,
smoneymin = 10,
smoneymax = 100
where stablenum = ( select ssid from monster where strname = 'bric' )

The first word you will notice is update. This means that it will update/edit a table, rather than adding or deleting stuff from it.

Monsterset is the table that needs updating.

Set is a very important word. It's the main command that says "change the stuff I'm going to list after this". If you forget this, your query will not work.

The things after set are the basic commands. You won't use those exact commands alot, they basicly just say what to do rather than how. In this case we're doing the following:

sitem01 = 50,
sitem01rand = 100,
smoneymin = 10,
smoneymax = 100

The first word allways says what to change. The '=' sign tells it to change it into what. The comma at the end of the line tells QA that there is more than one command that it has to run in this query. If you forget the comma it will give an error. The last command does not have a comma, this means it's the last command.

You will notice that the last part of the query is a bit more advanced than you might be used to, I did this on purpose.

The first word where indicates that it only updates the rows where, in this case the stablenum the stablenum is of a Bric.

I've said it when explaining the where part, the stablenum is the thing that it will look at when excecuting the commands. It will look for the row where the stablenum is the same as the number after the '=' sign.

Code: Select all
( select ssid from monster where strname = 'bric' )

This is a command on it's own, it won't actually work, but I will explain this later.

QA works a bit like BODMAS, brackets will be done first. Select ssid from monster means exactly what it says, select the ssid from the monster table.
Where strname = 'bric' should be pretty clear aswell. The brick is in quotation marks for a reason. If there would be a space in the name of the monster, it would see them as seperate word. With the quotation marks you told QA that it goes with eachother.

This was basicly the query I was breaking down, I hope it was clear on what every part of it does. As a follow-up I will add extra commands that you can use. I won't be explaining them as really, well, they don't need an explanation

List of words you can use;
Delete and/or Remove (does the same thing)
( ) (One doesn't go without the other)
> (greater than)
< (smaller than)
<> (does not equal)

There's also the 'insert'-query. This type of query allows you to insert stuff directly into the database.

The commands are like this:
Code: Select all
insert into [table name]
values (value 1, value2, value3… etc)

The first line speaks for itself.
The second line is a bit harder. You need to know each value, you can't skip any value even if they are 0, -1 or 1. This isn't a query that's used alot because the tables need so much info.

This is all I can think of right now. Will add more if I can think of more.

Now, for something very important. The risks:

The moment you execute a query you CANNOT undo it. The only way to undo it is by writing another query that will reverse the effect, but basicly it'll requir manual work most of the times. Allways double check if you didn't put in the wrong numbers or something like that.

As a final but very important thing, the errors you can get. Everyone will get an error from time to time, it's nothing to be ashamed of.

I told you before in this guide, that the query I broke down in parts was flawed. It will give the following error when you try to run it:
Code: Select all
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Why is this? Simple. Subquery's (when using brackets in a query) do not allow more than one outcome. What does this mean? It basicly means that there is more than one row in the Monster table that has Bric as Strname.

How do you fix this? I'm afraid you can't in this case. You will have to find the ssid manually for the Bric you want to use.

Another known error is this:
Code: Select all
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'smoneymax'.

You will get this error when using this query:
Code: Select all
update monsterset set
sitem01 = 50,
sitem01rand = 100,
smoneymin = 10
smoneymax = 100
where stablenum = 93

Basicly it means that there's something wrong with the code near smoneymax on line 5. You can find what's wrong (in this case a comma missing) by going there and double checking it.

You will notice that everytime you get an error there are two lines at least. The first row shows where the error is taking place, this is very helpfull when locating it. The second line shows what the error is. Read this carefully.

There are alot of errors you can get. I'm not going to explain them all as I'd be busy for a few hours probably. Basicly you'll need to read and double check.

We've come to an end to this guide. I hope this helps, even though most of it is known and obvious to most people, it might help the people who don't have the experience. Any questions or thing you want to add, feel free to post and I will answer them or update the main post.

Code: Select all
[sSid] nvarchar (255) NULL,
[sMapName] nvarchar (255) NULL
insert into Maps values (1, 'Thousand Year Town')
insert into Maps values (2, 'TYT - Dungeon Lvl1')
insert into Maps values (3, 'TYT - Dungeon Lvl2')
insert into Maps values (4, 'Mercenary')
insert into Maps values (5, 'Abias')
insert into Maps values (6, 'TYT - Dungeon Lvl3')
insert into Maps values (7, 'TYT - Dungeon Lvl4')
insert into Maps values (8, 'TYT - Human Guild Village Room')
insert into Maps values (9, 'Pandemonium')
insert into Maps values (10, 'Chandalirs Room')
insert into Maps values (11, 'Infernal Caves')
insert into Maps values (12, 'Abias - Valley of Death (VoD)')
insert into Maps values (13, 'Devil Guild Village Room')
insert into Maps values (14, 'Conti - Castle')
insert into Maps values (15, 'Conti - Throne Room')
insert into Maps values (16, 'Conti - Crafting Room')
insert into Maps values (17, 'Continental')
insert into Maps values (18, 'Hwan')
insert into Maps values (19, 'Hwan Castle - First Floor')
insert into Maps values (20, 'Hwan Castle - Second floor')
insert into Maps values (21, 'Devil Dungeon')
insert into Maps values (22, 'Merc - The Farm')
insert into Maps values (26, 'Unknown Map')
insert into Maps values (33, 'Bugged Map')
insert into Maps values (35, 'Devil Abyss')
insert into Maps values (36, 'Human Abyss')
insert into Maps values (80, 'Useless Map')
SD Pro 2 Star
SD Pro 2 Star
Posts: 428
Joined: Sat Jan 12, 2008 1:17 pm

Re: Query Glossary

Postby matrik22 » Thu Apr 10, 2008 8:12 pm

Hey there I was just wondering if you could have added some of the more important queries like how to add an account and how to make GM char's update stats etc thanks a lot much appreciated.
Posts: 9
Joined: Thu Apr 10, 2008 12:53 am

Re: Query Glossary

Postby Deadice » Sat Apr 12, 2008 8:08 pm

to make an account you have to run
exec createaccount "account","password" in query analyzer place the details you want in the account and pasword

im unsure about doing GM Stats ect in a query, but i do know that stats can be edited in the 'gameruser' table

hope this helps
SD One Star
SD One Star
Posts: 17
Joined: Fri Feb 29, 2008 6:15 pm

Re: Query Glossary

Postby unvmeh » Wed Jan 07, 2009 2:27 pm

Sry but i had to bump this...

Alot of ppl making new servers and im sure this will help alot !
......./..../ /
..\.......... _.·´/
User avatar
SD Pro 5 Star
SD Pro 5 Star
Posts: 1438
Joined: Tue Apr 29, 2008 7:33 pm

Re: Query Glossary

Postby Jasica052 » Fri Feb 19, 2010 12:00 pm

Thanks for this nice post. this is so useful for me.
Posts: 1
Joined: Fri Feb 19, 2010 11:54 am

Re: Query Glossary

Postby lee » Tue Mar 23, 2010 11:57 pm

a = b and x = a

makes more sense than ever cheers
SD Pro 2 Star
SD Pro 2 Star
Posts: 461
Joined: Sun Apr 27, 2008 12:56 am
Location: Manchester

Return to Server Development

Who is online

Users browsing this forum: No registered users and 1 guest