[TUT] SQLite

[JR10]

Note: This is a tutorial about the old executeSQL* functions. For the recent and better db* functions, check the first reply here

SQLite:

There is two ways: Using executeSQLQuery and supplying the query string (The Query needs a certain string to define what to do That string can be found @ each SQLite function WIKI Page) or using the functions like (executeSQLCreateTable, executeSQLDelete).

Let's start by creating a table: (2 ways)

--executeSQLCreateTable(string tableName , string columns in table with the type) see below 
executeSQLCreateTable("tableName", "column1 TEXT,column2 NUMBER,column3 TEXT,column4 NUMBER") 

OR:

--executeSQLQuery(string theQuery) see below 
executeSQLQuery("CREATE TABLE IF NOT EXISTS tableName (column1 TEXT,column2 NUMBER,column3 TEXT,column4 NUMBER") 
  

If you take a look at executeSQLCreateTable wiki page @ the top you will find:

The executed SQL query is the following:

CREATE TABLE IF NOT EXISTS

That's what we used in executeSQLQuery

Deleting a table

executeSQLDropTable("tableName") 

OR

executeSQLQuery("DROP TABLE tableName") 

Inserting a row

--executeSQLQuery(string tableName, string values, [string columns]) 
executeSQLInsert("tableName", "text1", "1", "text2", "2") 
-- we inserted in the 4 columns we created with the table  row with the values: string text1 , number 1 , string text2 , number 2 

OR

executeSQLQuery("INSERT INTO tableName (column1,column2,column3,column4) VALUES ('text1','1','text2','2')") 
--also you can use question marks and then specify it 
executeSQLQuery("INSERT INTO tableName ((column1,column2,column3,column4) VALUES (?,?,?,?)", "text1", 1, "text2", 2) 

Updating a row

Conditions: condition is what specifies which row to update or select or delete.

--executeSQLUpdate(table name, set, conditions) 
executeSQLUpdate("tableName", "column1 = 'text1',column2 = '1',column3 = 'text2',column4 = '2'", "column1 = 'text1') 
  
  
  
  
  
  
  
  
  
  
 

Above third argument in executeSQLUpdate is the conditions

Meaning that it will find the row where column1 = "text1" and update it's values

OR

executeSQLQuery("UPDATE tableName SET column1 = ?,column2 = ?,column3 = ?,column4 = ? WHERE column1 = 'text1'", "text1", 1, "text2", 2) 

selecting a row

You can use "*" wild card to get all the data because you will have to specify what to select from the row, using the wild card will select all (ALL THE DATA IN THE ROW, not all the rows)

local SQLTable = executeSQLSelect("tableName", "*", "column1 = 'text1'") 
--now the SQLTable is a table containing the rows and the data in the rows like if you want the text1) 
--SQLTable[row].ColumnName 
--like :  
--SQLTable[1].column1 Will be "text1" 
--that's how select works 
  
  

OR

local SQLTable = executeSQLQuery("SELECT * FROM tableName WHERE column = ?", "text1") 
--And the same in the above 

Deleting a row

executeSQLDelete("tableName", "column1 = 'text1'") 

OR

executeSQLQuery("DELETE FROM tableName WHERE column1 = ?", "text1") 

That's it.

Read the arguments of each function ( using the WIKI ) before looking at the example.

Note: it's not internal.db , internal.db is for the accounts and for setAccountData.

It's registry.db

Note: I didn't make this at the time of posting, I did write it for someguy before.

viewtopic.php?f=91&t=34941&p=362581&hilit=sqlite+explanation#p362581.

[JR10]

SQLite it's similar to MySQL, but it doesn't require a SQL server like MySQL does.

I will explain the new db functions.

dbConnect 
dbExec 
dbQuery 
dbPoll 
dbFree 

dbConnect 

This function opens a connection to a database and returns an element that can be used with dbQuery. To disconnect use destroyElement.

So you must use dbConnect, to open a connection with the database (the .db file) which you can later use with dbQuery to execute a query.

dbConnect connects to a .db file, or create it if it doesn't exist.

The path can be the resource, another resource or the global databases folder.

Ex:

--In the same resource 
local connection = dbConnect ( "sqlite" , "newDB.db" ) 
  
--Another resource 
local connection = dbConnect ( "sqlite" , ":resourceName/newDB.db" ) 
  
--Global databases folder 
local connection = dbConnect ( "sqlite" , ":/newDB.db" ) 

And later you will use the variable connection with dbQuery.

You can destroy the connection with destroyElement.

dbQuery 

This function starts a database query using the supplied connection. Use the returned query handle with dbPoll to get the result, or dbFree if you don't want the result.

You use dbQuery to execute a query and it returns a query handler which can be used with dbPoll to get the result, or dbFree if you don't want it at all.

You can also use

dbExec 

If you want to execute a query and you don't want a result.

Ex:

The example is a continue to the above example.

local queryHandle = dbQuery ( connection , "SELECT * FROM someTable" ) 
  
--Another example 
local queryHandle = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS someTable (column1 TEXT,column2 TEXT)" ) 
  
--Another example 
local queryHandle = dbQuery ( connection , "INSERT INTO someTable (column1,column2) VALUES (?,?)", "text" , "text2" ) 

