FrontBase Documentation

Backtrack:   Welcome!     6. Original Documentation

Updated: 20-Nov-2000 prev — next Table of Contents

6.6. FBAccess

This article describes FBAccess.

Introduction
Connecting to a database
To share or not
Closing a database connection
Creating a session
Multiple sessions
Disconnecting sessions
Executing SQL statements
SELECT and fetching
Binary Large OBjects - BLOBs
Mapping of SQL 92 datatypes into objects

Introduction

FBAccess is an object-oriented framework for accessing and administrating FrontBase servers/databases. FBAccess is ideal for use in applications where you for some reason don't want to use Apple's Enterprise Objects Framework (EOF) and when you want to administrate (create/start/stop/delete) databases programmatically.

In order to send SQL statements to a FrontBase server through the FBAccess API, a simple two step process must be carried out:

1) Create a connection to the database (see Connecting to a database).
2) Create a session within the database connection (see Session management).

If these two steps are excuted successfully, you are ready to send SQL statements to a FrontBase server and have them executed.

Whenever an SQL statement is executed by a FrontBase server, the so-called meta data are always returned to client. The meta data provides information as to how the execution went, e.g. whether errors were detected, how many rows were affected, and in case of SELECTs, information detailing the columns and their data types.

Connecting to a database

Connecting to a database is very simple, it only requires you to supply the name of the database and the name of the host computer on which the database is running:

   #import <FBAccess/FBDatabaseConnection.h>

   FBDatabaseConnection* fbdc;

   fbdc = [FBDatabaseConnection connectToDatabaseNamed:@"Accounts"
                                                onHost:@"server0"];

Note: Please note that the name of the database is case sensitive.

If you have secured the database with a so-called database password, you need to supply this password as well:

   fbdc = [FBDatabaseConnection connectToDatabaseNamed:@"Accounts"
                                                onHost:@"server0"
                                              password:@"<a password>"];

Note: Please note that the password is not encrypted or digested in any way by FBAccess, i.e. this has to be done prior to connecting the database connection (see FBAccess/FBPasswordDigest.h).

If nil is returned, the connection could not get established and the most probable cause can be found via the class method +errorMessage:.

   fbdc = [FBDatabaseConnection connectToDatabaseNamed:@"Accounts"
                                                onHost:@"server0"];
   if (fbdc == nil) {
      NSLog(@"Could not connect: %@", [FBDatabaseConnection errorMessage]);
      ...
   }

Possible error messages are:

No such host

The specified host computer name could not be looked up.

No FBExec found on host

There is no FBExec process running on the specified host computer.

Database is not running

The specified database is either not running or it doesn't exists.

Incorrect database password

An incorrect database password has been specified.

Could not get secure socket to server

The FrontBase server has requested a secure communication, but an error in either the protocol or the encryption keys has occurred.

No socket available

No new socket could be allocated (considered a serious system error).

Could not connect to database

A socket has been created, but a connection could not be established (considered a serious system/network error).

Write message failed

A serious error on the server side has occurred, with the most likely cause being that the server has been terminated.

If, for some reason, a database connection is closed/terminated, a notification will be posted, allowing you to clean up any reference to the connection. This is in particular important when connections are shared.

   [[NSNotificationCenter defaultCenter]
             addObserver:self
                selector:@selector(connectionWillTerminate:)
                    name:[FBDatabaseConnection FBDCConnectionWillTerminate]
                  object:nil];

	   ...

   - (void) connectionWillTerminate:(NSNotification *) notif
   {
      NSLog(@"Got connectionWillTerminate");

      if ([notif object] == fbdc)
         fbdc = nil;
   }

To share or not

Depending on your applications needs, connections to the same database can be either shared (i.e. one connection to a database per application) or they can be discrete. By default connections are shared, but this behaviour can be changed via the class method called +setShareDatabaseConnections:

   #import <FBAccess/FBDatabaseConnection.h>

   [FBDatabaseConnection setShareDatabaseConnections:NO];

Note: +setShareDatabaseConnections should be called before any connections are established.

If connections are shared, +connectToDatabaseNamed:onHost: will handle the sharing, i.e. multiple calls with return the same connection object (assuming same database name and host computer name). For shared connections you do not need to worry about retain/release. If connections aren't shared, you should remember to carry out the normal retain/release procedures (FBDatabaseConnection objects are autoreleased).

Closing a database connection

To close a database connection is very simple:

   #import <FBAccess/FBDatabaseConnection.h>

   [fbdc close];
   [fbdc release];	// Only for non-shared connections

