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" 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).
  
  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, 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: 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. 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.
  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.
  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. 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, multibyte 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.