Datasource Connections

A datasource connection is the mechanism used by Logi applications to communicate with datasources through a driver or provider for the purpose of reading and writing data. Selecting and configuring a Connection element is the first step in the process of developing most Logi applications; connections are discussed in this topic.
 

 

About Datasource Connections

Logi applications connect to a datasource in order to read and write its data. In most cases, this is done using a Connection element, which specifies the nature of the communication, including security credentials.

The exceptions are for accessing certain kinds of files, such as XML, CSV, and Excel files, which your Logi application is able to access directly through the web server file system. These sources do not use Connection elements.

Connection elements do not work with datasources requiring SSH.

Connection elements are configured in the _Settings definition and are therefore available application-wide. One connection can be used for multiple report definitions, and a Logi application can have multiple connections, allowing data from multiple sources to be included in a single report. There are three types of connection elements:
 

Vendor-Specific Connections

Vendor-specific connection elements, such as Connection.MySQL, are the connections we recommend you use. They provide the best performance and easiest configuration. The available vendor-specific Connection elements include:

  • Connection.DB2
  • Connection.Google Docs
  • Connection.Google Maps
  • Connection.MongoDB
  • Connection.MySQL
  • Connection.OpenEdge  
  • Connection.Oracle
  • Connection.PostgreSQL
  • Connection.Redshift
  • Connection.Salesforce
  • Connection.SQLServer (Microsoft)
  • Connection.Sybase
  • Connection.Twitter
  • Connection.Vertica


Your Logi product, in combination with the .NET framework or Java libraries, contains all of the underlying drivers or providers necessary to make all of these connections work, except for Oracle, DB2, and Sybase. Due to licensing restrictions, these connections are only usable if you have the client driver software that's distributed with the database server, or that you download. See the Usage Notes sections for each of these systems on page two for information about their drivers.

MySQL 5.5 is not supported for use with our DataLayer.ActiveSQL technology.

The configuration of Connection.Google Docs and Connection.Google Maps is discussed in Google Connections.

 Salesforce.com has discontinued support for the TLS 1.0 protocol. If you're using Connection.Salesforce, or REST or SOAP API connections to Salesforce, you must be using Logi Info v12.2-SP4 or later, which supports the TLS 1.1 and 1.2 protocols. In addition, Login Java applications must use JDK or OpenJDK 8 or later to use the appropriate protocol version.

The most recent .NET and Java drivers shipped with Logi Info v12.6 only work with MongoDB 2.6 and later. If you're using an older version of MongoDB, such as 2.4, you cannot upgrade your Logi application to Info v12.6.
 

Generic Connections

Generic connection elements, such as Connection.OLEDB, require you to provide, or use a wizard to construct, a "connection string". Connection strings are text strings that are passed in code to an underlying driver or provider in order to initiate the connection. The available generic Connection elements include:

  • Connection.ADO
  • Connection.JDBC
  • Connection.LDAP
  • Connection.ODBC
  • Connection.OLAP
  • Connection.OLEDB
  • Connection.SMTP

  Connection.OLAP is used to connect to Microsoft Analysis Services OLAP databases and Connection.SMTP is used to connect to SMTP email servers.

The other generic elements are for connection to a variety of databases. For example, the Connection.OLEDB element allows you to use the Microsoft Jet driver to connect to MS Access database files and can also be configured to connect to Microsoft's SQL Server database server. Note that Microsoft elected not to include the MS Jet driver in its 64-bit OS versions, but it is possible to use the "MS Ace" OLEDB driver, distributed with Office 2007, instead.
 

Special-Purpose Connections

