ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE
Help / WLanguage / Managing databases / HFSQL / HFSQL functions
  • Syntax 1: Opening a preset connection: automatic management of connections
  • Syntax 2: Using a character string for the OLE DB provider
  • Syntax 2 and 3: If a failure occurs, the connection description is not stored
  • Pseudo-connection to HFSQL Classic database
  • Differences between HOpenConnection and HDescribeConnection
  • UDL file (file for describing an OLE DB connection)
  • Native Oracle Connector: managing the external authentication
  • Limitations
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
Opens a connection to a specific database. The connection remains opened until the call to HCloseConnection. HOpenConnection is used to:
  • open a preset connection.
  • define and open a connection. Then, this connection must be assigned to a data file with HChangeConnection.
  • define and open a connection by using a connection string. This syntax can be used with some OLE DB providers who have specific ADO connecting strings.

  • open a connection defined by a Connection variable.
Caution: The order in which the functions for connecting to an HFSQL Client/Server database are called is important. For more details, see Optimizing the connection times to the HFSQL Client/Server databases.
Example
Reports and QueriesWindowsHFSQL ClassicHFSQL Client/ServerOLE DBNative Connectors (Native Accesses)
MyConnection is Connection
// Describe the connection
MyConnection.User = "USER" 
MyConnection.Password = "PASSWORD" 
MyConnection.Server = "MYSERVER" 
MyConnection.Database = "Database" 
MyConnection.Provider = hAccessHFClientServer 
MyConnection.Access = hOReadWrite 
MyConnection.ExtendedInfo = "Extended information" 
MyConnection.CursorOptions = hClientCursor 
HOpenConnection(MyConnection)
Syntax

Opening a preset connection Hide the details

<Result> = HOpenConnection(<Connection>)
<Result>: Boolean
  • True if the operation was performed,
  • False if a problem occurred (during a connection described through programming, some parameters used in HDescribeConnection may be incorrect for example).
HErrorInfo is used to identify the error.
<Connection>: Character string or Connection variable
Connection to a database. This connection corresponds to:
  • the name of the connection defined in the data model editor,
  • the name of the connection defined by HDescribeConnection. The connection description must be known when running the function.
  • the name of a variable of type Connection.

Defining and opening a connection Hide the details

<Result> = HOpenConnection(<New connection> , <User> [, <Password>] , <Data source> [, <Database>] , <OLE DB provider or Native Connector> [, <Access> [, <Extended information> [, <Additional options>]]])
<Result>: Boolean
  • True if the operation was performed,
  • False if a problem occurs. HErrorInfo is used to identify the error. To assign this connection to a file, use HChangeConnection. The connection description will be deleted when closing the connection.
