Level 2 · Intermediate: data, queries & reports

2.1HFSQL: WinDev's database

Goal: Understand HFSQL (Classic & Client/Server), manage data and the control centre: the data foundation of your management application.

Estimated time: 11 min · self-paced reading · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

HFSQL is the DBMS supplied with WinDev: included, with no additional server licence for local use, and perfectly integrated into the IDE.
Two modes to distinguish: Classic (data in files on a local disk or network share — standalone or small network) and Client/Server (a real server centralises access — multi-user, volumes, security, remote access).
The index (the “key”) is the most cost-effective optimisation; the engine also handles SQL, referential integrity, transactions, the journal and replication, and WinDev can connect to other databases.

Running mini-case — Yacine lays the database foundation for his application

Yacine, a WinDev developer at an SME trading electrical equipment in Oran, is starting the management application (customers, products, orders, invoices, stock). He must first decide what the data will rest on. At the beginning, only one person enters orders: he chooses HFSQL Classic, since the database is included and requires no additional server licence. But the boss soon announces three sales reps on the network and increasingly heavy queries: Yacine then switches to HFSQL Client/Server, a real server that centralises access. Above all he makes sure to index the most frequently searched columns (CodeProduit, NomClient), because, he notes, that is the most cost-effective optimisation. Finally, he sets up a daily backup: the trading company's data is his responsibility.

Self-contained case for this section: it returns in the course, diagram, script and 48h challenge of section 2.1.

1. WinDev's built-in DBMS

HFSQL is the DBMS (database management system) supplied with WinDev. It is a major asset: the database is included, with no additional server licence for local use, and perfectly integrated into the IDE. Yacine does not have to assemble a third-party engine for his management application.

2. Classic or Client/Server

(1) HFSQL Classic: data lives in files placed on the local disk or a network share; the application accesses them directly. Simple and free — ideal for standalone use or a small network. (2) HFSQL Client/Server: a real server is installed; applications send it queries and it manages access centrally. This is the multi-user, high-volume, security and remote-access solution. The analysis and WLangage code remain largely identical from one mode to the other.

3. Indexes and engine capabilities

HFSQL manages indexes (the “keys”) that greatly speed up searches and sorts: indexing a frequently searched column is the most cost-effective optimisation. It supports SQL, referential integrity, transactions (all or nothing), the journal and replication. And WinDev does not lock you in: it also connects to SQL Server, MySQL, Oracle and PostgreSQL via native or ODBC access.

Common misinterpretation: “HFSQL Classic and Client/Server are just a paid option; indexes are a comfort; and backups are the publisher's business”. No, three mistakes. Classic stores data in files on a local disk or network share, Client/Server entrusts access to a centralised server: these are two architectures, not two price plans. Indexes are the most cost-effective performance optimisation (searches and sorts). And backups are your responsibility, whatever the version: an unbacked-up database is a disaster waiting to happen. Yacine therefore indexes and backs up from the start.

Classic accesses files on disk or a network share; Client/Server goes through a server that centralises access.

Text version of the diagram (SEO / screen readers / translation): 1) Yacine, a WinDev developer at an SME trading electrical equipment in Oran, learns that HFSQL is the DBMS supplied with WinDev: included, with no additional server licence for local use. 2) He distinguishes the two modes: Classic (data is stored in files on a local disk or network share, accessed directly — standalone or small network) and Client/Server (a real server processes queries — multi-user, large volumes, security, remote access). 3) For his management application, he starts in Classic, then switches to Client/Server when three sales reps work on the network; he indexes CodeProduit and NomClient and sets up a daily backup. Lesson: the index is the most cost-effective optimisation and backups remain his responsibility.

🔎 Concrete example — 🔎 Concrete example — Classic or Client/Server?

The right mode depends on the number of users and the volume: Yacine chooses according to his trading company.

// HFSQL Classic: local files or on a network share
HOuvre("Produit")
HIndexe("Produit", "CodeProduit")      // most cost-effective optimisation

// HFSQL Client/Server: centralised server
HOpenConnection("CS_Negoce")
HChangeConnection(Produit, "CS_Negoce")

Reading   : Classic = standalone / small network; C/S = multi-user
Reflex    : index the searched columns + back up the data

Reading: Classic is enough for standalone use or a small network; Client/Server becomes necessary as soon as several users and large volumes arrive.

Lesson: the WLangage code and the analysis change little from one mode to the other; indexing and backups are the real habits to adopt.

Section vocabulary

HFSQL

The DBMS supplied with WinDev, integrated into the IDE and included with no additional server licence for local use.

Simple example: Yacine relies on HFSQL to store products, orders and invoices.

What it is not: an external engine to install and license separately.

Reflex: database included with WinDev → HFSQL.

HFSQL Classic

Mode where data consists of files on the local disk or a network share, accessed directly by the application.

Simple example: Yacine starts in Classic, alone in entering orders.

What it is not: a server centralising the queries of several workstations.

