• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

NPCs referencing SQL database

luptonicedtea

New Member
Joined
May 3, 2016
Messages
36
Solutions
1
Reaction score
4
Hello!
The server is TFS 1.3.
I'm trying to make an NPC who references the AAC database to determine whether or not to send someone somewhere. I have added a column in znote_accounts called `adventurer`, which is a boolean. The person uses shop points to acquire the "adventurer" status, and once `adventurer` becomes 1, the player can interact with this NPC to travel to a place.

cbrm has an awesome list of LUA functions, but I can't figure out how to obtain the player's account_id from an NPC. I've tried printing it to the console, but I feel like I've got the order of how NPCs interact with players out of whack.

At this point, I'm not getting an error message, but I can't make the function getAdventurerStatus perform its task. I would really appreciate any help!

Lua:
local keywordHandler = KeywordHandler:new()
local npcHandler = NpcHandler:new(keywordHandler)

NpcSystem.parseParameters(npcHandler)

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

local voices = { {text = 'I miss my grand adventures.'} }
npcHandler:addModule(VoiceModule:new(voices))

--Get znote_accounts adventurer status
local function getAdventurerStatus(cid)
    local resultId = db.storeQuery("SELECT `adventurer` FROM `znote_accounts` WHERE `id` = " .. getAccountNumberByPlayerName(name))
    print(getAccountNumberByPlayerName(name))
    if resultId ~= 1 then
        doRelocate(getPlayerPosition(cid), 32369, 32232, 7)
    else
        npcHandler:say('You\'re not prepared to go on an adventure!', cid)
    end   
end

--Basic
keywordHandler:addKeyword({'hammer'}, StdModule.say, {npcHandler = npcHandler, text = 'Good luck on your adventures! <grumbles> I thought I left my hammer somewhere around here..'})
keywordHandler:addKeyword({'knee'}, StdModule.say, {npcHandler = npcHandler, text = 'No, I\'m still an adventurer.'})
keywordHandler:addKeyword({'old'}, StdModule.say, {npcHandler = npcHandler, text = 'I\'m still an adventurer!'})

npcHandler:setMessage(MESSAGE_GREET, 'What do you want from me,|PLAYERNAME|?')
npcHandler:setMessage(MESSAGE_FAREWELL, 'If you dont go looking for adventure, sometimes it finds you!')
npcHandler:setMessage(MESSAGE_WALKAWAY, 'Adventure awaits!')

npcHandler:addModule(FocusModule:new())
 
Before you run a query with a value acquired from a player's property, you must first sanitize it. In this case it really isn't that relevant, because upon being attributed a nil value for the SQL Query @ db.storeQuery(...), it will simply print an error output to the console. This should happen whenever the "name" (wherein your error lies, because in spite of me not seeing where the getAdventurerStatus function is being referenced, I see it has only one parameter cid which doesn't assist in the definition of "name" [getAccountNumberByPlayerName(name) i.e, this will be nil, as I just explained].

In order to fix:
Lua:
local name = getPlayerName(cid)
if(name == "" and name == nil and ... (extra sanitization and verification here) ...) then
       return false
end

Add before your definition of resultId.

Edit: also, please note making sql queries within code is really impractical. Don't do this, prefer to add retrieval from C++ at instatiation.
 
Great! Thanks for your reply. I know I have a lot to learn about sanitization and the creation of new information at instances in C++. I integrated your suggestion about sanitization, but am still getting no response (in the console or in the NPC chat) that suggests that he is calling on the function getAdventurerStatus.

You mentioned in your response: "... because in spite of me not seeing where the getAdventurerStatus function is being referenced...", and now I'm wondering if that's my issue. Is it in the right place to get used as a function when someone speaks to the NPC? I see that travel-NPCs have the local function addTravelKeyword in npc scripts which rely on specific travelkeywords to active that function. Do I have to have a keyword to activate the function getAdventurerStatus?

Thanks!
 
The best way to do this in the sources.

Load the adventurer status from the database on login and create a getter to use in lua that gets the status from the player, that way you don't need to query the database everytime the player talks to the NPC, you just get it from memory.

Querying the DB with scripts like this can be a problem as players can easily spamm it.

Edit:
What @ScorpionOT meant is that you are creating the function to get the adventurer status but we can't see where you are using it.
 
Sorry, it should have been obvious, but I have been having a ton of errors thrown whenever I try to integrate the function:
Code:
 npcHandler:setCallback(CALLBACK_MESSAGE_DEFAULT, *anyFunctionHere*)
So I switched to using
Code:
nodeadventure = keywordHandler:addKeyword({'adventure'}, StdModule.say, {npcHandler = npcHandler, onlyFocus = true, text = 'To go on an adventure, you have to have an adventurer\'s badge. Do you have it with you?'})
    nodeadventure:addChildKeyword({'yes'}, getAdventurerStatus, {})
    nodeadventure:addChildKeyword({'no'}, StdModule.say, {npcHandler = npcHandler, onlyFocus = true, text = 'Head to the shop to get one!', reset = true})

I'll have to rewrite this in the sources the way y'all are suggesting, but for now I'd like to have it work.
At this point, the only real issue I'm having is pulling the correct value for the adventurer status. resultid (line 16) is returning "true" when the value in the database is 0, 1, or 2. I tried using db.storeQuery instead of db.query, but that's just returning an ever-increasing value (adds 1 every time anyone interacts with the NPC, even if the NPC is reloaded). If anyone has any insight as to why that might be, I'd appreciate it. Perhaps it is as you say, and that NPCs shouldn't be calling SQL statements.
 
Lua:
  local resultId = db.storeQuery("SELECT `adventurer` FROM `znote_accounts` WHERE `id` = " .. getAccountNumberByPlayerName(name))
    print(getAccountNumberByPlayerName(name))
    if resultId then
        local status = result.getDataInt(resultId, "adventurer")
        if status ~= 1 then
            doRelocate(getPlayerPosition(cid), 32369, 32232, 7)
            return
        end
    end
    npcHandler:say('You\'re not prepared to go on an adventure!', cid)
    end
end

This should work.

Calling db.storeQuery returns a shared pointer to a DBResult, so you have to get the value from that.

Now the variable status has the value you want.
 
Last edited:
Back
Top