SFDataBase
Inherits From: NSObject
Declared In: SFDatabase.h
Class Description
A SFDataBase is an NSObject that provides the application program with the tools for accessing and manipulating the database tables. The SFDataBase object uses SFDataServer for all communication with the database (the SFDataServer is an Objective-C adapter that encapsulates the client library calls to a particular RDBMS server. It is instantiated by the STEPforward server and is distributed to the client application either locally by a copy or remotely by a proxy). For the most part, the SFDataBase methods are simply wrap-arounds for the equivalent methods in SFDataServer object. However, unlike SFDataServer, the SFDataBase objects are not designed for distribution to other applications via Distributed Objects system. The SFDataBase objects remain in the address space of the application that creates them, and serve as bridges between the host application and SFDataServer objects that SFDataBase may access remotely by proxy, or locally by copy.
The main function of the SFDataBase is to shield the host application from the intricacies of transporting data to and from the remote objects, presenting the application with a simple and consistent interface to the database through the SFDataServer object regardless of how the SFDataServer object is accessed - remotely or locally.
Method Types
Initialization | - intForUser:password:appName: |
Termination | - release |
Transaction processing | - beginTransaction - commitTransaction - rollbackTransaction |
Current database | - useDatabase: - currentDatabase |
Retrieving data | - getRowsFor:fromTable:conditions:convertAs:intoContainer - getRowsFor:fromTable:conditions:convertAs: - getNextRowIntoContainer: |
Changing data | - updateRowsFor:inTable:where:convertAs:fromContainer: - addRowFor:inTable:convertAs:fromContainer: - deleteRowFromTable:where: |
Changing metadata | - renameTable:to: - renameColumn:to:inTable: |
Testing conditions | - existsRow:inTable: - existsTable: - countRows:inTable:where: - affectedRowCount |
Locking table | - setExclusiveLockOnTable: |
Generic SQL commands | - execSQL: |
Error handling | - displayErrorMessage - setDisplayErrorMessageEnabled: |
Miscellaneous | - getCurrentDate:time: + restrictedKeyword: + dataFormatFor: + maxVarCharSize + concatenationOperator + moneyPrefix + booleanYes + booleanNo + isNullClause + isNotNullClause + isCaseSensitiveSearchPossible |
Class Methods
setDebugging:
+ setDebugging: (BOOL) flag
Turns the debugging mode on or off. When the debugging mode is activated, all SQL statements formatted by the SFDataBase are printed out before being sent to the database server. The printing is done in the Console window, if the application is launched through the Workspace Manager, or in the Terminal shell if the application is launched from it.
restrictedKeyword
+ (BOOL) restrictedKeyword: (char *) string
Checks the null-terminated string against the list of restricted keywords, and returns YES if a match is found; otherwise returns NO. Use this method to avoid the SQL Server error that occurs when the user attempts to assign a name to the database object that is also a reserved keyword.
dataFormatFor:
+ (NSString *) dataFormatFor: (int) type
Translates the integer format type to its NSString equivalent, using the following translation table:
Type | Format | |
INTEGER | @"INT" | |
SHORT | @"SMALL" | |
TINY | @"TINY" | |
FLOAT | @"FLT8" | |
MONEY | @"MONEY" | |
BINARYOBJECT | @"BLOB" | |
IMAGE | @"BLOB" | |
OBJECT | @"OBJECT" | |
LOGICAL | @"LOGICAL" | |
NOTE | @"TEXT" | |
other | @"STRING" |
This method can be used in constructing the format string of the retrieval and update methods of SFDataBase.
The following group of class methods can be used to get information about some standard database settings that are RDBMS-specific:
maxVarCharSize:
+ (int) maxVarCharSize
Returns the largest possible size of varchar field supported by the current RDBMS.
concatenationOperator
+ (NSString *) concatenationOperator
Returns the operator for concatenation of character strings used by the current RDBMS. The operator is returned as NSString, even if it is only a single ASCII character.
moneyPrefix
+ (NSString *) moneyPrefix
Returns the prefix for money-type values used by the current RDBMS. The operator is returned as NSString, even if it is only a single ASCII character.
booleanYes
+ (NSString *) booleanYes
Returns the value used by the current RDBMS for logical YES. The operator is returned as NSString, even if it is only a single ASCII character.
booleanNo
+ (NSString *) booleanNo
Returns the value used by the current RDBMS for logical NO. The operator is returned as NSString, even if it is only a single ASCII character.
isNullClause
+ (NSString *) isNullClause
Returns the string used by the current RDBMS for positive comparison with NULL value in WHERE clause. The exact syntax of this comparison varies from one RDBMS to another; this method allows the application to dynamically construct the WHERE clause without locking itslef into the specific syntactical constraints of the given database.
isNotNullClause
+ (NSString *) isNotNullClause
Returns the string used by the current RDBMS for negative comparison with NULL value in WHERE clause. The exact syntax of this comparison varies from one RDBMS to another; this method allows the application to dynamically construct the WHERE clause without locking itslef into the specific syntactical constraints of the given database.
isCaseSensitiveSearchPossible
+ (BOOL) isCaseSensitiveSearchPossible
Returns YES if the current RDBMS does search on character-based fields in the case-sensitive manner; NO otherwise.
The following values are returned for the currently supported databases:
Sybase | SQL Server | Informix | OpenBase |
maxVarCharSize | 255 | 255 | 255 | 255 |
concatenationOperator | @"+" | @"+" | @"||" | @"+" |
moneyPrefix | @"$" | @"$" | @"$" | @"$" |
booleanYes | @"1" | @"1" | @"1" | @"1" |
booleanNo | @"0" | @"0" | @"0" | @"0" |
isNullClause | @"is NULL" | @"is NULL" | @"is NULL" | @"= null" |
isNotNullClause | @"!= NULL" | @"!= NULL" | @"is NOT NULL" | @"!= null" |
isCaseSensitiveSearchPossible | YES | YES | YES | NO |
Instance Methods
addRowFor:inTable:convertAs:fromContainer:
- (int) addRowFor: (NSString *) columnNames
inTable: (NSString *) tableName
convertAs: (NSString *) formatString
fromContainer: (id) container
Adds one or more rows to the table. Column names are passed as a NSString in columnNames, with names of the columns separated by commas or blanks (or both); the same rule applies to the formatString.
In addition to sending the request to the SFDataServer, this method also establishes the bind between table columns and program objects that will supply the values for the table rows. The source data are passed to the method via a NSArray of SFDataWrapper objects; each object corresponds to the column in columnNames string.
See description of getRowsFor:... method for the list of valid format codes specified in the formatString.
The following values can be returned by the method:
SUCCEED | Successful completion; the row has been added and the contents of containter have been copied to the columns. |
FAIL | Method was unsuccessful. |
addRowFor:inTable:convertAs:fromVars:
- (int) addRowFor: (NSString *) columnNames
inTable: (NSString *) tableName
convertAs: (NSString *) formatString
fromVars: (void *) var,...
This method is a wrap-around for addRowFor:inTable:convertAs:fromContainer: method; it takes data from program variables rather than from SFDataWrapper objects.
See description of getRowsFor:... method for the list of valid format codes specified in the formatString.
Also, see the note to updateRowsFor:inTable:where:convertAs:fromVars: method for handling of BLOB variables.
The following values can be returned by the method:
SUCCEED | Successful completion; the row has been added and the contents of variables have been copied to the columns. |
FAIL | Method was unsuccessful. |
affectedRowCount
- (int) affectedRowCount
Returns the number of rows affected by the last processed SQL command.
beginTransaction
- (int) beginTransaction
Starts the complex transaction by issuing "BEGIN TRANSACTION" command to the database server. Note that the exact spelling of the command is database-dependent, and is formatted by SFDataServer in accordance with the requirements of the given RDBMS.
The following values can be returned by the method:
SUCCEED | Successful completion; the "BEGIN TRAN" command has been sent to the SQL Server. |
FAIL | Method was unsuccessful. |
commitTransaction
- (int) commitTransaction
Commits transaction started with beginTransaction method.
The following values can be returned by the method:
SUCCEED | Successful completion; the "COMMIT TRAN" command has been sent to the SQL Server. |
FAIL | Method was unsuccessful. |
countRows:inTable:where:
- (int) countRows: (int *) count
inTable: (NSString *) tableName
where: (NSString *) searchCondition
Counts the total number of rows in the table, including those with NULL values. Search condition is any valid SQL WHERE clause, passed as a NSString. If all rows in the table must be counted, nil should be specified for the search condition string.
The following values can be returned by the method:
SUCCEED | Successful completion; the row count has been found and passed to the calling program. |
FAIL | Method was unsuccessful. |
currentDatabase
- (NSString *) currentDatabase
Returns name of currently selected database (the one that was activated with the most recent useDatabase: method.
deleteRowFromTable:where:
- (int) deleteRowFromTable: (NSString *) tableName
where: (NSString *) searchCondition
Deletes one or more rows from the table. Search condition is any valid SQL WHERE clause, passed as a NSString.
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been deleted from the table. |
FAIL | Method was unsuccessful. |
displayErrorMessage
- displayErrorMessage
This method displays an attention panel with the error message generated by database server. This is a one-shot panel; it will not be displayed again until setDisplayErrorMessageEnabled:YES is called.
execSQL:
- (int) execSQL: (NSString *) sqlStatement
This is a "catch-all" method that can be used to send any valid SQL statement to the database server. It should be used for SQL requests that do not require passing data to the program objects (such as "CREATE TABLE", for example). The SQL statement must be passed as a NSString.
The following values can be returned by the method:
SUCCEED | Successful completion; the SQL statement has been executed. |
FAIL | Method was unsuccessful. |
existsRow:inTable:
- (int) existsRow: (NSString *) searchCondition
inTable: (NSString *) tableName
Checks if a row exists in the table. Search condition is any valid SQL WHERE clause, passed as a NSString.
The following values can be returned by the method:
EXISTS | Successful completion; the specified row exists in the table. |
NOT_EXISTS | Successful completion; the specified row does not exist in the table. |
FAIL | Method was unsuccessful. |
existsTable:
- (int) existsTable: (NSString *) tableName
Checks if a table exists in the current database.
The following values can be returned by the method:
EXISTS | Successful completion; the specified table exists in the database. |
NOT_EXISTS | Successful completion; the specified table does not exist in the database. |
FAIL | Method was unsuccessful. |
dealloc
- (void) dealloc
Frees the SFDataBase object, dropping connection with the database server.
getCurrentDate:time:
- (void) getCurrentDate: (NSString **) date
time: (NSString **) time
Requests current date and time from the database server and stores the NSStrings in the buffers pointed to by date and time parameters. The date and time are returned in CCYYMMDD and HHMMSS format respectively. The returned NSStrings are autoreleased.
getNextRowIntoContainer:
- (int) getNextRowIntoContainer: (NSMutableArray **) container
This method works in cooperation with getRowsFor:fromTable:conditions:convertAs: method. It retrieves the next row of the result data, prepared by getRowsFor:... method, and copies the column data into corresponding SFDataWrapper objects, formatting them as specified in the formatString parameter of the getRowsFor:... method. The container, which is an instance of NSMutableArray object, is created by this method and autoreleased; it is filled with objects of SFDataWrapper class (one for each retrieved column).
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been fetched and the columns have been copied to the SFDataWrapper objects. |
NO_MORE_ROWS | There are no more rows to fetch, or the method did not return any rows. |
BUF_FULL | This code is returned if buffering is turned on and reading the next row would cause the buffer to be exceeded. |
FAIL | Method was unsuccessful. |
getRowsFor:fromTable:conditions:convertAs:intoContainer:
- (int) getRowsFor: (NSString *) columnNames
fromTable: (NSString *) tableName
conditions: (NSString *) searchCondition
convertAs: (NSString *) formatString
intoContainer: (NSMutableArray **) container
Sends the query to the database for retrieval of one or more rows from one or more tables, based on the specified search criteria. Column names are passed as a NSString in columnNames, with names of the columns separated by commas or blanks (or both); the same rule applies to the table names, passed in tableName. Search condition is any valid SQL WHERE clause or ORDER BY, passed as a NSString. Note that the keyword "where" must be included in the string, if the WHERE clause is used.
After the query is sent, this method waits for successful completion of the query, and then loops over all returned rows. In every pass of the loop it creates a number of SFDataWrapper objects (one for each column requested in the query), sets their contents and data types according to the values and data types of the columns in the retrieved row, and places all newly created SFDataWrapper objects into an instance of NSMutableArray class that it creates for each retrieved row. The autoreleased NSMutableArray object is then added to the container. The container, which is an instance of NSMutableArray object, is created and autoreleased by this method.
There is no direct correspondence between the data formats used by the DBMS, and those inherent in the Objective-C language. The data fetched from the database table must therefore be converted to the format expected by the receiving SFDataWrapper objects. The following is the list of valid format codes that may be passed in the formatString:
CHAR | Copies a single character from the column into the corresponding SFDataWrapper object. |
STRING | Copies a NSString from the column of char or varchar type into the corresponding SFDataWrapper object. All characters from the column will be copied, including padding blanks at the end of the string (if any), and the null terminator. |
TEXT | Copies a null-terminated string from the column of blob type into the corresponding SFDataWrapper object. |
BLOB | Copies an arbitrary stream of bytes from the column of blob type into the corresponding SFDataWrapper object. |
OBJECT | Copies a contents of the column of blob type into the corresponding SFDataWrapper object and converts it into an object from the Objective-C hierarchy of classes. The contents of the retrieved column must have been created with updateRowsFor:... or addRowFor:... methods from a valid Objective-C object. |
SMALL | Converts the integer value from the column of smallint type and copies it into the corresponding SFDataWrapper object. |
INT | Converts the integer value from the column of int type and copies it into the corresponding SFDataWrapper object. |
FLT8 | Converts the floating-point value from the column of double type and copies it into the corresponding SFDataWrapper object. |
MONEY | Converts the floating-point value from the column of money type and copies it into the corresponding SFDataWrapper object. |
LOGICAL | Converts the boolean value from the column and copies it into the corresponding SFDataWrapper object. The converted value is always 0 or 1. |
The format codes must be passed to the method as a NSString with codes separated by commas or blanks (or both). There must be an exact correspondence in the number and type between the format codes and columns.
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been fetched and the columns have been copied to the DataWrapper objects. |
NO_MORE_ROWS | There are no more rows to fetch, or the method did not return any rows. |
BUF_FULL | This code is returned if buffering is turned on and reading the next row would cause the buffer to be exceeded. |
FAIL | Method was unsuccessful. |
getRowsFor:fromTable:conditions:convertAs:
- (int) getRowsFor: (NSString *) columnNames
fromTable: (NSString *) tableName
conditions: (NSString *) searchCondition
convertAs: (NSString *) formatString
This method is a variation of getRowsFor:fromTable:conditions:convertAs:fromContainer: method. It does not wait for the returned rows after sending the query, but expects the getNextRowIntoContainerId: method to loop over the returned rows. This method provides the calling program with an opportunity to inspect data from the returned rows as they come, taking action in the middle of the retrieval loop, if necessary.
getRowsFor:fromTable:conditions:convertAs:intoVars:
- (int) getRowsFor: (NSString *) columnNames
fromTable: (NSString *) tableName
conditions: (NSString *) searchCondition
convertAs: (NSString *) formatString
intoVars: (void *) var, ...
This method is a wrap-around for getRowsFor:fromTable:conditions:convertAs:intoContainer: method; it places returned values into program variables rather than SFDataWrapper objects. The program variables, being receptacles of data, must be pre-allocated (by declaring them as typed variables or by dynamically allocating them with sufficient capacity) prior to calling this method. The method takes a variable number of these receptacles, one for each column/format pair passed in columnNames and formatString parameters. The data types of program variables must correspond to the formats as in the following chart:
Format | Variable type | |
CHAR | char * (pointer to a one-character buffer) | |
STRING | NSString ** (pointer to a variable of NSString * type) | |
TEXT | NSString ** (pointer to a variable of NSString * type) | |
BLOB | void * (pointer to a memory buffer of sufficient capacity to store the data) | |
OBJECT | id * (pointer to a variable of id type to store a generic object) | |
TINY | unsigned char * (pointer to variable of unsigned char type) | |
SMALL | short * (pointer to variable of short type) | |
INT | int * (pointer to variable of int type) | |
FLT8 | double * (pointer to variable of double type) | |
MONEY | double * (pointer to variable of double type) | |
LOGICAL | BOOL * (pointer to variable of BOOL type) |
This method operates in the way similar to getRowsFor:fromTable:conditions:convertAs:intoContainer: method in that it must be followed by getNextRow method that places the contents of every retrieved row into the target variables. The old contents of the variables is obliterated with every pass through the loop; therefore it must be used by the program (or copied in a safe place) prior to the end of each pass. Objects returned by this method for STRING, TEXT and OBJECT formats are autoreleased.
Note: data of BLOB type are copied into a memory buffer pointed to by the void pointer provided by the calling app; the buffer must be large enough to accomodate the copied data. Because it may be difficult to determine the size of the retrieved BLOB before allocating the buffer, use of this method for retrieval of BLOBs is generally discouraged. Use getRowsFor:fromTable:conditions:convertAs:intoContainer: method instead; the SFDataWrapper objects returned by this method carry both the data buffer and its size for BLOBs.
getNextRow
- (int) getNextRow
This method works in cooperation with getRowsFor:fromTable:conditions:convertAs:intoVars: method. It retrieves the next row of the result data, prepared by getRowsFor:... method, and copies the column data into corresponding target variables, formatting them as specified in the formatString parameter of the getRowsFor:... method.
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been fetched and the columns have been copied to the variables. |
NO_MORE_ROWS | There are no more rows to fetch, or the method did not return any rows. |
BUF_FULL | This code is returned if buffering is turned on and reading the next row would cause the buffer to be exceeded. |
FAIL | Method was unsuccessful. |
initForUser:password:appName:
- initForUser: (NSString *) uid
password: (NSString *) pwd
appName: (NSString *) app
Initializes the newly allocated instance of SFDataBase object. uid and pwd are the database login account and database login password of the user. app is the name of the application that created SFDataBase object (optional, can be nil).
This method is the designated initializer of SFDataBase class.
renameColumn:to:inTable:
- (int) renameColumn: (NSString *) oldName
to: (NSString *) newName
inTable: (NSString *) tableName
Changes the name of the column in the specified table from oldName to newName. Both names are passed as a null-terminated string.
The following values can be returned by the method:
SUCCEED | Successful completion; the column was renamed. |
FAIL | Method was unsuccessful. |
renameTable:to:
- (int) renameTable: (NSString *) oldName to: (NSString *) newName
Changes the name of the table from oldName to newName. Both names are passed as a null-terminated string.
The following values can be returned by the method:
SUCCEED | Successful completion; the table was renamed. |
FAIL | Method was unsuccessful. |
rollbackTransaction
- (int) rollbackTransaction
Rolls back transaction that was started with beginTransaction method.
The following values can be returned by the method:
SUCCEED | Successful completion; the transaction was rolled back. |
FAIL | Method was unsuccessful. |
setDisplayErrorMessageEnabled:
- setDisplayErrorMessageEnabled: (BOOL) flag
This method sets the flag for display of an attention panel with the error message generated by the database whenever an error condition occurs. Returns self.
updateRowsFor:inTable:where:convertAs:fromContainer:
- (int) updateRowsFor: (NSString *) columnNames
inTable: (NSString *) tableName
where: (NSString *) searchCondition
convertAs: (NSString *) formatString
fromContainer: (id) container
Updates one or more rows in the table, based on the specified search criteria. Column names are passed as a NSString in columnNames, with names of the columns separated by commas or blanks (or both); the same rule applies to the tableName. Search condition is any valid SQL WHERE clause, passed as a NSString.
In addition to sending the request to the database, this method also establishes the bind between table columns and program objects that will supply the values for the table rows. The source objects are passed to the method via a NSArray of SFDataWrapper objects; each object corresponds to the column in columnNames string.
See description of getRowsFor:... method for the list of valid format codes specified in the formatString.
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been updated and the contents of container have been copied to the columns. |
FAIL | Method was unsuccessful. |
Normally, the original value contained in the updated column and row is replaced with the new value from the corresponding SFDataWrapper object. However, optionally, format codes for numerical columns can be suffixed with one of the following four characters: '+', '-', '*' and '/'. These are arithmetic operations that can be performed on the columns in the course of update; when they are present, the original value of the column in the updated row (before update) will be arithmetically combined with the new value according to the operation, and the calculated result will be used to replace the original value. For example, format INT+ will instruct the SFDataBase object to add value contained in the variable to the value found in the corresponding column (and selected row), rather than replace it with the one from the SFDataWrapper object.
updateRowsFor:inTable:where:convertAs:fromVars:
- (int) updateRowsFor: (NSString *) columnNames
inTable: (NSString *) tableName
where: (NSString *) searchCondition
convertAs: (NSString *) formatString
fromVars: (void *) var, ...
This method is a wrap-around for updateRowsFor:inTable:where:convertAs:fromContainer: method; it takes data from program variables rather than from SFDataWrapper objects.
See description of getRowsFor:... method for the list of valid format codes specified in the formatString.
The following values can be returned by the method:
SUCCEED | Successful completion; the rows have been updated and the contents of variables have been copied to the columns. |
FAIL | Method was unsuccessful. |
Note: data of BLOB type must be placed into the BLOB structure defined in dbconstants.h header file as follows:
typedef struct
{
int length; /* Length of data */
void *data; /* Pointer to the data buffer */
} BLOB;
This calling program must provide this structure and set its data member to point to the buffer with BLOB data, and its length member should be set to the actual length of data in the buffer in bytes. The pointer to the BLOB structure is then passed to updateRowsFor:... method.
useDatabase:
- (int) useDatabase: (NSString *) databaseName
Sets the current database that will be used as a default database for all tables with unqualified names used in the SFDataBase methods. The databaseName must follow the naming conventions for SQL names, and the database with this name must exist prior to using this method.
The following values can be returned by the method:
SUCCEED | Successful completion; the database was set. |
FAIL | Method was unsuccessful. |