Lua Error: Table Value in SQL Query Concatenation

Data and Databases
1 
-- Create a button for character speech
local chatBtn = display.newRect(100, 100, 200, 50)
chatBtn:setFillColor(0.5, 0.5, 0.8)
chatBtn.characterName = "hero"
chatBtn:addEventListener('tap', startConversation)

local dialogueCount = 0

function startConversation(event)
    local speaker = event.target.characterName
    showNextLine(speaker)
end

function showNextLine(speaker)
    dialogueCount = dialogueCount + 1
    fetchDialogue(dialogueCount, speaker)
end

function fetchDialogue(lineNum, speaker)
    local db = sqlite3.open("mydb.sqlite")
    
    local query = string.format(
        "SELECT dialogue FROM conversations WHERE speaker='%s' LIMIT %d,1",
        speaker, lineNum - 1
    )
    
    for row in db:nrows(query) do
        print(row.dialogue)
    end
    
    db:close()
end

I’m having trouble with a Lua script that fetches character dialogue from a SQLite database. The first line shows up fine, but when I try to get the next one, I get an error about concatenating a table value. I think it’s related to how I’m passing the ‘speaker’ variable. Any ideas on how to fix this? I’m new to Lua and SQL, so I might be missing something obvious.

2

I’ve encountered similar issues with SQLite in Lua. The problem likely arises from constructing your SQL query with string.format(), which can inadvertently lead to a table value instead of the expected string. A safer alternative is to use parameterized queries that properly bind variables and avoid SQL injection as well as concatenation errors.

Consider modifying your fetchDialogue function as follows:

function fetchDialogue(lineNum, speaker)
local db = sqlite3.open(‘mydb.sqlite’)
local stmt = db:prepare(‘SELECT dialogue FROM conversations WHERE speaker = ? LIMIT ?, 1’)
stmt:bind_values(speaker, lineNum - 1)

for row in stmt:nrows() do
    print(row.dialogue)
end

stmt:finalize()
db:close()

end

This approach not only resolves the concatenation error but also makes your code safer and more maintainable.

3

hey max, sounds like u might be havin trouble with string concat. try usin the … operator instead of + for joining strings in lua. also, make sure speaker is actually a string and not a table. u could try print(type(speaker)) to check. hope this helps!