The link between databases and developers

Earlier this year I talked about stored procedures.

These are encapsulated blocks of code, frequently queries written in SQL, typically written by a database designer (DBD) and stored on a database server.

Application developers can then write applications that call these stored procedures.

The demarcation of developer roles described above neatly explains why we have stored procedures in our armoury.

The DBD knows about databases and how to query them, and the app developer knows how to write applications, but they needn’t necessarily share any common ground.

It is, of course, possible to develop a skill set that crosses these boundaries; people do, and you do see job descriptions that call for both, but many people specialise in one or the other.

Stored procedures provide a mechanism for database and app developers to communicate.

To illustrate this with a real-world example, let’s look at a stored procedure I’ve been working on.

The requirement was for a simple user interface that lets users type a year into a box, click a button and have returned to them the data pertaining to that year from a database.

Starting at the database end, first the DBD builds a straightforward SQL query to search for a sample date and return the necessary data.

Once it works as it should, the sample date is replaced by a variable called, say, ‘@year’ which holds a string of four characters.

Using a variable means that a parameter can be passed to the stored procedure from an application.

The query is then saved as a stored procedure and given a name, in this case ‘GetLocationsForYear’.

In order to start work on the application, the app developer needs to know only the name of the stored procedure and what sort of data it is expecting: here it’s a four-digit string equating to a year.

Code can then be written using whatever naming conventions the app developer deems appropriate, like this:

GetLocationsForYear(UserDate)

where ‘UserDate’ is a variable to hold the parameter typed in by the user.

The line of code above calls the stored procedure and sends it the parameter entered by the user, for example, ‘2006’.

This is used in place of the ‘@year’ variable in the query and the data for 2006 is returned to the user interface.

This process keeps the necessary communication between developers to a minimum of the stored procedure’s name and the data type expected by its variable or variables (a stored procedure can handle multiple variables).

These are the only constraints on code the app developer can build.

If you’re an Access developer moving into the SQL Server arena, you already have the database and SQL skills necessary to write stored procedures for use within an enterprise.

Large organisations usually employ application developers: you simply hand over the completed stored procedure and associated information as described above, and the job’s done.

Lookup hiccough
Recently I was asked to unravel an Access 2003 problem that centred around the use of the Lookup data type.

I wrote about the issue a while ago here but haven’t investigated it in Access 2007.

I did so and, I am sad to report, the situation has not improved. I feel it’s worth revisiting as the problem is still out there causing grief to the many users of Access, whether they work in large organisations or are home-based enthusiasts.

Imagine the scene: you have two tables, Colour containing data about the colour we use and Product for products that are painted.

Each product can only have one colour applied, so there is a one-to-one join between the two tables.

ColourID is a foreign key in Product and it points to the primary key field in the Colour table, also called ColourID.

Inspecting the Product table shows the foreign key column to be full of numbers not readily interpretable as colours.

Microsoft might ask at this point ‘Why not use the Lookup wizard to render these unfriendly numbers into recognisable words?’

I’ll be telling you why not in a moment but let’s go with the flow for now.

Open the Product table in Design mode and, in the Data Type column, click the popdown list alongside the ColourID field (the foreign key field).

The last item on the list is ‘Lookup Wizard… ‘. In its first step, select ‘I want the lookup column to look up the values in a table or query’, that is, you want it to display the colour names from the Colour table.

You select the table and the column in the next steps, add a sort order if required and then you’re shown the values you’ll see.

They look fine, and are much better than those old numbers. In the last step, type in text for use as the column label.
Inspect the table in Datasheet view and there are lovely colours displayed in the ColourID column.