Jump to content
kyleflow

LuaSQL Access seem not working

Recommended Posts

Hi guys, previously these setting is working properly before I reinstall everything with the same files. But now, only tradedb working properly but the LuaSQL connection to change the database in tradedb seem to be not working. This are the list of my setting.

 

-- ***********************************
-- Sample Lua-SQL System
-- Requires: SQL Server 2005
-- By Sultan | V3ct0r
-- ***********************************
local PLUGIN_NAME = '[[LUA-SQL System]]'
local PLUGIN_VERSION = '0.2'
local PLUGIN_AUTHOR = 'Sultan | V3ct0r'

--[[
	******************
	** Version: 0.2 **
	******************
	[*] 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
	
	
	******************
	** Version: 0.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
]]--

-- --------------------------
-- Display loading message on startup
-- --------------------------
print(">> Loading extension: "..PLUGIN_NAME)
print("	[Version: "..PLUGIN_VERSION.."] from "..PLUGIN_AUTHOR)

-- ------------------------
-- LUA-SQL ::DO NOT TOUCH::
-- ------------------------
do
	LuaSQL = {}
	LuaSQL.Directory = GetResPath('script\\extension\\Lua-SQL\\');
	LuaSQL.Host = "(local)"; --asal (local)
	LuaSQL.User = "sa";
	LuaSQL.Pass = "Y87dc#$98";

	LuaSQL.LG = function(file_name,text)
		local file = LuaSQL.Directory..file_name..'.txt'
		LogFile = io.open(file,'a')
		LogFile:write("["..os.date().."]\t"..text.."\n")
		LogFile:close()
	end

	LuaSQL.Execute = function(query)
		LuaSQL.LG('Query','Executed Query: ['..query..']');
		os.execute("sqlcmd -S "..LuaSQL.Host.." -U "..LuaSQL.User.." -P "..LuaSQL.Pass.." -Q \""..query.."\" 1>NUL 2>NUL");
	end
	
	-- if GetPlayerByName is nil, it will generate
	if(GetPlayerByName == nil)then
		Roles = {}
		
		Timer = cha_timer
		cha_timer = function(r,f,t)
			if(IsPlayer(r) == 1)then
				local characterName = GetChaDefaultName(r)
				if(Roles[characterName] == nil)then
					Roles[characterName] = r
				end
			end
			Timer(r,f,t)
		end
		
		GetPlayerByName = function(name)
			if(Roles[name] ~= nil)then
				return Roles[name]
			end
			return nil
		end
	end
end

-- ------------------------
-- LUA-SQL Functions
-- ------------------------

--[[
	*****************************
	** Sample Custom Functions **
	*****************************
	[*]: AddMallPoints(cha,points) => Requires Userdata to grant Mall Points
	[*]: AddCreditsPoints(cha,points) => Requires Userdata to grant Credits
	[*]: ChangePlayerName(cha,newName) => Requires Userdata to change players name
	[*]: ChangeGuildName(guildID,newName) => Requires Guild ID to change guild name
	[*]: Ban(cha) => Will ban character by Userdata
	[*]: UnBan(account Name) => Will unban character by account
	
	P/S: You can add yours, just use one of samples below and create your own.
	
	@FAQ [How To Use]:
	
	Example:
	&lua AddMallPoints("Astral",1000)
	&lua ChangeGuildName(1,"New Guild Name")
	&lua Ban("Account Name")
]]--

function AddMallPoints(cha,points)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE tradedb.dbo.AccountInfo SET Money = Money + "..points.." WHERE accName = '"..accountName.."'")
		LuaSQL.LG('Logs','[AddMallPoints] = Account name['..accountName..'] successfully added '..points..' mall points');
	end
end

function AddCreditsPoints(cha,points)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE GameDB.dbo.account SET credits = credits + "..points.." WHERE act_name = '"..accountName.."'")
		LuaSQL.LG('Logs','[AddCreditsPoints] = Account name['..accountName..'] successfully added '..points..' credits');
	end
end

