Connecting Genero to SQL Server

Genero provides a database driver that's compatible with Easysoft's SQL Server ODBC driver, and this enables you to create Genero programs that use SQL Server as their back end.

1. Download the SQL Server ODBC driver for 64-bit Linux.
2. Install and license the SQL Server ODBC driver on the machine where Genero is or will be installed.

   For installation instructions, refer to the SQL Server ODBC driver documentation.

   Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Genero. For example:

   [MSSQL-GENERO]
   Driver          = Easysoft ODBC-SQL Server
   Server          = my_machine\SQLEXPRESS
   User            = my_domain\my_user
   Password        = my_password
   # If the database you want to connect to is the default
   # for the SQL Server login, omit this attribute
   Database        = Northwind

4. Use isql to test the new data source. For example:

   cd /usr/local/easysoft/unixODBC/bin
   ./isql.sh -v MSSQL-GENERO

   At the prompt, enter help to display a list of tables. To exit, press RETURN in an empty prompt line.

   If you are unable to connect, refer to this article and the SQL Server ODBC driver Knowledge Base for assistance.

5. If you have not already done so, install Genero on this machine.

   Note One of our customers has reported to us that if you want to use Genero with SQL Server 2016, you need to use Genero 3.00.10 or later.

   During setup, you are prompted to choose the default database for fglrun. If you want SQL Server 2014 or earlier as your default database, choose option [7] when prompted. If you want to use SQL Server 2016 or later, choose [6]. If you do not want to use SQL Server as the default database, choose another database. You can set the database on a per-application basis by specifying the database driver to use in your Genero source code.

6. Create a Genero program that retrieves some SQL Server data:

   $ more connect.4gl
   MAIN
     DEFINE varchar_col VARCHAR(100)
     # You can omit +driver... if you dbmesm_1 is the default driver in FGLPROFILE.
     CONNECT TO "MSSQL-GENERO+driver='dbmesm_1'"
     DECLARE xx CURSOR FOR SELECT * FROM MyVarcharTable
     FOREACH xx INTO varchar_col
       DISPLAY varchar_col CLIPPED
     END FOREACH
   END MAIN

7. Set the environment so that Genero can load the SQL Server ODBC driver:

   export LD_LIBRARY_PATH=/usr/local/easysoft/sqlserver/lib

   If didn't install the Easysoft driver under /usr/local, amend the path accordingly.

8. Compile and run your program:

   $ cd /opt/fourjs/gep/fgl/bin/
   $ ./fglcomp connect.4gl
   $ ./fglrun connect.42m
   Value1
   Value2

9. If you have any problems running the program, try setting FGLSQLDEBUG:

   $ export FGLSQLDEBUG=3
   $ ./fglrun connect.42m
   SQL: CONNECT
    | 4gl source      : connect.4gl line=3
    | loading driver  : [/opt/fourjs/gep/fgl/dbdrivers/dbmesm_1]
    | Dynamic linker error: [libessqlsrv.so: cannot open shared object file: No such file or directory]...
    | loading driver  : [/opt/fourjs/gep/fgl/dbdrivers/dbmesm_1]
    | Dynamic linker error: [libessqlsrv.so: cannot open shared object file: No such file or directory]...
   Program stopped at 'connect.4gl', line number 3.
   SQL statement error number -6366 (0).
   Could not load database driver dbmesm_1. Set FGLSQLDEBUG to get more details.
   $ export LD_LIBRARY_PATH=/usr/local/easysoft/sqlserver/lib
   $ unset FGLSQLDEBUG
   $ ./fglrun connect.42m
   Value1
   Value2