The question mark can be used to insert a value as an argument, without having to quote it or anything, I use them personally.

If you don't know what to type in the query, look in the old sqlite functions wiki page for examples.

dbPoll 

This function checks the progress of a database query.

table: Returns a table when the results are ready. This automatically frees the query handle, so you do not have to call dbFree.

When you use dbQuery it returns a query handle, in order to get the result you have to use dbPoll.

Ex:

--Without timeout setting. 
local queryResult = dbPoll ( queryHandle , -1 ) 
  
--With timeout setting. 
local queryResult = dbPoll ( queryHandle , 10 ) 

The second argument:

How many milliseconds to wait for a result. Use 0 for and instant response (which may return nil). Use -1 to wait until a result is ready. Note: A wait here will freeze the entire server just like the executeSQL* functions

dbFree just frees the result no more explaining needed.

dbExec executes a string, without any values returned.

Ex:

dbFree ( dbQuery ( connection , "UPDATE someTable SET column1 = ?" , "row1" ) ) 
--OR JUST (AS A REPLACEMENT) 
dbExec ( connection , UPDATE someTable SET column1 = ?" , "row1" ) 
  
 

viewtopic.php?f=91&t=37892#p390384

[Otto]

Nice tuto! I could understand everything, thanks.

PD: Your profile img.. is it an actor or the human version of the Heavy class from Team Fortress 2?

[GanJaRuleZ]

Nice tuto! I could understand everything, thanks.

PD: Your profile img.. is it an actor or the human version of the Heavy class from Team Fortress 2?

It's "The rock" from WWE , as far i know

ON : Nice topic , i was a bit confused with the db's but now i can understand them , thx

[JR10]

You're welcome.

[myonlake]

Liked it though someone should make a MySQL tutorial.

I know MySQL already, but it would be nice if others would find it interesting aswell as SQLite.

[JR10]

I've never used MySQL, perhaps I will learn it soon and post a tut.

[Otto]

MySQL is a programming language or what?

[Castillo]

http://en.wikipedia.org/wiki/MySQL

[Otto]

http://en.wikipedia.org/wiki/MySQL

Thank you!

[mjau]

nice tut but one question what is the easiest thing i can create with sqlite ? i wanna learn but making a gangsystem wich i want to will be t0o hard...

[JR10]

That's up to you, you should pick something easy to practise with.

[mjau]

Well yeah thats my question i dobnt know anything easy to create with sql

[JR10]

Make a simple saving system.

Check some tutorials on the web.

[CapY]

One question, how would I check does something exists in a database table, in some column - a row ( in my case, the account ) - do I do it with dbQuery or what?

I literally suck at MySQL stuff

function test ( name ) 
      local acc = dbQuery( exports.misc:getConnection ( "server" ), "SELECT * FROM user_list WHERE username = ?", name ) 
     if not acc then 
        -- bla bla 
     else  
        -- acc exists 
     end 
end 

So that basically would check does a 'name' data ( acc ) from a 'username' column in the 'user_list' table exists?

[Anderl]

One question, how would I check does something exists in a database table, in some column - a row ( in my case, the account ) - do I do it with dbQuery or what?

I literally suck at MySQL stuff

function test ( name ) 
      local acc = dbQuery( exports.misc:getConnection ( "server" ), "SELECT * FROM user_list WHERE username = ?", name ) 
     if not acc then 
        -- bla bla 
     else  
        -- acc exists 
     end 
end 

So that basically would check does a 'name' data ( acc ) from a 'username' column in the 'user_list' table exists?

Yes.

And returns a table with the values if a 'name' data exists, otherwise returns nothing[?].

[CapY]

The point is the 'name' data doesn't exists and it still returns me else condition.

[Anderl]

I think you must check that way:

if #acc == 0 then 

[CapY]

I think you must check that way:

if #acc == 0 then 

I have tried that already before your post - so, not really..

[Castillo]

function test ( name ) 
    local query = dbQuery ( exports.misc:getConnection ( "server" ), "SELECT * FROM user_list WHERE username = ?", name ) 
    local result, numrows, errmsg = dbPoll ( query, -1 ) 
    if ( type ( result ) == "table" and #result == 0 or not result ) then 
        -- does not exist 
    else 
        -- exist 
    end 
end 

[Callum]

function test ( name ) 
    local query = dbQuery ( exports.misc:getConnection ( "server" ), "SELECT * FROM user_list WHERE username = ?", name ) 
    local result, numrows, errmsg = dbPoll ( query, -1 ) 
    if ( type ( result ) == "table" and #result == 0 or not result ) then 
        -- does not exist 
    else 
        -- exist 
    end 
end 

If you're only expecting one result, you should append 'LIMIT 1' to the end of the query, so that SQL will stop looping the database once it has 1 result. This can majorly improve performance.

[Sparrow]

there is a way to delete all rows in a table?

like this:

local account = getAccountName(getPlayerAccount(player)) 
executeSQLQuery("DELETE * FROM tableName WHERE accountName ='"..tostring(account).."'") 

[Cadu12]

executeSQLQuery("DELETE * FROM tableName") -- It will delete all rows 

[myonlake]

Does MTA have an automatic mysql_real_escape_string function when querying? Just would like to know.

[Castillo]

As far as I know, is not required.