How to create and run an SQL query through programming?
An SQL query can be directly created via the query editor (see Creating a query in SQL code
). However, it may be useful to create an SQL query through programming.
To create an SQL query through programming:
- Declare a string variable where the SQL code of the query will be stored.
- Declare a variable of type data source. This variable will represent the query at runtime.
- Use HExecuteSQLQuery to execute the query.
- To read and retrieve the query result, use the standard read functions: HReadXXX, FOR EACH, ...
- Don't forget to free the query when it is no longer used (HFreeQuery).
If the query uses parameters coming from variables, use StringBuild
to build the string representing the SQL code. This tip is used in the example below.
SQLCode is string
DS is Data Source
SQLCode = [
SELECT CustomerName, City FROM CUSTOMERS
// List the customers living in France
SQLCode = StringBuild(SQLCode, "FRANCE")
IF HExecuteSQLQuery(DS, cntDatabBase, hQueryWithoutCorrection, SQLCode) THEN
// Loop through the result
FOR EACH DS
// Process the record read
Info("Error while running the query.")
This creation mode presents several drawbacks:
- no completion on the names of items.
- you must know and even master the SQL language.
- no assisted input of SQL code.
This page is also available for…