17 5 The RUNSQL Utility

17.5 The RUNSQL Utility

17.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.
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). A Visual LANSA table definition: when the Table Schema is defined as using the partition's Default Table Schema then the CTD is located in: ..\x_win95\x_ppp\<Default Table Schema>\source when the Schema is defined as using the partition's Module Library then the CTD is located in: ..\x_win95\x_ppp\<Module Library>\source when the Schema is uniquely defined such that is not using the Default Table Schema and is not using the Module Library then the CTD is located in: ..\x_win95\x_ppp\source Non-Visual LANSA table definition (i.e Imported tables): the CTD is always located in: ..\x_win95\x_ppp\source
2	The name of the database or data source that the table is to be created into.
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 X_DBMENV.DAT file in ...\x_win95\x_lansa. Some of the standard shipped database types are: - SQLANYWHERE (Sybase Adaptive Server Anywhere and Sybase SQL/Anywhere) - MSSQLS (Microsoft SQL/Server)
6	The User ID/Password to be used when attempting to connect to the specified database or data source. This parameter is required even when using a Trusted Connection. In the case of a trusted connection, you could enter, for example: SA/TEST to specify that User ID SA with Password TEST is to be used when connecting to the database or data source. (The provided values are not used.)
7	Specifies the directory in which the X_DBMENV.DAT file can be found: ...\x_win95\x_lansa
8	Optional New Collection Name. Specify *DEFAULT to ignore this parameter.
9	Optional CTD Connection data option. Must be Y if the connection information contained in the .ctd file is to be used. Only Imported tables will have connection data in the .ctd file. N or blank if not used.
10	Optional Prompt User ID/ Password option. Is Y if the User ID and Password in the .ctd file is to be used. N or blank if not 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:
Runsql "…\myfile.ctd" LX_LANSA Y Y MSSQLS uid/pswd "…\x_win95\x_lansa"