Special-purpose connection elements, such as Connection.Web Service, are designed to connect with specific web services or special Logi services. The available special-purpose Connection elements include:

  • Connection.DataHub  
  • Connection.HTTP
  • Connection.Leaflet Map  
  • Connection.Logi Application Service  
  • Connection.OData  
  • Connection.REST
  • Connection.Scheduler
  • Connection.Web Service (SOAP)

  Connection.DataHub is used to connect to our Logi DataHub data virtualization product. Connection.Logi Application Service is used to connect to Logi Platform Services, installed with Logi Info 12.5+ or the Discovery Module 3.0. Connection.HTTP allows the following datalayers to use a connection to an HTTP or HTTPS datasource that requires authentication:

  • DataLayer. CSV
  • DataLayer.Excel
  • DataLayer.Fixed Format File
  • DataLayer.GPX File
  • DataLayer.JSON
  • DataLayer.KML File
  • DataLayer.Web Feed
  • DataLayer.Web Scraper
  • DataLayer.XML


The Request Header is a child element of Connection.HTTP and Connection.REST. This element allows custom information, primarily intended for authentication purposes, to be sent through the connection in the HTTP request header.

Connection.Leaflet Map is used to connect to a Leaflet API-compatible map server. See Leaflet Maps for more information.

The Connection.Logi Application Service element is used to connect to a special service installed with the Discovery Module and is only available in Logi Info if the add-on module is installed. Connection configuration details are available in Develop with the Discovery Module .

Connection.OData, and its OData Parameters child element, allow connection to REST-style APIs.

Connection.REST and Connection.Web Service are similar, in that they both connect to web services. However, Connection.REST works with Representational State Transfer (REST) protocols, such as HTTP, and Connection.Web Service works with the SOAP protocol. Connection.Scheduler is used with the Logi Scheduler service provided in Logi Info to manage scheduled reporting.
 

The Studio Connection Element

Logi Studio includes tools, like the SQL Query Builder and the Database Browser, that normally use the configured Connection elements to transparently connect to the data during development. However, for a variety of reasons, the Windows development machine where Studio is installed may not be able to use the configured data connections. For instance, the driver may not be installed on, or may be incompatible with, the Windows machine running Studio. In this case, a special Studio Connection element can be added to provide a separate connection for Studio's tools. Studio Connection is available as a child of the following connections:

  • Connection.DataHub  
  • Connection.DB2
  • Connection.JDBC
  • Connection.MySQL
  • Connection.Oracle
  • Connection.PostgreSQL
  • Connection.Redshift
  • Connection.SQLServer
  • Connection.Sybase
  • Connection.Vertica

Back to top

 

Using the Connection Wizard

The fastest and easiest way to add and configure a Connection element to your application is to use one of Logi Studio's Connection Wizards. Here's how:
 

  1. In the _Settings definition in Logi Studio, select the Connections element and either click a Connection item in the main menu's Wizards tab (shown above) or right-click the element and select Element WizardsAdd a Connection item from the context menu. For this example, we'll add a Database Connection.
 
  1. A series of dialog boxes, like those shown above, will be displayed. You'll be asked to identify the server, the database, and to provide server login credentials. Provide appropriate information for your datasource and click Next to move to the next dialog box.



     
  2. Finally, the connection between Studio and the database server will be tested, as shown above. When you click Next and Finish, an appropriately configured Connection element will be added to the _Settings definition.
     

That's it - your connection is ready to be used.

Back to top

 

Using Vendor-Specific Connections

Vendor-specific Connection elements are designed to make configuring a connection very simple.
 

As shown in the example above, a Connection.MySQL element has been added to the _Settings definition in an application. Its attributes allow the developer to specify the server name, database name, and access credentials. At runtime, the required connection string will be created behind-the-scenes using this information.
 

Optional parameter elements, as shown above, can be added beneath the connection element. These provide the flexibility of including additional connection string parameters, if necessary. For example, the optional parameters for the MySQL connection include Compress, Connection Lifetime, Direct, Embedded, Port, Protocol, and Unicode. You can also enable special MySQL connection behavior, like use of User Defined Variables, by creating the parameter Allow_User_Variables and setting it to True. The other attributes for Connection.MySQL and similar connection elements, are:
 

Attribute

Description

ID
(Required) Specifies a unique element ID; referred to by other elements that use this connection.
Command Timeout Specifies the amount of time, in seconds, before the request to connect to the datasource is presumed to have failed. For most datasources, the default value is 60 seconds. For MySQL, the default is 30 seconds.
Connection String A fully-formed connection string for the database. If a value is defined here, all other attributes will be ignored and this string will be used to connect to the database. Any child parameter elements will be added to the connection string.
Port The port number for the database, if the default number is not being used.


