Using the QlikView Expressor Datascript SQL Module

    Originally published on 07-20-2011 09:20 AM

     

    Expressor Datascript SQL is an ODBC interface from Expressor Datascript to database management systems. In order to use the API, you must first create an ODBC DSN to your database, preferentially using one of the expressor supplied database drivers. To open the Windows ODBC DSN Administrator, use the link in the Start > All Programs >  expressor > expressor 3 > system tools menu item, which opens the  administrator that manages the 32-bit drivers used by QlikView Expressor 3.

    In order to use expressor DatascriptSQL, you must cross reference the library that contains this functionality in your script by adding a require statement.

    require "dssql.expressor"

    Now your code may instantiate and use the three objects in the interface: environment object, connection object, and cursor object.

    Environment Object

    An environment object is created by invoking the initialization function.

    env = dssql.expressor()

    Use the environment object to create a connection object to a database management system.

    dssql1.png

    Connection Object

    The connection object encapsulates the connection information for a single data source.  Use this object to execute SQL statements against the data source.  If a SQL statement returns a result set, the connection object returns a cursor object, which you then use to retrieve the rows in the result set.

    dssql2.png

    Cursor Object

    Use the cursor object to retrieve the data returned by a SQL statement.

    dssql3.png

    Example Usage

    In this example, the names of one or more political parties are provided as input to the application via a Read File operator.  Within the Transform operator, Expressor DatascriptSQL is used to retrieve the politicians who are/were members of the party from a SQL Server database table that contains a listing of the presidents of the United States. Since the result set will contain multiple rows, the transform function  uses an iterator function to generate multiple output records. Each  time the cursor's fetch function is called, it returns a table with  three elements; the indices of the three elements are the column names  listed in the SELECT statement.

    MSSQL_demo is the name of an ODBC DSN configured to connect to the target database.

    expressor_user and expressor_pwd are the credentials for an account allowed to read the target table.

    dssql4.png