Manual Review Required

Please finish this page using the corresponding Old Wiki article. Go to Contribution guidelines for more information.

---

This function opens a connection to a database and returns an element that can be used with dbQuery. To disconnect use destroyElement.

Note

Connecting and disconnecting many times can have a performance impact on the server. For optimal performance it is recommended that you use dbConnect only once when the resource starts, and share the connection element with the whole script.

Note

In MySQL 8.0 and later, you can choose between caching_sha2_password , sha256_password , or mysql_native_password for password management. If you opt for caching_sha2_password or sha256_password , SSL is not mandatory but recommended for secure authentication. If you choose mysql_native_password , you can set it as the default authentication plugin: In the mysqld.cnf configuration file, by adding or modifying the following line: default-authentication-plugin=mysql_native_password Using SQL queries (e.g., ALTER USER). In PHPMyAdmin (via the user settings page). Please note that starting with MySQL 9.0, mysql_native_password will no longer be available, and only caching_sha2_password and sha256_password will be supported.

Note

Under certain platforms, for example on Unix-based OSes like Linux, using this function could fail with a debug warning containing "[Could not connect]" accompanied by a prior debug error explaining the problem. In that case you should check the Server Manual to see if you have missed any recommended (best-effort) steps for server set-up.

OOP Syntax Help! I don't understand this!

• Constructor: Connection(...)

Syntax

element dbConnect ( string databaseType, string host, [ string username = "", string password = "", string options = "" ] )

Required Arguments

• databaseType: The type of database. This can be either sqlite or mysql
• host: Host address e.g. host=127.0.0.1

Optional Arguments

NOTE: When using optional arguments, you might need to supply all arguments before the one you wish to use.

• username (default: ""): Usually required for MySQL, ignored by SQLite
• password (default: ""): Usually required for MySQL, ignored by SQLite
• options (default: ""): MISSING_PARAM_DESC

Returns

• element: value

Returns a database connection element unless there are problems, in which case it return false .

Code Examples

Example 1 Example 2 Example 3 Example 4 Example 5 Example 6 Example 7 Example 8

shared

This example opens a connection to a SQLite database file in the current resource

test_db = dbConnect( "sqlite", "file.db" )

shared

This example opens a connection to a SQLite database file in another resource

test_db = dbConnect( "sqlite", ":resname/file.db" )

shared

This example opens a connection to a SQLite database file in the global databases directory

test_db = dbConnect( "sqlite", ":/file.db" )

shared

This example opens a connection to a SQLite database file in a sub directory of the global databases directory

test_db = dbConnect( "sqlite", ":/example/sub/dir/file.db" )

shared

This example opens a connection to a MySQL database called 'frank' at server ip 1.2.3.4 using utf8 character set and allows the connection to be shared. Note that changing the database or other connection dependent settings affect all connections that are shared.

test_db = dbConnect( "mysql", "dbname=frank;host=1.2.3.4;charset=utf8", "username", "password", "share=1" )

shared

This example opens a connection to a SQLite database is disallows sharing of the connection

test_db = dbConnect( "sqlite", "file.db", "", "", "share=0" )

shared

This example output debug message, if the connection with SQLite database was established or not

test_db = dbConnect( "sqlite", "file.db" )

if test_db then
    outputDebugString( "Connection with database was successfully established." )
else
    outputDebugString( "Connection with database couldn't be established." )
end

shared

The folowing example shows how you could approach a common resource for database operations with exported functions (queryandexecute):

function connect()
    DBConnection = dbConnect( "mysql", "dbname=DBNAME;host=HOST;charset=utf8", "USERNAME", "PASSWORD" )
    if (not DBConnection) then
        outputDebugString("Error: Failed to establish connection to the MySQL database server")
    else
        outputDebugString("Success: Connected to the MySQL database server")
    end
end

addEventHandler("onResourceStart",resourceRoot, connect)

function query(...)
    local queryHandle = dbQuery(DBConnection, ...)
    if (not queryHandle) then
        return nil
    end
    local rows = dbPoll(queryHandle, -1)
    return rows
end

function execute(...)
    local queryHandle = dbQuery(DBConnection, ...)
    local result, numRows = dbPoll(queryHandle, -1)
    return numRows
end

function getDBConnection()
    return DBConnection
end

See Also

Database Functions

• dbConnect
• dbExec
• dbFree
• dbPoll
• dbPrepareString
• dbQuery
• executeSQLQuery

Reference

• Scripting Functions