Livecode can work with any database. Livecode has integrated SQLite, but you can interface with any database that has an ODBC interface (almost all have it), here are the most popular:
- SQLite
- MySQL
- Oracle
- PostgreSQL
- Valentina DB
Databases are usually files outside our program that can be on the same PC on which our program runs or on a remote server. The databases contain lots of cataloged information. Thanks to the databases we can search for information, insert or delete data, all according to specific criteria that we set ourselves.
The language used by almost all databases is SQL. Generally the valid commands for a database also apply to all others.
Summarizing in a few words a database, we can say that it is a collection of tables, within each table the data are organized by rows and by columns.
Databases have advantages:
- quick to find the data you need among thousands of data
- take up little space
- they are optimized to do their job
- they are very fast (very, very, very fast)
- it is possible to cross the data of several tables easily
You could do the same things as a database with an array; but if you deal with a lot of data, I suggest you learn how to use databases.
Connection
A database can be an external running program, you talk with it and it answers you. Thisi is called client/server database. (MySQL, Oracle, PostgreSQL)A database can be simply just a file (SQLite).
To use a database you must first connect to a database (server or file), using the revOpenDatabase () function and store the connection identifier. For example with SQLite we will write:
put revOpenDatabase("sqlite", "./myDB.sqlite", , , , ) into connID
In this example the connID variable contains the connection identifier (it's just a number). You can establish multiple connections simultaneously.
Query
To retrieve information from the database, in jargon it is said to query, just use the command the revDataFromQuery function, specifying how to separate rows and columns: put "SELECT * FROM users ; " into tSQL put revDataFromQuery(tab,return,connID,tSQL) into tRecords
Execution
To execute commands that change in the database, or make more complex actions, just use the revExecuteSQL command, for example:put "INSERT into users (name,surname) VALUES ('Jack','Sparrow')" into tSQLrevExecuteSQL connID,tSQLSpeeding execution
Sometime you need to send many commands one after another, like inserting a lot of data, or updating a lot of data. In this case is much better to use transactions. Transaction is a list of task, the database reads them all and then decide the best strategy to do all task in the shortest time possible.For example:
put "BEGIN TRANSACTION;" into myQueryput "INSERT INTO myTable (name, surname) VALUES ('Mark', 'Red') ;" after myQueryput "INSERT INTO myTable (name, surname) VALUES ('John', 'Green') ;" after myQuery--..... a lot of data moreput "INSERT INTO myTable (name, surname) VALUES ('Elvis', 'Yellow') ;" after myQueryput "COMMIT;" after myQueryrevExecuteSQL connID,myQueryCheck column existence
SQLite is simple fast, portable and compact, but it misses some complex function like how to know if a table column exists. You can do it, but it needs to call PRAGMA.Here the example code:
put "PRAGMA table_info(myTable);" into tSQL put revDataFromQuery(comma,return,connID,tSQL) into tRecords if "muyColumn" is not among the items of tRecords then answer "Column myColumn doesn't exist"else answer "Column myColumn exists!"end ifClose the connection
When working on databases installed on client/server type, it is advisable to close the connection when you no longer work with the database, here is the code:revCloseDatabase connID
