PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Table of correspondence between the different types of databases, their sources and their databases
  • Failure or success of connection
  • Connection to a HFSQL analysis
  • ODBC and OLE DB: Nesting connections
  • Connection to a database by ODBC
  • Native MySQL, MariaDB and PostgreSQL Accesses
  • Native MySQL, MariaDB Accesses and PHP
  • Connection to an ISAM database via ACCESS
  • Using a connection with NT authentication
  • Native Oracle Access: managing the external authentication
  • SQL query (HExecuteSQLQuery or queries created in the query editor)
  • Connection to a HFSQL database in Java
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Connects the current application to a database that will be interrogated by SQL.
PHP The SQL functions are used to handle the MySQL databases and the databases accessible by ODBC (HFSQL for example).
PHP The SQL functions are used to handle the MySQL and MariaDB databases and the databases accessible by ODBC (HFSQL for example).
Linux SQLConnect is used to connect to a HFSQL Classic database, to a HFSQL Client/Server database or to a database accessible via ODBC.
Java See Java and database for more details.
Versions 16 and later
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) for more details.
New in version 16
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) for more details.
WEBDEV - Browser code This function is now available in Browser code.
WEBDEV - Browser code The SQL functions are used to handle the local databases (such as Web SQL databases). Only the SQLFetch/SQLGetCol browse mode is available. See Accessing a database in local mode (SQLite) for more details.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
Versions 21 and later
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
New in version 21
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
Universal Windows 10 App This function is now available in Universal Windows 10 App mode.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)ODBC
// 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 picker
// 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
// The connection failed: display 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()
WINDEVWEBDEV - Server codePHPAjaxUser code (UMC)Native Accesses
// Connection to a MySQL database on the apollon computer with the "superv" login
ConnectionNum is int
ConnectionNum = SQLConnect("apollon", "superv", "", "test")
WINDEVJavaUser code (UMC)
// Connection to a MySQL database by using a JDBC driver:
SQLConnect("jdbc:mysql://" + myServer + "/" + mydatabase, "myself", "mypwd", "", ...
"JDBC", "com.mysql.jdbc.Driver")

// Connection by using an ODBC database:
SQLConnect("jdbc:odbc:MySource", "myself", "mypwd", "", "JDBC", "sun.jdbc.odbc.JdbcOdbcDriver")

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

// Connection by HFSQL with a database in client/server:
SQLConnect("ServerName:port", "User", "Password", "DatabaseName", "HFSQLCS")
Syntax
<Result> = SQLConnect(<Source> , <User> , <Password> [, <Database Name> [, <Database Type> [, <OLE DB Provider> [, <Optional Information>]]]])
<Result>: Integer
  • Identifier of connection: this identifier is used by SQLChangeConnection.
  • 0 if an error occurred. To find out the error details, use SQLInfo.
<Source>: Character string (with quotes)
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). See remarks for more details.
PHPNative Accesses MySQL: This parameter corresponds to the name or IP address of the computer containing the database. For example, "Apollon".
PHPNative Accesses MySQL and MariaDB: This parameter corresponds to the name or IP address of the computer containing the database. For example, "Apollon".
Java URL for connecting to the database. This URL is specific to each driver. For example:
  • Connection to a MySQL database via a JDBC driver: "jdbc:mysql://" + myServer + "/" + mydatabase
  • Connection by using an ODBC database: "jdbc:odbc:MySource"
  • Connection by using HFSQL: "MyAnalysis.WDD"
Linux The source can be a HFSQL Classic or HFSQL Client/Server database.
Versions 16 and later
WEBDEV - Browser code This parameter corresponds to an empty string ("").
New in version 16
WEBDEV - Browser code This parameter corresponds to an empty string ("").
WEBDEV - Browser code This parameter corresponds to an empty string ("").
<User>: Character string (with quotes)
User Name. This name is optional for some data sources: in this case, use an empty string ("") for this parameter.
Versions 16 and later
WEBDEV - Browser code This parameter is ignored.
New in version 16
WEBDEV - Browser code This parameter is ignored.
WEBDEV - Browser code This parameter is ignored.
<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.
Versions 16 and later
WEBDEV - Browser code This parameter is ignored.
New in version 16
WEBDEV - Browser code This parameter is ignored.
WEBDEV - Browser code This parameter is ignored.
<Database Name>: Optional character string
Name of the database to use. See the Notes for more details.
  • 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.
