[TUT] SQLite

[Ryancit2]

Hey guys.. I have made a database to save some accounts and then those accounts are listed inside a table.. And at end, that table is listed in a guiGridList..Everything is perfect till here but i wanna know following:

1. How can i outputChatBox to all the accounts listed there?

2. How can i check if one account is in that database/table or no?

[JR10]

If you're MySQL, then I will recommend that onResourceStart, you get all the accounts and store them in a table. That will allow easier and less consuming access.

I don't really get your first question but as for the second, dbQuery(callback, db, 'SELECT * FROM accounts WHERE name = ? LIMIT 1', name) should do. name is the name of the account.

[Ryancit2]

Ok i got second one and about first, i mean that how can i show some message to specific people who are in a table/sql..?

[JR10]

Depends on what identifies them in the table. Since we're talking about accounts, how do you identify a player's account? By knowing that, you can simply retrieve the account from the table, then loop through players trying to find the player with that account. If you're using the default MTA account system, then getAccountPlayer will get you the player with that account. If it's a totally custom accounts system, then only you would know how to get an account's player.

Something like:

dbQuery(callback, database, 'SELECT * FROM accounts') 
  
function callback(qh) 
 local result = dbPoll(qh, 0) 
 for index, row in pairs(result) do 
  local account_name = row.name 
  -- now find the player with that account name 
 end 
end 

[Ryancit2]

I am using default MTASA accounts system.. Will that code work?

[JR10]

This should work if the row for the account name is `name`:

dbQuery(callback, database, 'SELECT * FROM accounts') 
  
function callback(qh) 
 local result = dbPoll(qh, 0) 
 for index, row in pairs(result) do 
  local account_name = row.name 
  local player = getAccountPlayer(getAccount(account_name)) 
  if (isElement(player)) then 
   outputChatBox('Hi', player) 
  end 
 end 
end 

[Ryancit2]

Thanks alot.

[monday]

Hi, is it necessary to download something to use SQLite thing or it's somehow built in and all I have to do is to make the correct script?

[Anubhav]

It's built in.

[monday]

Nice, thanks:)

[Anubhav]

No problem.

[monday]

By the way, what is more efficient, having one database file with a lot of tables/columns or having a lot of small database files with only few tables/columns? Or it doesn't make any big difference?

[JR10]

I can't imagine a scenario where you'll want several databases. You'll probably never go beyond 50 tables, that's not "a lot".

[spoty]

hey i have a question can you see whats wrong with this i get this warning and it dont update the points into table after reconnect

script

dataName  = { 
    ["anterior"] = "Last Drift", 
    ["total"] = "Total Drift", 
    ["mejor"] = "Best Drift" 
} 
  
addEventHandler ( "onResourceStart", root, 
    function ( ) 
        -- creating connection and if true creates column if not exist 
        connection = dbConnect ( "sqlite" , "Driftpoint.db" ) 
        if ( connection ) then 
            dbQuery ( connection , "CREATE TABLE IF NOT EXISTS `drift` ( `data` TEXT, `account` TEXT )" ); 
            outputDebugString ( "connected" ); 
        else 
            outputDebugString ( "not connected" ); 
        end 
        -- creating connection and if true creates column if not exist 
    end 
); 
  
function saveDriftPoints ( ) 
    local account = getPlayerAccount ( source ); -- getting account 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
    
    local accName = getAccountName ( account ); -- getting name 
    
    local t = { } -- creating table for drift's data 
    for k, v in pairs ( dataName ) do 
        local data = getElementData ( source, v ); 
        if ( data ) then 
            t[v] = data; -- inserting values to table from ElementData 
        end 
    end 
    
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if exist row 
    if ( #check > 0 ) then 
        dbQuery ( connection, "UPDATE `drift` SET `data`=? WHERE `account`=?", toJSON ( t ), accName ); -- if yes updating 
    elseif ( #check == 0 ) then 
        dbQuery ( connection, "INSERT INTO `drift` ( `data`, `account` ) VALUES ( ?, ? )", toJSON ( t ), accName ); -- if not creating 
    end 
end 
addEventHandler ( "onPlayerQuit", root, saveDriftPoints ); 
  
function loadDriftPoints ( _, account ) 
    if ( isGuestAccount ( account ) ) then -- checking if it is valid account 
        return; 
    end 
    
    local accName = getAccountName ( account ); -- getting name 
    
    local check = dbPoll ( dbQuery ( connection, "SELECT `data` FROM `drift` WHERE `account`=?", accName ), -1 ); -- checking if row exist 
    if ( #check > 0 ) then -- if yes 
        local d = check[1]; 
        
        local t = fromJSON ( d["data"] ); 
        if ( type ( t ) == "table" ) then 
            for k, v in pairs ( t ) do 
                setElementData ( source, k, v ); -- setting ElementData from saved drift's table in table 
            end 
        end 
    end 
end 
addEventHandler ( "onPlayerLogin", root, loadDriftPoints ); 

this are the score when i quit

this are the loaded scores when i join the game and login

this are the score when i join and did 1 drift

[myonlake]

Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).

[spoty]

Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).

okay thnx for telling me was asking for couple days now i gonna try it out and see if it will work better

[Rexy]

dbExec(db, "CREATE TABLE IF NOT EXISTS `accountdata`(`id` INT NOT NULL AUTO_INCREMENT, `name` TEXT, PRIMARY KEY(id))") 
dbExec(db, "CREATE TABLE IF NOT EXISTS `inventory`(`id` INT NOT NULL AUTO_INCREMENT, `name` TEXT, PRIMARY KEY(id))") 

something wrong on this can u help.?

[JR10]

dbExec(db, "CREATE TABLE IF NOT EXISTS `accountdata`(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT)") 
dbExec(db, "CREATE TABLE IF NOT EXISTS `inventory`(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT)") 

[G-Stefan]

I 'used to use xml' before i heard about sql.After hearing about this type of database i tried to learn it from scripts and wiki examples but i couldnt.Then i found your post and it really helped me to learn and use sql,thanks for your awesome tutorial

[Backsage]

I never understood what is the purpose of dbExec. When you execute it, how will you know if it worked if it doesn't return anything? I guess one way would be through a script error. But I always use dbQuery because I want that query to return something, so that way I know it worked. Not sure what would be the purpose of using executeSQLQuery either.

On 4/27/2015 at 11:29, myonlake said:

Whenever you INSERT or CREATE something, you should just use dbExec instead of dbQuery. dbQuery returns a result everytime, and that's why you should use dbFree on your query. However, I suggest you use dbExec instead of dbQuery, you only need to know if it in theory went through. You would get errors if it didn't work, anyway (depends how complex you want to build this thing).

Oh, I didn't know that. Thx for that.

Edited June 30, 2017 by Backsage