Prepare the runtime environment - connecting to the database

Genero BDL provides several database drivers based on different ODBC clients. This list describes each of them:
Important:
Configure your ODBC data source to use the appropriate Microsoft SQL Server driver.
- On Microsoft® Windows® platforms:
  - With the Microsoft ODBC 17 driver for SQL Server (MSODBCSQL17.DLL), use the dbmsnc_17 ODI driver.
    
    Minimum Microsoft ODBC for SQL Server version: 17.6.
  - With the Microsoft ODBC 18 driver for SQL Server (MSODBCSQL18.DLL), use the dbmsnc_18 ODI driver.
- On Linux® platforms:
  - With Microsoft ODBC 17 driver for SQL Server, use the dbmsnc_17 ODI driver.
    
    Minimum Microsoft ODBC for SQL Server version: 17.6.
  - With Microsoft ODBC 18 driver for SQL Server, use the dbmsnc_18 ODI driver.
  - With FreeTDS ODBC (http://www.freetds.org/files/stable/), use the dbmftm_0 ODI driver.
    
    Minimum FreeTDS version: 1.00.104.
  - With Easysoft ODBC for SQL Server, use the dbmesm_1 ODI driver.
    
    Minimum Easysoft ODBC for SQL Server version: 2.0.19.
Important:
By default, Microsoft ODBC 18 for SQL Server enables connection encryption, that can lead to the following ODBC error when TLS/SSL certificates are not properly configured:
[Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines: tls_process_server_certificate:certificate verify failed:self signed certificate]
To disable encryption on Linux platforms, set the following ODBC option in the data source definition file:
Encrypt = No
On Microsoft Windows, set "Connection Encryption" to "Optional" in the 4th panel of the ODBC data source configuration application.
Check that the Genero distribution package has installed the SQL Server database driver you need. A "dbmsnc", "dbmftm", or "dbmesm" driver must exist in $FGLDIR/dbdrivers.
An ODBC data source must be configured to allow the BDL program to establish connections to SQL Server.
Make sure you select the correct ODBC driver (see step 1).
Important:
When using the FTM (FreeTDS) or ESM (EasySoft) database driver, you have to define the ODBCINI and ODBCINST environment variable to point to the odbc.ini and odbcinst.ini files.
Install and configure the database client software:
1. When using the SNC database driver on Windows, you must have the "Microsoft ODBC for SQL Server" software installed on the computer running Genero applications (see msdn.microsoft.com).
  On Windows platforms, the SNC ODI driver is linked with ODBC32.DLL; There is no need to set the PATH environment variable to a specific database client library path: The ODBC32.DLL driver manager will find the MS ODBC driver from the data source settings.
  
  On Windows, the MS ODBC database client locale is defined by the Windows regional settings for non-unicode applications, and must match the BDL application locale. The BDL application locale is usually also defined by the regional settings (the ACP), but it can be set with the LANG environment variable (typically to .fglutf8). UTF-8 can also be enabled in the Windows regional settings (then LANG does not have to be set to .fglutf8).
  Important:
  The ODBC data source option "Perform translation for character data" must be disabled, when using UTF-8 application locale defined by LANG=.fglutf8, NLS_LENGTH_SEMANTICS=BYTE, and the database has _UTF8 collation for CHAR/VARCHAR columns.
  
  On Linux platforms, the dbmsnc_nn drivers are directly linked to the corresponding libmsodbcsql-nn.so ODBC driver library. There is no need to install the unixODBC software. The SNC drivers will be able to connect to SQL Server, as long as the dynamic linker can find the Microsoft ODBC driver library. The libmsodbcsql-nn.so shared library is a symbolic link located in /usr/lib64, which points to the real ODBC shared library.
  
  On Linux, with the MS ODBC driver, no ODBC configuration is required: Character set conversions and ODBC bindings are automatically deduced from to C application locale (LANG/LC_ALL).
  
  Important:
  On both Linux and Windows, ODBC character string bindings is controlled by the widechar option, which is automatically selected according to the application locale and length semantics. For more details see CHAR and VARCHAR data types.
  Unix ODBCINI sample for MS ODBC driver for SQL Server:
```
[snc_msvtest1_dirac_utf8]
Driver            = /usr/lib64/libmsodbcsql-17.so
Description       = SQL Server ODBC 17
#Server           = [protocol:]server[,port]
Server            = tcp:dirac,1433
Database          = msvtest1
#-- Always Encrypted (Column Encryption)
#  ColumnEncryption = Enabled
#-- Transport encryption with SSL/TLS
#  Encrypt = Yes/No
#  TrustServerCertificate = xxx
#  Trusted_Connection=yes
```
2. When using the FTM database driver, the FreeTDS driver must be installed (see www.freetds.org). There is no need to install unixODBC: The FTM driver is directly linked to libtdsodbc.so.0.
  Make sure the FreeTDS environment variables are properly set. Check for example FREETDS (the path to the configuration file). See FreeTDS documentation for more details.
  
  With the FTM driver, there is no need to install a driver manager like unixODBC: The FTM database driver is linked directly with the libtdsodbc.so shared library. Verify the environment variable (LD_LIBRARY_PATH or equivalent) specifies the search path for that database client shared library.
  
  You must create the odbc.ini and odbcinst.ini files to defined the data source.
  
  Important:
  Set the TDS protocol version depending on the SQL Server version, by setting the tds version parameter in freetds.conf or TDS_Version in odbc.ini. For example, for SQL Server version 2012 and 2014, use TDS_Version=7.3. For more details, see the FreeTDS documentation.
  
  Define the client character set for FreeTDS (client charset parameter in freetds.conf or ClientCharset parameter in odbc.ini). You may need to link FreeTDS with the libiconv library to support character set conversions.
  
  Important:
  On both Linux and Windows, ODBC character string bindings is controlled by the widechar option, which is automatically selected according to the application locale and length semantics. For more details see CHAR and VARCHAR data types.
  Unix ODBCINI sample for FreeTDS driver:
```
[ftm_msvtest1_ida_utf8_2017]
Description     = SQL Server 2017
Server          = ida
Database        = msvtest1
Port            = 1433
TDS_Version     = 7.3
ClientCharset   = UTF-8
#dump_file = /tmp/freetds.log
#dump_file_append = yes
```
  See FreeTDS documentation for more details about installation and data source configuration in ODBC files.
3. When using the ESM database driver, the EasySoft ODBC driver for SQL Server must be installed (see www.easysoft.com). There is no need to install unixODBC: The ESM driver is directly linked to libessqlsrv.so.
  Make sure the EasySoft environment variables are properly set. Check for example EASYSOFT_ROOT (the path to the installation directory). See FreeTDS documentation for more details.
  With the ESM driver, there is no need to install a driver manager like unixODBC. The ESM database driver is linked directly with the libessqlsrv.so shared library. Verify the environment variable (LD_LIBRARY_PATH or equivalent) specifies the search path for that database client shared library.
  You must create the odbc.ini and odbcinst.ini files to define the data source.
  Define the client character set for EasySoft with the Client_CSet parameter in odbc.ini. The client character set is an iconv name and must match the locale of your Genero application.
  
  Note:
  To support all UNICODE characters when using UTF-8 with NCHAR/NVARCHAR columns, you need to define Client_CSet=UTF-8 and Server_UCSet=UTF-16LE.
  When using CHAR/VARCHAR types in the database and when the database collation is different from the client locale, you must also set the Server_CSet parameter to an iconv name corresponding to the database collation. Some examples:
  - If Client_CSet=ISO-8859-15 and the db collation is Latin1_* (=CP1252), you must set Server_CSet=WINDOWS-1252 (otherwise, the characters €, Š, š, Ž, ž, Œ, œ, Ÿ which are encoded differently
  - If Client_CSet=BIG5 and the db collation is Chinese_Taiwan_Stroke_BIN, you must set Server_CSet=BIG5HKSCS.
  Important:
  On both Linux and Windows, ODBC character string bindings is controlled by the widechar option, which is automatically selected according to the application locale and length semantics. For more details see CHAR and VARCHAR data types.
  
  You must also set the following DSN parameters:
  AnsiNPW=Yes
  Mars_Connection=No
  QuotedId=No
  UNIX® ODBCINI sample for EasySoft ODBC for SQL Server driver:
```
[esm_msvtest1_ida_utf8_2017]
Driver=Easysoft ODBC-SQL Server
Description=Easysoft SQL Server ODBC driver
Server=ida
Port=1683
Database=msvtest1
Mars_Connection=No
Logging=No
LogFile=/tmp/odbc.log
#QuotedId=No
AnsiNPW=Yes
Language=
Version7=No
ClientLB=No
Failover_Partner=
VarMaxAsLong=No
DisguiseWide=No
DisguiseLong=No
Trusted_Connection=No
Trusted_Domain=
IPv6=No
Client_CSet=UTF-8
Server_UCSet=UTF-16LE
```
  See EasySoft documentation for more details about installation and data source configuration in ODBC files.
Set up the FGLPROFILE entries for database connections.
1. Define the SQL Server database driver according to the database client used:
```
dbi.database.dbname.driver = { "dbmsnc" | "dbmesm" | "dbmftm" }
```
2. The "source" parameter defines the name of the ODBC source.
```
dbi.database.dbname.source = "test1"
```
3. Force the *.widechar FGLPROFILE parameter only if needed (the appropriated default value is automatically selected). See CHAR and VARCHAR data types for more details.
```
dbi.database.dbname.snc.widechar = false
```
4. If required, define the serial emulation method to "trigseq", when the INSERT statements use all columns of the table, including the serial column. For more details, see SERIAL and BIGSERIAL data types.
```
dbi.database.dbname.ifxemul.datatype.serial.emulation = "trigseq"
```
5. If needed, define the login timeout with the following FGLPROFILE entry:
```
dbi.database.stores.driver-code.logintime = 5
```
6. If needed, define the number of rows to be fetched at once on the application side, for each single FETCH instruction:
```
dbi.database.stores.driver-code.prefetch.rows = 50
```
  The default is 10 rows. This is usually sufficient for regular interactive applications. Increase this parameter only in case of batch programs processing large result sets. The bigger this parameter is, the more memory is used by each program.
7. If needed, add ODBC connection string parameters with the datasource?options notation, in the source parameter of the connection. You can for example define the SQL client application identifier for SQL Server.
```
dbi.database.dbname.source = "test1?APP=myappid;"
```
  The source parameter can also be defined at runtime in the database specification of CONNECT TO instruction.