Basic's SQL

[IIYAMA]

This is my first try to create a SQL database.

  
local possibleData = { 
    "serial TEXT", 
    "playerName TEXT" 
} 
  
-- This is for creating the .db file? 
local resultOfExecuteSQLQuery = executeSQLQuery("CREATE TABLE IF NOT EXİSTS player ( " .. table.concat(possibleData,", ") .. ")") 
  
outputDebugString(tostring(resultOfExecuteSQLQuery)) 
  
  
addCommandHandler("test", 
function (player) 
    local account getPlayerAccount(player) 
    if not isGuestAccount (account) then 
        local accountName = getAccountName(account) 
        local serial = getPlayerSerial(player) 
        local playerName = getPlayerName(player) 
        -- todo -- 
    end 
end) 
  
  
  

Yet I do not understand how to add players.

[Anubhav]

Advice for you, use db* functions. They're better if you want to create files ( only for sqlite ). They can be found on wiki.

( dbExec. dbConnect ). This connects to internal.db or something, I realy dont remember ( executeSQLQuery ).

[IIYAMA]

Can you continuous my addCommandHandler example, so I know how I can use the account name as key for the sql table?

Edited May 19, 2015 by Guest

[WhoAmI]

Use dbExec for inserting/updating values into database.

Use dbQuery for selecting values from database.

Remember to free querys by using dbFree. If you are using dbPoll, you don't have to free them - dbPoll free them automatically.

Examples

-- conencting 
connect = dbConnect ( "sqlite", "somefile.db" ) 
  
-- creating tables 
dbExec ( connect, "CREATE TABLE IF NOT EXISTS `table` (`column1` INTEGER, `column2` TEXT)" ) 
  
-- inserting values 
dbExec ( connect, "INSERT INTO `table` SET `column1`=?, `column2`=?", 0, "test" ) 
    -- or 
dbExec ( connect, "INSERT INTO `table` ( `column1`, `column2` ) VALUES (?,?)", 0, "test" ) 
  
-- updating values 
dbExec ( connect, "UPDATE `table` SET `column1`=?", 1 ) 
  
-- selecting values and showing them 
local q = dbQuery ( connect, "SELECT `*` FROM `table`" ) 
for k, v in pairs ( dbPoll ( q, -1 ) ) do 
    print ( v["column1"], v["column2"] ) 
end 
  
  

Edited May 19, 2015 by Guest

[WhoAmI]

To create database you can create a file with '.db' extension and connect to it using

dbConnect ( "sqlite", "file.db" ) 

For rest you have example up.

[IIYAMA]

Is this how I can make accounts like with accountdata?

Or I do get problems with special characters in the players their names?

  
  
local connect = dbConnect ( "sqlite", "file.db" ) 
local possibleData = { 
    "serial TEXT", 
    "playerName TEXT" 
} 
  
  
addCommandHandler("test", 
function (player) 
    local account getPlayerAccount(player) 
    if not isGuestAccount (account) then 
        local accountName = getAccountName(account) 
        local serial = getPlayerSerial(player) 
        local playerName = getPlayerName(player) 
        dbExec ( connect, "CREATE TABLE IF NOT EXISTS `" .. accountName  .. "` ( " .. table.concat(possibleData,", ") .. ")" )         
        dbExec ( connect, "INSERT INTO `" .. accountName .. "` SET `serial`=?, `playerName`=?", serial, playerName ) 
    end 
end) 

[novo]

I would rather do it as follows:

  
local connect = dbConnect ( "sqlite", "file.db" ) 
local possibleData = { 
    "username TEXT", 
    "serial TEXT", 
    "playerName TEXT" 
} 
dbExec(connect, "CREATE TABLE IF NOT EXISTS `accounts` (" ..table.concat(possibleData,", ")..")")      
  
addCommandHandler("test", 
    function (player) 
        local account getPlayerAccount(player) 
        if not isGuestAccount (account) then 
            local accountName = getAccountName(account) 
            local serial = getPlayerSerial(player) 
            local playerName = getPlayerName(player) 
            -- 
            local query = dbExec(connect, "SELECT * FROM `accounts` WHERE `username` = ?", accountName) 
            local _, rows = dbPoll(query, -1) 
            if rows == 0 then -- in case an account's data row is not created yet 
                dbExec(connect, "INSERT INTO `accounts` SET `username` = ?", accountName) 
            end 
            dbExec(connect, "UPDATE `accounts` SET `serial`= ?, `playerName` = ? WHERE `username` = ?", serial, playerName, accountName) 
        end 
    end 
) 
  

Edited May 19, 2015 by Guest

[IIYAMA]

"UPDATE `accounts` SET `serial`= ?, `playerName` = ? WHERE `username` = ?" 

The "WHERE" must be the last thing in the string?

[novo]

As specified here and checked here, seems like.

Use the following code @second link.

  
CREATE TABLE IF NOT EXISTS `accounts` (username TEXT, serial TEXT, playerName TEXT); 
INSERT INTO `accounts` SET `username` = 'IIYAMA'; 
INSERT INTO `accounts` SET `username` = 'novo'; 
  