This will effectively release all resources on both the client and the server side related to the connection. -close implies an implicit [fbdc disconnectAll], i.e. all sessions are automatically released.

To facilitate proper cleaning up of potentially dangling references to the FBDatabaseConnection object, an FBDCConnectionWillTerminate notification is posted.

Creating a session

Once you have created a database connection, you need to create a session before SQL statements can be executed:

   #import <FBAccess/FBDatabaseConnection.h>
   #import <FBAccess/FBMetaData.h>

   FBDatabaseConnection* fbdc;
   FBMetaData* meta;

   meta = [fbdc createSession:@"<some id of the session>"
                  sessionUser:@"<database user name>"
                     password:@"<database user password>"
                   systemUser:@"<any name, e.g. login name>"];

Note: Please note that the password is not encrypted or digested in any way by FBAccess, i.e. this has to be done prior to creating the session (see FBAccess/FBPasswordDigest.h).

If nil is returned, a system error has occured and the most probable cause can be found via the method called -errorMessage:

   if (meta == nil) {
      NSLog(@"Could not create session: %@", [fbdc errorMessage]);
      ...
   }

Even if a non-nil object is returned, errors are still possible:

   if ([meta errorsFound] == YES) {
      NSLog(@"Could not create session: %@", 
            [[fbdc errorMetaData] allErrorMessages]);
      ...
   }

If no errors are found, a current session is now established at the server side and you are ready to execute SQL statements.

Multiple sessions

A single database connection can have multiple sessions, distinguished by the specified session identifiers, but at any point in time only a single session can be active, other sessions attached to the database connection are in a dormant state. Each session carries its own characteristics, e.g. session user, time zone, current schema etc.

You can switch between sessions, which effectively is identical to setting the current session on the server side, via the -setConnection: method:

   meta = [fbdc setConnection:@"<some session id>"];
   if ([meta errorsFound] == YES) {
      NSLog(@"Could not set connection: %@", 
            [[fbdc errorMetaData] allErrorMessages]);
      ...
   }

A shorthand for switching to the first created session (for a given database connection):

   meta = [fbdc setConnectionToDefault];

Disconnecting sessions

Disconnecting a session means that all related resources on the server side are released, but please note that the actual database connection stays alive.

A number of methods exist to disconnect sessions:

- (FBMetaData*) disconnect:(NSString*) sessionId;
	// Disconnects a given session
- (FBMetaData*) disconnectAll;
	// Disconnects all sessions
- (FBMetaData*) disconnectDefault;
	// Disconnects the first session created
- (FBMetaData*) disconnectCurrent;
	// Disconnects the current (active) session

Executing SQL statements

Once you have created a database connection and a session within that database connection, executing SQL statements is straightforward:

   #import <FBAccess/FBDatabaseConnection.h>
   #import <FBAccess/FBMetaData.h>

   FBDatabaseConnection* fbdc;
   FBMetaData* meta;

   meta = [fbdc executeDirectSQL:@"CREATE TABLE T0(C0 INT);"];

Note: Each SQL statement must be terminated with a semicolon.

The returned meta data object provides information pertinent to the execution, e.g. whether errors were detected (see FBAccess/FBMetaData.h):

   if ([meta errorsFound] == YES) {
      NSLog(@"Execution failed: %@", [[fbdc errorMetaData] allErrorMessages]);
      ...
   }

Note: If errors are detected during the execution of a SQL statement, the implied transaction (if any) is rolled back automatically.

In some cases, e.g. when an UPDATE didn't affect any rows, so-called warning messages are returned:

   if ([meta warningsFound] == YES) {
      NSLog(@"Warnings: %@", [[fbdc errorMetaData] allErrorMessages]);
      ...
   }

Warnings can be ignored without affecting the subsequent actions, but unless the actual warning messages are known and understood, it is a good idea to test for and log warnings.

SELECT and fetching

Fetching the resulting rows from a SELECT statement involves multiple steps. First the SELECT statement has to be executed:

   #import <FBAccess/FBDatabaseConnection.h>
   #import <FBAccess/FBMetaData.h>

   FBDatabaseConnection* fbdc;
   FBMetaData* meta;

   meta = [fbdc executeDirectSQL:@"SELECT * FROM INFORMATION_SCHEMA.TABLES;"];

Assuming that no errors were detected, the actual rows can now be fetched, which is done in batches:

   NSData* rawData;

   while ((rawData = [fbdc fetch: handle:[meta fetchHandle]]) != NULL) {
	   // See below
   }

The data returned by the -fetch:handle: method represents a number of rows (<= <row count>) packed into a compact binary representation, which is unpacked as follows:

   NSArray* rows;

   rows = [fbdc buildRowsFrom:rawData];

The rows array contains a an object for each row, with this object being another array with one element for each column.

In most cases, the number of rows returned by a SELECT statement can be determined up front via the -rowCount method ([meta rowCount], see FBAccess/FBMetaData.h). If -rowCount returns -1, the row count hasn't been determined yet (due to the lazy evaluation strategy).

Once all rows have been fetched, the -cancelFetch: method will release all related resources on the server side:

   [fbdc cancelFetch:[meta fetchHandle]];

-cancelFetch: can be invoked at any point in time during a fetch cycle, i.e. not all rows need to be fetched.

Assuming that the SQL statement to be executed is a SELECT of some sort, the first batch of rows can be returned together with the meta data:

   meta = [fbdc executeDirectSQL:@"SELECT * FROM INFORMATION_SCHEMA.TABLES;"
                        preFetch:
                            into:&rawData];

The above shortcut is in particular ideal if the row count is either known up front or known to be less than a given number, i.e. all rows are returned at once thus saving a number of round trips to the server.

Binary Large OBjects - BLOBs

FBAccess comes with all the necessary API functionality to deal with BLOBs. FrontBase actually draws a distinction between two types of large objects:

BLOB	-	Binary Large OBject, any form of binary data that is not interpreted further in any way by FBAccess nor by the FrontBase server.
CLOB	-	Character Large OBject, textual data that is handled like CHARACTER data, i.e. subject to the standard Unicode treatment.

Assuming the following table has been defined:

	CREATE TABLE T0(C0 INTEGER, C1 BLOB);

To INSERT a row in T0 is done as follows:

   #import <FBAccess/FBDatabaseConnection.h>
   #import <FBAccess/FBMetaData.h>
   #import <FBAccess/FBBlobHandle.h>

   FBMetaData* meta;
   FBBlobHandle* blobHandle;
   NSString* sql;
   NSData* blobData;	// Assumed initialized properly

   blobHandle = [fbdc writeBLOB:blobData];

   sql  = [NSString stringWithFormat:@"INSERT INTO T0 VALUES(0, %@);", 
                                     [blobHandle handle]];
   meta = [fbdc executeDirectSQL:sql];

CLOBs are INSERTed in a similar way, just use -writeCLOB: instead of -writeBLOB:.

Reading BLOBs/CLOBs is likewise a simple process. Depending on the actual size of a BLOB/CLOB it is either returned directly in the fetch batch or a handle is returned. To read a BLOB thus requires the following (assuming the BLOB is the first column in the first row):

   NSData* blob = [[rows objectAtIndex:0] objectAtIndex:0];

   if ([blob class] == [FBBlobHandle class])
      blob = [fbdc readBLOB:blob];

The variable blob now points to the NSData object that holds the actual data.

Use -readCLOB:, which returns an NSString object, instead of readBLOB: when dealing with CLOBs.

Mapping of SQL 92 datatypes into objects

The SQL 92 datatypes are mapped into the following object classes:

BOOLEAN	NSNumber, 0 => FALSE, 1=> TRUE, 255 => UNKNOWN
SMALLINT, INTEGER	NSNumber
INTERVAL YEAR TO MONTH	NSNumber, unit is months
NUMERIC, DECIMAL, FLOAT, REAL, DOUBLE PRECISION	NSDecimalNumber
INTERVAL DAY TO SECOND	NSDecimalNumber, unit is seconds
TIME, TIME WITH TIME ZONE	NSString, e.g. "+10:23:67" NSString, explicit time zone, e.g. "+10:23:67-08:00"
DATE	NSCalendarDate
TIMESTAMP	NSCalendarDate (the time zone as that of the session when the value was inserted)
TIMESTAMP WITH TIME ZONE	NSCalendarDate (the time zone as that of the expression when the value was inserted)
CHARACTER, CHARACTER VARYING	NSString
BIT, BIT VARYING	NSData
BLOB*	FBBlobHandle or NSData (depending on the size of the object)
CLOB*	FBBlobHandle or NSString(depending on the size of the object)

* BLOB and CLOB values are covered in more detail in a separate document called "Binary Large OBjects - BLOBs"

If you have feedback or questions on this document, please send e-mail to doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!

©2000 FrontBase, Inc. All rights reserved.