NewsLetter TOC
This article follows on from my earlier article on using SQLite with LB. This time, We will discuss the functions required to link Tsunami and Liberty Basic, Based in the same approach as previously, we will a database of details of singers, later to be linked to audio CDs.
You will need the following files to follow this article:
Keep the .bas and .dat files together, and place trm.dll in the windows\system directory.
As before we will discuss opening and closing a database, creating a database structure, adding, modifying, and deleting records, and finally searching the database for a particular singer.
But first, this is the code that you will require in your programme to access Tsunami.
In your main programme,
struct Tsu , _ op as long, _ file as long, _ dataptr$ as PTR, _ datalen as long, _ keyptr$ as PTR, _ keylen as long, _ keyno as long dim error(1)
and after your main programme the functions CallTsunami(), and CallTsunami$(). The first will be used to call a function returning a number, and the second for a function returning a string. The syntax for each call is quite precise, as we shall see later. Now let us start to use Tsunami.
Defining the database structure is quite complex in Tsunami. It is possible to define one within a running Liberty Basic programme, but this is difficult and error prone, so I will not illustrate this at this time. (I am in the process of designing a stand-alone programme, FilDefWizard.zip, to help with this task. When complete, it will be uploaded to my files area on the Yahoo forum.)
For now, we will use one I created earlier - singers.dat. This has four fields in each record, each 20 characters long - firstname, lastname, genre, identity.
To use Tsunami, we must first open a channel the trm.dll, and get a handle for the database we will use. At the finish of the programme we must close both the handle, and the channel to Tsunami. Here is the code:
open "trm.dll" for dll as #tsunami
dbase$ = "singers.dat"
dummy = CallTsunami("opendb", 0,0," ", dbase$,0)
dbHandle = Tsu.file.struct
[quit]
dummy = CallTsunami("closedb",1, dbHandle, " ", " ", 0)
close #tsunami
stop
Observe that the function CallTsunami() takes 6 parameters. This is always the case with this version of the tsunami interface, no matter how much information is passed either way. What is in each of these parameters is not always significant, and varies from call to call, but you must always include them all.
The first parameter is a text string containing a description of the function. This is not used within the function, so it could be anything, but it is useful to remind the programmer of what function is being called. The second parameter is an integer used to signify the call being made. Opening a database is code 0, closing is code 1, inserting a record is code 2, etc. In most calls, the third parameter is the database handle (dbHandle) obtained following the opening of a database file. The last three parameters are two strings and an integer. Their significance varies with different function calls.
Also observe the fourth line of the code above. After an "opendb" call, the database handle is found in Tsu.file.struct. It is not found in the result returned from the function call. This occurs in one or two other calls, (including the call to count records used later) and you need to pay attention to it to avoid difficult to find errors occurring.
Once the database is opened and we have a handle, we can start interacting with it. The first thing we will do is to display any existing records. Here is the code to do this:
[ShowRecords]
dummy = CallTsunami("count", 17 , dbHandle, " ", " ", 0)
count = Tsu.keyno.struct
print "there are "; count ; " records in the database "
select case count
case 0
print "we have no records yet "
return
case 1
print "record 1 is "; CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2 )
print "there is only one record"
case else
print "record 1 is "; CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2 )
for x = 2 to count
print "record "; x ;" is ";CallTsunami$("getnext$",6, dbHandle, " ", " ", 2 )
next x
print " the end of the records "
end select
return
In contrast to SQLite, Tsunami is mainly based around full records of a fixed length (as far as the programmer is concerned), whereas SQLite mainly fetches Tables and Fields. Calls fetch complete records. If your records are very long they will need splitting into fields before display or further processing, but this is not necessary in this application.
In Tsunami, we get a complete set of records by going to the first record, according to some criterion, displaying that, and then calling the next record and displaying that, and then the next record, and so on, until we reach the end of the records. This means that, if there are any records, we must call a "getfirst$", function, followed by zero or more calls to a "getnext$" function. This means three different programming scenarios: no records, 1 record, and more than 1 record. This leads to the Select Case routines above. How do we know what case to select? We call Tsunami to give us a count of the records in the database, at the top of the loop. See where we find the count, after the function call?
But what is the criterion that we use to find the start of the records, and the next record etc? Note the integer '2' at the end of the 'getfirst$' and 'getnext$' calls. This tells Tsunami that we want to order records by the second field, or Key (not actually a field, but close enough for our purposes), which is of course, lastname.
Adding a new record is accomplished with a single function call.
[AddRecord]
gosub [GetRecord]
dummy = CallTsunami("insert",2,dbHandle,record$," ",0)
return
Have close look at the [GetRecord] routine. In contrast to the one used for SQLite, this routine defines a complete record string, rather than a collection of fields, reflecting the different orientation of the two database managers.
Removing a record is a little more complicated. First we need to be sure we have a valid record number to remove. If we are removing the first record, we call the 'getfirst$' function, to position Tsunami's internal record marker correctly, then we call the delete function, to actually remove the record. If the record we need is further on in the database, then we must loop through the records with 'getnext$' until the record pointer is where we want it, and then call 'delete' as before.
[RemoveRecord]
r = 0
while r < 1 or r > count
input "what record do you want to remove? "; r
if r < 1 or r > count then print "You must select a valid record number "
wend
if r = 1 then
dummy$ = CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2 )
dummy = CallTsunami("delete",4,dbHandle," "," ",0)
else
dummy$ = CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2)
for x = 2 to r
dummy$ = CallTsunami$("getnext$",6, dbHandle, " ", " ", 2 )
next x
dummy = CallTsunami("delete",4,dbHandle," "," ",0)
end if
print "we have removed a record " : print
return
This looks clumsy to me, and there may be a more efficient way to do deletions - but this does work.
Modifying a record works in a similar way.
[ModifyRecord]
r = 0
while r < 1 or r > count
input "what record do you want to modify? "; r
if r < 1 or r > count then print "You must select a valid record number "
wend
gosub [GetRecord]
if r = 1 then
dummy$ = CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2 )
dummy = CallTsunami("update",3,dbHandle,record$," ",0)
else
dummy$ = CallTsunami$("getfirst$",12, dbHandle, " ", " ", 2)
for x = 2 to r
dummy$ = CallTsunami$("getnext$",6, dbHandle, " ", " ", 2 )
next x
dummy = CallTsunami("update",3,dbHandle,record$," ",0)
end if
print " we have modified a record " : print
return
This only differs from the removal of a record in the inclusion of the call to the Liberty Basic [GetRecord] function, to input new data for the record, and the use of the "update" tsunami call, instead of the delete call.
Finally, how to de search a database for a specific item of data, in this case, a lastname? Here is the code:
[search]
input "what lastname do you want to match? "; search$
result$ = CallTsunami$("getequals",5,dbHandle," ",search$,2)
print result$
anyMoreVals = 1
while anyMoreVals
match$ = CallTsunami$("getnext$", 6, dbHandle, " ", " ", 2)
if trim$(mid$(match$,21,10)) = search$ then
print match$
else
anyMoreVals = 0
end if
wend
notice "please check your result"
return
Finding the first match to our search term is simple. Tsunami provides us with a 'getequals$' call. But what if there are several records matching our search term, for example several singers with the same lastname? A second call to 'getequals$' only returns the first singer. Then we need a more complex strategy. In the example, I set a flag (anyMoreVals), then I call 'getnext$' within while loop, resetting anyMoreVals when 'getnext$' no longer matches the search string. I can then safely leave the while loop. However, remember that Tsunami is mainly a records based programme. Here however, we are intrested in comparing only one field to the search term we input. To test if the search string still matches the field we are interested in, we must slice up the record, with trim$(mid$(match$,21,10)). I have not yet found a way to make tsunami do that for us. On the other hand, I am new to Tsunami. If I find a way, perhaps by using "Key-Only" searches, I will post it up.
So now we have explored displaying records, adding, removing and modifying records, and performing a simple search. There is much more you can do, for example using indexes and 'Key only' searches for greater efficiency. I look forward to the LB community letting me know what they have done with Tsunami in the coming months.
Using Tsunami with Liberty Basic is very different from using SQLite. With Tsunami, that are a range of function calls to learn, with SQLite there are basically only three calls commonly used. With Tsunami you often need a combination of function calls, linked to some careful looping, to achieve what you want to do. With SQLite, much of that processing is done within the DLL. But with Tsunami, the function calls themselves are fairly simple - you do not need to construct long on complex query strings to pass to the catch-all SQLite_get_table routine. Try them both, and decide which approach best fits your own programming style.
NewsLetter TOC