Database Information - MSSQL (Magic xpa 2.x)
The Database Information parameter in the Database Properties, Data Source Properties, Column Properties and Index Properties dialog boxes lets you supply database-dependent information that Magic xpa can pass to the underlying DBMS. The use of this parameter is optional.
Multiple keywords should be delimited by one or more spaces.
Some of the keywords may not require values to be specified. All of the keywords and their values must be specified in uppercase.
For the values that can be sent in the Database Information parameter see:
|
Keyword
|
Description
|
|
|
|
|
|
CHECK_KEY=Y
|
Checks the data table for the existence of a unique index value as the end user attempts to input a data into a record. If a duplicate value is input by the end user into the variables constructing an index defined as Unique in Magic xpa’s Data repository, Magic xpa will issue a Duplicate Index error message and prevent further end-user processing until the input is changed. For some DBMSs, this check takes a toll in performance and it is not recommended. In such a case, the developer may decide not to use this keyword, and then the results of duplicate index value entry will depend on the DBMS behavior.
|
v
|
|
|
|
|
FORCE_INDEX=N
|
Forces the optimizer to use an index.
|
|
v
|
|
v
|
|
MAGICKEY=N
|
For a data source with no unique index, adds a column that will be the unique index. Magic xpa will insert values automatically to this column. The column that is the MAGICKEY should be selected.
|
|
v
|
v
|
|
|
MSDATE=Y
|
If this keyword is set to N, the default attribute is changed to Alpha. The date format will be YYYY-MM-DD-HH24:MM:SS.
|
v
|
|
|
|
|
SQL_APPLICATION_ROLE = [Role Name]
SQL_APPLICATION_ROLE_PASSWORD = [Password]
|
The connection to the database can be done under a specified application role using these two keywords. These keywords have no default.
Secret names can be used to define the role name and password.
|
v
| | | |
|
SQL_ DATETOALPHA=N
|
Automatically converts the RDBMS date field to a Magic xpa Alpha Zstring field with a length of 19 characters and with the format YYYY- MM- DD HH: MM: SS.
Note: Neither Magic xpa nor the RDBMS can perform data validation on an Alpha_ Date field due to the use of the internal date format in the RDBMS. If you use the SQL_ DATETOALPHA parameter you should implement your own validity checks for Insert and Update operations. Otherwise, invalid dates can be inserted in the database.
|
v
|
|
|
|
|
SQL_PHYSICAL_LOCKING=Y
|
MSSQL supports physical locking at the row level as a default, when using physical transactions.
To use logical locking, add the flag: SQL_PHYSICAL_LOCKING=N
When this flag is set to Y or the flag is not set at all, Magic xpa sends the UPDLOCK table hint in the FROM clause as described below:
SELECT a,b
FROM tab1 (UPDLOCK NOWAIT)
ORDER BY a
|
v
|
|
|
|
|
SQLBLOB = size
|
When using the TEXT data type in a Direct SQL statement, the maximum BLOB size will be 64K. To change that size (either increasing or decreasing it) use the SQLBLOB = size keyword.
The default value is 65534 and the maximum value is 2147483647.
This keyword is relevant for:
1. Stored procedures using DSQL.
2. DSQL SELECT operations with a non-MSSQL result database.
This keyword is irrelevant for:
1. Browse tables. Instead, use the size of the BLOB before fetching the BLOB data.
2. DSQL SELECT operations with an MSSQL result database. Instead, temporarily create a table; before fetching the data, Magic xpa calculates the size of the BLOB field.
|
v
|
|
|
|
