An introduction to SQL Query Hooking.

[General]

Hey, I want to learn you all something that I've been using for a while now. I already released the list of DBServer queries (http://forum.ragezone.com/f315/list-...ueries-524196/) but nobody really cares about it since they don't know what they can do with it.

Well, the possibilities are unlimited. That's why I'm going to give you a (very) small example of what it can do.

The key to query hooking is "Stored Procedures", a method in SQL to do multiple actions/queries with 1 command.

In this example, we are going to make a talisman that makes an item bound on succes.

Stored Procedure
Go to enterprise manager -> databases -> kal_db -> Stored Procedures -> right click -> "New stored procedure"

Paste this into the window:

CREATE PROCEDURE SetPrefix

@prefix int,
@iid int

AS

IF  (@prefix = 150)
begin
--do a special thing, like make bound
UPDATE  [Item] SET Info = (Info|128) WHERE IID = @iid
end
ELSE
begin
-- do  the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] =  @iid
end 

Press OK

Screenshot:



DB Server
Now, we need the DBServer.exe to run this SP instead of the usual query hexed within it.

Open DBSvr.exe with a hex editor, find

UPDATE Item SET [Prefix] = %d WHERE [IID] = %d

Put your cursor before UPDATE and start typing

EXEC SetPrefix %d,%d

Make sure you fill the old text up with 00 bytes (put your cursor in the hex part or your editor

Screenshot:


Using ingame
How to use it ingame? Make a talisman with success prefix 150. Don't forget to dd them in prefix.txt and prefix.dat client sided, but I assume you all know how to add a new talisman.

When the talisman succeeds, your item will be bound on relog.

You can imagine the power of Stored procedures now. This is also how SwordOnline fixed the imperial stats bug, made bof with talismans and many more. We have over 30 stored procedures and hooked queries.

Kind regards

[MentaL]

[Momo5000]

Wow ty Bjorn, i love you :D

[Tommy1]

Thanks alot Bjorn , also we can do it without talisman isnt it?

You can imagine the power of Stored procedures now. This is also how SwordOnline fixed the imperial stats bug, made bof with talismans and many more. We have over 30 stored procedures and hooked queries.

Kind regards

Wow , soon i will try the bof thing

[exe08]

Sry for this stupid question, but how i can make 00-Bytes? Because of the different length.

Thanks for every answer. :P

greetz exe

/edit: nvm, i found it out by myself. ;)

/edit2: Thank you very much for tis information. But is there a possebility to make an item which can be used on armor, weapons and accessoires? Atm it is more like top, tow, toi, isn

[2Kxx]

really usefull release bjorn!
+1

[_Varis_]

Great release.

-V

[cristy_abaddon]

great tutorial and thanks for the list
did not know about it till now :|

[YourFear11]

thx Bjorn but i have one question ^^
maybe i'm dump but nvm :P
How to make 2 talizmans with bound function?
Here is "CREATE PROCEDURE SetPrefix", ok i can't make next procedure with this name, and here my question if i change name on SetPrefix2 or something like that how can i add next function to DBserver?
You changed UPDATE on "EXEC SetPrefix %d,%d" it's possible to add next one?

[exe08]

What you would like to do?
Make 1 bound tali for weapons, 1 bound tali for armor? If yes you don

[YourFear11]

ehh look in DBserver we have atm this:

EXEC SetPrefix %d,%d

DBserver reading only procedure SetPrefix and my question is how to add next one if i need 2 toi's with bound function for example King and Legendary

I think you need 2 procedure too if you trying to make bound and unbound :P
in first procedure changing prefix on 128, and in 2nd procedure changing prefix on 0.

[cristy_abaddon]

CREATE PROCEDURE SetPrefix

@prefix int,
@iid int

AS

IF  (@prefix = 151)
begin
--do a special thing, like make bound
UPDATE  [Item] SET Info = (Info|0) WHERE IID = @iid
end
ELSEIF (@prefix = 152)
begin
--do a special thing, like make bound
UPDATE  [Item] SET Info = (Info|0) WHERE IID = @iid
end
ELSE
begin
-- do  the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] =  @iid
end 

