OOo Basic crash course: Creating a simple game using strings in a database

Just because OpenOffice.org Basic is designed to automate mundane tasks doesn’t mean that you must use it only for serious work. It’s a programming language after all, and nothing stops you from using it to write something fun. Today we’ll use it write a simple game where you have to guess a word, a letter at a time, from among words you’ve stored in a Base database. Although this is not a particularly sophisticated game, it contains a couple of string manipulation techniques and a clever trick for picking a random record from a database, which you might find useful when writing your own macros.

Before you start coding the game, you have to create a simple Base database (let’s call it “WordDB”) with a table called “wordlist” containing two fields: the “ID” integer field that acts as a primary key, and the “Word” text field used for storing words. Populate the database with the words you want to use in the game.

With the database in place, you are ready to get to work. You can connect to the database with three lines of code:

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("WordDB")
ConnectToDB=DataSource.GetConnection ("","")

To make the macro a bit more user-friendly, you might want to add a simple routine that returns an error message and stops the macro if it fails to establish the connection. In that case, the code would look something like this:

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
 If not DBContext.hasByName("WordDB") then
   MsgBox ("Connection failed!" , "Error!") : End
 End If
DataSource=DBContext.getByName("WordDB")
ConnectToDB=DataSource.GetConnection ("","")

To retrieve records from the database using an SQL statement, the macro uses the RowSet service, which you must initiate first:

SQLResult=createUnoService("com.sun.star.sdb.RowSet")

Next, you need to create and execute an SQL query that retrieves all records from the database. You can use the standard SELECT command for this. Although the SQL dialect of the built-in HSQL database engine is pretty close to standard SQL, there are a few minor but important differences, the most notable of which is that fields and database tables in an SQL query must be included in quotes:

SQLQuery="SELECT ""ID"", ""Word"" FROM ""wordlist"""

Now you can add code that executes the specified query to retrieve all the records in the “wordlist” table:

SQLResult.activeConnection=ConnectToDB
SQLResult.Command=SQLQuery
SQLResult.execute

Besides the contents of the records, you also need to obtain the total number of retrieved records, which you’ll use to generate a random number that doesn’t exceed the number of records. For example, if you have 15 words in the wordlist table, then the macro can generate a random number not higher than 15.

SQLResult.Last
Rows=SQLResult.getRow

With all the required bits in place, you can generate a random number and use it to pick one of the retrieved records:

Do
 RandomID=Int(Rnd()*Rows)
 SQLResult.Absolute(RandomID)
 RndWord=SQLResult.getString(2)
Loop While isEmpty(RndWord)

In the code above, the macro generates a random number (RandomID). Then the macro picks a record, the ID of which equals the RandomID value. Finally, the macro assigns the contents of the Word field to the RndWord variable, which is going to be the word that the user has to guess.

The next step is to turn the picked word into an array of letters. To do this, you can use the Split() string function, which bursts a given string into an array of characters using a specified separator, for example, space. The problem is that the words in the “wordlist” table don’t have any separators. This means that the macro must turn the picked word into characters separated by spaces, so, for example, the word “monkey” becomes “m o n k e y.” The following code does exacly that using the Right(), Left(), Space(), and Trim() string routines:

WordLength=(Len(RndWord))
i=1
NewWord=""
While i

The processed word can then be transformed into an array:


LetterArray=Split(FinalWord, " ")

You also need to obtain the number of letters in the string, which you can do using the UBound (Upper Bound) function:

LetterCount=UBound(LetterArray())

The macro needs the number of letters so it can stop the game once the last letter has been guessed correctly. To do this, the macro uses a simple counter and the While ... Wend loop:

RoundCounter=0
While RoundCounter
Next, the macro displays an input box, in which the user enters the first letter:

InputLetter=InputBox("Type a letter", "Letter Game")

To stop the game at any time, the user can leave the input field empty and press any button. The following code aborts the game if the input field is empty:

If InputLetter="" Then : End
End If

We use a RoundCounter not only to keep track of game rounds, but also to move to the next letter in the LetterArray array when the user guesses the current letter correctly. The following code block takes care of that:

If InputLetter=LetterArray(RoundCounter) Then
 MsgBox ("Correct! Now guess the next letter of the word.", , "Correct answer!")
RoundCounter=RoundCounter+1

If the user enters an incorrect letter, the macro displays an appropriate message and doesn't move to the next letter:

Else
 MsgBox ("Wrong! Try again.", , "Wrong answer!")
 End If
Wend

What happens after the user has guessed all the letters correctly is up to you. The code below prompts the user to enter the whole word and checks the result. To re-assemble the word back from the letter array, the macro makes use of the Join function.

MsgBox "So what is the correct word?"
CorrectWord=Join (LetterArray, "")
InputWord=InputBox("Type the word", "Letter Game")
 If Inputword=CorrectWord Then
 MsgBox ("Yep, that's the word!" & Chr(13) & "You win!", , "Congratulations!")
 Else
 MsgBox ("Nope, that's not the correct word. " & Chr(13) &_
 "The correct word is " & """" & CorrectWord & """." & Chr (13) &_
 "Well, better luck next time!", , "Oops!")
 End If

At the end of the game, close the connection to the database:

ConnectToDB.close
ConnectToDB.dispose()

That's all there is to it. Here is the entire macro for your perusal:

Sub LetterHunt()
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
 If not DBContext.hasByName("WordDB") then
 MsgBox ("connection failed!" , "Error!") : End
 End If
DataSource=DBContext.getByName("WordDB")
ConnectToDB=DataSource.GetConnection ("","")
SQLResult=createUnoService("com.sun.star.sdb.RowSet")
SQLQuery="SELECT ""ID"", ""Word"" FROM ""wordlist"""
SQLResult.activeConnection=ConnectToDB
SQLResult.Command=SQLQuery
SQLResult.execute
SQLResult.Last
Rows=SQLResult.getRow
Do
 RandomID=Int(Rnd()*Rows)
 SQLResult.Absolute(RandomID)
 RndWord=SQLResult.getString(2)
Loop While isEmpty(RndWord)
WordLength=(Len(RndWord))
i=1
NewWord=""
While i
If you don't feel like entering the entire macro by hand, grab the Writer's Tools extension, written by yours truly, which contains the game.