Explanation: SQL

Hey everybody,

can someone explain me how to use SQL to save data & get data?

And is it possible to open this database?

You need to install this module: https://wiki.multitheftauto.com/wiki/Modules/MTA-MySQL on your server, then you need to add the module in your mtaserver.conf file, you will see get a message when you start your server notifying you that the module has started, after that you can refer to this wiki section for various functions: https://wiki.multitheftauto.com/wiki/Modules/MTA-MySQL#Handler_functions

I want SQL the build-in feature not MYSQL

EDIT: The explanathion to SQL on wiki isn´t understandable for me at all... Maybe you can explain better!

SQL build-in feature? SQL is not a build in feature, SQL means Structured Query Language, and you need to install the module I gave you the link to in order to use SQL in your resources. You also need a MySQL database installed on your PC/Server in order to store the data.

Lol. A friend of mine ( jessunit ) showed me and hey, nothing necessary to download. Simply script a function and tada use SQLSelect or smthng. Works fine!

Anyway another explanation would be nice

he means the SQL functions built into mta:

bool executeSQLCreateTable ( string tableName, string definition ) 

the examples on the wiki are a little confusing when u first start out.

SQL is a built-in feature - you can use the SQL functions to save/retrieve data efficiently. MySQL is only neccesary if you plan on using data across multiple server applications, i.e an external website or another MTA server.

The SQL function work with the SQL file named internal.db, in the server/mods/deathmatch directory. It can be viewed with the SQLite browser: http://sqlitebrowser.sourceforge.net/

He means SQLite.

There is two ways: Using executeSQLQuery in every query (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).

I use executeSQLQuery.

All you can do with SQLite is: creating a table , deleting a table , inserting a row with values, updating a row to set the values we inserted before , Select a row to retrieve the values , deleting a row .

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.

Sorry if it's not useful.

MY skype : JR10.cx10

If you want to ask about something.

EDIT: @ John_Michael:

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

It's registry.db

Nice post JR10, sorry i didnt have time earlier to post a detailed reply.. good job man.

/me gives JR10 a cookie for his hard work.

I just hope it's useful, and that I didn't make any mistakes.

Thanks alot this is the best explanation ive ever seen! they should use it in wiki!

Awesome.

No problem.