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:

For Windows™ platforms, use the SNC database driver based on SQL Server Native Client ODBC driver (SQLNCLI*.DLL) for Microsoft™ SQL SERVER 2005 and higher. Make sure that the dbmsnc* driver matches the SQNCLI*.DLL.

The SNC driver is supported starting from Genero 2.10.
For Unix platforms, Genero supports the FTM driver is based on the FreeTDS ODBC client (www.freetds.org).

This driver can be used with FreeTDS to connect from a UNIX™ platform to a Windows platform running SQL SERVER.

You need at least FreeTDS version 0.83, recommended version is 0.95 to connect to recent SQL Server versions such as 2014.

The FTM driver is supported starting from Genero 2.11.
For Unix platforms, Genero supports the ESM driver is based on the EasySoft ODBC driver for SQL Server (www.easysoft.com).

This driver can be used with EasySoft to connect from a UNIX platform to a Windows platform running SQL SERVER.

You need at least EasySoft version 1.2.3.

The ESM driver is supported starting from Genero 2.21.

Check that the Genero distribution package has installed the SQL SERVER database driver you need (i.e. 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 ( SNC = "SQL Server Native Client", FTM = "FreeTDS", ESM = "EasySoft").

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:

When using the SNC database driver, you must have the "Microsoft SQL SERVER Native Client" software installed on the computer running Genero applications.
Since the SNC driver is using ODBC32.DLL, there is no need to set the PATH environment variable to a specific database client library path.

The database client locale is defined by the regional settings of the application server and must match the locale used by the BDL application. Character set conversion (current code set <=> Wide-Char) is done by the SNC ODI driver according to the LANG environment variable. If the LANG environment variable is not defined, the application character set defaults to the ANSI code page (ACP).
When using the FTM database driver, you must install FreeTDS ( www.freetds.org ).
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 defining the search path for that database client shared library (LD_LIBRARY_PATH or equivalent).

You must create the odbc.ini and odbcinst.ini files to defined the data source.

Do not forget to 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: You must set the TDS protocol version according to the SQL Server version (2005, 2008, etc), by setting the tds version parameter in freetds.conf or TDS_Version in odbc.ini. For example, when using SQL Server 2005, you must use the TDS protocol version 7.1. For SQL Server version 2008, 2012 and 2014, use TDS_Version=7.3.

See FreeTDS documentation for more details about installation and data source configuration in ODBC files.
When using the ESM database driver, you must install EasySoft ODBC for SQL Server (www.easysoft.com).
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 defining the search path for that database client shared library (LD_LIBRARY_PATH or equivalent)

You must create the odbc.ini and odbcinst.ini files to defined the data source.

Do not forget to 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.

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

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.

Define the SQL Server database driver according to the database client used:
```
dbi.database.dbname.driver = { "dbmsnc" | "dbmesm" | "dbmftm" }
```
The "source" parameter defines the name of the ODBC source.
```
dbi.database.dbname.source = "test1"
```
With the SNC driver you might consider setting the snc.widechar FGLPROFILE parameter to false if your database columns are defined with the CHAR/VARCHAR/TEXT types (by default the driver is prepared to work with the "UNICODE" types NCHAR/NVARCHAR/NTEXT). See CHARACTER data types for more details.
```
dbi.database.dbname.snc.widechar = false
```
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 data types.
```
dbi.database.dbname.ifxemul.datatype.serial.emulation = "trigseq"
```