Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Placeholders
- Placeholders allow for query parameters to be safely executed, preventing common SQL injection methods.
- Parameters can be passed an an array or map (referred to as named named parameters).
- local identifier = 'license:abc123'
- local group = 'admin'
- MySQL.scalar('SELECT `username` FROM `users` WHERE `identifier` = ? AND `group` = ?', { identifier, group })
- -- Named placeholders (deprecated)
- MySQL.scalar('SELECT `username` FROM `users` WHERE `identifier` = @identifier AND `group` = @group', {
- group = group
- identifier = identifier
- })
- These are distinct from prepared statements which are handled by the MySQL server; you can use MySQL.prepare for more optimised and secure queries.
- Insert
- Inserts a new entry into the database and returns the insert id for the row, if valid.
- Promise
- local id = MySQL.insert.await('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
- identifier, firstName, lastName
- })
- print(id)
- Aliases
- MySQL.Sync.insert
- exports.ghmattimysql.executeSync
- exports.oxmysql.insert_async
- Callback
- MySQL.insert('INSERT INTO `users` (identifier, firstname, lastname) VALUES (?, ?, ?)', {
- identifier, firstName, lastName
- }, function(id)
- print(id)
- end)
- Aliases
- MySQL.Async.insert
- exports.ghmattimysql.execute
- exports.oxmysql.insert
- Prepare
- Prepare can be used to execute frequently called queries faster and accepts multiple sets of parameters to be used with a single query.
- Date will not return the datestring commonly used in FiveM
- TINYINT 1 and BIT will not return a boolean
- You can only use ? value placeholders, ?? column placeholders and named placeholders will throw an error
- Unlike rawExecute, the SELECT statement will return a column, row, or array of rows depending on the number of columns and rows selected.
- Promise
- local response = MySQL.prepare.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- })
- print(json.encode(response, { indent = true, sort_keys = true }))
- Aliases
- exports.oxmysql.prepare_async
- Callback
- MySQL.prepare('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- }, function(response)
- print(json.encode(response, { indent = true, sort_keys = true }))
- end)
- Aliases
- exports.oxmysql.prepare
- Query
- When selecting data, returns all matching rows and columns; otherwise, returns data like insertId, affectedRows, etc.
- Promise
- local response = MySQL.query.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- })
- if response then
- for i = 1, #response do
- local row = response[i]
- print(row.firstname, row.lastname)
- end
- end
- Aliases
- MySQL.Sync.fetchAll
- exports.ghmattimysql.execute
- exports.oxmysql.query_async
- Callback
- MySQL.query('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- }, function(response)
- if response then
- for i = 1, #response do
- local row = response[i]
- print(row.firstname, row.lastname)
- end
- end
- end)
- Aliases
- MySQL.Async.fetchAll
- exports.ghmattimysql.execute
- exports.oxmysql.query
- RawExecute
- rawExecute can be used to execute frequently called queries faster and accepts multiple sets of parameters to be used with a single query.
- Date will not return the datestring commonly used in FiveM
- TINYINT 1 and BIT will not return a boolean
- You can only use ? value placeholders, ?? column placeholders and named placeholders will throw an error
- Unlike prepare, the SELECT statement will always return an array of rows. When using SELECT, the return value will match query, single, or scalar depending on the number of columns and rows selected.
- Promise
- local response = MySQL.rawExecute.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- })
- print(json.encode(response, { indent = true, sort_keys = true }))
- Aliases
- exports.oxmysql.rawExecute_async
- Callback
- MySQL.rawExecute('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ?', {
- identifier
- }, function(response)
- print(json.encode(response, { indent = true, sort_keys = true }))
- end)
- Aliases
- exports.oxmysql.rawExecute
- Scalar
- Returns the first column for a single row.
- Promise
- local firstName = MySQL.scalar.await('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
- identifier
- })
- print(firstName)
- Aliases
- MySQL.Sync.fetchScalar
- exports.ghmattimysql.scalar
- exports.oxmysql.scalar_async
- Callback
- MySQL.scalar('SELECT `firstname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
- identifier
- }, function(firstName)
- print(firstName)
- end)
- Aliases
- MySQL.Async.fetchScalar
- exports.ghmattimysql.scalar
- exports.oxmysql.scalar
- Single
- Returns all selected columns for a single row.
- Promise
- local row = MySQL.single.await('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
- identifier
- })
- if not row then return end
- print(row.firstname, row.lastname)
- Aliases
- exports.oxmysql.single_async
- Callback
- MySQL.single('SELECT `firstname`, `lastname` FROM `users` WHERE `identifier` = ? LIMIT 1', {
- identifier
- }, function(row)
- if not row then return end
- print(row.firstname, row.lastname)
- end)
- Aliases
- exports.oxmysql.single
- Transaction
- A transaction executes multiple queries and commits them only if all succeed.
- If one fails, none of the queries are committed.
- The return value is a boolean, which is the result of the transaction.
- Specific format
- When using this format, you must pass an array containing sets of queries and parameters to the transaction method.
- In this case, your queries do not necessarily match and the values are unique to each query.
- -- You might rename "values" as "parameters" for mysql-async compatibility.
- local queries = {
- { query = 'INSERT INTO `test` (id) VALUES (?)', values = { 1 }},
- { query = 'INSERT INTO `test` (id, name) VALUES (?, ?)', values = { 2, 'bob' }},
- }
- -- You can also pass an array of arrays.
- local queries = {
- { 'INSERT INTO `test` (id) VALUES (?)', { 1 } },
- { 'INSERT INTO `test` (id, name) VALUES (?, ?)', { 2, 'bob' } },
- }
- Shared format
- When using this format, you must pass an array containing queries and a set containing shared parameters to the transaction method.
- In this case, your queries do not necessarily match and the values are unique to each query.
- -- You might rename "values" as "parameters" for mysql-async compatibility.
- local queries = {
- 'INSERT INTO `test` (id, name) VALUES (@someid, @somename)',
- 'SET `name` = @newname IN `test` WHERE `id` = @someid'
- }
- local values = {
- someid = 2,
- somename = 'John Doe',
- newname = 'John Notdoe'
- }
- Promise
- local success = MySQL.transaction.await(queries, values --[[leave nil for specific format]])
- print(success)
- Aliases
- MySQL.Sync.transaction
- exports.ghmattimysql.transaction
- exports.oxmysql.transaction_async
- Callback
- -- specific
- MySQL.transaction(queries, values, function(success)
- print(success)
- end)
- -- shared
- MySQL.transaction(queries, function(success)
- print(success)
- end)
- Aliases
- MySQL.Async.transaction
- exports.ghmattimysql.transaction
- exports.oxmysql.transaction
- Transaction Isolation Level
- This can be set through the convar mysql_transaction_isolation_level, and is an integer ranging from 1-4.
- The default value is 2.
- Convar Value Result
- 1 Repeatable Read
- 2 Read Committed
- 3 Read Uncommitted
- 4 Serializable
- Update
- Returns the number of rows affected by the query.
- Promise
- local affectedRows = MySQL.update.await('UPDATE users SET firstname = ? WHERE identifier = ?', {
- newName, identifier
- })
- print(affectedRows)
- Aliases
- MySQL.Sync.execute
- exports.ghmattimysql.executeSync
- exports.oxmysql.update_async
- Callback
- MySQL.update('UPDATE users SET firstname = ? WHERE identifier = ?', {
- newName, identifier
- }, function(affectedRows)
- print(affectedRows)
- end)
- Aliases
- MySQL.Async.execute
- exports.ghmattimysql.execute
- exports.oxmysql.update
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement