PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Handling a query or a view by 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Data source (Type of variable)
In French: Source de données (Type de variable)
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.
    Note: 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.
Notes:
  • When closing the application (or the process where the data source was declared), the data source will automatically be destroyed.
  • A global data source is always global to the HFSQL context where it was declared.
  • A data source has an internal name that is equal to the name of the variable. There can only be one data source of a given for the same file context. If you are using data source variables in a loop, in recursive code or in a class, the internal name of the data source will have to be modified to insure its uniqueness.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Versions 17 and later
iPhone/iPad This type of variable is now available for the iPhone/iPad applications.
New in version 17
iPhone/iPad This type of variable is now available for the iPhone/iPad applications.
iPhone/iPad This type of variable is now available for the iPhone/iPad applications.
Versions 18 and later
Universal Windows 10 App This type of variable is now available in Windows Store apps mode.
New in version 18
Universal Windows 10 App This type of variable is now available in Windows Store apps mode.
Universal Windows 10 App This type of variable is now available in Windows Store apps mode.
Versions 21 and later
Universal Windows 10 App This type of variable is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This type of variable is now available in Universal Windows 10 App mode.
Universal Windows 10 App This type of variable is now available in Universal Windows 10 App mode.
// 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

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

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

<Variable Name> 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
New in version 19

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

<Variable Name> 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

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

<Variable Name> 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 by programming

To handle a query or a view by programming, we recommend that you use a "Data Source" variable.
However, you have the ability to give a logical name when initializing the query or the view. In this case, the Extern keyword must be used to directly handle the view of query in the code editor. This method can slow down the execution of your processes.
Note: 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:
  1. Using the name assigned to the "data source" in its declaration:
    ArchiveTicket is Data Source = "NameForSQLInterrogation"

    HDeclareExternal("Ticket_2008.FIC", ArchiveTicket, CntDatabase)
       
    QRY is Data Source
    sMyQry is string = [
    SELECT *
    FROM NameForSQLInterrogation
    WHERE ZIPCODE='26110'
    ]

    IF HExecuteSQLQuery(QRY, hQueryDefault, sMyQry) THEN
    Info(HNbRec(QRY) + " tickets match.")
    END
  2. Using the name of "data source" returned by ..Name:
    ArchiveTicket is Data Source

    HDeclareExternal("Ticket_2008.FIC", ArchiveTicket, CntDatabase)
       
    QRY is Data Source
    sMyQry is string = [
    SELECT *
    FROM %1
    WHERE ZIPCODE='26110'
    ]
    sMyQry = StringBuild(sMyQry, ArchiveTicket..Name)


    IF HExecuteSQLQuery(QRY, hQueryDefault, sMyQry) THEN
    Info(HNbRec(QRY) + " tickets match.")
    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.
FicCryptMethodConfigures the encryption mode of the data source.
MmoCryptMethodConfigures the encryption mode of the memo file (.MMO extension) associated with the data source.
NdxCryptMethodConfigures the encryption mode of the index file associated with the data source.
CtAccessManages the type of access to the file during the OLE DB connection to a specific table (while taking into account the modifications performed by HConnect).
CtDescribedAccessManages the type of access to the file during the OLE DB connection to a specific table (while ignoring the modifications performed by HConnect).
CtDatabaseManages the OLE DB data source during the OLE DB connection to a specific table (while taking into account the modifications performed by HConnect).
CtDescribedDatabaseManages the OLE DB data source during the OLE DB connection to a specific table (while ignoring the modifications performed by 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 OLE DB provider (while taking into account the modifications performed by HConnect).
CtDescribedOLEDBProviderManages the name of OLE DB provider (while ignoring the modifications performed by HConnect).
CtUserNameManages the user name during an OLE DB connection (while taking into account the modifications performed by HConnect).
CtDescribedUserNameManages the user name during an OLE DB connection (while ignoring the modifications performed by 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.
FilterWithBoundsUsed 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.
MaxAutoIDAllows you to find out the maximum automatic identifier on a replicated file.
MinAutoIDAllows you to find out the minimum automatic identifier on a replicated file.
ExtendedInfoReturns and modifies the additional information of a data file or item accessed by Native Access.
LogMethodReturns the method for logging a data file.
NbItemReturns the total number of items (including the composite keys) found in a record of a file described in the data model editor, a file described by programming, a HFSQL view or a query.
NbKeyItemReturns the total number of key items (including the composite keys) found in a record of a file described in the data model editor, a file described by programming, a 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 by programming, a 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 (while ignoring the change of names performed by HChangeName).
NullSupportedUsed to manage the NULL value in a HFSQL file.
GenerationNumberDefines the generation number of the physical file associated with the specified logical file.
PasswordProtectedUsed 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 directory performed by 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 by programming). For the files defined by programming, you can specify whether this file is in logged replication mode or not.
FilteredItemUsed to find out the item on which a filter was implemented by HFilter on a data file, a HFSQL view or a query.
TextItemCompletedConfigures the management of text items in a 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 required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment