SQLConnectWS (Function)

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 / SQL functions

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
Oracle Native Connector: external authentication management
SQL query (HExecuteSQLQuery or queries created in the query editor)

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQLConnectWS (Function)

In french: SQLConnecteWS

Connects the current application to a database that will be queried by SQL through a web service proxy (SOAP).

Remarks:

This function requires the setup of a web service for accessing the databases. For more details, see Accessing the databases via a web service.
This function does not allow access to password-protected data files.

Example

ConnectionNum is int

ConnectionNum = SQLConnectWS("http://MyServer/WDSOAPDB_WEB/", ...
	"MS Access 97 Database", "", "", "", "ODBC")
IF ConnectionNum <> 0 THEN
	// The connection was successful
	 ...
ELSE
	// Connection failed: explanatory message displayed 
	SQLInfo()
	Error("The connection to the data source " + SourceName + ...
		" failed." + CR + "Error code: " + ...
		SQL.Error + CR + SQL.MesError)
END
// In any case (connection OK or not)
SQLDisconnect()

Syntax

<Result> = SQLConnectWS(<Server of web service> , <Source> , <User> , <Password> [, <Database name> [, <Type of database> [, <OLE DB provider> [, <Optional information>]]]])

<Result>: Integer

Connection identifier: this identifier is used by the SQLChangeConnection function.
0 if an error occurs. To find out the error details, use SQLInfo.

<Server of web service>: Character string

URL for accessing the server where the web service for accessing the external databases was installed. This URL was specified in the wizard for installing the web service. For more details, see Accessing the databases via a web service.

<Source>: Character string

