Database Resource (Magic xpi 4.5)
Magic xpi stores information about database resources in the Settings dialog box. This contains a list of the databases that have data that can be mapped using the Data Mapper.
Database resources contain the following parameters (parameters in bold are mandatory):
|
|
|
|
DBMS
|
The type of database. Select the database type from the drop-down list. Microsoft SQL Server, Oracle, ODBC, DB2/400, DB2, and Pervasive SQL are included in the list.
The defaults for the database types are saved in the following file: <Magic xpi installation>\Studio\data_types.xml.
|
|
Database Name
Connect String
Data Source Name
Database Alias
Library/Schema Name
|
Enter the following depending on the type of database:
|
Note:
|
If the database name includes a space or a hyphen (-), or ends with a plus sign(+), you must surround it with square brackets. For example, [My Database Name].
|
|
Note:
|
You have to use an alias to connect to Oracle via Magic xpi, even if the database is local.
|
-
For ODBC/Pervasive enter the ODBC Data Source name. Make sure that the ODBC Data Source is set in your computer's Windows settings. Magic xpi can connect to table names that are a maximum of 30 characters. If you have a table name that is longer, see this Technical Note for further information about handling it.
|
Note:
|
When you are using a Data Mapper with an ODBC resource and you delete the ODBC resource from the Resources section in the Settings dialog box, the schema will contain the default ODBC data types and not the ODBC data types from the deleted ODBC resource. The defaults appear in the data_types.xml file.
|
-
For DB2/400, click the Library button to select from the libraries available in the DB2/400 Libraries dialog box. If you select a non-existing library, it will not appear in the wizard or during runtime.
|
Note:
|
Verify that the appropriate Database gateway is uncommented in the [MAGIC_GATEWAYS] section of the Magic.ini file. If the entry is commented, which means that it has a semicolon (;) before it, remove the comment to enable the gateway.
|
|
|
Server Name
|
The name of the physical computer where the database is installed. For DB2/400, the server name cannot exceed 19 characters.
|
|
User
|
The name of the user who is authorized to work with the database. The user name should not contain a comma (,). See also Filters for Database.
|
Note:
|
Make sure the name entered in the User field also exists on the server where the database is located.
For DB2/400 databases:
-
On the IBM i server where your database is installed make sure that the User Profile for the User entered in the Settings dialog box's Resources section does not contain the number 65535 in the Coded character set ID option.
-
Multiple sessions to the same server with a different user login are not supported. Magic xpi takes the first session to the server that it finds.
|
|
|
Password
|
The user’s password. The password should not contain a comma (,) or a percent sign (%).
|
|
Starting owner
|
The owner to filter with when using this database entry. Click Owner to open the Owners List. Select the required owner from the list.
If this property is blank, the owner will be set to the first owner. Since version: 4.5
See also Filters for Database.
|
Database resources also give you the following additional buttons:
|
|
|
|
Validate
|
Click this button to verify that the connection to the database is valid. You will receive a message indicating that the connection is established, or an error message if the connection failed. When you select the DB2/400 database, you can click this button to check accessibility to libraries. A table selection form displays tables from specified libraries, prefixed by the library name (not by owner). When you select a table, the owner details are displayed at the bottom of the screen. The Available Columns list in the Database Wizard column selection form displays columns from the tables that you selected in the library/schema.
|
|
Table
|
This button opens the Filters for Database window. This window lets you select which tables are used in this database connection.
|
|
Owner
|
This button opens the Owners List. Here, you can select the required starting owner for this database entry.
This button is not available when you select a DB2/400 database.
|
|
Library
|
This button is only available when you select a DB2/400 database. It lets you select one or more libraries to work with.
|
|
Online/Offline
|
Determines whether the Data Mapper will attempt to connect to the configured database whenever a Data Mapper document is open. Click this button to toggle between Online and Offline (default) modes.
If the button is set to Offline, the cache will be loaded. If the button is set to Online, Magic xpi will attempt to connect to the database to retrieve the current tables' structure.
A connection will be forced to the database even if the DB schema belongs to an Offline resource, when:
-
Selecting the Refresh option on the schema node
-
Changing the SQL Statement in the properties sheet
-
Changing the schema resource
-
Opening the DB wizard
If the connection fails, the last cached data will be used.
You can refresh multiple database schemas by clicking the Refresh Database Schemas  icon on the toolbar or by clicking the Edit menu and selecting Refresh Database Schemas. This functionality is available only when a Data Mapper document is in focus in the Studio and when there is at least one database schema that belongs to an Online resource
Since version: 4.5
|
|
Note:
|
You can use environment variables for the following fields:
Environment variables are an alias for the actual database that let you dynamically define the field. Environment variables must be surrounded (back and front) with a percent (%) sign. You must also define the environment variable in the Environment Variables Repository. For more information on environment variables, see Using Environment Variables.
If there are two or more libraries defined with environment variables that translate to the same value, the "Incomplete libraries/schema definition" error appears.
When two libraries (real library names) are defined with the same value (name), you cannot leave the Libraries screen if you type or select a duplicate name.
|
