NthSQL 2.0 User Guide

 
Introduction
NthSQL is a SQL database interface similar to Microsoft Query, Oracle's SQL+ or SQL Server's Query Analyzer. NthSQL can be used to query data or perform updates. Basically any SQL command that is supported by the database can be executed through NthSQL. NthSQL will work with any database that is SQL compatible and provides either an ODBC or BDE compatible interface.

In addition to executing keyed in SQL commands, NthSQL can run a script of SQL commands. NthSQL can also be used to perform data import an export functions. NthSQL can run scripts or perform imports and exports either from the command line or from the menu interface.

These capabilities make NthSQL a good choice for a database tool to distribute with your applications. It can be used to create data structures and load data as part of an installation or perform database modifications as part of an upgrade. And it can be used as a debugging tool as well.
Contents

 
Installation
NthSQL comes with a setup program that should be run to perform the installation. If you are downloading from the internet or receive e-mail delivery then you must first unzip the installation. Then run the setup.exe program to install NthSQL. If you have a CD then just run setup.exe to install.
Contents

 
Connecting to a Database
NthSQL can be used with either file databases such as DBase, FoxBase and Paradox databases, client server databases such as Oracle or SQL Server or with an ODBC connection to any database such as Access.

Reopening a Database
NthSQL will keep a list of the most recent databases that you have opened in the File menu. To repoen a database simply select a database off of the list.

Connecting to a File Database
When connecting to a file database the only thing that is required is to give the directory where the database exists. Select the File | Select Database Directory menu option and indicate the directory where the database exists.

Connecting to a Client Server or ODBC Database
When logging into a client server or ODBC database the only thing that is required is to give the name of the ODBC DSN or the BDE (Borland Database Engine) database name. Select the File | Log In to Client Server Database menu option and select the database from the list. If the database that you want to use does not exist then press the Add button and select the ODBC DSN of the database.

Connecting to a Client Server BDE Database
If you are using Borland Database Engine SQL drivers then select the database as if it were an ODBC database with the File | Log In to Client Server Database menu option. If the database is not in the list then use the BDE Administrator program to configure it.
Contents

 
Executing SQL Statements
Once you have made a database connection you can begin entering SQL commands and executing them. Position the cursor into the SQL window and begin entering a command. When you are ready to execute it click in the Execute button. If the command was a database update statement the results will be shown in the Output window. If the command was a query then the Output window will show the number of rows found and a second window will be opened showing the retrieved data. If your command generates an error then an error message will be shown in the Output window.

If the command that you are entering is long, you may enter it on more than one line.

Repeating Commands
NthSQL keeps a history of the commands that you execute. Clicking the History button will recall a list of past commands. If you select a command from the list it will be executed again.

Executing Multiple Commands
You may enter more than one command and execute them all at once. Place a semicolon (;) at the end of each command in order to distinguish the end. Then when you click the execute button each statement will be executed sequentially and the results will all be shown in the Output window. If you execute multiple queries, any query results will be shown in separate Results windows.

You may not enter multiple commands on the same line.

Spooling Output
In addition to showing results in the Output window and queried data in the Results window, you may elect to spool output to a file. This is useful if you want to create a script in order to repeat the session at a later date or if you wan to record the results for some reason. To spool output select the File | Spool Output to File menu option or click the Spool button and indicate the file that you want output spooled to. The actual information that will be spooled is indicated by settings on the Options dialog and may include the SQL commands executed, queried data and results or error messages.
Contents

 
Executing an SQL Script
In addition to typing in commands and executing them, you may execute the commands that exist in a script file. To execute a script file select the File | Run Script menu option or click the Script button and select the script file. The results will appear in the the bottom half of the NthSQL main window and any selected data that is found will be loaded into results windows as if the instructions were typed into the SQL dialog.
Contents

 
Exporting Data
NthSQL exports data on a table by table basis to ASCII files. You may either export data in a fixed column width format or in a delimited format. 

To export data select the Import / Export | Export Data menu option to bring up the Export Database Dialog. Select either Delimited or Fixed Column Width. If you select delimited then you must indicate the delimiter to use. Next select the tables that you want to export. Finally indicate if you want to create a control file. A control file is required if you will use NthSQL to import the data at some later point. When you have made your selections press the Export button.

Each table will be exported to a file named after tge table name with a .exp extension. These files will be located in the import / export directory indicated on your NthSQL Options.
Contents

 
Importing Data
NthSQL imports data from ASCII files into database tables using a control file. If you exported the data using NthSQL then you had the option to automatically create a control file. Occasionally you will need to create or edit a control file. In this case you should see the section on control files.

