- 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 by 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
- Native Oracle Connector: managing the external authentication
- SQL query (HExecuteSQLQuery or queries created in the query editor)
- Connection to an HFSQL database in Java
SQLConnect (Function) In french: SQLConnecte Connects the current application to a database that will be interrogated by SQL. Remark: From version 19, HFSQL is the new name of HyperFileSQL. Versions 21 and later New in version 21 Syntax
<Result> = SQLConnect(<Source> , <User> , <Password> [, <Database name> [, <Database type> [, <OLE DB provider> [, <Optional information>]]]])
<Result>: Integer - Connection identifier: 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. <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.
<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. See the Remarks 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.
<Database type>: Optional character string Type of accessed database. The types of databases installed on the current computer are returned by SQLListSource. See remarks for more details. <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. | hOledbAccess2007 | OLE DB provider for Access 2007. | hOledbAccess2000 | OLE DB provider for Access 2000. | Versions 19 and laterhOledbAccess2010 New in version 19hOledbAccess2010 hOledbAccess2010 | OLE DB provider for Access 2010. | hOledbDBase5 | OLE DB provider for dBase 5. | hOledbExcel2007 | OLE DB provider for Excel 2007. | hOledbExcel2000 | OLE DB provider for Excel 2000. | hOledbExcel97 | OLE DB provider for Excel 97. | 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.
Remarks Lookup table between the different types of databases, their sources and their databases | | | Database type | Source | Database name |
---|
ACCESS | Name of 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
Versions 21 and laterHFSQL New in version 21HFSQL HFSQL | Database name | "" (empty string) | HFSQLCS | Name or address of server | 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. | Versions 21 and laterHIVE New in version 21HIVE HIVE | URL for connecting to the Hive server. | Database | Versions 20 and laterMariaDB New in version 20MariaDB 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 | Versions 16 and laterSQL AZURE New in version 16SQL AZURE SQL AZURE | Native 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 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 a browser (available in Chrome and Safari only) "" (empty string) | "" (empty string) |
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).
Versions 18 and later New in version 18
This page is also available for…
|
|
|
| |
| 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() // <-------- |
|
|
|
| |
| |
| |
| |
| |
| |
| | |
|