v1.0 [Extension] Lua-SQL System (v0.1)

[Sultan 68]

Hello everyone, I have found recently @V3ct0r SQLQuery function using os.execute and decided to make an extension out of it!

Lua-SQL System Extension v0.1:

• Use SQL Query directly using your scripts!
• Built in the extension, there are few sample SQL Queries: AddMallPoints, AddCreditsPoints, ChangePlayerName, and more!
• Requires GetPlayerByName if used by &lua command
• Built in the extension the GetPlayerByName function, if it isn't exists in your files it will add it
• You can freely add any SQL Query Execute function you wish for
• This system will only generate UPDATES to SQL, you cannot "GET" any data from SQL
• Requires SQL 2005 and above to use

For more information & details: http://forum.maindev.ru/threads/npc-dlja-nadoedalok-skajpa.19514/#post-159439

Thank you so much 

Lua-SQL (v0.1).rar

[Sultan 68]

Just put the Lua-SQL folder to your extension folder and load file named "index.lua" to let it work, if you had some problems getting it to work just make sure you do configurations into the file and make sure you use SQL verion: 2005 and above!

 

Just make very sure you configure the following:

LuaSQL = {}
LuaSQL.Directory = GetResPath('script\\extension\\Lua-SQL\\');
LuaSQL.Host = "(local)";
LuaSQL.User = "sa";
LuaSQL.Pass = "";

Feel free to ask if you didn't understand something! 

Edited March 19, 2016 by Sultan

[Yomazu 20]

Not bad.

[Sultan 68]

Important Notice:
You have to use this system rarely, for example: In my server I use this for exchanging item for Item Mall Points / Credits. The more Query executed you might find hang for the Gameserver for couple of seconds.

I have checked by trying to run same function multiple times with for i = 1,99 do AddMallPoints end, which means 99 runs at same time:

Gameserver got hanged/lagged up for around 8 sec(s) for 99 SQL Query run. I believe its fine if admins are going to use it rarely.

Example, in my server I use it for exchanging item for Mall Points. If someone exchanged, the next time he/she exchange will be after 15 second(s) to avoid doing a crash.

Edited March 20, 2016 by Sultan

[V3ct0r 2,117]

Good job, Sultan!

Does sqlcmd.exe display the query output on the GameServer.exe console? Add  1>NUL 2>NUL to end of sqlcmd.exe parameters list to disable the query output. It may increase speed:

sqlcmd.exe -S <MSSQL Address> -U <User> -P <Password> -Q <Query> 1>NUL 2>NUL

 

[Sultan 68]

@V3ct0r No, I don't think it does display it. It's just create file inside folder in the extension called "cache".

[Sultan 68]

@V3ct0r I did what you recommend and it's now slightly faster, find function "LuaSQL.Execute = function(a,b)" inside index.lua and change it to this one:

LuaSQL.Execute = function(a,b)
	if(type(a) == 'userdata')then
		LuaSQL.LG('Executed Query: ['..b..']');
		os.execute("sqlcmd -S "..LuaSQL.Host.." -U "..LuaSQL.User.." -P "..LuaSQL.Pass.." -Q \""..b.."\" 1>NUL 2>NUL");
	else
		LuaSQL.LG('Executed Query: ['..a..']');
		os.execute("sqlcmd -S "..LuaSQL.Host.." -U "..LuaSQL.User.." -P "..LuaSQL.Pass.." -Q \""..a.."\" 1>NUL 2>NUL");
	end
end

Edited March 23, 2016 by Sultan

[Sultan 68]

Lua-SQL System (V0.2) - Stable version:

• Slightly faster and less lag on execution
• Edited LuaSQL.LG function to generate new files instead of editing Query.txt
• Added LuaSQL.LG for every sample function to trace players inside new file Logs.txt
• Removed/Edited few unneccessary stuffs from V0.1

Lua-SQL System (V0.2).rar

[MonkeyCode 453]

Back in ERO, @Xero built something related to this. However, it did hang.
Our solution was that instead of forcing LUA to stop when doing queries, is have a third party be in idle
and read tables that LUA generates. (hence the folder sqlio/update.dat) haha
So for example:
LUA function -> writes to .txt format
Third Party -> reads .txt format and runs the query.

The only problem was, now it doesn't delay ingame, but delay in terms of retrieving data from .txt format.

A better approach at which Xero undergo:
GameServer is now acted like client
Third party is now acted like server
so client (GS) -> sends packet to third party (Server) -> Server does query as stand-alone.

Perhaps this could be a solution that you guys might want to try.

[Guest]

No idea if someone still uses that but I just wanted to warn that in Sultan's examples (Lua-Sql System v0.2) some corrections are required:

- Before changing IGN check if character is offline.

- To ban a character edit: SET ban = 'true' to SET ban = 1;

- To remove ban from character edit: SET ban = 'false' to SET ban = 0;

 

[Masuka00 54]

Thanks

Edited September 14, 2016 by Covadola

[Graf 210]

Somebody can share this?

[Angelix 374]

10 hours ago, Graf said:

Somebody can share this?

On the download link, change "daylight-online.ru" to "pkodev.net", like this: http://pkodev.net/applications/core/interface/file/attachment.php?id=28

[Rogue 19]

15 hours ago, Angelix said:

On the download link, change "daylight-online.ru" to "pkodev.net", like this: http://pkodev.net/applications/core/interface/file/attachment.php?id=28

Fixed.

[Angelix 374]

On 8/24/2020 at 12:40 PM, Snowfall said:

Fixed.

You missed this reply:
 

On 3/23/2016 at 8:14 PM, Sultan said:

Lua-SQL System (V0.2) - Stable version:

• Slightly faster and less lag on execution
• Edited LuaSQL.LG function to generate new files instead of editing Query.txt
• Added LuaSQL.LG for every sample function to trace players inside new file Logs.txt
• Removed/Edited few unneccessary stuffs from V0.1

Lua-SQL System (V0.2).rar

 

[Rogue 19]

15 minutes ago, Angelix said:

You missed this reply:
 

 

Fixed once again.

[kyleflow 35]

I read again this files, does it do not need luasql.dll to work ? since i do not play with other ppl. a little lag due to usage of item does not hinder me