The previous example is very simple; it requires no connections and only a minimum number of API calls. A more complex example would have included connections, connection options, statements, statement options, comprehensive error handling, transaction control, recordset manipulation, bulk operations, and so forth—in fact, many more things than I could hope to cover in one short chapter. In most cases, you will be most productive when you use one of the object models provided by Visual Basic for standard database access, such as executing SQL statements and retrieving the results—it’s much easier. However, when you need fine-grained control or you need to reach functionality that isn’t exposed by DAO or RDO, the ODBC API is the tool for the job.
Because the ODBC API is designed for C programmers, the best approach can be to use Microsoft Visual C++ to write the actual code and then expose functions to Visual Basic in the form of a DLL. This enables you to take advantage of some features that are really usable only when you’re manipulating memory pointers. For example, when you’re retrieving variable length string data, it’s not possible to know how much memory will be required to store the data until it’s all been retrieved. This leads to the problem of overspecification—allocating much more memory than will actually be required. As an example, imagine you’re retrieving variable length strings from a table that contains descriptive memos for accounting transactions and that these strings can be anywhere from 0 to 4096 characters long. Most of the time, they’ll only be a few characters long; however, the application will have to allocate buffers that are at least 4096 bytes long (Figure 13-4) to accommodate the records that are longer than a few characters.
Figure 13-4 Memory used vs. memory allocated to buffers
Version 3 of ODBC introduces the capability of assigning a single large buffer, with strings stored sequentially within the buffer as they are retrieved. A pointer must be maintained for each record that points to the beginning of the field in the buffer (Figure 13-5). This kind of pointer manipulation is easy in C but much harder in Visual Basic.
Figure 13-5 Pointers to records in a buffer