Day 026 - Core database access functions and error handling

I'm still grooving on yesterday's realization that I can use the H* data access functions to do a mix of full record retrieval and selected field/column retrieval (via queries). I think that's pretty slick. 

When you think about it, both of those techniques are needed when dealing with SQL databases. Most of the time you really want to avoid bringing back every column in a table for something like a browse because you almost always display just a subset, and it's a waste of bandwidth to bring back columns you don't display. 

At the same time, when you're updating a record most of the time you want all of the data available to the form so you can run any necessary validations. 

As an aside, this is a common problem with object-oriented software development, and it goes back what's often called the object-relational impedance mismatch. The way relational databases store data is often different from the way objects model data, and one way to deal with this is to have an Object-Relational Mapping (ORM) layer in between the two. For instance, an ORM can build up a graph (a set of related objects) to correspond to a set of parent-child records (e.g. an invoice and its line items). 

That's great for forms, not so great for browses since again you typically want to bring back just a subset of the table(s). I've run into this with NHibernate; using the regular ORM approach was just too expensive. But most ORMs (NHibernate included) give you a way to bring back just query results. 

WinDev doesn't have a built-in ORM so if you want to model your data as objects you'll have to roll your own code (Peter Holemans did a WxLive webinar on this subject), but the standard data handling functions are pretty effective and with this ability to bring back either the entire record (via the file) or a subset (via the query) you can get a lot of work done in a hurry and with good efficiency. 

Okay, back to the tutorial.

HReadFirst, HReadNext, HReadLast, HReadPrevious

These functions are used in pairs. HReadFirst and HReadLast set up the starting point and the sort order for stepping through the records. 

You pass in the name of a data file, a view or a query and optionally a sort field. 

Reading through the file/view/query in this way involves two different functions. The read first/last function sets the order and gets the first record, and after that you have to use a read next/previous function. In either case you use the HOut function to determine if the record was found, e.g.

HReadFirst(Customer,Name)
WHILE NOT HOut(Customer)
  // Process the record
  HReadNext(Customer,Name)
END

Interestingly, the read next/previous function also specifies the sort order, so I suppose you could just go traipsing off through the file in whatever order you like at any given moment. 

Having to specify the sort order on each read next/previous seems a bit inelegant, but I see from the docs that all of the parameters are optional, and if not specified the last value used is applied. So I think that means you could just execute HReadNext() in the above example. 

HReadSeek

HReadSeek is a "find records that start with" function. You specify the key and the value. You can also add the hIdentical constant if you just want exact matches. 

For Each

Use the For Each construct to loop through all records in a file, query or view. 

You can also add a condition to the For Each statement, similar to an SQL WHERE clause. 

There are many more data access and data structure query and modification commands not mentioned in the tutorial (or at least this section). 

Database error handling

WinDev provides two modes for handling database errors. 

In automatic mode the application pops up  standard window explaining the error to the user. To my mind at least these aren't always the most informative messages. For instance, here's what you see if you have some missing data:

I can pretty much guarantee that your average user isn't going to have a clue what that message means. 

The Details aren't much better:

The problem is that no VAT was chosen from the drop-down list. 

The tutorial gives an example of using custom code to display errors, but to be honest I wasn't that impressed with the implementation. 

The only option that actually did anything here was the second one, so the example was a little incomplete. 

But keep in mind that these error messages are a second line of defense. Your first line as always should be validating input at the user interface level; database error messages should display only if the UI validation somehow fails. 

Tomorrow: database logging.