All of the developments made under previous versions of MultiBase are compatible with this new version.
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”:
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.
f1 = headtab.c1 = linestab.c1 required
f2 = headtab.c2 = linestab.c2 required
f3 = linestab.lin noentry noupdate
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:
before delete of states
select "1" from customers
where customers.state = $states.state
if found = true then cancel
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.
ctsql stream tcp nowait root $TRANSDIR/lib/ctsql ctsql system 3.0 0.0 NET
gwinformix stream tcp nowait root $TRANSDIR/lib/gwinformix gwinformix system 3.0 $TRANSDIR/etc/gwinformix.env NET
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"
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.
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.
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”.
If someone was intending to generate a report which was using execution parameters, a non-recoverable error was produced when opening.
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.
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.
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.