In general, vendor-specific connections are much easier to configure than generic connection and developers should look to them first when creating applications.

Back to top

 

Using Generic Connections and Connection Strings

Another method of creating a connection is to use one of the generic Connection elements, which requires that you provide a connection string.
 


In the example shown above, a Connection.OLEDB element has been added to the _Settings definition. Its Connection String attribute has been configured with a connection string, which looks like this in its entirety:

    Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=user;Initial Catalog=Northwind;Data Source=myDBServer

You can see that the connection string has a specific format, which is unique to the connection type. The Connection.OLEDB element can invoke a special wizard to build its connection string; for all other connections, developers will need to provide an appropriate string.
 


     

The Data Link Properties wizard, shown above, assists in the creation of connection strings for the Connection.OLEDB element. In Studio, click the little browse button at the end of the element's Connection String attribute value to invoke the wizard. The Test Connection button only ensures network connectivity to the database server - it does not guarantee that the User Name and Password entered are valid! The test can be successful and yet you may still not be able to access the data if the credentials are wrong. If providing specific credentials, be sure that you enter them carefully and check Allow saving password.

This very useful web site: http://www.ConnectionStrings.com provides example connection strings for a wide variety of datasources and can assist in providing the proper format for strings for other connection types.
 

With ActiveSQL

If Connection.JDBC or Connection.ODBC is used with DataLayer.ActiveSQL, you must configure the Connection element's optional SQL Syntax attribute to identify the database's SQL syntax version.
 

Notes For Java Developers

When using a Connection.JDBC element, specifying an IP address instead of a host name in the connection string may significantly increase the connection opening time, due to the underlying Java implementation. Therefore, it is recommended that developers use host names, not IP addresses, in connection strings to minimize the time it takes to open a connection.

The Connection.SMTP element supports TLS/SSL authentication in Java applications only. This allows connection to Gmail and other similar services that will programmatically send email on your behalf. Set the element's SMTP Authentication Mode attribute to 3 to select TLS/SSL. In v12.2-SP4+, the TLS 1.1 and 1.2 protocols are supported.

Back to top

 

Using Special-Purpose Connections

Special-purpose connections are used to connect to specific web services or to special OS services for the Logi Scheduler and other facilities. All require credentials to authenticate the connection.
 

In the example shown above, a Connection.Web Service element has been added to the _Settings definition. The attributes identify specific aspects of the web service. As with all connections, a unique ID is required so that it can be referenced later by datalayer elements.

For developers connecting to their own web service, note that the WSDL document specified in this connection element's required WSDL URL attribute must be "valid", meaning that it can contain no errors that would cause it to fail to compile. A document that's readable but that will not compile is invalid and will cause the connection to fail.

Connection.Web Service has an optional child element, Connector Property Parameters, which makes it possible to connect to a web service which requires special authentication and proxy settings.
 

In the next example, shown above, a Connection.REST element has been added to the _Settings definition. Its Url Host attribute identifies the web service host address, and other attributes are used to provide user credentials, if required. As with all connections, a unique ID is required so that it can be referenced later by datalayer elements.
 

Connection.REST with Windows Domain Authentication

The Connection.REST element includes an attribute that lets you use Windows domain authentication, also known as "integrated security", with your connection. To enable this, set the connection element's IntegratedSecurity attribute to True and leave the Username and Password attribute values blank. The integrated security credentials that the application is running under will be used for the connection.

Back to top

 

Creating Dynamic Connections

You may want to create dynamic connections, which connect to a datasource based on external criteria. Connection elements make this possible by accepting tokens in their attributes.  

In the example above, left, tokens are used for the required attributes of the Connection.SQL Server element. Another approach is to assign a complete connection string to a Session variable and use its token in the Connection String attribute, as shown above right. A value in that attribute will cause the "required" attributes to be ignored.  

