Jump to content

Explanation: SQL


Maurize

Recommended Posts

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.

Link to comment

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/

Link to comment

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

Edited by Guest
Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...