Objective-C and C API Tutorial

OpenBase offers a C and Objective-C API for communicating with databases and retrieving results. This section describes the API calls that are common to both versions of the communications APIs.

Connecting to a Database Server

The first thing that your application should do is initialize its connection with the database server. We recommend that you do this after your application has initialized. You may create as many OpenBase objects as you want and connect to several different databases at the same time.

The following code shows one way to connect to the OpenBase server. For this example, we have stripped out some of the error handling to make the necessities clear.

#import <OpenBaseAPI/OpenBase.h>
...
- (OpenBase *)connectDatabase
{
    int returnCode;
    OpenBase *database;
    database = [ [ OpenBase alloc] init] ;
    if (![database connectToDatabase:"Movies"
            onHost:"127.0.0.1"
            login:"admin"
            password:"" return:&returnCode]) {
        printf("failed with error code %d\n", returnCode);
        exit (0) ;
    }
    return database;
}

In the above example code, the call to connectToDatabase: shows how to connect to the OpenBase server. The code sample above specifies a “127.0.0.1” for the target host indicating that you want the Movies database on the local computer.

If your database has a static port assigned to it you can also specify “#port” for the database name in cases where you have an application that needs to connect quickly. Specifying the port allows the client to go directly to the database without looking up the database port.

Constructing SQL

Once you have initialized a connection with OpenBase, you are ready to interact with it using SQL. Constructing an SQL statement can be done all at once or in little pieces using the makeCommand: method. Here is an example where the entire SQL statement is defined with a single makeCommand: call.

