Objective-C Espresso API

The Espresso API offers a superset of objects which can be combined with the OpenBase API to offer a higher level of access which insulates program logic from SQL. The Espresso API layer takes care of all the SQL, record locking, data encoding and all database access. It is an abstraction layer. At the same time, the Espresso API does not prevent you from writing your own SQL.

videoicon3.png Video: Objective-C Espresso API

Features Include:

- A BLOB/object faulting mechanism that automatically fetches BLOB data when requested. This improves performance when requesting large data sets which include BLOB/object data.

- A relationship faulting mechanism allowing users to ask for records in relating tables. The API layer takes care of the relationships.

- Easy access to schema information.

- Client-side caching of changes.

- Optimistic and Pessimistic locking seamlessly supported.

- Fully compatible with all transaction modes.


It uses the WOMovies database so that must be started before you begin. You need to also include the
OpenBaseAPI. framework


in your project.


There are two new public classes which have been added to the OpenBaseAPI.framework. They are DBEntity and DBRecord. The DBEntity class gives you access to the database schema and relationships. Instances of the DBEntity class offer schema definitions and management of individual tables. Instances of DBRecord allow you to interact with rows of data.

Before you begin, you need to include the the following headers in your project:

#import <OpenBaseAPI/OpenBase.h>
#import <OpenBaseAPI/DBEntity.h>
#import <OpenBaseAPI/DBRecord.h>

You need to also establish a connection to the database using the standard OpenBase API. Here is an example:

