SQLConnect - A library for connecting Objective-C/Swift applications to Microsoft SQL Server

The complete SQLConnect library can be found here.

The file from which the following method belongs can be found here.

At some point I may or may not open questions for reviewing other aspects of this library, but I intend to keep the questions relatively small. This is the largest method in the entire library and is responsible for all the hard work.

Once the user has successfully connected to the database, they call this method in order to execute a SQL command on the database. This happens in a background thread. The thread waits for response from the server, then parses the results into Objective-C objects.

The structure of the results look like this:

• Results is an array of tables.
• Each table is an array of rows.
• Each row is a dictionary, with the key being the column name and the value being the value in that column.

I'm curious to any ways the code can be more efficient from a speed perspective. In practice, I've found that any query that selects less than about 1000 rows can produce the results without the user really noticing. Once we venture into queries over 1000 rows, the time to finish all this works starts to be noticeable by the user.

Now obviously, a slow query will run slow. But I can run a query that will select 5000 rows in SQL Management Studio that returns relatively quickly, while the same query here takes noticeably longer. I don't have any specific examples (nor do I know of a publicly available SQL Server for you to test against), but my testing has shown that there is definitely more time spent turning the results into usable Objective-C objects than there is time spent executing the query and getting the results.

Here's the code:

- (void)execute:(NSString *)statement {
    void(^cleanUp)(SQLColumn *, SQLColumn *, int numCols) = ^(SQLColumn *pcol, SQLColumn *columns, int numCols) {
        for (pcol = columns; pcol - columns < numCols; ++pcol) {
            free(pcol->dataBuffer);
        }
        free(columns);
    };

    // work on background queue
    [self.workerQueue addOperationWithBlock:^{

        // set execute timeout
        dbsettime(self.executeTimeout);

        // prepare the SQL statement
        dbcmd(_connection, [statement UTF8String]);

        // attempt to execute the statement
        if (dbsqlexec(_connection) == FAIL) {
            NSError *error = [NSError errorWithDomain:kSQL_ExecutionError
                                                 code:SQL_ExecutionError
                                             userInfo:nil];

            [self executionFailure:error];
            return;
        }

        // create an array to contain the result tables
        NSMutableArray *result = [NSMutableArray array];

        SQLColumn *columns;
        SQLColumn *pcol;
        int statusCode;

        // loop through each table
        while ((statusCode = dbresults(_connection)) != NO_MORE_RESULTS) {
            int numCols;
            int row_code;

            // create an array to contain the result rows for this table
            NSMutableArray *table = [NSMutableArray array];

            // get number of columns
            numCols = dbnumcols(_connection);

            // allocate C-style array of COL structs
            columns = calloc(numCols, sizeof(SQLColumn));

            // handle allocation error
            if (columns == NULL) {
                NSError *error = [NSError errorWithDomain:kSQL_ColumnStructFailedToInitialize
                                                     code:SQL_ColumnsStructFailedToInitialize
                                                 userInfo:nil];

                [self executionFailure:error];
                return;
            }

            // bind the column info
            for (pcol = columns; pcol - columns < numCols; ++pcol) {
                // get current column number
                int c = (int)(pcol - columns + 1);

                // get column metadata
                pcol->columnName = dbcolname(_connection, c);
                pcol->dataType = dbcoltype(_connection, c);
                pcol->dataSize = dbcollen(_connection, c);

                // if the column is varchar, we want defined size.
                // otherwise, we want max size when represented as a string
                if (pcol->dataType != SYBCHAR) {
                    pcol->dataSize = dbwillconvert(pcol->dataType, SYBCHAR);
                }

                // allocate memory in the current pcol struct for a buffer
                pcol->dataBuffer = calloc(1, pcol->dataSize + 1);

                // handle allocation error
                if (pcol->dataBuffer == NULL) {
                    NSError *error = [NSError errorWithDomain:kSQL_BufferFailedToAllocate
                                                         code:SQL_BufferFailedToAllocate
                                                     userInfo:nil];
                    [self executionFailure:error];

                    // clean up
                    cleanUp(pcol, columns, numCols);

                    return;
                }

                // bind column name
                statusCode = dbbind(_connection, c, NTBSTRINGBIND, pcol->dataSize + 1, (BYTE*)pcol->dataBuffer);
                if (statusCode == FAIL) {
                    NSError *error = [NSError errorWithDomain:kSQL_ErrorBindingColumnName
                                                         code:SQL_ErrorBindingColumnName
                                                     userInfo:nil];
                    [self executionFailure:error];

                    // clean up
                    cleanUp(pcol, columns, numCols);

                    return;
                }

                // bind column status
                statusCode = dbnullbind(_connection, c, &pcol->columnStatus);
                if (statusCode == FAIL) {
                    NSError *error = [NSError errorWithDomain:kSQL_ErrorBindingColumnStatus
                                                         code:SQL_ErrorBindingColumnStatus
                                                     userInfo:nil];
                    [self executionFailure:error];

                    // clean up
                    cleanUp(pcol, columns, numCols);

                    return;
                }
            }
            // done binding column info

            // loop through each row
            while ((row_code = dbnextrow(_connection)) != NO_MORE_ROWS) {
                // check row type
                switch (row_code) {
                    // regular row
                    case REG_ROW: {
                        // create a dictionary to contain the column names and values
                        NSMutableDictionary *row = [NSMutableDictionary dictionaryWithCapacity:numCols];

                        // loop through each column, creating an entry in row where dictionary[columnName] = columnValue
                        for (pcol = columns; pcol - columns < numCols; ++pcol) {
                            NSString *columnName = [NSString stringWithUTF8String:pcol->columnName];

                            id columnValue;
                            // check if column has NULL value
                            if (pcol->columnStatus == -1) {
                                columnValue = [NSNull null];
                            } else {
                                // TODO: type checking
                                columnValue = [NSString stringWithUTF8String:pcol->dataBuffer];
                            }

                            // insert the value into the dictionary
                            row[columnName] = columnValue;
                        }

                        // add an immutable copy of the row to the table
                        [table addObject:[row copy]];

                        break;
                    }
                    // buffer full
                    case BUF_FULL: {
                        NSError *error = [NSError errorWithDomain:kSQL_BufferFull
                                                             code:SQL_BufferFull
                                                         userInfo:nil];
                        [self executionFailure:error];

                        // clean up
                        cleanUp(pcol, columns, numCols);

                        return;
                    }
                    // error
                    case FAIL: {
                        NSError *error = [NSError errorWithDomain:kSQL_RowReadError
                                                             code:SQL_RowReadError
                                                         userInfo:nil];
                        [self executionFailure:error];

                        // clean up
                        cleanUp(pcol, columns, numCols);

                        return;
                    }
                    // unknown row type
                    default: {
                        [self message:SQLConnectRowIgnoreMessage];
                        break;
                    }
                }
            }

            // clean up
            cleanUp(pcol, columns, numCols);

            // add immutable copy of table to result
            [result addObject:[table copy]];

        }

        // parsing results complete, return immutable copy of results
        [self executionSuccess:result];

    }];
}

As a note, this library is built on top of the FreeTDS API.