GainSeeker SQL Utility

The GainSeeker SQL Utility is a powerful tool that can be used to execute Structured Query Language (SQL) functions, including:

• Changing the part number/process name in the data records

• Changing traceability values

• Setting values in the standards

• Deleting orphaned data records

Note: You can have multiple copies of this utility running simultaneously in GainSeeker.

Caution: If you are not confident in your SQL abilities, defer this activity to a database administrator. Always perform backups of your data tables prior to using this utility.

Accessing the SQL Utility

The SQL Utility is accessed from the SQL Utility button on the GainSeeker Utility module:

When you click the SQL Utility button, GainSeeker displays this warning:

1. • If you click Cancel, you will remain on the GainSeeker Utility main window.

   • If you click OK, the SQL Utility displays:

Select Database

The Select Database button lets you change to another database connection. You can choose either from the list of stored database connections or add a new temporary database connection. The temporary database connection only lasts during this SQL utility session.

SQL Assistant

The SQL Assistant button makes it easy to change data without needing to create your own SQL statements.

System and Change type

Choose whether to change data in the SPC or in DMS system, then choose whether to change Data, Standards, or Process for that system.

Field to change

For the System and Change type that you selected, this lists all of the fields that are available to update.

Current value

For the Field to change that you selected, this lists all of the values currently stored in the current GainSeeker database.

New value

For the Field to change that you selected, this displays the pre-defined list (of traceability values, existing standards, or existing processes - depending on the field you selected) for that field.

SPC Standards

If you chose the System "SPC" and the Field to change "Part Number", you can click this button to create, edit or delete SPC Standards.

SQL statement(s)

As you select options for System, Change type, Field to change, Current value, and New value, GainSeeker automatically creates SQL scripts to carry out those changes.

Some items like filters, priority lists, etc. cannot be changed by SQL Utility.  These will also be listed in the SQL statement(s) box.  You should use other GainSeeker modules to manually update these items with the New value.

Execute (F5)

After selecting all of the desired options on this window, click this button or press the F5 key to execute the queries and carry out the update.

Examples

1. 1. • Renaming a standard

      • Deleting an unused defect

Composing SQL Statements

The following buttons can be used to help compose SQL statements:

1. • Table Names displays a list of available table names in the database.

   • Column Names contains a list of all column headers for the list of available tables.

   • Reserved Words contains a list of commonly used SQL commands.

For more information, see Help for typing SQL statements and  Sample SQL Statements.

Multiple SQL statements

Multiple SQL statements can be executed at once, but only one result set will display.

Stored procedures

You can call stored procedures from the SQL Utility.

To execute a stored procedure, consult your database documentation for the appropriate syntax. The simplest method of calling a stored procedure uses the following syntax:

• Microsoft SQL Server and Oracle databases: EXEC stored_procedure_name

• MySQL and Pervasive.SQL databases: CALL stored_procedure_name

Right-click menu options for composing SQL Statements

Import from File and Export to File

Use these options to open a saved SQL statement (Import), or to save your current SQL statement for later use (Export).

Orphaned Data

This function queries data tables for all data records in auxiliary tables that do not have matching records in the corresponding master table. This will display the count of orphaned records for each of the auxiliary data tables with corresponding SQL statements to delete these records in the SQL text field. These statements can then be uncommented and executed as desired. If no orphaned data is found, a message will appear in the SQL text field alerting you that no records were found.

Note: Remember that deleting these records is permanent. It is impossible to recover this data once it is deleted.

Working with Query Results

Each time you execute a SQL statement, GainSeeker will use the bottom pane in this window to display any messages returned by your database server - number of records affected, errors, etc.

Additionally, if you executed a SELECT statement that returned one or more values (such as data records, a list of distinct values in one database column, etc.), the middle pane of this window will show those values on grid. When this grid is displayed, you can:

1. • Change the sort order by clicking a column header

   • Select one or more rows on the grid by clicking and dragging across the desired row selectors

   • Select one or more cells on the grid by clicking and dragging across the desired cells

   • Right-click on the grid to:

   • • select all cells on the grid

     • copy any selected cells - or all cells on the grid - to the Windows Clipboard

     • export all cells on the grid to a text file with column headers and comma separators

If a column's data type is a byte array, each value that does not resolve to a valid image will display "System.Byte[]".