Data Source (Variable 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 / Managing databases / HFSQL / Types of HFSQL variables

Manipulating a query or view programmatically
Using a data source in an SQL query
Properties associated with Data Source variables

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Data Source (Variable type)

In french: Data Source

A Data Source variable is used to describe a temporary data source (query, view, alias, etc.).
All possible operations on a view or query can be performed using a Data Source variable.

To describe a data source, you must:

Declare a "Data Source" variable.
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 with the HDeclareExternal function.. In this case, the data source is used to manipulate a data file via its description, 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 "Data source names" option.

Example

See additional examples

// Déclaration d'une source de données
MaSourceDonnée is Data Source
MaSourceDonnée1, MaSourceDonnée2 are Data Sources

// Initialiser une variable de type Source de Données
MaRequête is Data Source
// MaRequête est associée à une requête
// Initialisation de la variable MaRequête 
HExecuteSQLQuery(MaRequête, "SELECT NOM FROM CLIENT")

// Initialiser une variable de type Source de Données
MaVue is Data Source
// MaVue est associée à une vue
// Initialisation de la variable MaVue 
HCreateView(MaVue, CLIENT, "*", "NOM,VILLE", "NOM]='A'", hViewDefault)

// Supprimer une source de données
MaRequête is Data Source
HExecuteSQLQuery(MaRequête, "SELECT NOM FROM CLIENT")
// Destruction de la source de données associée à la variable MaRequête
HCancelDeclaration(MaRequête)
HExecuteSQLQuery(MaRequête, "SELECT NOM FROM FOURNISSEUR")

// Suppression d'une source de données
MaVue is Data Source
// MaVue est associée à une vue
// Initialisation de la variable MaVue 
HCreateView(MaVue, CLIENT, "*", "NOM,VILLE", "NOM]='A'", hViewDefault)
// Destruction de la source de données associée à la variable MaVue
HDeleteView(MaVue)
HCreateView(MaVue, CLIENT, "*", "NOM,PRENOM", "NOM]='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 the Data Source variable to be declared.
Note: The keywords an and des are not mandatory: they are approval words..

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

<Nom de la variable> is a Data Source <description=Nom du fichier de données ou de la requête>

<Variable name>:

Name of the Data Source variable to be declared.

<Name of the data file or name of the query>:

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.

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.
HDeclareExternal if the variable is to be associated with a physical file, by importing the description of the corresponding data 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, it is necessary to free up the first data source.

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

HCancelDeclaration if the variable is associated with a query.
HDeleteView if the variable is associated with a view.

Remarks

Manipulating a query or view programmatically

To programmatically manipulate a query or a view, 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.

Note: When using a logical name, the query or view is not automatically destroyed: you need to use functions HCancelDeclaration and HDeleteView.

For example:

Initializing a query:

Initializing a query with a logical name:

EXTERN MaRequête
HExecuteSQLQuery(MaRequête, "SELECT NOM FROM CLIENT")
// Autre syntaxe possible :
// HExécuteRequêteSQL("MaRequête", "SELECT NOM FROM CLIENT")
 
// Utilisation de la requête
HReadFirst(MaRequête, Nom)
IF NOT HOut() THEN
	Info("Le 1er client est : " + MaRequête.Nom)
END
...
HCancelDeclaration(MaRequête)

Initialize a query with a "Data Source" variable:

MaRequête is Data Source
HExecuteSQLQuery(MaRequête, "SELECT NOM FROM CLIENT")
// Utilisation de la requête
HReadFirst(MaRequête, Nom)
IF NOT HOut() THEN
	Info("Le 1er client est : " + MaRequête.Nom)
END

Initializing a view:

Initializing a view with a logical name:

EXTERN MaVue
HCreateView(MaVue, CLIENT, "*", "NOM,VILLE", "NOM]='A'", hViewDefault)
// Autre syntaxe possible :
// HCréeVue("MaVue", CLIENT, "*", "NOM,VILLE", "NOM]='A'", hVueDéfaut)

// Utilisation de la vue
HReadFirst(MaVue, Nom)
IF NOT HOut() THEN
	Info("Le 1er client est : " + MaVue.Nom)
END
...
HDeleteView(MaVue)

Initializing a view with a "Data Source" variable:

MaVue is Data Source
HCreateView(MaVue, CLIENT, "*", "NOM,VILLE", "NOM]='A'", hViewDefault)
// Utilisation de la vue
HReadFirst(MaVue, Nom)
IF NOT HOut() THEN
	Info("Le 1er client est : " + MaVue.Nom)
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:

TicketArchive is Data Source
HDeclareExternal("Ticket_Bak.fic", TicketArchive)
     
REQ is Data Source 
sMaReq is string = [
	SELECT *
	FROM [%TicketArchive%]
	WHERE CODEPOSTAL='26110'
]
IF HExecuteSQLQuery(REQ, hQueryDefault, sMaReq) THEN
	Info(HNbRec(REQ) + " tickets correspondent.")
END

Example of code to be used in the case where the description of the external file is that of one of the analysis data files:

TicketArchive is Data Source <description=Ticket>
HDeclareExternal("Ticket.FIC", TicketArchive)
REQ is SQL Query = [
		SELECT *
		FROM [%TicketArchive%]
	]
HExecuteQuery(REQ)
FOR EACH REQ 
	Trace(REQ.IDTicket)
END

Properties associated with Data Source variables

The following properties are associated with Data Source variables. These properties are identical to the ones that apply to a data file:


Abbreviation	Returns the abbreviation of the data source.
Charset	Allows you to find out the character set used by the data source.
Database	Used to find out and modify the database associated with a connection (HFSQL Client/Server, OLE DB, ...).
MaxValue	Gets the upper bound of the filter (set with HFilter) currently applied to a data source.
MinValue	Gets the lower bound of the filter (set with HFilter) currently applied to a data source.
MmoPackMethod	Configures the compression mode of the MMO file (containing the binary and text memos) associated with the data source.
FilterCondition	Returns the selection condition implemented by HFilter on a data source.
Connection	Allows you to find out the connection currently associated with a data source.
FicEncryption	Configures the encryption mode of the data source.
MmoEncryption	Configures the encryption mode of the memo file (.MMO extension) associated with the data source.
NdxEncryption	Configures the encryption mode of the index file associated with the data source.
CtAccess	Manages the type of access to the file when connecting to a specific table via OLE DB (taking into account the changes made with HConnect).
CtDescribedAccess	Manages the type of access to the file when connecting to a specific table via OLE DB (ignoring the changes made with HConnect).
CtDatabase	Manages the OLE DB data source when connecting to a specific table via OLE DB (taking into account the changes made with HConnect).
CtDescribedDatabase	Manages the OLE DB data source when connecting to a specific table via OLE DB (ignoring the changes made with HConnect).
CtExtendedInfo	Allows you to see the additional information inserted in the database connection string.
CtInitialCatalog	Allows you to identify the default database defined for the access.
CtDescribedCaption	Configures the caption of connection to the OLE DB data source.
CtCursorOptions	Allows you to find out the type of cursor used during a connection to an external database.
CtOLEDBProvider	Manages the name of the OLE DB provider (taking into account the changes made with HConnect).
CtDescribedOLEDBProvider	Manages the name of OLE DB provider (ignoring the modifications made by HConnect).
CtUserName	Manages the username in a connection via OLE DB (taking into account the changes made with HConnect).
CtDescribedUserName	Manages the username in a connection via OLE DB (ignoring the changes made with HConnect).
ExecutionCompleted	Tells you whether an HFSQL Classic or Client/Server query or view has finished executing.
Extension	Allows you to find out and modify the extension of a data file.
LogFile	Allows you to find out whether a data file is a log file.
TransactionFile	Allows you to find out whether a data file is a transaction file.
FilterWithBounds	Allows you to find out whether bounds have been specified on the filter implemented by HFilter on a data source.
Format	Configures the format of data found in the data file (ANSI or Unicode).
HugeFile	Configures the maximum size of data source.
GUIDAnalysis	Returns the GUID of the analysis that was used to create the data file.
GUIDFile	Returns the GUID of the file defined in the analysis that was used to create the data file.
MaxAutoID	Allows you to set the maximum automation ID for a replicated file.
MinAutoID	Allows you to set the minimum automation ID for a replicated file.
ExtendedInfo	Returns and changes the additional information of a data file or item accessed via a Native Connector.
LogMethod	Returns the method for logging a data file.
NbItem	Returns 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.
NbKeyItem	Returns 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.
NbMemoItem	Returns 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.
Name	Manages the name of the different HFSQL objects.
Name55	Manages the logical name of a file in Hyper File 5.5 format found in an analysis in HFSQL Classic format.
DescribedName	Manages the logical name of a data file.
PhysicalName	Manages the physical name of data files.
DescribedPhysicalName	Manages the physical name of HFSQL data files (regardless of name changes made using HChangeName).
NullSupported	Used to manage the NULL value in an HFSQL data file.
GenerationNumber	Defines the generation number of the physical file associated with the specified logical file.
PasswordProtected	Allows you to find out whether a file is password protected.
Directory	Manages the physical directory of HFSQL data files. This property takes into account directory changes made using HChangeDir or HSubstDir.
DescribedDirectory	Manages the physical directory of HFSQL data files. This property ignores directory changes made using HChangeDir or HSubstDir.
LogDirectory	Manages the directory of log file described in the analysis.
OperationLogDirectory	Allows you to manage the directory of the log of operations file associated with a Journalé file.
ReplicationDirectory	Allows you to manage the directory of replica (".rpl" or ".rpm" file).
LogBackupDirectory	Allows you to find out and modify the backup directory of log files.
Replication	Enables you to identify the replication mode used for a data file (data file defined in the data model editor or programmatically defined). For the files defined through programming, you can specify whether this file is in logged replication mode or not.
FilteredItem	Allows you to find out the item on which a filter was implemented by HFilter on a data file, an HFSQL view or a query.
TextItemCompleted	Parameters for managing text fields in an HFSQL data file. The text items can be automatically filled with space characters (operating mode identical to the one of WINDEV 5.5) or not.
Secure	Configures the security level for file encryption.
RecordSize	Returns the size of a record (in bytes) while ignoring the composite keys.
Type	Identifies and modifies the type of an item.
WDD55	Path 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.