16 5 The RUNSQL Utility

16.5 The RUNSQL Utility

16.5.1 Configuration Notes - Creating Tables and Indexes

All Visual LANSA systems are shipped with a utility named RUNSQL.

RUNSQL can be used to automatically create the definition of a table into any supported DBMS system.

RUNSQL, combined with a .CTD (Common Table Definition File) file created by Visual LANSA during table compilation, form the essential ingredients that you need to move table definitions (not data) between different supported DBMS systems.

To understand how RUNSQL works consider this diagram:

If you imagine that you are attempting to transfer the definition of a table named PSLMST (that you have previously defined and compiled in your development environment) into another DBMS, then the key things shown in this diagram are:

• When the RUNSQL utility is invoked it reads in the file named PSLMST.CTD. This is the "Common Table Definition" (CTD) of table PSLMST that is created by Visual LANSA whenever you compile a table in your development environment. It defines table PSLMST and its associated views and indices in a common cross platform / cross DBMS format (full details of the format of .CTD files can be found in another section of this guide).

• RUNSQL also reads in a standard Visual LANSA file named X_DBMENV.DAT (Database Environment Definitions) that defines the unique characteristics of the DBMS that it is about to work with. 

• By using PSLMST.CTD and X_DBMENV.DAT the RUNSQL utility can assemble the unique "create" commands appropriate for the selected DBMS.

• Once the "create" commands are assembled the DBMS is invoked (via ODBC in Windows environments) and it is asked to create the necessary table, view, indices, etc.

RUNSQL is a simple program. It has the following positional and non-positional parameters:

1 The (qualified) name of the .ctd (Common Table Definition) file that contains the definition of the table to be created. Common Table Definition files are created whenever you create a table in your Windows development environment. The.ctd files can be found in the X_LANSA\X_ppp\SOURCE directory (where "ppp" is the partition identifier). 2 The name of the database or data source that the table is to be created into. Typically this parameter is passed as LX_LANSA. 3 Commitment Option. Must be Y or N and indicates whether a commit operation is to be issued after the table has been successfully created. You should always set this parameter to Y. 4 Reporting Option. Must be Y, N or F to indicate the level of reporting that RUNSQL should use. Y = Report on all messages and warnings. N = Do not report any messages or warnings. F = Report on fatal messages only. 5 The type of database. This value is used to locate the database characteristics in the specified "X_DBMENV.DAT" file. Some of the standard shipped database types are: -  SQLANYWHERE (Sybase Adaptive Server Anywhere and Sybase SQL/Anywhere) -  MSSQL (Microsoft SQL/Server) 6 The user profile / password to be used when attempting to connect to the specified database or data source. For example SA/TEST specifies that user profile SA with password TEST be used when connecting to the database or data source. 7 Specifies the directory in which the "X_DBMENV.DAT" file can be found. 8 Specifies the collection. Default is specified in the .ctd file 9 CTD Connection data option. Must be Y or N and indicates whether to use the connection information contained in the .ctd file. Only PC Other Files will have connection data in the .ctd file 10 Prompt User ID/ Password option. Must be Y or N and indicates whether to use the User ID and Password in the .ctd file (N) or to prompt for a new pair of values (Y). This is ignored unless CTD connection data is being used.

 

Non-Positional Parameters

OLDCTD= Old .ctd file name. This is the .ctd file that was last used to create/change the table. The new and the old CTD are compared and any changes or new columns are added to the table without deleting the existing data.

 

Note that non-positional parameters can be placed anywhere on the command line separated by spaces from the other arguments.

For example, this command executed from the x_Lansa\source directory compares myfile.ctd to myfile_old.ctd and makes the changes to the table. Note that it also uses the x_dbmenv.dat file from the parent directory -  – which in this case is the x_lansa directory: 
..\execute\runsql myfile.ctd OLDCTD=myfile_old.ctd LX_LANSA Y Y SQLANYWHERE DBA/SQL