Easysoft Blog

Thur, 08 June 2017

Connecting Genero to SQL Server

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

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

    For installation instructions, see the 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, type "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