Name of the data source (also 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 can correspond to the name of the workstation where the database is available or to its IP address.. 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 or Secret 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..
New in version 2025
Secret strings: If you use the secret string vault, the type of secret string used for this parameter must be "Ansi or Unicode string".
To learn more about secret strings and how to use the vault, see Secret string vault.
Secret strings are not available for this parameter in Android/Android widget applications.

<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 MariaDB Native 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:
"Microsoft.Jet.OLEDB.3.51" Access
"Microsoft.Jet.OLEDB.4.0" Access
"MSDAORA" Oracle (Microsoft)
"MSDASQL" ODBC
"OraOLEDB.Oracle" Oracle
"SQLOLEDB" SQLServer
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.
The keywords recognized by OLE DB and the Native Connectors are presented in Optional connection information.

Remarks

Lookup table between the different types of databases, their sources and their databases

Type of database	Source	Database name

ACCESS	Name of the Access file	"" (empty string)
AS400	Native AS/400 Connector (optional module)
DB2	Native DB2 Connector (optional module) Source defined in ODBC Administrator	"" (empty string) or Database
HFSQLCS	HFSQL Client/Server access Name of address of the HFSQL C/S server	Database name
HYPER FILE HFSQL	Database name	"" (empty string)
INFORMIX	Native Informix Connector (optional module)
JDBC	URL for connecting to the database. This URL is specific to each driver.	The database must be specified in the connection URL. This URL is specific to each JDBC driver.
HIVE	URL for connecting to the Hive server.	Database
MySQL	Native MySQL Connector (optional module) Database name	Database
MariaDB	Native MariaDB Connector (optional module) Database name	Database
POSTGRESQL	Native PostgreSQL Connector (optional module) Database name	Database
ODBC	Source defined in ODBC Administrator	"" (empty string) or Database
OLEDB	Source name	"" (empty string)
ORACLE	Native Oracle Connector (optional module) Name of the alias defined in SQL NET Easy configuration and in WDORAINS	"" (empty string)
PROGRESS	Native Progress Connector (optional module) Source defined in ODBC Administrator	"" (empty string) or Database
SQL AZURE	Native SQL Azure Connector (optional module supplied with the Native SQL Server Connector) Server name. If the server name is of type: ServerName.database.windows.net, the login must be of the form: login@ServerName	Database
SQL SERVER	Native SQL SERVER Connector (optional module) Server name	"" (empty string)
Sybase	Native SYBASE Connector (optional module) Name of server or its alias	"" (empty string)
xBase	Native xBase Connector (supplied with the product)
	Database available via the browser (available in Chrome and Safari only) "" (empty string)	"" (empty string)

The basic types supported are: HYPER FILE.

Failure or success of connection

If the connection is successful, the connection identifier can be used to change the connection (SQLChangeConnection).

If the connection fails, the SQL.Error and SQL.MesError variables are not initialized. For more details about the connection failure, use SQLInfo.

In any case (failure or success), the connection must be closed (SQLDisconnect).

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 web service proxy (SOAP). To access an HFSQL Client/Server database, simply use HOpenConnection.

To connect to an HFSQL analysis via a web service proxy, use the following syntax:

SQLConnectWS(<Server of web service>, ...
<"<Drive>:<Full Analysis WDD Path>", "", "<Analysis Password>")

For example:

// URL of the server that owns the web service
sURLWebserviceServer is string = "http://ServerURL"
// WDD of analysis on the server. 
// The path is given in relation to the data directory of the web service.
sPathAnalysisWDDOnServer is string = ".\" + ProjectInfo(piAnalysis) + ".wdd"
// Password
sWDDPassword is string
// Connection to the HFSQL database according to what is specified in the analysis
IF NOT SQLConnectWS(sURLWebserviceServer, sPathAnalysisWDDOnServer, "", ...
		sWDDPassword) THEN 
	// Connection failure
	SQLInfo()
	Error(SQL.MesError)
	RETURN
END

// Connection OK 
// Rest of the code with SQLExec, etc.

Remarks:

After the connection to an HFSQL analysis, the SQL.Connection variable returns -1.
The HFSQL data files will be searched for on the server in the current directory of the web service programs or in the directory described in the analysis. No path can be specified programmatically.
If a password has been set for the analysis, it must be specified in the third parameter passed to SQLConnectWS.
To manipulate HFSQL Client/Server data files, the connection described in the analysis must contain all the necessary information (server name or IP address, port, database, etc.): this information cannot be specified programmatically..

ODBC and OLE DB: Nesting connections

For ODBC and OLE DB, several calls to the SQLConnectWS function can be nested (note: this is not possible with HFSQL databases).

The last opened connection is the current connection. SQLChangeConnection is used to modify the current connection.

SQLDisconnect disconnects the current connection.

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.

Note: it's possible to use an OLE DB provider over ODBC. MDAC must be installed (on the development computer and on the deployment computer). The following syntax must be used:

<Result> = SQLConnectWS(<Server of web service>, <Source>, <User>, ...
<Password>, <Database Name>, "OLE DB", hODBC)

Note: The variable SQL.HandleODBC is used to find out the ODBC connection handle for 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.
database name: this is the name given by the MySQL/MariaDB/PostgreSQL database administrator.

Remark: The name of the MySQL or PostgreSQL data source can be replaced by the name of the workstation where the database is available or by its IP address.. In any case, the name of the "Database" must be specified.

// Connection to a MySQL database
ConnectionNum = SQLConnectWS("http://ServerURL", "MySQLDatabase", ...
"", "", "", "MySQL")
// or
// 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 web service>, <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>	User password. 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
Text	"Text"	drive:\directory

Remarks:

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.

Using a connection with NT authentication

The following syntax allows you to establish a connection with NT authentication:

SQLConnectWS(<Server of web service>, <MaDatabase>, Null, Null, Null, ...
"OLEDB", <OLE DB Provider>, "Trusted_Connection=YES")

Oracle Native Connector: external authentication management

To connect via an external authentication, the following connection parameters must be used:

<User> = / (slash)
<Password> = "" (empty string)

Remark: External authentication relies on the Windows user name and 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.

SQL query (HExecuteSQLQuery or queries created in the query editor)

When using the SQL DELETE, INSERT or UPDATE statements, no integrity check and no duplicate check are performed on an HFSQL database. This feature is not available in this version.

Solution: Use HFSQL functions (HDelete, HAdd or HModify) on your data files. The integrity check and the duplicate check will be automatically performed.

Component: wd300hf.dll