Liberty Basic is develeopped by Carl Gundel
Original Newsletter compiled by Alyce Watson and Brosco
Translation to HTML: Raymond Roumeas

  Brosco's Liberty Basic Newsletter - Issue #12 - June 98

In this issue:

  1. Using Random files - Part 3 (Using the DBdll to Index a Database)


Last issue we improved the program to maintain our collection of movie cassettes - but we still had a couple of problems with the solution:

1) The Previous and Next functions returned records in Random sequence - (it used the sequence that they were physically stored in the database).

2) To use the GET function - you had to enter a valid record number.

For demonstration puposes - when you add movies to this new database use 'Cassette Numbers' of C001, C002, etc. for your Comedy movies, D001, D002, etc for your Drama movies, etc.

Lets have a look at the changes that I made to the program.

open "dbdll.dll" for dll as #db

This Opens the DLL and allows us to access the functions that it contains.

if lof(#f)/55 = 0 then       ' Empty DB - create CONTROL Rec
        casNum$ = "0"
        mainActor$ = "(Control Record)"
        put #f, 1
                            ' Create the Index -
 
        calldll #db, "CreateIndex", _
            fn$ as ptr,_     ' Full path to the index file
            6 as word, _     ' Length of the key - maximum 255 bytes
            0 as word, _     ' 0 = No duplicates , 1=Duplicates OK
            result as word   ' 0 = OK, anything else is a file error
 
        print "Create Index returned:";result
        end if

The first part of this code is the same as last Issue. If the Database doesn't exist - we must create it and initialise the Control Record. But since the DB didnt exist - obviously, we also need to Create the Index file as well.

The parameters to the CreateIndex function are fairly easy. We need to specify a file were the Index data will be stored. We also tell it the maximum size of the KEY field that must be recorded - in this case, its the Cassette Number field, which is 6 bytes long. Then we tell it if the Index can have duplicate keys. In other words, can we record 2 different movies on the same cassette. For this exercise, I have assumed that there is only 1 movie per cassette.

' Open the Index file for processing
 
    calldll #db, "OpenIndex", _
        fn$ as ptr, _      ' File name used in CreateIndex
        0 as word, _       ' Share mode
        0 as word, _       ' Normal operation
 
        hx as word         ' Handle of the Index file to be
                           ' used in subsequent calls

CreateIndex does not leave the file open for processing, so we must Open it. Obviously, the Filename is the same one we used in CreateIndex. ShareMode is an option for Networked computers with multiple users - it is not required for this exercise - so just set it to 0. The next parameter is a performance related option and is only required for very large databases, so just leave it set to 0. Finally, hx is the file handle that is returned by the call. The returned value must be greater than zero - otherwise its an error (the index couldn't be opened). This field will be used in ALL subsequent calls to the DBdll.

BROWSING THE DATABASE

'  Position the browser at the start of the database
    calldll #db, "GetFirst", hx as word, "" as ptr, cNum as long
    if cNum > 1 then
        get #f, cNum
        gosub [display.movie]
 
        end if

Since we will be Using the Previous and Next functions to Browse the database - we must 'initialise' DBdll's browser. There are two functions available for use: GetFirst and GetLast. GetFirst returns the RecordNumber of the first Key whose value is equal to or greater than the Key supplied - in this case, we specified a searck key of "", so we will be returned the RecordNumber of the lowest Key in the Index.

[previous]
    CallDll #db, "GetPrevious", hx as word, cNum as long
 
    if cNum < 2 then     ' Control rec = 1.  cNum = -1  Not Found!
        casNum$ = ""
        name$ = ""
        mainActor$ = ""
    else
        get #f, cNum
        end if
    gosub [display.movie]
    if cNum < 2 then print #w.status, "You are at the start of the database"
    goto [loop]
 
[next]
    CallDll #db, "GetNext", hx as word, cNum as long
    if cNum < 2 then            ' cNum = -1   - Not Found!
        casNum$ = ""
        name$ = ""
        mainActor$ = ""
 
    else
        get #f, cNum
        end if
    gosub [display.movie]
    if cNum < 2 then print #w.status, "You are at the end of the database"
    goto [loop]   

The GetNext and GetPrevious functions 'remember' the Browser's current position in the Index - so you dont need to provide a search key. When there are no more keys to retrieve, a RecordNumber of -1 will be returned.

RETRIEVING A RECORD BY KEY VALUE

[get.movie]
    print #w.cn, "!contents?"
 
    input #w.cn, casNum$
'                           You can access a record by typing in the
'                           casNum$  and clicking on "Get"
    key$ = trim$(casNum$) + chr$(0) + "          "
    CallDll #db, "GetKey", hx as word, key$ as ptr, cNum as long
    if cNum < 2 then
        notice "This key doesn't exist in the database!"
        goto [loop]
        end if
 
    get #f, cNum
    gosub [display.movie]
 
'  Keep our Browser in Sync with the position in the DB we are working on
 
    calldll #db, "GetFirst", hx as word, key$ as ptr, result as long
 
    goto [loop] 

If you type in a Key value - say 'C002', and click on Get, this function will look in the index to see if the Key exists. If it does, it will return the RecordNumber of where it exists in the database. If not, it will return a value of 0.

The DBdll's position 'memory' is only updated by the GetPrevious and GetNext Functions (or a new GetFirst or GetLast). So if the user GETs a record, and then clicked on Next - he would get the Next record after the last one that was BROWSED - NOT the Next one after the one he just retrieved using the GET function - this would look a little strange - so the last call we make is a GetFirst, using the KEY of the current record. This will reposition the 'browsers memory'.

ADDING A RECORD TO THE DATABASE

[add.movie]
 
    print #w.cn, "!contents?"
    input #w.cn, casNum$
'                          Ensure that this KEY isnt in the DB already
 
    key$ = trim$(casNum$) + chr$(0) + "          "
    CallDll #db, "GetKey", hx as word, key$ as ptr, result as long
    if result > 0 then
        notice "This key exists in the database - duplicates not allowed"
        goto [loop]
        end if
 
    save$ = casNum$         ' [GetEmptyRec] destroys the value of casNum$
    gosub [GetEmptyRec]     ' Find somewhere to write the new record.
    casNum$ = save$
    gosub [write.record]
'                   [write.record] added at cNum - so tell the index
 
    CallDll #db, "AddKey", hx as word, key$ as ptr, cNum as long, result as word
 
'  Keep our Browser in Sync with the position in the DB we are working on
    calldll #db, "GetFirst", hx as word, key$ as ptr, cNum as long
 
    print #w.status, "Movie: " + casNum$ + " has been added."
    print "DB After Add new Record:"
    gosub [print.db]
    goto [loop]
 

This looks a litle long and complicated - but when you view it in small logical sections, you will see that its really quite simple. Before we add a new record to the database, we insure that the Key has not been recorded before - remember - we have said that there is to be NO duplicate keys. If we dont make this check - AddKey would fail - but the 'Put, #f, cNum' would work! We would end up with a record on the database - but we would never be able to retrieve it! In computer terms - this is referred to as 'creating an orphan'!!!!!

Next we use the function that we created last issue: [GetEmptyRec] to find a place in the database to add it. Remember, this function re-uses space left from deleted records if available. Unfortunately, this function changes the value of casNum$ (when it reads in the Control record) - so we need to preserve the casNum$ value before the call is made.

[GetEmptyRec] gives us a value in cNum where we can write the new data record. We update the index by telling it about the Key and were the data is stored (cNum). We also need to update the 'Browsers' position.

UPDATING A RECORD

OK, I am running out of space in this Newsletter (I am restricted to 10Kb) - so you will need to refer to the complete sample program for the code associated with the following explanations.

Obviously we need to have the facility to update a record, maybe fix a spelling mistake, or perhaps we have recorded a new movie on the cassette. If we do this - no problem. But what if we changed the cassettes label from, say, C004 to D007? We would have an entry in the Index for C004 - but the data record would have D007! With Index schemes like DBdll - you cannot update a key value - you must DeleteKey the old value and AddKey with the new value.

DELETING A RECORD.

key$ = trim$(casNum$) + chr$(0)
    CallDll #db, "DeleteKey", _
        hx as word, key$ as ptr, 0 as long, result as word

OK, that's fairly straight forward - buts what's that '0 as long' in the middle of the parameter list? That's used when we have an Index which allows Duplicate keys. You would also need to specify the RecordNumber (cNum) of the key that you wanted to delete, so that DBdll knew which of the duplicate Keys is the one to be deleted.

********************************

To get the full sample program, download NL0012.ZIP from my Newsletter Archives page at:

http://users.orac.net.au/~brosco 

Also see - Using Random files - Part 1

Also see - Using Random files - Part 2


Newsletter written by: Brosco. Comments, requests or corrections to: brosco@orac.net.au Translated from Australian to English by an American: Alyce Watson. Thanks Alyce.