Connect to Databases

Create a Connection for a Specific Database Provider

Create a Connection for a Provider Not Supplied by expressor

Create a Connection for an Existing DSN

Change a Provider Connection

Change a DSN Connection

Create DSN Connections with Drivers Supplied by expressor Software

 

Create a Connection for a Supplied Database Provider

  1. Click the New Connection button on Create tab of Studio ribbon bar.

  2. Select  Database Connection

The New Database Connection dialog box opens.

 NewDBConnection.png

  1. Select "Supplied database drivers" for a specific database provider (e.g., Microsoft SQL Server).

Note: The list of supplied databases includes MySQL Community Edition and Netezza. The drivers for MySQL Community Edition and Netezza are not supplied with expressor Studio, but if the latest driver has been installed ( MySQL ODBC 5.1 Driver or NetezzaSQL), selecting it from the list of supplied drivers will enable you to configure normally.
 

  1. Fill in the properties listed for the specific provider.

The properties listed in the Connection and Credentials sections are required.

DBConnection2.png

Note: If you are connecting to an Oracle database using the SID (site identifier), leave the Service Name blank and under the "Advanced" section add a custom property with a name of SID.

 If there are additional connection properties required, they can be named and set in the Advanced section. See Drivers Supplied with the expressor Software.

Note: Access through the Connection is tested before you can finish creating it.

  1. In the final dialog box, select the Project in which to place the new Connection.

  2. Name the Connection with a name unique within the workspace.
    Names can be composed of alpha-numeric characters and underscores. Names must be begin with an alphabetic character or an underscore.

  3. Provide a description of the purpose of the Connection (optional).

Return to Top

Create a Connection for Providers Not Supplied by expressor

  1. Click the New Connection button on Create tab of Studio ribbon bar.

  2. Select "ODBC drivers not supplied" and click Next.
    The next screen in the New Database Connection dialog box provides text-entry fields for naming connection properties and specifying values for each property.

BlankConnectProps.png

You need to know the minimum complement of connection properties that must be set to enable connectivity.

  1. Specify the names of the properties that the database requires to establish a connection.

  2. Specify the values each of the properties.
    For example, if the connection properties are host name, port number, and service name, you would fill in the properties like the following:

ODBC-connection.png
Also note that when creating a connection for this type of provider, you must specify the driver name. Drivers must be 32-bit.

  1. Enter the username and password credentials for the database.

  2. In the final dialog box, select the Project in which to place the new Connection.

  3. Name the Connection with a name unique within the workspace.
    Names can be composed of alpha-numeric characters and underscores. Names must be begin with an alphabetic character or an underscore.

  4. Provide a description of the purpose of the Connection (optional).

Return to Top

Create a Connection for an Existing DSN

This type of connection is useful for connecting to Microsoft Excel spreadsheets, Microsoft Access databases, or some other data resource for which there is an ODBC driver.

  1. Click the New Connection button on Create tab of Studio ribbon bar.

  2. Select  Database Connection

The New Database Connection dialog box opens.

 NewDBConnection.png

  1. Select "Existing DSN" (Data Source Name).

Note: Microsoft Windows 7 32-bit systems have four drivers for connecting to Excel spreadsheets.  Only the driver with version 12 allows Studio to connect to Excel spreadsheets.

  1. Choose an expressor-3 driver that handles the type of database your resources are stored in.

If there is no expressor-3 driver listed in the New Database Connection dialog box, your system administrator must add one.

Note:  When making a DSN connection to an Oracle database, N-CHAR support must be enabled. Otherwise, NCHAR and NVARCHAR come up as UNKNOWN.

  1. Specify the Username and Password credentials required to access the database.

  1. In the final dialog box, select the Project in which to place the new Connection.

  2. Name the Connection with a name unique within the workspace.
    Names can be composed of alpha-numeric characters and underscores. Names must be begin with an alphabetic character or an underscore.

  3. Provide a description of the purpose of the Connection (optional).

Return to Top

Change a Provider Connection

  1. Select the Provider Connection from the Explorer panel and double-click or use the right-click menu to open it.
    The Connection displays in the center panel of Studio with the Connection and Credentials properties for the specific provider's driver.

  2. Change Connection properties as appropriate.

  3. Enter access credentials for the new connection.

  4. Test connection by using the Test Connection button on the Connection Editing tab in the ribbon bar.

  1. Save the changes with the Save icon on the Quick Access Toolbar.

Return to Top

Change a DSN Connection

  1. Select the Database Connection from the Explorer panel and double-click or use the right-click menu to open it.
    The Connection displays in the center panel of Studio.
    DSNConnection.png

  2. Change the DSN connection by selecting a DSN name from the drop-down list of existing DSN connections.

Note:  When making a DSN connection to an Oracle database, N-CHAR support must be enabled. Otherwise, NCHAR and NVARCHAR come up as UNKNOWN.

Note: Microsoft Windows 7 32-bit systems have four drivers for connecting to Excel spreadsheets.  Only the driver with version 12 allows Studio to connect to Excel spreadsheets.

  1. Enter access credentials for the new connection.

  2. Test the Connection by using the Test Connection button on the Connection Edit tab in the ribbon bar.
    Changes can be saved before testing the Connection.  Testing is recommended before you use a Connection in a Dataflow.

  1. Save the changes with the Save icon on the Quick Access Toolbar.

Return to Top

Create DSN Connections with Drivers Supplied by expressor Software

To see a list of drivers available on your system, both those supplied by expressor and those supplied separately, open the ODBC Data Source Administrator.  

  1. Select the Start > All Programs > expressor > expressor3 > system tools > Data Sources (ODBC) menu item.  

  2. Click the Add command button.

  3. In the Create New Data Source window, scroll down the listing until you see the entries beginning with expressor-3.

Note: The ODBC drivers in expressor's list of certified drivers should not be used to create a Data Service Name (DSN). The certified drivers should only be used by connecting through the procedures to Create a Connection for a Supplied Database Provider and Create a Connection for Providers Not Supplied by expressor. expressor does not support using the certified drivers as DSN connections.

Any other ODBC drivers in the Create New Data Source window can be used for a DSN connection. While those additional drivers are supplied, they are not certified or supported by expressor.

  1. Select an expressor-3 driver from the list in the Create New Data Source window and press Finish.

  2. Select the Advanced tab in the ODBC Driver Setup dialog box to see advanced properties that can be configured for the driver.

A complete listing of the connection properties for each of the drivers supplied with expressor software is available at: http://media.datadirect.com/download/docs/odbc/allodbc/wwhelp/wwhimpl/js/html/wwhelp.htm

Return to Top