- Importing the structure of files
- The import steps
- Taking into account the evolution of the SQL Server database
- 1. Establishing the connection
- 2. Running queries
- 3. Retrieving the result
- 4. Closing the connection
- SQL Azure
Native SQL Server Connector: Programming with HFSQL functions
Not available with these kinds of connection
To use an SQL Server database with Native Connector, the structure of SQL Server tables must be imported into the data model analysis
Differences compared to SQL programming: The queries are not required to handle the SQL Server data. Indeed, the HFSQL functions can be directly used to read data, perform searches, implement filters, ... However, the structure of SQL Server files must be imported into the data model editor.
Importing the structure of files
The import steps
To perform this import:
- Start the data model editor: click in the quick access buttons.
- On the "Analysis" tab, in the "Creation" group, expand "Import" and select "Import file/table descriptions...".
- The wizard starts. A connection to the SQL Server database is automatically established and associated with the files imported into the analysis. This connection will be used to handle the SQL Server data file through programming.
In the different steps of the wizard, choose the following options:
- Type of database: SQL Server.
- Characteristics of the connection: Data server, User and password, database.
- The application will access the data in the current format.
- When the connection is established, all you have to do is select the tables that will be imported into the WINDEV or WEBDEV analysis. These tables can be handled through programming with the HFSQL functions of WLanguage. These tables will be displayed in blue in the data model editor.
Taking into account the evolution of the SQL Server database
To take into account the evolutions of the tables imported into the data model editor, go to the "Analysis" tab, "Analysis" group, expand "Synchronization" and select "Update analysis from external databases".
A wizard starts, allowing you to:
- analyze the differences for the imported tables.
- analyze the differences for all the SQL Server tables.
1. Establishing the connection
To establish the connection with the SQL Server database, use HOpenConnection
and specify the name of the connection to open. The connection name was defined when importing the structure of files into the analysis.
To modify some parameters of this connection (username or password for example), call HConnect
- If you try to read the file directly (HReadFirst, ...), the connection associated with the file description in the analysis will be automatically opened.
- The optional connection information is used to configure the behavior of the Native Connector (date format, result of concatenations, etc.).
- By default, the installed version of the Native SQL Server Connector is used. If you have installed both the Native SQL Server Connector via OLE DB and via ODBC, the Native Connector via OLE DB will be used. You can select the version to use. The H.SQLServerMode variable is used to force a mode via the following values:
This variable must be modified before using HDescribeConnection and/or HOpenConnection.
- 0 to force the Native SQL Server Connector via OLE DB.
- 3 to force the Native SQL Server Connector via ODBC.
- You can use specific extended information (in HOpenConnection or HDescribeConnection):
- WD Cache Size: Defines the number of records that must be read at the same time by a server cursor. This value is set to 100 by default.
- WD Cursor Location: If "WD Cursor Location = SERVER", gives you the ability to use a server cursor for all the browses performed on this connection. The type of the server cursors used is FAST_FORWARD. For more details, see the documentation about SQL Server. This type of cursor can be used to:
- Limit the resources reserved on the server during the browse operations.
- Limit the number of connections opened by the native access to manage the multiple browses in parallel.
- In most cases, the browses performed with the server cursors are slower than the browses performed in default mode.
- The server cursors may not be compatible with some types of queries.
- The performance is affected by the cache size.
Remark: The server cursors are automatically used when a connection is in transaction.
- SQL Azure: If the name of the server is such as: ServerName.database.windows.net, the login must have the following format: login@ServerName.
2. Running queries
- HExecuteQuery: is used to run a preset query with the query editor.
- HExecuteSQLQuery: is used to execute a query by specifying the SQL code of the query and the name that will be assigned to this SQL code.
// Initialize the "Customer_84" query
HExecuteQuery(CUSTOMER_84, "SQLServerDatabase", hQueryWithoutCorrection)
HExecuteSQLQuery(CustomerQry, "SQLServerDatabase", hQueryWithoutCorrection, ...
"SELECT NAME FROM CUSTOMER")
3. Retrieving the result
The records cannot be locked by the lock options of these functions.
4. Closing the connection
is used to close the connection to the database once all the necessary queries have been run.
- The "Browsing table" controls based on queries are optimized: the content of the Table control can be sorted by clicking one of its columns.
- To avoid re-running the same query several times when browsing its result, we advise you to use the hNoRefresh constant (if the data is modified by a single computer for example).
- Each browse on a key requires the creation of a "cursor". This "cursor" is run in an SQL SERVER "task". To avoid storing useless processes on the server, we recommend that you use:
SQL Azure requires the presence of a "clustered index" in the data files.
If the data files are created by HCreation
, the Native Connector automatically creates the "clustered index" if the description of the data file contains an automatic identifier.
This page is also available for…