Reading Information from a Database

There are several functions for reading records from a DP4 database. All of them are very similar in operation.

rec_fetch() and dp4_rec_fetch() read records one at a time directly from the DP4 database interface.
blk_fetch() and dp4_blk_fetch() read multiple records from the database. If a program is processing multiple records this is always more efficient, and over a network it is very efficient. Unfortunately these functions are rather inconvenient for the average program to use directly.
bf_fetch() and dp4_bf_fetch() read records one at a time from a buffer that the DP4 interface fills as necessary by calling blk_fetch() as necessary. bf_fetch() is almost as easy to use as rec_fetch(), but performs almost as fast as blk_fetch(). It is usually the best function to use when you are going to be reading many consecutive records from a table. There are examples on this page,or you can find out more about bf_fetch() in Buffered Fetch Functions.

With all these functions you specify the record to be fetched by doing the following:

Assign the value of any key fields that have known values.
Call the appropriate fetch function specifying an index table and a number of keys to match and the index to be used. In C if, as will often be the case, you are searching using a table's primary index you can use the function rec_fetch_main() instead of rec_fetch(), to avoiding having to specify all the rec_fetch() parameters explicitly. In C++ you will often be able to rely on default parameter values to save you from having to pass all parameters explicitly.

The remainder of this section illustrates various aspects of using the fetch() functions.

Fetching a Record with a specific key
Reading records sequentially
Multi-Part Keys - Fetching a Group of Related Records

Fetching a record with a Specific Key

To fetch the customer record whose customer_number is 10000 you could use code like the following:

customer.customer_number = 10000;
if (rec_fetch_main(EQUAL,&customer.l) > 0)
  printf("Hurray\n");
else
  printf("Sorry - I can't find customer 10000\n");

If a program will be frequently looking up records with a specific key, and they are not going to change while the program is running it may be beneficial to use a cache to reduce the number of database accesses. DP4 C programs can take advantage of the cache_() family of functions to do this.

On the other hand if you know the customer name is SMITH you might write code like this:

WIPE(customer.name); /* same as memset(customer.name,' ',sizeof(customer.name)); */
memcpy(customer.name,"SMITH",5);
if (rec_fetch(EQUAL,&customer.l,_INDEX_CUST_NAME,0) > 0)
  printf("Hurray\n");
else
  printf("Sorry - I can't find customer SMITH\n");

Note that you need to know the index that corresponds to the field whose value you know when fetching a record with a known key value. If you assign to the wrong fields for the index you specify in the call to a fetch function, you won't get any warnings or errors - you will just get the "wrong" record returned.

Using rec_fetch() you can very easily find the record with the key "nearest" to a given value:

customer.customer_number = 10000;
if (rec_fetch_main(EQUAL|NEXT,&customer.l) > 0)
  printf("Customer %f is the first customer with number >=10000",customer.number);
else
  printf("Sorry - I can't find any customers with customer number >=10000\n");

NEXT on its own would find the first customer with a custom number > 10000 rather than >=. This type of fetch is very difficult using SQL, especially if there are several key fields.

Reading Records Sequentially

To fetch all the customers in customer number sequence you could use a loop like the following:

  if (rec_fetch_main(FIRST,&customer.l))
    do
      printf("Found customer %f",customer.number);
  while (rec_fetch_main(NEXT,&customer.l);

To improve performance you could replace this with calls to bf_fetch() as follows:

{
  BF_HANDLE h = bf_open(&customer.l,BF_SEQUENTIAL,0,0);
  if (bf_fetch(h,FIRST,&customer.l,0,0))
    do
      printf("Found customer %f",customer.number);
    while (bf_fetch(h,NEXT,&customer.l,0,0);
  bf_close(h);
}

If we want to fetch in name sequence all we have to do is change the fourth parameter in the calls to bf_fetch() from 0 to _INDEX_CUST_NAME.

We could fetch the records in reverse sequence by starting from the LAST record and then calling bf_fetch(PREV) repeatedly. However this is usually a little bit slower than fetching in the forward direction.

You don't have to start with the FIRST or LAST record - you can start off a sequential scan through a table using an EQUAL or an EQUAL|NEXT search. On a primary index you can also start with a NEXT or a PREV search. However this is not safe for non-unique secondary indexes - in rare cases this can cause one record to be skipped - and you should always include one of the "terminal" flags FIRST or EQUAL or NEXT in the flags.

Multi-part keys

Let's change the last example to read the ORDER_LINE table instead of the customer table.

{
  BF_HANDLE h = bf_open(&order_line.l,BF_SEQUENTIAL,0,0);
  if (bf_fetch(h,FIRST,&order_line.l,0,0))
    do
      printf("Found order_line %ld %d",order_line.order_number,order_line.line);
    while (bf_fetch(h,NEXT,&order_line.l,0,0);
  bf_close(h);
}

This reads all the order lines for all the orders. If we only want to read the order lines for order 1000 we use the DEPTH flag and specify a fixed order number as follows:

{
  BF_HANDLE h = bf_open(&order_line.l,BF_SEQUENTIAL,0,0);
  order_line.order_number = 1000;
  if (bf_fetch(h,FIRST+DEPTH,&order_line.l,0,0,1))
    do
      printf("Found order_line %ld %d",order_line.order_number,order_line.line);
    while (bf_fetch(h,NEXT+DEPTH,&order_line.l,0,0,1);
  bf_close(h);
}

Note the additional final parameter passed to bf_fetch() to specify the DEPTH value. This tells the DP4 database manager that the first part of the two part key of the order line table is fixed at the current value.

This is one time when the dp4_ versions of the DP4 API are somewhat different from the traditional C versions - in the C version the final parameter is optional so you have to specify the DEPTH flag to indicate that it is present. In the dp4_ version of the fetch functions the final parameter is always present (the C++ wrappers will default it to 0 for you), so you don't have to specify the DEPTH flag and the interface will add it to the flag automatically when every you pass an explicit non zero value for the final parameter.

What if we want to fetch the first order line for each order? Can we use a similar bit of code?
The answer is NO because if we specify a depth of 2 matching is done on both order_number and line.

There are two possible solutions - we can define another index where the order of the keys is reversed, or, probably better, we can change the key value after each fetch() to miss out the next records. In this case we mustn't use bf_fetch() because we are no longer reading consecutive records. So we might end with code a bit like this:

{
#define MAX_LINE 32767
  if (rec_fetch(FIRST,&order_line.l,0,0) > 0)
    do
    {
      printf("Found order_line %ld %d",order_line.order_number,order_line.line);
      order_line.line = MAX_LINE;
    }
    while (rec_fetch(NEXT,&order_line.l,0,0);
}

With the native DP4 API you cannot fetch() a record using an arbitrary group of fields as you can in SQL using a WHERE clause. You can only specify a complete index or a leading subset of the keys of an index. The upside to this seeming restriction is that you can be very sure that any rec_fetch() you do call will complete very quickly, and you are free to define as many index tables as are necessary for your programs.