UPDATE `accounts` WHERE `username` = 'IIYAMA' SET `serial`= 'ABCD', `playerName` = 'name'; 
UPDATE `accounts` SET `serial`= 'ABCD', `playerName` = 'name' WHERE `username` = 'novo'; 
  

[Anubhav]

It's compulsory, I have tried that. The error popped up sadly. took a while figuring it but figured out.

[IIYAMA]

aha I understand that part now. (I am a fast learner)

Another question:

dbExec( connection, "INSERT INTO table_name VALUES (?,?,?)", "aaa", "bbb", 10 ) 
--(From wiki) 
  

Will I be able to add more than 3 values later?

[novo]

You can add as many values you wish, though the columns should be previously created.

[IIYAMA]

Will predefined columns give more performance? (while edit that column the next time)

The ones you can create with "CREATE TABLE IF NOT EXISTS".

[novo]

I'm afraid I'm unable to provide you an appropriate answer to your question, though you can add new columns through ALTER.

[IIYAMA]

Aha, great!

Another question, now I get the data based on the column username.

"UPDATE `accounts` SET `serial`= ?, `playerName` = ? WHERE `username` = ?" 

But can I also use the playerName or serial to find the account right?

"UPDATE `accounts` SET `serial`= ?, `username` = ? WHERE `playerName` = ?" 

Or am I limited to the key I first used to insert? Which is in this case the key username.

dbExec(connect, "INSERT INTO `accounts` SET `username` = ?", accountName) 

[novo]

Well, it depends on how frequently you're updating the accounts data - though I do not recommend you using any other way to find an account than the username itself. Technically you're able to do so but, in case of a shared account, an account might contain either the serial of another computer the user has connected through or whatever.

[IIYAMA]

Aha. I am planning to update it slowly, using a table which contains the same data as the database.

So I don't have to call the database to receive data from the players. Just only storage and only loading data when they join.

Just wondering, why do I have to index the result 1 time more before I can access the main data?

function loadData (player) 
    local account = getPlayerAccount (player) 
    if not isGuestAccount(account) then 
        local accountName = getAccountName(account)  
        if accountName then 
            local query = dbQuery(connection, "SELECT * FROM `accounts` WHERE `username` = ?", accountName) 
            if query and rows ~= 0 then 
                local result, rows = dbPoll(query, -1) 
                if result then 
                    for index,data in pairs(result[1]) do 
                        outputDebugString(tostring(index) .. " " .. tostring(data)) 
                    end 
                end 
            end 
        end 
    end      
end 
addCommandHandler("loadData",loadData) 

This part solved:

and this doesn't seems to work: (no errors/warnings) and returns true

  
  
-- the total string looks like this: -- 
"UPDATE `accounts` SET `location` = ?, `status` = ?, `weaponSkills` = ?, `serial` = ? WHERE `username` = ?" 
  
dbExec(connection, "UPDATE `accounts` SET " .. dbString .. " WHERE `username` = ?", unpack(dbArguments)) 

Edited May 19, 2015 by Guest

[Walid]

that's wrong

Example

function loadData (player) 
    local account = getPlayerAccount (player) 
    if not isGuestAccount(account) then 
        local accountName = getAccountName(account) 
        if accountName then 
            local query = dbPoll(dbQuery(connection, "SELECT * FROM accounts WHERE username = ?",tostring(accountName)), -1) 
                if type(query) ~= "table" or #query ~= 0 or query then 
               -- your code here  
            end  
        end 
    end     
end 
addCommandHandler("loadData",loadData) 

[IIYAMA]

Huh?

Why would you define a table under the name query?

And at line 7 you check if: it isn't a table or the variable query isn't empty or variable query.

Should be:

if type(result) == "table" and #result~= 0 then 

Yet I didn't notice any real mistakes in it, just extra checks.

Thx for that.

[Walid]

check this and tell me what's wrong here

[IIYAMA]

ah yea, mist that one. Yet not critical, since false can never be 0.

thx

[IIYAMA]

Also thank you

Novo and Anubhav for other explanations, examples and websites!

Storing and loading, it is all working now!

[Walid]

you are welcome more than that to make it easy for you

You can use sth like this

function loadData (player) 
    local account = getPlayerAccount (player) 
    if not isGuestAccount(account) then 
        local accountName = getAccountName(account) 
        if accountName then 
            if isAccountSaved(accountName) then  
               -- Update 
               else 
               -- Insert 
            end 
        end 
    end     
end 
addCommandHandler("loadData",loadData) 

 

 

-- check if the account name is already exsit

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

 

-- get player data from db

function getPlayerDataFromDB(accountName) 
    local data = dbPoll(dbQuery(connection, "SELECT * FROM accounts WHERE username = ?", tostring (accountName)), -1) 
        if type(data) ~= "table" or #data ~= 0 or data then 
            return data 
        else 
    return false 
  end  
end 

[IIYAMA]

thank you very much!

[Walid]

thank you very much!

You are welcome