Contents

Queries

A query is a statement or a command you send to the database to access and manipulate it or the data inside of it. In JC-MP, there are several ways which you can use to execute a query, we'll explore each in details.

SQL:Execute

This is the easiest way as it doesn't return anything, it only sends a query to execute. You can't bind parameters to it. It's useful for cases like creating/dropping a table.

Note: UNIQUE means that steamID must have a unique value, no two records can store the same steamID. You can also read about VARCHAR here. 'IF EXISTS' and 'IF NOT EXISTS' are optional clauses, they suppress the error that would normally result if the table does (CREATE TABLE) or does not (DROP TABLE) exist.

SQL:Command

You can use a command to execute a query where it needs be bound with parameters, but you don't want any returned values. It's useful in cases of inserting and updating, where you need to bind parameters but don't need any returned values.

Note: ? represents a parameter, you then must use command:Bind to bind the parameter.

A quote from Philpax on the forum: Binding is far superior to using string formatting. Please do not use string formatting under any circumstances. Binding has the SQL engine automatically insert the parameters properly without relying on poor escaping mechanisms or other problematic systems. SQL injection is the result of using string formatting and/or concatenation to form your statement. Please, I beg of you: bind your parameters, do not use string formatting!

SQL:Query

You use this when you need to bind parameters and need to return a value. This is useful for cases like selecting from a table.

Syntax:

object SQL:Query(string)

Example:

-- local result = SQL:Query('SELECT * FROM tableName'):Execute()-- local result = SQL:Query('SELECT column1, column2 FROM tableName'):Execute()-- local query = SQL:Query('SELECT column1, column2 FROM tableName WHERE column1 = ?')-- local query = SQL:Query('SELECT column1, column2 FROM tableName WHERE column1 = ? AND column2 = ?')-- query:Bind(1, 'value1')-- query:Bind(1, 'value2')-- local result = query:Execute()-- result[row].columnlocal result = SQL:Query('SELECT * FROM players'):Execute()-- Since we don't need to bind anything, we can call Execute() right away which will return the resultlocal query = SQL:Query('SELECT money, modelID FROM players WHERE steamID = ? LIMIT 1')
query:Bind(1, steam_id)local result = query:Execute()local money = result[1].money -- 1 is the row, money is the name of the column

Note: * is the wild card character, it means we want to select all the columns in the table. Appending LIMIT 1 to the end of our query means that we only want it to return one row/record. That's the use of LIMIT, it limits the returned data to a certain number of records. You should use LIMIT whenever possible, so that it will stop looping the database once it has 1 result. This can majorly improve performance.

SQL:Transaction

A transaction is a unit of work that is performed against a database. If a transaction is successful, all of the modifications made to the database during the transaction are committed. If a transaction encounters errors and must be canceled, then all of the data modifications are rolled back