SQL Light System

[Atton]

How to I go about updating and getting data using sql.

  
local connection = dbConnect("sqlite","data.db") 
  
  
function makeTable (Name) 
    local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS data (name TEXT , cash TEXT , x TEXT ,y TEXT ,z TEXT)" ) 
end 
  
function mT (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    local acc = getAccountName(account) 
    local x, y, z = getElementPosition(thePlayer) 
    local cash = getPlayerMoney(thePlayer) 
        local queryHandle = dbQuery ( connection , "INSERT INTO data (name,cash,x,y,z) VALUES(?,?,?,?,?)",tostring(acc),tostring(cash),tostring(x),tostring(y),tostring(z)) 
        outputChatBox(tostring(acc)..tostring(tonumber(cash))..tostring(x)..tostring(y)..tostring(z)) 
end 
addCommandHandler("box",mT) 

[Karuzo]

Use dbExec for INSERT and UPDATE .

To get the data use dbPoll on an dbQuery.

[Atton]

Tried did not work to well.

  
  
local connection = dbConnect("sqlite","data.db") 
  
  
addEventHandler( 'onResourceStart', resourceRoot, 
function () 
    local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS 'data' (name TEXT , cash TEXT , x TEXT ,y TEXT ,z TEXT)" ) 
end) 
  
     
function toxin (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    local acc = getAccountName(account) 
    local x, y, z = getElementPosition(thePlayer) 
    local cash = getPlayerMoney(thePlayer) 
--      local queryHandle = dbQuery ( connection , "INSERT INTO data (name,cash,x,y,z) VALUES(?,?,?,?,?)",tostring(acc),tostring(cash),tostring(x),tostring(y),tostring(z)) 
        outputChatBox(tostring(acc)..tostring(tonumber(cash))..tostring(x)..tostring(y)..tostring(z)) 
        local stat = true 
    if stat then 
        local tox = dbExec(connection,"UPDATE 'data' SET name = ?, cash = ?, x = ?, y = ?, z = ?",tostring(acc),tostring(cash),tostring(x),tostring(y),tostring(z)) 
        outputChatBox(tostring(tox).."1") 
    else 
        local tox = dbExec( connection , "INSERT INTO data (name,cash,x,y,z) VALUES(?,?,?,?,?)",tostring(acc),tostring(cash),tostring(x),tostring(y),tostring(z)) 
        outputChatBox(tostring(tox).."2") 
    end 
end 
addCommandHandler("doit",toxin) 
  
function getD () 
    local qh = dbExec( connection, "UPDATE * FROM data" ) 
    return gh 
end 
  
function pullData () 
local dtable = dbPoll(getD(),0) 
data = tostring(dtable) 
--for a,b in pairs (dtable) do 
--  outputChatBox(tostring(b)) 
    outputChatBox(data) 
--  end 
end 
addCommandHandler("pullData",pullData) 
  

[Noki]

local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS 'data' (name TEXT , cash TEXT , x TEXT ,y TEXT ,z TEXT)" )[/lua] 
  
You should use dbExec or executeSQLQuery for creating tables. 
  
Also, you're using apostrophes instead of a back tick (`). I recommend you to use back ticks. 
 

Edited April 24, 2014 by Guest

[Atton]

 local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS 'data' (name TEXT , cash TEXT , x TEXT ,y TEXT ,z TEXT)" )[/lua]

You should use dbExec or executeSQLQuery for creating tables.

Also, you're using apostrophes instead of a back tick (`). I recommend you to use back ticks.

That really is not what I am looking for I want to figure out how to pull data.

I tried with exacuteSQL but it is missing a value for a connection.

[TAPL]

addEventHandler("onResourceStart", resourceRoot, 
function () 
    connection = dbConnect("sqlite", "data.db") 
    dbExec(connection, "CREATE TABLE IF NOT EXISTS data (name TEXT, cash TEXT, x TEXT ,y TEXT ,z TEXT)") 
end) 
  
function toxin (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    if account and not isGuestAccount(account) then 
        local acc = getAccountName(account) 
        local x, y, z = getElementPosition(thePlayer) 
        local cash = getPlayerMoney(thePlayer) 
        outputChatBox(tostring(acc)..tostring(tonumber(cash))..tostring(x)..tostring(y)..tostring(z)) 
        local result = dbPoll(dbQuery(connection, "SELECT name FROM data WHERE name = ?", acc), -1) 
        if type(result) == "table" and #result ~= 0 then 
            local tox = dbExec(connection,"UPDATE data SET cash = ?, x = ?, y = ?, z = ? WHERE name = ?", tostring(cash), tostring(x), tostring(y), tostring(z), tostring(acc)) 
            outputChatBox(tostring(tox).."1") 
        else 
            local tox = dbExec(connection, "INSERT INTO data VALUES(?, ?, ?, ?, ?)", tostring(acc), tostring(cash), tostring(x), tostring(y), tostring(z)) 
            outputChatBox(tostring(tox).."2") 
        end 
    end 
end 
addCommandHandler("doit", toxin) 
  
function pullData (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    if account and not isGuestAccount(account) then 
        local acc = getAccountName(account) 
        local result = dbPoll(dbQuery(connection, "SELECT * FROM data WHERE name = ?", acc), -1) 
        for a,b in pairs (result[1]) do 
            outputChatBox(a..": "..b) 
        end 
    end 
end 
addCommandHandler("pullData", pullData) 

[Atton]

addEventHandler("onResourceStart", resourceRoot, 
function () 
    connection = dbConnect("sqlite", "data.db") 
    dbExec(connection, "CREATE TABLE IF NOT EXISTS data (name TEXT, cash TEXT, x TEXT ,y TEXT ,z TEXT)") 
end) 
  
function toxin (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    if account and not isGuestAccount(account) then 
        local acc = getAccountName(account) 
        local x, y, z = getElementPosition(thePlayer) 
        local cash = getPlayerMoney(thePlayer) 
        outputChatBox(tostring(acc)..tostring(tonumber(cash))..tostring(x)..tostring(y)..tostring(z)) 
        local result = dbPoll(dbQuery(connection, "SELECT name FROM data WHERE name = ?", acc), -1) 
        if type(result) == "table" and #result ~= 0 then 
            local tox = dbExec(connection,"UPDATE data SET cash = ?, x = ?, y = ?, z = ? WHERE name = ?", tostring(cash), tostring(x), tostring(y), tostring(z), tostring(acc)) 
            outputChatBox(tostring(tox).."1") 
        else 
            local tox = dbExec(connection, "INSERT INTO data VALUES(?, ?, ?, ?, ?)", tostring(acc), tostring(cash), tostring(x), tostring(y), tostring(z)) 
            outputChatBox(tostring(tox).."2") 
        end 
    end 
end 
addCommandHandler("doit", toxin) 
  
function pullData (thePlayer) 
    local account = getPlayerAccount(thePlayer) 
    if account and not isGuestAccount(account) then 
        local acc = getAccountName(account) 
        local result = dbPoll(dbQuery(connection, "SELECT * FROM data WHERE name = ?", acc), -1) 
        for a,b in pairs (result[1]) do 
            outputChatBox(a..": "..b) 
        end 
    end 
end 
addCommandHandler("pullData", pullData) 

Thank you for your help.