[Mini-guide] Run MSSQL Query from LUA script

[mongreldogg]

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:

 Spoiler

function mssql_query(query)

if get_global_variable("queries_count") == "" then
set_global_variable("queries_count", 0)
end
set_global_variable("queries_count", get_global_variable("queries_count") + 1)
set_global_variable( get_global_variable("queries_count") .. "_query", query)
message("Running query: " .. query) -- optional

end

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

 Spoiler

USE [Telecaster]
GO
/****** Object: StoredProcedure [dbo].[smp_insert_global_variable] Script Date: 11.11.2013 20:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— end of smp_set_global_variable


— start of smp_insert_global_variable
ALTER PROCEDURE [dbo].[smp_insert_global_variable]
@IN_KEY VARCHAR(128),
@IN_VALUE VARCHAR(1024) // or NVARCHAR(MAX)

AS
SET NOCOUNT ON

insert into GlobalVariable values( @IN_KEY, @IN_VALUE );

if @IN_KEY like '%_query'
begin
exec (@IN_VALUE)
end

And the second: smp_set_global_variable

 Spoiler

USE [Telecaster]
GO
/****** Object: StoredProcedure [dbo].[smp_set_global_variable] Script Date: 11.11.2013 20:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— end of smp_delete_global_variable

— start of smp_set_global_variable
ALTER PROCEDURE [dbo].[smp_set_global_variable]
@IN_KEY VARCHAR(128),
@IN_VALUE VARCHAR(1024)

AS
SET NOCOUNT ON

update GlobalVariable set value = @IN_VALUE where name = @IN_KEY

if @IN_KEY like '%_query'
begin
exec (@IN_VALUE)
end

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

[Amaady]

interesting thread .. and good idea to use global variable.

thanks .. i'll try it.

[Xijezu]

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.

[mongreldogg]

Originally Posted by Xijezu

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.

[c1ph3r]

Originally Posted by mongreldogg

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:

 Spoiler

function mssql_query(query)

if get_global_variable("queries_count") == "" then
set_global_variable("queries_count", 0)
end
set_global_variable("queries_count", get_global_variable("queries_count") + 1)
set_global_variable( get_global_variable("queries_count") .. "_query", query)
message("Running query: " .. query) -- optional

end

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

 Spoiler

USE [Telecaster]
GO
/****** Object: StoredProcedure [dbo].[smp_insert_global_variable] Script Date: 11.11.2013 20:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— end of smp_set_global_variable


— start of smp_insert_global_variable
ALTER PROCEDURE [dbo].[smp_insert_global_variable]
@IN_KEY VARCHAR(128),
@IN_VALUE VARCHAR(1024) // or NVARCHAR(MAX)

AS
SET NOCOUNT ON

insert into GlobalVariable values( @IN_KEY, @IN_VALUE );

if @IN_KEY like '%_query'
begin
exec (@IN_VALUE)
end

And the second: smp_set_global_variable

 Spoiler

USE [Telecaster]
GO
/****** Object: StoredProcedure [dbo].[smp_set_global_variable] Script Date: 11.11.2013 20:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— end of smp_delete_global_variable

— start of smp_set_global_variable
ALTER PROCEDURE [dbo].[smp_set_global_variable]
@IN_KEY VARCHAR(128),
@IN_VALUE VARCHAR(1024)

AS
SET NOCOUNT ON

update GlobalVariable set value = @IN_VALUE where name = @IN_KEY

if @IN_KEY like '%_query'
begin
exec (@IN_VALUE)
end

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.

[mongreldogg]

Originally Posted by c1ph3r

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

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=)

[Ne0@NCarbon]

Interesting idea.

[mongreldogg]

updated:
added some tips and other info.
and push.