Curvostella

SQL Tools

Folder: SQL

Curvostella

SergCODE source code package (Freeware) SergCODE source code package (Freeware)

For installing SQL system helpers into the data base as well as for translating other SQL scripts, you should use the $SqlTrans utility, which is necessary for using the SQL code from this package. For utilities to work, you must set a path to the CMD-Utilities folder by using the PATH environment variable.
See: Command Line Utilities (the CMD-Utilities section).

The SQL scripts from the SergCODE package contain references to variables supported by the SQLCMD utility (insertions of the following pattern: $(VaribleName)). They are set in the $sql_settings.cmd environment configuration file (found in the root directory of the corresponding SQL project). Thus, apart from auxiliary entities, a set of special TSQL language extensions is defined (additional keywords).

In the root directory of each project, there is an optional $sql_settings.sql file usually used for applying special settings by a series of SET instructions and a GO separator, which is automatically added at the beginning of each script by the $SqlTrans utility. This file is empty in all the shown samples (i.e., default SQLCMD settings are used). Special settings may be applied globally by making corrections in the file of the same name found in the CMD-Utilities directoty. (However, local $sql_settings.sql overrides ALL the global settings defined as explained above.)

For conveniency, the $SqlTran utility may be integrated into Windows Explorer, the EmEditor text editor, the Far Manager file commander, and other applications.
See: Integrating the Translator of SQL Script Files into the Shell (current page).

ATTENTION!!! You may run only ONE instance of the $SqlTrans utility at a given point in time. This inconveniency is due to the fact that the SQLCMD command processes a temporary $sql_script.sql file in the TEMP folder instead of source SQL files. (Running two or more translations in parallel will cause their “intersection.”).


System SQL Helpers (Stored Procedures of the Following Pattern: sys_HelperName”, the System Helpers Subsection):

Group of Procedures Procedures (List) Purpose (See Contents of Files or Translated Database Objects for Details)
Deleting a database object (if exists)

sys_DropProcedure, sys_DropProcedures

sys_DropFunction, sys_DropFunctions

sys_DropTrigger

sys_DropTable

sys_DropView

sys_DropType

Differently from the commands like drop procedure, drop table, etc., these procedures ignore the non-existance of the object being deleted (no error is caused). This way these procedures can be used efficiently in database object creation scripts: the same script may create as well as recreate objects. (Such behavior is really necessary for creating collections of script files for stored procedures, functions, etc.)

Differently from usual procedures that take a single object as a parameter, those procedures that support batch deletion (“sys_Drop...s”) use name patterns (placeholders: %, _; escape: \). This takes into account the fact that developers use namespaces for stored procedures and functions. (In this case, deletion scripts are extremely simple: all the procedures and functions can be deleted with a single command.) The somekey_ProgrammaticObjectName and Namespace:ProgrammaticObjectName patterns are good examples of this kind of namespaces. (Listing objects by pattern, grouping in Management Studio are also advantages of using namespaces.)

See SQL programming samples (the SQL\Programming Samples folder).

Granting/revoking user access rights for running stored procedures

sys_GrantExec, sys_RevokeExec

This procedures are designed for granting/revoking access rights of a certain user (a group of users) by stored procedure name pattern. This also takes into account the fact that developers use namespaces for stored procedures and functions (см.выше).

See SQL programming samples (the SQL\Programming Samples folder).

Obtaining a list of running SQL processes

sys_ListProcesses

This procedure provides a detailed list of SQL processes for the current database, processes for a specified database, processes that are not related to any database as well as processes that are running at the moment of invokation (default).

This procedure resembles (to a certain degree) the sp_who и sp_who2 system procedures. (See comments in the sys_ListProcesses header.)


Integrating the Translator of SQL Script Files into the Shell:

Folder (Subsection) Purpose
Shell Translation

It is recommended: 1) to associate the .sql file extension with the $SqlTrans utility in Windows Explorer using the Execute action (в Windows XP); 2) to run scripts by pressing Enter (in Far Manager); 3) and to translate SQL with F5 (in EmEditor.

For achieving the expected integration, it is recommended to run StartSqlTrans.cmd or InvokeSqlTrans.NoConfirmation.cmd with a file name as a parameter. (These batches, in turn, delegate execution to the InvokeSqlTrans.cmd or InvokeSqlTrans.NoConfirmation.cmd batch.) Using the mentioned auxiliary files ensures that the translation is done in a maximized CMD console with a header corresponding to the translated script.

While in the case of FAR, it is enough to simply refer to the $SqlTrans command ($sqltrans "!.!"), the association with Windows Explorer requires the full absolute path to the $sqltrans.cmd file. In the EmEditor text editor, when setting up an extended SQL tool, it is recommended to use the SergCODE_Root environment variable (its value must be set in the system).

An auxiliary resource: Utilities\EmEditor Configuration (register configuration of a well-known text editor, EmEditor, including support for SQL translation by the $SqlTrans utility as an external tool.

The external tool setup window of the EmEditor text editor (SQL-Translation):

A sample of SQL translation from EmEditor:


Using Command Line Utilities with SQL:

Folder (Subsection) Purpose
Usage of CMD-Utilities

This folder contain simple samples that demonstrate usage of $SqlTrans and $SqlUpload.


SQL Programming Samples:

Folder (Subsection) Purpose
Programming Samples

Here we demonstrate two simple SQL projects that are designed for supporting the corresponding server-side database applications (SQL API based on stored procesures). These projects were used under real conditions.



Top

© Sergei Kitaev (Китаев С.Ю.)