Learning SQLite

Enargy, · May 23, 2015

Hello,

I decided to learn to make my own sqlite saved but I'm as stuck and having a lot mistakes about himself. If someone has something to suggest or what is exactly that can do (dbExec, dbPoll, dbQuery, dbFree)

I've taken examples from different topics and as results got this:

local database = dbConnect( "sqlite", "files.db" ); 
  
function makeDB () 
    dbExec(database, "CREATE TABLE IF NOT EXISTS accounts ( money TEXT, account TEXT)"); 
end 
  
function saveDataBase() 
    local playerAccount = getPlayerAccount ( source ); 
    local playerMoney   = getPlayerMoney ( source ); 
     
    --if (not isGuestAccount( playerAccount )) then -- if player has not an account. 
     
        local query = dbExec(database, "SELECT * FROM accounts WHERE money = ?", playerMoney); 
     
        local _, rows = dbPoll(query, -1); 
        if rows == 0 then 
            dbExec(database, "INSERT INTO accounts SET money = ?", playerMoney); 
        end 
         
    dbExec(database, "UPDATE accounts SET money = ?", playerMoney); 
     
    --end 
end 
  
--addEventHandler("onPlayerQuit", getRootElement(), saveDataBase) 
addEventHandler("onPlayerLogout", getRootElement(), saveDataBase) 
addEventHandler("onResourceStart", resourceRoot, makeDB)

When I leave the server or logout shows: Bad argument @ 'dbPoll' [Expected db-query at argument 1, got boolean]

I'll wait your answers.

Thanks.

Walid · May 23, 2015

it should be like this

local database = dbConnect( "sqlite", "files.db" ); 
  
function makeDB () 
    dbExec(database, "CREATE TABLE IF NOT EXISTS accounts (  account TEXT,money TEXT)"); 
end 
addEventHandler("onResourceStart", resourceRoot, makeDB) 
  
function saveDataBase() 
    local playerAccount = getPlayerAccount ( source ); 
    local playerMoney   = getPlayerMoney ( source ); 
        if playerAccount and not isGuestAccount(playerAccount) then  
            local accountName = getAccountName(playerAccount)  
            if AccountExist ( accountName ) then  
                dbExec(database, "UPDATE accounts SET money = ? WHERE account = ?",playerMoney,accountName) 
            else 
            dbExec(database, "INSERT INTO accounts VALUES (? , ?) ",accountName,playerMoney) 
        end  
    end  
