Manage ESQL project and module

When using DBMaker ESQL/C preprocessor dmppcc to preprocess an ESQL/C source file, you need to provide the database name, user name, and password. The user name you use for preprocessing the ESQL/C source file must have connect and resource privileges for connecting to the database and preprocessing the file.

For example:

dmppcc -d test_db -u db_user_id -p db_user_passwd esql_source.ec

When writing an ESQL/C source file, you should add a CONNECT statement in the source file to enable the application program to connect to the database.

The CONNECT statement is as follows:

EXEC SQL CONNECT TO dbname dbuser dbusr_passwd;

Parameter

Syntax

Dbname

[identifier | :host_variable_identifier]

Dbuser

[identifier | :host_variable_identifier]

dbusr_passwd

[ | identifier | :host_variable_identifier]

dbname, dbuser and dbusr_passwd can either be an identifier or the char type's host variable if it has been declared in the ESQL/C applications declare section. dbusr_passwd can be ignored, if the user has no password.

It's not necessary to use the same database user name (dbuser) for preprocessing the ESQL/C program and execution time in the ESQL/C program. For example, for a banking database, there's the user "acc_dba" for developing the "bank" program.

In the shell command, type the following:

dmppcc -d bank -u acc_dba -p acc_dba connect.ec

The contents of the file connect.ec are as follows:

EXEC SQL INCLUDE DBENVCA; 
EXEC SQL INCLUDE SQLCA; 
connect() 
   { 
   /* use the clerk account to connect to the bank database */ 
   EXEC SQL CONNECT TO bank clerk pd_clerk; 
   if (SQLCODE) return SQLCODE; 
   ... 
   do_clerk_operation(); 
   .... 
   }

Manage Project and Module object

About Modules

When preprocessing an ESQL/C source file, DBMaker will create some execution plan and store all related information in the database in a component called a module. If you do not specify the module name, the default module name will be the ESQL/C source file name.

To prevent error caused by user try to access an older version of execution plan when other ESQL developer has been re-preprocess the same ESQL program again, dmppcc will delete the previous stored plan, and then create the new stored plan. If you often found error message "the executable may be out of date, please rebuilt it" when you execute the ESQL program, you should compile the related .c file and relink to executable. However, although this is an executable version error, you may still want to ignore it where there are many developer developing different ESQL modules in the same ESQL application. In this case, you can use "-n" option to ignore this error message when you are in developing phase. To optimize performance and reduce trouble in ESQL project management, you should remove the "-n" option after you finish coding your application.

About Projects

Any developer's application system may contain more than one ESQL/C module. If the developer tries to manage (grant/revoke or drop privilege) every module individually, the burden will be big. The purpose of a project is to let the developer group ESQL/C modules all together, and organize the application system more easily. After preprocessing an ESQL/C source file, dmppcc will store the project name in addition to the module name. If you do not specify the project name, the default project name will be the same as the module name.

When preprocessing any ESQL source file, if the project does not exist in the database, DBMaker will automatically create a project to store the module. If the project already exists, DBMaker will also automatically associate the new module to the project. Each module can only be associated with one project.

To look for the information about ESQL projects and modules, you can reference the database system table SYSPROJECT by an SQL statement:

select * from SYSPROJECT;

Column

Meaning

PROJECT_NAME

ESQL project name

PROJECT_OWNER

ESQL project owner

MODULE_NAME

ESQL module name

MODULE_OWNER

ESQL module owner

MODULE_SOURCE

Module's source file name

REF_CMD

Referenced command number (used internally by dmppcc)

The information of the ESQL execution plan is stored in the SYSCMDINFO system table. This system table not only stores the ESQL execution plan, but also other execution plans for stored commands and procedures. You may reference the manual of stored commands for more detailed information about each field.

Column

Meaning

MODULENAME

ESQL module name

CMDNAME

ESQL preprocessor generated command name

CMDOWNER

ESQL preprocessor generated command owner

STATEMENT

Original SQL statement

DATA

Execution plan data

DESCPARM

Description parameters

STATUS

Valid or invalid

NOTE: If you set option -cs or -n in dmppcc, DBMaker will not store execution plan, module, project or owner name of related table. To prevent error caused by different ESQL/C preprocessor user and the program execution user, it is recommended that you put owner name for every table in the SQL statement when you set -cs or -n option.

Dropping a Project

Since projects are used for maintaining the relationship of the ESQL modules, when the project is no longer useful, you can use the DROP PROJECT statement to drop all the related execution plans and information for the project.

The syntax for DROP PROJECT is:

DROP PROJECT project_name;

Only the project owner or database administrator can drop the project or grant/revoke execution privileges to other users.

Remove module from project
You can remove module from a project and all stored commands related to this module from the database, when a module is no longer useful. When a project only contain a module, and the module has been removed, the project will also be removed from the database.

Only the project owner or database administrator can remove the module from the project.

LOAD/UNLOAD PROJECT or MODULE

You can use LOAD or UNLOAD PROJECT or MODULE function in dmSQL to unload and load the related project or only specified module.

The related syntax is listed as follows: You can check the detailed UNLOAD/LOAD syntax in the "dmSQL User's Guide".

For UNLOAD:

UNLOAD PROJECT FROM [ower_pattern.]project_pattern TO script_name 
UNLOAD MODULE [ower_pattern.]module_pattern FROM PROJECT [owner_name.]project_name TO script_name.

example:

dmSQL> UNLOAD PROJECT FROM project1 TO project.scr; 
dmSQL> UNLOAD MODULE module1 FROM PROJECT project1 TO module.scr;

For LOAD:

LOAD PROJECT FROM script_name. 
LOAD MODULE FROM script_name.

example:

dmSQL> LOAD PROJECT FROM project.scr; 
dmSQL> LOAD MODULE FROM module.scr;

NOTE: The UNLOAD/LOAD function only can be used in dmSQL.

Granting or Revoking Privileges for Projects

You can grant/revoke execution privileges for a project to other application system users.

The GRANT/REVOKE SQL statement syntax is:

GRANT EXECUTE ON PROJECT project_name TO auth_user_list; 
REVOKE EXECUTE ON PROJECT project_name FROM auth_user_list;

When any user tries to execute a project for which they don't have authority to execute, it will return an error at run time. Because the project is for the developer to group or manage modules in the database, it's possible that an application system has many linked modules from different projects. In this case, the application user can only execute the part for which they have the execution privilege.

The authority information related to ESQL is stored in the SYSAUTHEXE system table. You may look into the table for checking authorized users.

Column

Meaning

OBJNAME

Project name

OWNER

Project owner

OBJTYPE

PROJECT or STORE COMMAND or STORE PROCEDURE

GRANTEE

Authorized user