PHPNative Accesses MySQL: With Native MySQL Access, the database name is mandatory.
PHPNative Accesses MySQL and MariaDB: With Native MariaDB Access, the database name is mandatory.
Java 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 database accessed. The types of databases installed on the current computer are returned by SQLListSource. See remarks for more details.
Java Only the HFSQL and JDBC databases are accessible in this version. The databases accessed via the JDBC driver are reserved to a Java application
Versions 16 and later
WEBDEV - Browser code Only the databases available in browser code are accessible.
New in version 16
WEBDEV - Browser code Only the databases available in browser code are accessible.
WEBDEV - Browser code Only the databases available in browser code 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:
hODBCOLE DB provider for ODBC. Used to access an ODBC source declared in the ODBC data sources of Windows.
hOledbAccess97OLE DB provider for Access 97.
hOledbAccess2007OLE DB provider for Access 2007.
hOledbAccess2000OLE DB provider for Access 2000.
Versions 19 and later
hOledbAccess2010
New in version 19
hOledbAccess2010
hOledbAccess2010
OLE DB provider for Access 2010.
hOledbDBase5OLE DB provider for dBase 5.
hOledbExcel2007OLE DB provider for Excel 2007.
hOledbExcel2000OLE DB provider for Excel 2000.
hOledbExcel97OLE DB provider for Excel 97.
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.
Java Full name of the JDBC driver to use.
PHP This parameter is ignored.
Versions 16 and later
WEBDEV - Browser code This parameter is ignored.
New in version 16
WEBDEV - Browser code This parameter is ignored.
WEBDEV - Browser code 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.
OLE DBNative Accesses The keywords recognized by OLE DB and the native accesses are presented in the page named Optional connection information.
Java This parameter is ignored. The optional information must be specified in the connection URL. This URL is specific to each JDBC driver.
PHP This parameter is ignored.
Versions 16 and later
WEBDEV - Browser code This parameter is ignored.
New in version 16
WEBDEV - Browser code This parameter is ignored.
WEBDEV - Browser code This parameter is ignored.
Remarks

Table of correspondence between the different types of databases, their sources and their databases

Database TypeSourceDatabase Name
ACCESSName of Access file"" (empty string)
AS400Native AS/400 access (optional module)
DB2Native DB2 access (optional module)
Source defined in ODBC Administrator.
"" (empty string) or Database
HYPER FILEHYPER FILE
Versions 21 and later
HFSQL
New in version 21
HFSQL
HFSQL
Database name"" (empty string)
HFSQLCSName or address of serverDatabase name
INFORMIXNative Informix Access (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.
Versions 21 and later
HIVE
New in version 21
HIVE
HIVE
URL for connecting to the Hive server.Database
Versions 20 and later
MariaDB
New in version 20
MariaDB
MariaDB
Native MariaDB Access (optional module)
Database name
Database
MySQLNative MySQL Access (optional module)
Database name
Database
POSTGRESQLNative PostgreSQL Access (optional module)
Database name
Database
ODBCSource defined in ODBC Administrator"" (empty string) or Database
OLEDBName of source"" (empty string)
ORACLENative Oracle Access (optional module)
Name of the alias defined in SQL NET Easy configuration and in WDORAINS.
"" (empty string)
PROGRESSNative Progress Access (optional module)
Source defined in ODBC Administrator.
"" (empty string) or Database
Versions 16 and later
SQL AZURE
New in version 16
SQL AZURE
SQL AZURE
Native SQL Azure Access (optional module supplied with the Native SQL Server Access)
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 Access (optional module)
Server name.
"" (empty string)
SYBASENative SYBASE Access (optional module)
Name of server or its alias.
"" (empty string)
xBaseNative xBase Access (supplied with the product)
Versions 16 and later
WEBDEV - Browser code "Web SQL database"
New in version 16
WEBDEV - Browser code "Web SQL database"
WEBDEV - Browser code "Web SQL database"
Database available via the browser
(available in Chrome and Safari only)
"" (empty string)
"" (empty string)
PHP The following types of databases are supported: MySQL, ODBC, HYPER FILE, ORACLE or POSTGRESQL. We recommend that you use the corresponding hAccessxxx constant.
PHP The following types of databases are supported: MySQL, MariaDB, ODBC, HYPER FILE, ORACLE or POSTGRESQL. We recommend that you use the corresponding hAccessxxx constant.
Java The following types of databases are supported: JDBC, HYPER FILE.
Linux The following types of databases are supported: 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).
PHP The disconnection is not required if the connection failed.
WINDEVWEBDEV - Server codeReports and QueriesJavaAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5

Connection to a HFSQL analysis

To connect to a HFSQL analysis, use the following syntax:
SQLConnect("<Drive>:<Full Path of Analysis WDD>", "", "<Analysis Password>")
For example:
SQLConnect("D:\WINDEV\WDSTOCK\WDSTOCK.WDD", "", "")
Notes:
  • After the connection to a 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 was specified for the analysis, this password must be indicated in the third parameter of SQLConnect.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)OLE DBODBC

ODBC and OLE DB: Nesting connections

For ODBC and OLE DB, several calls to SQLConnect can be nested (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.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)ODBC

Connection to a database by ODBC

To connect to a database by ODBC, the following operations must be performed:
  1. Configuring the ODBC data source in the ODBC administrator (on the development computer or on the deployment computer).
  2. Using SQLConnect with the "ODBC" parameter.
Note: 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> = SQLConnect(<Source>, <User>, <Password>, ...
<Database Name>, "OLE DB", hODBC)
Note: The SQL.ODBCHandle variable is used to find out the handle of the ODBC connection for the other SQL functions on ODBC. This variable is filled during the last use of SQLConnect. SQL.ODBCHandle is set to -1 for the other connections.
WINDEVWEBDEV - Server codePHPAjaxUser code (UMC)Native Accesses

