ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Types of HFSQL variables
  • Handling a query or a view through programming
  • Using a data source in an SQL query
  • Properties associated with the Data Source variables
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
A Data source variable is used to describe a temporary data source (query, view, alias, ... ).
All the operations that can be performed on a view (or on a query) can be performed on a Data source variable associated with a view (or with a query).
To describe a data source, you must:
  1. Declare a "Data source" variable.
  2. Initialize the data source (HExecuteSQLQuery or HCreateView).
    The data source is automatically freed at the end of the process where this data source is declared.
    Remark: The data source can also be initialized by HDeclareExternal. In this case, the data source allows you to handle a data file by using the description of this file, independently of the analysis.
Remarks:
  • When closing the application (or the process where the data source was declared), the data source will automatically be destroyed.
  • A data source is always global to the HFSQL context in which it was declared.
  • A data source may have an internal name other than the variable name, for example, to allow it to be used in an array. In the project description window ("Compilation" tab), you can define the "Naming of data sources" option.
    PHP Automatic naming with references is not available.
// Declare a data source
MyDataSource is Data Source
MyDataSource1, MyDataSource2 are Data Sources
// Initialize a Data Source variable
MyQuery is Data Source
// MyQuery is associated with a query
// Initialize the MyQuery variable
HExecuteSQLQuery(MyQuery, "SELECT NAME FROM CUSTOMER")
// Initialize a Data Source variable
MyView is Data Source
// MyView is associated with a view
// Initialize the MyView variable
HCreateView(MyView, CUSTOMER, "*", "NAME,CITY", "NAME]='A'", hViewDefault)
// Delete a data source
MyQuery is Data Source
HExecuteSQLQuery(MyQuery, "SELECT NAME FROM CUSTOMER")
// Destroy the data source associated with the MyQuery variable
HCancelDeclaration(MyQuery)
HExecuteSQLQuery(MyQuery, "SELECT NAME FROM SUPPLIER")
// Delete a data source
MyView is Data Source
// MyView is associated with a view
// Initialize the MyView variable
HCreateView(MyView, CUSTOMER, "*", "NAME,CITY", "NAME]='A'", hViewDefault)
// Destroy the data source associated with the MyView variable
HDeleteView(MyView)
HCreateView(MyView, CUSTOMER, "*", "NAME,FIRSTNAME", "NAME]='C'", hViewDefault)
Syntax

Declaring one or more data sources Hide the details

<Name of variable> is Data Source
<Name of variable 1>, <Name of variable 2> are Data Sources
<Variable name>:
Name of "Data Source" variable to declare.
Remark: The a and an keywords are optional: they provide better readability.

Declaring a data source by associating it with a data file or with a query Hide the details

<Name of variable> is Data Source <description=File or query name>
<Variable name>:
Name of "Data Source" variable to declare.
<File or query name>:
Name of data file or query whose description must be associated with the data source. If this parameter is specified, the completion will propose the different items of the associated data source.
data source

Initializing a "Data source" variable (query or view) Hide the details

To initialize a "Data Source" variable, use the following function:
  • HExecuteSQLQuery if the variable is associated with a query.
  • HCreateView if the variable is associated with a view.
  • HDeclareExternalif the variable must be associated with a physical file by importing the description of this file.

Deleting the data source (query or view) Hide the details

When the same Data Source variable is used several times with different data sources, the first data source must be freed.

To free the memory space occupied by the data source (query or view), use the following function:
Remarks

Handling a query or a view through programming