function ChangePlayerName(cha,newName)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local characterName,characterID = GetChaDefaultName(cha),GetCharID(cha)
		LuaSQL.Execute("UPDATE GameDB.dbo.character SET cha_name = '"..newName.."' WHERE cha_id = '"..characterID.."'")
		LuaSQL.LG('Logs','[ChangePlayerName] = Character name['..characterName..'] with ID['..characterID..'] successfully changed name to ['..newName..']');
	end
end

function ChangeGuildName(guildID,newName)
	LuaSQL.Execute("UPDATE GameDB.dbo.guild SET guild_name = '"..newName.."' WHERE guild_id = '"..guildID.."'")
	LuaSQL.LG('Logs','[ChangeGuildName] = Guild Name['..GetGuildName(guildID)..'] with ID['..guildID..'] successfully changed name to ['..newName..']');
end
	
function Ban(cha)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE AccountServer.dbo.account_login SET ban = 'True' WHERE name = '"..accountName.."'")
		LuaSQL.LG('Logs','[Ban] = Account name['..accountName..'] was successfully banned');

		-- kick cha
		local pkt = GetPacket()
		WriteCmd(pkt,1505)
		SendPacket(cha,pkt)
	end
end

function UnBan(accountName)
	LuaSQL.Execute("UPDATE AccountServer.dbo.account_login SET ban = 'False' WHERE name = '"..accountName.."'")
	LuaSQL.LG('Logs','[UnBan] = Account name['..accountName..'] was successfully banned');
end

 

The corresponding itemeffect function for the item

 

function ImpsPointCard10 ( role , Item )
	local points = AddMallPoints(role,10)
	if points == 1 then
		SystemNotice(role ,"You just got 100 points !")
    end

end

 

The corresponding iteminfo script.

 

7317	10IMP in a Bottle	q0078	10130005	0	0	0	0	0	0	31	0	0	0	0	0	1	1	1	1	99	0	1000	-1,-2,-2,-2	0	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2	0	0	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0	0,0	0,0	0	0	0	0	0	0	0	0	0	ImpsPointCard10	0	0	0	0,0	0	0	IMP in a bottle	0

 

Normally if I use the item mentioned above, they will be an update in the log mentioning  a success of adding the IMP but the actual conditions in the IGS shop or database itself is not updating through item usage. Weirdly its not working as previously when I do it before clean setup, working properly as desired. Can anyone give any input on what should I do to troubleshoot this.

Share this post


Link to post
Share on other sites
On 12/4/2022 at 11:11 AM, kyleflow said:

Hi guys, previously these setting is working properly before I reinstall everything with the same files. But now, only tradedb working properly but the LuaSQL connection to change the database in tradedb seem to be not working. This are the list of my setting.

 


-- ***********************************
-- Sample Lua-SQL System
-- Requires: SQL Server 2005
-- By Sultan | V3ct0r
-- ***********************************
local PLUGIN_NAME = '[[LUA-SQL System]]'
local PLUGIN_VERSION = '0.2'
local PLUGIN_AUTHOR = 'Sultan | V3ct0r'

--[[
	******************
	** Version: 0.2 **
	******************
	[*] 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
	
	
	******************
	** Version: 0.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
]]--

-- --------------------------
-- Display loading message on startup
-- --------------------------
print(">> Loading extension: "..PLUGIN_NAME)
print("	[Version: "..PLUGIN_VERSION.."] from "..PLUGIN_AUTHOR)

-- ------------------------
-- LUA-SQL ::DO NOT TOUCH::
-- ------------------------
do
	LuaSQL = {}
	LuaSQL.Directory = GetResPath('script\\extension\\Lua-SQL\\');
	LuaSQL.Host = "(local)"; --asal (local)
	LuaSQL.User = "sa";
	LuaSQL.Pass = "Y87dc#$98";

	LuaSQL.LG = function(file_name,text)
		local file = LuaSQL.Directory..file_name..'.txt'
		LogFile = io.open(file,'a')
		LogFile:write("["..os.date().."]\t"..text.."\n")
		LogFile:close()
	end

	LuaSQL.Execute = function(query)
		LuaSQL.LG('Query','Executed Query: ['..query..']');
		os.execute("sqlcmd -S "..LuaSQL.Host.." -U "..LuaSQL.User.." -P "..LuaSQL.Pass.." -Q \""..query.."\" 1>NUL 2>NUL");
	end
	
	-- if GetPlayerByName is nil, it will generate
	if(GetPlayerByName == nil)then
		Roles = {}
		
		Timer = cha_timer
		cha_timer = function(r,f,t)
			if(IsPlayer(r) == 1)then
				local characterName = GetChaDefaultName(r)
				if(Roles[characterName] == nil)then
					Roles[characterName] = r
				end
			end
			Timer(r,f,t)
		end
		
		GetPlayerByName = function(name)
			if(Roles[name] ~= nil)then
				return Roles[name]
			end
			return nil
		end
	end
end

-- ------------------------
-- LUA-SQL Functions
-- ------------------------

--[[
	*****************************
	** Sample Custom Functions **
	*****************************
	[*]: AddMallPoints(cha,points) => Requires Userdata to grant Mall Points
	[*]: AddCreditsPoints(cha,points) => Requires Userdata to grant Credits
	[*]: ChangePlayerName(cha,newName) => Requires Userdata to change players name
	[*]: ChangeGuildName(guildID,newName) => Requires Guild ID to change guild name
	[*]: Ban(cha) => Will ban character by Userdata
	[*]: UnBan(account Name) => Will unban character by account
	
	P/S: You can add yours, just use one of samples below and create your own.
	
	@FAQ [How To Use]:
	
	Example:
	&lua AddMallPoints("Astral",1000)
	&lua ChangeGuildName(1,"New Guild Name")
	&lua Ban("Account Name")
]]--

function AddMallPoints(cha,points)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE tradedb.dbo.AccountInfo SET Money = Money + "..points.." WHERE accName = '"..accountName.."'")
		LuaSQL.LG('Logs','[AddMallPoints] = Account name['..accountName..'] successfully added '..points..' mall points');
	end
end

function AddCreditsPoints(cha,points)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE GameDB.dbo.account SET credits = credits + "..points.." WHERE act_name = '"..accountName.."'")
		LuaSQL.LG('Logs','[AddCreditsPoints] = Account name['..accountName..'] successfully added '..points..' credits');
	end
end

function ChangePlayerName(cha,newName)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local characterName,characterID = GetChaDefaultName(cha),GetCharID(cha)
		LuaSQL.Execute("UPDATE GameDB.dbo.character SET cha_name = '"..newName.."' WHERE cha_id = '"..characterID.."'")
		LuaSQL.LG('Logs','[ChangePlayerName] = Character name['..characterName..'] with ID['..characterID..'] successfully changed name to ['..newName..']');
	end
end

function ChangeGuildName(guildID,newName)
	LuaSQL.Execute("UPDATE GameDB.dbo.guild SET guild_name = '"..newName.."' WHERE guild_id = '"..guildID.."'")
	LuaSQL.LG('Logs','[ChangeGuildName] = Guild Name['..GetGuildName(guildID)..'] with ID['..guildID..'] successfully changed name to ['..newName..']');
end
	
function Ban(cha)
	if(type(cha) ~= 'userdata')then
		cha = GetPlayerByName(cha)
	end
	if(cha ~= nil)then
		local accountName = GetActName(cha)
		LuaSQL.Execute("UPDATE AccountServer.dbo.account_login SET ban = 'True' WHERE name = '"..accountName.."'")
		LuaSQL.LG('Logs','[Ban] = Account name['..accountName..'] was successfully banned');

		-- kick cha
		local pkt = GetPacket()
		WriteCmd(pkt,1505)
		SendPacket(cha,pkt)
	end
end

function UnBan(accountName)
	LuaSQL.Execute("UPDATE AccountServer.dbo.account_login SET ban = 'False' WHERE name = '"..accountName.."'")
	LuaSQL.LG('Logs','[UnBan] = Account name['..accountName..'] was successfully banned');
