Property

Description

Information for SQL Database

Lets you supply database-dependent information that Magic xpa can pass to the underlying RDBMS. The use of this property is optional.

Note: For Hibernate data sources, if the column name in the original database is different than the column name in the Space database, you should write the original column name in this property so that the Hibernate Data Source Builder will be able to create the appropriate file.

See also Database Information - Database Property.

Owner

The database’s owner of the table or view. This property supports logical names.

Position

This setting determines the Position index for a table. The available options are:

Default – Magic xpa uses its own default as a Position index for a table.

Row ID – In Oracle tables, Magic xpa uses the ROWID column.

Unique index – In other RDBMSs, Magic xpa uses the shortest unique index. This can be overwritten by using another index as the Position index.

Index

If the position was chosen as Index, then the Index property will allow you to choose one of the table’s unique indexes.

Default Position

This is the unique identifier that Magic xpa uses as the default position. This property is read-only.

Magic xpa chooses an automatic unique identifier with a minimum number of segments (not the shortest).

Check Existence

Check Existence determines whether Magic xpa checks the existence of every SQL table it tries to access in Runtime, and creates the table if it does not exist. The available options are:

Yes – Magic xpa can create tables in the database and also check for the existence of every table it tries to access. This check may cause performance degradation.

No – Magic xpa will not check if the table already exists before accessing it. If the table does not exist, a database error message appears. In Runtime, you should set the Check Existence property to No for enhanced performance.

As Database (default) – This option uses the value of the Check Existence property from the Database Properties dialog box.

Table Type

The table type can either be Table or View. If the table type is View, the DbDel and DbCopy functions will not work, because a View cannot be created, deleted, or altered by Magic xpa. If you do get a definition or a View, you must define a unique index as the Position Key.

Hint

Some RDBMSs allow hinting the optimizer for processing a query. In this field the programmer can enter a string that will be concatenated to the SELECT statement.

Yes – Activates the Hint property. The hint will be used when this property is set to Yes and when the Hint value is blank.

No – De-activates the Hint property.

Magic xpa does not evaluate the string. It is the developer’s responsibility to use correct syntax. It is recommended to only use Hints in special cases.
The Hint value can be inherited from the Data Source properties (only when it is set to Yes).

Relevant interfaces: MSSQL and Oracle

See also How Do I Affect the Database Optimizer Behavior?

Cursor

When using the MSSQL gateway, either internal DB commands or cursors are used. Using DB commands requires separate connections for each result set. Performance is enhanced when the result set is large.

The available options are:

Yes – Enables the use of cursors on the specific table

No – Disables the use of cursors on the specific table and uses DB commands instead

Default – Magic xpa uses cursors or DB commands according to the following considerations for MSSQL tables:

• Magic xpa uses cursors when the MSSQL table is designated as the main task table.

• Magic xpa uses DB commands when the MSSQL table is designated as a linked table.

Relevant interfaces: MSSQL

Default value: Default

Array Size

The Magic xpa gateways to the various SQL databases support array processing. When fetching records from the database, the gateway fetches a group of records, rather than one record at a time, thereby reducing network traffic. The array size is the number of rows fetched together from the database in a single fetch operation. The data is stored in an engine-side array.

When the default value of 0 is set:

• For Online tasks – The array size is the number of records of the Online screen multiplied by 5.

• For interactive Rich Client tasks – The array size is the chunk size multiplied by 5.

In the following scenarios, the array size is calculated because all records are fetched:

• Main Data source for batch task

• DSQL online / RIA that fetches all records into the temporary table

• Magic sort

• Data source for choice controls

• DataViewToxxx functions

• Loading a resident table into the memory table

In these scenarios, the number of records in the array are calculated according to the following formula: Since version: 3.0a

floor(Cache size for array fetching / The record's length)

The Cache size for array fetching is set in the Cache size for array fetching environment setting.

When the array size is greater than 0, the array size is used as is and no calculation is done.

Relevant interfaces: MSSQL, Oracle, DB2/400 and Space (IMDG)

Default value: 0

See also How Do I Reduce Database Access?