This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Programming

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

By Dmitri Popov on April 02, 2008 (9:00:00 AM)

Share    Print    Comments   

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<=WordLength NewWord=NewWord+Right(Left(RndWord, i), WordLenght-1)&Space(1) i=i+1 Wend FinalWord=Trim(NewWord)
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<=LetterCount

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<=WordLength NewWord=NewWord+Right(Left(RndWord, i), WordLenght-1)&Space(1) i=i+1 Wend FinalWord=Trim(NewWord) LetterArray=Split(FinalWord, "-") LetterCount=UBound(LetterArray()) RoundCounter=0 While RoundCounter<=LetterCount InputLetter=InputBox("Type a letter", "Letter Game") If InputLetter="" Then : End End If If InputLetter=LetterArray(RoundCounter) Then MsgBox ("Correct! Now you have to guess the next letter of the word.", , "Correct answer!") RoundCounter=RoundCounter+1 Else MsgBox ("Wrong! Try again.", , "Wrong answer!") End If Wend 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 ConnectToDB.close ConnectToDB.dispose() End Sub

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.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, US, German, and Danish computer magazines.

Share    Print    Comments   

Comments

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

Note: Comments are owned by the poster. We are not responsible for their content.

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

Posted by: toronto on April 03, 2008 03:28 AM
I don't something else to cut into my already severe <a href="http://www.precede.ca" target="_blank">time</a> mis-management but thanks for taking the time to write up the <a href="http://www.charmehosting.com" target="_blank">Seo hosting</a> article.
[Modified by: toronto on April 25, 2008 02:39 AM]

#

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

Posted by: Anonymous [ip: 124.109.65.75] on April 03, 2008 09:52 AM
This article is a good example of where MS office has an edge. A 10 year old can make a game with it, and I know this because I did (although dad helped). Office macros are for the intelligent, but not programming types.

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
^ Why is office playing uno? Sun? Why are they involved? Star? astronomy? These were my first thoughts when I tried to understand OOo macros, before I came to understand that OO was made by sun, and was an ugly extension of something called 'Sun StarOffice'. I never found out why the Uno card game is involved (I jest, I'm sure it isn't the same uno) - I started coding in C instead. And Python. Easy languages, that didn't have odd legacy items that needed dropping.

OOo needs an easier extension framework - lots of people have complaints, and in projects like Firefox, Linux (drivers), etc, the answer is modules and extensions. I still haven't heard of an OO extension/macro that can add a useful feature (eg, fill a spreadsheet selection with random numbers). I tried - the macro language was too much for me. Easier to work try with KOffice, which has a sensible (read: python) scripting interface by default.

#

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

Posted by: Anonymous [ip: 66.122.165.196] on April 03, 2008 10:45 PM
This type of interface looks like it could be used to automate instruction and test taking.

#

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

Posted by: Anonymous [ip: 24.203.203.89] on April 06, 2008 04:09 AM
An excellent introduction to the subject.

There seem to be some similarities to asp.net, is there any kind of compatibility with vba?

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya