CompanyOnwership_icon
NovioQ Development
Created on 19 June 2020
icon_unfollowing
Login to follow
exasol-connector

Exasol Connector

Stable version 1.0.0 (Compatible with OutSystems 11)
Uploaded on 19 June 2020 by 
NovioQ Development
exasol-connector

Exasol Connector

Documentation
1.0.0

Visit our instruction page to watch step by step recordings on how to install and use this connector.


Install Exasol Connector

  1. Download the latest Exasol ADO.NET Driver from here: https://www.exasol.com/portal/display/DOWNLOAD/
    And install it on your Windows Server where OutSystems is installed.
  2. Download ExasolDatabaseProvider.dll file from here:
    https://dev.azure.com/NovioQPublic/OutSystemsExasolConnector 
  3. To get a free evaluation licence, send us an email at: support@novioq.com with your Windows Server Product ID, which you can find in Settings > System > About



  4. (Note: Make sure you save all the work being done on your OutSystems Environment before doing this step)
    Go to your windows server where OutSystems is installed, open services and stop the following services:



  5. After you sent us an email, you will receive a .lic file,
  6. Copy the .lic file, the EXADataProvider.dll which you can find in where you installed Exasol ADO.NET Driver, and ExasolDatabaseProvider.dll file you downloaded, to this path: “\OutSystems\Platform Server\plugins\database”
  7. After you copy the files, start these services again:



  8. Go to “ \OutSystems\Platform Server” and open ConfigurationTool.exe



    First click on Create/Update Database Button (click Yes on new pop ups if needed) then click on Apply and Exit Button (click Yes on new pop ups if needed), the server will run some commands and then you will see if it’s “done”, and that’s it. 




  1.   How to set up a connection
    1. Exasol
      1. Create your data model in Exasol. Please note the Know Limitations  section in this documentation.
      2.  Create a user in Exasol that has all privileges you need in your OutSystems application.

    2.  Define a Connection to the External Database
      1.  Set up a connection to your database in Service Center:
        1. Open Service Center and go to Administration > Database Connections
        2. Click New Database Connectionand fill in the form. Parameters you need for the Connection String:
          1. IP Address of the Exasol Server
          2. Port Number where you can connect to Exasol Client e.g 8563
          3. Username and Password
          4. Name of the schema you want to connect to. (Optional)
             (You still can connect to other schemas as long as you have privileges for them)



      2. Click Test Connection to check if the connection is working properly, then click Create to create the Database Connection.

    3. Map Tables or Views to Entities in an Extension Module
      1. Open Service Studio, create a new application and add an Extension module to this application.
      2. Clicking on Create module will open Integration Studio. Connect to your OutSystems environment.
      3. Right-click the Entities folder in the Extension Tree and select Connect to External Table or View.
      4. After closing the wizard, review the imported entity names, descriptions and data types for each attribute.
      5. Click 1-Click Publish to publish the Extension Module.
      6. After publishing the extension, OutSystems warns you that you still need to configure which database connection the extension will use.
      7.  In the 1-Click Publish summary window, select the Missing Configuration warning and then select Configure.



    4.  Configure the Extension to Use a Database Connection
      1. In Service Center, make sure you are in the Operation tab of Factory > Extensions > <your extension name>.
      2.  Associate the logical database name of the extension to the database connection that the extension will use in runtime.



    5. Using the entities in your application
      1. In your application, click Manage Dependencies....
      2. Add a dependency to the Extension and select the Entities that you will use in your application.
        https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database for more screenshots and a general description of how to set up a connection to an external database.

    6.  Using custom queries in your application
      For custom queries, we have built an extension in OutSystems where you can find a function that can execute any query directly to Exasol Server:
      1.  After installing the Forge Component, you will find the extension “ExasolConnectorExtension”, in this extension you can find an action called “ExasolExecute
      2. Add this Action to your application



      3. This action takes two parameters; SQL Query (Query to be executed) and Connection Name (Exasol Connection Name that’s installed on your OutSystems Server in Service Center), and it returns the result set in Rows, Columns, Count, and Affected Rows
      4. You will find in our demo app how to use it properly, and here’s an example of it:



    7. Known limitations
      1. Don’t use capital and lower case in your schema and table names in Exasol (Exasol will add quotes to them, preventing them from showing up in OutSystems).
      2. Don’t put spaces in your schema, table and attribute names in Exasol. OutSystems will replace these with underscores, but then your queries won’t work.
      3. OutSystems only supports the following data types:
        1. BOOLEAN
        2. DATE
        3. CHAR
        4. DECIMAL
        5. DOUBLE PRECISION
        6. TIMESTAMP
        7. VARCHAR
      4. In Advanced Query: using like ‘%’ + @SearchKeyword + ‘%’, it won’t work, but a work around it is: using Concat function: e.g. Concat('%', @SearchKeyword,'%')
         
        Note that it’s also case sensitive.
      5.  In order to be able to insert data into your table, the ID Column of that table needs to have the IDENTITY Attribute set, this will allow auto incremental inserts into that table;
        1.  If you’re creating a new table: 
           
          Add “IDENTITY” attribute to the ID Column:



        2. If you already have tables with data:
          SET IDENTITY to the ID Column with MAX ID of the table to match OutSystems Criteria:



          e.g. “ALTER TABLE Actors ALTER COLUMN ID SET IDENTITY(SELECT MAX (ID) FROM Actors);”
           where the Table Name is: Actors, and the ID Column Name is: ID.
      6. Each query you send to Exasol using the Execute function is its own session. Therefore changing the session or impersonating another user doesn’t work.
      7. You can not send more than one query at a time when using the Execute function.
      8. If you want to use LogFile in your connection string, bear in mind that you need to give Read/Write Permissions for the Path Folder on the Server to all OutSystems to write these files at the runtime. 

Support options
This asset is not supported by OutSystems. You may use the discussion forums to leave suggestions or obtain best-effort support from the community, including from  who created this asset.
Dependencies
Exasol Connector has no dependencies.