|   Home   |   Back   |

Examples using the Database-Link

Reading a MS Access Database

Reading a MS Excel 4.0 Spreadsheet

Reading from Sybase SQL Server

Transfering data from dBase to MS Access

Reading from a MS SQL Server

Reading a MS Access Database

Celebrating fifty years of the german Mark the German central bank released a CD-Rom with statistical time series filling a MS Access database with 50 MB. There is a program connected to extract the data but it is still a tedious and time consuming job. Using Mathematica together with the Database Link it is simple to get access to the data. Sadly there is no description on the database delivered. Having no or insufficient documentation is quite common in practical situations.

Load the Database Link and the standard packages Statistics`DataManipulation` and Graphics`

[Graphics:Images/index_gr_1.gif]

Connect to the database (no other setup is necessary)

[Graphics:Images/index_gr_2.gif]

Findout about the different tables in the database using DatabaseInfo.

[Graphics:Images/index_gr_3.gif]
[Graphics:Images/index_gr_4.gif]

The description for the different time series can be found in the table Zeitreihe (time serie) and the values are in the table Zeitreihenwert (time series value). The structure of Zeitreihenwert can be seen by another version of DatabaseInfo

[Graphics:Images/index_gr_5.gif]
[Graphics:Images/index_gr_6.gif]

The are 122 different fields in each record. The field names starting with werte contain the numbers in a long integer format. The devisor is defined in the table Zeitreihe.The field ZW_LfdNr contains the sequence of the records in the table. This is a data format which is optimized for speed but which makes data retrievel a little bit tricky.

Now the list of field names and field type is extracted from the return value of DatabaseInfo

[Graphics:Images/index_gr_7.gif]

From the list of field names the first column is taken and the names matching "werte*" are selected

[Graphics:Images/index_gr_8.gif]
[Graphics:Images/index_gr_9.gif]

Now the SQL statement is build. First by creating a string from the list ...

[Graphics:Images/index_gr_10.gif]
[Graphics:Images/index_gr_11.gif]

...then the curly brackets are replaced with blanks...

[Graphics:Images/index_gr_12.gif]
[Graphics:Images/index_gr_13.gif]

...and the  SQL statement is build, fired and the results are clued together using Join. The identifier WZ0000 in the Sql statement refers to the spread between the short and long term interest rates

[Graphics:Images/index_gr_14.gif]
[Graphics:Images/index_gr_15.gif]

There is still a problem in the data set since there are Nulls where values are missing. They can  be set to zero through applying a rule or dropped using DropNonNumeric.

[Graphics:Images/index_gr_16.gif]
[Graphics:Images/index_gr_17.gif]

Now its easy to work with the data.

[Graphics:Images/index_gr_18.gif]

[Graphics:Images/index_gr_19.gif]

[Graphics:Images/index_gr_20.gif]

top

Reading a MS Excel 4.0 Spreadsheet

In the example sheet is a 250-day history of german interest rates.  It is possible to open Excel in parallel to this session but Excel has to be opened first.

Load the Database Link and Graphics`

[Graphics:Images/index_gr_21.gif]

Connect to the Excel spreadsheet using the dialog box.

[Graphics:Images/index_gr_22.gif]

Use DatabaseInfo to see what is included

[Graphics:Images/index_gr_23.gif]
[Graphics:Images/index_gr_24.gif]

There is the table Interestdata$. Let's try to load it:

[Graphics:Images/index_gr_25.gif]
[Graphics:Images/index_gr_26.gif]

It did not work. Excel needs a specification of the range. The interest rate data is in the range A1 to H251.

[Graphics:Images/index_gr_27.gif]
[Graphics:Images/index_gr_28.gif]

Now it is possible to work with the data

[Graphics:Images/index_gr_29.gif]

[Graphics:Images/index_gr_30.gif]

[Graphics:Images/index_gr_31.gif]

top

Reading from Sybase SQL Server

The sample database for the Sybase SQL Server is the Pubs2 database.

Load the Database Link

[Graphics:Images/index_gr_32.gif]

Open the ODBC control panel dialog box. If you should have access to a Sybase SQL Server, want to follow this example and you do not find the Pubs2 database in the ODBC control panel please ask your system administrator to set it up for you.

[Graphics:Images/index_gr_33.gif]

Use DatabaseInfo to see what tables are available

[Graphics:Images/index_gr_34.gif]
[Graphics:Images/index_gr_35.gif]

To read the data from sales just use the Sql command:

[Graphics:Images/index_gr_36.gif]
[Graphics:Images/index_gr_37.gif]

If you want to see the titles which were sold you have to use the data from sales together with salesdetail and titles. First you could use DatabaseInfo to find out how these two tables are connected. The -1 for the numbers of records just tells you that this information is not provided from the ODBC driver

[Graphics:Images/index_gr_38.gif]
[Graphics:Images/index_gr_39.gif]
[Graphics:Images/index_gr_40.gif]
[Graphics:Images/index_gr_41.gif]
[Graphics:Images/index_gr_42.gif]
[Graphics:Images/index_gr_43.gif]

Now join the two tables. If you want to see the fieldnames in your result set as the first record, set the option FieldNames->True.

[Graphics:Images/index_gr_44.gif]
[Graphics:Images/index_gr_45.gif]

top

Transfering data from dBase to MS Access

dBase sample files ship with Inprise products like C++-Builder, JBuilder or Delphi. If you have one of these products you should be able to follow our example when you found out where the sample databases reside on your hard disk. In dBase each table is its own file. All files together are located in one directory

Load the Database Link

[Graphics:Images/index_gr_46.gif]

Open any of the DBase (*.dbf) files with the file dialog box which pops up when you enter the command DatabaseConnect and use the drop down field for selecting different file types

[Graphics:Images/index_gr_47.gif]

Use DatabaseInfo to see what tables are available

[Graphics:Images/index_gr_48.gif]
[Graphics:Images/index_gr_49.gif]

To read the data from ANIMALS just use the Sql command. If you want to see the field names as the first record together with the data set the option FieldNames->True

[Graphics:Images/index_gr_50.gif]
[Graphics:Images/index_gr_51.gif]

If you want to transfer this data into a new MS Access database just create a new database either using MS Access or the ODBC control panel and connect to the new created database. Now you are connected to two databases

[Graphics:Images/index_gr_52.gif]

Now you are connected to two databases

[Graphics:Images/index_gr_53.gif]
[Graphics:Images/index_gr_54.gif]

Extract the field information for the table Animals

[Graphics:Images/index_gr_55.gif]
[Graphics:Images/index_gr_56.gif]

Create a new table in the MS Access database

[Graphics:Images/index_gr_57.gif]

Insert the data into the new table in the MS Access database

[Graphics:Images/index_gr_58.gif]
[Graphics:Images/index_gr_59.gif]

Close all connections

[Graphics:Images/index_gr_60.gif]
[Graphics:Images/index_gr_61.gif]

top

Reading from a MS SQL Server

With the demo version of MS SQL Server 7.0 comes another pubs sample database.

Load the Database Link

[Graphics:Images/index_gr_62.gif]

Connect to the database pubs with a connect string

[Graphics:Images/index_gr_63.gif]

Use DatabaseInfo to see what tables are available

[Graphics:Images/index_gr_64.gif]
[Graphics:Images/index_gr_65.gif]

Count the number of records in the first ten tables

[Graphics:Images/index_gr_66.gif]
authors
23
discounts
3
employee
43
jobs
14
pub_info
8
publishers
8
roysched
86
sales
21
stores
6
sysalternates
0

Close the connection

[Graphics:Images/index_gr_67.gif]

top


Converted by Mathematica      December 14, 1999