How Do I Define a Connection to a Database? (Magic xpa 4.x)
Magic xpa is very good at connecting to multiple databases, including Oracle, SQLServer, DB2, ODBC, MySQL, AS400, DB2/400 and Pervasive. You can even link to multiple databases located on multiple servers, simultaneously. Before you use the databases though, you need to define them to Magic xpa.
This is done in several steps:
A. Checking the Database Gateway
B. Defining the Database
C. Checking the Connection
Each of these steps is explained below. In addition, the connection is defined slightly differently depending on the database involved. These differences are also explained below.
When you install Magic xpa, you need to select the Database gateways that you might use in the future, so the appropriate DLLs are installed. If you didn’t install them when you first installed Magic xpa, you can use Start->Control Panel->Add or Remove Programs, selecting Magic xpa, and pressing the Repair/Modify button, or, use the installation disk. Don’t uncheck any existing Database gateways, but add checkmarks for the new ones you want to install.
-
Now, in the Magic.ini file you are using, you need to make sure the appropriate databases are un-commented and that the path is correct.
-
After you edit the INI, start Magic xpa.
Now you are ready to define the database.
-
In the Name column, give the database any name you like. This name is used for readability only.
-
Select DBMS for the Data Source Type.
-
For the Database Name, type in the actual Database name as it is defined in the database manager. The database needs to have been previously defined. In this example, Sales is defined in SQLExpress.
-
Zoom from the column marked “DBMS”. The list that pops up will show the Magic Databases that were found in the Magic.Ini, as described in the previous section. In our example, we are using Microsoft SQLServer. Now, press Alt+Enter to access the Databases’s properties.
-
In the Login tab, set up the Database Server name. User Name and User Password need to be entered if the database requires them. In our example we are using Windows authentication, so no user name or password is required.
-
In the Options tab, select Change Tables in Studio if you want to maintain the table in Magic xpa. If the tables are created by another application, you will not want a programmer to change it accidentally, so you would un-check this box.
-
In the SQL tab, you can add SQL commands that will be used to connect to the tables. If you want Magic xpa to create the table, check the Check existence box.
You can make sure the Database was set up correctly by using the Get Definition option in the Data repository. You can see how to do this in How Do I Access an Existing Database Table?.
To define an Oracle Database, set the DBMS column to Oracle. The Oracle alias already points to the database, so you do not need to specify anything in the Database Name column.
In the Database properties, the Database Server should get the Host String, while the User name and password are the same.
To define an DB2 Database, set the DBMS column to DB2. The DB2 alias should be typed in the Database Name column.
For an ODBC database, set the Database name in Magic xpa to the Data Source Name in ODBC. Also, for some databases, such as ODBC_MSSQQL and ODBC_MySQL, you will also need to set the user name and password.
For a Pervasive ISAM database, you don’t need to set up a database within Pervasive. The ISAM files are created like any other operating system file.
Set the DBMS name to Btrieve. You can specify a path in the Location column if you want. You can also specify the location in Data repository, in the Data source name column.
