Register for your free account! | Forgot your password?

Go Back   elitepvpers > MMORPGs > Rappelz > Rappelz Private Server
You last visited: Today at 11:18

  • Please register to post and access all features, it's quick, easy and FREE!

 

[Mini-guide] Run MSSQL Query from LUA script

Reply
 
Old   #1
 
elite*gold: 30
Join Date: Mar 2012
Posts: 633
Received Thanks: 287
[Mini-guide] Run MSSQL Query from LUA script

Once i've got an idea to make it by using console proggie that was running by os.execute function, it was horrible enough to call it something like shit.

I've found a better and easier way to make it.

We just need to make one LUA script function and update two stored procedures in Telecaster.

So let's start from LUA:


so we write new SQL query into GlobalVariables table, it's aso good to see queries logs, but you will clear your global variables table time after another when it will be really flooded by queries. Or you can modify stored procedures (i'll write it below) to delete written query automatically.

Lets continue on stored procedures:

First: smp_insert_global_variable

And the second: smp_set_global_variable

So, if you found it useful, use it=)
If not, i won't argue about the power of possibility to run SQL queries from LUA script or console/chat command.
If you found it so easy to do - i don't really care if you can better.

And yeah i forgot: HOW TO USE xD

mssql_query("DROP Telecaster")

And bye bye dear Telecaster!

One tip to use:

If you need to execute huge queries, make a stored procedure for it and call it by mssql_query. set_global_variable() command doesnt support writing variable value with more than 1024 symbols of length.

To addition: some tests showed that wrong written query don't crash game server, just don't execute and server works good anyway, when other stored procedures are crashing GS when return any error while its execution.


Thanks for Thanksfarmer!

P.S. i don't really care if you have crashed your server or hurted your databases using THIS. Just keep your hands closer to your balls.

P.P.S. there was so much words said about that GS will update global variables table when it saves all data etc, but some tests are shown that global variables writes into database momentary, as well as queries execution using this way. Just tested it on own function that allows player to move his donation points back to shop inventory and it was working without any delays before execution. But there is no possibility to get global variable that's written by query momentary, because it must be stored in server memory and it takes time. btw its possible to refresh gameserver memory and read all variables again from table using #refresh() command (tested by my own), but first, it takes time + freeze GS, and second, it will bug markets if you don't know how to act well with this shit.

So there is no possibility to return query result data back to script function using global variables way, means this ^^ without damaging server productivity.
If i'm not right and there IS a way, correct me and share your idea



mongreldogg is offline  
Thanks
7 Users
Old   #2
 
elite*gold: 0
Join Date: Oct 2012
Posts: 14
Received Thanks: 2
interesting thread .. and good idea to use global variable.

thanks .. i'll try it.


Amaady is offline  
Old   #3


 
elite*gold: 30
Join Date: May 2011
Posts: 4,805
Received Thanks: 3,185
As long as the database get updated immediately it isn't a bad idea, but I actually doubt that.
Usually the GS does save the global variable temporary, on update/exit it should update those values in the database, so it could take a bit to get executed.
I actually think there are better solutions for this tho.
Anyways, nice idea.
Xijezu is offline  
Old   #4
 
elite*gold: 30
Join Date: Mar 2012
Posts: 633
Received Thanks: 287
Quote:
Originally Posted by Xijezu View Post
As long as the database get updated immediately it isn't a bad idea, but I actually doubt that.
Usually the GS does save the global variable temporary, on update/exit it should update those values in the database, so it could take a bit to get executed.
I actually think there are better solutions for this tho.
Anyways, nice idea.
about better solutions - may be. but for now i use this for simple db updates, such as banning accounts using chat command, etc.


mongreldogg is offline  
Old   #5
 
elite*gold: 0
Join Date: Sep 2008
Posts: 1,570
Received Thanks: 1,154
Quote:
Originally Posted by mongreldogg View Post
Once i've got an idea to make it by using console proggie that was running by os.execute function, it was horrible enough to call it something like shit.

I've found a better and easier way to make it.

We just need to make one LUA script function and update two stored procedures in Telecaster.

So let's start from LUA:


so we write new SQL query into GlobalVariables table, it's aso good to see queries logs, but you will clear your global variables table time after another when it will be really flooded by queries. Or you can modify stored procedures (i'll write it below) to delete written query automatically.

Lets continue on stored procedures:

First: smp_insert_global_variable

And the second: smp_set_global_variable

TIP: make your 'value' column in dbo.GlobalVariable wider for long string values (recommended: NVARCHAR(MAX) )

So, if you found it useful, use it=)
If not, i won't argue about the power of possibility to run SQL queries from LUA script or console/chat command.
If you found it so easy to do - i don't really care if you can better, i'll just be interested if you'll make an update for this shit to return values to user when running SELECT or FETCH query.

