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.)
Macro expansion is currently not recursive. Thus, if the macro
&kalle, Preppis will not try to expand
but AbaPerls will pass the string
&kalle to SQL
There are two kinds of macros, short and long. Short macros are macros that are expanded within a line and can appear anywhere in the file outside comments. 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.
The AbaPerls file-loading process strips all comments from the input file before passing it to Preppis. Thus, Preppis never sees macros or directives that appear in comments. Macros can include comment delimiters, but it is up to you to keep track of what it leads to. Thus, such usage is discouraged.
If a macro appears in a string literal it is expanded as in this example:
SELECT @x = 'Kalle Anka &co'
Preppis will attempt
to translate the macro
&co and emit an error message if
&co is undefined.
(On the other hand the string
'Kalle Anka & co', will not cause an error,
because a lone ampersand is never interpreted as a macro.)
This table lists the directives in Preppis, divided into categories.
|Include a file.|
|Include a file, unless it is has already been loaded|
|State that file is known to depend on the current file.|
||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 like:
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
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 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. 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 the file mentioned in the $USEDBY directive does not exist in the current subsystem, DBUPDGEN does not include the file in the update script. This is a good thing when an include-file is shared in SourceSafe between multiple subsystems. (Unfortunately, there is no way to refer to an include-file in a different subsystem directly.)
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:
The argument to $USEDBY is not macro-expanded.
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.
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.
As for $MACRO, the definition of long macro can include other macros, short or long, and these macros are expanded as a part of the definition process.
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-2012,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 12-02-07 16:03