Run Raw SQL

...is a DELUXE LTOOL which allows the user to run SQL alters, inserts, deletes, and updates against the various MS Access tables in his/her Legacy database.

Screen:

uri/../images/deluxe-run-raw-sql.jpg

Usage:

To run SQL alters, inserts, deletes, and updates against the various MS Access tables in your Legacy database:

  1. Back up your Legacy database.
  2. Open your Legacy database in _LTools_.
  3. Double-click Run Raw SQL in the Deluxe LTools tree to bring up the dialog for this LTool.
  4. Enter the ALTER, INSERT, DELETE, or UPDATE statement that you wish _LTools_ to execute in the SQL textbox.
  5. Click the Run button.
  6. _LTools_ will execute the SQL statement and report the number of affected rows.

Fields:

uri/../images/bluepin.gif Clear SQL

Click this to clear the SQL textbox.

uri/../images/bluepin.gif SQL

The ALTER, INSERT, DELETE, or UPDATE statement that you wish _LTools_ to execute. This statement may contain sub-selects.

Click here for help writing SQL query and update statements.

NOTE: Right-clicking on this field brings up a context menu containing some "canned" SQL UPDATE queries. If you wish to add your own you may do so by editing the MyUpdateQueries.csv file in your _LTools_ _Application Data_ folder (C:\Documents and Settings\\Application Data\Zipper Software\LTools\ ). Queries may contain parameters ({parameter-name}). When _LTools_ encounters a parameter it will prompt the user to enter some text. The text entered will replace {parameter-name} in the resulting query.

Once loaded, the window's title will display the name of the loaded query.

Any updates you make to MyUpdateQueries.csv will not get overlaid when you install a new version of _LTools_.

NOTE: Another item on the context menu is "Load Queries from File...". This feature is for loading one or more non-SELECT queries from a single file. If the file contains multiple queries, they must be separated by a semicolon. Clicking this menu item will allow you to select an existing file and load its contents. Click Run to actually execute the loaded SQL statements.

The {parameter-name} feature does not work for SQL statements loaded from a file.

NOTE: If you select "Load Queries from File..." on the context menu, and the file you select has an extension of .psv and contains lines of pipe separated values, you will be prompted to enter an SQL template.

For example:

If the .psv file looks like...

41.4575061\|-82.6534718\|"Amherst, Lorain, Ohio, USA"

... and you enter the following SQL template ...

UPDATE tblLR SET Latitude={0},Longitude={1} WHERE Location={2}

... an SQL statement will be generated in the form ...

UPDATE tblLR SET Latitude=41.4575061,Longitude=-82.6534718 WHERE

Location="Amherst, Lorain, Ohio, USA"

... where {0} is the first pipe separated value, {1} is the second pipe separated value, etc.

An SQL statement for each line in the .psv file will be formatted using the template and you will be told the total number of statements that were loaded. You can then click 'Run' and Run Raw SQL will execute the loaded SQL statements until it processes them all or it encounters an error.

uri/../images/bluepin.gif Rows affected

The number of rows inserted, deleted, or updated. ALTER will always show zero rows affected.

uri/../images/bluepin.gif Run

Click to execute the SQL statement that you have entered in the SQL textbox.

uri/../images/bluepin.gif Close

Click to close this dialog.

uri/../images/bluepin.gif Help

You can use the ? (on the caption bar) or the \ key to display help for this dialog.



Introduction | Deluxe Features | Find Missing Information | Send Legacy Reminders | Tidy Legacy HTML | List Events | Find Unattached Multimedia | Add Events | Add To-Do's | Address Converter | Advanced Set Living++ | Advanced Tagging - Tag Living | Backup/Restore USR Files | Copy Master Event | Delete Master Events | Advanced Tagging - Y-DNA | Get IPTC Data | Global Text Search | Merge Basic to SourceWriter Sources | MRIN Filing Assistant | Repair Multimedia Paths | RegEx GEDCOM | Run Raw SQL | Set IPTC Data | Set Picture Captions | Set Special User IDs | Sort All Children | Sort Children of Tagged Individuals | Sort Events | Sort Marriages by Date | View Legacy Tables | Rename Non-Unique Multimedia | Advanced Tagging | Advanced Tagging - Ancestors Plus | Advanced Tagging - Mito DNA | Create Unknown Spouses | Remove Dead Multimedia Links | Data Bridge | Save/Restore Tags | Link Unattached Multimedia | Standard Features | Set Private/Invisible | Change To-Do Priorities | Clear IPTC Data | DAR Lineage Worksheet | Delete To-Do's | Include Citations by Surety Level | Include Citations by Verified Flag | Remove Medical Notes for Living Individuals | Search Citations | Search File IDs | Set Individual User IDs | Set Marriage User IDs | Set Birthday/Anniversary/Death Reminders | Set Source Surety Levels | Set Source Verified Flags | System Requirements | Contact Info | Miscellaneous Help | Legacy Tables | Product Registration | Edit Preferences | Command Line | Legacy Tables - tblSX.Type | pedigree.css | Source Template | Event Template | About Wiki Web Help | Miscellaneous Tools | Convert Long to Short U.S.A. Location Names