ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / SQL functions
  • Overview
  • How to manage a local database?
  • Available SQL functions
  • Example: Saving the data of an offline site locally
  • Steps
  • Retrieving the records found in the remote database
  • Retrieving the local data to update the remote database
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Accessing a database in local mode (SQLite)
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
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:
  • Chrome,
  • Safari,
  • Opera 11, ...
How to manage a local database?
To manage a local database:
  1. 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")
  2. Use SQLExec to run queries on the local database.
    Caution: The SQL queries are executed 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:
    • 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.
    If new queries are run in this procedure (to add records for example), you can:
    • 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.
Remarks:
  • 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:
SQLChangeConnectionModifies the current connection.
SQLCloseDeclares the end of the query execution and frees the memory resources allocated during the execution of the query.
SQLColumnReturns the characteristics of all the columns (or items):
  • for a given table.
  • for a given query.
SQLConnectConnects the current application to a database that must be interrogated via SQL.
SQLDisconnectCloses the current connection and frees the memory used by the connection.
SQLExecNames and runs an SQL query.
SQLFetchGoes to the next row (i.e next record) of the query result.
SQLGetColRetrieves the content of the specified column from the query result, for the current line.
SQLInfoInitializes the different SQL variables with information relative to the last query run.
SQLReqExistsChecks 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.

Steps

The steps are as follows:
  1. Connecting to the database
    The connection to the database is established with SQLConnect. For example:
    SQLConnect("","","", "APTCustomers", "Web SQL database")
  2. 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 );
    ]
    // Execute 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.
  3. 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)
    AJAXExécute(SaveData, EDT_ID, EDT_SUMMARY)
    END
    sQuery = [
    UPDATE Appointment
    SET Summary='%2'
    WHERE AppointmentID=%1
    ]
     
    // 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:
  1. Read the records and store the items as strings, in a server procedure.
    PROCÉDURE GetRec()
    FOR EACH MyFile1
    sList += [CR]+ MyFile1.Item1 + TAB + ...
    MyFile1.Item2 + TAB + MyFile1.Item3
    END
    RESULT sList
  2. Retrieve this list in browser code:
    // Retrieves the list
    sList = AJAXExécute(GetRec)
  3. 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:
  1. 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)
  2. 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:
    PROCÉDURE _SynchronizeDatabase(sQuery)
     
    // As long as there are appointments to synchronize
    WHILE SQLFetch(sQuery) = 0
    // Synchronizes the appointment
    AJAXExécute(_SynchronizeARemoteAppointment, SQLGetCol(sQuery, 1), SQLGetCol(sQuery, 2))
    END
    Remark: The update of remote database can be provoked during the re-connection to the server. During the reconnection to the server, the "Switch to online mode" event is run. This process is an optional process of the page.
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.
  • Validate.
  • Enter the code for updating the remote database.
Minimum version required
  • Version 16
Comments
Click [Add] to post a comment

Last update: 01/26/2023

Send a report | Local help