Adding a database (other) connection to Winshuttle Composer

Adding data connections

Note: In order to use database connections for non SQL Server databases with Composer, the database driver must be installed on both the Server where Composer resides and the Workflow server. Check with your IT department for the correct connection string parameters.

Once a connection is made to the database for a specified user, the tables must exist in their schema. Composer does not support fully-qualified table names, i.e. user1.table1 . For example: if you’re logged in as user1, you cannot specify in your query user2.mytable.

Adding a database connection

On the ribbon, click the SolutionTab, and then click Add Data Connection.
Under Name, type a descriptive name for the connection.
Under Type, click the drop arrow and select Database (Other).
Under Connection String, type the connection string to the database.
Under Table, click the drop arrow, and then select the table.
Under Columns, check the columns you require.
Click OK.

Oracle connections

If you are connecting to an Oracle database, your connection string will look something like this:

Driver={Oracle in instantclient_12_1};dbq=<hostname_or_ip_address>:<port_number>/<instance_name>;Uid=<userid>;Pwd=<password>;

For example, if you have a database server called oracle-db.company.local running a TNS listener on port 1521 with a database instance called DB1 accessible by user dbuser and password dbpass, the connection string would be:

Driver={Oracle in instantclient_12_1};dbq=oracle-db.company.local:1521/DB1;Uid=dbuser;Pwd=dbpass;

Oracle connection string example:

Driver={Oracle in instantclient_12_1};dbq=oracle-db.company.local:1521/DB1;Uid=dbuser;Pwd=dbpass;

{Oracle in instantclient_12_1} is the driver name, which varies by version
db.company.local is the Oracle Server followed by the port :1521 then /databasename
Uid is the user name and Pwd is the password

The driver string refers to the specific driver that is installed on the Workflow server.

Contact your database administrator if you need help configuring your database string.

Note: Once a connection is made to the database for the specified user, the tables must exist in their schema. Composer does not support fully-qualified table names, i.e. user1.table1 . For example: if you’re logged in as user1, you cannot specify in your query user2.mytable.

If the database user needs to access tables that are not in their default schema, do ONE of the following:

Create a trigger for the database user that changes the current schema when the user logs in to the schema they need to access from your Composer Solution.

Create a synonym for the database user to the table they wish to use in the query.

DB2 connections

If you are connecting to DB2, your connection string may look something like this:

Driver={IBM DB2 ODBC DRIVER - C_apps_clidriver};Database=sample;Hostname=db2server;Port=50000;Protocol=TCPIP;Uid=DB2;Pwd=DB2PASS

{IBM DB2 ODBC Driver - C_apps_clidriver} is the driver name, which varies by version
Hostname is the database server
Port is the port to use communicate to the database
Protocol is the communication protocol
Uid is the userid and Pwd is the password

If the database user needs to access tables that are not in their default schema:

• Append a CurrentSchema parameter to the connection string. For example:

Driver={IBM DB2 ODBC DRIVER - C_apps_clidriver};Database=sample;Hostname=db2server;Port=50000;Protocol=TCPIP;Uid=DB2;Pwd=DB2PASS;CurrentSchema=SYSIBM;