The import steps

1. Launch the data model editor: click on among the quick access buttons.
2. On the "Analysis" tab, in the "Creation" group, expand "Import" and select "Import file/table descriptions...".
3. 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 programmatically.

• Database type: SQL Server.
• Connection features: 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

• analyze the differences for the imported tables.
• analyze the differences for all the SQL Server tables.

1. Establishing the connection

• 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:
  • 0 to force the Native SQL Server Connector via OLE DB.
  • 3 to force the Native SQL Server Connector via ODBC.
  This variable must be modified before using HDescribeConnection and/or HOpenConnection.
• You can use specific extended information (in HOpenConnection or HDescribeConnection):
  • WD Cache Size: Sets the number of records to be read at once with a server cursor. This value is set to 100 by default.
  • WD Cursor Location: If "WD Cursor Location = SERVER", requests the use of a server cursor for all routes 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.
    Caution:
    • 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.
      Note: When a connection is in transaction, server cursors are automatically used.
• SQL Azure: If the server name is of type: ServerName.database.windows.net, the login must be of the form: login@ServerName.

2. Running queries

• HEExecuteQuery: executes a predefined query using the query editor.
• HExecuteQuerySQL: allows you to execute a query by directly specifying the query's SQL code, and the name to be allocated to this SQL code.

// Initialisation de la requête "Client_84"
HExecuteQuery(CLIENT_84, "BaseSQLSERVER", hQueryWithoutCorrection)
HExecuteSQLQuery(ReqClient, "BaseSQLSERVER", hQueryWithoutCorrection, ...
		"SELECT NOM FROM CLIENT")

3. Retrieving the result

4. Closing the connection

Optimization

• "Table file" fields based on queries are optimized: you can sort the contents of a Table field by clicking on 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:
  • HClose on the tables once the browse is ended,
  • HCancelDeclaration for browsing queries.