MultiBase

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:

• “cdsedit.exe”: Equivalent to mbeasy of EasyReport in the UNIX version. This command generates a CDS (Conceptional Data Scheme) from a database.
• “confedit.exe”: This command allows to define all of the configuration parameters of a CDS, as well as the different format types which could be used by the user to run his/her reports. This is equal to the “CONFIGURATION” section of the CDS.
• “easyrep.exe”: Equivalent to the command trw of EasyReport in the UNIX version, this command is used to create different types of reports dependant on the information contained in the CDS which was previously generated with the command cdsedit.exe.

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:

• -v: Optional argument which indicates the version of the command and its “upgrade” state.
• -m: Optional argument to execute the editor in maximized mode.
• file: Parameter to indicate the name of the file which shall be created or edited.
• -l search_sequence: Optional argument which permits to activate a text at a certain point determined by a sequence of characters (“search_sequence”) which is given by the user. The way this is done is that the cursor appears at the position of the first character of the first discovered sequence. This option only makes sense to access already existent files and is especially useful for large documents.
• -x extension: This option permits to indicate the extension to be applied to the files generated.

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.

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:

• CTL Language.
• Database Server (CTSQL).
• Program linker (CTLINK command).
• Debugger.
• Development Environment (TRANS).
• Transactions.
• EasyReport (UNIX).
• Documentation generator.
• “Gateways”.

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.

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 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.

Gateways

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:

• ORACLE_PROC: Indicates to the “gateway” if the Oracle server allows to create SQL procedures. By default it is ON.

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.

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: