The need to include a simple database in a current project pushed me into investigating the RealDataBase in the new RealBasic2.0. Dan's article in last's month's magazine got me started. The RealBasic Developer's Guide helped also. But, none of these really laid out something that could be picked up and watched, as it executed. As I flailed around, I found that working with a database was hard. But, not because its hard; instead, its because the terminology is so unfamiliar.
So, I set out to create a little application that would allow me to create a very simple database. And, I did a second one to read it. I separated the two functions to make it absolutely simple. I wanted to focus on the database, not menus, windows, and such. I really wanted to learn what the strange terms meant and how things functioned.
Creating a Database
So, lets start with the app that creates the database. I called it DbaseMaker.
It has a window with one StaticText and one button. The button's Action sub contains
the single instruction: Quit. This allows the window to stay open, showing
the StaticText until the button is clicked.
The window's Open event is where all the action is. Much was taken out of the
on-line reference and pieced together into something that works. Here is the listing:
Sub Open()
dim dbFile as FolderItem
dim r as DatabaseRecord
dbFile = GetFolderItem("DBaseTest.rdb")
If (dbFile nil) AND (dbFile.exists = false) then
db = NewRealDatabase(dbFile)
If db nil then
db.SQLExecute("create table invoice(id integer not null, Cust_ID integer not null, Amount varchar (25), Date varchar (25), primary key (id))")
r=new DatabaseRecord
r.Column("id")= "04"
r.Column("Cust_ID") = "02"
r.Column("Amount") = "$9.98"
r.Column("Date") = "10/22/98"
db.InsertRecord("invoice",r)
r.Column("id")= "17"
r.Column("Cust_ID") = "33"
r.Column("Amount") = "$1356.78"
r.Column("Date") = "112/30/99"
db.InsertRecord("invoice",r)
StaticText1.text = "DBaseTest.rdb has been saved."
else
StaticText1.text = "DBaseTest.rdb could not be created for some reason"
End if //db nil
Else //dbFile
StaticText1.text = "DBaseTest.rdb is already present. Please remove it for most reliable operation."
End if //dbFile
End Sub
And here is what happens in that subroutine. First, it gets a FolderItem, dbFile,
for DBaseTest.rdb. This is the name of the file that is going to contain
the database. I wanted to find out if it was already present or not. If the dbFile
is nil or if its exists property is true (the latter happens when the file is already
present), skip the database creation and report that fact in the StaticText at the
end of the subroutine. But, if dbFIle isn't nil and it
does not already exist, then proceed by creating a new database using db =
NewRealDatabase(dbFile). If that instruction returns nil something
is wrong and it should not proceed. But, if its NOT nil, then we proceed
by putting stuff into the database.
The instruction db.SQLExecute("create table invoice(...)")
was the first really alien thing we encounter. It creates a "table" named
"invoice". A table, in database-speak, is a collection of data that shares
a common arrangement. In this case, the invoice contains "records". Each
record contains the information about a sales transaction. Every transaction is recorded
in one record. Every transaction includes an "id" number. The idea is to
have a unique number for each transaction/record. Each transaction has a customer
number, "Cust_ID". This might have been obtained by looking up in another
database or another part of this database (not yet created); here, we are going to
use arbitrary numbers. There is also an "amount", and a "date".
A real invoice would certainly have more, such as what was purchased, but this will
do for our experiment. You can think of these as "columns" in the database
table named "invoice" while the records are "rows". Be aware,
howwever, that these are abstractions and have little to do with how things are stored
in the database.
Lets look a little more closely at how the table columns are specified in the
create table invoice(...) part. Each of the columns has to be specified
as part of this SQL command. The first is id integer not null. This
says that the id number must be an integer and that there must be something in it
for the record to be acceptable to the database. The second is Cust_ID integer
not null; it looks just like the previous one. The third will probably look
very strange to BASIC programmers: Amount varchar (25). In spite of
its appearance, this says that the Amount column contains a string that
can be up to 25 characters long. The same is true for date. The last item may also
look a bit strange; it tells which of the columns is guaranteed to be unique for
purposes of extracting information. Take a look at the section in the Developer's
Guide on the Create Table Statement in the explanation of Structured Query Language.
Now, we have a database, and we can start putting stuff in. The instruction r=new
DatabaseRecord provides space for one record. Put things into the record with
statements like r.Column("id")= "04". Columns can
be specified in any order; notice that the columns are referenced by the name of
the column. Another really strange thing, to me, anyway, is the form of the data.
Its a string even though the id column was specified to be an integer. I have not
yet tried to see what would happen if that string contains non-numeric characters.
Once the columns of the new record are filled in, db.InsertRecord("invoice",r)
sends it to the actual database. This adds a new record; you change records
another way. The program adds two records to the empty database, then notifies you
via StaticText1 that it has finished.
Reading the Database
The database has been created and has two records. Lets try reading it. I called
the app DbaseReader. It has a window with several StaticTexts and two buttons. The
"Quit" button's Action sub contains the single instruction Quit.
This allows the window to stay open, showing the StaticText until the button is clicked.
There is another button captioned "Next". It steps through the records
in the database.
This program works very similar to DBaseMaker. There are several differences,
howver. One is that there is a class property: cur as DataBaseCursor.
Another is that there is a method named ShowRecord. The cursor is a
class property so that it can be readily passed from the place where the database
is read (the app's Open event handler) to the place where it is displayed, ShowRecord
and to the Next button handler.
Lets look first at the Open event handler.
Sub Open()
dim dbFile as FolderItem
dim db as Database
dbFile = GetFolderItem("DBaseTest.rdb")
if (dbFile nil) AND (dbFile.exists = True) then
db = OpenRealDatabase(dbFile)
if db nil then
cur = db.SQLSelect("select * from invoice")
ShowRecord("First")
else //db nil
StaticText1.text = "DBaseTest.rdb can't be opened for some reason. "
StaticText1.text = StaticText1.text + "Try discarding it and running DBaseMaker again."
End if //db
else
StaticText1.text = "DBaseTest.rdb can't be found. Please run DateBaseMaker first!"
End if //dbFile.exists
Again, a folderitem for DBaseTest.rdb is created. This time, however,
the fault case is a nil value for dbFile or that dbFile.exists
= FALSE. If neither of these are true, then it is possible to open the file
with db = OpenRealDatabase(dbFile). If this is also not nil,
the the program can proceed.
Data is retrieved from the database using cur = db.SQLSelect("select
* from invoice"). What this means is to make a "cursor" (a
datastream, if you will) containing all (that is what the "*" means) of
the data from the invoice table. No sorting or anything else has been
done; we are just going to view it to verify that we can recover what was stored.
ShowRecord displays the first record. The parameter is used to indicate
in StaticText which record is being displayed.
Sub ShowRecord(Ordinal as string)
dim CrLf as string
CrLf = chr(13)
#if TargetWin32 then
CrLf = CrLf + Chr(10) //add line feed
#Endif
StaticText1.text = Ordinal +" record:"+CrLf
StaticText1.text = StaticText1.text + cur.idxField(1).Name + " = "
StaticText1.text = StaticText1.text + cur.idxField(1).StringValue+CrLf
StaticText1.text = StaticText1.text + cur.idxField(2).Name + " = "
StaticText1.text = StaticText1.text + cur.idxField(2).StringValue +CrLf
StaticText1.text = StaticText1.text + cur.idxField(3).Name + " = "
StaticText1.text = StaticText1.text + cur.idxField(3).StringValue+CrLf
StaticText1.text = StaticText1.text + cur.idxField(4).Name + " = "
StaticText1.text = StaticText1.text + cur.idxField(4).StringValue
cur.moveNext
If cur.EOF = true then
NextButton.enabled = false
else
NextButton.enabled = true
End if
End Sub
If we look at the ShowRecord subroutine, the first thing we might
see is the #If block; it is intended to account for the differences
in end-of-line between Mac and Win32. It seems to work as it is; I have not tried
leaving it out. It makes the CrLf string to be a carriage-return, then if its Win32,
it adds a line-feed. Next, we see that a string containing 5 lines of text are created
and transferred to StaticText1.text. The StaticText in this window has to be large
enough to show 5 lines of text.
Next, the program just steps through the four columns of the record in the cursor.
cur.idxField(1).Name gives the name of the first field of the record
as it is arranged in the cursor. cur.idxField(1).StringValue contains
the data from that column. The same is true for the 2nd, 3rd, and 4th columns. Finally,
cur.moveNext moves to the next record. The next couple of lines
may warrant a short explanation. cur.EOF is true when there are no records
in the cursor. EOF stands for "end-of-file". If the end has
not been reached, the Next button is enabled; if the end of the file has been reached,
then the Next button is disabled.
Finally, the Next button handler simply calls ShowRecord. The only
time the user can do this is when the first record has been shown in the Open event
handler since there is one record, still in the cursor. Once the Next buttton handler
has been executed once, the end of the cursor has been reached and the Next button
is disabled.
We are at the end of this little database demo/tutorial. Its pretty simple. We didn't sort or modify a database. But, we created one with one table and two records in the table. We then read the table, showing that data could be recovered. This should get most of you up and going. Have a go at it.
This has been contributed by Jim Wagner. He is a professional electrical engineer and embedded-processor programmer (8051 assembly language). His employment has included Tektronix, the College of Oceanography at Oregon State University, and now Kalatel, Inc. in Corvallis, Or. RealBasic is one of many things he does for fun; other fun things include mountain climbing, ham radio, bike riding, and cross-country skiing. He can be reached by e-mail at wagnerj@proaxis.com or ka7ehk@yahoo.com.