SQL-file installation

BASIC STEPS (CLIENT ENVIRONMENT):

DATABASE STEPS (SQLAUX LIBRARY IMPORT):

DB-1. ;
DB-1x..

Installation of SQL-file environment is not simple nor very complex. The software is installed by hands, it is needed to work a little.
Download pages (this domain): |
Files for download (one of): “Handicraft_Toolkit_YYYYMMDDx.7z” (without password), or “HandicraftSDK YYYY-MM-DDx.7z” (password: qwerty).
(It is recommended to install complete Handicraft Toolkit, compound package: Handicraft-SDK, BookRegistry app., TEST (DB).)

List of the manipulations that are needed to install the SQL-file programming environment (STEPS: 11+2):

Copy/extract either HandicraftSDK folder or Handicraft_Toolkit_YYYYMMDDx folder (SDK + additional examples) to/at user's machine (it is desirable to be situated not far from volume root). Path to the SDK must not to contain exclamation sign character ‘!’; the same is actual for the path(s) to user SQL-project(s)!

Register path to CMD-utilities folder — include it in PATH variable of system or user environment (to make working the following utilities of the console mode: $SQLTRANS, $SQLUPLOAD, $SQLCON, $SQL, $SQLTOCSVEXP, $CONFIRMPAUSE, $TYPEINCOLOR and others).

Specify in the system SQLAUX_ROOT variable (system / user environment) so that it refers to the following path: “...\SQL\SQLAUX” (or similar location) for the sake of availability of SQLAUX scripting library. Also it is very useful to define SSMS_ExePath variable (to something like the following): “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe”.

Copy CmdCpAuto plugin folder (it is for the Far Manager) “...\HandicraftSDK\Utilities\Far plugins\CmdCpAuto plugin, build 100 (Far v3)\x64\CmdCpAuto” (or “...\x86\CmdCpAuto”) into “%FARHOME%\Plugins\” folder. (“Far Manager” folder is located under the “Program Files” folder in Windows OS.)

Copy “*.lua” files (all simple macros or selectively) to the Far Manager into “%FARPROFILE%\Macros\scripts\” folder. The LUA-macros are taken from “...\CMD-utilities\Shell\Integration” folder.

Configure file association for “.sql” extension according to instructions from the “...\CMD-utilities\Shell\Integration” folder (please read “*.txt” files). You have to perform some other actions at this step in concordance with the instructions. For instance — to make adoption of liked menu items to user system (Far Manager) as suggested in FarMenu.ini sample file (located in Integration folder). Also there you’ll find an information how to configure for SQL-translation (i.e. processing performed by $SQLTRANS-utility) one of the best autonomous editors of program source text, it is about GUI-editor (see the Integration folder for details).

See following instructions and illustration. Here below, on it is illustrated the way of configuration of SQL-script association (.SQL) in the Far Manager.

Inside SQL Server Management Studio (SSMS IDE) you have to switch on the following important setting:
Main menu >> Tools :: Options :: Query Execution :: SQL Server :: General :: By default, open new queries in SQLCMD mode (switch on the checkbox).

It is desirable to achieve precise accurate adjustment of Far Manager window by fitting in into the work area of your desktop. Well fitting console does not have vertical (and horizontal) scroll bar when its window is maximized. Good-looking pseudographic frames in Far Manager panels (composed by line drawing characters) are normally without gaps. (An insignificant clearance in text graphics is possible for output from CMD batches / utilities.) It is effective to “play” with parameters of the program shortcut (.LNK binary file configured by Windows Explorer) to reach successful view.

Recommended font family name is Consolas, for the sake of compatibility with Unicode, good-looking appearance on high DPI display etc.
(Further there are settings acting on the author’s computer.) Font :: Size: 18, not bold (this is for 1920×1080 display resolution combined with scale value 125%). Layout :: Screen Buffer Size: 158×43. Wrap text output on resize: ON. Layout :: Window Size: 158×43. Layout :: Window Position: -4×-4. Let system position window: ON.
(Some of your parameters will probably be another.)

Also for startup from anywhere by hotkey, define the following for your Far Manager shortcut (.LNK) located on your desktop:
Shortcut :: Shortcut key :: Ctrl + Alt + F12 (or similar, for “Far Manager 3 x64.lnk”).

It is important and this is implied that MSSQL client utilities are installed in the system (SQLCMD, BCP) and the corresponding location is referenced by the PATH environment variable, as its part. For instance: “C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn” (one of the PATH components delimited from the others by column sign ‘;’).

More over, it is suggested to neutralize in the system possibility of quick text selection in the console (by the mouse pointer). It is needed to avoid unintentional making of such selection, that in its turn causes undesirable cessation of program text output to the console.

Auxiliary reg-file: “...\HandicraftSDK\Utilities\System Configuration\Console\QuickEdit=0.HKCU.reg”.
“...\HandicraftSDK\Utilities\System Configuration” folder contains a group of registry setting helpers.

