MultiBase

Technical Notes of Version 3.0 Release 00

Incorporated ImprovementsTop

Version 3.0 of MultiBase adds new functionalities and power to the product. Especially significant improvements where introduced for the Windows version, which contains the report generator EasyReport (already available in UNIX since version 2.0.05), a new editor "Wedit" and the command "ctlinf" from now on.

Easy Report

This tool includes interactive documentation in each product, using the help options ("HELP") available in each of the programs. Basically EasyReport is composed of the following three elements:

Wedit

This new editor allows to manage larger files compared with the one included in previous versions. This increase affects the number of characters per line as well as the total number of lines. The current limits are approximately 20.000 characters per line and 8.000 lines per file.

Also, this new editor uses different colors to distinguish keywords (black), user variables and internal variables (red), literals (blue), comments (green), etc.

The command employed to run the the editor is wedit, which syntax is the following:

wedit [-v] [-m] file [-l search_sequence] [-x extension]

Where:

Note: The arguments ".-m", "-l" and "-x" can be used without difference before or after the name of the "file".

Command CTLINF

This command -which was already existent in version 2.0.05 for UNIX- is used to give information about programs and modules which are the elements of an application developped with MultiBase.

New Functionalities and Implemented SuggestionsTop

In the following the new functionalities and the implemented improvements of the product compared with the previous version are described for each operating system. This information has been structured into the following sections:

In case some of the suggestions or new functionalities are specific to a concrete environment (UNIX, Windows or Local Area Network), this is printed in brackets before the corresponding notes.

IMPORTANT: The SQL and the "gateways" of version 3.0 are not compatible with the CTL of previous versions of MultiBase and viceversa. Therefore, in client-server installations both machines (client and server) need to have version 3.0 of CTL and of CTSQL.

All of the developments made under previous versions of MultiBase are compatible with this new version.

CTL Language

1. [Local Area Network in Windows]. In Local Area Network environments under Windows locking was not performed in the machine where a row was locked in a FORM using the MODIFY statement (update operation) and where in another node of the network someone was fetching the same row of a CURSOR defined with "FOR UPDATE". The same also happened if a CURSOR with "FOR UPDATE" was used in both nodes.

2. [UNIX]. If a CURSOR "FOR UPDATE" was defined without the use of any index to build the derived table (sequential access), the next locking was caused by an access from different workstations: If the first workstation, which was executing the program, read a number of rows which are physically located before those to be read by the second workstation, that one was locked until the first one closed the CURSOR.

For example: Values to be updated by a CURSOR "FOR UPDATE":

Group Description
1 Value1
1 Value2
1 Value3
2 Value4
2 Value5
2 Value6

The first workstation has read the rows corresponding to group "1": if the second workstation wants to read those corresponding to group "2", it will be blocked until the first one closes its CURSOR after having read the rows "value1-value3".

3. In a maintenance program (FORM) of head-lines type with rows to be ordered ("ORDER BY" clause in the JOINS section) the program slowed down dramatically if there were many rows existent for the detail of the head, thus holding instead of simply displaying the first row matching the join condition. This problem was caused due to the change of the optimizer since versions 2.0.xx of MultiBase. To solve this, it is sufficient to add to the "ORDER BY" clause in the JOINS section the join columns of the two tables.

Example:

f1 = headtab.c1 = linestab.c1 required
f2 = headtab.c2 = linestab.c2 required

f3 = linestab.lin noentry noupdate

joins
    linestab lines of headtab order by c1,c2,lin
    composites <headtab.c1, headtab.c2> <linestab.c1, linestab.c2>
end joins

If the table "linestab" contains many rows for "headtab", utilize the JOINS section according to the example given. In case of not doing so, the program will work identically, but more slowly.

4. When reading a null value ("NULL") through a CURSOR, the variable of the clause "INTO" or "BY NAME" recognized blanks in the place of the null value.

5. [Windows]. The statement WINDOW with the clause "AS FILE" was not properly performing the "scroll" on the last page when it wasn't completely filled, thus displaying parts of the information contained on the previous page.

