How do I Create and set up a Database?
This lesson describes a number of aspects relating to creating, setting up, accessing and securing a database in a LiveCode Hosting account. Examples are provided that demonstrate how to connect to the newly created database using LiveCode from within an application. A brief coverage of security measures are given that demonstrate the use of access control to the database.
Log into cPanel
The first step is to log in to your hosting account using cPanel. cPanel provides access to and control of various settings and properties of the account, but this lesson looks exclusively at database management.
Note: If you are unsure of how to log into your hosting account, you will find a "Hosting" tab in your LiveCode account down the lefthand navigation, which contains a login link and instructions. If you do not see this tab, then you do not have an account with us, this can be purchased here:
Once logged into cPanel it is possible to create a MySQL database. Scroll down to the Databases panel and select MySQL Databases (1) as shown above.
Create a Database
A new screen opens that prompts for the name of a new database. After specifying the name, select Create Database (1).
Create a User
The next screen prompts you to create a user for your new database. Enter the new user name (1) and the password (2), (3), then select Create User. Steps (2) and (3) ensure that the password is spelt correctly. The password strength indicator determines how secure the chosen password is.
Add a User to the Database
Once the users has been created, select Go Back (1). This opens a new screen from where the user can be assigned to the database that was created earlier.
Assign the User to the Database
Scroll to the section Add User to Database. This allows you to assign the user to the database. The database name and the user name are prefixed with your account user name.
Select both the user (1) and the database (2) that have just been created and select Add (3).
Connect to the Database
Now that the database is set up and a users has been assigned, it is possible to connect to the database from within an application. The connection can be established using a single line of LiveCode:
put revOpenDatabase("mysql", "yourusername.livecodehosting.com", "yourusername_mydatabase", "yourusername_myuser", "yourpasswordhere") into tDatabaseID
The first parameter specifies the database type. LiveCode Hosting uses MySQL databases, which means that type has to be set to mysql. A full list of the other supported database types can be found in the documentation set that accompanies LiveCode.
The second parameter specifies the host. This is the internet protocol (IP) address or domain name of the server hosting the database. If the host is set to localhost or is left blank, the command assumes that the database is running on the local machine.
The third parameter is the database name. In this case, the one that was just created.
The fourth parameter is the user name. In this case, the one that was just assigned to the database.
The sixth parameter is the password. In this case, the one that was just created for the new user.
Note: More detailed documentation for revOpenDatabase can be found here.
Enabling Remote Database Access
By default, you can only access your database from a script on the server. If possible you should always connect to the database in this way. If your desktop or mobile app needs to access the database it should connect to a script on the server. This script can then sanitise the SQL request and prevent your end users running arbitrary SQL requests.
However, it is possible to connect to the database from anywhere, providing the username and password are available. This can be desirable in a test environment and, as well as enabling global access, you can restrict access to a specified group of IP addresses. Such restrictions can be controlled from cPanel.
Log into cPanel and select Remote MySQL (1)
Managing Access Hosts
A new screen is opened that provides control over who is to have access to the LiveCode Hosting database.
By default the access host is set to your server's IP address. This entry should be left. It is then possible to add specific IP addresses that are allowed to access the database. Enter the domain or IP address in the host field (2) and select Add Host (3). It is possible to add an arbitrary number of hosts to the list. To revoke an IP address's access simply click the "Remove" button (1).
You can also add a wildcard '%' sign either as the whole IP address or part of the IP address. This wild card indicates that there are no restrictions on the location from where the database can be accessed. For example just entering '%' in the Host box would enable unrestricted access.