SUB...ENDSUB einsetzen [tdbengine.org]

Deutsch

English

Snippets

Home Overview Search Impressum Contact Members

How to use SUB..ENDSUB loops optimal?

Basically

On principle there are two alternatives to select and to analyse/return files on tdbengine.
The first one leads over entries in the mark list to a table and following work with all marked datasets using a WHILE or REPEAT construction combined with ReadRec() - NextRec() orders.
The other one that I want to picture here uses the (hardly documentated) SUB..ENDSUB loop.

SUB .. ENDSUB

Let us not even try to describe SUB..ENDSUB with words and look at anexample instead:

PROCEDURE Main

Var dbADDRESSES : INTEGER = OpenDB("..,/database/addresses.dat","",0, 0) //Open a table in reading mode

CGIWRiteLn("content-type: text/html")
CGIWRiteLn("")
CGIWRiteLn("Addresses<hr><ul>")

Access(dbADDRESSES, "addresses.id")
SUB _DBName(dbADSRESSES)
CGIWriteLn("<li>"+ GetField(dbADDRESSES, "lastname")+", "+ GetField(dbADDRESSES, "firstname") +"</li>")
ENDSUB

CGIWRiteLn("</ul>")

CloseDB(dbADDRESSES)
ENDPROC

The result of this little program is (assumed there is a table addresses.dat with fields "lastname" and "firstname") a simple HTML list of all names enlisted in the table.

What happens actually in this example?

First the table handle to addresses.dat returned by OpenDB() will be assigned to the new declared variable dbADRESSEN. So we can access the table using this handle.
Following a normal HTTP and HTML header will be returned. Now it becomes exciting.

Using the Access()command we say tdbengine that we want to display datasets sorted by the ID index. Sure enough it assumes that an analogical index for the table exists. We assume that it's so and the body of this index is "lastname:10,firstname:5". So we want to design the data output as in a phone book.

SUB _DBName(dbADDRESSES)

Here a SUB loop will be started. This call needs to be explained a bit and in a short while it will be explained.

SUB starts a SUB..ENDSUB block and ENDSUB exits it. That means the one is never used without the other. So far so clear.
With SUBa loop about the files are current accessable starts in the order that the access gives it. In our example ALL entries will be displayed sorted for ID index (afferent).

The Underline before the DBName() command causes the dynamic analysis of any expression at runtime. Here it means following:
At the compile time tdbengine doesn't know anything about the table addresses.dat but it knows the reference thereon: dbADDRESSES.
The SUB..ENDSUB loop is a primary rock from the DOS-TDB times. There it was used following:

SUB ADDRESSES, $ADDRESSES.Name like "Meier"
Print $ADDRESSES.Name + ", "+ $ADDRESSES.firstname
ENDSUB

At that time it was smooth because differently from today all tables of a database were opened while the whole runtime and could be reached using the global variables (here $ADDRESSES).
SUB..ENDSUB works today like aeons of years ago and so it needs a specific care but it is a real powerful tool.

Finally the line

SUB _DBName(dbADDRESSES)

means to tdbengine as much as

SUB ADDRESSES

Only while compiling you'll get an error hail if you write the latter - ADDRESSES describes at that moment an "unknown designator".

Back to the SUB loop:

SUB _DBName(dbADDRESSES)
CGIWriteLn("<li>"+ GetField(dbADDRESSES, "lastname")+", "+ GetField(dbADRESSEN, "firstname") +"</li>")
ENDSUB

With any cycle passing the dataset pointer starts with the next entry as long as the last dataset wasn't passed.

The alternative construction using ReadRec()-NextRec() loop looks like this:

Var nRec : Integer
nRec := FirstRec(dbADDRESSES)
WHILE nRec DO
    ReadRec(dbADDRESSES,nRec)
    CGIWriteLn("<li>"+ GetField(dbADDRESSES, "lastname")+", "+ GetField(dbADDRESSES, "firstname") +"</li>")
    nRec := NextRec(dbADDRESSES)
END

Till now simply less source code lines argues for using SUB..ENDSUB.

I make the conditions here!

As discripted in the example above is the whole thing very unspectacular yet. The use of (dynamic) selection criterias might surely give it bit more condiment.

SUB _DBName(dbADDRESSES)

is this kind of a selective order the one that applies to alldatasets of the addresses.dat table.

What about our phone book having as many entries that you couldn't display them all on only one HTML page (or rather wouldn't like to)?
We would need an alphabetical index. Each letter of the alphabet gets its own page and the alphabetical sorted addresses will be displayed.

Now simply to search for entries whose lastname starts with "H" the following code line suffices

SUB _DBName(dbADDRESSES) + ", $lastname[1] = 'H'"

Now it becomes complexer. To do you easier to understand now we translate this line in runtime version:

SUB ADDRESSES, $lastname[1] = 'H'