end

 

The corresponding itemeffect function for the item

 


function ImpsPointCard10 ( role , Item )
	local points = AddMallPoints(role,10)
	if points == 1 then
		SystemNotice(role ,"You just got 100 points !")
    end

end

 

The corresponding iteminfo script.

 


7317	10IMP in a Bottle	q0078	10130005	0	0	0	0	0	0	31	0	0	0	0	0	1	1	1	1	99	0	1000	-1,-2,-2,-2	0	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2,-2	0	0	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2	-1,-2,-2,-2,-2,-2,-2,-2,-2,-2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0,0	0	0,0	0,0	0	0	0	0	0	0	0	0	0	ImpsPointCard10	0	0	0	0,0	0	0	IMP in a bottle	0

 

Normally if I use the item mentioned above, they will be an update in the log mentioning  a success of adding the IMP but the actual conditions in the IGS shop or database itself is not updating through item usage. Weirdly its not working as previously when I do it before clean setup, working properly as desired. Can anyone give any input on what should I do to troubleshoot this.

UP.

 

Can anyone answer me why this is not working as previously, its working properly before i reset my laptop. Is there any setting in MySQL that I miss or any required files that I miss somehow after resetting.

Share this post


Link to post
Share on other sites

Hello @kyleflow,

 

I am not sure that I am the co-author of the script. Perhaps I once gave advice on writing it, no more.

 

I strongly don't recommend you use that script since it based on the os.execute() lua function. This function is synchronius, that means that your whole GameServer.exe will hang for the time of execution of the function which can cause lags.


Share this post


Link to post
Share on other sites
20 hours ago, V3ct0r said:

Hello @kyleflow,

 

I am not sure that I am the co-author of the script. Perhaps I once gave advice on writing it, no more.

 

I strongly don't recommend you use that script since it based on the os.execute() lua function. This function is synchronius, that means that your whole GameServer.exe will hang for the time of execution of the function which can cause lags.

That being said, is there a simpler solution to resolve this issues. Do u have other script that can utilize the same script as the mentioned above. I think there is some error on my part where the new reset on my laptop cause me a crucial files for the script above to work.

Share this post


Link to post
Share on other sites
13 minutes ago, kyleflow said:

That being said, is there a simpler solution to resolve this issues. Do u have other script that can utilize the same script as the mentioned above. I think there is some error on my part where the new reset on my laptop cause me a crucial files for the script above to work.

Why not just use LuaSQL?

Share this post


Link to post
Share on other sites
3 hours ago, Angelix said:

Why not just use LuaSQL?

I'm using exactly that files of LuaSQL.dll to make it work. I did not know if the script would work without the LuaSQL. I use the iteration for 1.38 instead of 1.36.

Share this post


Link to post
Share on other sites
5 hours ago, kyleflow said:

I'm using exactly that files of LuaSQL.dll to make it work. I did not know if the script would work without the LuaSQL. I use the iteration for 1.38 instead of 1.36.

Those functions you posted do not use LuaSQL. You can replace them for the ones in the other thread. 

Share this post


Link to post
Share on other sites
11 hours ago, Angelix said:

Those functions you posted do not use LuaSQL. You can replace them for the ones in the other thread. 

can you kindly pinpoint which script should i use. some of the script u put in the post u tagged does not return any values in my system. I do get confuse somehow about this. Thanks a lot @Angelix

Share this post


Link to post
Share on other sites
5 minutes ago, kyleflow said:

can you kindly pinpoint which script should i use. some of the script u put in the post u tagged does not return any values in my system. I do get confuse somehow about this. Thanks a lot @Angelix

There's example on how to use. There's probably some more scripts lying around in the forum if you search for them.

 

Share this post


Link to post
Share on other sites
11 hours ago, Angelix said:

There's example on how to use. There's probably some more scripts lying around in the forum if you search for them.

 

I'm not sure how to use anything on the forum on regards to this issues. I did ask on the post u tagged in march this year and still cant configure about it. haha.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...