Objective-C Stored Procedures

This section focuses on building OpenBase stored procedures in Objective-C using the OpenBase Espresso API. Example project code can be found in the OpenBase Developer download which will help you get started in building your own stored procedures in Objective-C or C.

Video: Objective-C Stored Procedures

This section will cover:

• The basic structure and elements of an Objective-C stored procedure project
• How to return a multi-row result set
• How to use Objective-C Espresso to select and insert records
• How to build an Objective-C stored procedure to perform an INSERT INTO SELECT

++What You’ll Learn

Each project has a main initialization method in main.m

int main (int argc, const char * argv[])
{
    NSAutoreleasePool * pool = [[NSAutoreleasePool alloc] init];
    OBStoredProcedure * procedure = [[OBStoredProcedure alloc] init];
    [procedure addStoredProcedure:[[InsertSelect alloc] init]];
    [procedure run];
    [pool release];
    return 0;
}

Each project has a main initialization method in main.m

int main (int argc, const char * argv[])
{
    NSAutoreleasePool * pool = [[NSAutoreleasePool alloc] init];
    OBStoredProcedure * procedure = [[OBStoredProcedure alloc] init];

    [procedure addStoredProcedure:[[InsertSelect alloc] init]];
    [procedure run];

    [pool release];
    return 0;
}

Each stored procedure has a class with these methods:

- (NSString *)procedureName
- (NSString *)procedureType
- (int)numberOfParameters
- (NSString *)usageDescription
- (NSArray *)paramDescription
- (BOOL)requiresDatabaseConnection
- (NSArray *)executeProcedureWithParameters:(NSArray *)params connection:(OpenBase *)connection

procedureName gives the name of the stored procedure. In this example, the procedure name is InsertSelect.

- (NSString *)procedureName
{
    return @"InsertSelect";
}

Each stored procedure has a type definition. procedureType should always return "returnsResult"

- (NSString *)procedureType
{
    return @"returnsResult";
}

numberOfParameters specifies the number of inputs expected into the stored procedure. In the example, we will be receiving three input parameters.

- (int)numberOfParameters
{
    return 3;
}

usageDescription documents how to call the procedure.

- (NSString *)usageDescription
{
return @"call InsertSelect(sourceSelectQuery,targetTable, targetColumnList)";
}

returnColumnNames provides a list of column names in the result. These are evaluated after the stored procedure executes, making it possible to dynamically configure the return column names. These will appear in the column titles of the result if you are using openisql.

- (NSString *)returnColumnNames
{
return @"title1|title2";
}

paramDescription provides more information about the parameters to the procedure. This is sometimes used by other APIs to get more information about what is being passed in.

- (NSArray *)paramDescription
{
    NSMutableArray *paramDescriptions = [NSMutableArray array];
    NSMutableArray *array = NULL;
    int ct = 0;

    // sourceSelectQuery
    array = [NSMutableArray array];
    [array addObject:[self procedureName]];
    [array addObject:@"sourceSelectQuery"]; // name
    [array addObject:[NSString stringWithFormat:@"%d",++ct]];
    [array addObject:@"1"]; // type
    [array addObject:@"char"]; // typeName
    [array addObject:@"Query which defines the source data"];
    [paramDescriptions addObject:array];

    ... (for each parameter)

requiresDatabaseConnection tells whether a database connection is needed by the stored procedure. When it returns NO, the stored procedure does not bother establishing a connection back to the database.

- (BOOL)requiresDatabaseConnection
{
return YES;
}

executeProcedureWithParameters defines the business logic

- (NSString *)procedureName
- (NSString *)procedureType
- (int)numberOfParameters
- (NSString *)returnColumnNames
- (NSString *)usageDescription
- (NSArray *)paramDescription
- (BOOL)requiresDatabaseConnection
- (NSArray *)executeProcedureWithParameters:(NSArray *)params
connection:(OpenBase *)connection

Simple Example

This is a simple example of the stored procedure code with a multi-row result.

- (NSArray *)executeProcedureWithParameters:(NSArray *)params
connection:(OpenBase *)connection
{
    NSMutableArray *array = [NSMutableArray array];
    NSMutableArray *rowarray = NULL;
    NSString *name1 = [params objectAtIndex:0];
    NSString *name2 = [params objectAtIndex:1];

    // row #1
    rowarray = [NSMutableArray array];
    [rowarray addObject:@"Hello World"];
    [rowarray addObject:
    [NSString stringWithFormat:@"Hello %@", name1]];
    [array addObject:rowarray];

    // row #2
    rowarray = [NSMutableArray array];
    [rowarray addObject:@"Hello World"];
    [rowarray addObject:[NSString stringWithFormat:@"Hello %@", name2]];
    [array addObject:rowarray];

    return (NSArray *)array;
}

Multi-row Procedure Results

• Build an array of NSStrings for each row
• Return an array of row arrays
• Define returnColumnNames so that the number of columns
returned matches the columns in your rows

Insert-Select Stored Procedure

This is an example of stored procedure code that uses espresso. It uses parameters 0, 1 and 2 to perform a query using Espresso and return a result.

- (NSArray *)executeProcedureWithParameters:(NSArray *)params
connection:(OpenBase *)connection
{
...
NSString *sourceSelect = [params objectAtIndex:0];
NSString *targetTable = [params objectAtIndex:1];
NSArray *targetColumnList = [[params objectAtIndex:2] componentsSeparatedByString:@","];
NSArray *sourceRecords = [DBEntity recordsForQuery: sourceSelect connection: connection];

DBEntity *targetEntity = [DBEntity entityForTable:targetTable connection: connection];

This is the code segment loops through records and transfers source values to the target table.

for (row = 0; row < [sourceRecords count] ;row++) {
    newRecord = [targetEntity newRecordWithConnection: connection];
    sourceRecord = [sourceRecords objectAtIndex:row];
    for (col = 0; col < [targetColumnList count] ;col++) {
        columnName = [targetColumnList objectAtIndex:col];
        targetColNumber = [newRecord columnForColumnName: columnName];
        .....
        string = [sourceRecord stringForColumn: col];
        [newRecord setString: string forColumn:targetColNumber];

- For each source row, we create a new record in the target table
- Get the source record that we are going to get the values from
- For each target column, we get the column number in preparation for setting the value
- Get the source value
- Set the value in the target
- The saveAllChangesWithConnection: converts all your changes to SQL.

Saving changes using Espresso

If one fails it returns false and invokes a rollback
On the rollback, we also create an error message, which is logged in the message log

[connection beginTransaction];
if ([targetEntity saveAllChangesWithConnection:connection]) {
    [connection commitTransaction];
    [array addObject:@"success"];
} else {
    [connection rollbackTransaction];
    [connection makeCommand: "ALERT ERROR 'insert failed:"];
    [connection makeCommand: [targetTable cString]];
    [connection makeCommand: "' "];
    [connection executeCommand];
    [array addObject:@"insert failed to commit"];
}

Depending on the result, a return code is created. The array variable is returned to the calling process.