Preppis is a preprocessor based on the same ideas as the well-known preprocessor in C/C++. The habitual C/C++ programmer should be aware of that some things work differently with Preppis than in C/C++.
Preppis is part of the AbaPerls file-loading process, and you execute Preppis indirectly through ABASQL, DBBUILD and the update scripts generated by DBUPDGEN. There is no command to execute Preppis independently.
When Preppis reads a file, it looks for two types of strings, directives and macros:
|Directives|| Directives start with
a dollar sign (|
|Macros||Macro names start with
an ampersand (|
If Preppis finds a string which fits in with these descriptions, but there is no such directive or macro defined, this is an error. (With the exception of macro names in $IFDEF and $ELSEDEF directives, see below under Conditional Compilation.) Preppis does not recognize directives and macros inside comments, string literals and quoted identifiers.
There are two kinds of macros, short and long. Short macros are macros that are expanded within a line and the line can include other code as well. Long macros are multi-line macros, and they must appear alone on a line, see further below under $MACRO_LONG.
Short macros are defined in several ways.
A long macro can only be defined with the $MACRO_LONG directive.
This table lists the directives in Preppis, divided into categories.
|Including (and referring) files|
|Include a file.|
|Include a file, unless it is has already been loaded.|
|State that file is known to depend on the current file.|
|State a dependency to another file without loading it.|
||If the expression is true, then include the code that follows.|
||If no previous expression was true, and this is, include the code that follows.|
|If no expression was true then include the following code.|
|End of conditional compilation.|
|Define a macro.|
|Define a multi-line macro.|
|Closes the definition of a multi-line macro.|
|Remove a macro definition.|
|Include a DLL as a hex-string, only permitted in assembly files.|
|Compile a CLR source file in a .Net language, only permitted in assembly files.|
||Specifies a key file to use when compiling a CLR source file, only permitted in assembly files.|
|SQL clause, which causes Preppis to look for a $USEDBY in the assembly file.|
|Delimits code which is execute before the pre-work code generated by AbaPerls.|
|Assign a module some special permission on database or server level respectively. These macros are not covered on this page, but in the topic Modules with Special Permissions.|
‑noexec switch is in force, Preppis does not expand macros, and processes
directives $INCLUDE, $REQUIRE, $DLLINCLUDE, $COMPILE, $KEYFILE, EXTERNAL NAME and $USEDBY.
You use the macro $INCLUDE to include another file. The include-file must have a $USEDBY directive for the including file (see below). The file you include must have the extension .sqlinc and reside in the INCLUDE directory. $INCLUDE is only permitted in the following types of files: .sp, .sqlfun, .tri, .sqlinc, .view and .vtri. (In earlier versions of AbaPerls it was permitted to include any file from any file, but this was removed, since the consequences for update scripts were unclear.)
Preppis looks up the include-file according the AbaPerls file-lookup order. Thus the same rules apply to include-files as to all other files you pass to AbaPerls.
An include-file can include other include-files. If you $INCLUDE the same file more than once in the same execution, the contents of the file is included each time.
The argument to $INCLUDE
is macro expanded. (But since macros are not expanded when you run with
this may be of limited use.)
A typical use for $INCLUDE is to make it possible for a stored procedure to reference a temp table created by a calling stored procedure without the need to have the CREATE TABLE statement in two places. Here is an example. compute_sp performs some complex computations and places the result in the table #compute that calling stored procedures have to create. So in caller_sp.sp it would look this:
CREATE PROCEDURE caller_sp @param int AS $INCLUDE compute.sqlinc -- Other declarations and initialisations. EXEC compute_sp @comp_param, @comp_param2 -- Further processing.
In compute_sp.sp you create the temp table before the stored procedure, like this:
$INCLUDE compute.sqlinc go -- This go is required, as CREATE PROCEDURE must be the first in batch. CREATE PROCEDURE compute_sp @comp_param int, @comp_param char(4) AS -- Various stuff INSERT #compute (...) SELECT ... FROM ...
In compute.sqlinc we find this:
$USEDBY compute_sp.sp $USEDBY caller_sp.sp -- See below about $USEDBY, which is mandatory here. CREATE TABLE #compute (first_column int NOT NULL, second_column ... )
Notice that there must not be any
go in compute.sqlinc, as
go would be
inserted in caller_sp.sp and sever the second half of the code in the file
from the stored procedure.
(The knowledgeable reader may have some questions on this use, so let me briefly answer them. Q1) this is not necessary with deferred name resolution, so why? A1) AbaPerls thinks deferred name resolution is bad, and checks for references to non-existing objects. Read further about Object checking. Q2) Wouldn't it be better to implement compute_sp as a table-valued function. A2) Yes, sometimes. But compute_sp may have to call other stored procedures. And even with an UDF, the callers may need the table declaration, because they will do further processing on the table.)
Another typical example where $INCLUDE comes in handy is dynamic search conditions. Say that you have a complex SELECT statement that could return data for all customers, or only one. It could be about impossible to have one SELECT statement to cover both cases and get good performance for requests on a single customer. You could put the SELECT for all customers in an include-file and then add this line at the end:
Then you would have two stored procedures. The one to get data for a single customer would look like this:
CREATE PROCEDURE get_data_for_customer @customerid int AS $MACRO &more_conditions tbl.customerid = @customerid $INCLUDE get_data.sqlinc
The procedure to get all customers would take no parameters and just define
&more_conditions to be blank:
CREATE PROCEDURE get_data_for_all_customers AS $MACRO &more_conditions $INCLUDE get_data.sqlinc
When passing data in a temp table between stored procedures, it is not inconceivable that the procedures are in different subsystems. Say for instance that in the example above, that compute_sp.sp and compute.sqlinc are in the subsystem ALPHA and caller_sp.sp is in the subsystem BETA. The $INCLUDE directive in caller_sp.sp would then use this syntax:
And the $USEDBY directive in compute.sqlinc would read:
That is, you prefix the subsystem name separated with an exclamtion mark before the filename. To find the path to the subsystem, AbaPerls uses the following search path:
‑get, they will use that structure to locate the file.
Beware that the feature is designed with the existence of a sysdef-file in mind and you should be very cautious with using $INCLUDE across subsystems if you are not using sysdef-files.
If the source file is loaded without the
‑label option or with
AbaPerls loads the most recent version of the include-file. When the source file
is loaded with
‑label, AbaPerls will determine the label for subsystem of the
include-file this way:
On the surface, $REQUIRE may seem similar to $INCLUDE, but the two directives serve very different purposes. The purpose of $INCLUDE is to incorporate a piece of code here and now. The purpose of $REQUIRE is make sure that a file has been loaded, so that the current file will load properly. This is the mechanism to ensure that DBBUILD and update scripts generated by DBUPDGEN load files in the correct order. To take an example, say for instance you have two views outer and inner and the definition of outer refers to inner. In this case outer.view should include this line:
$REQUIRE inner.view go
to guarantee that inner.view is executed before outer is (re)created.
A $REQUIRE can result in file being inserted into the calling file, just like with $INCLUDE, but it does not happen in these cases:
As a mirror of the second point: If DBBUILD or an update script first loads a file through $REQUIRE, and then encounters the file itself in the build, they will not load the file a second time.
As for $INCLUDE, the file loaded by $REQUIRE must include a $USEDBY directive for the referring file and AbaPerls checks that $USEDBY is present, even if the file is not loaded.
$REQUIRE is only permitted in the following combinations:
Why these combinations and no others? Since DBBUILD and update scripts generated by DBUPDGEN load one type of files at a time, you mainly need $REQUIRE to ensure the load order when a file depends on a file of the same type, and when a file of one type depends on a file of another type which is loaded later. One combination you may be missing is stored procedures calling other stored procedures. $REQUIRE appears a bit unpractical here, due to the sheer volume. While you get a warning if you load procedures in the wrong order, the tool LISTERRS takes care of this situation.
For an example of $REQUIRE in use with INSERT-files, see the example that comes with INSFILGEN.
The $USEDBY directive is used to state that a file references the current file. You need to use $USEDBY when you use any of the directives $INCLUDE, $REQUIRE, $DEPENDSON and EXTERNAL NAME.
For instance, if file my_sp.sp says
$INCLUDE temptbl.sqlinc, and temptbl.sqlinc does not say
my_sp.sp, this is an error. If temptbl.sqlinc in its turn
includes other.sqlinc, other.sqlinc needs to have
other.sqlinc does not need a $USEDBY for my_sp.sp.
The main consumer of the $USEDBY directives is DBUPDGEN. When DBUPDGEN finds that a file that could include a $USEDBY directive, DBUPDGEN reads the file and adds the files listed in $USEDBY to the update script, even if these files themselves are unchanged. This includes the files in different subsystems, which thus will be loaded as part of the update script of the subsystem where the $USEDBY appears. (See further the topic for DBUPDGEN for details.) The same applies if a DLL or a CLR source file has changed; in this case DBUPDGEN will search the corresponding .assem file for references.
If DBUPDGEN cannot find the file or the subsystem listed in $USEDBY, DBUPDGEN does not give any warning or any error, nor does it include the file in the update script. (As this could be an obsolete reference. If the file name in $USEDBY is incorrect, the error should be detected when the referrring file is loaded.)
You specify the name of the includer for $USEDBY as you always specify names with AbaPerls. That is, AbaPerls looks up the file according to AbaPerls SQL directory structure. That is, if you include a file from some_sp.sp that is in the SP directory, you should say:
On the other hand, if the file is in the sub-directory sp\sub, you should say:
And if the file is in a different subsystem you say:
The argument to $USEDBY is not macro-expanded.
Just like $INCLUDE and $REQUIRE, $DEPENDSON must be mirrored by a $USEDBY in file, but in difference to the other two directives, $DEPENDSON does not cause file to be loaded; AbaPerls only opens file to check that the mirroring $USEDBY is there. As discussed above, $USEDBY permits DBUPDGEN to add an unchanged file to an update script, if the file containing the $USEDBY has changed and that is the ultimate purpose of $DEPENDSON.
$DEPENDSON is mandatory when a file has a dependency to another file, such that when the object defined in the file listed in $DEPENDSON is changed, AbaPerls may have to drop the object(s) in the file where the $DEPENDSON directive appears. More precisely, it applies to these cases:
Mandatory here means that if $DEPENDSON is missing when you load the file with ABASQL you will get an error. To be compatible with existing code, this is only a level 9 message with DBBUILD and update scripts generated by DBUPDGEN before the $DEPENDSON directive was introduced. Newer script considers missing $DEPENDSON to be an error.
In the case you need to use $REQUIRE, you do not also need to add $DEPENDSON as $REQUIRE counts as $DEPENDSON.
It follows from the first item in the list above, that $DEPENDSON just like $INCLUDE have cross-subsystem references.
You can also add $DEPENDSON to state dependencies to .typ and .xmlsc files. However, this is not mandatory, as AbaPerls does not drop any objects on its own when such files changes.
A special case is the dependency between an assembly and the objects derived from it. You don't use $DEPENDSON directive in this case, but in this case, EXTERNAL NAME, which is part of the regular SQL syntax for defining objects from assemblies also serves as a Preppis directive.
With conditional compilation you can specify that some parts of a file is to be included or excluded according to some condition. In this way you can have different code for different versions of SQL Server, or different code at different customer sites.
A block of conditional compilation starts with one of the directives $IF or $IFDEF, followed by zero or more $ELSEIF or $ELSEDEF, then zero or one $ELSE and closes with a mandatory $ENDIF. After each $IF, $IFDEF, $ELSEIF, $ELSEDEF and $ELSE comes a sub-block of zero or more lines of other code – SQL code or other Preppis directives, including a new $IF or $IFDEF starting a nested block of conditional compilation. The first sub-block that follows an $IF, $IFDEF, $ELSEIF or $ELSEDEF that evaluates to true is included in the code that is passed to SQL Server, and the rest of the blocks up to the closing $ENDIF are discarded. If no $IF, $IFDEF, $ELSEIF or $ELSEDEF has an expression that evaluates to true, the sub-block following any $ELSE will be passed to SQL Server.
The expression that follows $IF and $ELSEIF is macro-expanded like regular code, and undefined macros will yield an error message. For $IFDEF and $ELSEDEF the macro-expansion is done in an unique way: undefined macros are expanded to 0 and defined macros to 1 regardless of their actual value.
After macro-expansion, Preppis passes the expression to Perl for evaluation. This has the side effect that if the syntax is not correct, the error message is likely to be more or less cryptic, not the least for a user who is not very well versed in Perl. On the other hand, I was saved the task of writing a parser.
Note that normally Preppis does not evaluate directives in a sub-block excluded by conditional compilation. This means that if you have something like:
$IFDEF &Use_current_version $INCLUDE this_file.sqlinc
$ELSE $INCLUDE this_othher_file_I_cant_spell.sqlinc $ENDIF
Preppis will not give you an error for the missing include-file if the macro
&Use_current_version is set. However, when
‑noexec is in force, Preppis
does not process the conditional-compilation directives, and thus in this case
Preppis will process all $INCLUDE and $REQUIRE
directives in the file and you will get an error in this example.
Since Perl is used to evaluate the expressions, there is a vast offering of available operators that you can use. I would encourage you to stick to the simple stuff, though. Here is a short list of the operators you are most likely to use.
|String concatenation. (+ is numeric addition and nothing else. )|
Test on (in)equality for numeric values. If you use these operators on
strings, they will be interpreted as 0 in most cases, so beware! Please
note that test on equality is
||Test on (in)equality for strings. They must be written in lowercase.|
Logic operators. These must be written in lowercase.
Example on use (For the predefined macros
&SQL2008 see below.)
-- Filtered indexes were added in SQL 2008. $IF &SQL_version >= &SQL2008 CREATE UNIQUE INDEX fac_isuptodate_ix ON fifoacquisitions (status, acqdatesorter, datesorters, facid) WHERE status IN ('N', 'E', 'D') $ELSE CREATE UNIQUE INDEX fac_isuptodate_ix ON fifoacquisitions (status, acqdatesorter, datesorters, facid) $ENDIF -- Different way of doing things at different customer sites. $IFDEF &Compaq or &Dell -- They want it this way. $ELSEDEF &HP -- But HP wants it that way. $ELSE -- Everyone else go for the standard way. $ENDIF
With $MACRO you define a new macro, or change the value of an existing. The value of the macro is the text that follows the macro name. If there is no text, the value will be an empty string, but the macro will still be defined.
If the value definition includes a macro, Preppis expands this macro before defining the
new macro. From this follows that
the PRINT statement below will print
$MACRO &kalle 'Ada' $MACRO &nisse &kalle $MACRO &kalle 'Lovelace' PRINT &nisse
In difference to the C preprocessor, Preppis does not offer any possibility to use parameters in macros.
Preppis does not expand macros inside a comment, a string literal or a quoted identifier. That is, the following line will not be modified in any way by Preppis. (But as long as the configuration option quoterepl is in force, the double quotes in the example will be replaced by single quotes.)
SELECT [&nisse], "&nisse", '&nisse' /* &nisse */
On the other hand, you can use any of the delimiters
<> within the macro
expansion and for the first four pairs, the you get the macro value enclosed in
those delimiters. This does not happen with the angle brackets. The purpose of
the angle brackets is to permit you to delimit the macro name from characters
directly following. This is useful when you want to form several names from one
base macro. Here are some examples of delimited macros:
$MACRO &nisse Ada SELECT &'nisse', &"nisse", &[nisse], $<nisse>_Lovelace
This expands to:
SELECT 'Ada', "Ada", [Ada], Ada_Lovelace
This example is not very intelligent, but imagine that you have a number of tables that all have the same basic structure (but they describe separate entities, so they should really be separate tables) and the column names follow a common pattern. You want a set of stored procedures that all should be the same structure, so you put the main meat in an include file. You could have code in that include-file which goes like this:
SELECT &<tla>id, &<tla>name FROM &tablename WHERE &<tla>id = @&<tla>id
&tablename would be macros that are
defined in including file.
If you want to use a macro in the middle of a string, you need to put it into variable and work with that.
$MACRO &mystring This is my string DECLARE @mystring varchar(200) = &'mystring' SELECT 'Didn''t I tell you? ' + @mystring + '! Keep your hands away'
If you include comments in a macro definition, AbaPerls will strip out the comments before the the file is passed to Preppis, and the comment will thus not be included in the value of the macro.
Defines a multi-line macro. All lines that follows up to the directive $ENDMACRO are part of the macro definition. One case where long macros are useful is when you have a complex SELECT statement with optional search condition and you want to make use indexes as much as possible. Then you can do something like:
$MACRO_LONG &BASE_SELECT SELECT .... FROM ... WHERE ... $ENDMACRO IF @thatpar IS NOT NULL BEGIN &BASE_SELECT AND thatcol = @thatpar END ELSE IF @thispar IS NOT NULL BEGIN &BASE_SELECT AND thiscol = @thispar END ...
When you use a long macro it must be alone on a line (save leading and trailing spaces). If there is a syntax error within the long macro, the AbaPerls File-loading Process will refer you to the line in the macro definition. Long macros cannot be expanded with delimiters like short macros can be.
If the definition of the long macro includes other macros, Preppis will by default expand these macros as the macro is defined. You can override this by providing the argument NOEXPAND to the macro definition. This instructs Preppis to define the macro without expanding the other macros, but instead these macros are expanded when the long macro is expanded. This permits you reuse a chunk of code where the same logic operates on the same tables or columns. Here is a quick example:
$MACRO_LONG &update_column NOEXPAND &this_column = 2 * &that_column $ENDMACRO UPDATE tbl SET $MACRO &this_column targetcol1 $MACRO &that_column sourcecol1 &update_column $MACRO &this_column targetcol2 $MACRO &that_column sourcecol2 &update_column
That is, before each usage of the long macro, you redefine the short macros to define to the next set of columns.
The definition of a long macro cannot include any other Preppis directive.
Removes a previously defined macro, be that a short or long macro.
There are a number of predefined macros for SQL versions.
|The version of SQL Server of the server you are
connected to. For instance |
You cannot redefine or delete these macros.
When you compare
&SQL_version in an $IF directive to one of the predefined
macros for different versions of SQL Server, or some version string that you
supply, Preppis only compares the part of the version string that is supplied on
both sides. Say that
&SQL_version has the value
10.50.1600.1. Then the following expressions are true:
&SQL_version == 10 &SQL_version == 10.50 &SQL_version > 10.50.1200 &SQL_version lt 10.60
As you see from these examples, here it does not matter whether you use the
operators for numeric or character comparison, but beware that this is a special
== when you should have used
eq is a common error,
even among seasoned Perl programmers.)
This permits you to make the comparison with SQL version as fine-grained as you need. In most cases, you only care whether you are on SQL 2005, SQL 2008 etc. But occasionally you may need different code depending on service pack. E.g., a bug in the RTM version may call for a workaround that is slow, so you don't want to use it on a server where the service pack has been applied.
(Those who know Perl should know that these comparisons are unrelated to the
rules that Perl has for comparing its version. If I tell you that the actual
value of the macro
new VersionCmp('6.00'), then you might be able to guess the
These directives are covered in more details on the page Using the CLR with AbaPerls. Here follows a short reference.
This directive is only permitted in .assem files and instructs Preppis to compile a source file in the language specified by the first argument. Currently the only supported values are C# and VB. A $COMPILE directive must be followed by a $DLLINCLUDE directive to include resulting DDL file as a hex-string in the CREATE ASSEMBLY statement, see further this directive below.
The remaining arguments to $COMPILE are assumed to be compiler switches and
they are passed to the command line for the compiler. Preppis always passes
/keyfile, if $KEYFILE is present. You should never specify
these switches yourself.
Preppis finds the source file to compile through the AbaPerls file-lookup order. The source files should reside in the ASSEMBLIES directory.
This directive is only permitted in .assem files and will read a file with the same name of the .assem file with the extension .dll and include the file as one long hex-string.
Say that you have a file abacalc.assem, that looks like this:
CREATE ASSEMBLY abacalc AS $DLLINCLUDE
Preppis will read abacalc.dll and, the resulting SQL will look something like this:
CREATE ASSEMBLY AS abacalc AS 0x4D5A9000030000...
If the $COMPILE directive is not present in the file, Preppis finds the DLL through the AbaPerls file-lookup order. (When $COMPILE is present, this directive specifies where the DLL is.)
$DLLINCLUDE accepts one optional argument, which specifies that the assembly is to have a certain permission. This is discussed in detail in the topic Privileged assemblies in the CLR page.
This directly is only permitted in .assem files, and only when the $COMPILE
directive is present. It specifies a key file to be used to sign the assembly
EXTERNAL NAME is a T‑SQL clause that appears in the definition of a CLR object. For instance:
CREATE PROCEDURE clrsp @z int AS EXTERNAL NAME clrsp_assem.StoredProcedures.testclr
Preppis reacts on EXTERNAL NAME and reads clrsp_assem.assem to verify that this file includes this line:
When loading an assembly, AbaPerls faces the problem that ALTER ASSEMBLY is not always possible. For instance, say that you have an assembly that implements a stored procedure, and you add a parameter to the CLR routine. ALTER ASSEMBLY fails in this case, since the definition of the stored procedure no longer agrees with the assembly. In this case AbaPerls will drop all objects that depend on the assembly. By including all files that depend on the assembly in the update script, DBUPDGEN can ensure that no objects will be lost from the database.
When AbaPerls processes a file, it may generate one or more statements that are executed before the file itself, the so-called pre-work phase. This may be code to drop existing objects. Occasionally, you may have need to execute code that is dependent on the objects being dropped. In this case you can put this code between $PRELUDE and $ENDPRELUDE.
Currently, preludes are only permitted in Service Broker files (extension .sb) as this is the only place a real need has been identified. (Be able do detect active conversations on services defined in the file), but the concept could be extended to other files types in the future.
Between $PRELUDE and $ENDPRELUDE you cannot use other Preppis directives. Macros are expanded. You can only have one active prelude section in a file. That is, it is only legal to have multiple $PRELUDE directives in a file, if they are in different $IF branches.
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:42