end 
addEventHandler("onPlayerQuit", getRootElement(), saveDataBase) 
  
  
function AccountExist ( AccountName ) 
    local result = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?", tostring (AccountName)),-1) 
        if ( type ( result ) == "table" and #result == 0 or not result ) then 
            return false 
        else 
        return true 
    end 
end

You need to use dbQuery (not dbExec) with dbPoll to get the result.

Enargy, · May 23, 2015

it should be like this

local database = dbConnect( "sqlite", "files.db" ); 
  
function makeDB () 
    dbExec(database, "CREATE TABLE IF NOT EXISTS accounts (  account TEXT,money TEXT)"); 
end 
addEventHandler("onResourceStart", resourceRoot, makeDB) 
  
function saveDataBase() 
    local playerAccount = getPlayerAccount ( source ); 
    local playerMoney   = getPlayerMoney ( source ); 
        if playerAccount and not isGuestAccount(playerAccount) then  
            local accountName = getAccountName(playerAccount)  
            if AccountExist ( accountName ) then  
                dbExec(database, "UPDATE accounts SET money = ? WHERE account = ?",playerMoney,accountName) 
            else 
            dbExec(database, "INSERT INTO accounts VALUES (? , ?) ",accountName,playerMoney) 
        end  
    end  
end 
addEventHandler("onPlayerQuit", getRootElement(), saveDataBase) 
  
  
function AccountExist ( AccountName ) 
    local result = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?", tostring (AccountName)),-1) 
        if ( type ( result ) == "table" and #result == 0 or not result ) then 
            return false 
        else 
        return true 
    end 
end

You need to use dbQuery (not dbExec) with dbPoll to get the result.

and when the time to enter an account as I do to get the values of the table?

dbQuery(database, "SELECT * FROM accounts") ??

Walid · May 23, 2015

and when the time to enter an account as I do to get the values of the table?

dbQuery(database, "SELECT * FROM accounts") ??

You must specify from which array you want to get the value so you need to use

"WHERE" like this

dbQuery(database, "SELECT * FROM accounts WHERE account = ?, --[[ account name here ]]")

Enargy, · May 23, 2015

and when the time to enter an account as I do to get the values of the table?

dbQuery(database, "SELECT * FROM accounts") ??

You must specify from which array you want to get the value so you need to use

"WHERE" like this
dbQuery(database, "SELECT * FROM accounts WHERE account = ?, --[[ account name here ]]") 

e.g, getting player money

function loadDataBase ( _, account ) 
    local moneyData= dbQuery(database, "SELECT * FROM accounts WHERE account = ?", getAccountName(account) ) 
   --- as you should get the value I have inserted is known as money. 
end 
addEventHandler ( "onPlayerLogin", root, loadDataBase );

Walid · May 23, 2015

Example

function getPlayerMoneyFromDb(accountName) 
local check = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?",tostring(accountName)), -1) 
  if type(check) == "table" and #check == 0 or not check then 
    return false 
  else 
    return tonumber(check[1]["money"]) 
  end 
end

onPlayerLogin

function loadDataBase ( _,cur) 
      local acountName = getAccountName(cur)  
    local moneyData = getPlayerMoneyFromDb(accountName) 
  
-- You code here 
  
end 
addEventHandler ( "onPlayerLogin", root, loadDataBase );

Enargy, · May 23, 2015

Example

function getPlayerMoneyFromDb(accountName) 
local check = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?",tostring(accountName)), -1) 
  if type(check) == "table" and #check == 0 or not check then 
    return false 
  else 
    return tonumber(check[1]["money"]) 
  end 
end

onPlayerLogin

function loadDataBase ( _,cur) 
      local acountName = getAccountName(cur)  
    local moneyData = getPlayerMoneyFromDb(accountName) 
  
-- You code here 
  
end 
addEventHandler ( "onPlayerLogin", root, loadDataBase );

I tried this but not working.

function loadDataBase ( _,cur) 
    local acountName = getAccountName(cur) 
    local moneyData = getPlayerMoneyFromDb(accountName) 
    outputChatBox(moneyData, source) 
  
end

Bad argument @ 'outputChatBox' [Expected string at argument 1, got boolean]

Walid · May 23, 2015

the problem is here

to fix it Replace money TEXT with money INTEGER

also you can fix it by replacing tonumber with tostring here

Enargy, · May 23, 2015

the problem is here

to fix it Replace money TEXT with money INTEGER

also you can fix it by replacing tonumber with tostring here

I've changed but it's the same.

Enargy, · May 23, 2015

the problem is here

to fix it Replace money TEXT with money INTEGER

also you can fix it by replacing tonumber with tostring here

I've changed but it's the same.

function makeDB () 
    dbExec(database, "CREATE TABLE IF NOT EXISTS accounts (account TEXT, money INTEGER)"); 
end 
  
function saveDataBase() 
    local playerAccount = getPlayerAccount ( source ); 
    local playerMoney   = getPlayerMoney ( source ); 
        if playerAccount and not isGuestAccount(playerAccount) then 
            local accountName = getAccountName(playerAccount) 
            if AccountExist ( accountName ) then 
                dbExec(database, "UPDATE accounts SET money = ? WHERE account = ?",playerMoney,accountName) 
            else 
            dbExec(database, "INSERT INTO accounts VALUES (? , ?) ",accountName,playerMoney) 
        end 
    end 
    --setPlayerMoney(source, 0) 
end 
  
  
function AccountExist ( AccountName ) 
    local result = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?", tostring (AccountName)),-1) 
        if ( type ( result ) == "table" and #result == 0 or not result ) then 
            return false 
        else 
        return true 
    end 
end 
  
  
function loadDataBase ( _,cur) 
    local acountName = getAccountName(cur) 
    local moneyData = getPlayerMoneyFromDb(accountName) 
    outputChatBox(moneyData, source) 
  
end 
  
  
function getPlayerMoneyFromDb(accountName) 
local check = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?",tostring(accountName)), -1) 
    if type(check) == "table" and #check == 0 or not check then 
        return false 
    else 
        return tostring(check[1]["money"]) 
    end 
end 
  
addEventHandler("onPlayerLogin", root, loadDataBase) 
--addEventHandler("onPlayerQuit", getRootElement(), saveDataBase) 
addEventHandler("onPlayerLogout", root, saveDataBase) 
addEventHandler("onResourceStart", resourceRoot, makeDB)

Walid · May 23, 2015

i gave you two solutions just use one of them.

Enargy, · May 23, 2015

I have tried adding outputs in the part where the values are saved and don't appear.

-- Never run this part. 
            if AccountExist ( accountName ) then 
                dbExec(database, "UPDATE accounts SET money = ? WHERE account = ?",playerMoney,accountName) 
                outputDebugString("database was updated.") 
            else 
            dbExec(database, "INSERT INTO accounts VALUES (? , ?) ",accountName,playerMoney) 
            outputDebugString("Some values inserted in database")            
        end

here might be the possible error

function AccountExist ( AccountName ) 
    local result = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?", tostring (AccountName)),-1) 
        if ( type ( result ) == "table" and #result == 0 or not result ) then 
            return false 
        else 
        return true 
    end 
end

EDIT: I tried your solutions but not work, return false.

Walid · May 23, 2015

the problem in your code here

it's accountName not acountName.

it should be like this

local database = dbConnect( "sqlite", "files.db" ); 
  
function makeDB () 
    dbExec(database, "CREATE TABLE IF NOT EXISTS accounts (account TEXT, money INTEGER)") 
end 
  
function saveDataBase() 
    local playerAccount = getPlayerAccount ( source ) 
    local playerMoney = getPlayerMoney ( source ) 
        if playerAccount and not isGuestAccount(playerAccount) then 
            local accountName = getAccountName(playerAccount) 
            if AccountExist ( accountName ) then 
                dbExec(database, "UPDATE accounts SET money = ? WHERE account = ?",tonumber(playerMoney),tostring(accountName)) 
            else 
                dbExec(database, "INSERT INTO accounts VALUES (? , ?) ",tostring(accountName),tonumber(playerMoney)) 
        end 
    end 
end 
  
  
function AccountExist ( AccountName ) 
    local result = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?", tostring (AccountName)),-1) 
        if ( type ( result ) == "table" and #result == 0 or not result ) then 
            return false 
        else 
        return true 
    end 
end 
  
function getPlayerMoneyFromDb(accName) 
    local check = dbPoll(dbQuery(database, "SELECT * FROM accounts WHERE account = ?",tostring(accName)), -1) 
  if type(check) == "table" and #check == 0 or not check then 
    return false 
  else 
    return tonumber(check[1]["money"]) 
  end 
end  
  
  
function loadDataBase (_,cur) 
    local accountName = getAccountName(cur) 
    local moneyData = getPlayerMoneyFromDb(accountName)  
    if moneyData then  
    outputChatBox(moneyData,source) 
    end  
end 
  
  
addEventHandler("onPlayerLogin", root, loadDataBase) 
addEventHandler("onPlayerQuit", root, saveDataBase) 
addEventHandler("onPlayerLogout", root, saveDataBase) 
addEventHandler("onResourceStart", resourceRoot, makeDB)

Enargy, · May 24, 2015

Still happening

Walid · May 24, 2015

Still happening

What i already tested it , working fine try to stop the resource delete the db then start the resource.

Enargy, · May 24, 2015

Still happening

What i already tested it , working fine try to stop the resource delete the db then start the resource.

I reconnected from my server and work finally

Thank you.

Walid · May 24, 2015

Still happening

What i already tested it , working fine try to stop the resource delete the db then start the resource.

I reconnected from my server and work finally

Thank you.

You are welcome.

Learning SQLite

Recommended Posts

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members