Reflex: standalone or small network → Classic.

HFSQL Client/Server

Mode where a real server manages data centrally; applications send it queries.

Simple example: Yacine switches to Client/Server with three sales reps on the network.

What it is not: files shared in a simple network folder.

Reflex: multi-user, volumes, remote access → Client/Server.

Index (key)

A structure that greatly speeds up searches and sorts on a column; indexing frequent columns is the most cost-effective optimisation.

Simple example: Yacine indexes CodeProduit and NomClient.

What it is not: a rarely searched column left without an index.

Reflex: speed up searches and sorts → an index.

Data backup

A safety copy of the database, the developer's responsibility whatever the version.

Simple example: Yacine schedules a daily backup of the trading company.

What it is not: relying only on the files being present on disk.

Reflex: protect the data → backups.

In practice — In practice — Choosing and using HFSQL

Determine your mode: Classic (standalone or very small network, maximum simplicity) or Client/Server (multi-user, volumes, security, remote access), like Yacine.
Create indexes on the columns you search or sort often (CodeProduit, NomClient) — this is the most cost-effective optimisation.
Use the engine's capabilities as needed: referential integrity, transactions (all or nothing), journal, replication.
Set up a data backup policy from the start — it is your responsibility, whatever the mode.

You know how to choose the right HFSQL mode, speed up access with indexes, use the engine's features and secure your data with backups.

Step-by-step script — set up the database like Yacine (4 min)

1) Open your analysis files (Produit, Client, Commande). 2) In Classic, open a file with HOuvre("Produit"); in Client/Server, connect via HOpenConnection then HChangeConnection. 3) Index the searched columns: HIndexe("Produit", "CodeProduit"). 4) Enable transactions for sensitive writes. 5) Schedule a daily backup of the database.

Keep this foundation: sections 2.2 (queries), 2.3 (reports) and 2.4 (traversal) build on the files and indexes created here.

Copy-paste template — my HFSQL foundation

Mode        : Classic (standalone/small network) OR Client/Server (multi-user)
Index       : on searched/sorted columns  (most cost-effective optimisation)
Engine      : SQL, referential integrity, transactions, journal, replication
Other DBs   : SQL Server, MySQL, Oracle, PostgreSQL (native or ODBC)
Backup      : daily, from the start (YOUR responsibility)
Rule        : switch to C/S when simultaneous users and volumes increase

Starting in Classic

Yacine starts in HFSQL Classic, alone in entering orders: maximum simplicity and no cost.

Moving to Client/Server

Three sales reps arrive on the network: Yacine centralises everything in HFSQL Client/Server.

Opening up to other databases

A customer imposes SQL Server: Yacine connects to it via native access without leaving WinDev.

Indexing the right columns

Yacine adds an index on CodeProduit: product searches become instantaneous.

✓ Best practices

Choose the mode according to users and volumes (Classic or Client/Server).
Index the columns that are frequently searched or sorted.
Set up a backup from the start.

✗ Common pitfalls

Believing that HFSQL Classic and Client/Server are merely a pricing option.
Neglecting indexes on heavily used columns.
Postponing backups — it is your responsibility.

Key takeaways

HFSQL is the DBMS supplied with WinDev (included, with no additional server licence locally).
Two modes: Classic (disk files or network share) and Client/Server (centralised server).
The index is the most cost-effective optimisation; the engine handles SQL, integrity, transactions, journal and replication.
WinDev also connects to SQL Server, MySQL, Oracle and PostgreSQL — and backups remain your responsibility.

48h challenge — apply it tomorrow

Like Yacine, open a file in your analysis and add an index on the column you search most often, then run a search again to measure the gain. Write one sentence explaining why you stay in Classic or move to Client/Server according to the number of users, and trigger a backup of the database.

Section exit checklist

I distinguish HFSQL Classic and Client/Server modes.
I know how to index frequently searched columns.
I understand that backing up the data is my responsibility.

Self-assessment 1-5: do I choose the right HFSQL mode for my context?Self-assessment 1-5: can I index the columns to optimise and back up my database?

Next : Query editor & SQL (V3) →

Frequently asked questions

When should I move from HFSQL Classic to Client/Server?

As soon as several intensive simultaneous users appear, along with growing slowness, large volumes, a need for fine-grained security or remote access. Good news: the analysis and WLangage code remain largely identical, so the switch does not call everything into question.

What is the best performance optimisation under HFSQL?

Index the columns you search or sort often: this is the most cost-effective optimisation, and it greatly speeds up searches and sorts. Next comes filtering on the database side (see 2.2) rather than loading the whole file.

Check your understanding

What is the difference between HFSQL Classic and Client/Server?

Additional resources (optional, to go further)

📘

HFSQL: the database (PC SOFT docs)

doc.pcsoft.frEN

Videos “HFSQL Classic & Client/Server”

YouTubeEN

Level 2 examFresh, harder questions to test what you've learned