To import data select the Import / Export | Import Data menu option to bring up the Import Dialog. Select the control file that you want to import and indicate whether you want to replace the current contents of the table with what is in the import file or whether you want to add the contents of the import file to what is already in the table. When you have made your selections press the Import button.
Contents

 
Control File Format
The control file is an ASCII text file used by NthSQL to import data into a table from an ASCII data file. A control file has two sections. The control file header section assigns values to keywords indicating for instance the table to be imported and the data file containing the import data. The field definition section defines the fields in the table that should be updated.

Control File Header
The control file header should include 3 of the 4 parameters shown below. They may occur in any order but you should never have both a DELIMITER and a FIXED keyword.

TABLE=tablename
 Indicates the name of the database table to be updated.
DATAFILE=filepath
 Indicates the name and potentially the location of the data file. If no path is included then the import / export path path set in the Options Dialog is used.
DELIMITER=,
 Indicates the delimiter that is used to differentiate the fields in the data file. This should only be used if the file is delimited. If the file is tab delimited you may spell out "tab" or use a tab character Otherwise use the character that is used as the delimiter.
FIXED
 Indicates that the data file has a fixed column with format.

Control File Field Definition
The field definition section begins with the "FIELDS=" keyword. On the lines following should be a list of database column or field names in the order that they occur in the data file. If the data file is delimited then only the field names are used. If the data file is fixed field width format then fllowing each field name should be a beginning position and an ending position. An example of this follows:

Name
 1
 20
Address
 21
 41
City
 42
 59
State
 60
 61

Contents

 
Command Line Operations
In addition to using the Windows interface described so far, it is also possible to have NthSQL operate from the command line. This is useful for scripting database updates for instance from a batch file. You could also use this to create a shortcut that performs a common task. The following command line options are supported:

/db=database
 Causes NthSQL to open the indicated database. You may either use a datbase directory for file databases such as dbase or paradox, an ODBC handle or a BDE database name. If the /db switch is not included then NthSQL will attach to the last database that it used.
/user=username
 Indicates the user name to use when logging in. You my omit this if the database does not require a login or if you want to enter the login when the program runs.
/password=password
 Indicates the password to use when logging in. You my omit this if the database does not require a login or if you want to enter the login when the program runs.
/script=scriptfile
 Causes NthSQL to execute the indicated script file.
/export=table
 Causes NthSQL to export the indicated table.
/import=ctlfile
 An import will be executed using the indicated control file.
/delim=c
 Indicates a delimiter that should be used when exporting a table. This switch has no meaning unless the /export switch is used. You may indicate any character to be used as a delimiter. /delim=tab causes the tab character to be used. If you omit the /delim switch then the export will be performed in a fixed column width format.
/log=logfile
 Indicates that the indicated log file should be used to record the session.
/logsql
 Causes NthSQL to write every SQL instruction to the log file. This option has no meaning if results are not being logged.
/nologsql
 Suppresses writing of SQL instruction to the log file. This option has no meaning if results are not being logged.
/logtitles
 Causes NthSQL to write column or field names of queried data to the log file. This option has no meaning if results are not being logged.
/nologtitles
 Suppresses writing of field names of queried data to the log file. This option has no meaning if results are not being logged.
/logcounts
 Causes NthSQL to write the row counts for update and query operations to the log file. This option has no meaning if results are not being logged.
/nologcounts
 Suppresses writing of row counts for update and query operations to the log file. This option has no meaning if results are not being logged.
/show
 Causes NthSQL to load query results into a query window which can be viewed as normal.
/noshow
 Supresses the normal display of query results. This is useful if you only want to write the results to a log file.
/exit
 Causes NthSQL to automatically exit after performing the command line instructions. Otherwise it will stay open.

Command line switches may be entered in any order.
Contents

 
Customizing NthSQL
NthSQL has a variety of settings tht you may use to customize the way NthSQL works and looks. These options are all accessed from the File | Customize menu option which brings up the NthSQL Options window. An explanation of the the settings found under each of the tabs follows:

Settings
The Settings tab has four configurable settings. The Import Export Directory is where export files and control files will be written to and where import control files will be searchd for by default.

When NthSQL starts up it the default behavior is to open without a database requiring you to select a database before you can do anything else. There is an option on the Settings tab to automatically reopen the last database that you used in your last session.

When you perform a database select the results are shown in a query results window which pops up. If you perform an additiojnal query the new results will be placed in the same results window overwriting the previous results. There is an option to have each query's results shown in a new window. Also you may turn the grid lines in the results window on or off.

Colors and Fonts
The Colors and Fonts tab allows you to select a font and background color for the SQL entry window, the results window and the query results window.

Log Options
The Log Options tab determines what inormation will be included in the log if you are logging your session. If you are querying data to a log file you may also turn off the query results pop up window. These settings are not relevant if you have not opened a log file.

Contents