[ database makeCommand: “select TITLE from MOVIE where REVENUE > 300000"] ;

While this is correct, you may find it more convenient to specify the statement in smaller pieces.

[database makeCommand: “select TITLE “] ; 
    [database makeCommand: “from MOVIE “] ;
    [database makeCommand: “where REVENUE > 300000"];

When constructing queries in pieces, one common mistake is forgetting the spaces at the beginning or end of each segment. For instance, if you send the strings “select TITLE” and “from MOVIE”, the result is “select TITLEfrom MOVIE”. As you can see, there is a space missing that will cause an error. Therefore, be very careful to include the appropriate spaces when constructing SQL statements.

SQL Statement Execution

Once you are done constructing an SQL statement, you can send it to the database by calling the executeCommand method. Here is an example of how the executeCommand method is invoked:

[database executeCommand] ;

When you execute a statement, the content of the command buffer is sent to the OpenBase server. The executeCommand method returns TRUE if the query is executed, otherwise FALSE is returned.

Using Row IDs

While it is not necessary, we recommend that application designers use the _rowid column to identify records. _rowid holds automatically generated unique values for identifying records in each table.

If you don't specify _rowid’s in your insert statements, they will be automatically generated for you by the server. However, if you want to insert a record that you want an immediate handle on, you need to ask the database for the next available _rowid for use with your insert statement. In this section, we show what must be done to generate and use unique _ rowid’s.

Before you insert a record into the database, you should get a new unique identification number from the OpenBase object. This new unique number (returned as a string) should be used when inserting the record into the database. The following code segment gets a new record identifier for the contact table.

char uniqueKey[20] ;
    strcpy(uniqueKey, [database uniqueRowIdForTable: “MOVIE”]);

Once you have this unique identification value, you can use it to construct an SQL statement for inserting a record into the database. Here is an example of how this is done:

[database makeCommand: “insert into MOVIE (_rowid, TITLE, REVENUE) values “] ;
    sprintf (buff, “(' %s', ' %s', %f’) “, uniquekey, movieTitle, revenue) ;
    [database makeCommand:buff] ;
    [database executeCommand] ;

The above code segment demonstrates how to insert a set of values into a database table.

Linking BLOBs To Your Records

OpenBase supports text and object data types for storing BLOBs (Binary Large OBjects) to your database. While other APIs, such as Espresso and Java JDBC handle the interaction of these data types for you, the low-level C API requires you to handle these types yourself through API calls.

BLOBs are stored inside the database but outside the where the table data is stored. You associate a record with a BLOB using a BLOB key. The key is stored in the table data and that allows you to fetch the BLOB when you retrieve the record values. The BLOB fetching happens separately.

BLOB keys are 8 characters long and look like this: “aacsa100”. When you retrieve one of these strings from an object or text column, use the retrieveBinary: method to retrieve the data associated with the blob key. Here is an example:

blobData = [connection retrieveBinary:blobID];

This method returns an NSData object containing the BLOB data. Text data works the same way except that the Text type expects text to be inserted and retrieved in the database’s native encoding. This allows text to always show up correctly on different platforms when the data is always stored in a common format.

Here is an example of storing a blob in the database:

strcpy(key, [connection insertBinary:[dataObject bytes] size: [dataObject length]]);

The key should then be used as the value to insert into the record.

One important note about working with BLOBs is that BLOBs are automatically removed when records receive new BLOB keys. So there is no need to remove BLOBs.

Retrieving Records

You can use a select statement to retrieve previously saved information. Here is a sample select statement that retrieves all PG rated Movies and sorts them by title:

SELECT _rowid, TITLE FROM MOVIE WHERE RATED='PG' ORDER BY TITLE

This query can be sent to the database by the following Objective-C commands:

[database makeCommand:”SELECT _rowid, TITLE FROM MOVIE WHERE RATED ='PG' ORDER BY TITLE”];
    [database executeCommand] ;

When we get each row of the result, the column values are dumped into variables specified by the programmer. The following code segments demonstrate how to retrieve the results from the SQL request made above. After executing a query, you should call rowsAffected to see if any results have been returned.

if ([database rowsAffected]== 0)
    return NO_RESULT;

If there is a result, you should bind your program variables to the columns of the result. Since there are two columns, we will call the bindString: method twice. This tells the OpenBase interface where to put the information as it retrieves each row of the result.

[database bindString: uniqueKey];
[database bindString: movieTitle];

In your result is of another type, bindString: will make sure that the values are converted to a string. You may also want to use the bindInt:, bindDouble:, bindLong: and bindLongLong: methods for other target values. The interface will always try to convert column data to the target variable's type. Variables must be bound in order, starting with the first and ending with the last column.

Now that the columns have been bound to the program variables, you will need to retrieve the rows. This is done using the nextRow method, which should be called once for each row in the result. Each time it is called, the program variables bound to the columns will be filled with the column data for the next available row. When nextRow returns FALSE, it means there are no more rows to be retrieved. Here is an example:

while ([ database nextRow] ) {
    // do something with uniqueKey and movieTitle printf(“%s,%s”, uniqueKey, movieTitle); 
}

SimpleTool Example

Previously in this chapter we showed some examples using Objective-C. In this section we will discuss a program written using the C API. All Objective-C calls have equivalent C calls which perform the same functions. The Objective-C version of this program is available in the examples directory.

SimpleTool demonstrates interaction with a relational database, without using the tedious programming overhead, common with databases.

Using C or Objective-C is the simplest way to access OpenBase. SimpleTool will retrieve from the database the movies and the revenue from the producing studios. The listing below illustrates the OpenBase API framework. A discussion follows.

#import <Foundation/Foundation.h>
#import <OpenBaseAPI/OpenBase.h>
int main (int argc, const char *argv“);
{
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init] ;
    int returnCode;
    OpenBase *connection = ob_newConnection();

    //variables to hold values 
    char movieTitle[256]; 
    char studioName[256]; 
    long revenue;

    if (!ob_connectToDatabase(connection,"Movie", "*","","",&returnCode)) {
        printf (“%s\ n”,ob_connectErrorMessage(connection));
        return -1;
    } 
    ob_makeCommand(connection, ”select t0.TITLE, t0.REVENUE, t1.NAME from MOVIE t0, STUDIO t1 where t0.STUDIO_ID = t1.STUDIO_ID order by t0.REVENUE DESC”);

    if (!ob_executeCommand(connection)) { 
        printf(“ERROR%s\n”,ob_serverMessage(connection));     
        ob_invalidate(connection);
        return -1;
    }
    ob_bindString(connection, movieTitle);
    ob_bindLong(connection, &revenue);
    ob_bindString(connection, studioName);
    while (ob_nextRow(connection)) {
        printf(“%s made $%ld for %s.\n”,movieTitle, revenue, studioName);
    } 
    ob_invalidate(connection);
    [pool release] ;
    return 0;
}

Discussion: SimpleTool_main.m

Main begins by establishing a connection to the database, if a connection was not made, print the offending message returned from the connection object and exit. Using the ob_connectToDatabase() function, establish a connection to the database with the database name, hostname, logon id, and password.

int returnCode;
    OpenBase *connection = ob_newConnection();
    if (!ob _ connectToDatabase(connection , "Movie", "*", "","", &returnCode)) {
        printf (“%s\ n”, ob_connectErrorMessage(connection));
        return -1;
    }

After a successful connection has been established, the ob_makeCommand() function is used to send SQL statements. The TITLE and REVENUE data columns from the MOVIE table as well as the associated studio NAME from the STUDIO table are retrieved. The SQL statements are now buffered for later execution by the database server.

ob_makeCommand(connection,”select t0.TITLE, t0.REVENUE, t1.NAME from MOVIE t0, STUDIO t1 where t0.STUDIO_ID = t1.STUDIO_ID order by t0.REVENUE DESC”);

The ob_executeCommand() passes the buffered SQL statements to the database server and returns TRUE for successful and FALSE for failed execution.

if (!ob_executeCommand(connection)) { 
        printf("ERROR - %s\n",ob_serverMessage(connection)); ob_invalidate(connection);
        return -1;
    }

The ob_bindString() and ob_bindLong() functions, bind the resulting data columns from the database, to the receiving program variables. SimpleTool binds the variables movieTitle, revenue and studioName respective to the order of the initial SELECT statement.

ob_bindString(connection, movieTitle);
ob_bindLong(connection, &revenue);
ob_bindString(connection, studioName);

ob_nextRow() increments through the result rows and retrieves the data. FALSE is returned when all data is processed.

while (ob_nextRow(connection)) {
    printf(“%s made $%ld for %s.\n”,movieTitle, revenue,studioName);
}

Main ends with a call to terminate the connection to the database server.

ob_invalidate(connection);

Application Notification

Application Notification means that your applications can be notified when the database changes. This allows you to build data views or other information displays that will always stay up-to-date with the information in the database.

Registering for Notification

Before you can receive notifications, you will need to prepare an object to respond to the appropriate delegate method.

To start notification for object self, do the following:

[OpenBaseObj startNotificationFor:self];

To remove self from the notification list, do the following:
[OpenBaseObj removeNotificationFor:self];

Executing these commands will register your object to receive notification of database changes. Finally, you will need to implement the notifyChange:database:intable:vid:field:value: delegate method. This method is defined as follows:
notifyChange:database:intable:vid:field:value:

- notifyChange:(const char *)action database:(const char *)databaseName intable:(const char *)tableName vid:(const char *)rowid
field:(const char *)fieldName
value:(const char *)aValue

This delegate method is called to notify your application when a change is made to the database. The field and value parameters will always be an empty string, but they will be used in a future version of OpenBase. All objects that set themselves up as the delegate of the notifier object will be notified of database changes.

The action parameter is one of the following values: (lock), (unlock), (update), (insert) or (delete). (lock) and (unlock) indicate when a record has been marked or unmarked by another user.

When your object is notified of a change, you can select the new values from the database. Since others will be asking for the same piece of information, OpenBase buffers the information for faster access.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License