Or you can enter the literal connection string text into that attribute, as shown above, and tokenize parts of it, like this:

    Server=@Session.DBServer~;Database=@Session.DBName~;User Id=@Function.UserID~;Password=@Session.DBPassword~;


There are a variety of techniques available for setting the token values prior to the processing of the _Settings definition. For example, the Startup Process element, when included in _Settings, can be used to call a Process task that assigns the Session variables needed. This task will run before the Connection elements are processed.  For more information, see The _Settings Definition.

The example shown above is simplistic but shows you how to use a Process task to set Session variables, which can be used later as tokens in the Connection element's attributes. If you're unable to use the Startup Process element for some reason, you can still make the Process task run by calling it directly, rather than calling the default report definition:

    http://yourServer/yourLogiApp/rdProcess.aspx?rdProcess=yourProcessDefinition&rdTaskID=taskStartUp

Back to top

 

Connecting with Metadata

Metadata files can be used with the Analysis Grid and Active Query Builder elements to provide a dynamic data environment for end users. You create Metadata files are using a wizard in Logi Studio during application development and they can be highly tailored to present desirable data objects and relationships. Numerous data qualities, including formatting, security restrictions, and joins, can be specified.

The Metadata element is used to identify the metadata files to be included with a data connection. The following Connection elements can use the Metadata element:

  • Connection.ADO
  • Connection.DataHub
  • Connection.DB2
  • Connection.JDBC
  • Connection.MySQL
  • Connection.OpenEdge  
  • Connection.Oracle
  • Connection.PostgreSQL
  • Connection.RedShift
  • Connection.SQLServer
  • Connection.Vertica


Information about building metadata files can be found in Use the Metadata Builder Wizard .

The original wizard has been deprecated. In its place, the Use the Web Metadata Builder appears in a window within Studio.

Back to top

1010data

1010data provides a cloud-based software platform and associated services for business analytics and database publishing of large data sets. Here are the installation and configuration instructions for using 1010data with your Logi applications: 

Required Software

The following components are required for use of CData's ADO.Net driver for 1010data:

  • Microsoft Visual C++ Redistributable - This is a CData driver dependency. Available for download at
    https://www.microsoft.com/en-gb/download/details.aspx?id=13523 .
     
  • 1010data .NET SDK - This is the SDK the CData driver relies upon and is available for download directly from 1010data at https://www.1010data.com/technology/technical-interfaces/software-development-kits/

    On the web page, select the .NET SDK -> 64/32-bit Windows (ZIP) file download; when you expand the .zip file, you should find the "dotNET_2016022" version folder. This is the version required for use with the CData driver.
     
  • Logi Info 64-bit v12.1 SP4+ - Use the latest Logi Info release that supports functionality for 1010data-specific functions. Not compatible with 32-bit versions.
     
  • CData Driver - This is an installer file for the CData driver, which is provided separately by Logi Analytics.

Installation for v12.2+

Install the required software in the following order:

  1. Install the Visual C++ Redistributable.
  2. Install the CData Driver.
  3. Install, or upgrade to, Logi Info 64-bit 12.1.SP4+
  4. Create a new Logi Info application, or upgrade an existing Info application, to the newly installed version of Logi Info.
  5. Close Logi Studio, if it's currently running.
  6. Locate and copy these files (assumes a default Logi Info installation location):
    C:\Program Files\LogiXML IES Dev\Engine64\12.2.116\CData.C1010.System.dll

    C:\Program Files\LogiXML IES Dev\Engine64\12.2.116\System.Data.CData.C1010.dll
    to this folder:
    C:\Program Files\LogiXML IES Dev\LogiStudio\bin
      
     
  7. Expand the 1010data .NET SDK .zip file and locate and copy this file:
    dotNET_2016022\win64\CppCLISDK1010.dll  
    to this folder:
    C:\Program Files\LogiXML IES Dev\LogiStudio\bin   (assumes a default Logi Info installation location)
     
  8. Stop the IIS web server.
  9. Copy the dotNET_2016022\win64\CppCLISDK1010.dll file, from Step 7, to the folder <your Logi Application folder>/bin.
  10. Restart the IIS web server.

