Lua Help to make npc interact with DB

[Vikarious]

Hello there, I'm working on a banker npc that interact with player's 'balance' on table accounts in distro "9.31 - The Forgotten Server - Version 0.2.11.2 (Mystic Spirit) - R4".


But I got no clue on how to do that, first of all, is that possible without adding any new function?

There is a function on global.lua that can lead the way:

-- Returns player name if player exists in database or 0
function playerExists(name)
	dofile("./config.lua")
	if sqlType == "mysql" then
		env = assert(luasql.mysql())
		con = assert(env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost, mysqlPort))
	else
		env = assert(luasql.sqlite3())
		con = assert(env:connect(sqliteDatabase))
	end

	local cur = assert(con:execute("SELECT `name` FROM `players` WHERE `name` = '" .. escapeString(name) .. "';"))
	local row = cur:fetch({}, "a")

	local name_ = ""
	if row ~= nil then
		name_ = row.name
	end

	cur:close()
	con:close()
	env:close()
	return name_
end

Theres a way to edit this function to make it work on a script or to make a new function so it would be able to check, add and remove values from 'balance'?
Like:
doPlayerWithdrawMoney
doPlayerSetBalance
getPlayerBalance

???

Thanks in advance!!

EDIT:

As I'm not being sucessful adding or editing that one function I'm trying this script:

local keywordHandler = KeywordHandler:new()
local npcHandler = NpcHandler:new(keywordHandler)
NpcSystem.parseParameters(npcHandler)
local talkState = {}
 
function onCreatureAppear(cid)			npcHandler:onCreatureAppear(cid) end
function onCreatureDisappear(cid)		npcHandler:onCreatureDisappear(cid) end
function onCreatureSay(cid, type, msg)	npcHandler:onCreatureSay(cid, type, msg) end
function onThink()						npcHandler:onThink() end
 




function creatureSayCallback(cid, type, msg)
	local talkUser = NPCHANDLER_CONVBEHAVIOR == CONVERSATION_DEFAULT and 0 or cid
-- select action 



    if (msgcontains(msg, "balance")) and (npcHandler:isFocused(cid)) then
      selfSay('Your account balance is ' .. db.executeQuery('SELECT balance FROM players WHERE name=' .. db.escapeString(name) .. ' LIMIT 1'), nil .. ' gold.', cid)
	
		npcHandler:addFocus(cid)
		talkState[talkUser] = 0
		return true




    end 

end

npcHandler:setCallback(CALLBACK_MESSAGE_DEFAULT, creatureSayCallback) 
npcHandler:addModule(FocusModule:new())

but it returns this error:

[14/12/2011 09:17:16] Lua Script Error: [Npc interface] 
[14/12/2011 09:17:16] data/npc/scripts/bankold.lua:onCreatureSay
[14/12/2011 09:17:16] data/db.lua:10: attempt to call field 'sqlite3' (a nil value)
[14/12/2011 09:17:16] stack traceback:
[14/12/2011 09:17:16] 	[C]: in function 'sqlite3'
[14/12/2011 09:17:16] 	data/db.lua:10: in function 'getConnection'
[14/12/2011 09:17:16] 	data/db.lua:22: in function 'executeQuery'
[14/12/2011 09:17:16] 	data/npc/scripts/bankold.lua:22: in function 'callback'
[14/12/2011 09:17:17] 	data/npc/lib/npcsystem/npchandler.lua:390: in function 'onCreatureSay'
[14/12/2011 09:17:17] 	data/npc/scripts/bankold.lua:8: in function <data/npc/scripts/bankold.lua:8>

PD: I'm using 'mysql" and I don't know why it returns about sqlite =S

 

[sn3ejk]

You should use a sample function for database operation. Try it (not tested, ofc):​

function ExistsPlayer(name)
	local query, value = db.getResult("SELECT `name` FROM `players` WHERE `name` = ".. name .." LIMIT 1;"), false
	if (query:getID() ~= -1) then
		value = true
	end
	query:free()
	return value
end

And when you using a database in NPC, you should set exhausted.​

 

[Vikarious]

I have added the function on script but console returns me this error:

[15/12/2011 00:03:33] Lua Script Error: [Npc interface] 
[15/12/2011 00:03:33] data/npc/scripts/bankold.lua:onCreatureSay
[15/12/2011 00:03:33] data/npc/scripts/bankold.lua:35: attempt to concatenate global 'query' (a nil value)
[15/12/2011 00:03:33] stack traceback:
[15/12/2011 00:03:33] 	[C]: in function '__concat'
[15/12/2011 00:03:33] 	data/npc/scripts/bankold.lua:35: in function 'callback'
[15/12/2011 00:03:33] 	data/npc/lib/npcsystem/npchandler.lua:390: in function 'onCreatureSay'
[15/12/2011 00:03:33] 	data/npc/scripts/bankold.lua:8: in function <data/npc/scripts/bankold.lua:8>

Script here:

local keywordHandler = KeywordHandler:new()
local npcHandler = NpcHandler:new(keywordHandler)
NpcSystem.parseParameters(npcHandler)
local talkState = {}
 
function onCreatureAppear(cid)			npcHandler:onCreatureAppear(cid) end
function onCreatureDisappear(cid)		npcHandler:onCreatureDisappear(cid) end
function onCreatureSay(cid, type, msg)	npcHandler:onCreatureSay(cid, type, msg) end
function onThink()						npcHandler:onThink() end




function creatureSayCallback(cid, type, msg)
	local talkUser = NPCHANDLER_CONVBEHAVIOR == CONVERSATION_DEFAULT and 0 or cid


	



	function ExistsPlayer(name)
		local query, value = db.getResult("SELECT `balance` FROM `players` WHERE `name` = ' .. getPlayerName(cid) .. '"), false
		if (query:getID() ~= -1) then
		value = true
		end
		query:free()
		return value
	end




    if (msgcontains(msg, "balance")) and (npcHandler:isFocused(cid)) then
      selfSay('Your account balance is ' .. query .. ' gold.', cid)
	
		npcHandler:addFocus(cid)
		talkState[talkUser] = 0
		return true




    end 

end

npcHandler:setCallback(CALLBACK_MESSAGE_DEFAULT, creatureSayCallback) 
npcHandler:addModule(FocusModule:new())

I'm almost giving up, it is terryfing(?) if youre not a scripter to look at it, change a lot and even though, be unable to solve the problem =X

PD: The value I'm trying to get is 'balance' from player X, I think that if I'm able to make this part of script work, to add deposit/withdraw and transfer will be easier.