To handle a query or a view by programming, we recommend that you use a "Data Source" variable.
However, you can specify a logical name when initializing the query or view. In that case, you will need to use the Extern keyword to directly manipulate the view or query in the code editor. This method can slow down the execution of your processes.
Remark: When using a logical name, the query or the view is not automatically destroyed: you must use HCancelDeclaration and HDeleteView.
For example:
  • Initializing a query:
    1. Initializing a query with a logical name:
      EXTERN MyQuery
      HExecuteSQLQuery(MyQuery, "SELECT NAME FROM CUSTOMER")
      // Other possible syntax:
      // HExecuteSQLQuery("MyQuery", "SELECT NAME FROM CUSTOMER")
       
      // Use the query
      HReadFirst(MyQuery, Name)
      IF NOT HOut() THEN
      Info("The 1st customer is: " + MyQuery.Name)
      END
      ...
      HCancelDeclaration(MyQuery)
    2. Initialize a query with a "Data source" variable:
      MyQuery is Data Source
      HExecuteSQLQuery(MyQuery, "SELECT NAME FROM CUSTOMER")
      // Use the query
      HReadFirst(MyQuery, Name)
      IF NOT HOut() THEN
      Info("The 1st customer is: " + MyQuery.Name)
      END
  • Initializing a view:
    1. Initializing a view with a logical name:
      EXTERN MyView
      HCreateView(MyView, CUSTOMER, "*", "NAME,CITY", "NAME]='A'", hViewDefault)
      // Other possible syntax:
      // HCreateView("MyView", "CUSTOMER", "*", "NAME,CITY", "NAME]='A'", hViewDefault)
       
      // Use the view
      HReadFirst(MyView, Name)
      IF NOT HOut() THEN
      Info("The 1st customer is: " + MyView.Name)
      END
      ...
      HDeleteView(MyView)
    2. Initializing a view with a "Data source" variable:
      MyView is Data Source
      HCreateView(MyView, CUSTOMER, "*", "NAME,CITY", "NAME]='A'", hViewDefault)
      // Use the view
      HReadFirst(MyView, Name)
      IF NOT HOut() THEN
      Info("The 1st customer is: " + MyView.Name)
      END

Using a data source in an SQL query

To use a data source in the SQL code of a query, the SQL code of query must use the logical name assigned to the data source.
For example, the following code can be used regardless of the data source naming mode:
ArchiveTicket is Data Source
HDeclareExternal("Ticket_Backup.FIC", ArchiveTicket)
   
QRY is Data Source
sMyQry is string = [
SELECT *
FROM [%TicketArchive%]
WHERE ZIPCODE='26110'
]
 
IF HExecuteSQLQuery(QRY, hQueryDefault, sMyQry) THEN
Info(HNbRec(QRY) + " tickets match.")
END
Example of code to be used if the description of the external file is the description of one of the files in the analysis:
ArchiveTicket is Data Source = <description=Ticket>
HDeclareExternal("Ticket.FIC", ArchiveTicket)
REQ is SQL Query = [
SELECT *
FROM [%TicketArchive%]
]
HExecuteQuery(QRY)
FOR EACH QRY
Trace(QRY.TicketID)
END

Properties associated with the Data Source variables

