Create Database Connections

View database connections

You can view the following database connection information on the Connections menu, on the Databases tab:

• Connection name
• Scope level
• Connection type
• Server name
• Authentication type
• Database name
• Applies to

On the Databases tab, you can filter on any column.

Add a database connection

1. Go to Connections > Databases.
2. Click Add. The Add New Connection panel will appear.

3. On the Add New Connection panel, do the following:

   • Enter a Connection name.
   • Select from the following Connection types:
     • SQL Server – Enter the SQL Server name, including the complete path and name of the database server (for example: DBSERVER1\SQLEXPRESS). Enter the Database name and select an option in the Authentication drop-down list (see below for details). Enter details as prompted.
     • ODBC (Microsoft SQL) – Enter the connection string.
     • ODBC (Oracle) – Enter the connection string.
     • ODBC (IBM Db2) – Enter the connection string.
     • OLEDB – Enter the connection string.
     • ODBC (MySQL) – Enter the connection string.

     • SharePoint Library or List – Enter the connection string.

     • Enterworks – Enter the connection string.

     • Rest API – Enter the connection string.

4. Click Save.

Edit a database connection

1. Go to Connections > Databases.
2. On the databases page, select the database connection and click Edit.
3. On the Update connection panel, make your changes.
4. Click Save. You will be notified that the connection details have been updated successfully.

Delete a database connection

1. Go to Connections > Databases.
2. On the databases page, select the database connection and click Delete.
3. When you are prompted to confirm the deletion, click Yes. You will be notified that the database connection has been deleted successfully.

Authentication modes

SQL User: The SQL user is used to connect to the SQL Server. This user should be created in SQL and have read permission on the database used in this connection.

Windows Specified Account: The specified Windows user is used to connect to the SQL Server. This user should have login access to SQL Server and should have read permission on the database used in this connection.

Windows Default Account: The application pool account is used to connect to the SQL Server. This user should have login access to SQL Server and should have read permission on the database used in this connection.

Adding connection strings

ODBC Microsoft SQL Server connections

If you are connecting to Microsoft SQL Server, your connection string will look similar to this:

DRIVER={SQL Server};SERVER=localhost;DATABASE=dbws;UID=svdb;PWD=ws;OPTION=N

• {SQL Server} is the driver name; this varies by version
• localhost is the database server
• dbws is the database name
• svdb and ws are the username and password which must be used to connect to the SQL Server database
• For more information on OPTION parameter, please refer to Microsoft SQL Server documentation

ODBC Oracle connections

If you are connecting to an Oracle database, your connection string will look similar to 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 that is accessible by user dbuser and password dbpass, the connection string would be the following:

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; this varies by version
• db.company.local is the Oracle Server followed by the port :1521 and then /databasename
• Uid is the username 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.

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 which changes the current schema as the user logs in to the schema they need to access from your Solution.
• Create a synonym for the database user to the table they wish to use in the query.

ODBC DB2 connections

If you are connecting to DB2, your connection string will look similar to 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; this varies by version
• Hostname is the database server
• Port is the port number to 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, do the following:

• 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;

OLEDB connections

If you are connecting using OLEDB, your connection string may look similar to this:

Provider=sqloledb;Data Source=myServer;Initial Catalog=myDatabase;User Id=user1;Password=pwd;

• sqloledb is the provider name
• myserver is the database server
• myBatabase is the database name
• user1 and pwd are the username and password you need to connect to the database

Applies to field:

Studio – If selected, the connection will be available for Query output to SQL Server for Query solutions.

Solution Designer – If selected, the connection will be available for the creation of data connections in a Form solution.

SharePoint Library or List

If you are connecting using SharePoint Library or List, you will get below Access or Authentication types:

• Window Specified Account: Provide window user and password for connection.

• Window Default Account: This option will use Evolve IIS server application pool account for connection.

EnterWorks

If you are connecting using EnterWorks, you will get below Access or Authentication types:

• Basic: Provide User Name and Password to connect to Enterworks.

• Trust: Provide only User Name and connection will be made with Winshuttle trust between Evolve and Enterworks in the context of given user name. User Name supports pattern resolver, means we can provide an pattern and application resolve the given pattern to get the username.

See Evolve integration with EnterWorks for detailed steps to integrate Evolve with EnterWorks.

Enterworks Connection (Datetime handling)

Date Format

For Enterworks type connections, date format is mandatory to be provided while creating the connection. The Date format provided should be same as that of provided in Enterworks shared configuration. Date format is used while filtering and fetching the data from Enterworks to Evolve.

Repositories has two type for date field data type:

1. Snapshot Date: Filter input date should be in format "YYYY-MM-DDThh:mm:ss.tttZ".