Initial installation and configuration is complete. When creating additional applications, the steps for copying the two .dll files will have to be completed for each new application.
 

Installation for versions prior to v12.2

Install the required software in the following order:

  1. Install the Visual C++ Redistributable.
  2. Install the CData Driver.
  3. Install, or upgrade to, Logi Info 64-bit 12.1.SP4+
  4. Create a new Logi Info application, or upgrade an existing Info application, to the newly installed version of Logi Info.
  5. Close Logi Studio, if it's currently running.
  6. Locate and copy this file:
    C:\Program Files\CData\CData ADO.NET Provider for 1010Data 2016\lib\4.0\x64\System.Data.CData.C1010.dll

    to this folder:
    C:\Program Files\LogiXML IES Dev\LogiStudio\bin
      (assumes a default Logi Info installation location)
     
  7. Expand the 1010data .NET SDK .zip file and locate and copy this file:
    dotNET_2016022\win64\CppCLISDK1010.dll
    to this folder:
    C:\Program Files\LogiXML IES Dev\LogiStudio\bin   (assumes a default Logi Info installation location)
     
  8. Stop the IIS web server.
  9. Copy the System.Data.CData.C1010.dll file, from Step 6 to the folder <your Logi Application folder>/bin.
  10. Copy the dotNET_2016022\win64\CppCLISDK1010.dll file, from Step 7, to the folder <your Logi Application folder>/bin.
  11. Restart the IIS web server.

Initial installation and configuration is complete. When creating additional applications, the steps for copying the two .dll files will have to be completed for each new application.
 

Using the Connection.ADO Element

The Connection.ADO element is used, in the _Settings definition, to connect to 1010data. Its attributes are:
 

AttributeDescription
ADO Connection String (Required) The a connection string valid for the ADO.NET data provider. Valid parameters include:

User - Specifies the username to be used for login.
Password - Specifies password associated with the username.
LoginURL - Specifies the URL (or gateway) to the server.
Login Type - Specifies the type of login to use. Options include Possess, Kill, or NoKill. Defaults to Possess, which will not interrupt sessions if you are also logged in via the browser.
Group - Specifies the group to log in as (used for pooled connections). You must set the User to the name of a group admin.
QueryPassthrough - Specifies a boolean which indicates if the query will contain 1010data XML to be executed. Default value: false
UseSimpleNames - Specifies a boolean that determines whether or not names will be simplified to alphanumeric with underscores. Specifying false, for example, will make data tables display as they appear in 1010data. Default value: true Example connection string:
user=someUserID;password=myPass;LoginURL=https://www2.1010data.com/cgi-bin/x.xx/ab.c;
 
Assembly Name (Required) Specifies the provider assembly name. For Logi apps, specify this value: System.Data.CData.C1010.dll
ID(Required) Specifies a unique, arbitrary connection element ID.
Command TimeoutSpecifies the amount of time, in seconds, before the request to connect to the data source is presumed to have failed. Default value: 60 seconds.
SQL Syntax Specifies the type of database server being queried so that the proper syntax is used. For Logi apps, specify this value: 1010data

  Once the connection is configured, we recommend that you create metadata by adding a child Metadata element beneath the connection element.  

Then, as shown above, you can configure the query and metadata using the Metadata Builder wizard.

Back to top

 

Amazon Redshift

Amazon Redshift is a fast, fully-managed, petabyte-scale data warehouse service in "the cloud". It's optimized for datasets ranging from a few hundred gigabytes to a petabyte, or more. Before using the service, you must sign-up and be licensed by Amazon. For more information, see the Amazon Redshift website.

The Connection.Redshift element allows you to connect your Logi application to the Amazon service. This is an ODBC connection and requires an ODBC connection string that includes the credentials assigned to you by Amazon.

For a .NET application, you'll need to install the Amazon Redshift ODBC Driver (32- or 64-bit, matching your Logi Info installation), and configure it with appropriate settings and a connection string with account credentials. This process is described in detail in this Amazon Web Services document.