The following properties are associated with the Data Source variables. These properties are identical to the ones that apply to a data file:
AbbreviationReturns the abbreviation of the data source.
CharsetAllows you to find out the character set used by the data source.
DatabaseUsed to find out and modify the database associated with a connection (HFSQL Client/Server, OLE DB, ...).
MaxValueRetrieves the upper bound of the current filter (defined by HFilter) for a data source.
MinValueRetrieves the lower bound of the current filter (defined by HFilter) for a data source.
MmoPackMethodConfigures the compression mode of the MMO file (containing the binary and text memos) associated with the data source.
FilterConditionReturns the selection condition implemented by HFilter on a data source.
ConnectionAllows you to find out the connection currently associated with a data source.
FicEncryptionConfigures the encryption mode of the data source.
MmoEncryptionConfigures the encryption mode of the memo file (.MMO extension) associated with the data source.
NdxEncryptionConfigures the encryption mode of the index file associated with the data source.
CtAccessManages the type of access to the file when connecting to a specific table via OLE DB (taking into account the changes made with HConnect).
CtDescribedAccessManages the type of access to the file when connecting to a specific table via OLE DB (ignoring the changes made with HConnect).
CtDatabaseManages the OLE DB data source when connecting to a specific table via OLE DB (taking into account the changes made with HConnect).
CtDescribedDatabaseManages the OLE DB data source when connecting to a specific table via OLE DB (ignoring the changes made with HConnect).
CtExtendedInfoAllows you to find out the additional information inserted into the string for connecting to the database.
CtInitialCatalogAllows you to identify the default database defined for the access.
CtDescribedCaptionConfigures the caption of connection to the OLE DB data source.
CtCursorOptionsAllows you to find out the type of cursor used during a connection to an external database.
CtOLEDBProviderManages the name of the OLE DB provider (taking into account the changes made with HConnect).
CtDescribedOLEDBProviderManages the name of OLE DB provider (ignoring the modifications made by HConnect).
CtUserNameManages the username in a connection via OLE DB (taking into account the changes made with HConnect).
CtDescribedUserNameManages the username in a connection via OLE DB (ignoring the changes made with HConnect).
ExecutionCompletedAllows you to find out whether the execution of a query or HFSQL Classic or Client/Server view has ended.
ExtensionAllows you to find out and modify the extension of a data file.
LogFileAllows you to find out whether a data file is a log file.
TransactionFileAllows you to find out whether a data file is a transaction file.
FilterWithBoundsAllows you to find out whether bounds have been specified on the filter implemented by HFilter on a data source.
FormatConfigures the format of data found in the data file (ANSI or Unicode).
HugeFileConfigures the maximum size of data source.
GUIDAnalysisReturns the GUID of the analysis that was used to create the data file.
GUIDFileReturns the GUID of the file defined in the analysis that was used to create the data file.
MaxAutoIDEnables you to find out the maximum automatic identifier for a replicated file.
MinAutoIDEnables you to find out the minimum automatic identifier for a replicated file.
ExtendedInfoReturns and changes the additional information of a data file or item accessed via a Native Connector.
LogMethodReturns the method for logging a data file.
NbItemReturns the total number of items (including composite keys) found in a record of a file described in the data model editor, a file described programmatically, an HFSQL view or a query.
NbKeyItemReturns the total number of key items (including composite keys) found in a record of a file described in the data model editor, a file described programmatically, an HFSQL view or a query.
NbMemoItemReturns the total number of memo items found in a record of a file described in the data model editor, a file described through programming, an HFSQL view or a query.
NameManages the name of the different HFSQL objects.
Name55Manages the logical name of a file in Hyper File 5.5 format found in an analysis in HFSQL Classic format.
DescribedNameManages the logical name of a data file.
PhysicalNameManages the physical name of data files.
DescribedPhysicalNameManages the physical name of HFSQL files (ignoring the names changed with HChangeName).
NullSupportedAllows you to manage the NULL value in an HFSQL file.
GenerationNumberDefines the generation number of the physical file associated with the specified logical file.
PasswordProtectedAllows you to find out whether a file is password protected.
DirectoryManages the physical directory of HFSQL files. This property takes into account the change of directory performed by HChangeDir or HSubstDir.
DescribedDirectoryManages the physical directory of HFSQL files. This property ignores the change of directories made with HChangeDir or HSubstDir.
LogDirectoryManages the directory of log file described in the analysis.
OperationLogDirectoryAllows you to manage the directory of operation file for the log associated with a logged file.
ReplicationDirectoryAllows you to manage the directory of replica (".RPL" or ".RPM" file).
LogBackupDirectoryAllows you to find out and modify the backup directory of log files.
ReplicationAllows you to find out the replication mode used for a data file (file defined in the data model editor or through programming). For the files defined by programming, you can specify whether this file is in logged replication mode or not.
FilteredItemAllows you to find out the item on which a filter was implemented by HFilter on a data file, an HFSQL view or a query.
TextItemCompletedConfigures the management of text items in an HFSQL file. The text items can be automatically filled with space characters (operating mode identical to the one of WINDEV 5.5) or not.
SecureConfigures the security level for file encryption.
RecordSizeReturns the size of a record (in bytes) while ignoring the composite keys.
TypeIdentifies and modifies the type of an item.
WDD55Path of the WDD file in 5.5 format used to handle the files in 5.5 format in a WINDEV, WEBDEV or WINDEV Mobile application.
PHP The properties on the data sources are not available.
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 03/28/2023

Send a report | Local help