Building Applications Using TOR DBMS

Our NCR WorldMark 4800 system consists of two nodes, each having 4 Pentinum III processors running at 500 MHz. Each node has 32 GB of system disk space and 144 GB of RAID storage space. These two nodes are cstor01.cs.wright.edu (upper node) and cstor02.cs.wright.edu (lower node). Each node runs Microsoft Windows NT Server 4.0, Enterprise Edition and the NCR Teradata Object Relational (TOR) DBMS.

Client Application can access the TOR DBMS from any PC installed Windows 98/NT and TOR client software. We have two ways to execute DB operations on our database on TOR DBMS server across the network:

1)  Through TOR client console installed on client PC.

2)  Through client applications developed by yourself through ODBC interface

In both two ways, we need to specify the upper node, cstor01.cs.wright.edu, to connect because it is the Query Coordinator.

In the following demo, we will describe each step of using TOR DBMS and developing DB application on TOR. In the example, we assume there is a user called John and he has an account on TOR DBMS. Let us see how to do step by step:

Step 1. Create John’s database by using TORQ client software from a PC

1)  Running the TORQ console

2)  On TORQ console, connect to an account on TOR DBMS server.

3)  On TORQ console, create database whose owner is John

4)  On TORQ console, create table and drop table

5)  On TORQ console, use these SQL operations: insert, select, delete, update

6)  On TORQ console, close database

7)  On TORQ console, disconnect the account and quit TORQ

Step 2. Create one ODBC Data Source for developing application

Step 3. Develop application by using ODBC interface

Step 4. Running and reading the demo

This demo is just a simple example and it will issue most often used SQL statements on one table student_info created by user John.

Step 1. Create John’s database by using TOR client software from a PC

Make sure that you have already installed TOR client software on your PC and you can access cstor01.cs.wright.edu( you can ping it ). If TOR client software was already installed on your PC and the install directory is e:\ncr\tor, you will find three executable files in directory e:\ncr\tor\bin:

perl.exe

torq.exe

torexport.exe

And you can also find a include directory, a lib directory, and a sample directory. They will be helpful in your future developing.

  1. Execute the TORQ console

From Start -> programs -> command prompt , enter the “command prompt” window

Then enter the TOR client software install directory, execute the TORQ command as follow:

Note: -h option specifies the host which is the query coordinator of TOR system.

Now we already access TOR DBMS server. Here, we also introduce another way to execute TORQ as following :

Start -> programs -> NCR TOR

  1. On TORQ console, connect to an account on TOR DBMS server.

Here, we assume that TOR administer already created a new user whose user name is john and password is iamjohn.

  1. On TORQ console, create database whose owner is John

After successfully connecting to TOR DBMS, John will create a database for his DB application.

  1. On TORQ console, create table or drop table

Then, John can create some tables in his database. In the example, he created a table student_info. You also can use the command to delete a table:

Ready > drop TABLE mytable ;

  1. On TORQ console, use these SQL operations: insert, select, delete, update

John can issue insert, select, delete, update SQL statement on his tables.

  1. On TORQ console, close database

After all these operations, John can close database

7. On TORQ console, disconnect the account and quit TORQ

Step 2. Create the ODBC Data Source for developing application

The DB application based on ODBC interface has the following architecture:

In this figure, the Data source modules refer to physical database with different DBMSs. While the term “Data Source” we mentioned here is just like a profile which is one link between the first level Application and the second level ODBC Driver Manager. We use the profile, Data Source, to describe which ODBC driver will be used in the application and the application will connect which account on which database.

The following figures will show each step of creating the Data Source used in our application.

choose the start -> settings -> control panel

choose ODBC Data Sources

choose “Add” button on the window

choose the Teradata ORDMBS ODBC Driver.

John has a database on TOR DBMS server and cstor01.cs.wright.edu is upper node.

After these steps, we already create a Data Source called ExampleDataSource.

Step 3. Develop application by using ODBC interface

The demo was developed on Windows NT by using VC++ 6.0 . We can use ODBC interface to access the table student_info. You need to include the following files:

#include <sql.h>

#include <sqlext.h>

#include <odbcinst.h>

#include <odbcss.h>

#include <odbcver.h>

Then you also need to link odbc32.lib. In VC++ 6.0 develop studio, you can set the link option to finish the link operation when compiling your program. Another way is directly adding this lib file into your project.

The ODBC program scheme is as following ( this is a code segment of executing select

SQL statement ):

SQLAllocEnv(&DSNhenv);

SQLAllocConnect(ODBChenv, &ODBChdbc);

SQLConnect(ODBChdbc, DataSourceNname, DBusername, DBuserpassword );

SQLAllocStmt(ODBChdbc, &ODBChstmt);

Construct the SQL command string

SQLExecDirect(ODBChstmt, (UCHAR *)command, SQL_NTS);

if (ODBC_SUCCESS)

{

SQLFetch(ODBChstmt);

while (ODBC_SUCCESS)&(data set is not empty)

{

processing the data

SQLFetch(ODBChstmt);

}

}

SQLFreeStmt(ODBChstmt, SQL_DROP);

SQLDisconnect(ODBChdbc);

SQLFreeConnect(ODBChdbc);

SQLFreeEnv(DSNhenv);

When you call ODBC function SQLConnect() in your program, you need to specify the Data Source you created on step 2. Our demo is just a simple example, though it uses almost all functions we often used in most applications. So for more details about ODBC programming, please see related ODBC reference and VC++ reference.

Step 4. Running and Reading The Demo

Copy all files of the project to your PC and double click the file ODBCexample.dsw . This will load the Win32 project into VC++ 6.0 develop studio automatically. Then you can choose manu item “build” and “execute ODBCexampl”. The VC studio will automatically compiles and links the program.

Now we show what the demo does:

choose command

Here, you can see all Data Source profiles defined on you PC. We can choose ExampleDataSource created on the step 2. And we will use the user name john and the password iamjohn. Then we click the button “Connect To Database”, we can connect the TOR DBMS server. Now we can click “>” button to enter next window sheet.

Now after you press the button “Get Information of All Tables In The Database”, you can see all tables in your database including mydatabase created on step 1. Then we can choose one table from the most left listbox and enter the next window sheet by clicking button “>”.

You can click button “Get Scheme of the Table” to see the definition of the table.

And if the table you choose is student_info , you can press the button “Run SQL on This Table” to execute SQL statement on the table.

After you input the select SQL statement in the edit box, you can press button “Get Information” to execute it. And we also can update and delete one student. If you want to add a student, please click “Add Student”.

The above is describing how to run the demo.

If you’d like to see any piece of code for a specific ODBC function, you can load the project into VC++ 6.0 develop studio and double click the related resouce. Fox example, if you are interested in how to execute a select SQL statement, please enter the resource compiling window and double click the button “Get Information” in the last figure. The code for the button will be displayed for you.