Microsoft SQL Server Concepts Overview

A quick overview of some basic design concepts underlying the Microsoft SQL Server (MSSQL) Database Management System (RDBMS) will help you interface your COBOL program to it.

Servers

An MSSQL "server" is one copy of the database engine executing on a computer. A server has a name, and when a program wants to access the database controlled by a server, the program asks for a connection to that server by name. Multiple servers can be executing on a single machine, controlling different databases. The default name that MSSQL gives to a server is localhost. (The naming of servers is discussed under the configuration variable A_MSSQL_DEFAULT_CONNECTION.)

Table Ownership

Table names in MSSQL have the form database.owner.table_name. Within MSSQL, if you are the owner of a given table, you can refer to it as just table_name. If you are not the owner, you must refer to it with the owner of the table as a prefix. Different owners can thus have tables of the same name. However, this is not true when you use the Database Connectors for MSSQL interface.

The Database Connectors interface works a little differently: it automatically determines the owner name it will use to reference a table. It is therefore essential that there not be multiple tables with the same name in a single database, even though the tables have different owners. If there are such multiple tables, the interface will not necessarily find the correct one, and no diagnostic will be issued.

Note that table names include dots (.) as separators. Because of this, you must make sure there are no extensions on COBOL file names that will be converted to table names. For example, if you had a COBOL file named IDX1.DTA, the Connector would attempt to open a table "DTA" with owner "IDX1." You can avoid this problem either by renaming your COBOL file in your source program or by using a configuration file variable to map the file name to an allowable file name, such as:

IDX1.DTA  IDX1

In the above example, IDX1.DTA is the name in the ASSIGN clause of the file's SELECT statement.

If you map your file name to a new name, we recommend that you simply drop the extension to form the new name. Here's why. The Compiler uses the base file name — without the extension — to create the eXtended File Descriptor (XFD) file name IDX1.XFD. (XFDs are explained in detail in the chapter XFDs.) The run-time system needs to be able to locate this file. But if you've mapped the file name to something completely different (such as MYFILE), the run-time system will look for an XFD file named MYFILE.XFD. So you'd have to remember to change the name of IDX1.XFD to MYFILE.XFD in the XFD directory. Save yourself this extra step by simply dropping the extension when you map the name. (For an alternate method of removing file extensions, see the configuration variable 4GL_IGNORED_SUFFIX_LIST.)

Security

Security is implemented in the MSSQL RDBMS. A user is required to log in to the RDBMS before any file processing can occur. Database Connectors provides both a default and a user-configurable method for implementing this.

Generally, it is best for someone with database administrator (DBA) privileges to create and drop the tables, allowing others only the permissions to add, change, or delete information contained in them.

See the Microsoft SQL Server documentation for more details on DBA privileges.