SQL-file was tested under the following system locales (default code pages for single-byte apps):
1. English (USA), i.e. CP-1252 (Latin alphabet) / OEM: CP437;
2. Russian (Russia), i.e. CP-1251 (Cyrillic script) / OEM: CP866.
For other system languages (default locales) SQL-translation date-time stamp may be formed with some mistakes, probably in its date part. (Sometimes it may be non-critical, because time order inside the day is then preserved.)

For locales different from the two above, following environment variables may be helpful:
YYYY_START_IN_DATE=<Zero-based position> (optional)
MM_START_IN_DATE=<Zero-based position>
DD_START_IN_DATE=<Zero-based position>

Zero-based positions have to be specified through the variables, according to corresponding user locale, concerning the DATE environment variable (CMD). Also they are needed to account any nonstandard settings of date-time format in your system, concerning the DATE variable. (This problem is connected to limitations of legacy CMD-processor, which is used for implementation of $SQLTRANS command.)

(A restart of the Far Manager will be required to reflect the changes of the above steps!)

Performing this step implies that SQL-file program environment is installed, that is: above described basic steps (all or those that are required for you) have been done, and you have convinced that $SQLTRANS-command is accessible and SQL-translations really do work.

In Far Manager or another file management program (Windows Explorer etc.) open (pass into) directory with SQLAUX scripting library sources: “HandicraftSDK\SQL\SQLAUX\Source”. Look into “#programmatics.txt” file, there are numerous records there, which correspond to SQLAUX-library last compilation at author's machine (to local DB). Open “$sql_settings.cmd” (predefined settings file) in embedded Far Manager editor (normally by F4-key). It is very recommended your OEM encoding have to be switched on (“|OEM |” in editor top line), it is done automatically if CmdCpAuto-plugin is installed. Correct following two lines with requisites addressing your target DB:

You should write here your appropriate values or leave as is, if you are really targeted local database named “TEST”. Also there is a way to relay SQL-password to $SQLTRANS. This is done through assignment to “$sql_password_var” property, which is a “name of environment variable (container) for SQL-password” (see , invoke it without parameters).

Try to execute “list_programmatics.sql” (this requires configured association for .SQL estension), it should display empty contents for your database (“Stored procedures”, “Scalar functions”, “Table functions”, “Auxiliary tables”). In Far Manager, <Ctrl>+<PageDown> combination is recommended for this quick report (if LUA-macros for shell are installed), in order to see SQL-translation result in standalone console with vertical scroll-bar (however no preliminary safety confirmation is provided for SQL-execution in this way). Then try “list_programmatics.cmd” command. This have only to recreate your “#programmatics.txt” (with empty lists of DB-objects). Then proceed to SQLAUX DB-objects translation (re-creation).

SQLAUX library full re-translation is performed by “translate_programmatics.cmd” complex command. In case of success it creates all the objects in target database (“AUX:…”, “AUX::…”) and produces corresponding full listings in “#programmatics.txt” report. Also (as specified in “$sql_settings.cmd”: “rem goto SKIP_SOURCE_GENERATION” etc.), the compilation is configured to write into $GENERATED and $OUTPUT subfolders to save result of strong preprocessing, that is switched on in these settings (not so as by default).

As opposed to library creation, “!drop_programmatics.cmd”-command is intended to destroy all SQLAUX objects in target database, with accompanying deletion of “#programmatics.txt” report.

This step is an alternative to the previous one (), it doesn't require deployed SQL-file environment (basic steps above) at computer (clinet of SQL Server), for advanced programming in parameterized Transact-SQL with files. (It is not reasonable to require SQL-file environment there, to where developer do not has direct access, or by some other reasons. Nevertheless DB-objects which are produced with help of SQL-file technology may to function somehow, in such remote DB.)

Launch SQL Server Management Studio. Connect to corresponding SQL-server. Then create new query tab, select target database in it (for SQLAUX library import). Find and open “HandicraftSDK\SQL\SQLAUX\Source\$OUTPUT\sqlaux_library.sql” file, load it into the query. It is not needed to have SQLCMD-mode on in this case. However it is admissible to have this setting active (“By default, open new queries in SQLCMD mode”), because SQLAUX programmatics are not readonly: “sqlfile_readonly_aux = 0 -- allow 3rd party translation (to be able correct the objects from SSMS)” (it is set to 0 in SQLAUX “$sql_settings.cmd”, so no “$(SQLFILE_READONLY)”-insertion is made into SQLAUX queries and scripts).

Simply execute this SQLAUX import script, whose content is just loaded (from corresponding “sqlaux_library.sql”). This will destroy and then create all library DB-objects (“AUX:…”, “AUX::…”), in database you've selected as target.

As opposed to library creation, “00_drop_programmatics.sql”-script in “…\SQLAUX\Source\$OUTPUT”-folder is intended to destroy all SQLAUX objects in target database.
(Other SQLAUX re-creation scripts from this folder are: “01_auxiliary_tables.sql”, “02_functions.sql”, “03_procedures.sql”.)