- How to manage a local database?
- Available SQL functions
- Example: Saving the data of an offline site locally
- The steps
- Retrieving the records found in the remote database
- Retrieving the local data to update the remote database
Accessing a database in local mode (SQLite)
Available only with this kind of connection
WEBDEV allows a site to create and access a database created by the browser on the computer of the Web user in browser code.
This features gives you the ability to enter data in disconnected mode and to transmit this data to the server as soon as the connection to Internet is restored.
Caution: This feature is available for some browsers only:
- Opera 11, ...
How to manage a local database?
To manage a local database:
- Use SQLConnect in browser code to connect to the local database. For example:
// Connection to a browser database named "LocalDatabase"
// The database is created if it does not exist
SQLConnect("LocalDatabase", "", "", "", "Web SQL database")
- Use SQLExec to run queries on the local database.
Caution: The SQL queries are run asynchronously. Therefore, the syntax of SQLExec uses a specific procedure. This procedure is started at the end of real query execution (regardless of the query result). This browser procedure is used to:
If new queries are run in this procedure (to add records for example), you can:
- check the proper execution of the query. SQLInfo is automatically run during the call to the procedure. Therefore, all the SQL variables are positioned. If an error occurred, SQL.Error will differ from "00000". The error message is returned by the SQL.MesError variable.
- browse the query result.
- use the same procedure: the parameter of this procedure is used to find out the query currently run.
- use a different browser procedure to test the result of these new queries.
- During the exit from the browser procedure, the values returned by SQLInfo are restored. If these values have been modified in the browser procedure, they are overwritten.
- To find out the SQL commands that can be used, see the documentation about the "Web SQL databases".
Available SQL functions
The following SQL functions are available in browser code:
|SQLChangeConnection||Modifies the current connection.|
|SQLClose||Declares the end of the query execution and frees the memory resources allocated during the execution of the query.|
|SQLColumn||Returns the characteristics of all the columns (or items):|
- for a given table.
- for a given query.
|SQLConnect||Connects the current application to a database that must be interrogated via SQL.|
|SQLDisconnect||Closes the current connection and frees the memory used by the connection.|
|SQLExec||Names and runs an SQL query.|
|SQLFetch||Goes to the next row (i.e next record) of the query result.|
|SQLGetCol||Retrieves the content of the specified column from the query result, for the current line.|
|SQLInfo||Initializes the different SQL variables with information relative to the last query run.|
|SQLReqExists||Checks the existence of a query.|
Example: Saving the data of an offline site locally
WEBDEV allows you to implement offline sites
. If this type of site must save data during the disconnection period, you have the ability to use a local database.
The steps are as follows:
- Connecting to the database
The connection to the database is established by SQLConnect. For example:
SQLConnect("","","", "APTCustomers", "Web SQL database")
- Creating the local database
The local database must be created before it can be used. To do so, all you have to do is use a CREATE TABLE query. This query must be run by SQLExec. For example:
sQuery is string
// Code for creating the table
sQuery = [
CREATE TABLE IF NOT EXISTS "Appointments"
("AppointmentID" INTEGER PRIMARY KEY , "StartDateTime" VARCHAR(16) ,
"Customer" VARCHAR(100) , "Address" VARCHAR(200) , "Summary" LONGVARCHAR );
// Run the query
SQLExec(sQuery, "QRY_CREATION", _cbQuery)
When the "QRY_CREATION" query is completed, the _cbQuery procedure is run.
In this example, this procedure is used to manage all the queries run and to perform an appropriate process after the query.
The creation query must be used once, at the beginning of the day for example. For example, the database can be created only if a specific parameter ("First" for example) is not passed to the pages.
- Accessing the local or remote data
In this example, the site can be used in online mode or in offline mode. You must be able to access the local database (in offline mode) or to the remote database (in online mode).
All the accesses (creation, modification, deletion, ...) to the local database must be performed via the SQLExec procedure.
The accesses to the remote database can always be performed via the Hxxx functions. However, you must: For example:
sQuery is string
// The browser is connected
IF BrowserIsConnected() = True THEN
// Saves in the remote database (server)
AJAXExecute(SaveData, EDT_ID, EDT_SUMMARY)
sQuery = [
// In any case, save in the local database
SQLExec(StringBuild(sQuery, gnBrwAPTID, EDT_SUMMARY), "QRY_BACKUP", _cbQuery)
Retrieving the records found in the remote database
To retrieve records from the remote database (server) into the local database (to initialize it for example), you must:
- Read the records and store the items as strings, in a server procedure.
FOR EACH MyFile1
sList += [CR]+ MyFile1.Item1 + TAB + ...
MyFile1.Item2 + TAB + MyFile1.Item3
- Retrieve this list in browser code:
// Retrieves the list
sList = AJAXExecute(GetRec)
- Browse the string and extract the information with ExtractString. Therefore, the records can be easily added with an addition query (INSERT).
Retrieving the local data to update the remote database
Similarly, the data can be retrieved from the local database to update the remote database. Simply:
- Run a query locally to retrieve the records. For example, to retrieve the appointments of the day:
// Runs the query that selects the appointments of the day
sQuery is string
sQuery = StringBuild("SELECT AppointmentID,Summary FROM APPOINTMENT " + ...
"WHERE StartDateTime LIKE '%1%' ORDER BY StartDateTime ASC;", Today())
// Starts the query
SQLExec(sQuery, "QRY_SYNCHRONIZEAPT", _cbQuery)
- In the check procedure started by SQLExec, simply run a procedure for updating the remote database via AjaxExecute. In our example, the browser procedure named __SynchonizeDatabase is started:
Remark: The update of remote database can be provoked during the re-connection to the server. During the reconnection to the server, the event "Switch to online mode" is run. This process is an optional process of the page.
// As long as there are appointments to synchronize
WHILE SQLFetch(sQuery) = 0
// Synchronizes the appointment
AJAXExecute(_SynchronizeARemoteAppointment, SQLGetCol(sQuery, 1), SQLGetCol(sQuery, 2))
To display it in the code editor, you must:
- Display the code of the page.
- Click "Add other events to xxx", at the bottom of the code window, below the last event. All the optional events available for the page are displayed.
- Select the "Switch to online mode" event.
- Enter the code for updating the remote database.