Configuration Options

This page details all configuration options, as well as the precedence rules between the various places where you can specify configuration options.

Contents:
    Introduction
    The ANSI Options (-Adefaults, -Anulls, -Apadding and -Aquoted.)
    Character-Set Conversion (-charset)
    Encrypting SQL Objects (-crypt)
    Classifying the Purpose of a Database (-environment)
    Defining and Undefining Macros (-Macro, -undef)
    Replacing Double Quotes (-quoterepl)
    Site-Specific Files (-site)
    Replication Options (-subscriber)
    Config-file Options (-onlylabel, -notlabel)
    Precedence Rules

Introduction

Configuration options control how ABASQL, DBBUILD and the update scripts generated by DBUPDGEN load files into the database. Typically you specify the options with DBBUILD in a config-file or on the command line. Then ABASQL and the update scripts generated by DBUPDGEN reads the options from the database and apply them as if you had specified them on the command line to these tools. The condition is that you have installed the ABAPERLS subsystem so that the AbaPerls system tables are present in the database.

The point with saving the settings in the database, is that it permits you specify once for all that, for instance, all stored procedures in a database are to be stored encrypted. Or that a certain macro should apply to the database (or just a single subsystem).

You should probably exercise some caution with specify configuration options on subsystem level, because when developers manually loads files with ABASQL, they may not specify -subsystem, in which case the default settings and the settings on database will apply.

You can override the settings saved in the database by specifying a different setting for the configuration options on the command line, see more below under Precedence rules.

To learn how to specify the configuration options in the config-file, see the config-file page.

Here is an summary of all configuration options. Each option is detailed below.

Option	Default	Purpose
`-[no]Adefaults`	On	Setting for SET ANSI_DEFAULTS and others.
`-[no]Anulls`	On	Setting for SET ANSI_NULLS.
`-[no]Apadding`	On	Setting for SET ANSI_PADDING.
`-[no]Aquoted`	On	Setting for SET QUOTED_IDENTIFIER.
`-charset CP`		Charset to convert the loaded files into.
`-[no]crypt`	Off	Add WITH ENCRYPTION to stored procedures etc.
`-Macro &macro[=value]`		Define a Preppis macro.
`-[no]quoterepl`	On	Replace `"` as string delimiter with `'`.
`-site site-id[:site-id...]`		Define one or more site-id:s.
`-[no]subscriber`	Off	Database is to be a subscriber database in replication.
`-undef &macro`		Undefine a Preppis macro.
`-onlylabel`	See below	When searching for labels, consider only labels with this pattern.
`-notlabel`		When searching for labels, do not consider labels with this pattern.

The ANSI Options

-[no]Adefaults, -[no]Anulls, -[no]Apadding, -[no]Aquoted

When you specify -Adefaults, AbaPerls emits these SET commands:

   SET ANSI_NULL_DFLT_ON ON
   SET ANSI_NULLS ON
   SET ANSI_PADDING ON
   SET ANSI_WARNINGS ON
   SET ARITHABORT ON
   SET CONCAT_NULL_YIELDS_NULL ON      -- Not on SQL 6.5.
   SET QUOTED_IDENTIFIER ON

When you specify -noAdefaults, AbaPerls instead emits commands to turn all of these OFF. For information of the effect of all these SET commands, please see SQL Server Books Online.

With the options -Anulls, -Apadding and -Aquoted you can control the setting for ANSI_NULLS, ANSI_PADDING and QUOTED_IDENTIFIER individually. Note that if you specify, say, -Anulls on database level, and then specify -noAdefaults on subsystem level, that also turns of ANSI_NULLS for that subsystem. That is, -[no]Adefaults is a catch-all for all ANSI settings.

If you do not specify any of these options, -Adefaults is assumed, that is all settings above are in force.

Note that, even if QUOTED_IDENTIFIER is ON by default with AbaPerls, you cannot use double quotes (") to delimit identifiers unless you specify -noquoterepl, see further this configuration option below.

Normally, you would only turn off any of these options if you have a legacy application that has not adapted to the ANSI settings. In case you are unacquainted with the consequence of these, here is a background:

There are a couple of features in SQL Server that requires the following SET commands to be ON: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER. (and NUMERIC_ROUNDABORT must be OFF.):

Index on views.
Index on computed columns.
XQuery and XML type methods.
Queries accessing linked servers. (ANSI_NULLS and ANSI_WARNINGS only.)

(This list may not be exhaustive, as new SQL Server versions typically adds more item to the list.)

The settings must be in effect both at run-time and when you create the object in question. Some of these settings are pure run-time settings; that is the application can issue the correct setting when it connects, and that's it. However, SQL Servers saves some of these settings with the object, so that the setting that applied when the object was created applies at run-time too no matter what the user specified on connection level.

More precisely, the settings of ANSI_NULLS and QUOTED_IDENTIFIER are saved with the objects when you create stored procedures, views, functions, tables and triggers, and the settings of ANSI_PADDING is saved when you create a table column. Thus, if you use features like indexed views or XQuery, it's essential that you have the correct settings when you create objects, since else you will get trouble at run-time.

As for the other settings controlled by -Adefaults, ANSI_WARNINGS, ARITHABORT and CONCAT_NULL_YIELDS_NULL control run-time behaviour only, and thus are only likely to be an issue for you in INSERT-files when you run AbaPerls. Finally, ANSI_NULL_DFLT_ON will have an important effect on all tables you create without specifying NULL/NOT NULL for your columns. But AbaPerls thinks it is a lousy idea to rely on defaults in this case, and will emit style warnings if you omit NULL/NOT NULL.

Note: the T-SQL command SET ANSI_DEFAULTS ON also activates IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT, but AbaPerls always turns these off when connecting, which is in agreement with how ODBC and OLE DB behaves. AbaPerls also always explicitly turn off NUMERIC_ROUNDABORT when connecting.

Character-Set Conversion

-charset CP

Specifies which character set that the source code should be converted to before being sent to SQL Server. Normally this would be the same as the default character set of the server. The default is to send the source code to SQL Server without any conversion.

Example: you have your files in ANSI format, but you need to load them into a database which has CP850 as its character set. For this database you would specify -charset 850 in the configuration file. (Or on the command line when you build the database with DBBUILD.)

Encrypting SQL Objects

-[no]crypt

With this option in effect, ABASQL, DBBUILD and the update scripts generated by DBUPDGEN adds the WITH ENCRYPTION clause to the source code of stored procedures, functions, triggers and views before sending the source to SQL Server. If you include WITH ENCRYPTION yourself in the source code, you must not use -crypt, because else AbaPerls will insert a second, giving you a syntax error.

Default is -nocrypt.

Classifying the Purpose of a Database

-environment DEV | TEST | PROD

You can use this option to state to AbaPerls whether the database is for development, test or production. For test and production databases, the AbaPerls file-loading process will perform checks of the version number stored in the system table abasysobjects versus the version number of the file currently being loaded. The purpose of these checks is to prevent (PROD) or alert (TEST) that an update script overwrites a higher version that has been installed manually. See further the file-load page, the section Checks for Production and Test Databases about these checks.

As for other configuration options, you can have a one global setting for the database, and have something different for a certain subsystem. Be advised that setting the environment on database level to DEV, and then have TEST or PROD on some occassional subsystem is not that bright idea, as this will permit developers to circumvent the checks for TEST and PROD by simply not specifying a subsystem. The most likely scenario in which you like to have a different environment for a single subsystem, is with a new subsystem that you prefer to set as TEST or DEV, while the database has a stricter régime.

This configuration option is different from the other config-options, as it can only be used in a config-file and with DBBUILD, but you cannot override it with ABASQL or an update script generated by DBUPDGEN.

The default is DEV, that is a development database for which there are no particular checks.

Defining and Undefining Macros

-Macro &macro=[value]
-undef &macro

Defines (-Macro) or undefines (-undef) a Preppis macro.

If you do omit value, the macro will be defined to have the value of the empty string. Note that you still have to provide an equal sign. AbaPerls currently permits you to omit the equal sign, but in this case the value of the macro is set to 1, due to the current behaviour of the module Getopt::Long, but a future version of Getopt::Long (which comes with Perl, not AbaPerls) could change this.

Note that on the command line, you need to enclose the macro name (and the value for -Macro) in double quotes, as & is a meta character in the command-line shell. This does not apply to config-files.

If you in a config-file need to include a macro like space in the value for a macro, you should put the -Macro option on a line on its own, preceded by a period, see the config-file page for further details.

You can include several -Macro or -undef to define or undefine any number of macros, as well as on the command line as in the config-file, both on database level and on subsystem level.

Replacing Double Quotes

-[no]quoterepl

With this option in effect (and it is in effect by default), ABASQL, DBBUILD and the update scripts generated by DBUPDGEN replaces all occurrences of double quotes (") as string delimiters with single quotes ('). Any nested single quotes in the string are doubled, so that the string syntax remains correct and the resulting string at execution is unchanged. Here are some examples:

   SELECT "This is a string"       => SELECT 'This is a string'
   SELECT "It's five o'clock tea"  => SELECT 'It''s five o''clock tea'
   SELECT """Heroes"""             => SELECT '"Heroes"'
   RAISERROR('Error "%s"', 16, -1) => RAISERROR('Error "%s"!', 16, -1)
   EXEC ("SELECT '" + @val + "'")  => EXEC (''SELECT ''' + @val + '''')

When would you use -quoterepl?

You have legacy code which freely uses double quote to delimit strings, and you don't want to change all that code.
You program a lot in Visual Basic where double quote is the only string delimiter, and you have a problem to relearn for T-SQL.
You want to be able to use two types of quotes when you nest strings, for instance in dynamic SQL. See further the note below.

When would you specify -noquoterepl?

You want to delimit identifiers that includes special characters with double quotes to be ANSI compliant. For instance:
```
   SELECT * FROM "Order details"  
```
Note that with SQL Server 7 and later you can use also square brackets [] to delimit identifiers. See also Known Issues for limitations of using quoted identifiers with AbaPerls.

Note: As seen from the next-to-last example above, AbaPerls does not change double quotes that are part of strings, even if the delimit a string nested in that string. From this follows that if you use EXEC() or sp_executesql to delimit dynamic SQL, you should use single quotes as delimiters in strings that are nested within the dynamic SQL code. If you use double quotes in the nested strings, and QUOTED_IDENTIFIER is ON, you are likely to get errors when the dynamic SQL code is executed. (AbaPerls turns on QUOTED_IDENTIFIER by default. You can control it with the configuration options -Aquoted and -Adefaults.)

Site-Specific Files

-site site-id[:site-id...]

Defines site-id:s that apply to this database or subsystem. If a filename includes an at sign(@), the part that comes after the @ is the site-id. Such a file will only be loaded by ABASQL, DBBUILD and the update scripts generated by DBUPDGEN if and only if the site-id has been specified with -site.

Example: say that we have the files PARAMETERS.TBL, PARAMETERS.INS, PARAMETERS@KR.INS, PARAMETERS@LG.INS and PARAMETERS@IBM.INS. The first file defines the table parameters, the second file base data which is added at all sites, the next file data which is installed at all Korean sites, and the last two files are specific to the two sites LG and IBM. The config-file for Lucky Goldstar (LG), would then look like:

   -site=kr:lg

this would cause all files in the example to be loaded, save PARAMETERS%IBM.INS. Without any -site at all, AbaPerls would load only the two @-less files.

Files with site-id:s may appear anywhere in the AbaPerls SQL directory structure, with one exception: you can not have site-specific versions of GRANT.TEMPLATE.

You can only have one -site on database level, and only one per subsystem. A -site on subsystem level completely overrides any -site on database level.

When you generate an update script with DBUPDGEN, and the main file has changed, all site-specific files are also included in the update script, even if they are unchanged.

Note: site-id:s is an older way of single out site-specific information with AbaPerls. The preferred method today is to use the Preppis macro $IFDEF. See also the command INSFILGEN for a way to merge the four PARAMETERS.INS into one file.

Note: AbaPerls also permits using % as the separator, and this was in fact the original choice. However, there were bugs with in SourceSafe with % in file names, and Microsoft resolved this by outlawing the use of % in names at all.

Replication Support

-[no]subscriber

Specify -subscriber if the database is to become a subscriber part in replication. -subscriber will cause ABASQL, DBBUILD and the update scripts generated by DBUPDGEN perform the following modifications of the source before sending it to SQL Server:

No rules are created or bound. (Default values are created and bound as stated.)
IDENTITY columns are created as regular columns, without IDENTITY. This only applies to TBL files, not to temp tables in stored procedures.
The datatype timestamp is replaced with varbinary(8). Applies only to .TBL files.
The files TBL/*.TRI and TBL/*.FKEY are not loaded.

The idea is that once the replication has been set up, you should specify that there is to be no synchronization. If you have built two empty databases, one with -subscriber and one without, you have to assure yourself that they are the synchronized.

The default is -nosubscriber.

Note: -subscriber was developed during an abandoned experiment with replication under SQL 6.5. SQL7 and SQL2000 provides improved replication support that AbaPerls has not considered.

Config-file options

-onlylabel regexp
-notlabel regexp

These two options are different from the other configuration options, since they affect how the config-file itself is interpreted, and they are not saved in the database.

These two options control which labels AbaPerls considers when it searches for the labels of a project, when there is no version-designator in the config-file. The argument is a regular expression according to Perl rules. By default AbaPerls considers only labels that match the pattern ^[A-Z]\d+\.\d+\.\d+$, that is the standard AbaPerls label format of LetterMajor.Middle.Minor. You can use these options to change this. To include all labels in the search, specify -onlylabel with the argument of a single dot.

While would you use these options? One possible situation is that you have non-standard labels, and yet want these to be considered. Another possibility, is that there are labels that do adhere to the standard AbaPerls format, but which should apply to the SQL part of the project. Presuming that these labels are still distinguishable in some way, for instance uses a different Letter, you can use -notlabel to have AbaPerls to discard those labels.

While these options are accepted on the command line to DBBUILD and DBUPDGEN, they currently do not have any function, but they only have effect if you specify them in the config-file.

Precedence Rules

The settings for a configuration option can have several possible sources:

In the config-file on database level.
In the config-file on subsystem level.
In the database on database level.
In the database on subsystem level.
On the command line.

If a configuration option is specified in more than one place, there may be a conflict. More precisely there is a conflict in these cases:

`-Adefaults -Anulls -Apadding -Aquoted -crypt -quoterepl -subscriber`	E.g. `-crypt` in one place, and `-nocrypt` in another. There is also a conflict if `-Adefaults` is specified in one place, and one or more of `-noAnulls`, `-noApadding` or `-noAquoted` are specified in another or vice versa.
`-site -charset`	Different values, e.g. `-site mnbpb:fi` in one place and `-site fi` in another.
`-Macro -undef`	Different values or different states for the same macro. E.g. `-Macro &nisse=1` in one place and `-Macro &nisse=2` or `-undef &nisse` in another. Thus, definitions of two different macros are never in conflict with each other.
`-onlylabel -notlabel`	Since these options are not stored in the database, and are not functional on the command line, there can not be conflicts with these options.

These are the rules for which source that applies. First without taking the command-line in regard:

If there is a conflict between subsystem level (2 or 4) and database level (1 or 3), the subsystem level takes precedence.
1 and 2 can apply only to DBBUILD, never to ABASQL or an update script created by DBUPDGEN.
When running DBBUILD, 1 and 2 apply when:
- when there is no subsystem present in the database, i.e. you build a new database.
- when you specify -rebuild, which will erase all saved options and replace them with the contents of the config-file.
For DBBUILD, 3 and 2 apply when you add a new subsystem to a database in which one or more subsystems are already present. In this case database-level options in the config-file are discarded.
For ABASQL and the update scripts generated by DBUPDGEN, 3 and 4 always apply.
4 never applies to DBBUILD. (Because either DBBUILD creates the subsystem, in which case there are is nothing to read from, or -rebuild was specified, in which case the database never applies.)

And now we add the rules for the command line.

If you do not specify the command-line switch -force, there must be no conflicts between the command-line options and the other sources, neither on database level nor on subsystem level.
If you specify -force, the command-line switches will be in force, and override the options on subsystem level and database level in case of a conflict. Options from 1-4 that are not in conflict with the command-line still apply
With -force, -Adefaults on the command line overrides the settings from 1-4 for all three of -Anulls, -Apadding and -Aquoted. Any of these three on the command line only overrides that particular setting, and the remaining ANSI settings are determined by the setting of -Adefaults.
In the case you add a new subsystem with DBBUILD, the command-line options you specify will be saved on database level, even if you only add one subsystem using the -subsystem option. Any conflicting options specified in the config-file for the subsystem will not be saved. (Thus, the only way to save settings on subsystem level, is to use a config-file.)
No options saved in the database can ever be changed with ABASQL or an update script generated by DBUPDGEN; you can only override what is saved with -force for the current invocation.