Programming in SQL

Editing and executing scripts

The text of scripts is edited in the windows of the editor. You can create scripts for solving various practical tasks and save them to the disk. You can open and execute existing scripts. Naturally, you can also execute separate SQL queries in the editor for displaying the data you are interested in and modifying them if necessary.

To start editing a new text, you should select File / New Window in the menu or use the Ctrl + N key combination. To open a script saved to the disk before, you should select File / Open in the main menu or use the Ctrl + O key combination and then select the path and file name in the new dialog box. You can work simultaneously with several windows containing various scripts.

You can execute any SQLite commands in one window. Several commands executed win one SQL query are separated with a semicolon. The results of all the select commands included into your SQL query are displayed.

It is possible to execute not the entire script, but its separate parts. To execute commands in the next line, F6 is used. You can also single out a block of commands uniting several lines or a block selecting a command in a part of one line and use the regular execution command (F5). Only commands within the selected block are executed in such an SQL query.

The result of script execution is displayed at the bottom of the window. You can hide or show the result using the menu Query / Show Results or the Ctrl + R key combination.

SQLite Analyzer highlights text according to the rules specified in the options of the editor (see 2.2.7 Options, Fig. 2.11. Two variants of displaying a script.).

Any methods of searching and replacing text are available during the work with scripts. You can start searching for the text you need either by pressing the Ctrl+F key combination or selecting Search / Find in the main menu while replacing is available by selecting Search / Replace from the menu. For more details see 2.2.3 Search.

You can save the text of the script to the disk by either selecting File / Save, File / Save as …in the menu or using the Ctrl +S key combination.

The current database is displayed in the drop-down list on the toolbar. When you create a new window, it works with the database selected in the list. You can change the database for the current window. Each window remembers the database it works with.

Generating scripts

To quickly and precisely write the commands CREATE, INSERT, SELECT, UPDATE for any table, you can use script generation.

A script is created for the object specified in the Database Browser using either the main menu of the editor (Database /Script Object to New Window As and Script Object to Clipboard As) or the context menu in the Database Browser. The first command opens the script in a separate window while the second one copies it into the clipboard. To insert the script from the clipboard into any window of the editor, just use the Paste command from the Edit menu or the Ctrl+V key combination.

Several script types can be used for SQLite objects: Select, Insert, Update, Delete, Drop. Scripts are commands written with the SQLite syntax. Depending on the type, either they can be ready to be executed or they can be half-prepared scripts that should be supplemented before their execution.

To do it, you should:

As a result, you will get a window with the script if the Script Object to New Window As command is selected (Fig. 5.1. Script generation command) or a copy of the script in the clipboard that will be inserted in the specified place in the window of the editor with the Ctrl + V key combination.

Fig. 5.1. Script generation command

Creating views

No graphical design wizard for creating views is used in this version of SQLite Analyzer. It will be available in one of future versions. So far you can create views using SQL scripts. You can see an example of creating a view in Fig. 5.2. Script generation command.

Fig. 5.2. Script generation command

The CHM file was converted to HTML by chm2web software.