For earlier versions of Logi Info .NET and Info Java applications, you must use the PostgreSQL 8.x JDBC and ODBC drivers to ensure compatibility. The PostrgreSQL 9.x JDBC and ODBC drivers might not work properly with all applications.

Though you can have success using standard DataLayer.SQL, for best performance with very large datasets, we recommend use of DataLayer.ActiveSQL. Amazon Redshift SQL is based on PostgreSQL 8.0.2. syntax but has a number of very important differences that you must be aware of as you design and develop your data warehouse applications. Please refer to the Amazon SQL Reference for more information.

Because of the nature of the Amazon Redshift service, Studio's wizards, Query Builder, Database Browser, and the "available columns" list in the Attributes panel, may not work, or work completely, in all cases.

Back to top

Connection.ADO

Connection.ADO enables data connections using ADO drivers, such as HPCC from CData. These connections are normally used with DataLayer.SQL elements. See the Using the Connection.ADO Element section above for a detailed description of its attributes. The following Connection.ADO example works with HPCC clusters: 

<Connection Type="ADO"
AdoAssembly="System.Data.CData.HPCC.dll" ID="connHPCC" AdoConnectionString="URL=http://192.168.nnn.nnn:8510;Offline=false;Cluster=yourCluster;Version=1" CommandTimeOut="30" />
And here's an example of using it to connect to Salesforce : 

<Connection Type="ADO"
AdoAssembly="System.Data.CData.Salesforce.dll" ID="connSalesforce"
AdoConnectionString="user="yourEmailAddress;password=yourSFPassword;security token=yourSFSecurityToken;" />

ADO connections work with Logi .NET applications only; they are not usable in Java applications. This connection element also allows you to use ADO-style drivers that you may develop yourself, however, Logi does not guarantee your driver will work.

Back to top

Connection.JDBC and SQL Server 2008

Developers creating Logi applications for Java, and using Connection.JDBC to connect to Microsoft SQL Server 2008, may encounter the following errors: There was a problem running a DataLayer. The error was: Could not open the supplied connection to the data. The server version is not supported. The target server must be SQL Server 2000 or later. This occurs when a newer JDBC driver is required for SQL Server 2008. The solution is:

  1. Download a new driver from Microsoft and uncompress the download.
  2. From the download, copy the file sqljdbc4.jar to your Logi application's WEB-INF/lib folder.
  3. Delete the old driver, sqljdbc.jar, from the same folder.
  4. Restart the web server.
  5. Run the Logi application.

If the following error message is received: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0. it means you either did not remove the original sqljdbc.jar, replaced it with the sqljdbc.jar file that came with the download instead of adding sqljdbc4.jar to the folder, or did not restart the web server.

Back to top

Configuring Connection.LDAP

The Connection.LDAP element has the following attributes:
 

AttributeDescription
Base DN (Required for Java, ignored for .NET) Specifies the top level of the LDAP structure, its "base Distinguished Name". The attribute value is derived from the company DNS domain name components. For example, to connect to an LDAP server within the domain www.MassiveDynamic.com, the attribute value would be this string:  dc=MassiveDynamic,dc=com
ID (Required) The unique element ID.
Server (Required) Specifies the LDAP server host machine name.
Command Timeout Specifies the amount of time, in seconds, before the request to connect to the data source is presumed to have failed. Default value: 60
Connection String Specifies a complete connection string to the LDAP server. If a value is provided, it will be used instead of the connection string generated by the element and all other attributes will be overridden.
Password Specifies the password for the LDAP user Distinguished Name (DN).
Port Specifies the port address of the LDAP server. Default value: 389.
Server Type Specifies an LDAP server type. Specify Standard LDAP for open system servers, such as OpenLDAP, Apache Directory, OpenDS, etc. Specify MS Active Directory for Windows Active Directory.
Default value: Standard LDAP
User DN Specifies an LDAP user "Distinguished Name" and must represent a user than can query the LDAP server. For example, the attribute value would be this string: uid=john.smith,ou=Users,dc=MassiveDynamic,dc=com

Back to top

