SQLConnect (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
Native MySQL, MariaDB and PHP 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)
Connection to an HFSQL database in Java

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQLConnect (Function)

In french: SQLConnecte

Connects the current application to a database that must be interrogated via SQL.

The SQL functions are used to handle the MySQL and MariaDB databases and the databases accessible by ODBC (HFSQL for example).

SQLConnect is used to connect to an HFSQL Classic database, to an HFSQL Client/Server database or to a database accessible via ODBC.

For more details, see Java and database.

The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. For more details, see Accessing a database in local mode (SQLite).

Example

See additional examples

// Example of connection via ODBC
ConnectionNum is int
SourceName is string
// Connection to a specific data source via ODBC MS ACCESS
// (note: some drivers open a file selection box
// if no file is associated with this source)
SourceName = "MS Access 97 Database"
ConnectionNum = SQLConnect(SourceName, "", "", "", "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()

// Connection to a MySQL database: 
// apollon computer, login "superv", test customer database
ConnectionNum is int
ConnectionNum = SQLConnect("apollon", "superv", "", "test")

// Connection to a MySQL database using a JDBC driver:
SQLConnect("jdbc:mysql://" + myServer + "/" + mydatabase, "myself", "mypwd", "", ...
		"JDBC", "com.mysql.jdbc.Driver")

// Connect using an ODBC data source: 
SQLConnect("jdbc:odbc:MySource", "myself", "mypwd", "", "JDBC", "sun.jdbc.odbc.JdbcOdbcDriver")

// Connection using an HFSQL database: 
SQLConnect("MyAnalysis.WDD", "", "mypwd", "", "HFSQL")

// Connection using HFSQL with a Client/Server database: 
SQLConnect("ServerName:port", "User", "Password", "DatabaseName", "HFSQLCS")

Syntax

<Result> = SQLConnect(<Source> , <User> , <Password> [, <Database name> [, <Database type> [, <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.

<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 the 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".
URL for connecting to the database. This URL is specific to each driver. For example:
Connect to a MySQL database using a JDBC driver: "jdbc:mysql://" + myServer + "/" + madatabase
Connection using an ODBC data source: "jdbc:odbc:MaSource".
Connection using HFSQL: "MonAnalyse.WDD".
The source can be an HFSQL Classic or HFSQL Client/Server database.
This parameter corresponds to an empty string ("").

<User>: Character string

Username. This name is optional for some data sources: in this case, use an empty string ("") for this parameter..
This parameter is ignored.

<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.
This parameter is ignored.

<Database name>: Optional character string

Name of the database to use. For more details, see the 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 the MariaDB Native Connector, the database name is mandatory.
This parameter is ignored. The database must be specified in the connection URL. This URL is specific to each JDBC driver.

<Database type>: Optional character string

Type of accessed database. The types of databases installed on the current computer are returned by SQLListSource. For more details, see the remarks.
Only the HFSQL and JDBC databases are accessible in this version. The databases accessed via the JDBC driver are reserved to a Java application.
Only databases available via a browser are accessible.

<OLE DB provider>: Optional character string or constant

Name of OLE DB provider used. The most common ones are as follows:
"SQLOLEDB" SQLServer
"MSDASQL" ODBC
"Microsoft.Jet.OLEDB.3.51" Access
"Microsoft.Jet.OLEDB.4.0" Access
"MSDAORA" Oracle (Microsoft)
"OraOLEDB.Oracle" Oracle
or one of the following constants:
hODBC OLE DB provider for ODBC. Used 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.
hOledbAccess2010 OLE DB provider for Access 2010.
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.
Full name of the JDBC driver to use.
This parameter is ignored.
This parameter is ignored.

<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 displayed on Optional connection information.
This parameter is ignored. The optional information must be specified in the connection URL. This URL is specific to each JDBC driver.
This parameter is ignored.
This parameter is ignored.

Remarks

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

Database type	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
HYPER FILE HFSQL	Database name	"" (empty string)
HFSQLCS	Server name or address	Database name
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
MariaDB	Native MariaDB Connector (optional module) Database name	Database
MySQL	Native MySQL 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: ServerName.database.windows.net, the login must be in 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)
"Web SQL database"	Database available via a browser (available in Chrome and Safari only) "" (empty string)	"" (empty string)

Supported database types are: MySQL, MariaDB, ODBC, HYPER FILE, ORACLE or POSTGRESQL. We recommend that you use the corresponding hAccessxxx constant.

Supported database types are: JDBC, HYPER QUEUE.

Supported database types 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).

The disconnection is not required if the connection failed.

Connection to an HFSQL analysis

To connect to an HFSQL analysis, use the following syntax:

SQLConnect("<Drive>:<Full path of analysis WDD>", "", "<Analysis password>")

For example:

SQLConnect("D:\WINDEV\WDSTOCK\WDSTOCK.WDD", "", "")

Remarks:

After the connection to an HFSQL analysis, SQL.Connection returns -1.
For the HFSQL analysis, a single connection can be established at one time in the same project.
If the analysis was already opened by HOpenAnalysis or if the project is associated with the analysis, SQLConnect does not re-open the analysis.
On the contrary, if the analysis is not opened yet, it is automatically opened by SQLConnect.
If the HFSQL data files are not found in the current program directory or in the directory described in the analysis, the directory must be modified by HChangeDir.
If a password has been set for the analysis, it must be specified in the third parameter passed to SQLConnect.

ODBC and OLE DB: Nesting connections

For ODBC and OLE DB, several calls to the SQLConnect 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).
Use SQLConnect with the "ODBC" parameter.

Remark: 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> = SQLConnect(<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 SQLConnect is called. SQL.ODBCHandle 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 (also called Native Access), you need to pass the following elements as parameters to SQLConnect:

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 local MySQL database 
ConnectionNum = SQLConnect("MySQLDatabase", "", "", "", "MySQL")
// or ConnectionNum = SQLConnect("MySQLDatabase", "User", "Password", "MyDatabase", "MySQL")
 
// Connection to a remote MySQL database
ConnectionNum = SQLConnect("192.168.1.51", "User", "Password", "test", "MySQL")

Native MySQL, MariaDB and PHP Connectors

To open a connection in PHP:

to a MySQL database, it is recommended to pass the hNativeAccessMySQL constant as parameter to SQLConnect.
to a MariaDB database, it is recommended to pass the hNativeAccessMariaDB constant as parameter to SQLConnect.

The Native Connector (MySQL or MariaDB) is required to develop the WEBDEV site, but is not necessary for the site to function: you don't need to install the Native Connector at the hosting company.. Indeed, at runtime, it is the MySQL/MariaDB client of the current PHP engine that is used.

Connection to an ISAM database via ACCESS

The following syntax is used to connect to an ISAM database:

SQLConnect(<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 where the connection must be established (the corresponding ISAM driver must have been installed).

Database type	<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 WDMemoBinary WLanguage keyword, used to add or change a binary memo via an SQL query, is not supported by the HFSQL engine and the ACCESS driver. For an HFSQL database, use memo-specific functions.
You can also use:
- the 'UNHEX' SQL function with hexadecimal content
- the WL.Decode function with the encodeBASE64NoCR constant and a string encoded in Base64.

Using a connection with NT authentication

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

SQLConnect(<MyDatabase>, 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.

Connection to an HFSQL database in Java

To connect to an HFSQL database in Java, you must use the following syntax:

<Result> = SQLConnect(<MyAnalysis.WDD>, <User>, <Password>, "", "HYPERFILE")

The following files must be present in the same directory as the generated Java archive: <MonAnalyse.WDD>WD300jav.dll, wd300hf.dll and wd300sql.dll.

For more details, see Java and database.

Component: wd300hf.dll