ABASQL

ABASQL loads one or more SQL files. Typically you load one file at time as part of an edit-compile-test cycle from a text editor, for instance TextPad or Visual Studio. ABASQL is basically a command-line interface to the AbaPerls file-loading process.

Command-Line Syntax

abasql {-database db [-Server server] [-User user] [-Password pwd] | 
        -noexec}
       [-get] [-VC VC-path [-nouse_disk]] [-label version] 
       [-subsystem subsys] [-nocreate] [-force]
       [-log log-file] [-save [save-file]] [-LoadRequire]
       [-[no]crypt] [-site site-id:s] [-[no]quoterepl]
       [-Macro &macro=value [...]] [-undef &macro [...]]
       file [file2 ...]

Note that if you load a file that includes a $REQUIRE macro, ABASQL will only open that file to check that the $USEDBY directive is present, but ABASQL will not include the required file in what it passes to SQL Server.

Specifying the VC-path

There are two ways the VC-path can be specified. You can specify it explicitly with the ‑VC option, or AbaPerls can set it implicitly from your current Windows directory – but only if you are using TFS as your version-control system; this is not available for SourceSafe. AbaPerls asks TFS about path mappings, and if TFS says that the current Windows directory maps to a TFS folder, AbaPerls will use that folder if you did not specify ‑VC. If the TFS folder has /SQL/ as a middle part, AbaPerls strips everything after SQL from the path. If the TFS path does not include SQL at all, AbaPerls adds that to the path – no matter whether there actually is an SQL folder in place or not.

When ‑VC is specified – explicitly or implicitly – and the file to load exists in the given VC-path, AbaPerls will only read the file from disk, if you have the file checked out. If the file does not exist in the VC-path, AbaPerls will always try to read it from the disk.

Note there is a small gotcha with the TFS mapping. Say that you work in subsystem ABC and you decide to add a table to this subsystem, but hitherto there is no Tbl directory in that subsystem. You create a Tbl directory in your SQL directory on disk, but when you load the table you are told that you need to specify the subsystem. Since the Tbl directory does not exist in TFS, TFS does not think that the Windows directory is part of the TFS mapping. (And therefore AbaPerls is not able to determine the subsystem.) You will need to add the directory to TFS  for the mapping to work.

Beware that if your Windows directory maps to a TFS directory, ABASQL will permit you to use ‑get.

Specifying the Subsystem

More often than not, AbaPerls requires that you specify the subsystem one way or the other. There are two ways that the subsystem can be specified. You can use the ‑subsystem option, or AbaPerls can look up the VC-path in the system-definition file and thereby determine the subsystem.

If you specify a subsystem explicitly with ‑subsystem and AbaPerls finds that your VC-path maps to a different name, AbaPerls lets this pass if the subsystem name you specify does not appear in the sysdef-file at all. Maybe the database uses a non-standard name out of legacy. (Maybe you made a typo, but then you will get an error because the subsystem is missing from the database.) However, if you specify a name which is present in the sysdef-file for a different VC-path, ABASQL considers this to be an error.

For many file types of files AbaPerls requires that you specify the subsystem. Currently this is required for the following file types: .sql, .postsql, .syno, .sb, .tbl, .fkey, .ix, .fkey, .tri, .ins, .vtri, .vix, that is, file types where the same name can appear in multiple subsystems.

Subsystem is also mandatory in these situations:

• When you specify ‑get.
• When you specify ‑log.
• When the file SS‑FILES.LIS is present in the current directory. (This is an install kit created with ‑noexec ‑get, see the page Installation at a customer site for more information.)

If you are so inclined, you can also specify a subsystem with the file specification. For instance:

abasql -d somedatabase -S someserver somefile.sp SUBSYS!someotherfile.sp

This can be handy if you want to load files from several subsystems without having to move around among the folders. Beware, though, that this may not always do what you want. This syntax is mainly useful in these two scenarios:

• ABASQL can find a sysdef-file, and from this find the path to the other subsystem.
• You are standing on the top of a structure created with ‑noexec ‑get.

Also, beware that even if you specify the subsystem with the file name, you still need to use the ‑subsystem option where this is required as described above.

