ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

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
  • Native Oracle Connector: managing the external authentication
  • SQL query (HExecuteSQLQuery or queries created in the query editor)
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Connects the current application to a database that will be queried by SQL through a Webservice proxy (SOAP).
Remarks:
  • 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.
Example
ConnectionNum is int
 
ConnectionNum = SQLConnectWS("http://MyServer/WDSOAPDB_WEB/", ...
"MS Access 97 Database", "", "", "", "ODBC")
IF ConnectionNum <> 0 THEN
// The connection was successful
 ...
ELSE
// The connection failed: displaying an error message
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 Webservice> , <Source> , <User> , <Password> [, <Database name> [, <Type of database> [, <OLE DB provider> [, <Optional information>]]]])
<Result>: Integer
  • 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.
Native Connectors (Native Accesses) MySQL and MariaDB: This parameter corresponds to the name or IP address of the computer containing the database. For example, "Apollon".
Linux 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.
Native Connectors (Native Accesses) 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:
"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:
hODBCOLE DB provider for ODBC. Allows you to access an ODBC source declared in the ODBC data sources of Windows
hOledbAccess97OLE DB provider for Access 97
hOledbAccess2000OLE DB provider for Access 2000
hOledbAccess2007OLE DB provider for Access 2007
hOledbDBase5OLE DB provider for dBase 5
hOledbExcel97OLE DB provider for Excel 97
hOledbExcel2000OLE DB provider for Excel 2000
hOledbExcel2007OLE DB provider for Excel 2007
hOledbLotus4OLE DB provider for Lotus 4
hOledbOracleOLE DB provider for Oracle
hOledbSQLServerOLE 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.
OLE DBNative Connectors (Native Accesses) The keywords recognized by OLE DB and the Native Connectors are presented in Optional connection information.
Remarks
WEBDEV - Server codeAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5OLE DBODBCNative Connectors (Native Accesses)

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

Type of databaseSourceDatabase name
ACCESSName of Access file"" (empty string)
AS400Native AS/400 Connector (optional module)
DB2Native DB2 Connector (optional module)
Source defined in ODBC Administrator
"" (empty string) or Database
HFSQLCSHFSQL Client/Server access
Name of address of the HFSQL C/S server
Name of the database
HYPER FILE
HFSQL
Database name"" (empty string)
INFORMIXNative Informix Connector (optional module)
JDBCURL 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.
HIVEURL for connecting to the Hive server.Database
MySQLNative MySQL Connector (optional module)
Database name
Database
MariaDBNative MariaDB Connector (optional module)
Database name
Database
POSTGRESQLNative PostgreSQL Connector (optional module)
Database name
Database
ODBCSource defined in ODBC Administrator"" (empty string) or Database
OLEDBName of source"" (empty string)
ORACLENative Oracle Connector (optional module)
Name of the alias defined in SQL NET Easy configuration and in WDORAINS
"" (empty string)
PROGRESSNative Progress Connector (optional module)
Source defined in ODBC Administrator
"" (empty string) or Database
SQL AZURENative SQL Azure Connector (optional module supplied with the Native SQL Server Connector)
Server name.
If the name of the server is such as: ServerName.database.windows.net, the login must have the following format: login@ServerName
Database
SQL SERVERNative SQL SERVER Connector (optional module)
Server name
"" (empty string)
SYBASENative SYBASE Connector (optional module)
Name of server or its alias
"" (empty string)
xBaseNative xBase Connector (supplied with the product)
Database available via the browser
(available in Chrome and Safari only)
"" (empty string)
"" (empty string)
Linux The following types of databases are supported: HYPER FILE.
WEBDEV - Server codeAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5OLE DBODBCNative Connectors (Native Accesses)

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).
WEBDEV - Server codeAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5

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>")
For example:
// 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"
// 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 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.
WEBDEV - Server codeAjaxOLE DBODBC

ODBC and OLE DB: Nesting connections

For ODBC and OLE DB, you can nest multiple calls to SQLConnectWS (caution: this is not possible with the HFSQL databases).
The last opened connection is the current connection. SQLChangeConnection is used to modify the current connection.
SQLDisconnect disconnects the current connection.
WEBDEV - Server codeAjaxODBC

Connection to a database using ODBC

To connect to a database using ODBC, follow these steps:
  1. 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.
  2. 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)
Remark: 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.
WEBDEV - Server codeAjaxNative Connectors (Native Accesses)

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")
// 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")
WEBDEV - Server codeAjaxNative Connectors (Native Accesses)

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
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.
WEBDEV - Server codeAjaxOLE DB

Using a connection with NT authentication

The following syntax allows you to establish a connection with NT authentication:
SQLConnectWS(<Server of Webservice>, <MaDatabase>, Null, Null, Null, ...
"OLEDB", <OLE DB Provider>, "Trusted_Connection=YES")
WEBDEV - Server codeNative Connectors (Native Accesses)

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.
WEBDEV - Server codeAjaxHFSQL ClassicHFSQL Client/ServerHyper File 5.5OLE DBODBCNative Connectors (Native Accesses)

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 the HFSQL functions (HDelete, HAdd or HModify) on your data files. The integrity check and the duplicate check will be automatically performed.
Component: wd290hf.dll
Minimum version required
  • Version 19
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help