SQLite Problem While Retrieving Data

[Bilal135]

I know that it is definitely saving the text in the db because I got an error in debug after 5 minutes that '5 minutes have passed and data has not been retrieved from table (with correct data shown)' something like that. I'm not sure if I'm retrieving the data in the right way. Any help would be appreciated.

server:

function loadAllData(queryHandle)
    local results = dbPoll(queryHandle, 0)

    for index, textresults in pairs(results) do
        text_c_changelog = textresults.text_changelog
        text_c_rules = textresults.text_rules
        text_c_general = textresults.text_general
        triggerClientEvent(root, "updateMemoOnStart", resourceRoot, text_c_changelog, text_c_rules, text_c_general)
    end
end


addEventHandler("onResourceStart", root, function()
    local db = exports.infopaneldb:getConnection()
    dbQuery(loadAllData, db, "SELECT * FROM text")
end)



addEvent("updateMemoCallback", true)
function memoCB(text_changelog, text_rules, text_general)
    local db = exports.infopaneldb:getConnection()
    dbQuery(db, "UPDATE text WHERE text_changelog = ?, text_rules = ?, text_general = ?;", text_changelog, text_rules, text_general) 
    triggerClientEvent(root, "updateMemo", resourceRoot, text_changelog, text_rules, text_general)
    local players = getElementsByType("player")
end
addEventHandler("updateMemoCallback", root, memoCB)

client:

addEvent("updateMemo", true)
addEventHandler("updateMemo", root, function(text_changelog, text_rules, text_general)

guiSetText(InfoPanel.memo[1], text_changelog)
guiSetText(InfoPanel.memo[2], text_rules)
guiSetText(InfoPanel.memo[3], text_general)

end)

addEvent("updateMemoOnStart", true)
addEventHandler("updateMemoOnStart", root, function(text_c_changelog, text_c_rules, text_c_general)

guiSetText(InfoPanel.memo[1], text_c_changelog)
guiSetText(InfoPanel.memo[2], text_c_rules)
guiSetText(InfoPanel.memo[3], text_c_general)

end)

Also, while making the database in SQLite browser application, I could not set 'Auto increment' for all 3 of the table values - text_changelog, text_rules and text_general. Maybe this is the cause? 

[Lpsd]

You are not providing a callback to your `UPDATE` query - which means dbPoll is not being called on the query handler, resulting in the query not being freed.

You should instead use dbExec, instead of dbQuery, for your `UPDATE` query - providing you don't need to do anything once the query has completed. Otherwise, see the wiki page for dbQuery on providing a callback function with dbPoll.

Regarding your problem with auto increment. You cannot set a text/string type column to auto increment. Auto increment is limited to INT type columns. You should have an additional column (at the start) called `id`, with INT type, which is set to UNIQUE Key and Auto Increment.

[Bilal135]

I do not want the query to be freed as I have to use its values. Can't use dbExec either because then I wouldn't be able to update the memo text with the same GUI button. Tried this, still not retrieving values,

addEventHandler("onResourceStart", root, function()
    local db = exports.infopaneldb:getConnection()
    local dbquery = dbQuery(function(qh)
        local results = dbPoll(qh, 0)
        for index, textresults in pairs(results) do
            text_c_changelog = textresults.text_changelog
            text_c_rules = textresults.text_rules
            text_c_general = textresults.text_general
            triggerClientEvent(root, "updateMemoOnStart", resourceRoot, text_c_changelog, text_c_rules, text_c_general)
            outputChatBox(""..tostring(text_c_changelog).."", 255, 255, 255) -- this is a test
        end
    end, db, "SELECT * FROM text")
end)

This is how the wiki did it.

[JeViCo]

3 hours ago, Lynch said:

data has not been retrieved from table

I guess it's because of the second argument of dbPoll function:

local results = dbPoll(qh, 0)

Try to use -1 instead of 0 or use little amount of time like 500-1000 ms

local results = dbPoll(qh, -1) -- wait until a result is ready

 

[Bilal135]

@JeViCo Still not working.

outputChatBox(""..tostring(text_c_changelog).."", root, 255, 255, 255)

outputs 'nil' every time the resource is restarted.

[Bilal135]

I downloaded the database from the server and browsed the table and found no values in it. This is where I trigger 'updateMemoCallback' in client side.

if (source == InfoPanel.button[2]) then
text_changelog = guiGetText(InfoPanel.memo[1])
text_rules = guiGetText(InfoPanel.memo[2])
text_general = guiGetText(InfoPanel.memo[3])
triggerServerEvent("updateMemoCallback", resourceRoot, text_changelog, text_rules, text_general)
  
-- on 'onClientGUIClick' event.
  
  

[JeViCo]

Possible problems:
1) Script:
Check variable values before updating/saving into database. They might have nil values
2) Database:
    1) Wrong column types may cause problems
    2) Wrong column length may cause problems

[Bilal135]

@JeViCo I checked the variable values and they're fine. What do you mean by wrong column types / length?

[JeViCo]

By column types and column length i mean this

Apologizing for the inaccuracy

[Bilal135]

Solved. 

Just replaced 'WHERE' with 'SET'.

Thanks @JeViCo anyway.

Edited December 14, 2019 by Lynch

[JeViCo]

I tried