Introducing Datasource Connections
Introducing Datasource Connections
|
Logi Info, Report, ETL
v10.0.101 - 14 Jun 2010
|
A datasource connection is the mechanism used by Logi applications to communicate with datasources through a driver or provider for the purpose of retrieving or updating data. Selecting and configuring a connection is the first step in the process of developing Logi applications that work with data; connections are discussed in this document and topics include:
About Connections
Logi applications connect to a datasource in order to retrieve and, sometimes update, its data. In most cases, this is done using a Connection element, which specifies the nature of the communication with the datasource, including security credentials.
The exceptions are certain kinds of flat files, such as XML, CSV, and Excel files, which your Logi application is able to access directly through the web server file system. These situations employ datalayers but do not need connections.
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 vendor-specific Connection elements, such as Connection.MySQL, generic Connection elements, such as Connection.OLEDB, and special-purpose connections, such as Connection.Web Service, for your use.
Generic connection elements require you to provide, or use a wizard to construct, a connection string. These are text strings that are passed in code to an underlying driver or provider in order to initiate the connection; they're discussed in a later section.
Vendor-specific connection elements do the job of creating a connection string for you in most cases, using the values you enter as attributes, or do not use traditional connection strings.
Special-purpose connections are designed to ease communication with specific web services or Logi services.
Vendor-Specific Connections
The available vendor-specific Connection elements include:
- Connection.DB2
- Connection.Google Maps
- Connection.Google Docs
- Connection.MySQL
- Connection.Oracle
- Connection.PostgreSQL
|
- Connection.Salesforce
- Connection.SimpleDB
- Connection.SQLServer
- Connection.Sybase
- Connection.Twitter
|
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 two:
Due to licensing restrictions, the DB2 and Sybase connections are only usable if you already have the client or provider software that's distributed with the database server. In the case of the Sybase connection, you must have installed the ADO.NET 2.0 data provider from Sybase, which is
typically included on your Sybase CDs or can be downloaded from the Sybase web
site. Logi products do not include providers for these two connections.
Connection.SQLServer is for use with Microsoft's SQL Server database server. Connection.Twitter uses the Twitter API to interact with the popular social networking web site.
If you're using a version of Logi Info or Logi Report earlier than 9.5.46, some or all of the vendor-specific connection elements may not be available to you. You will have to use a generic connection and build a connection string.
Generic Connections
The available generic Connection elements include:
- Connection.JDBC
- Connection.ODBC
- Connection.OLAP
|
- Connection.OLEDB
- Connection.SMTP
|
Connection.OLAP is used for connection to XML/A OLAP databases and Connection.SMTP is for connecting 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.
Special-Purpose Connections
The available special-purpose Connection elements include:
- Connection.REST
- Connection.Scheduler
|
|
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 other connection elements are used with web services.
Back to the 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, whatever connection string may be required will be automatically created behind-the-scenes using this information. The developer does not need to know the specific format of the connection string.
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.
The other attributes for the MySQL and similar connection elements, are:
|
|
|
|
|
(Required) A unique element ID; referred to by datalayer 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.
|
|
MySql Connection String
|
A fully-formed connection string for the MySQL 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 are processed and added to the connection string.
|
| MySql Port |
The port address of the MySql database. Default: 3306.
|
In general, vendor-specific connection are much easier to configure than generic connection and developers should look to them first when creating applications.
Back to the top
Using Generic Connections and Connection Strings
An alternate method of creating a connection is to use one of the generic Connection elements, which requires that you provide a connection string for it.
In the example shown above, a Connection.OLEDB element has been added to the _settings definition. It's 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.
Java Developers Note: When using 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.
Java Developers Note: The Connection.SMTP element supports TLS/SSL authentication in our Java products only. This allows connection ot 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.
Back to the top
Using Special-Purpose Connections
Special-purpose connections are used to connect to specific web services or to the Logi Scheduler service (Logi Info only). 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.
In the 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.
Back to the top
Special Oracle Considerations
The following considerations apply to connections to Oracle databases:
"Oracle 8.1.7 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 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 ASPNET account, under which the web server runs, does not have access permissions to the folder that contains the Oracle Client. Granting the ASPNET account Read and Execute permissions for the folder that contains the client will usually resolve this problem.
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 elsewhere than the default location.
Back to the top