As is generally known using ADRESSENwe select ALL datasets from the table addresses.dat. Using comma (a logical AND) we combine this selection with the following one.
Using $lastname we access to the value of the "lastname" field of any dataset. Only if its first letter (therefore the [1]) is identical with 'H' it will be used in the SUB..ENDSUB loop. Otherwise tdbengine will go directly to the next dataset. The interesting thing is that we don't use GetField() but use the field notation (that was legal even in the DOS-TDB) directly.
This kind of selection causes intern some optimisation. Tdbengine tries to find the optimal method of selection considering existing signs - it is a question of parts of seconds but sometime of more.

For comparison: The ReadRec() - NextRec() version would look like this:

Var nRec : Integer
  nRec := FirstRec(dbADDRESSES)
WHILE nRec DO
    ReadRec(dbADDRESSES,nRec)
    IF GetField(dbADDRESSES, "lastname")[1] = "H" THEN
       CGIWriteLn("<li>"+ GetField(dbADDRESSES, "lastname")+", "+ GetField(dbADDRESSES, "lastname") +"</li>")
    END
    nRec := NextRec(dbADDRESSES)
END

Now you see how easy it is to limit the mass of the files to analyse to a subset. It only needs a valid selection string.
You can also hold it in a variable and transfer it.

...
Var cSel : String = DBName(dbADDRESSES) +", Birthday < 1.1.1970 UND firstname like 'thomas'"
...
SUB _cSel
...
ENDSUB
...

Do never forget the underline to force a adynamic analysis because SUB doesn't want a string as parameter but a selection.

SUB by SUB

If come till here without getting completely bored you'll be worthy of a bit more strain.

What else can a dinosaur like SUB..ENDSUB beside going through data inturn?

For example you can call data from divers tables related to each other.

PROCEDURE Main

Var dbBRANCH : INTEGER = OpenDB("..,/database/branch.dat")
Var dbADDRESSES : INTEGER = OpenDB("..,/database/addresses.dat")

RELATION

CGIWRiteLn("content-type: text/html")
CGIWRiteLn("")
CGIWRiteLn("Branch affiliation<hr><ul>")

PrimTable(dbBRANCH) //Set branch.dat as main table
Access(dbBRANCH, "branch.id") //Sort by the ID index (=alphabetical by name)

SUB _DBName(dbBRANCH)
CGIWriteLn(GetField(dbBRANCH, "Name") +":<br>")

ENDSUB

CGIWRiteLn("")

CloseDB(dbADDRESSES)
CloseDB(dbBRANCH)
ENDPROC

A possible result:

Accounting:

Bares, Baerbel
Schein, Sabine

Computing:

Bit, Birgit
Platine,Peter
Virus, Viktor

So what's all new?

First thing is that we no more have only one OpenDB() command in our program but two (it's easy to understand why).
Now the Branches are to be called, too, which are in an other table abteilung.dat. That's the way the normalisation rules want it.
After the second OpenDB() the command RELATION follows.
Also Access() call which PrimTable() precedes now relates no more to the table addresses.dat but to branch.dat
The function PrimTable() says tdbengine which table has to be treated as the prime data. It's the best you use PrimTable() before working with divers tables related to each other.
Our SUB..ENDSUB now looks a bit different:
Now there are two nested loops. The external cycle is used with the table abteilung.dat. The interior loop is the same as in the first example we told about it often enough.

In the following we want to dissect the new commands and constructions exacting...

RELATION

Using RELATIONyou order tdbengine to apply the rules and automatisms of ADL system to all tables opened before. So you relate the randomly opened tables to each other.
But it all will only work if you give each of youre tables an AUTO field. It has the function of a prine key of the table and of a foeign key of all the other tables it's related to.
In turn to connect tables you'll need so-called LINK fields. I'd like to give you a link to a detailed tutorial for ADL (Automatic Data Link) system now. Unfortunately I haven't one. Perhaps a good idea for a further HOWTO?!
So for our example above we have to assume that the table branch.dat has a field of type AUTO. We assume it's name is AutoID. Also our addresses.dat needs a possibility to save AutoID of the branch a colleague is working in. Thereto we use a LINK field in addresses.dat whose name is LinkBRANCH
The combination of AUTO and LINK fields caring for relations is one of the clous of tdbengine (others are for example size, speed, indexing of the full text, programmability). At least with SUB..ENDSUB loops (and in some other specific cases).

Nesting of SUB..ENDSUB

comes soon

Author: Thomas Friebel <tf@tdb.de>

tdbengine Anwendungen im Web:

Open-Source Web CMS

Bug-A-Boo - Open Source Bug Tracking System

Open-Source Bug-Tracking

Free wiki hosting

Open-Source Wiki-System

Kostenloses Foren-Hosting

Diät mit tdbengine 8-)

tdbengine chat irc.tdbengine.org #tdbengine

Programmers Heaven - Where programmers go!