EFT Database Utility

A command line utility is included in the installer that is capable of performing various database-related tasks. This same utility is used by the EFT installer to handle upgrades of existing databases. Typically, all common database tasks are handled by the EFT installer. However, on occasion it may be useful to use the command-line utility to verify the status of a database or perform an upgrade independent of the EFT installation process.

The database utility (DBUtility.exe) is included as part of the core EFT installation. Once installed it is located in the "DBUtility" sub-directory of the EFT program files installation directory. Typically this will be C:\Program Files (x86)\Globalscape\EFT Enterprise\DBUtility.

Requirements

• The utility requires the .NET Framework 4 (Full version)

• When operating against an Oracle database, the utility requires the 32-bit version of the Oracle Data Access Components (ODAC)

Capabilities

The database utility is capable of performing the following tasks:

• Check the version of the database to see if it is up to date. This may be used to see if an upgrade must be performed on a database before it is ready to be used by EFT.

• Generate an SQL script that may be used to create a new database schema (tables, views, etc.) manually within an existing database.

• Generate an SQL script that may be used to upgrade an existing database schema manually.

• Analyze an existing database prior to performing an upgrade. The analysis will verify prerequisites, display information about the database, and display the SQL that will be used to upgrade the database.

• Upgrade an existing database schema to the latest version.

Logging

The utility is capable of outputting various levels of information ranging from errors to debug/trace level information.

By default, the utility will output errors, warnings, and informational messages to the command window. If the "-verbose" command line parameter is specified, the utility will also output more fined-grained debug/trace level messages to the command window.

The utility may also optionally output to a log file as specified using the "-logfile" command line option. The output to the log file will include all levels of messages from errors to debug/trace level information.

SQL Scripts

The utility requires the presence of various SQL Scripts located in database-specific subdirectories to perform its actions. These subdirectories contain scripts such as:

• create_* - scripts used for creating new, clean database schemas for use by the EFT application

• Purge* - scripts that may be used for purging data from the database

• *ODBC - scripts that may be used to create the necessary tables to use an ODBC data source for user authentication

• upgrade_* - upgrade scripts for upgrading various versions of the database

By default, the utility will look for the "SQL Server" and "Oracle" directories under its current working directory. During installation of the Database utility, these script directories will be created under the <InstallDir>\DBUtility directory, so the scripts will be available to the utility.

If the utility is unable to locate these subdirectories, it will also attempt to consult the registry for the EFT "AppData" path and then look for the subdirectories under that location.

Additionally, the user may specify an alternate parent directory using the "-scripts" command line parameter.

Usage

The database utility is a command line utility and may be executed by opening a Windows Command Prompt and navigating to the "DBUtility" subdirectory of the EFT installation folder (e.g., C:\Program Files (x86)\Globalscape\EFT Enterprise\DBUtility) and running the command "DBUtility.exe."

Help

The utility includes built-in help documentation. Additionally, the utility will provide feedback on incorrect or missing command line parameters.

The built-in help documentation for the utility may be accessed using the command:

DBUtility.exe -help

More detailed help for the various top-level actions may be accessed using the command:

DBUtility.exe -help -action <Action ID>

Where <Action ID> is one of:

• CheckVersion - checks the version of the database to see if it is up to date

• CreateScript - generates a SQL script that may be used to manually create a new database schema

• UpgradePreview - used prior to upgrading a database. This action will generate and display useful pre-upgrade information as well as the actual SQL that will be used to upgrade the database

• UpgradeSchema - upgrades the database, if needed

• UpgradeScript - generates a script that may be used to manually upgrade a database

Examples

Example executions for each of the actions supported by the utility may be viewed in the command line help for each action.

DBUtility Command Line Parameters

The following section describes each of the command line parameters for the utility. Depending on the action performed, only a subset of the parameters will be applicable or required.

For the command line parameters that accept a value, the value should be enclosed in double-quotes if the value contains spaces. For example,

-logfile="C:\My Logs\MyLogFile.txt"

Parameter Definitions

• -help

• • Description: Display help on the command line. Refer to the "Help" section above for additional information.

• -logfile=<file>

• • Description: When specified the utility will log output of the execution to the specified log file.

  • Default: None

  • Example:

  • -logfile="C:\My Logs\MyLogFile.txt"

• -optionsfile=<file>

• • Description: When specified the utility will load command line parameters from the file. The file should specify parameters in a "parameter=value" pair with one pair specified per line. Parameters specified on the command line override parameters specified in the file.

  • Default: None

  • Example:

  • -optionsfile="C:\My Scripts\MyOptionsFile.txt"

• -scriptfile=<file>

• • Description: For actions that generate output SQL scripts this parameter defines the file to which the script should be written.

  • Default: None

  • Example:

  • -scriptfile="C:\My Scripts\MySQLScript.sql"

