Name: revOpenDatabase

Type: function

Syntax: revOpenDatabase("mysql", <host> [: <port>], <databaseName>, [<userName>],[<password>],[<useSSL>], [<socket>], [<timeout>], [<autoReconnect>])

Syntax: revOpenDatabase("odbc", <host> [: <port>], <databaseName>, [<userName>],[<password>],[<cursorType>])

Syntax: revOpenDatabase("sqlite",filepath,[<sqliteOptions>])

Syntax: revOpenDatabase("postgresql", <host> [: <port>], <databaseName>, [<userName>],[<password>])

Syntax: revOpenDatabase("oracle", <host> [: <port>], <databaseName>, [<userName>],[<password>])

Summary: Connects to a MySQL, SQLite, PostgreSQL, ODBC or Oracle database.

Synonyms: revdb_connect

Introduced: 1.1

OS: mac,windows,linux,ios,android

Platforms: desktop,server,web,mobile

Security: disk,network

Example:
get revOpenDatabase("mysql", "www.example.com", "RatesDB", myUsr, myPass)

Example:
get revOpenDatabase("odbc", "BizFile", , "jenkins" ,the dbPassword of me, "emulated static")

Example:
put revOpenDatabase("sqlite", "mydb.sqlite") -- open with legacy binary mode and loadable extensions disabled
put revOpenDatabase("sqlite", "mydb.sqlite", "binary") -- open the connection in the 'new' binary mode
put revOpenDatabase("sqlite", "mydb.sqlite", "extensions") -- enable loadable extensions for this connection
put revOpenDatabase("sqlite", "mydb.sqlite", "binary,extensions") -- enable both 'new' binary mode and loadable extensions

Example:
get revOpenDatabase("mysql", "localhost", "dbName", myUsr, myPass, false, "/var/mysql.sock", 1, true)

Parameters:
host (string): A string specifying the IP address or domain name of the system hosting the database. For SQLite databases, the host should be the full path to the database file.
port: A number specifying the port number you want to connect to, and is used only for MySQL and PostgreSQL databases. If no port is specified, MySQL database connections use port 3306 and PostgreSQL database connections use port 5432.
databaseName (string): A string specifying the the name of the database to connect to. For SQLite databases, the database name should be left empty.
userName (string): A string specifying your authorized user name for the database (Some databases do not require a user name).
password (string): A string specifying the authentication password for the userName (Some databases do not require a password).
useSSL: (MySQL Only) A boolean specifying whether to use SSL to secure the socket connection when connecting to a MySQL database. If you use this feature in a standalone application, remember to select 'SSL  Encryption' from among the available 'script libraries' in the standalone application settings panel.
socket: (MySQL Only) A string specifying the socket or named pipe the driver should use to connect with.  If empty, the default for the host will be used.
timeout: (MySQL Only)  A number specifying the database read/write timeout in whole seconds.  Defaults to 20 seconds if empty or not a positive integer. If 0, the connection never times out.
autoReconnect: (MySQL Only) A boolean specifying if the driver should attempt to automatically reconnect to the database when the connection is lost. A reconnection attempt will be made the next time a command is executed.
cursorType (enum): (ODBC Only) A string specifying the type of cursor to use.
- "forward only": This is a cursor that can be scrolled forwards only. All ODBC drivers must support this type of cursor. Forward only cursors are usually faster than scrollable static cursors, but using revMoveToPreviousRecord, revMoveToFirstRecord or revMoveToLastRecord will fail. Forward only is the default cursor type, and is what was used in LiveCode 2.8.1 and earlier.
- "emulated static": This cursor has the same behavior as a static cursor, except it allows ODBC to emulate the cursor's behavior if the driver does not support it. ODBC can typically emulate static cursor behavior on Windows systems only.
- "static": This is a scrollable static cursor. Using this cursor it is possible to navigate backwards as well as forwards through record sets, and to move straight to the first or last record. Static cursors operate on an "offline" copy of the data, meaning that any changes to the data will not be detected by the cursor. Not all ODBC drivers support static cursors, for example FileMaker 6 does not.
sqliteOptions (enum): A comma delimited list contain one or me of the following (Note that the order of the items in the options parameter is not important):
- "extensions": Enable loadable extensions for the connection.
- "binary": Places binary data into the database verbatim (without LiveCode encoding).
databaseType (enum): A string specifying the database type to use. One of the following:
- "mysql"
- "oracle"
- "odbc"
- "postgresql"
- "valentina"
- "sqlite"
filename: (SQLite Only) A string specifying the path to the SQLite database.

Returns:
The <revOpenDatabase> function returns a database ID which can be used to refer to the database in other Database library commands and functions. The database ID is always an integer.
If the database is not successfully opened, the <revOpenDatabase> function returns an error message. The error message is never an integer, so you can check whether the connection was successful by checking whether the return value is an integer or not.

Description:
Use the <revOpenDatabase> function to start working with a database.

>*Note:*  To use a DSN to identify an ODBC database, use the DSN as the host, and leave the databaseName parameter empty.

>*Important:*  The revOpenDatabase function is part of the Database library. To ensure that the function works in a standalone application, you must include this custom library when you create your standalone. In the Inclusions section of the General screen of the Standalone Application Settings window, make sure the Database Support checkbox is checked and the database drivers you are using are selected in the list of database drivers.

The version of SQLite has been updated to 3.8.2.

The SQLite RTREE module is now available.

SQLite loadable extensions are now supported. To utilize loadable extensions, the 'extensions' option must be passed to the revOpenDatabase() call when creating the database connection.

Binary data can now be placed into SQLite databases verbatim (without the encoding that used to occur) - this means databases can be made to contain binary data which is compatible with other applications. To utilize this functionality, the 'binary' option must be passed to the revOpenDatabase() call when creating the database connection.

The SQLite revOpenDatabase() call no longer requires 5 arguments and only requires a minimum of 2.

Changes:
LiveCode 6.6

References: revSetDatabaseDriverPath (command), revExecuteSQL (command), revLicenseType (function), revDatabaseID (function), revDatabaseConnectResult (function), revDatabaseTableNames (function), revQueryIsAtStart (function)

Tags: database