here it is how to do for multiple items

[exe08]

Ok i see, i need to change something for SetPrefix2 in db ...
But what? >.< ^^

I used SetPrefix for unbound, its sure that i need 2 procedures here. ;)

But i don

[General]

CREATE PROCEDURE SetPrefix

@prefix int,
@iid int

AS

IF  (@prefix = 150) --bound prefix
begin
--do a special thing, like make bound
UPDATE  [Item] SET Info = (Info|128) WHERE IID = @iid
end
ELSE IF  (@prefix = 151) --unbound prefix
begin
--do a special thing, like make unbound
UPDATE  [Item] SET Info = Info - (Info&128) WHERE IID = @iid
end
ELSE
begin
-- do  the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] =  @iid
end

you cannot make a new procedure for it, since 1 stored proceure = 1 db query in DBServer.exe

so you have to make an extra "'hook" for it within the SP

this way you can do "actions" before doing the real query.

It's an introduction guide, there are much more things you can do... like

after AS:

DECLARE @pid int
SET @pid = (SELECT PID FROM Item WHERE IID = @iid)
Print 'Item with IID '+@iid+' belongs to player with PID '+@pid+'

or something similar :)

[exe08]

Thank you very much, Bjorn! It works perfect, of course. ;)

I

[cristy_abaddon]

you want to make a new item (something like polishing stone) to make other items bound?

[exe08]

Right. I don

[cristy_abaddon]

hmm in theory it should be very possible
i just looked into inititem.txt
and it should be possible with little changes

(item(name 681)(Index 481)(Image "shop015")(desc 228)(class general charm)(code 3 7 21 0)(country 0 2)(use 1)(plural 1)(buy 150000)(specialty(protect))) // polish
(item(name 531)(Index 332)(Image "item017")(class general charm)(code 3 5 12 0)(country 0 1 2)(use 1)(plural 1)(buy 0)(sell 1500)(specialty(Changeprefix weapon 29 90 85 91))) // talisman 

just add new prefixes and everything is done ;)

a "hybrid" :P which is a normal tali ^_^

(item    (name 531)        (Index 332)    (Image "item017")            (class general charm)    (code 3 5 12 0)    (country 0 1 2)            (use 1)    (plural 1)                                (buy 0)    (sell 1500)                    (specialty                                                                                                                (Changeprefix weapon     100 200 )                        )        ) 

Where 100 is the tali % chance
and 200 the prefix
hope im right didn't work with kal files for long time
and this is just 1 part talisman not with 2 parts

and for removing the bound you could make a new talisman with

(Changeprefix weapon     100 0 ) 

and put a rule in stored procedure
if (x == 0) then blablabla

hope you understood

if by chance you test this tell me if it does work ^_^

[exe08]

Bjorn already posted a way to unbound, just add something at SetPrefix procedure and add prefix 151.

I already got that what you called "hybrid". The problem is, that it is just working on weapon: (changeprefix weapon). You can also make (cahngeprefix defense) for armor, but i would like to have something i can use on both things. ^^

just (changeprefix) isn

[General]

1 item for weap+armor? afaik that's impossible... the codes in inititem stand for the 'kind' of item

(class general charm)(code 3 7 21 0)
(class general charm)(code 3 5 12 0)

7 21 means it will polish
5 12 means it will update weapon prefix

you can try to mess with it, but it's ver time consuming to test those codes...

[Tommy1]

Bjorn , it will clean the weapon/armor state?

Ex: if the weapon already kings / it will remove the kings and bound it?

[exe08]

Hmm ok thanks for this answer.

I got another question:

Why an item that i get ingame with /get xxx 150 do not change to bound after relog. :P

greetz exe

[General]

@tommy it doesnt actually do the talisman on it, it hooks and changes the purpose of the query.

so stats will be kept as they are

[exe08]

What kind of script language is that stored procedure? I would like to understand the commands and try to write something by myself. Is there anywhere something to look up commands etc.?


greetz exe

[_Varis_]

It's SQL.

-V

[General]

look at the existing SP's to learn more!