Native MySQL, MariaDB and PostgreSQL Accesses

To open a connection to a MySQL, MariaDB or PostgreSQL database via the native access, the following elements must be passed in parameter to SQLConnect:
  • the type of the database used, MySQL, MariaDB or POSTGRESQL.
  • the database name: it corresponds to the name given by the administrator of the MySQL/MariaDB/PostgreSQL database.
Note: The name of the MySQL or PostgreSQL data source can be replaced by 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 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")
Versions 16 and later
Note: The native MariaDB access is available from version 20.
New in version 16
Note: The native MariaDB access is available from version 20.
Note: The native MariaDB access is available from version 20.
WEBDEV - Server codePHPAjaxUser code (UMC)Native Accesses

Native MySQL, MariaDB Accesses and PHP

To open a connection in PHP:
  • to a MySQL database, we recommend that you pass the hNativeAccessMySQL constant in parameter to SQLConnect.
  • Versions 20 and later
    to a MariaDB database, we recommend that you pass the hNativeAccessMariaDB constant in parameter to SQLConnect.
    New in version 20
    to a MariaDB database, we recommend that you pass the hNativeAccessMariaDB constant in parameter to SQLConnect.
    to a MariaDB database, we recommend that you pass the hNativeAccessMariaDB constant in parameter to SQLConnect.
The Native Access (MySQL or MariaDB) is required to develop the WEBDEV site but it is not required for the site to operate: there is no need to install the native access at the hosting company. Indeed, at run time, it is the MySQL/MariaDB client of the current PHP engine that is used.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)Native Accesses

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>User Name. 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
Notes:
  • In order for the requested connection to be established, the corresponding ISAM driver must have have 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.
WINDEVWEBDEV - Server codeReports and QueriesAjaxUser code (UMC)OLE DB

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")
Versions 18 and later
WINDEVWEBDEV - Server codeNative Accesses

Native Oracle Access: managing the external authentication

To connect via an external authentication, the following connection parameters must be used:
  • <User> = / (slash)
  • <Password> = "" (empty string)
Note: The external authentication consists in using the name of Windows user and his password to connect to the database. The external authentication requires a setting of the server. See the documentation about Oracle to find out how to authorize the external authentications on the server.
New in version 18
WINDEVWEBDEV - Server codeNative Accesses

Native Oracle Access: managing the external authentication

To connect via an external authentication, the following connection parameters must be used:
  • <User> = / (slash)
  • <Password> = "" (empty string)
Note: The external authentication consists in using the name of Windows user and his password to connect to the database. The external authentication requires a setting of the server. See the documentation about Oracle to find out how to authorize the external authentications on the server.
WINDEVWEBDEV - Server codeNative Accesses

Native Oracle Access: managing the external authentication

To connect via an external authentication, the following connection parameters must be used:
  • <User> = / (slash)
  • <Password> = "" (empty string)
Note: The external authentication consists in using the name of Windows user and his password to connect to the database. The external authentication requires a setting of the server. See the documentation about Oracle to find out how to authorize the external authentications on the server.
WINDEVWEBDEV - Server codeAjaxUser code (UMC)HFSQLHFSQL Client/ServerHyper File 5.5OLE DBODBCNative 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 a 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.
Java

Connection to a HFSQL database in Java

To connect to a HFSQL database in Java, you must use the following syntax:
<Result> = SQLConnect(<MyAnalysis.WDD>, <User>, <Password>, "", "HYPERFILE")
The following files and the generated Java archive must be found in the same directory: <MyAnalysis.WDD>, WDxxxjav.dll, wdxxxhf.dll and wdxxxsql.dll.
See Java and database for more details.
Components
WINDEVWEBDEV - Server codeReports and Queries wd230hf.dll
WEBDEV - Browser code WDJS.DLL
Java wd230java.jar
Linux wd230hf.so
Minimum required version
  • Version 9
This page is also available for…
Comments
Example with SQLConnect/SQLExec/SQLDisconnect
ConnectionNum is int
SourceName is string
// Connection to a specific data source via ODBC MS ACCESS
SourceName = "MS Access 97 Database"
ConnectionNum = SQLConnect(SourceName, "", "", "", "ODBC") // <--------
IF ConnectionNum <> 0 THEN
// The connection was successful

// Run the query and retrieve the result line by line
i is int = 0
SQLExec("SELECT LASTNAME, FIRSTNAME, EXTENSION, PHOTO FROM CUSTOMER", "QRY1") // <--------
WHILE SQLFetch("QRY1") = 0 // There is still another line to read
i++
// Retrieve the data
LASTNAME[i] = SQLGetCol("QRY1", 1)
FIRSTNAME[i] = SQLGetCol("QRY1", 2)
EXTENSION[i] = SQLGetCol("QRY1", 3)
{"IMAGE"+i} = SQLGetMemo("QRY1", 4)
END
SQLClose("QRY1")

ELSE
// The connection failed: display 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() // <--------
BOLLER
Sep. 11 2018