Database Browser and Query Builder Connections

The Database Browser and Query Builder tools will work with the databases for which we provide "native" Connection elements, such as Oracle*, MS SQL Server, and MySQL. They may work with other databases that use a connection that mimics that of a database for which we provide a native connection, such as PostgreSQL. They are not guaranteed to work with every possible database and, in that case, we recommend that you use tools provided with the database to examine and manipulate data, and create and test queries, outside of Studio.

* Due to the way in which Oracle configures its software, the Database Browser and Query Builder will not connect, using our native Connection.Oracle element, to Oracle databases when Logi Studio is run on a 64-bit platform. In this situation, developers can choose to do without these tools or to use Connection.ODBC to connect.

Back to top

 

HP Vertica

The column-oriented Vertica RDBMS is designed to manage large, fast-growing volumes of data and provide very fast query performance when used for data warehouses and other query-intensive applications. It claims to drastically improve query performance over traditional relational database systems, provide high-availability, and petabyte scalability on commodity enterprise servers. You can download a free, feature-limited copy for local use, or subscribe to their hosted service. For more information, see the Vertica website.

The Connection.Vertica element allows you to connect your Logi applications to the database. Special drivers must be downloaded and installed. For more information, see Work with HP Vertica.

Though you can have success using standard DataLayer.SQL, for best performance with very large datasets, we recommend use of DataLayer.ActiveSQL. Vertica SQL is based on standard SQL syntax but has a number of very important differences that you must be aware of as you design and develop your data warehouse applications. Please refer to the Vertica SQL Reference for more information

Back to top

IBM DB2 Universal

As mentioned earlier, the data provider for DB2 is not distributed with Logi Info. However, you can download the provider from the IBM software site for use with your Logi application. You'll need to have an IBM ID to download files from the site which, as an IBM customer, you may already have.

The download page is shown above (the version shown may be different from your version). Download the IBM Data Server Client and install it on your web server. This will install some files into the Windows\assembly folder and you're all set for Logi .NET applications. For Logi Java applications, once the client package is installed, you'll need to copy the file db2jcc.jar from the installation folder to your Logi application's WEB-INF/lib folder.

Back to top

 

IBM DB2 on iSeries

In order to use a .NET Logi application with DB2 on the IBM AS/400 iSeries platform, you'll need to install some additional software and make a special Connection.DB2 element configuration, as follows:

1. Logi Info uses the IBM Toolkit JT AS400 driver for Java. The file jt400.jar has to be downloaded from http://sourceforge.net/projects/jt400 and placed in the Logi application's WEB-INF/lib folder.

2. IBM iSeries Client Access must also be installed and can be downloaded from http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html.
 



The current release at the time of this writing is shown above.

3. After installation, the file C:\Program Files (x86)\IBM\Client Access\IBM.Data.DB2.ISeries.dll should be copied to the Logi applications \bin folder.

4. In your Logi application's _Settings definition, you must configure the Connection.DB2 element to work with the iSeries:  

This is done, as shown above, by setting the DB2 Flavor attribute to iSeries.

Back to top

Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. It's available in a variety of editions and in local, remote, and cloud-based configurations. It supports a wide variety of transaction processing, business intelligence, and analytics applications in corporate IT environments and is one of the market-leading database technologies.
   

Context Switching

Microsoft SQL Server supports "context switching" - changing of the identity against which permissions to execute statements or perform activities are checked. This is analogous to using the "EXECUTE AS <UserName>" command in SQL.
 

To switch contexts in a Logi application, use a child SQL Server Context element beneath the Connection.SQL Server element, as shown above, and set its Username attribute value to a valid SQL Server user name, or to any Logi token containing one. The context is re-evaluated with every data request, gets set just before the SQL query is run, and gets reset right after it runs, providing a way to dynamically set the context.
 

Connection Parameters

The Microsoft SQL Server Parameters element allows you to supply additional parameters when connecting to a Microsoft SQL Server database.
 

Examples of additional connection parameters include Async, Encrypt, Integrated Security, Persist Security Info, Replication, and TrustServerCertificate.
 

