|
- Handling a query or a view through programming
- Using a data source in an SQL query
- Properties associated with the Data Source variables
Data source (Type of variable) In french: Data Source
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: - 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. 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.
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. 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.
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. For example: - Initializing a query:
- 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)
- 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:
- 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)
- 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: | | 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 | Retrieves the upper bound of the current filter (defined by HFilter) for a data source. | MinValue | Retrieves the lower bound of the current filter (defined by HFilter) for 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 find out the additional information inserted into the string for connecting to the database. | 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 | Allows you to find out whether the execution of a query or HFSQL Classic or Client/Server view has ended. | 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 | Enables you to find out the maximum automatic identifier for a replicated file. | MinAutoID | Enables you to find out the minimum automatic identifier 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 files (ignoring the names changed with HChangeName). | NullSupported | Allows you to manage the NULL value in an HFSQL 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 files. This property takes into account the change of directory performed by HChangeDir or HSubstDir. | DescribedDirectory | Manages the physical directory of HFSQL files. This property ignores the change of directories made with HChangeDir or HSubstDir. | LogDirectory | Manages the directory of log file described in the analysis. | OperationLogDirectory | Allows you to manage the directory of operation file for the log associated with a logged 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 | Allows 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. | 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 | Configures 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. | 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. |
This page is also available for…
|
|
|
|
|
|
|