#define DATABASE_NAME "WOMovies"
#define DATABASE_HOST ""
#define DATABASE_LOGIN "admin"
connection = [[OpenBase alloc] init];
if (![connection connectToDatabase:DATABASE_NAME onHost:DATABASE_HOST
login:DATABASE_LOGIN password:DATABASE_PASSWORD return:&returnCode]) {
printf("%s\n", [[connection connectErrorMessage] cString]); return NULL;

Schema Information

Here is some example code of how to get information about the schema using the WOMovies database. For those who know Objective-C this should be fairly easy to see how this works.

// list the tables in the database NSArray of names printf ("tables = %s\n", [ [ [ DBEntity tableNamesWithConnection:connection] description] cString]);
// get the movie entity
testEntity = [DBEntity entityForTable:@"MOVIE" connection: connection] ;
// print the columns
printf("columns for MOVIE = %s\n", [[[testEntity columns] description] cString]);
// print the valid relationships
printf("relationships for MOVIE = %s\n", [[[testEntity relationshipNamesUsingConnection:connection] description] cString] ) ;

Here is what each of these routines actually returns:

[DBEntity tableNamesWithConnection:connection] returns an NSArray of table names.

[testEntity columns] returns an NSArray of column names.

[testEntity relationshipNamesUsingConnection:connection] returns an NSArray of relationship names.

Fetching Records

What most folks want to do is fetch a bunch of records and do something useful with them. There are several "selectRecordsWith:" methods which offer a variety of options to choose from. The one below selects all records where the TITLE column starts with the letter "L". Results are ordered by TITLE. A MaxReturned value of 0 means that all results are returned. Specifying a number greater than 0 would limit the number returned. "selectRecordsWith:"returns an NSArray of DBRecord objects.

// find all the movie titles
records = [testEntity selectRecordsWith:@"TITLE like 'L* '" orderBy:@"TITLE" maxReturned:0 usingConnection:connection];

Here is a complete list of similar methods for selecting data:

(NSMutableArray *)selectRecordsWith:(NSString *)whereStatement orderBy:(NSString *)orderBy maxReturned:(int)maxReturned forUpdate:(BOOL)forUpdate usingConnection:(OpenBase *)connection;
(NSMutableArray *)selectRecordsWith:(NSString *)whereStatement orderBy:(NSString *)orderBy maxReturned:(int)maxReturned usingConnection:(OpenBase *)connection;
- (DBRecord *)selectRecord:(NSString *)keyValue usingConnection:(OpenBase *)connection;
(NSMutableArray *)selectAllRecordsUsingConnection:(OpenBase *)connection;

Getting Values

Next we may want to loop through the records and view some of the values. In this example we demonstrate the stringForColumn: method. However, there are a number of accessor methods offered for all the different data types. The API will automatically convert the information to the destination type.

The "columnForColumnName:" method looks up the column name in a dictionary and returns a column number. This number is used identify the column in the DBRecord.

// print the record values
for (ct=0; ct < [ records count] ; ct++) {
    recordObject = [records objectAtIndex:ct]; 
    printf ("MOVIE = %s\ n", [[recordObj stringForColumn:[recordObject columnForColumnName:@"TITLE"]] cString] ) ;

Here is a complete list of the accessor methods used to get information:

// getting a timestamp value

(NSCalendarDate *)dateValueForColumn:(int)i;

// getting string values (includes BLOBS)

(NSString *)stringForColumn:(int)i;

// getting data values (includes BLOBS)

(NSData *)dataForColumn:(int)i;

// getting numerical values

(long long)longlongValueForColumn:(int)i;

// checking for NULL value


Accessing Records Through Relationships

Each MOVIE has several actors. As we loop through each MOVIE record we might also want to select the related records from the MOVIE_ROLES table. To do this we need to call the DBRecord's "recordsForRelationship:" method which returns a list of the related records relative to the specific record we are referencing. In the example below we get the roles and loop through each.

// now for each movie I want to drill down and get related records from the roles table
relatedRecords = [recordObject recordsForRelationship:@"roles" connection:connection];
// NOTE: to get the colums and tablename you simple have to get the entity of one of the records in this Array. See examples
// loop through related records
for (ct2=0; ct2 < [relatedRecords count]; ct2++) {
    relatedRecord = [relatedRecords objectAtIndex:ct2];
    printf ("ROLE NAME = %s\n", [ [ relatedRecord stringForColumn:[relatedRecord columnForColumnName:@"ROLE_NAME"]] cString] ) ;

Updating the Values

The next thing we are going to do in this example is update each movie title by adding the word "foo" at the end. First we create a new string using the original value, and then we call the setString:forColumn: method to set the value.

newvalue = [NSString stringWithFormat:@"%@ foo", [recordObject stringForColumn:[recordObject columnForColumnName:@"TITLE"]]];
[recordObject setString:newvalue forColumn: [recordObject columnForColumnName:@"TITLE"]];

Here is a complete list of methods that can be used to set values: // setting timestamp values

- (void)setDateValue:(NSCalendarDate *)value forColumn:(int)i;

// setting string values (includes BLOBS)

- (void)setString:(NSString *)value forColumn:(int)i;

// setting data values (includes BLOBS)

- (void)setData:(NSData *)value forColumn:(int)i;

// setting numerical values

- (void)setBooleanValue:(BOOL)value forColumn:(int)i;
(void)setIntValue:(int)value forColumn:(int)i;
(void)setLongValue:(long)value forColumn:(int)i;
(void)setDoubleValue:(double)value forColumn:(int)i;
(void)setLongLongValue:(long long)value forColumn:(int)i;

Inserting New Rows

(DBRecord *)newRecordWithConnection:(OpenBase *)connection;

Inserting new rows are accomplished through the DBEntity object using the "newRecordWithConnection:" method. Creating the record will automatically create a new primary key. However, the record will not physically be inserted into the table until you save changes.

newRecord = [testEntity newRecordWithConnection:connection]; 
[newRecord setString:@"My Family Movie" forColumn: [newRecord columnForColumnName:@"TITLE"]];

Committing the Changes to the Database

The final step is to commit the changes. Since this new API is an extension of our existing API, the software developer has a lot of control over how this happens. For instance, if you would like to put everything inside a transaction (as we did below) you can do that. You can also validate business rules or perform other operations using the low-level API as part of the same transaction.

You need to call saveAllChangesWithConnection: for each entity that has changes. You can also optionally tell individual records to save.

[connection beginTransaction];
[testEntity saveAllChangesWithConnection:connection]; // put code here to validate business rules
[ connection commitTransaction] ;

Using Editing Contexts

An editing context is a container which keeps track of all the changes that are made across many tables and allows you to commit those changes all at once. The DBEntity class also keeps track of the changes, as demonstrated by the testEntity above. However, there may be cases where you want to keep track of changes made beyond a single table and execute those changes in the order they were made by the application.

Here is how you initialize a global editing context and get a handle on it.

contextObject = [DBEntity globalEntityCache];

After initializing the editing context you can perform changes to the records. When you are finished you can save the changes to the database as follows:

If you are using a multi-threaded application it is important to keep track of the editing contexts for each thread and set the editing context before each change using semaphores to keep the context from being reset by a different thread.

[connection beginTransaction];
    [contextObject saveAllChangesWithConnection:connection]; 
[connection commitTransaction] ;

[DBEntity setGlobalEntityCache:NULL]; 
contextObject = [DBEntity globalEntityCache];

If you are using a multi-threaded application it is important to keep track of the editing contexts for each thread and set the editing context before each change using semaphores to keep the context from being reset by a different thread.

Here is how you create a new entity cache.

[DBEntity setGlobalEntityCache:NULL]; contextObject = [DBEntity globalEntityCache];

This is how you set the global entity cache once it is created.

[DBEntity setGlobalEntityCache:contextObject];
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License