ABASQL will respect the configuration settings for the subsystem you specify for the file. As an example, if you say:

abasql -d thatdatabase -S thatserver -subsystem ALPHA BETA!my_sp.sp

Assume that for ALPHA the setting is ‑nocrypt and for BETA the setting is ‑crypt. my_sp.sp will be loaded with the setting in BETA, that is WITH ENCRYPTION.

Using ‑label

The ‑label option may deceive you if you are not careful. It is applied to all files you load, including files referred to by $INCLUDE and $REQUIRE. As long as the argument to ‑label is a real label or a date, you are fine. It is when you specify a version number, you can go wrong.

As long as you specify a single file on the command line, and there is no $INCLUDE or $REQUIRE involved, saying something like ‑label 9 will work fine. But if there are multiple files involved, you need to be careful.

With SourceSafe ‑label 9 means version 9 of every file. They may be some occasions of coincidence where this produces the expected result, but you if you want to load a certain version of a stored procedure that uses an include-file, you will be better off specifying a label or a date that matches what you want to load.

With TFS, it is different. Saying ‑label 6400 will work if you do it right. 6400 in this case is a changeset id, and changeset ids are unique over the database. Therefore, if you ask TFS for a file at version 6400, you will get the version of the file that was current at the time the changeset was checked in. Say that at some point you changed some_sp.sp checked it in with changeset number 6321. As part of the same change, you also changed some_include.sqlinc, which you for whatever reason checked in separately as changeset 6322. If you want to load some_sp to a database, you should specify ‑label 6322 to get the correct version of the procedure and the include file. The entry for the procedure in abasysobjects will have the value 6321 in the column ss_version.

It follows from this that if you want to load the most recent version of a file, the best is not to specify ‑label at all, since this will always give the most recent of all files.

Output from ABASQL

ABASQL always print out the label for the AbaPerls installation you are using. Note that this is not the same as full version information. New labels are mainly set when there are also changes to the database part of AbaPerls. Thus, even if ABASQL says that you have what is known to be the latest label, your installation can be still be out of date, if you run AbaPerls from a local copy.

ABASQL echoes the command options that you have specified. All error messages and warnings from Preppis, Pre-SQL Analysis and SQL Server are printed with line numbers related to the file the message relates to. That is, a message can relate to file1 (or file2, file3 etc), but the message could also relate to a file included by file1. Note that even though AbaPerls may add lines on its own to the code you pass it, the line numbers always relate the original file(s). Thus, the line number you see will always be accurate and point you to where the error is. ...if only SQL Server had been more accurate when it reports the line number. Alas, this is not always the case.

If ABASQL is able to match your current Windows directory with a TFS directory, ABASQL prints a message like:

Inferred VC path $/ABAPERLS/KATI/2.70/SQL from your disk path.

Likewise ABASQL prints an informational message, if it is able to deduce the subsystem implicitly:

Inferred subsystem KATI from the VC path.

If ABASQL finds the file SS‑FILES.LIS in the current directory, ABASQL prints the number of files listed in the file. Keep in mind that when ABASQL has found files in SS‑FILES.LIS, it will change the directory to subsystem\SQL, and read files in that directory structure. Thus, ABASQL will not see files that are in the current directory where you invoked ABASQL.

Messages generated by AbaPerls itself have message number 0 or in some case a negative number. Messages from SQL Server usually have message numbers > 0. The severity level of the message is 11 or higher for errors, 10 for warnings and 9 for style remarks.

If you invoked ABASQL through TextPad, and have configured the TextPad tool properly, you can double-click on the message and TextPad will take you to this line. See further Using ABASQL with TextPad.

ABASQL does not really expect the file to produce any result sets, but would they appear, ABASQL will print these in a fairly primitive manner.

ABASQL prints also a final message, related to your degree of success:

• Congratulations, no errors and no warnings.
• Files extracted, when you use ‑noexec.
• File loaded, but there were warnings.
• File not loaded, because of one or more errors.
• ABASQL even didn't even come as far as attempt to load the file, for instance because the file was missing.

Copyright © 1996-2016, Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 16-09-13 10:45