PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
This content has been translated automatically. Click here to view the French version.
  • 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:
    // Connexion à une base de données navigateur nommée "BaseLocale"
    // La base de données est créée si elle n'existe pas
    SQLConnect("BaseLocale", "", "", "", "Web SQL database")
  2. Use SQLExec to run queries on the local database.
    Caution: The SQL queries are executed asynchronously. The syntax of the SQLExec function therefore 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 made with the SQLConnect function. For example:
    SQLConnect("","","", "RDVClients", "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:
    sRequête is string
    // Code de création de la table
    sRequête = [
    CREATE TABLE IF NOT EXISTS "RendezVous"
    ("IDRendezVous"  INTEGER PRIMARY KEY , "DateHeureDebut" VARCHAR(16) ,
    "Client" VARCHAR(100) , "Adresse" VARCHAR(200) , "Resume" LONGVARCHAR );
    ]
    // Exécution de la requête
    SQLExec(sRequête, "REQ_CREATION", _cbRequete)

    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:
    sRequête is string
    // Le navigateur est connecté
    IF BrowserIsConnected() = True THEN
    // Enregistre dans la base de données distante (serveur)
    AJAXExécute(SauveDonnées, SAI_ID, SAI_RESUME)
    END
    sRequête = [
    UPDATE RendezVous
    SET Resume='%2'
    where IDRendezVous=%1
    ]
     
    // Dans tous les cas, enregistre dans la base de données locale
    SQLExec(StringBuild(sRequête, gnIDRDVNav,SAI_RESUME), "REQ_SAUVEGARDE", _cbRequete)

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 RécupèreEnreg()
    FOR EACH MonFichier1
    sListe += [CR] + MonFichier1.Rubrique1 + TAB + ...
    MonFichier1.Rubrique2 + TAB + MonFichier1.Rubrique3
    END
    RETURN sListe
  2. Retrieve this list in browser code:
    // Récupère la liste
    sListe = AJAXExécute(RécupèreEnreg)
  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:
    // Exécute la requête qui sélectionne les rendez-vous de la journée
    sRequête is string
    sRequête = StringBuild("SELECT IDRendezVous,Resume FROM RENDEZVOUS " + ...
    "WHERE DateHeureDebut LIKE '%1%' ORDER BY DateHeureDebut ASC;", Today())
    // Lance la requête
    SQLExec(sRequête, "REQ_SYNCHRONISERDV", _cbRequete)
  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 _SynchroniseBase(sRequête)
     
    // Tant qu'il existe des rendez-vous à synchroniser
    WHILE SQLFetch(sRequête) = 0
    // Synchronise le rendez-vous
    AJAXExécute(_SynchroniseUnRendezvousDistant, SQLGetCol(sRequête, 1), SQLGetCol(sRequête, 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 event "Switch to online mode" 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