Re: Cocoa Database Connection

FROM : Andrew Satori
DATE : Wed Mar 26 21:12:45 2008

Justin,

Sorry, I've been buried with other work and didn't get this earlier.

There are several ways to solve what you want.  You can bridge to the 
various databases using native toolkits or you can use a generic 
toolkit like JDBC via the java bridge (not supported anymore under 
Leopard) or ODBC.

Since you are rolling with PostgreSQL, there are several ways of 
getting there.  libpq is the most well supported and documented, but 
it is not very 'Cocoa'.  For that you need BaseTen, which is 
excellent.  However, it might be more than what you are after.

I've been down this path with PostgreSQL twice now.  I started by 
writing pgCocoaDB which is the foundation of the current GUI tools in 
the PostgreSQLforMac project.  I learned alot, and honestly did a fair 
amount wrong there.  So I reworked that into what is the foundation of 
the future PostgreSQL tools from both my company and the OSS 
PostgreSQLforMac project.

It is called PGSQLKit, it is a framework that is intended to be a 
middle ground bridge of Cocoa like structure and the very familiar 
ADODB interfaces that most Windows programmers will be familiar with.

It is built upon libpq, but does not require a local installation of 
PostgreSQL and it's header files.


At it's very basic, it exposes a PGSQLConnection class that is more or 
less the root of all your PG usage.  for example, let's establish a 
connection, login, run a query and return a result (this is copied 
directly from the upcoming automator action for running an array of 
dynamic querie against a PostgreSQL database):


- (id)runWithInput:(id)input fromAction:(AMAction *)anAction error:
(NSDictionary **)errorInfo
{
   // Add your code here, returning the data to be passed to the next 
action.
   NSMutableDictionary *dict = [self parameters];
   
   NSString *user = [dict valueForKey:@"userName"];
   NSString *password = [dict valueForKey:@"password"];
   
   NSString *serverName = [dict valueForKey:@"serverName"];
   NSString *serverPort = [dict valueForKey:@"serverPort"];
   NSString *databaseName = [dict valueForKey:@"databaseName"];
   
   PGSQLConnection *connection = [[PGSQLConnection alloc] init];
   
   [connection setUserName:user];
   [connection setPassword:password];
   
   [connection setServer:serverName];
   [connection setPort:serverPort];
   [connection setDatabaseName:databaseName];
   
   if ([connection connect])
   {
       NSArray *cmdArray = [[NSArray alloc] initWithArray:input];
       NSMutableArray *result = nil;
       
       // loop the array and execute each command in a transaction,
       // rollback ALL command if any errors occur.
       int i;
       for (i = 0; i < [cmdArray count]; i++)
       {
           NSString *query = (NSString *)[cmdArray objectAtIndex:i];
           
           PGSQLRecordset *rs = [connection open:query];
           if (rs != nil) {
               if (![rs isEOF])
               {
                   if (result != nil)
                   {
                       [result release];
                       result = nil;
                   }
                   result = [[NSMutableArray alloc] init];
                   while (![rs isEOF])
                   {
                       [result addObject:[rs dictionaryFromRecord]];
                       [rs moveNext];
                   }                
               }
               [rs close];                            
           }
       }
       
       [connection close];
       
       return result;        
   } else {
       // setup the error dictionary
   }
   return nil;    
}

The relevant code is the PGSQLConnection and setting the login 
variables, the connect command, the open command (which returns a 
result set) and the dictionaryFromRecord that returns an NSDictionary 
representation of the current record.

The framework is available as part of the dmg download from www.postgresqlformac.com
, and though it is not complete, it is usable and I am slowly building 
user friendly documentation.  If you wish, there is a good bit of 
documentation written for it's syntactically similiar 
ODBCKit.framework, that is part of the ODBCKit.  The PGSQLKit does 
have some goodies that I haven't pushed back into ODBCKit, including 
the built in login dialog has intrinsic support for saving the login 
connections to the keychain, and  does support reading them back from 
the keychain.

Feel free to ask questions, I'll do my best to answer promptly, though 
I am in the middle of building up the 8.3.1 packages.


Andrew Satori - Owner & Janitor Druware Software Designs
Business Solutions for Small Business
http://www.druware.com/



On Mar 21, 2008, at 7:50 PM, Justin Giboney wrote:
> So, libpq sounds like a well supported way to go, but when I import 
> "libpq-fe.h" into my project I get an error that says 
> "postgres_ext.h: No such file or directory", along with 30 more 
> errors which I assume are related to the lack of this file. I 
> searched my computer for it and I can't find it.
>
> Is there something that I should have installed besides PostgreSQL 
> 8.3.0? I have seen something about doxygen, but I don't know what 
> that is. Do I need it?
>
> Thank you,
>
> Justin Giboney
>
>
> On Mar 21, 2008, at 4:43 PM, A.M. wrote:
>

>>
>> On Mar 21, 2008, at 6:33 PM, Justin Giboney wrote:
>>

>>> I am trying to find a way to connect Cocoa to a DBMS.
>>>
>>> I have been working for a few weeks trying to find a way to get 
>>> Cocoa to communicate with PostgreSQL (at this point I am willing 
>>> to use just about any mainstream database). I have tried using 
>>> BaseTen (http://www.karppinen.fi/baseten/) and PostgreSQL Cocoa 
>>> Framework (http://sourceforge.net/projects/pgsqlcocoa/), but I 
>>> have not been able to get either of them to work. Both seem to be 
>>> very complicated and need a lot of tweaking.
>>>
>>> I have heard hints of odbc/iodbc but no tutorials on how to use it 
>>> in cocoa.
>>>
>>> I have PostgreSQL 8.3 installed, running 10.5 on the host that 
>>> will talk to the DB, and multiple client computers running 10.4 or 
>>> 10.5
>>>
>>> What would you use to create an enterprise level application?
>>> Is there any tutorials that you would use to create the 
>>> connections to the database?
>>>

>>
>> You can certainly use the straight-C libpq- it works fine.
>>
>> http://www.postgresql.org/docs/8.3/static/libpq.html
>>
>> Cheers,
>> M
>>

>
> _______________________________________________
>
> Cocoa-dev mailing list (<email_removed>)
>
> Please do not post admin requests or moderator comments to the list.
> Contact the moderators at cocoa-dev-admins(at)lists.apple.com
>
> Help/Unsubscribe/Update your Subscription:
> http://lists.apple.com/mailman/options/cocoa-dev/<email_removed>
>
> This email sent to <email_removed>