About
STNPostgreSQL.framework is an open source (BSD-style licensed) Objective-C framework for accessing PostgreSQL databases in a convenient object oriented way.
STNPostgreSQL.framework is still under development and shouldn’t be used in productive environments yet (or do it at your own risk!)!
Download
STNPostgreSQL.framework source code is currently available only through a Git repository.
A Universal Binary of the current revision (r41) can be downloaded here.
To get the latest source code revision start your terminal and do a git clone:
$ git clone http://www.stiefels.net/~sst/repos/STNPostgreSQL.git
You’ll find a Xcode project with several Targets which are described below.
Please note that only read-only access is provided. If you want to help me extending this framework send me a mail and you’ll get an account.
Targets
libpq - * Targets
As the framework is built on the libpq library it is neccessary to build that library first out of the PostgreSQL sources.
The libpq - (ppc/i386/universal) Targets are responsible for that task.
The framework will be built for use on PowerPC and Intel architectures and therefore the libpq needs also to be built for PowerPC (libpq - ppc), Intel (libpq - i386) and combined to a fat file (libpq - universal).
To support you with building the libpq these Targets provide some shell scripts which will build the complete libpq automatically.
All you have to do is to specify the directory with the PostgreSQL distribution sources. This is done in the Build Settings of the STNPostgreSQL project (not Target!). The directory has to be entered at the POSTGRESQL_SRC_DIR setting:
After that, the libpq library can be created by selecting libpq - universal as Active Target and starting the Build procedure.
The libpq.a file will automatically copied into the project folder.
STNPostgreSQL Target
This is the actual Target to build the framework. Before you start the Build process be sure that you have successfully run the libpq - universal Target.
Ideally this Target would have the libpq - universal target as a direct dependency in Xcode. I removed this dependecy because otherwise the whole libpq compilation would be done every time if you try to build this Target.
Unit Tests Target
The project comes with some Unit Tests to check the functionality of the framework.
If you plan to use the Unit Tests you have to do two things.
The first ist to create a table (stnpostgresqltests) in a database on a PostgreSQL server. You can find a SQL script file (create_unittest_table.sql) in the Resources folder which you can use to create that table.
The second is to give Xcode the access parameters (username, password, host, etc.) to that database so that the Unit Tests can connect.
You can do this in the Build Settings of the STNPostgreSQL project (not Target!). The parameters must be provided in form of preprocessor macros (see screenshot):
The POSTGRESQL_VERSION and POSTGRESQL_INT_VERSION (integer representation of version number) should be set to used version of the PostgreSQL server you want to connect to.
These parameters are used in Unit Tests to check if the transmitted version matches.
To run the Unit Tests select Unit Tests as Active Target and Build them.
Playground Target
I used the Playgound Target to play around with the API. It does not affect the actual framework.
Usage
Unfortunately, at this time there is no documentation available but if you have the sources you can look at the Unit Tests to see some examples how this framework can be used.
Here are some examples/snippets how this framework can be used:
Include Header file
The first thing you should do after adding the framework to your project is to include the STNPostgreSQL header file:
#import <STNPostgreSQL/STNPostgreSQL.h>
Establish connection to server
STNPostgreSQLConnection *conn = [[STNPostgreSQLConnection alloc] init];
NSError *connectionError;
[conn setUsername:@"myusername"];
[conn setPassword:@"itssecret"];
[conn setHost:@"pgserv.myhome.local"];
[conn setDatabaseName:@"postgres"];
if ([conn connect:&connectionError]) {
NSLog(@”connection successful”);
} else {
NSLog([[error userInfo] objectForKey:@”errormessage”]);
}
// disconnect
[conn disconnect];
[conn release];
Establish connection to server (threaded mode)
A connection can also be established in a separate thread which doesn’t block your application during the connection process.
Have a look in the Unit Tests to see how that can be done (it’s not very difficult).
Simple statements
// connection already exists
STNPostgreSQLStatement *statement =
[[STNPostgreSQLStatement alloc] initWithConnection:conn];
NSError *statementError;
[statement setStatement:@"INSERT INTO test VALUES (1, 'one')"];
if ([statement execute:&statementError]) {
NSLog(@”statement successful”);
} else {
NSLog([[statementError userInfo] objectForKey:@”errormessage”]);
}
Parametered statements
Sometimes you want to insert data from unsecure sources (i.e. user input). You can use the STNPostgreSQLParameteredStatement class which will automatically escape the values to avoid SQL injection attacks.
// connection already exists
STNPostgreSQLParameteredStatement *statement =
[[STNPostgreSQLParameteredStatement alloc] initWithConnection:conn];
NSError *statementError;
[statement setStatement:@"INSERT INTO test VALUES($1, $2)"];
[statement addParameterWithValue:@"12" type:@"int8"];
[statement addParameterWithValue:@"twelve" type:@"varchar"];
if ([statement execute:&statementError]) {
NSLog(@”statement successful”);
} else {
NSLog([[statementError userInfo] objectForKey:@”errormessage”]);
}
statements (threaded mode)
Statements can also be executed in a separate thread which won’t block your application during the execution process.
Please have a look at the Unit Tests in the repository to see how that works.
Select statements
To fetch query results you have to use STNPostgreSQLResult:
// connection already exists
STNPostgreSQLStatement *statement =
[[STNPostgreSQLStatement alloc] initWithConnection:conn];
NSError *statementError;
[statement setStatement:@"SELECT * FROM test"];
if (! [statement execute:&statementError]) {
NSLog([[statementError userInfo] objectForKey:@”errormessage”]);
} else {
NSLog(@”statement successful”);
STNPostgreSQLResult *result = [statement result];
NSLog(@”%d tuples fetched”, [result numberOfTuples]);
// Value at Row 4 Column 3 (beginning with 0)
NSLog(@”Value: %@”, [result valueAtRow:4 column:3]);
}
Transactions
You can use STNPostgreSQLTransaction to combine multiple statements to one transaction.
An example can be found in the Unit Tests. 
Thank you
As always, I’d be pleased to hear your opinions and ideas to improve this software. Thanks!
Latest Comments
RSS