View this PageEdit this PageAttachments to this PageHistory of this PageHomeRecent ChangesSearch the SwikiHelp Guide
Hotspots: Admin Pages | Turn-in Site |
Current Links: Cases Final Project Summer 2007

How to Better Use External Databases with Your App - Alex Milstead

A Better Way to Organize and Manage an External Database for Application Data Persistence in SmallTalk/VisualWorks.

First of all, if you're reading this page, that means you're interested in using a physical Database (not File I/O) in order to manage your 2340 application information persistence (henceforth referred to as simply "app info").

If this is truly the case, there are a few things you should have at your disposal before you give this a go:
  1. You should either have your own external server or have access to a server which allows you to connect to a database stored there.
  2. You should pick your favorite database early on. If you don't have one, I'd recommend MySQL to start, it has some great GUI tools for inexperienced users.
  3. YOU MUST KNOW (or at least be able to manipulate) SQL!!

Another student gave a similar write-up about databases and using VisualWorks to connect to a PostgreSQL database Cincom has prepared for users, check out Adding database to your project by Seung-Hun Park for more details.

If you have these things taken care of, and you've got some kind of database server listening for connections, then the next step is to write some SmallTalk that will enable you to access your database and execute commands.


Unfortunately many groups (mine included, at first) make the mistake of opening (and sometime not closing) a database connection every time they want to make a database call. This is very bad, as the connections will eventually aggregate to the point of total saturation on the database side. VisualWorks doesn't handle DB connections very well, and so even if you're application isn't exactly running, but the IDE is, any of the connections you have previously created will idly persist.

Opening a connection every time you need to execute a query is simply bad style. Even if you're closing the connection immediately after a call is made – this (in and of itself) can be taxing on the application and your database.

The best way to setup your external database connection is to use a static class (singleton [whoo, extra credit. =D]) to handle connecting and distributing that connection to different classes that need it for different purposes. In order to make this happen, the first thing needed is to understand shared (static) variables in VisualWorks/SmallTalk. If you don't already understand how to use them, check out this link: It will show you what you need to know in order to work with static variables in VisualWorks.

Next we'll need to create the class that will dish out the connection to any other classes that might need it. I called my class "ConnectionHandler".

In the class you'll need three important things:
  1. A "Connection" static (shared) variable.
  2. An initializer method to actually start the connection.
  3. A "GetConnection" static (class) method.

The "Connection" variable should be defined and initialized as the type of connection you need for your chosen database. In my group, we used PostgreSQL, so our connection variable looked like this:
HospitalAdministration.Dao.ConnectionHandler defineSharedVariable: #Connection
	private: false
	constant: true
	category: 'connection'
	initializer: 'PostgreSQLEXDIConnection new.'

Next (as discussed in the SmallTalk Daily on Shared Variables), you'll need to make sure you reload the code package (the package you're developing in) in order for your initializer to fire properly (I know, it's silly. But what isn't with VisualWorks, eh?).

Now on to the initializer method. It will need to be a class (static) method, as we aren't dealing with object instances in our ConnectionHandler class. Our initializer method looked like this:

		username: 'username';
		password: 'password';
     		environment: 'serverName_databaseName';

At the very beginning of your application, you'll want to fire this message to start-up your connection. In our project, this "init" method was called when our initial login window popped up.

Now we need to look at being able to grab the connection from another class. Chances are, you'll want different database-related classes for different types of domain objects. For instance, we had "User" and "Patient" objects in our project, so likewise we had a "UserDAO" and "PatientDAO" classes (DAO = Database Access Object). In each of these DAO classes, you'll want to setup an instance variable to hold your connection, and initialize that variable in the "initialize" method by retrieving the connection from our ConnectionHandler class. If you aren't sure on how to get make static calls in VW/SmallTalk, it's pretty easy:

(This is an example "initialize" method from one of our DAO classes)
	" Tell the connection handler to give us a reference of its initialized connection "
	connection := ConnectionHandler GetConnection.

Now this is great because smalltalk hands out a reference to the connection to any object that asks for it in the application. That means that everyone can reference the same connection without having to create their own! One connection for the entire app – it'll only be opened and closed once. From this point on, you can make as many database calls as you want from as many classes possible and you won't have to worry about any connection issues or VisualWorks just being plain dumb (NOTE: This does not ensure that any SQL you execute will actually work. You gotta figure that out for yourself).