6. The statement CANCEL within a CONTROL section of a FORM was manipulating the value of a column which is a component of the primary key of the table being used to null-value ("NULL"). For example:

database stock

define
    form
    tables
        states
    end tables

    control
        before delete of states
            select "1" from customers
            where customers.state = $states.state
            if found = true then cancel
    end control
    end form
end define

main begin
    menu form
end main

If in the above example the user chooses to delete the current state and there are customers within this state the program was cancelling the delete operation, changing the value of "states.state" to null. If immediately afterwards the same operation would have been performed, the unintended delete was carried out, because the referential integrity between the tables "customers" and "states" was no longer existent.

7. The statement WHENEVER with the clause "INTERRUPT" was not executed when the "break" key of the operating system was pressed for the second time.

8. The condition "MATCHES" in an embedded statement of a CTL module didn't use the index defined for a column resulting in very slow access which was in contrast to CTSQL. The problem was due to the analysis of the "host" variable used as condition.

9. [UNIX]. When performing a query for a column of "char" type using "MATCHES" metacharacters through a FORM, the number or rows returned where not identical to the number of rows returned by CTSQL using a SELECT statement with the same conditions.

10. [Windows]. If the "START OUTPUT STREAM ..." statement (with the "APPEND" clause) was employed on an existent file for which the user executing the program was not having the right to read/write the file the program was hanging the whole system.

11. [Windows]. If the "START OUTPUT STREAM ..." statements was executed and the name of the file was included in a lengthy variable of "char" type (for instance 500 chars) a general protection error from Windows was coming up, when the clause "CLIPPED" was ommitted. This happened, because Windows tried to create a file with a 500 character long name. Currently an automatic "CLIPPED" is applied for this type of variables.

12. The internal variable "sqlrows" is assigned the number of rows found when it was read using a SELECT statement with the "INTO TEMP" clause as if it had been prepared (PREPARE and EXECUTE statements). In contrary, this variable is not assigned a value if this SELECT statement is found in a TSQL.

13. [Windows]. The first icon of a "Lotus" menu disappeared when the maintenance of a FORM table was executed consecutively a second time.

14. The statements BREAK and EXIT PROGRAM were not unallocating the reserved memory dependant on where they where placed.

15. It is necessary to indicate the version 3.0 in client-server installations in the line to be inserted into the file "/etc/inetd.conf" in the server machine, relevant to the database server being employed.

For example:

For CTSQL:

ctsql stream tcp nowait root $TRANSDIR/lib/ctsql ctsql system 3.0 0.0 NET

For Informix:

gwinformix stream tcp nowait root $TRANSDIR/lib/gwinformix gwinformix system 3.0 $TRANSDIR/etc/gwinformix.env NET

For Oracle:

gworacle stream tcp nowait root $TRANSDIR/lib/gworacle gworacle system 3.0 $TRANSDIR/etc/gworacle.env NET

16. When repetitionary executing the INPUT FRAME statement with "FOR UPDATE" clause and if for of the variables of the FRAME a LOOKUP attribute was included, the lookup was not removed from the screen.

17. [Documentation error]. The clause "LABEL" of the PROMPT FOR statement may also use an expression.

Database Server (CTSQL)

1. If you were using an internal function of CTSQL (in the "WHERE" clause) regarding the datatype "date" no rows where found, always and when the "date" column used as parameter was part of an index. For example, the following did not find a single row:

create table table1 (column1 char(11) not null,
    date1 date not null,
    description char(20) upshift)
primary key (column1, date1);

select * from table1
    where table1.column1 = "value"
    and year(date1) between 1990 and 1995

2. The following SELECT statements were not returning the correct derived table:

select * from states
    where state not between 1 and 4
    and state not between 5 and 7

select a,b from table1
    where a not in ("Y", "N") and b not in ("Y","N")

3. Creating a database with "COLLATING" clause determined that certain conditions (of the "WHERE" clause) were not returning the correct rows.

4. [HP-UX]. The reading of a table in which a condition was applied for a "DECIMAL" or "MONEY" column didn't return the correct derived table.

5. The negation of an "IS NOT NULL" condition of CTSQL didn't select the correct rows. For example:

