Oracle and SQL Server

The Gateway extracts the files from structured data base sources such as Oracle and SQL Server.

You can extract the Oracle and SQL Server-specific data from the Gateway by defining specific settings in the Gateway.

Oracle Server:

SQL Server:

To extract the specific settings for Oracle/SQL Server data you will need to install the relevant ODBC driver for that database:

• Data Name: Specify the extracted data name that is used to generate the name of the output EIWM file, CSV file and logs.

• Server Details: Defines the database server details.

  • Driver: Type the driver name.

  • Oracle Server/SQL Server Name: Type the database Server name.

  • Integrated Security: Windows Authentication) Allows you to connect to your SQL Server or Oracle database using your current Windows user credentials, instead of entering a separate database username and password. This means the Gateway user must also be an authorised user in the database.

    • Enabled: The UserID and Password fields are disabled, and any saved credentials are cleared upon saving for security.

    • Disabled: You’ll see a warning that storing credentials in the project is less secure than using Integrated Security.

      Note: As this is a more secure method for connecting to the database, as user credentials don't need to be stored in the Gateway configuration, support for the UserID and Password fields will be removed in future versions.

  • UserID: Type the User name to connect to the database.

  • Password: Type the password to connect to the database server.

    Notes:

    • When you save the settings, the password will get encrypted and will be stored in the configuration file.

    • Because the encrypted password is associated with the local machine where the Gateway is running, other machines cannot use the configuration file directly. You will have to encrypt the password before using the configuration created in another machine. You can obtain an encrypted password using the encryption utility.

  • Database: Type the database name.

  • Connection Retry Time: Specify the timeout period for the Gateway to wait for a response from the database Server. When the Gateway requests a query from the database and does not receive a response, for example, if the database server or network is busy, the Gateway process waits for the timeout period and then sends another request to the database.

  • Connect: Select this option to connect to database server.

  • Select Table: Select the table that gets populated in the box. Click Import Schema to fetch the list of rows in that table. These rows are displayed in the Column Filter box, which you can select to generate a query.

• Column Filter: Specify the column names and provides the filter to select particular column data for processing. Click Select to select all the columns.

• Query Generator: Type the query to fetch the data in the box.

• Edit Query: Click this button to edit the query.

  Note: Extraction Query (SQL Joins): You can extract and collate data from multiple tables within a database using a SQL Join syntax in the extraction's query. For example, if the data source has two tables, Orders and Customers with the following schema and data:

  OrderID	CustomerID	OrderDate
  10308	2	2014-09-18
  10309	37	2014-09-19
  10310	77	2014-09-20

  CustomerID	CustomerName
  1	First_Name1 Last_Name1
  2	First_Name2 Last_Name2
  3	First_Name3 Last_Name3

  • You can use the following SQL query to extract attributes from both the tables having a common CustomerID:

    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

  • The resultant set will be as follows:

    OrderID	CustomerName	OrderDate
    10308	Felicity Cheadle	2014-09-18

• Incremental Scan Section

  • Required: If you select this option, the Gateway keeps track of the last scan date by storing it in a configuration file of the extractor in the same location where Project.xml exists. When the Gateway is used for the first time, then a text file is created. Next time onwards, it reads the text file and passes this date to the Gateway.

• DateTime Column: Use this option to build the queries for a specific date-time.

• Last Scanned On: Use this option to store the last scan date-time of the most recent scan. The particular last scan data is appended to the existing query and accordingly you can generate the query. By default, this field is automatically filled with the latest scan.

• Discard Settings: If you do not require those settings any longer, select this box to discard the settings.

• Save Settings: Click this button to save the Oracle/SQL Server settings options.

  Notes:

• For security reasons, it is recommended to restrict the database user account with the read-only access to the databases (or the selected tables/views) from which information needs to be extracted. It is required to avoid accidental addition, change or deletion of sensitive data.

• In order to ensure the security of data which is in transit between the Gateway and an Oracle or SQL Server database, you can configure an SSL-encrypted connection between the two.