Database

HappinessMP natively supports MySQL X and SQLite.

Quick Reference

Name	Type	Description
Connect	Server	Connects to the database server.
Close	Server	Closes the database connection.
Execute	Server	Executes a SQL statement with given arguments.
ExecuteAsync	Server	Queues a SQL statement and reports the result to a callback.
Select	Server	Retrieves data based on a SQL query and arguments.
SelectAsync	Server	Queues a SQL query and reports the result to a callback.
Insert	Server	Inserts data into a table and returns the insert ID.
InsertAsync	Server	Queues an insert and reports the insert ID to a callback.

info

A connection belongs to the resource that opened it, and each resource has at most one. Calling Connect while a connection is already open raises a script error, and Close without an open connection does the same.

---

Server Functions

Connect

Connects to the database server.

Database.Connect(int type, string url)

info

The MySQL implementation is based on the X DevAPI. A MySQL server with X plugin is therefore required.

Example:

Lua

-- MySQL
Database.Connect(0, "mysqlx://user:password@host:port/database")

-- SQLite
Database.Connect(1, "database.db")

Squirrel

// MySQL
Database.Connect(0, "mysqlx://user:password@host:port/database");

// SQLite
Database.Connect(1, "database.db");

---

Close

Closes the database connection.

Database.Close()

---

Execute

This function executes a SQL statement using the provided arguments. It returns the number of rows affected by the execution.

Synchronous (blocking):
int affectedRows = Database.Execute(string statement, list params)

Asynchronous (non-blocking):
Database.ExecuteAsync(string statement, list params, [optional] function callback)

Example:

Lua

-- in client script:
Events.Subscribe("chatCommand", function(command)
    if command == "/register" then
        Events.CallRemote("registerPlayer", { Player.GetRockstarID() })
    end
end)

-- in server script:
Events.Subscribe("registerPlayer", function(rid)
    -- sync:
    local affectedRows = Database.Execute("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", { rid, 1000, 0 })

    -- async:
    Database.ExecuteAsync("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", { rid, 1000, 0 }, function(affectedRows)
        Console.Log(affectedRows)
    end)
end, true)

Squirrel

// in client script:
Events.Subscribe("chatCommand", function(command) {
    if (command == "/register") {
        Events.CallRemote("registerPlayer", [ Player.GetRockstarID() ]);
    }
});

// in server script:
Events.Subscribe("registerPlayer", function(rid) {
    // sync:
    local affectedRows = Database.Execute("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", [ rid, 1000, 0 ]);

    // async:
    Database.ExecuteAsync("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", [ rid, 1000, 0 ], function(affectedRows) {
        Console.Log(affectedRows);
    });
}, true);

---

Select

This function executes a SQL statement with the given arguments. It returns the resulting data as a table.

Synchronous (blocking):
list result = Database.Select(string statement, list params)

Asynchronous (non-blocking):
Database.SelectAsync(string statement, list params, [optional] function callback)

Example:

Lua

-- in client script:
Events.Subscribe("chatCommand", function(command)
    if command == "/login" then
        Events.CallRemote("loginPlayer", { Player.GetRockstarID() })
    end
end)

-- in server script:
Events.Subscribe("loginPlayer", function(rid)
    -- sync:
    local result = Database.Select("SELECT * FROM users WHERE rockstarid = ?", { rid })

    if #result > 0 then
        Console.Log("Player exist (money: " .. result[1]["money"] .. ", admin: " .. result[1]["admin"] .. ")")
    end

    -- async:
    Database.SelectAsync("SELECT * FROM users WHERE rockstarid = ?", { rid }, function(result)
        if #result > 0 then
            Console.Log("Player exist (money: " .. result[1]["money"] .. ", admin: " .. result[1]["admin"] .. ")")
        end
    end)
end, true)

Squirrel

// in client script:
Events.Subscribe("chatCommand", function(command) {
    if (command == "/login") {
        Events.CallRemote("loginPlayer", [ Player.GetRockstarID() ]);
    }
});

// in server script:
Events.Subscribe("loginPlayer", function(rid) {
    // sync:
    local result = Database.Select("SELECT * FROM users WHERE rockstarid = ?", [ rid ]);

    if (result.len() > 0) {
        Console.Log("Player exist (money: " + result[0]["money"] + ", admin: " + result[0]["admin"] + ")");
    }

    // async:
    Database.SelectAsync("SELECT * FROM users WHERE rockstarid = ?", [ rid ], function(result) {
        if (result.len() > 0) {
            Console.Log("Player exist (money: " + result[0]["money"] + ", admin: " + result[0]["admin"] + ")");
        }
    });
}, true);

---

Insert

This function inserts data into a table using the provided SQL statement and arguments. It returns the ID of the newly inserted row.

Synchronous (blocking):
int insertId = Database.Insert(string statement, list params)

Asynchronous (non-blocking):
Database.InsertAsync(string statement, list params, [optional] function callback)

warning

Needs an auto-incremented primary key to work.

Example:

Lua

-- in client script:
Events.Subscribe("chatCommand", function(command)
    if command == "/register" then
        Events.CallRemote("registerPlayer", { Player.GetRockstarID() })
    end
end)

-- in server script:
Events.Subscribe("registerPlayer", function(rid)
    -- sync:
    local insertId = Database.Insert("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", { rid, 1000, 0 })

    -- async:
    Database.InsertAsync("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", { rid, 1000, 0 }, function(insertId)
        Console.Log(insertId)
    end)
end, true)

Squirrel

// in client script:
Events.Subscribe("chatCommand", function(command) {
    if (command == "/register") {
        Events.CallRemote("registerPlayer", [ Player.GetRockstarID() ]);
    }
});

// in server script:
Events.Subscribe("registerPlayer", function(rid) {
    // sync:
    local insertId = Database.Insert("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", [ rid, 1000, 0 ]);

    // async:
    Database.InsertAsync("INSERT INTO users (rockstarid, money, admin) VALUES (?, ?, ?)", [ rid, 1000, 0 ], function(insertId) {
        Console.Log(insertId);
    });
}, true);