Using the SQLite database manager in LB

An Introduction for Beginers

by Colin McMurtchie


NewsLetter TOC

Newsletter Home

Blast

MDI

OS Editor

LBW Review

Fast Data

SQLite

Tsunami

Begin Prog II

Locate/LBCard

"Can we put these on the computer?" she said.

"They won't fit", I said.

"Leave them on the shelves", I said.

"No!", she said, "put them inside the computer, so that we can look at them".

"But it is dark inside the computer," I said, "Just turn the lights on!"

We are talking audio CDs here. Lots of Audio CDs. At the last count, my partner and I had 1500 albums between us, which must be at least 20000 selections to keep track of. How sad is that? On the other hand, the wish to know how many different versions of "In the Pines" or "City of New Orleans" we had meant we had to think about putting things "in the computer". I had to think about using a database, or writing one!

Enter Liberty Basic, with it's wonderfully direct approach to GUI programming, and the ability to see all the code in one window if you need to. It seemed ideal for the job of input and display, but then what about the database itself? This led me to explore several approaches to database programming in LB, and in turn to contribute this article. We will come back to those piles of CDs later.

This article is for beginners, so do not expect advanced database manipulation. I do not know how to do that. I cannot as yet even index my finger, let alone my fields. But in a sense we are all beginners at using database managers in LB, because the tools to do this are newly developed. What I hope to do in this article is to explain the use of a database manager in LB, using very simple tasks - defining a database structure, opening it, adding records, modifying records, deleting records, performing a simple search, and finally closing the database. I will do this by reference to an application programme based on - you guessed it, Music CDs - specifically details of singers.

