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.

Contents:
   Command-Line Syntax
   Specifying the VC-path
   Specifying the Subsystem
   Using ‑label
   Output from ABASQL

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 ...]
Database switches
‑database Which database to load the file(s) in. ‑database is a mandatory switch.
‑Server Which SQL Server. The default is the server on the local machine. The environment variable ABAPERLS_CTIMEOUT determines how log to wait for a connection. The default is 5 seconds.
    Note that if you specify the server instance by port number, you must enclose the server specification in quotes, for instance ‑S "127.0.0.1,2093".
‑User Which login and password to use with SQL authentication. If you leave out both username and password, AbaPerls will use Windows authentication. If you specify ‑password only, AbaPerls will login as sa. It's illegal to specify a user without a password. If you have a blank password, you need to change this before you can use AbaPerls.
   No matter if you use SQL authentication or Windows authentication, the login you use must map to a database user with dbo as the default schema, or AbaPerls will not let you proceed. Obviously, the user needs permission to perform all actions implied by AbaPerls and the file you load.
‑Password Password for SQL Server. If you do not specify ‑Password, ABASQL will use Windows authentication and log in with your Windows user, which should have been granted access to the database. The user must have dbo as the default schema.
‑noexec Specifies that ABASQL should locate the files and run them through Preppis to get all required include files but not proceed with further steps in the AbaPerls file-loading process. You typically use ‑noexec together with ‑get to prepare for installation at customer sites.
   When you specify ‑noexec, you cannot specify ‑database, ‑Server, ‑Password or ‑save.
Version Control
‑VC

A VC-path for the SQL directory where to find file (and any files it may include through Preppis directives). If the path does not end in /sql, ABASQL adds /sql to the path. This option may be set implicitly, see below under Specifying the VC-path.
    If the database is labelled as a production database, ABASQL will require you to load the file from version-control, if the existing version of the file in the database was also loaded from version-control.

‑label Version number, date or label in the version-control repository that ABASQL is to read from. See below about using the ‑label option.
‑nouse_disk Specifies that no files are to be read from disk, but that ABASQL should retrieve all files from version-control. 
‑get With ‑get, ABASQL creates a directory with the same name as the subsystem specified with the ‑subsystem option, and in that directory it places the files it extracts from version-control in an AbaPerls SQL directory structure. ABASQL also writes information about the extracted files to SS‑FILES.LIS.  Typically you use ‑get together with ‑noexec as the first step when installing a file at customer site     
    ‑get implies ‑nouse_disk and you cannot combine ‑get and ‑use_disk. If you specify ‑get, you must also specify ‑VC. Unless AbaPerls is able to determine the subsystem implicitly (see below), you must also specify ‑subsystem.
   If you current Windows directory maps to a folder in TFS, ABASQL will not permit you to use ‑get.
Miscellaneous
‑subsystem Specifies that the file(s) belong to the subsystem subsys, and that the configuration options saved for subsys are to apply. If you specify a subsystem that is not present in the database, ABASQL will not load the file. If you do not specify ‑subsystem, ABASQL retains the subsystem stored in database for the file, but it will not apply the configuration options for that subsystem. For new files, ABASQL will leave subsystem unset in this case. Files that are not connected to any subsystem are reported in their own section by ap_sob_report_suspects_sp.
   AbaPerls may be able to determine the subsystem implicitly, relieving you from having to specify this option. See further below the section Specifying the Subsystem.
‑nocreate Specifies that ABASQL should not create the objects in the file(s), but only delete them. ABASQL can only delete the objects handled by Pre-SQL Analysis. ‑subsystem is mandatory when you specify ‑nocreate.
‑force Specifies that if there is a conflict between the configuration options saved in the database and the configuration options on the command-line, the command-line takes precedence. If you do not specify ‑force, and there is a conflict, ABASQL will abort with an error message, and no file will be loaded. You can also use ‑force to bypass some checks performed by the AbaPerls file-loading process.
   Only use ‑force when you really need to bypass checks or override the configuration saved in the database.
‑log Logs the output from ABASQL into the given file. Normally, an existing file is overwritten, but if you prepend the file name with ++, ABASQL appends to an existing file. When you specify ‑log, the subsystem must be specified.
‑save The generated SQL code is to be saved in a file. If you specify ‑save without save-file, ABASQL will save the generated code in install.sql. Use this switch if you want to know exactly what ABASQL sends to SQL server. ‑save is intended to be a debug aid only. Don't consider running a save-file on another database; any such usage is unsupported.
‑LoadRequire Forces load of files in $REQUIRE directives. By default, ABASQL only opens the file listed in $REQUIRE to check that there is a matching $USEDBY directive, but does not run the file. This option overrides that behaviour.
Configuration options
‑crypt
‑Macro
‑quoterepl
‑site
‑undef
These switches are configuration options. The setting of a configuration option is primarily taken from the database according to the precedence rules for configuration-options. If the database defines a setting for an option and you specify a different setting, you must use ‑force to override.
Argument
file1 file2... The file(s) that for ABASQL to send to SQL Server. ABASQL uses the AbaPerls file-lookup order to find the files. file may reside in a subdirectory to one of the special AbaPerls directories. In this case, specify the file with the subdirectory, for instance sub/nisse_sp.sp. If the subdirectory has a name that ends in an exclamation mark, ABASQL will only load the file, if a subsystem with that name (modulus the exclamation mark) is present in the database.

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:

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:

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: