This quick overview of some basic design concepts underlying the Microsoft SQL Server (MSSQL) Database Management System (RDBMS) helps to interface your COBOL program to it.
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 a database controlled by that server, the program asks for a connection to the server by name. Multiple servers, controlling different databases, can be executing on a single machine. The default name that MSSQL gives to a server is localhost; refer to the A_MSSQL_DEFAULT_CONNECTION configuration variable for further details on how to connect your program to one or more servers.
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; thus, different owners can 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 used to reference a table. It is therefore essential that there are not 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 does not necessarily find the correct one, and no diagnostic is issued.
Table names include dots (.) as separators. Because of this, you must make sure there are no extensions on COBOL file names that could be converted to table names. For example, for a COBOL file named IDX1.DTA, the Connector attempts 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, drop the extension to form the new name. 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 XFDs. The run time system needs to locate this file. But if you've mapped the file name to something completely different (such as MYFILE), the run time looks for an XFD file named MYFILE.XFD. So you would have to remember to change the name of IDX1.XFD to MYFILE.XFD in the XFD directory. Dropping the extension when you map the name prevents having to perform this extra step.
Refer to 4GL_IGNORED_SUFFIX_LIST configuration variable for an alternate method of removing file extensions.
When creating a table, the SQL Server interface also creates an index, and grants loose permissions to everyone via the A-MSSQL-GRANT-PERMISSIONS and A-MSSQL-GRANT-USERNAME configuration variables with their default values. If database permissions are not set to allow these loose permissions, the GRANT statement fails, causing OPEN OUTPUT to fail. See A-MSSQL-GRANT-PERMISSIONS and A-MSSQL-GRANT-USERNAME for details.
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; refer to Setting up the User Environment for further details on how to supply login details.
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.