- Lookup table between the different types of databases, their sources and their databases
- Failure or success of connection
- Connection to an HFSQL analysis
- ODBC and OLE DB: Nesting connections
- Connection to a database using ODBC
- Native MySQL, MariaDB and PostgreSQL Connectors
- Connection to an ISAM database via ACCESS
- Using a connection with NT authentication
- Native Oracle Connector: managing the external authentication
- SQL query (HExecuteSQLQuery or queries created in the query editor)
In french: SQLConnecteWS
Connects the current application to a database that will be queried by SQL through a Webservice proxy (SOAP).
- This function requires the setup of a Webservice for accessing the databases. For more details, see Accessing the databases via a Webservice.
- This function does not allow access to password-protected data files.
ConnectionNum is int
ConnectionNum = SQLConnectWS("http://MyServer/WDSOAPDB_WEB/", ...
"MS Access 97 Database", "", "", "", "ODBC")
IF ConnectionNum <> 0 THEN
// The connection was successful
// The connection failed: displaying an error message
Error("The connection to the data source " + SourceName + ...
" failed." + CR + "Error code: " + ...
SQL.Error + CR + SQL.MesError)
// In any case (connection OK or not)
<Result> = SQLConnectWS(<Server of Webservice> , <Source> , <User> , <Password> [, <Database name> [, <Type of database> [, <OLE DB provider> [, <Optional information>]]]])
- Connection identifier: this identifier is used by SQLChangeConnection.
- 0 if an error occurs. To find out the error details, use SQLInfo.
<Server of Webservice>: Character string
URL for accessing the server where the Webservice for accessing the external databases was installed. This URL was specified in the wizard for installing the Webservice. For more details, see Accessing the databases via a Webservice.
<Source>: Character string
Name of data source (alse called Data Source Name). If the data source contains several "Databases", you must specify the name of the "Database" used (<Database name> parameter). For more details, see remarks.
MySQL and MariaDB: This parameter corresponds to the name or IP address of the computer containing the database. For example, "Apollon".
The source can be an HFSQL Classic or HFSQL Client/Server database.
<User>: Character string
Username. This name is optional for some data sources: in this case, use an empty string ("") for this parameter.
<Password>: Character string
Password corresponding to the specified user. This password is optional for some data sources: in this case, use an empty string ("") for this parameter.
<Database name>: Optional character string
Name of the database to use. For more details, see remarks.
- If this parameter is specified, a dialog box specific to the driver is displayed during the connection. This dialog box may not be displayed according to the driver used.
- If this parameter is not specified, no dialog box is displayed.
MySQL and MariaDB: With Native MariaDB Access, the database name is mandatory.
<Type of database>: Optional character string
Type of database accessed. The types of databases installed on the current computer are returned by SQLListSource. For more details, see the remarks.
<OLE DB provider>: Optional character string or constant
Name of OLE DB provider used. The most common ones are as follows:
or one of the following constants:
|hODBC||OLE DB provider for ODBC. Allows you to access an ODBC source declared in the ODBC data sources of Windows|
|hOledbAccess97||OLE DB provider for Access 97|
|hOledbAccess2000||OLE DB provider for Access 2000|
|hOledbAccess2007||OLE DB provider for Access 2007|
|hOledbDBase5||OLE DB provider for dBase 5|
|hOledbExcel97||OLE DB provider for Excel 97|
|hOledbExcel2000||OLE DB provider for Excel 2000|
|hOledbExcel2007||OLE DB provider for Excel 2007|
|hOledbLotus4||OLE DB provider for Lotus 4|
|hOledbOracle||OLE DB provider for Oracle|
|hOledbSQLServer||OLE DB provider for SQL Server|
Caution: To use an OLE DB connection, you must:
- install MDAC version 2.6 or later (setup performed by WINDEV or WEBDEV when installing the application)
- install the OLE DB provider corresponding to the database used.
<Optional information>: Optional character string (not to be used with HFSQL or direct ODBC)
Used to specify the optional information. You can for example specify "Trusted_Connection=YES" in order to use a connection with authentication via the NT login. If several optional information must be specified, they must be grouped in a single character string and they must be separated by the ";" character.
Connection to an HFSQL analysis
For most of the platforms, the access to the HFSQL data (Classic or Client/Server) is available in native mode. There is no need to access the databases via a Webservice proxy (SOAP). To access an HFSQL Client/Server database, simply use HOpenConnection
To connect to an HFSQL analysis via a Webservice proxy, use the following syntax:
SQLConnectWS(<Server of Webservice>, ...
<"<Drive>:<Full Analysis WDD Path>", "", "<Analysis Password>")
// URL of the server that owns the Webservice
sURLWebserviceServer is string = "http://ServerURL"
// WDD of analysis on the server.
// The path is given in relation to the data directory of the Webservice.
sPathAnalysisWDDOnServer is string = ".\" + ProjectInfo(piAnalysis) + ".wdd"
sWDDPassword is string
// Connection to the HFSQL database according to what is specified in the analysis
IF NOT SQLConnectWS(sURLWebserviceServer, sPathAnalysisWDDOnServer, "", ...
// Connection failure
// Connection OK
// Rest of the code with SQLExec, etc.
- After the connection to an HFSQL analysis, the SQL.Connection variable returns -1.
- The HFSQL data files will be sought on the server in the current directory of the Webservice programs or in the directory described in the analysis. No path can be specified by programming.
- If a password has been set for the analysis, it must be specified in the third parameter passed to SQLConnectWS.
- To handle the HFSQL Client/Server data files, the connection described in the analysis must contain all the necessary information (name or IP Address of the server, port, database, ...): this information cannot be specified through programming.
Connection to a database using ODBC
To connect to a database using ODBC, follow these steps:
- Configure the ODBC data source in the ODBC administrator (on the development computer or on the deployment computer). The data source must be a "system" data source.
- Use SQLConnectWS with the "ODBC" parameter.
Remark: you have the ability to use an OLE DB provider on ODBC. MDAC must be installed (on the development computer and on the deployment computer). The following syntax must be used:
<Result> = SQLConnectWS(<Server of Webservice>, <Source>, <User>, ...
<Password>, <Database Name>, "OLE DB", hODBC)
: The SQL.ODBCHandle
variable is used to find out the handle of the ODBC connection for the other SQL functions on ODBC. A value is assigned to this variable the last time SQLConnectWS
is called. The SQL.HandleODBC
variable is set to -1 for the other connections.
Native MySQL, MariaDB and PostgreSQL Connectors
To open a connection to a MySQL, MariaDB or PostgreSQL database thorough a Native Connector, you need to pass the following elements as parameters to SQLConnectWS:
- the type of the database used, MySQL, MariaDB or POSTGRESQL.
- the name of the database: it corresponds to the name given by the administrator of the MySQL/MariaDB/PostgreSQL database.
Remark: The name of the MySQL or PostgreSQL data source can be replaced with the name or the IP address of the computer where the database is available. In any case, the name of the "Database" must be specified.
// Connection to a MySQL database
ConnectionNum = SQLConnectWS("http://ServerURL", "MySQLDatabase", ...
"", "", "", "MySQL")
// ConnectionNum = SQLConnect("MySQLDatabase", "User", ...
// "Password", "MyDatabase", "MySQL")
// Connection to a remote MySQL database
ConnectionNum = SQLConnectWS("http://ServerURL", "192.168.1.51", ...
"User", "Password", "test", "MySQL")
Connection to an ISAM database via ACCESS
The following syntax is used to connect to an ISAM database:
SQLConnectWS(<Server of Webservice>, <Database Path>, ...
<User>, <Password>, <Database Type>, "ACCESS")
|<Database path>||Path or full name (depending on the case) of the database accessed|
|<User>||Username. It is optional for some databases.|
|<Password>||Password for this user. It is optional for some databases.|
|<Database type>||Type of the database to which you are connected (therefore, the corresponding ISAM driver must have been installed).|
|Type of database||<Database type>||<Database path>|
|dBASE III||"dBASE III"||drive:\directory|
|dBASE IV||"dBASE IV"||drive:\directory|
|dBASE 5||"dBASE 5.0"||drive:\directory|
|Paradox 3.x||"Paradox 3.x"||drive:\directory|
|Paradox 4.x||"Paradox 4.x"||drive:\directory|
|Paradox 5.x||"Paradox 5.x"||drive:\directory|
|FoxPro 2.0||"FoxPro 2.0"||drive:\directory|
|FoxPro 2.5||"FoxPro 2.5"||drive:\directory|
|FoxPro 2.6||"FoxPro 2.6"||drive:\directory|
|Excel 3.0||"Excel 3.0"||Drive:\directory\file.xls|
|Excel 4.0||"Excel 4.0"||Drive:\directory\file.xls|
|Excel 5.0 or Excel 95||"Excel 5.0"||Drive:\directory\file.xls|
|Excel 97||"Excel 8.0"||Drive:\directory\file.xls|
|HTML Import||"HTML Import"||Drive:\directory\filename|
|HTML Export||"HTML Export"||drive:\directory|
- In order for the requested connection to be established, the corresponding ISAM driver must have have been installed. The setup program of MS OFFICE 97 proposes several ISAM drivers.
- The ACCESS and ISAM databases are accessible in 32-bit mode only.
- The WLanguage WDBinaryMemo keyword, used to add or modify a binary memo via an SQL query, is not supported by the ACCESS driver.
Native Oracle Connector: managing the external authentication
To connect via an external authentication, the following connection parameters must be used:
- <User> = / (slash)
- <Password> = "" (empty string)
Remark: The external authentication consists in using the name of Windows user and his password to connect to the database. The external authentication requires configuring the server. See the Oracle documentation to learn more about how to authorize external authentications on the server.
This page is also available for…