SQL Server Connection using Windows Authentication

If you're working within a Windows Domain, you may wish to use the domain login credentials as SQL Server login credentials. This may be a convenience just for developers or may be the method you want to employ for all application users. Here are the steps to achieve this:
 

  1. Add a Connection.SqlServer  element, with child Microsoft SQL Server Parameters element, as shown above. Leave the Connection element's User and Password attributes blank.


     
  2. Configure the Microsoft SQL Server Parameters element to have one parameter, as shown above. Use the exact spelling and case shown.

   

Logi Application Services Parameters

The Logi Application Service Jdbc Params element allows you to supply additional parameters when connecting to the Logi Application Service.
 

Logi Info generates special JDBC connections when the Logi Application Service has been installed. Use this to element to supply additional parameters for connecting to it. An example of a JDBC parameter is integratedSecurity.

Back to top

 

MongoDB

MongoDB is a cross-platform, document-oriented database system, classified as a "NoSQL" database. Rather than using a traditional table-based, relational DB structure, it uses a collection of JSON-like documents with dynamic schemas. You can download a free, feature-limited copy for local use, or subscribe to their hosted service. For more information, see the MongoDB website.

The Connection.MongoDB element allows you to connect your Logi application to the MongoDB server or service. Several special Mongo datalayer elements allow you to retrieve data. For more information, see Work with MongoDB.

The most recent .NET and Java drivers shipped with Logi Info v12.6 only work with MongoDB 2.6 and later. If you're using an older version of MongoDB, such as 2.4, you cannot upgrade your Logi application to Info v12.6.

Because of the nature of the MongoDB service, Studio's wizards, Query Builder, Database Browser, and the "available columns" list in the Attributes panel, may not work, or work completely, in all cases.

Back to top

Oracle

As mentioned earlier, the data provider for Oracle is not distributed with Logi Info. Use this link to download Oracle Instant Client drivers (navigate to the appropriate version).
 

Virtual Private Database Support

Oracle's Virtual Private Database (VPD) feature enables users to create security policies to control database access at the row and column level, and Logi Info supports this with a special element:
 

The Vpd Context element, highlighted above, is a child of the Connection.Oracle element. The Vpd Context element has a single Command attribute, which is typically set to the name of a Stored Procedure (SP). This SP is set up by an Oracle DBA to create security policies that control database access. Parameters are supplied to the SP by using one or more child SP Parameter elements, as shown above.
 

"Oracle x.x.x Client Required" Error

Oracle connections may fail, displaying the Inner Error message "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater." in the Debugging Trace Page. This may occur even though the SQL Query Builder is able to connect to the database, and even though a later version of the Oracle Client has been installed.

A likely cause of this problem is that the account that the web server uses to run your Logi application does not have appropriate fileaccess permissions to the folder that contains the Oracle Client software. That account may be ASPNET, Network Service, an Application Pool account, or another account, depending on your web server configuration. Giving that account Read and Execute permissions for the folder that contains the Oracle client will usually resolve this problem.

In addition, note that the web server must be rebooted after the Oracle client software has been installed.
 

Oracle 32-bit Software on Windows 64-bit OS

If you're attempting to connect to an Oracle database from a Windows platform using one of the following programmatic interfaces: ODBC, OLEDB, OO4O, or ODP.NET, after installing 32-bit Oracle client software on a 64-bit Windows operating system (OS) you may receive one of the following errors:      ORA-12154: TNS:could not resolve the connect identifier specified
     ORA-6413:  Connection not open.
This is because the 64-bit Windows OS installs 32-bit software by default into a folder beneath C:\Program Files (x86) and the presence of parenthesis in the file path is unacceptable to the Oracle software. This is a known bug that Oracle may fix in the future. Until then, you must install the Oracle 32-bit client software somewhere other than the default location.

Back to top

 

SAP Sybase

As mentioned earlier, the data provider for Sybase is not distributed with Logi Info. You must have installed the ADO.NET driver from Sybase, which is typically included with your Sybase installation software, or can be downloaded from the Sybase web site.