How Do I Create a Database Table Using Magic xpa? (Magic xpa 3.x)

It is very simple to create a database table in Magic xpa. All you need to do is specify the columns in the table, and Magic xpa will handle the details of creating the table in the DBMS. Furthermore, the process is basically the same no matter what kind of data source you are creating, whether it is an ISAM file, an SQL table, or a memory table. Even XML file definitions follow the same basic format.

There are two basic processes for working with SQL data sources in Magic xpa:

1. The table does not exist, and we want to create it in Magic xpa and in the SQL database

2. The table already exists in an SQL database, and we want to bring it into Magic xpa

The second case is covered in How Do I Access an Existing Database Table?. The first case is covered here.

Here is a summary of the basic steps. We’ll go into them in more detail below.

• Make sure the gateways and DBMS are loaded

• Set up the database definition: Load the gateway for the DBMS, set up the database definition.

• Create the table: Set up the table in the Data repository.

• Create the columns: Create definitions for each of the columns you want in the table.

• Create the indexes: Create definitions for each of the indexes you want in the table.

• Syntax check the table: Use the Magic xpa syntax checker to check for errors (F8).

• Test the table by creating a few records: Generate a simple Browse program (Ctrl+G) to check that it works.

If the database definition is set up correctly, Magic xpa will automatically take care of the details of creating the SQL table definition.

Note: While these instructions are slanted toward SQL tables, creating the table as a memory table or ISAM file is almost the same. The differences have to do with setting up the database definition, and the fact that ISAM files and memory tables don’t have the same naming constraints.

Make sure the gateways and DBMS are loaded

1. Before you can set up the database, you have to make sure the drivers are loaded. This is set up in the Magic.ini. When Magic xpa installs, the MAGIC_GATEWAY section is set up automatically depending on what Gateways you chose during installation.

2. If you are accessing a new DBMS type, it’s a good idea to check the Magic.ini, and the installation directory, to make sure these are installed correctly.

3. Also, of course, the actual DBMS client must be installed on the machine you are working on. In this example, we are accessing the Pervasive ISAM database, and the MS SQL database, so both of those products must be installed on our machine, and running when we start Magic xpa.

4. If you had to add the gateway in this step, then you will need to close and restart Magic xpa before you go to the next step.

Set up the database definition

The database definition is the key to how the data source specification is used. In fact, one data source specification can be repeated and used with several database definitions, so that the same data source could be a memory table, and SQL table, or an ISAM table, depending on which database definition it points to.

1. With no project open, go to Options->Settings->Databases. The DBMS you are using should show up on the DBMS list. Select the DBMS you want to use. Our example uses the Microsoft SQL Server.

2. Next, you need to choose the Database name. This is the name of the database in the DBMS. In our example we use two databases, Northwind and TestExamples.

   

3. While Magic xpa can create tables in the database, you have to create the databases themselves in the DBMS manager (or use existing ones).

4. Press Alt+Enter (Edit->Properties) to set up the user id and password as needed. Also, if you are going to create tables within the database from within Magic xpa, then you need to set Properties->Options->Change Tables in Studio to Yes.

Create the table

Your next step is to create the actual table definition. While you do this, you need to keep in mind the constraints of the particular DBMS you are using. Magic xpa does not have many constraints, so while you are using memory tables you don’t have to think much about such things as naming conventions. SQL, however, has constraints about what characters are allowed.

1. Open your Magic xpa project.

2. Go to the Data repository (Shift+F2) and open up a line (F4). Here is where you create your table.

Create the columns

Click on the bottom area, on the Column tab, to create your columns. For each column, use F4 to open up a line, then enter the data as shown below. Each line will show the basics for that column. More details are shown in the Properties pane (Alt+Enter) for each line. Setting up the column definition is very much like setting up the variables in a program.

Next, you need to set up the more detailed definition of this field in the Properties Pane (Alt+Enter). For most fields, the defaults will work (or you will have set them up in your field model). However, this is the section where you can indicate exactly how the data will be store in the SQL database.

An important thing to keep in mind here is that there are two descriptions for each bit of data. Magic xpa keeps the Magic xpa data attributes simple and generic (Alpha, Numeric, Date, Time, etc.). But you also have control over the DBMS-specific definition for the data (ZString, LString, Integer, Float, etc.) and the actual SQL definition (CHAR(50), INTEGER). By default, Magic xpa will choose the DBMS definition that seems to make the most sense, but you can override this if you like. This is explained in more detail in How Do I Define the Mapping Between a Magic xpa Field and a Database Column?.

Hint: It is always best to use Models to encapsulate most of your data definitions. This allows you to set the SQL defaults in one place.

Create the indexes

Next, you will need to create the indexes. Do this in the bottom area of the screen by clicking on the Indexes tab.

Syntax check the table

After you have your columns and indexes entered, use the syntax checker (F8, or Options->Check Syntax) to make sure you don’t have any major errors.

1. Position the cursor on the table you want to check.

2. Press F8.

The errors, if any, will appear in the Checker pane.

Test the table by creating a few records

Last, make sure you can create some records. The easiest way to do this is to use the Program Generator utility. You can do this easily:

1. Position the cursor on the table you want to check.

2. Press Ctrl+G (Options->Generate Program).

3. Click OK.

A browser window should appear. You should be able to view, modify, and add records from within the browser. If you can do that, then you know the table is okay. You will be able to view the table you just created in the SQL DBMS using the DBMS tools.

Once you have the table created in your DBMS, keep in mind that you need to keep the definition in the DBMS and the definition in Magic xpa synchronized. It is best to always maintain the definition in one place or the other. If you change the definition in Magic xpa, the Magic xpa will automatically reconfigure the table in the DBMS, if Properties->Options->Change Tables in Studio is set to Yes for that database.