• -errorfile=<file>

• • Description: When specified the utility will log terminal errors to the specified file. Mainly used for error handling when the utility is called by the EFT installer.

  • Default: None

  • Example:

  • -errorfile="C:\My Scripts\MyErrorFile.txt"

• - resultfile=<file>

• • Description: When specified the utility will output result status codes for the execution to the file. Mainly used for state handling when the utility is called by the EFT installer.

  • Default: None

  • Example:

  • =resultfile="C:\My Scripts\MyResultFile.txt"

• -pause

• • Description: When specified the utility will pause at the end of the execution. Useful when executing the utility through a shortcut to keep the console window from closing before the user has a chance to review the results.

  • Default: None

• -verbose

• • Description: When specified the utility will output additional debug level logging.

  • Default: None

• -action=<id>

• • Description: Specifies the overall action to be performed by the utility.

  • Valid values:

  • • CheckVersion - checks the version of the database to see if it is up to date

    • CreateScript - generates a SQL script that may be used to manually create a new database schema

    • UpgradePreview - used prior to upgrading a database. This action will generate and display useful pre-upgrade information as well as the actual SQL that will be used to upgrade the database

    • UpgradeSchema - upgrades the database, if needed

    • UpgradeScript - generates a script that may be used to manually upgrade a database

  • Default: None

  • Example:

  • -action=UpgradeSchema

• -type=<type>

• • Description: The dialect of the database.

  • Valid values:

  • • SQLServer - a SQL Server/SQL Server Express database

    • Oracle - an Oracle database

  • Default: None

  • Example:

  • -type=SQLServer

• -server=<server>

• • Description: The database server host or IP address

  • Default: None

  • Example:

  • -server="Jupiter"

• -port=<port>

• • Description: The listener port for the database on the database server/host.

  • Default: None

  • Example:

  • -port=1433

• -instance=<instance>

• • Description: The database server instance name.

  • Default: None

  • Example:

  • -instance="MSSQLSERVER"

• -database=<database>

• • Description: The name of the database.

  • Default: None

  • Example:

  • -database="GLOBALSCAPE"

• -timeout=<timeout>

• • Description: The timeout, in seconds, to continue trying to connect to the database. A value of 0 causes the utility to wait indefinitely and should be used with caution.

  • Default: 30 seconds

  • Example:

  • -timeout=5

• -auth=<auth>

• • Description: The type of authentication to use when connecting to a SQL Server database.

  • Valid values:

  • • SQLServer - Use SQL Server authentication which requires specification of the username and password.

    • Windows - Use Windows authentication which will use the currently logged in user account.

  • Default: None

  • Example:

  • -auth=SQLServer

• -user=<user>

• • Description: The login name to use when connecting to the database.

  • Default: None

  • Example:

  • -user="eftdbuser"

• -pass=<pass>

• • Description: The password to use when connecting to the database.

  • Default: None

  • Example:

  • -pass=“3qym9NCebHDJ”

• -scripts=<dir>

• • Description: Parent directory containing the SQL Server and Oracle SQL Scripts subdirectories. Refer to the "SQL Scripts" section above for additional information.

  • Default: Refer to the "SQL Scripts" section above for additional information.

  • Example:

  • -scripts="C:\ProgramData\Globalscape\EFT Server Enterprise"

Advanced Parameter Definitions

The following parameters are typically reserved for use by the EFT installer and will normally not be useful to end users. However, they are documented here for completeness.

• -conn=<connection string>

• • Description: When specified this string will be used as the full connection string to the database rather than constructing the string based on the distinct parts.

  • Default: None

  • Example:

  • -conn="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mth-oracle)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orastart)));Persist Security Info=true;User Id=eftdbuser;Password=3qym9NCebHDJ"

• -app=<application name>

• • Description: The application name to present to the database for use when identifying connections.

  • Default: None

  • Example:

  • -app="EFT Database Utility"

• -installerdir=<directory>

• • Description: The directory containing the EFT Installer. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example:

  • -installerdir="C:\Users\Administrator\AppData\Local\Temp\nsdB57C.tmp"

• -installationdir=<directory>

• • Description: The directory where EFT is installed or will be installed. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example:

  • -installationdir="C:\Program Files (x86)\Globalscape\EFT Server Enterprise"

• -appdatadir=<dir>

• • Description: The directory that will be used for the EFT application data. May be used during some upgrades for accessing or storing additional files.

  • Default: None

  • Example:

  • -appdatadir="C:\ProgramData\Globalscape\EFT Server Enterprise"

• -backupdir=<dir>

• • Description: The directory in which to store backup data. May be used during some upgrades.

  • Default: None

  • Example:

  • -backupdir="C:\ProgramData\Globalscape\EFT Server Enterprise\Backup"