2. State Attributes (Date Field): Filter input date should be in format "YYYY-MM-DDThh:mm:ss.tttZ" and should be be UTC value.

   Note: Note : State Attributes are not handled in 20.2 release as date fields rather they will be treated as string type attributes only

3. Non-snapshot date: This is kind of string field. As no date operation is supported on these field. So they should be consider as string type in filter.

   Note: Note : For filter on snapshot date: As the field is of Date type so time would be fixed and the Filter input date would be in format "yyyy-MM-ddT00:00:00.000Z". vfv
   Note: Get API: Date (snapshotDate, snapshotdatetime) data will always be in format defined in config. Value will be return as stored in EW system.

   Date Time Format

   For Enterworks type connections, Datetime format is mandatory to be provided while creating the connection. The Datetime format provided should be same as that of provided in Enterworks shared configuration. Datetime format is used while filtering and fetching the data from enterworks to evolve.

   1. Snapshot Datetime: Filter input date should be in format "YYYY-MM-DDThh:mm:ss.tttZ".

   2. Non-snapshot DateTime: This is kind of string field. As no date operation is supported on these field. So ithey should be consider as string type in filter.

   Note: Note : For filter on snapshot datetime : If filter value is from form text field, format should be "yyyy-MM-dd hh:mm:ss"

Rest API

If you are connecting using RestAPI, you will get below Access or Authentication types:

• None: No authentication applied.

• Basic: HTTP basic authentication i.e. username and password will be sent in http request as base64 encoded format as per basic authentication standards.

• HTTP Header: Provided Key-Value pairs will be sent as HTTP Request header, usually used to send access key or secret in Request headers.

• SAP Hana Basic: HTTP basic authentication for SAP Hana i.e. username and password will be sent in http request as base64 encoded format as per basic authentication standards.

• SAP Hana x509: Not Supported.

• Salesforce: Sales force username and password should be provided. Also password should suffix with user secure token i.e. <User password><secure token>.

REST API Connections (Datetime handling)

REST API Connection supports below two format for Datetime field.

1. ISO : like "2021-13-04T07:22Z" i.e. When API uses format "yyyy-MM-ddThh:mm:ss.fffZ" to handle date or datetime.

2. Ticks: Microsoft JSON date format i.e. /Date(1595808000000)/.

Correct format must be specified in connection so application can read and send correct date value in API execution. For all date fields, specify the type "date" in API schema. When field type is date defined then Form field mapping creates the form field of type date. But if field is mapped manually (i.e. manually or using existing fields etc) and it is mapped to fields not of date type, then these field will be considered as Text and specific date format in connection will be used to show the value in Text field and the input value will be sent as is in API’s payload to API Server.

POST / PATCH / PUT Operation in REST API connection

• Case-1 A: Posting Date using Date-Picker Control

  When using Date Picker control , user needs to enter date in format as set in Date-picker control irrespective of Date Format(ISO / Ticks) set in Rest API DC.

• Case-1 B:Time part in Date-Picker Control

  Time Part will be lost when posting using Date-picker control or sending the read date time field back to API server.

• Case-2 A: Posting Date using TextField (i.e. Schema Date field mapped with Form text field)

  When using Textfield i.e. Schema has date field , but on Create field mapping window, date field is mapped to Textfield type . Here correct format as accepted by:

  API needs to be set to form field.

  1. For Ticks: User needs to enter value in Ticks format . Example - /Date(1595808000000)/ .

  2. For ISO: User needs to enter date as per ISO format . Example - yyyy-mm-dd.

• Case-2 B: Time part and TimeZone using TextField

  Using Textfield, Time Part will not be lost . Also Timezone will be respected, if Date value is provided along with offset. For example - Providing value like 2021-03-31T01:01:13.8366463+05:30 will send this value as is to API Server(i.e. input value with Time Zone).

GET Operation in REST API connection

• Case-1 A: Getting Date in Date-Picker control

  Here Date will be set as per Date picker format irrespective of Date Format(Ticks/ISO) set in Rest API Data Connection.

• Case- 1 B: Time Part and TimeZone

  Time part will be lost. Date will always be returned in UTC .

• Case-2 A: Getting Date in Textfield

  Here date will be set as per Date Format in Rest API Data Connection. For example - For Ticks , date will be set as /Date(1595808000000)/ and for ISO , date will be set as yyyy-mm-dd hh:mm:ss.

• Case-2 B: Time part and TimeZone using TextField

  Date will always be returned in UTC timezone. For example - If date was posted like 2021-03-31T00:00:00.0000000+05:30 (Indian Time Zone), then on GET operation, Date will be returned as 2021-03-30 (i.e. UTC time zone).