<< Click to Display Table of Contents >> Navigation:  General Functions > Import / Export > Secure File Transfer > Direct Database Transfers > Defining a query

You must have previously defined a database connection before you may define a query.

Start by bringing up the client screen by clicking on the Secure File Transfer icon in the system tray.

Choose the Direct SQL menu option and click on Configure Transfer

Choose the transfer you want to configure from the description box on the left.  (Note, you must have defined one or more transfers using the MIE and those transfers must be currently marked as Active, the client must have downloaded its schedule recently, and the machine on which you are working must have connectivity to the MIE during this operation)

The read-only fields on the left will populate with information about the schedule and any last attempted transfers from the MIE.

Complete the information on the right of the screen to include choosing the appropriate database connection to use, and filling in the SQL.  When writing your own SQL statements, try to make use of the date of last successful transfer parameter to keep the volume of data to a reasonable level.

Optionally, if you are going to use one of the pre-defined connectors, then click on the checkbox Use a pre-defined connector for this query.  When that box is checked, the box for filling in the actual SQL query will be disabled and you will be able to select from the list of connectors.

Click on the Create file for this transfer via direct DB access checkbox to make the configuration active.  (Note, you can turn the automated querying of locally accessible databases in order to create the transfer file on and off in the future by checking, or un-checking, this checkbox.)

Click on Update to save your changes.

Click on Test Query to test the SQL statement.  If successful, the screen will switch over to the Query Results tab where you can view the results of the query (maximum of the first 50 records of the results).

 

Note: If this job is linked to a counter, such that the SQL issued is expected to return a count that will then be written to the counter, keep in mind how that count will be returned.  If the SQL statement contains any of the following functions: AVG(), SUM(), MIN(), MAX(), COUNT() - then the value returned by the query that is found in the first column of the first row will be written back as the count.  Otherwise, the record count returned will be written back.