Making Queries and SQL Transactions

Last but not least is actually making a database call.
There are three primary components to making an external database call in VisualWorks/SmallTalk:
  1. The connection
  2. The query
  3. A resultSet (if a resultset was returned).

Starting a database call requires you to get a session for the DB connection. To do this, you'll want to execute the following code:

connection getSession.

You can fill up a temporary variable with this if you'd like, or (like I prefer), simply grab the session right before you execute the call.

Next is making executing the actual call. There are two basic types of database calls – queries (select statements) and non-retrieval transactions (update, insert and delete statements). For basic queries you'll receive a result set back from the database which you'll use to populate domain objects.

There are two ways to format any given query, you can use string formatting (if you're a C/C++ fan like myself) or you can use a special message type for the connection called "bindInput". I'll discuss both ways below.

Using String formatting:

        | params query |
	params := Array new.
	params at: 1 put: someData.

	query := StringParameterSubstitution default.
	query originalString: 'SELECT * FROM table WHERE id = <1p>'.
	query args: params.

        "This line is what makes sure the input you want to format actually makes it into the string"
	query := query expandedText.

As you may have noticed, there's a "<1p>" in the string there, this is the way to reference incoming array indexes in smalltalk string formatting. Likewise if you had more than one element in an incoming array of input, you would have "<2p>", "<3p>", etc. for each of the given indexes.

Using bindInput message:

	| session params |

	params := Array new: 4.
	params at: 1 put: someData.

	(session := connection getSession)
      	        prepare: 'SELECT * FROM table WHERE id = :1';
	 	bindInput: params;

Here you'll notice that the process is really similar, except we aren't using any "expandedText" messages, we're simply passing the input to the query using the "prepare" and the subsequent "bindInput" messages. Another thing of note is that we used ":1" instead of "<1p>". You can also use "?" for each individual input you may want to throw into the query, but it's much spottier than both ":X" and string formatting's brackets. Just like the "<1p>", "<2p>", etc. you'll use ":1", ":2", etc. if you need multiple input for a single query.

NOTE: The ';' characters attached to the end of the lines are important if you like visual code structure. The call can also be made as:
session := connection getSession.
session prepare: 'some string'.
session bindInput: someInput.
session execute.

but that makes code much more cumbersome and ultimately more unreadable. The ';' characters will make sure to tell the object that the following message should be cascaded to the session object as separate calls.

Also, if you're using the string formatting style, you'll only need to make sure that the "prepare" and "execute" calls are made for the session object. Just make sure you pass in the "query" variable to the "prepare" method before executing.

Lastly, we need to make sure we can actually access the result set from a given executed query.

It's pretty simple:

        | answer row obj obj_list |
        "Typically we get more than one row back from a result set, so we'll need to keep a list of objects."
        obj_list := OrderedCollection new.

        "This gets the result set from the previous session where we made a DB call."
        answer := session answer.

        "Let's also assume that the query we executed returns a result set of x, y pairs."
	[answer atEnd] 
		whileFalse: [| row | 
                        "Get the next row from the result set"
			row := answer next.

                       "We'll assume here that obj has the methods 'setX: aNumber' and 'setY: aNumber'."
                        obj := SomeObject new.
			obj setX: (row at: 1).
			obj setY: (row at: 2).
			obj_list add: obj.

Notice we're assuming here that the result set is returning multiple rows. If you know for a fact you'll only get a single row back from the result set, you would execute the exact same thing as above, but without the "[answer atEnd] whileFalse:" loop.

Now if we put it all together, we get something like:

	| session params object |
	object_list := OrderedCollection new.
	params := Array new: 2.
	params at: 1 put: someNumber.
	params at: 2 put: someOtherNumber.
	(session := connection getSession)
      	        prepare: 'SELECT x, y FROM someTable WHERE someColumn = :1 AND someOtherColumn = :2';
		bindInput: params;

        answer := (session answer).
        [answer atEnd] 
		whileFalse: [| row | 
			row := answer next.
			object := SomeObject new.
                        object setX: (row at: 1).
			object setY: (row at: 2).
	   		object_list add: object.

That's it! That's all you need to know to start using databases in VisualWorks/SmallTalk. I hope this guide helps you out. I know I sure could have used it this semester.

Links to this Page