select * from states
    where not (prefix is not null)

6. In order to correctly optimize use "ROWID", it must be situated at the left part of the condition. For example:

select * from states
    where rowid = 20

In contrary, if you are typing:

select * from states
    where 20 = rowid

optimization is not performed correctly.

7. The referential integrity between two tables did not control parts of the column (substring) of the primary key of the main table in updates (UPDATE statement). For example:

update states set col_primary[2,3] = "ab"
where ...

8. [Solaris]. When executing a SELECT statement which read from a temporary table ("INTO TEMP" clause) the communication with CTSQL was interrupted.

9. [Solaris]. When using the EDIT statement for a variable its content was not displayed.

10. [UNIX]. The reading of the "ROWID" from a table with "MATCHES" condition only returned the first row found as being valid. Nevertheless, if any of the columns belonging to the table were read, the derived table was returned correctly. This problem was detected, because the execution of a SELECT statement with "MATCHES" condition and a query from a FORM with the same condition behaved in a different way. For example:

select * from items
    where description matches "M*e*"

In the demonstration database included with your copy of MultiBase this SELECT statement returns several rows. If instead of the asterisk (*) "ROWID" is selected, only the first row matching the condition is returned. Also, if one executes the "items" maintenance FORM and enters the condition "M*e*" in the "description" field only one row is selected.

11. When launching a DELETE in the case of an existent duplicated index with many duplicated rows the performance is very slow. To optimize this type of operation one has to change the duplicated index to an unique one composed of the column or columns of the initial duplicated index plus the column or columns which are the components of the primary key.

Program linker (CTLINK command)

Creating programs in UNIX from CTL sources generated with MultiBase for Windows which were including functions specific to this environment did not produce any errror, assuming that those functions were also internal functions of the named operating systems.

Debugger

In the moment of querying the value of a variable from the command line "?variable" the complete value was not shown if it was more than 80 characters wide. Currently, however, this value is displayed in as many lines as necessary.

Development Environment (TRANS)

When activating the option "Database" of the "Environment" menu the windows informing about the current database and about the help, information and select keys are eleminated.

As far as client-server installations are concerned, the name of the user to be connected to in the server machine ("DBUSER") and also the relevant password ("DBPASSWD") will be demanded when the Development Environment is run.

Lastly, if for a module the option "debugging" is set, all of the compilations are made with debugger until the contrary will be specified.

Transactions

The statement ROLLFORWARD produced the error "ROLLFORWARD database failed" when intending to recover the execution provoked by a DELETE statement of the SQL.

All of the "cursors" defined with the "FOR UPDATE" clause which are affected by a transaction ("BEGIN WORK") will always be "NOWAIT", so the locking of a row can be controlled through the internal variable "locked".

EasyReport (UNIX)

If someone was intending to generate a report which was using execution parameters, a non-recoverable error was produced when opening.

Documentation Generator

The automatic manual generation of MultiBase (tdocu) produced an unrecoverable error in the Windows version when generating a user's manual for a program in which the LOOKUP attribute was defined for a FRAME variable.

GatewaysTop

Beginning with this version a new internal function was implemented - named "getsqlerror()" - which returns the error number ("errno") returned by the database server being used.

Gateway Oracle

Configuration variables: The following variable was added:

Environment variables: The variables ORACLE_SID, ORACLE_HOME and ORACLE_UID are working identical to client-server environments. In this way it is possible to use a general value which can be assigned through the configuration file "gworacle.env" detailed for each client.

Foreign Keys: For the name of a "foreign key" the prefix FK doesn't has to be applied. (If you intended to delete a "foreign key" in older versions you had to delete them directly with the Oracle server and also in MultiBase in the maintenance mode).

INSERT statement: All of the limitations in functionality that existed were removed and also those that had to be considered when the datatype SERIAL was used.

IMPORTANT: The internal table "mbserial", used to simulate this datatype, has changed its structure. The following CTL program drops the table and creates a new scheme and updates the data that it keeps. It has to be executed as many times as MultiBase databases are existent.

define
    parameter[1] dbname char(20)
    parameter[2] user_pass char(40)
end define

main begin
    call putenv("ORACLE_UID", user_pass, 2)
    database $dbname
    tsql "execsql create table mbtserial
        (tabname char(30),lastserial integer)"
    tsql "execsql insert into mbtserial select tabname,
        lastserial " && "from mbserial, mbtables
        where mbserial.tabid = mbtables.tabid"
    tsql "execsql drop table mbserial"
    tsql "execsql rename mbtserial to mbserial"
end main

PRACTICAL CASES:

The field "dirpath" in "mbtables" contains the user where the table is located. If the field contains a null value inherited from previous versions, it must be filled with the corresponding value to guarantee correct functionality.

Changing of MultiBase database: The desired effect may be reached with the following instruction:

DATABASE name_of_the_database

but before a "putenv" of the variable "ORACLE_UID" with the corresponding value must be issued:

call putenv("ORACLE_UID", user_password, 2)

Temporary tables: If in a long session you have changed successively databases, it can be that in the moment of finishing the process temporary tables in different Oracle users are existent. The "gateway" ordinary drops them in order to simulate this temporal character, but there are two suppositions in the moment you connect to the users: that you have not changed ORACLE_SID and that the PASSWORD of this user coinsists with his name. In any other case the tables are left without being dropped.

Shared Tables: To share a table between two databases (two Oracle users) a possible way to realize this in the MultiBase server is given.

The user A, creator of the table to be shared, gives the permissions he considers to be appropriate to the user B for the same table and for the table "mbserial" if there is a field existent which is of SERIAL datatype.

The user B creates a synonym:

CREATE SYNONYM name_of_the_table FOR user_A.name_of_the_table

to be able to use the same name of the table. Next, create the table in maintenance mode in order your catalogue contains it and modify the field "dirpath" with UPDATE in the row which appears in "mbtables" for this table. Change the value "userB" to "userA" (because in reality it is the userA where the table resides).

If the structure of the table is changed in any moment, the previous procedures must be repeated (ALTER in maintenance mode and change of "dirpath").

MultiBase catalogue: It is possible to break the special relation existent between MultiBase databases and Oracle users.

If you want that one MultiBase database corresponds to various Oracle users maintaining one single catalogue, the "mb*" tables must be shared in the way described before. One of the users is the creator of the real catalogue ("grantor" of permissions) while the others share the tables (creating synonyms).

The table "mbserial" doesn't appear in the catalogue, because of its internal nature, but all of them have to be manipulated, therefore permissions must be granted:

EXECSQL GRANT ALL PRIVILEGES ON mbserial TO PUBLIC

Every user, except the creator of the catalogues, has to have the following synonym established:

EXECSQL CREATE SYNONYM mbserial FOR create_user.mbserial

This procedure can also be applied to the tables of the programming environment ("ep*") if you should wish to do this.

Gateway Informix

Datatype: The conversions:

Informix MultiBase
VARCHAR(size) CHAR(size)
FLOAT DECIMAL(p,s)
SMALLFLOAT DECIMAL(p,s)

were not specified in the manual, but were performed automatically.

Environment variables: The variable INFORMIXDIR is working identical to client-server environments. In this way it is possible to use a general value which can be assigned through the configuration file "gwinformix.env" detailed for each client.

CREATE statement; DEFAULT attribute: This attribute doesn't exist in version 4 of the Informix server. From now on the "gateway" simulates this in INSERT operations for VALUES which are inserted using constants or host variables (this is extended also for the FORM).

This simulation, however, doesn't work in the case that the values are given by a SELECT statement, because the result can't be controlled (INSERT ... SELECT ...).

Foreign Keys: For the name of a "foreign key" the prefix FK doesn't has to be applied. (If you intended to delete a "foreign key" in older versions you had to delete them directly with the Informix server and also in MultiBase in the maintenance mode).

START DATABASE statement: This statement is only used to write the "syslog" registry into the MultiBase catalogue, informing that the database is transactional. It does not send any command to the Informix server.

Temporary tables: If you are using this server temporary tables can not be shared among databases.