<New connection>: Character string or Connection variable
Name of new connection to define and to open. This name must not be used by another connection (defined in the analysis, or by HOpenConnection or HDescribeConnection).
This parameter corresponds to the Name property used on a variable of type Connection.
<User>: Character string
Username used for the database. This parameter can be modified and retrieved using the User property on a variable of type Connection.
<Password>: Optional character string
Connection password (if this password exists). If no password is required, use an empty string ("") or the NULL keyword. This parameter can be modified using the Password property on a variable of type Connection.
<Data source>: Character string
Full path of the data source. The connection will be created and opened for this data source.
This parameter can be retrieved and modified using the Source property (or Server) on a variable of type Connection.
HFSQL Client/Server This parameter corresponds to the server name. The port number used by the HFSQL server can be specified. The port 4900 will be used by default. If the port number of the HFSQL server is different, use the following notation:
<Computer name>: <Port number>
Example: "ComputerName:4901".
For a use via Internet, the ports must be managed by Firewall. For more details, see Managing ports. You have the ability to specify an IPv6 address by writing the IPv6 address between square brackets. In this case, the port number must be specified after the closing square bracket. For example: [0154:2145:2155::5554]:4900.
<Database>: Optional character string
Name of database that will be used on the data source. If this parameter is not required, use an empty string ("").
This parameter can be retrieved and modified using the Database property on a variable of type Connection.
In the case of a database divided into schemas, the name of the schema in the database will be specified by HChangeName.
This database will be automatically created if it does not exist.
Native Connectors (Native Accesses) DB2: This parameter is not required.
<OLE DB provider or Native Connector>: Character string or constant
  • Name of OLE DB provider used. To easily retrieve the string to use, select "Connection" in the data model editor (see the Notes).
    or
  • one of the following constants:
    hAccessHF7Pseudo-connection to HFSQL Classic database.
    hAccessHFClientServerNative Connector to an HFSQL Client/Server database.
    hNativeAccessAS400Native AS/400 Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessDB2Native DB2 Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessInformixNative Informix Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessMariaDBNative MariaDB Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessMySQLNative MySQL Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessOracleNative Oracle Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessOracleLiteNative Oracle Connector (optional module of WINDEV Mobile).
    hNativeAccessPostgreSQLNative PostgreSQL Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessProgressNative Progress Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessSQLAzureNative SQL Azure Connector (optional module of WINDEV/WEBDEV, supplied with Native SQL Server Connector).
    hNativeAccessSQLiteNative SQLite Connector (provided with WINDEV/WEBDEV).
    hNativeAccessSQLServerNative SQL Server Connector (optional module of WINDEV/WEBDEV)
    The former Native Connector is used by default. To use the new Native SQL Server Connector, use the H.SQLServerMode variable.
    hNativeAccessSQLServerMobileNative SQL Server Mobile Connector (optional module of WINDEV Mobile).
    hNativeAccessSybaseNative Sybase Connector (optional module of WINDEV/WEBDEV).
    hNativeAccessXBaseNative xBase Connector (provided with WINDEV/WEBDEV).
    hNativeAccessXMLNative XML Connector (supplied with WINDEV/WEBDEV).
    hODBCOLE DB provider for ODBC (used to access an ODBC data 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.
    hOledbAccess2010OLE DB provider for Access 2010.
    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


OLE DB Caution: To use an OLE DB connection, you must:
  • install MDAC version 2.6 or later (setup performed by WINDEV/WEBDEV and when creating the client version).
  • install the OLE DB provider corresponding to the database used.
    This parameter is returned by the CtOLEDBProvider property.
This parameter can be retrieved and modified using the Provider property on a variable of type Connection.
<Access>: Optional constant
Configures the type of access to the database.
hOReadThe database can be accessed in read-only. You can only read records. No record can be added or deleted.
HFSQL Client/Server This constant is ignored.
hOReadWrite
(default value)		The database can be accessed in read/write. You can read and write new records.
This parameter can be retrieved and modified using the Access property on a variable of type Connection.
<Extended information>: Optional character string
Optional information inserted into the string for connecting to the database. If specific keywords are recognized, the corresponding information is extracted from the connection string. The rest of the connection string is sent to the database. Then, the extracted parameters are processed by the Native Connector or by the OLE DB connector used.
These keywords must be indicated as follows: "<Keyword> = <Value>;".
You can for example specify "Trusted_Connection=YES" in order to use a connection with authentication via the NT login.
OLE DBNative Connectors (Native Accesses) The keywords recognized by OLE DB and the Native Connectors are displayed on Optional connection information.
Example of string to use:
"WD Cache Size = 10000; Server Port=3306;"
HFSQL Client/Server Recognized keywords for the access via HFSQL Client/Server:
COMPRESSIONEnables or disables the compression of data exchanged between Client and Server. This keyword can correspond to:
  • TRUE to enable the data compression.
  • FALSE to disable the data compression.
DB_EXISTChecks the existence of the database. If the specified database does not exist, this database is not created and HOpenConnection returns False.
ENCRYPTIONEnables the encryption of data exchanged between Client and Server. This keyword can take one of these values:
  • FAST
  • RC5_16 to use 16-round RC5 encryption.
  • AES128 to use 128-bit AES encryption with the OFB mode of operation.
  • AES192 to use 192-bit AES encryption with the OFB mode of operation.
  • AES256 to use 256-bit AES encryption with the OFB mode of operation.

This parameter can be retrieved and modified using the ExtendedInfo property.
<Additional options>: Optional constant
If this parameter is not specified, the HFSQL engine automatically defines the best parameters to use according to the database accessed and to the OLE DB provider used.
By default, this parameter corresponds to the following combination of constants:
hServerCursor + hOpenKeysetCursor + hOptimisticCursor
However, these parameters can differ according to the information returned by the database.
To force some parameters, you have the ability to combine the following constants:
  • Location of cursors
    hClientCursorCursor managed by MDAC. Offers more features than a server cursor. A client cursor must necessarily be a static cursor.
    hServerCursorCursor managed by the database. This cursor can easily apply the changes performed by the other users to the data.
  • Type of cursor
    hDynamicCursorCursor offering a lot of features but consuming a lot of memory. This cursor allows all types of moves. It allows you to access all the modifications, additions and deletions performed by the other users.
    hForwardOnlyCursorFast cursor consuming little memory. The move is performed from the first record to the last one. The data modified by the other users is not visible.
    hOpenKeysetCursorCursor that handles a fixed set of records. Only the keys corresponding to the record are stored and not the records (consumes less memory). The deleted records can no longer be accessed. Modifications performed by other users are visible (but records added are not).
    hStaticCursorMakes a copy of all query records in memory (high memory usage). All types of moves are allowed.
  • Lock mode
    hOptimisticCursorThe record is locked when it is updated
    hPessimisticCursorThe record is locked as soon as it goes into edit mode to be modified.
    hReadOnlyCursorThe set of records cannot be modified.
Caution: some types of cursors may not be supported by all the OLE DB providers.
This parameter can be retrieved and modified using the CursorOptions property.
Reports and Queries

Defining and opening a connection by defining the connection string Hide the details

<Result> = HOpenConnection(<Connection> , <ADO connection string> [, <Access>])
<Result>: Boolean
  • True if the operation was performed,
  • False if a problem occurs. HErrorInfo is used to identify the error. The connection description will be deleted when closing the connection.
<Connection>: Character string
Name of new connection to define and to open. This name must not be used by another connection (defined in the analysis, or by HOpenConnection or HDescribeConnection).
<ADO connection string>: Character string
ADO connection string to use. For more details on the syntax of this connection string, see the documentation of the OLE DB provider used.
<Access>: Optional constant
Configures the type of access to the database.
hOReadThe database can be accessed in read-only. You can only read records. No record can be added or deleted.
hOReadWrite
(default value)		The database can be accessed in read/write. You can read and write new records.
Remarks
Reports and QueriesHFSQL ClassicHFSQL Client/ServerOLE DBNative Connectors (Native Accesses)

Syntax 1: Opening a preset connection: automatic management of connections

  • The connection associated with a data file is automatically opened when the data file is opened. The connection is closed when the last data file that uses this connection is closed.
  • Any connection opened by HOpenConnection remains open even if no data file uses it.
  • If the application opens and closes data files many times in a row, the automatic management of connections will open/close connections many times, slowing down the application. In this case, it is recommended to use HOpenConnection to keep the connection open.
Reports and QueriesHFSQL ClassicHFSQL Client/ServerOLE DBNative Connectors (Native Accesses)

Syntax 2: Using a character string for the OLE DB provider

To easily find the character string that must be used to define the OLE DB provider, we recommend that you proceed as follows:
  1. In the analysis editor, create an HFSQL Client/Server connection, for example.
  2. In the connection description screen, select the provider to be used (in "Connection by").
  3. The character string corresponding to the provider is displayed. This character string can be used in the syntax 2 instead of constants.
Using a character string for the OLE DB provider
Reports and QueriesHFSQL ClassicHFSQL Client/ServerOLE DBNative Connectors (Native Accesses)

Syntax 2 and 3: If a failure occurs, the connection description is not stored

If the connection fails to open, the description of this connection is not validated. This allows you to redefine a connection with the same name.
For example, if the attempt to connect with the default password fails, you have the ability to perform a new attempt with another password.
Reports and QueriesHFSQL Classic

Pseudo-connection to HFSQL Classic database

OLE DB data files or native data files are described in the data model editor. To handle these descriptions with an HFSQL Classic database, simply specify the hAccessHF7 constant as provider name.
The data files in HFSQL Classic format corresponding to the description of the OLE DB or native files will be created (if necessary) in the directory defined by <Data source>.

Differences between HOpenConnection and HDescribeConnection

Method 1: To open a connection at the start of the application and close it at the end, use HOpenConnection. To use this connection when handling data files or tables, simply change this connection with HChangeConnection.
HOpenConnection(MyConnection, params) // Open the connection
HChangeConnection(AFile, MyConnection)
Method 2: To describe a connection through programming and to automatically open it when the file is opened, you must:
  • describe the connection with HDescribeConnection
  • associate this connection with the desired data file description using HChangeConnection. The connection will only be opened the first time the data file is accessed.
This solution can be useful when an application handles both HFSQL data files and Oracle data files. For example, if the application uses a single Oracle file, in a specific process, the connection to the Oracle database will only be made if necessary.
If the first solution is chosen for the same type of application, the connection (that may take quite a long time) will be established whenever the application is started.
Example:
HDescribeConnection(MyConnection, params) 
HChangeConnection(AFile, MyConnection) 
...
HReadFirst(AFile) // Open the connection
...
HClose(AFile) // Close the connection
Reports and QueriesOLE DB

UDL file (file for describing an OLE DB connection)

To create a UDL file:
  1. Create a text file whose extension is "UDL".
  2. Double-click this file. The window for the properties of the data links is opened.
  3. Configure the connection.
  4. Run the test of the connection.
  5. Confirm ("OK" button). The UDL file can now be used.
Native Connectors (Native Accesses)

Native Oracle Connector: managing the external authentication

To connect via an external authentication, the following connection parameters must be used:
  • <Username> = / (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.

Limitations

    Component: wd300hf.dll
    See also
    Minimum version required
    • Version 9
    This page is also available for…
    Comments
    //Exmplo de garantia de conexao e disponibilidade
    //Exmplo de garantia de conexao e disponibilidade

    If ping(“200.38.56.1”, 5s) = True

    //Deu certo log ok

    If Hopenconnection = True
    If Hchangeconnection = True

    Executa aqui teu código

    Else

    //log erro

    End

    Else

    // log erro

    End

    Else // Ping

    //Bug log
    Log.evento = “consumo de dados”
    Log.datahora = datesys()+Timesys()
    Log.descricao = herrorInfo() + cr + errorinfo()
    Hadd(log)

    End
    Boller
    11 May 2024
    Exemplo com porta diferente
    GLOBAL
    Banco is string = INIRead("Database","Banco","",fCurrentDir()+"\ConfigDB.ini")
    DNS_IP is string = INIRead("Database","DNS-IP","",fCurrentDir()+"\ConfigDB.ini")
    Porta is string = INIRead("Database","Porta","",fCurrentDir()+"\ConfigDB.ini")
    Usuario is string = INIRead("Database","Usuario","",fCurrentDir()+"\ConfigDB.ini")
    Senha is string = INIRead("Database","Senha","",fCurrentDir()+"\ConfigDB.ini")

    IF Usuario = "" AND Senha = "" THEN
    Open(WIN_ConfigDatabase)
    END

    MyHfsql01 is Connection
    MyHfsql01..Provider = hAccessHFClientServer
    MyHfsql01..Database = Banco
    MyHfsql01..Server = DNS_IP
    MyHfsql01..Source = DNS_IP +":"+ Porta
    MyHfsql01..User = Usuario
    MyHfsql01..Password = Senha


    HModifyStructure("*")
    HCreationIfNotFound("*")
    IF HOpenConnection(MyHfsql01) = True THEN

    IF HChangeConnection("*",MyHfsql01) = True THEN
    ToastDisplay("Conexao com sucesso!",toastLong,vaMiddle,haCenter)
    ELSE
    Error(HErrorInfo())
    END

    ELSE
    Error(HErrorInfo())
    END
    Boller
    25 Oct. 2022
    Conexao PostgreSQL com porta diferente da Padrão
    PROCEDURE ConexaoPostgreSQL()

    ok1, ok2 is boolean

    INIWrite("Config","HOST",EDT_Host,fCurrentDir()+"\Config.ini")
    INIWrite("Config","PORT",EDT_Porta,fCurrentDir()+"\Config.ini")
    INIWrite("Config","DBAS",EDT_DB,fCurrentDir()+"\Config.ini")
    INIWrite("Config","USER",EDT_User,fCurrentDir()+"\Config.ini")
    INIWrite("Config","PASS",EDT_Pass,fCurrentDir()+"\Config.ini")

    MyConnect is Connection
    MyConnect..Provider = hNativeAccessPostgreSQL
    MyConnect..Server = EDT_Host
    MyConnect..Source = EDT_Host//+";"+val(EDT_Porta)
    MyConnect..Database = EDT_DB
    MyConnect..User = EDT_User
    MyConnect..Password = EDT_Pass
    MyConnect..ExtendedInfo = "WD Cache Size = 10000; Server Port="+Val(EDT_Porta)+";"

    IF HOpenConnection(MyConnect) = True THEN
    ok1 = True
    END
    IF HChangeConnection("*",MyConnect) = True THEN
    ok2 = True
    END

    IF ok1 = True AND ok2 = True THEN
    ToastDisplay("Conectou com Sucesso no PostgreSQL!",toastLong,vaMiddle,haCenter,RGB(0,0,255))
    End
    Boller
    10 Jul. 2020
    HCS.Timeout & HCS.FrameSize
    A variável HCS.Timeout é usada para modificar o tempo limite da conexão:
    Para as conexões estabelecidas pelo HOpenConnection .
    Para as aberturas de conexão automática.
    Nota : A variável HCS.Timeout é ignorada para os acessos ao servidor que não seja a conexão. Um pedido cuja execução é maior do que o valor especificado na variável HCS.Timeout não seria interrompida, por exemplo. Esse tempo limite deve ser especificado no aplicativo cliente, antes que uma conexão seja aberta no servidor.
    Exemplo

    HCS.Timeout = INTRANET

    A variável HCS.FrameSize é usada para redimensionar o quadro de dados de saída. Em alguns casos, o redimensionamento dos quadros pode ser usado para adaptar o fluxo de dados de saída e de entrada aos desempenhos do hardware usado. Por exemplo, se a conexão entre os computadores clientes e o mecanismo HFSQL Client / Server for através de redes de configuração (placa de rede, switch, roteador, vpn, ...)

    HCS.FrameSize = hInternetFrame
    BOLLER
    28 Aug. 2017
    Exemplo de uso do driver nativo MS SQL SERVER
    //Conexao
    ok, ok1, ok2, ok3 is boolean

    myConn is Connection
    myConn..Provider = hNativeAccessSQLServer
    myConn..User = "adriano"
    myConn..Password = "adriano@2016"
    myConn..Source = "192.168.0.162\projeto01"
    myConn..Database = "projeto01"
    myConn..Access = hOReadWrite

    ok1 = HOpenConnection(myConn)
    IF ok1 = False THEN
    Info(ErrorOccurred() +" - "+ HErrorInfo())
    END


    IF gpwInitAnalysis() = False THEN
    Error(ErrorInfo())
    ok2 = False
    ELSE

    ok2 = True
    IF gpwOpen() = gpwOk
    ok3 = True
    ELSE
    Info(ErrorOccurred() +" - "+ HErrorInfo())
    ok3 = False
    END

    END

    NextTitle("Atenção")

    IF ok1 = True AND ok2 = True AND ok3 = True THEN //deve dar tudo ok

    Info("Conectou com sucesso no banco de dados!")

    ELSE

    Info("Falha ao tentar conectar no Servidor")

    END
    BOLLER
    28 Nov. 2016

    Last update: 01/09/2024

    Send a report | Local help