Data Tables, ADO, and Access Databases

  The Data Table Object allows you to input data into columns which are associated with Mathscript vectors. These Data Objects are Microsoft Access 97 Database Tables. Once you have a Table, you may access it using SQL queries to select a part of it according to some logical criterion. We make use of these databases as Active Data Objects (ADO).

This means that you do not have to have a database program to use these data bases, but you should have the Microsoft Access 97 Database installed as a Data Server. To be sure that your setup is ready to use them, just go to the Control Panel, and under ODBC Data Sources, be sure that the User DSN: MS Access 97 Database is selected, or type it in.

The discussion that follows is contained in the trials Microworld that accompanies this Author package, and may also be found in Tutorial Project 3. If you would like to follow along, use Windows Explorer to browse to C:\Program Files\Bluejay Lispware\Mathwright32 Author\microworlds\trials. You will find the interaction described below on page 2 of trials.

  The name of the Access Database Table is always printed at the top. It must be a single word. For the trials example, the Table was selected in the page script as we came into the page. You may see this if you get the page menu by right clicking on the background.

  It reads:

  select "data";

  A data table (even if it is the only one on the page) must be selected before it is used. Its columns can hold 3 types of data, but each column should hold the same type. These types are: name, number, and string. In commands and functions, we encode

 the types as:

 function_names = 1

 numbers = 2

 strings = 3

 The ID "column" on the far left is merely an index, and is not associated with a MathScript vector. All other columns represent MathScript vectors with the same name, but they must be updated, as we shall see shortly. Mathscript vectors, and data table columns are indexed with positive integers. The first index is always 1 (not 0).

  The Commands and Functions: entry, getentry, getvector, and setvector:

  The basic command for placing data in a data object is: entry.

  In the yellow command line at the bottom of the page type (Press enter after each semicolon)

  entry 1, 1, 1;

  entry 2,1,4;

  entry 3, 1, 9;

This enters three numbers in the first column, called column1 here.

  The general syntax is: entry row, column, value;

Now column1 is a vector, as are column2 and column3.

To see its value type:

  print column1;

  You see that it is still the original the length-1 vector [ 0.0 ]. There are two ways to update it.

The first is to click on the column caption column1. Try it.

The system asks whether the entries are names, numbers, or strings. Accept numbers.

Now type:

print column1;

  And you see the vector [1, 4, 9]. This means that the vector with name column1 now has the value [1,4,9].

  You may of course type entries into the table directly, but they will have to be updated to associate screen values with the vectors.

  Now try:

entry 1, 2, "this string";

  The entry in row 1, column 2 is the string "this string".

  Update this as before by pressing the caption "column2". And type:

  print column2;

  You see:

  [this string]

  If you type:

  print column2(1);

  You see:

  this string

  Now the identification of data table columns with vectors is very powerful. You are probably asking:

 How do you update columns to the vectors with the same name under script control?

 In column 3 type the numbers: 5, 6, 7. To do this, click the cell, and when you see the write symbol (*) , type the number.

Then click the next cell, and so on. When you are finished, be sure to dismiss the write symbol by clicking away from the cell.

Type:

 print column3;

  And the vector column3 is not yet identified with the entries. Now execute in the command field:

 print getvector(3,2)

 You see:

  [5,6,7]

  The value of the vector column3 is still [0], but the return value of getvector(column, type) is a Mathscript vector. If you had typed

 make column3 getvector(3,2);

 you would have set the value of the vector column3 to the third column. Now, the companion function to the command entry is getentry(row, column)

  This function simply returns the name, number, or string in the (row, column) position of the table.

  Thus,

  print getentry(3,3);

  returns 7;

  Another useful function is: setvector(column, vector, type).

  If you type:

  setvector(1, [2,3,4,5], 2);

  You set the first column to the vector of numbers [2,3,4,5]. This also updates the value of column1 to be [2, 3, 4, 5].

  So if you type:

  print column1;

  you will see [2,3,4,5].