|
- Declaration syntax: Remarks
- SQL code of query
- HFSQL context
SQL query (WLanguage type) In french: Requêtes SQL (type WLangage)
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.
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 query>: SQL code corresponding to the SQL query associated with the variable. Remarks Declaration syntax: Remarks - The query must be defined as soon as it is declared. You cannot declare an SQL Query variable then type the corresponding SQL code after several code lines.
- 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.
- 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)
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.
This page is also available for…
|
|
|
| |
| Exemplo Consulta endereço pelo CEP |
|
| Endereco_ConsultaCEP(gCEP)
gsCEP is string = gCEP
gsCEP = Replace(gsCEP,".","")
gsCEP = Replace(gsCEP,"-","")
gsCEP = Replace(gsCEP," ","")
arrResultado is array of string
qyrSql is SQL Query = [
SELECT
GPU_25_Enderecos.Logradouro AS Logradouro,
GPU_25_Enderecos.Bairro AS Bairro,
GPU_25_Enderecos.Cidade AS Cidade,
GPU_25_Enderecos.UF AS UF
FROM
GPU_25_Enderecos
WHERE
GPU_25_Enderecos.CEP = {ParamCEP}
]
qyrSql.ParamCEP = gsCEP
IF HExecuteQuery(qyrSql,hQueryDefault) = True THEN
FOR EACH qyrSql
IF HFound(qyrSql) THEN
Add(arrResultado,qyrSql.Logradouro)
Add(arrResultado,qyrSql.Bairro)
Add(arrResultado,qyrSql.Cidade)
Add(arrResultado,qyrSql.UF)
END
END
END
RESULT arrResultado |
|
|
|
| |
| |
| |
|
| PRIMEIRO EXEMPLO SQL QUERY |
|
| sql is SQL Query =
[
SELECT
cliente.clienteid,cliente.nome,
FROM cliente
ORDER BY cliente.nome
]
HExecuteQuery(_sql)
FOR EACH _sql
TableAddLine(TABLE_clientes,_sql.nome,_sql.clienteid)
END |
|
|
|
| |
| |
| |
|
| | // ANTES DE ALTERARMOS O CODIGO, VAMOS ELIMINAR OS DADOS DA TABELA
// BEFORE WE ALTER THE CODE, LET'S DELETE THE TABLE DATA
// AVANT DE MODIFIER LE CODE, SUPPRIMONS LES DONNÉES DE TABLE
TableDeleteAll(TABLE_CLIENTES)
// VAMOS AGORA COLOCAR UMA CONDIÇAO PROCURANDO UM NOME
// LET'S PUT A CONDITION LOOKING FOR A NAME NOW
// Mettons une condition à la recherche d'un nom maintenant
_SQL is SQL Query =
[
SELECT
cliente.clienteid,cliente.nome
FROM
cliente
WHERE cliente.nome = {parametro_nome}
ORDER BY cliente.nome
]
// VAMOS PERGUNTAR SE NOME ESTÁ EM BRANCO, SE FOR, DEIXAR NULL
// LET'S ASK IF NAME IS BLANK, IF SO, LEAVE NULL
// DEMANDONS SI LE NOM EST BLANC, SI CELA, LAISSER NUL
IF EDT_NOME="" THEN
_SQL.parametro_nome=Null
ELSE
_SQL.parametro_nome=EDT_NOME
END
HExecuteQuery(_SQL)
FOR EACH _SQL
TableAddLine(TABLE_CLIENTES,_SQL.clienteid,_SQL.nome)
END
|
|
|
|
| |
| |
| |
|
| EXEMPLO SQL QUERY PARAMETRO |
|
| EXEMPLO SQL QUERY
https://youtu.be/83za4WHGn2g
EXEMPLO SQL QUERY PARAMETRO
https://youtu.be/fmFgHHwzcVA
|
|
|
|
| |
| |
| |
| |
| |
| |
| | |
| |