SQL query (WLanguage type)

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

This content has been translated automatically. Click here to view the French version.

Help / WLanguage / WLanguage syntax / Declaring variables

Declaration syntax: Remarks
SQL code of the query
Using [% %] in SQL queries
HFSQL context

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQL query (WLanguage type)

In french: Requêtes SQL (type WLanguage)

The "SQL query" type is used to write an SQL query in the WLanguage code. The SQL query is defined and declared. Then, it can be run by HExecuteQuery or HExecuteSQLQuery.

Using the "SQL query" type presents several benefits:

Automatic coloring of SQL code in the editor,
Assisted input of SQL code,
Assisted input on the result items and query parameters,
Compilation errors:
- when an error occurs in the SQL code
- when using an output item that does not exist.
Data binding on the query if it is declared as global to a window,
Automatic freeing at the end of variable scope.

Example

qryFlightsStat is SQL Query =
[
	SELECT * FROM  Flights
	WHERE Flights.DepartureAirportID = {ParamDepartureAirportID}
	AND Flights.ArrivalAirportID = {ParamArrivalAirportID}
]
qryFlightsStat.ParamDepartureAirportID = 12
qryFlightsStat.ParamArrivalAirportID = 3

HExecuteQuery(qryFlightsStat)
FOR EACH qryFlightsStat
	...
END

Syntax

Declaring an SQL query Hide the details

<Name SQL query> is SQL Query =
[
<SQL code of query>
]

<Name SQL query>:

Name of variable corresponding to the SQL query.

<SQL code of the query>:

SQL code corresponding to the SQL query associated with the variable.
New in version 2025
This SQL code can manipulate a variable of type SQL Request.
Note: This feature is only available from version 2025 Update 1.

Remarks

Declaration syntax: Remarks

The query must be defined as soon as it is declared. It is not possible to declare a variable of type SQL Query and then enter the corresponding SQL code after a few lines of code.
When declaring SQL Query variables, you can launch the query editor wizard to write the SQL code of the query. Simply select "SQL query (wizard)".
The query editor wizard opens and allows you to enter the options of your query. Once you go through the steps of the wizard, the SQL code of the query is included in the WLanguage code.
The data files used in the FROM of the query must be declared in the project analysis. They cannot come from data sources initialized by an alias, external declaration or other request.
The query cannot be built with string concatenations. To build a query by concatenating strings, use HExecuteSQLQuery.
The SQL query is not run during its declaration. It must be run by HExecuteQuery or HExecuteSQLQuery.
When the query is declared and run, it can be used by all HFSQL functions for handling queries.

New in version 2025
When the SQL query is declared, it can be used in another variable of type SQL Query. For example:

reqVols is SQL Query =
[
	SELECT * FROM  Flights
	WHERE Flights.COUNTRY = "FRANCE"
]
qryFlightsStat is SQL Query =
[
	SELECT * FROM ReqVols
	WHERE Flights.DepartureAirportID = {ParamDepartureAirportID}
	AND Flights.ArrivalAirportID = {ParamArrivalAirportID}
]
qryFlightsStat.ParamDepartureAirportID = 12
qryFlightsStat.ParamArrivalAirportID = 3

HExecuteQuery(qryFlightsStat)
FOR EACH qryFlightsStat
	...
END

Note: This feature is only available from version 2025 Update 1.

When the SQL query is declared and run, it can be used in another SQL query by using the name of corresponding variable:

MyInterrogation is SQL Query = 
[
	SELECT * FROM CUSTOMER
]
HExecuteQuery(MyInterrogation)

OtherInterrogation is string = "SELECT * FROM %1"
OtherInterrogation = StringBuild(OtherInterrogation, MyInterrogation.Name)

QryWithMyInterrogation is Data Source
HExecuteSQLQuery(QryWithMyInterrogation, OtherInterrogation)

SQL code of the query

This SQL code typed benefits from the syntactic coloring. The SQL keywords are colored (see the example).
Completion: Completion is available:
- for the SQL keywords,
- for the items and the data files,
- for the output items of query.
  The assisted SQL input
Compilation errors: In the event of an error in the query's SQL code, a compilation error is generated (as with WLanguage code).
Error of SQL code detected in input
Like for an SQL query typed in the query editor, you can:
- type comments by writing the "--" or "//" character in front of them.
- type WLanguage functions. The WLanguage functions must be prefixed by "WL.". Assisted input is available.
- ...

Using [% %] in SQL queries

SQL queries support the new syntax:

[% variable_name %]

At runtime, the variable name between "%" characters will be replaced with the value of this variable.

Example:

IDOfCustomerToFind is int = xxx
// Search for all the customer orders
AllOrders is SQL Query = [
	SELECT * FROM Order WHERE CustomerID = [%IDOfCustomerToFind%]
	]
HExecuteSQLQuery(AllOrders)
FOR EACH AllOrders
	// a customer order
END

Remarks:

Parameters are evaluated only once, when the query is declared.
All executions of the query (calls to HExecuteSQLQuery) will therefore use the value the variable held at the time the query was declared, even if the value changes between two calls to HExecuteSQLQuery.

To pass parameters to the query at each call:

you can use an HFSQL query parameter "{<parameter>}".

You can also use another SQL query or an "alias" data source containing the alias of a file and described by an attribute such as <description="file">. Example:

CustomerSelection is Data Source <description="Customer">
// Search for all the customers in the selection for which a condition is true
FilterCustomer is SQL Query = [
		SELECT * FROM [%CustomerSelection%] WHERE ...
	]
HExecuteSQLQuery(FilterCustomer)
FOR EACH FilterCustomer
	// Customers in the selection that are filtered by the query
END

HFSQL context

An SQL query is defined in the current HFSQL context. An SQL query cannot exist in a window with independent HFSQL context.
An SQL query can be defined as a global variable. In this case, you must pay attention to the HFSQL contexts.