The database manager I have chosen is SQLite, because I have become most familiar with this. It is not the only method that can be used to add database functionality to LB. Much can be done with LBs native file handling commands, especially in Random mode. Also Dennis McKinney has recently produced an example of accessing Microsoft Database structures from within LB (see LB News #102). The Cheetah database manager from Paul Sqires, with an LB inferface written by Walter Decker is also well worth looking at. I intend also to illustrate database use with the Tsunami database manager in a future article.

You will need the following files to follow this article:

Consolesqlite.bas and singers.dbs must be in the same directory. The SQLite.DLL file is best placed in the windows\system directory.

The database file (singers.dat and singers.dbs) has four fields in each record - firstname, lastname, genre, and identity. All are simple text fields.

The example programme is kept as simple as possible. For this reason it runs in the mainwindow, rather than through a GUI, although it is relatively easy to link to a GUI with buttons, menus, list-boxes and the like. This would also allow the easier referencing of one set of data from another.

Communicating with sqlite.dll is set up in the usual way for dynamic link libraries.

 	open "SQLITE.dll" for dll as #sql

opens the dll for communication as #sql, and

 	close #sql

closes it.

A named database file must then be opened through a call to #sql. These lines name the file, and then open it.

	dbase$ = "singers.dbs" 
	calldll #sql, "sqlite_open", dbase$ as ptr, 0 as long, "" as ptr, result as long
	dbHandle = result

If dbase$ includes a path, then a file in any directory can be used, or even a file name obtained from a file picker dialog. But note, however, that consolesqlite.bas contains very little error checking, and if you try to use a non SQLITE file, the programme will not tell you, but will just fail. After the dbase$, the dll call passes a value '0 as long'. This value is required, but is not used by SQLite. Any integer can be used.

The result of this dll call is important, as it is used to refer to this database in future accesses within the programme. It therefore is stored in the variable dbHandle.

Note that "sqlite_open" is a dual purpose call. It opens a database if it finds it, but it will create a new one if none exists in the location specified by dbase$. Be careful therefore, about knowing where your existing databases are stored, as you can easily create multiple instances of database files with the same name, but containing different data.

How do you close a database? simple, using dbHandle :-

	calldll #sql, "sqlite_close", dbHandle as long, result as long

All other operations on the database are performed by passing a database identifier, and a text string, to the function GetTable(). For example:

	query$ = "select * from singers"
	[ShowSomeRecords]
	dummy = GetTable( dbHandle , query$)

Ignoring the label [ShowSomeRecords], this snippet of code defines the text string as query$, and, using the database identifier dbHandle, calls the GetTable() function.

The appendix to this article explains some of the workings of GetTable() and the functions it calls in turn. I have gone into this in some detail, mainly because I think it is clever and I am proud of it! At 54 I have to get pleasure out of something! However, most users will not need to concern themselves with these details. To use GetTable() in your own programmes you must include the following in your main programme, preferably near the top.

	maxrecords = 2000
	maxfields = 5
	dim fields$(maxrecords, maxfields)

	struct d, _
	  	tableresult as ptr
	  	d.tableresult.struct = space$(200000) 

	struct nr, _
		numrow as long
	struct nc, _
		numcol as long
	
	nr.numrow.struct = 0
	nc.numcol.struct = 0

	struct e, _
		error as long

After the main programme, the function definitions of GetTable(), PointerPeek() and PseudoPeek() must also be present. That is, everything below the three comment lines ( '=============== ) in consolesqlite.bas.

When called, GetTable() either changes the contents of the database, adding, deleting or modifying the records, or it reads back information about the records into a two dimentional Liberty Basic array called fields$(). We will see examples of both of these modes of action in what follows.

Creating a database structure is relatively easy in SQLite. All that is required is to name a 'table', define fields for that table, and tell SQLite what you want. SQLite allows many tables to be created within a database, thus easily enabling the creation of links between different sets of data (relational database operations), but for this example I have created only one table. Here is the code :

	query$ = "create table singers ( firstname varchar(20), lastname varchar(20)," +_
	"genre varchar(20), identity varchar(20))"
	dummy = GetTable(dbHandle, query$)

Strictly speaking, these lines are unnecessary in this application, as the database already exists, and therefore does not need to be defined. However, if the file did not already exist (in the selected directory) these lines would define it, so that records could be added.

There are a couple of things to note about these lines of code. Firstly, we ignore the value returned by GetTable(). This value is actually an error code from SQLite, and it might be useful to print it out to the MainWindow during debugging. In the final application however, we will generally ignore it.

The second thing to note is the length of query$, spanning two lines of code. The power of the SQL database language lies in the flexibility of the various query strings that can be used. Although they are simple text strings, and seem to be in natural language, in fact they are highly structured, with a precise syntax of their own. Because of this, it is often useful to check their effects directly on a database, before using them within a programme. Richard Peeters has produced an application to do just that (see lbNews #105), and SQLite provide their own monitor that runs in a dos window.

So what does this phrase "create table singers (... ) " mean? This is not the place for a full discussion of SQL commands, but the above means roughly "create in the present database file a table called singers." A table is a 'framework' to contain a collection of records (called in SQL rows) with each record made up of a number of fields, or individual items of data( called by SQL columns). In this table, each record has a field called 'firstname', that can be up to 20 characters long, a field called 'lastname', also of characters that can be up to 20 characters long, and the same for 'genre' and 'identity'. All the fields in this table happen to be character fields. Other field types could be used, including numbers or dates.

Having created a database, we need to see what is in it. This is done by the subroutine at [showrecords].

[ShowRecords]
query$ = "select * from singers"
[ShowSomeRecords]
dummy = GetTable( dbHandle , query$)
count = nr.numrow.struct
select case count
	case 0
		print "We have no records yet "
		return
	case else
		print "we have "; count  ; " records"
		for x = 1 to count
		print "record number "; x ;" "; 
		for z = 1 to nc.numcol.struct
		print fields$(x,z);" ";
		next z
		print
		next x
		return
	end select
return 

The first thing we do is to create a query$ to send to GetTable(). "select * from singers" will return everything in our table, which is a good place to start. This loads our data into the array fields$(row, column). The number of rows we have in the table is given to us by the contents of nr.numrow.struct, which we copy into the variable 'count' for ease of understanding.

We may in fact have no data, if the database is empty. We need to check for this and print an appropriate message. If there are records, we use an inner and an outer loop to print out columns and rows of data, using count and nc.numcol.struct as indices of these loops. I have used a Select Case structure here to control programme flow. If, Then, Else, End If would have worked perfectly well.

How do we add a new record to the database? We get the data we need for each field and then compose a query$, using the key phrase "insert into singers values( ", followed by the values of the fields we wish to use for this new record. Simple really. Lets see that in the programme.

[AddRecord]
gosub [GetRecord]
' call to add a record to database
query$ = " insert into singers values('"+ f$ + "' , '"+ l$ + _
	"' , '" + g$ + "' , '" + id$ + "' ) "
' notice query$
dummy = GetTable( dbHandle , query$ )
 print "we have added a record " : print
return

gosub[GetRecord] gets user input for the various fields of the record, ie firstname, lastname etc, as f$, l$, g$, and id$. But look at the query$. This consists of multiple parts. Some are Literals in double quotes " etc ". whilst some are the string variables we have just collected. The layout of the finished string is critical. For example, each variable (f$, l$, g$, id$) must be immediately preceded by a ', and immediately followed by a '. If there are extra spaces here, the query will fail. Similarly if brackets or commas are wrongly placed, the query fails. For this reason during testing I often print the query$ as a notice, so that I can check it's construction and identify any error.

Deleting records? This should now be a piece of cake!

	[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
	query$=" delete from singers where identity = '"+ fields$(r,4)+ "') "
	notice  "remove string is "; query$
	dummy = GetTable(dbHandle,query$)
	print "we have removed a record " : print
	return

First we check that a valid record number has been selected (with a GUI based application this is not even a problem) then make the query$. the keyword 'delete' is self-exaplanatory. However, we only want to delete one record. The 'where' keyword in SQL is used to limit the application of a command, only applying it to our selection. The problem we have here is that the database, as we have defined it, does not allow us to refer to a specific record by its record number. This is where the 'identity' field comes in. We need to retrieve the contents of the identity field for the record we want, which we do with 'fields$(r,4)' (r for the record we want, 4 for the fourth field defined, ie identity). We then insert this into our query$, and ask SQLite to delete away.

Lets now look at modifying a record, perhaps because we have made a spelling error, or our singer has changed her name. Here is the code:

	[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]
	query$=" update singers set firstname = '" + f$ + "', lastname = '" + l$ + _
		"' , genre = '" + g$ + "' , identity = '" + id$ + _
		"' where identity = '" + fields$(r,4)+ "' "
	' notice  "modify string is "; query$
	dummy = GetTable(dbHandle,query$)
	print " we have modified a record " : print
	return

No surprises here. This simply combines all the techniques we have used in adding and removing records. However, Look at the construction of query$. Four string variables and a string array member must be embedded in the final string, together with commas, single quotes, double quotes and = and + signs. All must be precisely placed.

Lastly, how to do a simple search. We are looking for singers with a particular last name.

	[search]
	input "what lastname do you want to match? "; search$
	query$ = "select * from singers where lastname = '" + trim$(search$) + "' "
	gosub [ShowSomeRecords] 

	  notice "please check your result"
	return

This should be fairly easy to follow. This uses the "select * from singers " syntax, with a added qualifier to make sure we only return the records we are interested in. Notice the trim$() function. We do not want our search to fail just because of a rogue space before or after our search term. Observe also the notice "please check your result". This is not commented out because it is neccessary to pause the programme if we are to actually see the results of this call. Unlike the add, remove and modify actions, we cannot see the results of this action displayed in the full database.

And there we have it. A simple application it may be, but it serves to illustrate what can be done with databases in LB, and some of how that can be achieved. Modify and enjoy!

Oh, one last ting.

"Are you going to type in all these details?" she said?

What do you think I said?

Appendix

The procedures involved in manipulating the SQL database.

All Communications with SQLite in this programme, apart from opening and closing the database, are handled through a call to "sqlite_get_table".

	calldll #sql, "sqlite_get_table", dbHandle as ulong, query$ as ptr, _
	d as Struct, nr as struct, nc as struct, e as struct, result as long

The SQLite literature states that this routine returns an "Array of Pointers" to the calling programme, through the structure d. These pointers are to character strings placed in memory by SQlite, that represent fields in the database. These pointers are decoded to addresses in memory by the functions PointerPeek() and PseudoPeek(), and, when valid, are translated back to liberty basic strings with winstring().

The array of pointers to the database fields are returned to Liberty Basic as a string in turn pointed to by d.tableresult.struct. This string contains the pointers which are each 4 bytes long. If these pointers could be deciphered into memory addresses, then Liberty Basic could access the strings starting at that addresses through the winstring() function.

Some early BASIC dialects had a peek() function that returned the numeric value of a byte of memory. Liberty Basic does not have this function, presumably because it may not work reliably in the Windows environment. However, Liberty Basic must, at some level, access specific bytes of memory, or how else can it read strings through winstring()?

PseudoPeek() takes a one byte memory location, and reads its contents, by treating that location as the start of a string, therefore passing that value to winstring(). The value of the first byte of the string is simply asc(winstring(memory_location)). However, this approach cannot work if the memory location holds a value of zero. Zero is the string terminator, telling Liberty Basic that no string exists, or rather, that the first byte of this string is the byte after the last character of the string! Calling asc() will fail. However, it happens that a string consisting only of chr$(0) has a length of zero, so it can be tested for with len(winstring(memory_location)), which returns a value of zero. If this test is done first, then PseudoPeek() will reliably return the value of the memory location it is given. However, it is still a PseudoPeek() and not a real Peek(). It should only be used where the programmer has a good idea of what is in the memory location referenced.

PointerPeek() simply calls PseudoPeek() four times, and does some arithmetic to calculate the correct memory address, which is then returned to GetTable().

PointerPeek() and PseudoPeek() could be used in other circumstances where a DLL calls return an "Array of Pointers". However, I summise that there are some important conditions that would apply. Firstly, a check should always be made for the return of a value of zero from PointerPeek(), as if 'print winstring(zero)' is called a crash inevitably occurs. Secondly, it is important to know when the "array of Pointers" ends, i.e. to know how long the "array of Pointers" is. This is explained later. Lastly, as each of the pointers in this array itself points to a character string or some other data placed into memory by the dll, the memory must be released, or an 'out of memory' error will be generated by Windows. SQLite provides "slqite_free_table" to take care of this. This should be called sometime after every call to "sqlite_get_table".

Why is it important to know the length of the "array of Pointers"? This is because what is returned to Liberty Basic from the DLL call is a pointer, or in this case, its synonym from the DLL's point of view, a struct ( d as struct, ). In most circumstances, DLL calls returning pointers to Liberty Basic point to either a pre-defined structure within the LB programme, therefore of known length, or a character string, terminated by a chr$(0). In either case the length of the entity pointed to is not a problem. However, an array of pointers can vary in length, depending upon the results returned, for example, the number of matches for a search string. Also the occurrence of a zero byte is not necessarily the end of the array, so the programmer must explicitly determine how many bytes need to be read. Fortunately, sqlite_get_table, if it returns any data at all through a table, tells us the number of rows of data it is returning (in nr.numrow.struct), and the number of columns (in nc.numcol.struct), plus the titles of those columns. From here, it is a short step to determine the length of the array of pointers :-

PointersReturned = (nc.numcol.struct * (nr.numrow.struct+1) ).

PointerPeek() is called by GetTable(). The function GetTable() simply calls a given database with a specified query string, and, if a table is returned (that is if nr.numrow.struct <> 0) it reads each table entry into the two-dimentional array fields$(rows, columns), again using nr.numrow.struct and nc.numcol.struct to provide the indicies. Row 0 of this array always contains the column headings, but this is ignored by this programme. Having captured the table in the Liberty basic array, memory is released through sqlite_free-table.

SQLite sometimes returns "NULL POINTERS" in the middle of tables, particlularly within queries to 'sqlite_Master'. In these cases, PointerPeek returns an address of zero. If this is passed to winstring() it will crash the programme, if not bring down Windows. I have now put a check in the function GetTable() to prevent this happening. This was not in my earlier versions of the interface. Thanks to Richard Peeters for alerting me to this bug and helping me fix it.

NewsLetter TOC

Newsletter Home

Blast

MDI

OS Editor

LBW Review

Fast Data

SQLite

Tsunami

Begin Prog II

Locate/LBCard