Print
page
The following DLL libraries are available through TransTOOLs in order to add Windows specific features to MultiBase.
TTNewGraph(WORD, WORD) return WORD
TTSet(WORD, LPSTR, LPSTR) return WORD
TTDraw(WORD, LPSTR, LPSTR) return WORD
TTDeleteGraph(WORD) return WORD
TTQuitGraph() return WORD
InitMBCheckDLL(WORD, WORD, WORD,
WORD, WORD)
SetMBCheckMessage(LPSTR) return WORD
CreateMBCheckGroup(WORD, WORD, WORD, WORD, WORD, LPSTR) return WORD
CreateMBCheckItem(WORD, WORD, WORD, WORD, WORD, LPSTR) return WORD
GetMBCheckSelection(WORD, WORD) return WORD
SetMBCheckSelection(WORD, WORD, WORD) return WORD
DeleteMBCheckGroup(WORD) return WORD
DeleteMBCheckItem(WORD, WORD) return WORD
DisableMBCheckItem(WORD, WORD) return WORD
EnableMBCheckItem(WORD, WORD) return WORD
DoKey (WORD, WORD, LPSTR)
ShowBmp (WORD, LPSTR, WORD, WORD, WORD, WORD) return WORD
CreateDibWin(WORD , LPSTR, WORD , WORD , WORD , WORD ) return WORD
DestroyDibWin(WORD) return WORD
DrawDibWin(WORD, LPSTR, LPSTR) return WORD
MoveDibWin(WORD, WORD, WORD, WORD, WORD) return WORD
LoadDibWin(WORD, LPSTR) return WORD
ShowDibWin(WORD, LPSTR)
ForceShow(WORD)
InitMBRadioDLL(WORD, WORD, WORD,
WORD, WORD)
SetMBRadioMessage(LPSTR) return WORD
CreateMBRadioGroup(WORD, WORD, WORD, WORD, WORD, LPSTR) return WORD
CreateMBRadioItem(WORD, WORD, WORD, WORD, LPSTR) return WORD
GetMBRadioSelection(WORD) return WORD
SetMBRadioSelection(WORD, WORD) return WORD
DeleteMBRadioGroup(WORD) return WORD
DisableMBRadioItem(WORD, WORD) return WORD
EnableMBRadioItem(WORD, WORD) return WORD
InitMBSliderDLL(WORD, WORD, WORD,
WORD, WORD) return WORD
SetMBSliderMessage(WORD, LPSTR) return WORD
CreateMBSlider(WORD, WORD, WORD, WORD, WORD) return WORD
SetMBScrollRange(WORD, WORD, WORD) return WORD
SetMBScrollPos(WORD, WORD) return WORD
DeleteMBSlider(WORD) return WORD
GetMBScrollPos(WORD) return WORD
GetMBNewScrollPos(WORD) return WORD
MovetxtWin(WORD, WORD, WORD, WORD,
WORD) return WORD
ShowTxt (WORD, LPSTR, WORD, WORD, WORD, WORD) return WORD
CreateTxtWin(WORD , LPSTR, WORD , WORD , WORD , WORD ) return WORD
WordTxtWin(WORD , WORD , LPSTR) return WORD
DestroyTxtWin(WORD) return WORD
DrawTxtWin(WORD, LPSTR, LPSTR) return WORD
LoadTxtWin(WORD, LPSTR) return WORD
ShowTxtWin(WORD, LPSTR)
[Windows only]: The windows version of MultiBase contains a new DLL named «UTIL.DLL». This library contains five functions. Four of them faciliate the communication with serial ports of the machine and the other one counts milliseconds. The names of the functions are: «OpenPort», «ReadPort», «WritePort», «ClosePort» and «Chrono».
To activate them the following must be added to the DLL section in MB.INI :
UTIL.DLL
OpenPort(LPSTR, LPSTR) return WORD
WritePort(WORD, LPSTR, WORD) return WORD
ReadPort(WORD, WORD) return LPSTR
ClosePort(WORD) return WORD
Chrono(WORD) return WORD
In order to use the communication functions for serial lines the syntax explained in the following paragraphs must be observed:
a) Function «OpenPort»: Opens a communication channel and returns the number of the open channel. The syntax is:
openport("Port", ["Parameters"])
Where:
Example:
let channel = dllfun("OpenPort", "COM1", "9600,n,8,1")
b) Function «WritePort»: Write the number of characters specified to the indicated channel. The following syntax has to be applied:
WritePort(Channel, String, Length)
Where:
Return:
Example:
call dllfun("WritePort", channel, "String", 6)
c) Function «ReadPort»: Reads a number of characters from a channel. Syntax:
ReadPort(Channel, Length)
Where:
Example:
let string = dllfun("ReadPort", channel, 6)
d) Function «ClosePort»: Closes the indicated channel.
closeport(Channel)
Where:
Channel: Value returned by «OpenPort».
Return:
Example:
call dllfun("ClosePort", channel)
e) Function «Chrono»: Returns the number of milliseconds since last «Reset». The syntax is:
Chrono(reset, function)
Where:
Examples:
call dllfun("Chrono",
1, 0) {Start}
call dllfun("Chrono", 0, 0) {Request}
call dllfun("Chrono", 0, 1) {Pause}
call dllfun("Chrono", 0, 2) {Continue}
Determines how menus behave. The possible values are:
This variable is used to indicate the user password of a UNIX server in a client/server installation. If this variable is assigned the value with putenv() in a program module, this has to be done before opening the database. If the UNIX version of MultiBase is < 2.0.05 and no password has been defined for the database user use the keyword NOPASSWD (DBPASSWD=NOPASSWD).
Changes the priority of a user program.
DBPRIOR=value
export DBPRIOR
It is possible to specify a value between 5 and 200. A value of 100 stands for normal priority, while 5 stands for the lowest (slowest) and 200 for the highest (fastest) priority. Values under 5 or over 200 will be adjusted to minimum or maximum. If only one MultiBase application is executed no change in performance can be observed.
The format in which negative values are stored was changed with version 2.0 of MultiBase in UNIX and with version 2.0 Release 05 in MS-DOS and Windows. If negative decimal values are stored in previous versions the environment variable DECNEG must have the value "1" assigned. In this mode tables which were created with older versions of MultiBase can still be used. The environment variable MBCOMPAT may also serve to solve differences with previous versions.
In the case that previous version were used to store negative decimal values they will appear as "-0,00" in a SELECT.
In order to change the tables to the new format you have to set «DECNEG=1» and perform an UNLOAD of the relevant tables. When reloading the tables take care that "DECNEG" is unset or contains a value different to "1".
With the help of this variable you will achieve a behaviour as in versions prior to 2.0. This equals setting of both MBCTRMODE and DECNEG. If this variable is set to "1" it is not necessary to set MBCTRMODE and DECNEG.
The clause BEFORE of a CONTROL section in a FRAME will be executed after execution of the last clause in the EDITING section. This equals to the behaviour of a FORM and was implemented with version 2.0. Another change is that the system variable "CANCELLED" will be set when a CANCEL statement was issued within a CONTROL section. To achieve a behaviour as before version 2.0 the variable has to contain the value "1".
This environment variable allows to define the maximum number of open files at the same time. The default value is 50 for UNIX and 16 for MS-DOS. Example: MBISFILES=70 (up to 70 file can be kept open). It is not possible, however, to overwrite the maximum value of 100.
[UNIX only]: The environment variable MBISFILES may contain two parameters separated by comma. The first parameter indicates the number of open files within the machine, while the second indicates the number of files which may be opened at the same time. This is achieved by closing files which are not used by a cursor. The second parameter may only be applied for databases without transaction handling.
This variable is similar to the variable DBLANG (see Page A/98 of the MultiBase Reference Manual), but without the need to specify the ‘msg’ directory ($TRANSDIR/msg will be anticipated). This variable has priority over DBLANG (which means that a value assigned to DBLANG will not be considered if MBLANG was also specified).
The environment variable MBTERMCAP indicates then name of the file which contains the TERMCAP information of the terminal to be used. If this variable is not set MultiBase will use the termcap file within $TRANSDIR/etc.
This environment variable allows the activation of the XON/XOFF procotol on a serial terminal line. To achieve this assign 1 (ONXON=1) to the variable. If it was not set, DTR protocol will be used.
It is possible to obtain information regarding access strategies of the SQL optimizer for local databases. This can be achieved by setting the environment variable OUTOPT=filename. The protocol information is found in the indicated file. Different output formats can be chosen with the variables OUTOPT2 and OUTOPT3.
Sentence:
SQL statement
Optimization: num Tables <-
Number of tables used.
Table tabname
num Ranges <- Number of conditions.
Range n: key
[start/length] ((s1/11)(s2/12...)
Indication
of the used indexes or sub-indexes.
()
means access with ROWID.
This variable is an extension to OUTOPT and includes further information regarding the criterions used in the selection order of several tables.
Shows the selection order of tables and the indexes, which were used for access. The information produced is the following:
Sentence:
select * from customers, states
where customers.state = states.state
and customers.customer = 50
;
Table Order:
Index customers.primary = (1) Cols: 1 Used 1 EQ 1 <-
Index customers.i2_customers = (1) Cols: 1 Used 0 EQ 0
--- Index Selected : primary ---
Index states.primary = (1) Cols:
1 Used 0 EQ 0
--- Index Selected : None ---
Compare Tables:
customers (Keyparts 1 Unique, Rows 97, (ATTR=VAL) <-
states (Keyparts 0 Unique, Rows 57)
--- Table Selected : customers ---
Index states.primary = (1) Cols:
1 Used 1 EQ 1 <-
--- Index Selected = primary ---
--- Table Selected : states ---
Optimization Results: 2 Tables
Table [customers] 1 Ranges
Range[1] : key [start / length]
((0 / 4))
Table [states] 1 Ranges
Range[1] : key [start / length]
((0 / 2))
Controls the pauses per page for STREAMS TO TERMINAL.
This environment variable defines the size of the memory region (in bytes) used for sorts in CTSQL (ORDER BY). The default value is 32.000 bytes.
Beginning with version 2.0 the communication protocol between CTL and CTSQL has been modified resulting in its optimization. This change effects fundamentally function calls between modules. In previous versions this call between two modules produced a communication with the CTSQL to indicate the new current module. In the cases in which the module being called did not receive any CTSQL handling this communication was innecessary. Possible values are:
SQLPROTO=1: Version 1.0 compatible
SQLPROTO=2: Version 2.0 compatible.
The TSQL statement allows the use of the NO LABEL clause as indicated in the reference manual.
The following actions were eleminated (Reference Manual page A/123):
fhead
flines
fline-number
fline-next-field
fline-previous-field
The following actions were replaced:
fforwards by fleft-page
fbackwards by fright-page
Each Query sequence received by CTSQL once syntactically analysed passes a phase of optimization in order to achieve fast access to the concerned data. Optimization strategies are based on:
Existing indexes: If there is a possibility to compare column values by an unique index or consecutive conditions (AND/OR) for duplicate indexes the QUERY sentence can be optimized. This type of access offers improved performance as the number of records referred to is supposingly less than that of the whole table. If the number is similar it may make more sense to prefer a sequential access.
Example:
where (bill > 10 {use index}
or 1 = 0) {Condition false}
Number of records: The number of rows in a table is particularly used in the execution of joins. This value is maintained by the SQL statement UPDATE STATISTICS and stored in the ‘nrows’ column of the ‘systables’ system catalogue.
ROWID: ROWID is the number of a row in a table providing most rapid access to it and thus should be used prior.
JOIN operation: If columns involved in a JOIN are indexed the execution is carried out as mentioned before. Due to the possibility of selection between various tables access is executed respecting the following priority orders:
If more than one table permits indicated access they are handled in the order of appearance established in the FROM clause, which means to indicate first those you presume to affect less rows.
Summary of optimizer priorities:
Performance of queries is also improved if:
‘Between’ optimization: If a condition contains from and to limits («between») CTSQL optimizes them like: «expression = value». For example:
| ORIGINAL CONDITION | OPTIMIZED CONDITION | |
| column between 1 AND 1 | is equal to | column = 1 |
In the following case parentheses are needed in order that the condition type can be discovered and the condition shown on the right side can be used.
| (column >= 1 AND column <= 1) | -> | column = 1 |
| (column1 >= 1 AND column1 <= 1) AND (column >= 2 AND column2 <= 2 |
-> | column1 = 1 AND column2 = 2 |
Beginning with version 2.0 all versions of MultiBase (UNIX, MS-DOS, Windows) contain new source code generators. These new generators recognize dictionary information - especially those about the elements for referential integrity (primary keys and foreign keys). If you intend to generate application prototypes it is useful to be aware of the integrity between all your tables.
FORM generator
The new FORM generator offers the possibility to choose the table
columns to be selected for maintenance as well as their order of appearance
and to modify the "labels". Moving on the selection screen is
done with the arrow keys and ENTER. By default all table columns are selected.
Furthermore a basic menu for maintenance of the selected table is generated
- including options ‘add’, ‘remove’, ‘update’ and ‘query’.
At last, not only a module including the object FORM with its possibilities
is generated but also a library named MBLIB containing standard functions
used by all maintenance modules. This library is built by the LIBRARY
generator to be dealt with later on.
LINES generator
The options for generation of Data Entry and Lines programs are
at the same pulldown level within module's maintenance. Data access by
the generator is performed in the same way as in former versions with the
additional feature to generate relations between joined columns (not only
between indexes). In this type of programs controlling menus for all related
tables are automatically generated.
LIBRARY generator
Using the information corresponding to referential integrity the
generated library automatically contains all the functions needed for the
control of data integrity faults possibly occuring during table maintenance,
providing integrity for respective rows of the referenced table (primary
key). The functions within this module are generated for each primary key
of the database.
The control of integrity faults is performed in the EDITING section of
the main module after each field with a foreign key.
Furthermore, other standard functions are generated:
OTHERS generator
The output created by this generator is a module writing a listing
to the screen or the printer. In case of choosing the printer for output
the program supplies dynamic page formatting.
The program simulates a ‘query by form’ with the FRAME object and the Query operation. The selection of the listing elements is done the same way as in the selection for Data Entry programs.
The new environment supports the usage of key macros for fast movement among pulldown options. By default there are three movements defined:
To change these movements open the ‘fastmove’ file of your MultiBase directory ($TRANSDIR/etc) which is describe below:
Fastmove: This is an ASCII file as created with the UNLOAD command containing three entries that can be changed by the administrator in order to create new fast movements.
Example:
0|fquit freturn d m e fuser2|fuser3|
0|fquit freturn b t d fuser2|fuser4|
0|fquit freturn s t|fuser5|
1|fquit fquit freturn d m e fuser2|fuser3|
1|fquit fquit freturn b t d fuser2|fuser4|
1|fquit fquit freturn s t|fuser5|
2|fquit fquit fquit freturn d m e fuser2|fuser3|
2|fquit fquit fquit freturn b t d fuser2|fuser4|
2|fquit fquit fquit freturn s t|fuser5|
Within the new environment the selection of objects (tables, modules, program etc.) can be realized by specifying the complete name or part of it using the metacharacters used in the MATCHES clause of SQL. In case the name field is left blank a selection window containing all of the respective objects will be displayed.
Since version 2.0 all open cursors are closed when a database change is performed. This means that all cursors must be reopened and rePREPARED. Also the database was not closed in older versions when a program was ended.
This system variable contains the number of processed rows after one of the following SQL statements: INSERT, DELETE, UPDATE, INSERT ... SELECT or LOAD FROM ... INSERT INTO.
Two columns of a SELECT statement can be concatenated with "||" (2 pipes). For instance:
SELECT first_name || last_name FROM addresses
The result created equals to the below:
(expression)
David Bell
Sandy Oliver
...
...
The use of an 8 bit character in Query in FORM or within a SQL statement will produce the error «Illegal character found in the statement...». In order to solve this problem, define this character in the «UPPER/LOWER» section of the TMAPPING file, which you can find in the subdirectory "etc" of MultiBase. For example:
SELECT * from states
WHERE description > "$"
In order to execute this statement without error the following line has to be added to TMAPPING for the concrete terminal type:
:AT386
.UPPER/LOWER
\246 \246 $
This example is used for a terminal which uses IBM «GCS#2» character set.
In order to update lines using a cursor the SQL UPDATE must contain a «WHERE CURRENT OF cursor» clause. The cursor must be declared as «FOR UPDATE». This clause, however, cannot be used with in SELECT with «ORDER BY». If you need to perform the updateing with such a SELECT statement you must use the corresponding ROWID and perfom the update using this ROWID. Example:
DATABASE stock
DEFINE
VARIABLE number INTEGER
VARIABLE retail_price LIKE items.pr_vent
VARIABLE item LIKE items.item
END DEFINE
MAIN BEGIN
DECLARE CURSOR actualize FOR SELECT rowid, item, retail_price
INTO number, item, retail_price
FROM items
ORDER BY item
FOREACH actualize BEGIN
UPDATE items SET retail_price = $retail_price * 1,05
WHERE rowid = $number
END
END MAIN
The execution of such a program is faster than specifying a condition, because direct access is possible through ROWID.
When using a head-lines relation (JOINS section) in a FORM the statements HEAD and LINES should be used instead of NEXT TABLE, because scrolling problems may occur otherwise.
The output of a variable with «PUT STREAM» containing a null value adds blanks up to the length of the variable. To solve this problem use the concatenation symbol «&» which eleminates these characters. Example:
PUT STREAM standard "" & a
In Client/Server installations it is not possible to check tables of the database from the client machine (MS-DOS, Windows or UNIX). The error which will be shown is «Impossible to open the file "c:\...dbs\systable". The table checks must be performed directly at the server.
The loading of large amounts of data with the LOAD statement produced locking errors in databases with transactions. This was due, because of the fact that the MS-DOS SHARE command or the relevant UNIX kernel parameter reached their limit. To solve this problem it is recommendable to lock the table (LOCK TABLE) before executing LOAD.