And yeah i forgot: HOW TO USE xD

mssql_query("DROP Telecaster")

And bye bye dear Telecaster!

Thanks for Thanksfarmer!

P.S. i don't really care if you have crashed your server or hurted your databases using THIS. Just keep your hands closer to your balls.
And remember: wrong query will crash your server... maybe
Be careful in changing datatypes in the database. These datatypes are hardcoded in the gameserver, too! That means if the original value is VARCHAR(1024) and you changed it to VARCHAR(MAX) the server can crash if there is a VARCHAR with 1025 Bytes because server sided validation of the datatype will fail.
And it's not recommended to work on the databases during the server is running. Changings on the arcadia database won't do anything because the gs is loading the arcadia on startup and do not reload it withouth using "refresh" (which will bug your shops and only reload about 50%).
Changings on the telecaster during the server is running are very difficult because the gs isn't reloading the database data everytime. The database is used to store the information of the players for a long time (if the server isn't running or will be rebooted). If the server is running all gamerelated data is stored in the memory and most of it can't be manipulated via database! If you login the server loads your data from the database. Now most of the database transactions are save operations to synchronize database and servermemory. Every 2 minutes the gs is updating the database with the original gamedata from the memory. That means if you change something in the database the gameserver will overwrite these changes most of the time during saveintervalls.

@Marcel: dbo.GlobalVariable is synchronized "on the fly" with the gameserver. But it won't realize if you delete something with a third party program or directly in the db. That means GS will hold this Variable and store it during the shutdown. (Which could cause 2 executions). And changing a database during lifetime is a bad idea because it could cause a lot of crashes.
c1ph3r is offline  
Old   #6
 
elite*gold: 30
Join Date: Mar 2012
Posts: 633
Received Thanks: 287
Quote:
Originally Posted by c1ph3r View Post
Be careful in changing datatypes in the database. These datatypes are hardcoded in the gameserver, too! That means if the original value is VARCHAR(1024) and you changed it to VARCHAR(MAX) the server can crash if there is a VARCHAR with 1025 Bytes because server sided validation of the datatype will fail.
And it's not recommended to work on the databases during the server is running. Changings on the arcadia database won't do anything because the gs is loading the arcadia on startup and do not reload it withouth using "refresh" (which will bug your shops and only reload about 50%).
Changings on the telecaster during the server is running are very difficult because the gs isn't reloading the database data everytime. The database is used to store the information of the players for a long time (if the server isn't running or will be rebooted). If the server is running all gamerelated data is stored in the memory and most of it can't be manipulated via database! If you login the server loads your data from the database. Now most of the database transactions are save operations to synchronize database and servermemory. Every 2 minutes the gs is updating the database with the original gamedata from the memory. That means if you change something in the database the gameserver will overwrite these changes most of the time during saveintervalls.

@Marcel: dbo.GlobalVariable is synchronized "on the fly" with the gameserver. But it won't realize if you delete something with a third party program or directly in the db. That means GS will hold this Variable and store it during the shutdown. (Which could cause 2 executions). And changing a database during lifetime is a bad idea because it could cause a lot of crashes.
right you are, and as i said before i won't argue on how to make it useful. there are a lots of variants, the simple one is to block account, as i said before. another one is to insert an item into cash shop etc.

about refresh function - its also possible to make a function that will:
- make a backup of marketresource table into another table
- clear marketresource table
- run refresh function

and another one function that will fill marketresource table from the backup back.
and all of this - using chat or console command that you created by yourself.

and its just examples

Quote:
That means GS will hold this Variable and store it during the shutdown. (Which could cause 2 executions)
there won't be a second execution because execution of query is running only when this global variable will be either updated or firstly inserted into db. thats why i made a counter of queries and every query is written as new variable. and cleaning the global variables table from already executed queries is not hard.


P.S. about changing data type, didn't knew, my fault=)
mongreldogg is offline  
Old   #7
 
elite*gold: 0
Join Date: Jul 2011
Posts: 111
Received Thanks: 85
Interesting idea.
Ne0@NCarbon is offline  
Old   #8
 
elite*gold: 30
Join Date: Mar 2012
Posts: 633
Received Thanks: 287
updated:
added some tips and other info.
and push.


mongreldogg is offline  
Thanks
1 User
Reply



« Previous Thread | Next Thread »

Similar Threads
[GUIDE] Run SQL Query from LUA script
How long time we were searching for solution? Lets have some guide and use it to f**k officials by doggie style=) Everything we must have - one...
29 Replies - Rappelz Private Server



All times are GMT +1. The time now is 11:18.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.

Support | Contact Us | FAQ | Advertising | Privacy Policy | Abuse
Copyright ©2017 elitepvpers All Rights Reserved.