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:
  - Use an SNC (dbmsnc*) driver either with the Microsoft ODBC driver for SQL Server, or with the Microsoft SQL Native Client driver (msdn.microsoft.com):
    
    For Microsoft ODBC 17 (MSODBCSQL17.DLL), use dbmsnc_17.
    
    For Microsoft ODBC 13 (MSODBCSQL13.DLL), use dbmsnc_13.
    
    For Microsoft SQL Native Client 11 (SQLNCLI11.DLL), use dbmsnc_11.
- On Linux® platforms:
  - With the SNC (dbmsnc*) driver, use the Microsoft ODBC for SQL Server on Linux client (msdn.microsoft.com):
    
    For Microsoft ODBC 17 (libmsodbcsql-17.so), use dbmsnc_17.
    
    For Microsoft ODBC 13 (libmsodbcsql-13.so), use dbmsnc_13.
    
    Minimum Microsoft ODBC for SQL Server on Linux version: 13.0.
  - With the FTM (dbmftm*) driver, use the FreeTDS ODBC client (www.freetds.org, http://www.freetds.org/files/stable/).
    
    Minimum FreeTDS version: 1.00.104.
  - With the ESM (dbmesm*) driver, use the Easysoft ODBC driver for SQL Server (www.easysoft.com).
    
    Minimum Easysoft version 1.5; Version 1.9 is strongly recommended.
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" or "Microsoft SQL Native Client" software installed on the computer running Genero applications (see msdn.microsoft.com).
  On Windows platforms, as the SNC ODI driver uses ODBC32.DLL, there is no need to set the PATH environment variable to a specific database client library path.
  
  On Windows, the MS ODBC database client locale is defined by the Windows regional settings of the application server and must match the BDL application locale (defined by LANG or LC_ALL). Character set conversion (current code set <=> Wide-Char) is done by the SNC ODI driver following the LANG environment variable. If the LANG environment variable is not defined, the application character set defaults to the ANSI code page (ACP).
  
  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 13 shared library.
  
  On Linux, the MS ODBC database client locale is always UTF-16: The dbmsnc ODI driver makes the required character set conversions between the BDL application locale (defined by LANG or LC_ALL) and UTF-16, for the Microsoft ODBC driver for SQL Server. Therefore, no ODBC configuration is required. You just need to set the application locale appropriately.
  Unix ODBCINI sample for MS ODBC driver for SQL Server:
```
[snc_msvtest1_dirac_utf8]
Driver            = /usr/lib64/libmsodbcsql-13.so
Description       = SQL Server ODBC 13 / DIRAC SQL Server 2017 / v14
#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.
  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: 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.
  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. For example, if Client_CSet=BIG5 and the db collation is Chinese_Taiwan_Stroke_BIN, you must set Server_CSet=BIG5HKSCS, otherwise invalid data will be returned from the server.
  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.
On Windows platforms, BDL programs are executed in a CONSOLE environment, not a GUI environment. CONSOLE and GUI environments may use different code pages on your system. Start the "SQL Server Configuration Manager" to setup your client environment and make sure no wrong character conversion occurs. See Microsoft SQL Server documentation for more details.
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. With the SNC driver, set the snc.widechar FGLPROFILE parameter to false, if database columns are defined with the CHAR/VARCHAR/TEXT SQL types, and your application is using a non-UTF-8, multi-byte encoding (typically with BIG5). When using ISO8859-? or UTF-8, do not set this parameter: The expected char mode will be used, depending on the current application locale. 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
```
  Note: 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;"
```
  Note: The source parameter can also be defined at runtime in the database specification of CONNECT TO instruction.