Re: ODBC
Re: ODBC
- Subject: Re: ODBC
- From: Andrew Satori <email@hidden>
- Date: Wed, 26 Jul 2006 10:36:09 -0400
the Actual project is fine, but I elected to build a quick and dirty
class to do it the first time. Now I'm working on something a little
more 'Cocoa'ish.
So, for example, I now have an ODBCConnection class that is roughly
analogous to the ADODB.Connection in VB6/.Net.
One of the hidden gotchas with ODBC, is that you MUST init the
environment first, or no ODBC call will work.
So, for example, the ODBCConnection.h class looks like this:
//
// ODBCConnection.h
// Convert from TrustWin
//
// Created by Andy Satori on 6/27/06.
// Copyright 2006 Satori & Associates, Inc. All rights reserved.
//
#import <Cocoa/Cocoa.h>
#import "ODBCRecordset.h"
#import "sql.h"
#import "sqlext.h"
#import "sqltypes.h"
#ifndef SQLERR_FORMAT
#define SQLERR_FORMAT "SQL Error State:%s, Native Error Code: %lX,
ODBC Error: %s"
#endif
// static HENV henv;
@interface ODBCConnection : NSObject {
BOOL isEnvironmentValid;
BOOL isConnected;
NSString *dsn;
NSString *userName;
NSString *password;
HENV henv;
HDBC hdbc; // hdbc
}
- (BOOL)initSQLEnvironment;
- (id)allocateStatement;
- (void)logError:RETCODE forStatement:stmt;
- (BOOL)freeSQLEnvironment;
- (BOOL)connect;
- (long)execCommand:(NSString *)sql;
- (ODBCRecordset *)open:(NSString *)sql;
- (BOOL)isEnvironmentValid;
- (BOOL)isConnected;
- (NSString *)dsn;
- (void)setDsn:(NSString *)value;
- (NSString *)userName;
- (void)setUserName:(NSString *)value;
- (NSString *)password;
- (void)setPassword:(NSString *)value;
@end
Now in the init method, the code does the initSQLEnvironment call and
the dealloc deals with the cleanup:
-(id)init
{
[super init];
isConnected = NO;
isEnvironmentValid = YES;
if (![self initSQLEnvironment]) {
NSLog(@"ODBC Environment Failed to initialize, ODBC functions will
fail.");
isEnvironmentValid = NO;
}
return self;
}
-(void)dealloc
{
[self freeSQLEnvironment];
[super dealloc];
}
Obviously, we need the init/free methods:
-(BOOL)initSQLEnvironment
{
henv = 0;
RETCODE nResult;
nResult = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (nResult != SQL_SUCCESS)
{
return NO;
}
nResult = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)
SQL_OV_ODBC2, (int)NULL);
if (nResult != SQL_SUCCESS)
{
// free the env handle in the case of a failure here
[self freeSQLEnvironment];
return NO;
}
return YES;
}
-(BOOL)freeSQLEnvironment
{
return (SQLFreeHandle(SQL_HANDLE_ENV, henv) == SQL_SUCCESS);
}
Next we need to be able to connect:
- (BOOL)connect {
RETCODE nResult; // Result code
if ((nResult = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)) !=
SQL_SUCCESS)
{
// DisplayError(nResult, henv, SQL_NULL_HDBC, SQL_NULL_HENV);
return NO;
}
nResult = SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS,
SQL_CUR_USE_IF_NEEDED);
nResult = SQLConnect(hdbc,(UCHAR *)[dsn cString], SQL_NTS,
(UCHAR *)[userName cString], SQL_NTS,
(UCHAR *)[password cString], SQL_NTS);
// if failed to connect, free the allocated hdbc before return
if (nResult != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO)
{
// DisplayError(nResult, SQL_NULL_HENV, m_hdbc, SQL_NULL_HENV);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
return NO;
}
// display any connection information if driver returns
SQL_SUCCESS_WITH_INFO
if (nResult == SQL_SUCCESS_WITH_INFO)
{
// DisplayError(nResult, SQL_NULL_HENV, m_hdbc, SQL_NULL_HENV);
}
if ([self allocateStatement] == NULL)
return NO;
return YES;
}
Then of course we need to be able to exec (update/insert/delete) and
open (select, stored proc that returns a result), so we need -open, -
execCommand and the supporting utility functions:
- (long)execCommand:(NSString *)sql
{
HSTMT hstmt;
int nResult;
// SQLINTEGER cbRowArraySize = 1;
long lRowCount;
hstmt = [self allocateStatement];
nResult = SQLExecDirect(hstmt, (UCHAR *)[sql cString], [sql length]);
if (nResult != SQL_SUCCESS)
{
[self logError:(void *)nResult forStatement:hstmt];
//DisplayError(nResult, henv, hdbc, hstmt);
if (nResult != SQL_SUCCESS_WITH_INFO)
{
SQLFreeStmt(hstmt, SQL_CLOSE);
return(FALSE);
}
}
lRowCount = 0;
SQLRowCount(hstmt, &lRowCount);
SQLFreeStmt(hstmt, SQL_CLOSE);
return lRowCount;
}
- (ODBCRecordset *)open:(NSString *)sql
{
HSTMT hstmt;
int nResult;
SQLINTEGER cbRowArraySize = 1;
hstmt = [self allocateStatement];
nResult = SQLExecDirect(hstmt, (UCHAR *)[sql cString], [sql length]);
if (nResult != SQL_SUCCESS)
{
[self logError:(void *)nResult forStatement:hstmt];
if (nResult != SQL_SUCCESS_WITH_INFO)
{
SQLFreeStmt(hstmt, SQL_CLOSE);
return(nil);
}
}
SQLSetStmtAttr(hstmt, SQL_ROWSET_SIZE, &cbRowArraySize, (int)NULL);
if (nResult != SQL_SUCCESS)
{
// DisplayError(nResult, henv, m_hdbc, m_hstmt);
return (nil);
}
return [[[[ODBCRecordset alloc] initWithConnection:henv
forDatabase:hdbc withStatement:hstmt] retain] autorelease];
}
- (id)allocateStatement
{
int nResult;
HSTMT hstmt;
if ((nResult = SQLAllocStmt(hdbc, &hstmt)) != SQL_SUCCESS)
{
[self logError:(void *)nResult forStatement:SQL_NULL_HSTMT];
return(NULL);
}
return hstmt;
}
- (void)logError:RETCODE forStatement:stmt
{
UCHAR szErrState[SQL_SQLSTATE_SIZE+1]; // SQL Error State string
UCHAR szErrText[SQL_MAX_MESSAGE_LENGTH+1]; // SQL Error Text string
char szBuffer[SQL_SQLSTATE_SIZE+SQL_MAX_MESSAGE_LENGTH+ 1024 +1];
char szDispBuffer[SQL_SQLSTATE_SIZE+SQL_MAX_MESSAGE_LENGTH+ 1024 +1];
// formatted Error text Buffer
SWORD wErrMsgLen; // Error message length
UDWORD dwErrCode; // Native Error code
int iSize; // Display Error Text size
SQLRETURN nErrResult; // Return Code from SQLError
int fFirstRun; // If it was first msg box
BOOL bErrorFound = FALSE;
fFirstRun = TRUE;
szBuffer[0] = '\0';
do
{
// continue to bring messageboxes till all errors are displayed.
// more than one message box may be reqd. as err text has fixed
// string size.
// initialize display buffer with the string in error text buffer
strcpy(szDispBuffer, szBuffer);
// call SQLError function with proper ODBC handles, repeatedly until
// function returns SQL_NO_DATA_FOUND. Concatenate all error strings
// in the display buffer and display all results.
while ((nErrResult = SQLError(henv, hdbc, stmt, szErrState,
(SQLINTEGER *)&dwErrCode, szErrText,
SQL_MAX_MESSAGE_LENGTH-1,
&wErrMsgLen)) != SQL_NO_DATA_FOUND)
{
if (dwErrCode != 5701 && dwErrCode != 5703 && dwErrCode != 1805)
{
sprintf(szBuffer, SQLERR_FORMAT, (LPSTR)szErrState, dwErrCode,
(LPSTR)szErrText);
iSize = strlen(szDispBuffer);
if (iSize && (iSize+strlen(szBuffer)+1) >= 1024)
break;
if (iSize)
strcat(szDispBuffer, "\n");
strcat(szDispBuffer, szBuffer);
bErrorFound = TRUE;
}
}
if (bErrorFound == FALSE)
return;
NSLog(@"%s", szDispBuffer);
}
while (!(nErrResult == SQL_NO_DATA_FOUND));
}
Now, that's the basics, You'll have to flesh out the other
boilerplate code for the set/get's and then build the ODBCRecordset
class and it's children accordingly. What you see above is part of
the project I'm working on, and I haven't decided as to how I'm going
to publish it, so for the moment, I can't publish the entire class
structure (not to mention it's still incomplete), but this should be
enough to get your ODBC efforts going.
The other functions you'll want to be aware of are:
nResult = SQLFetch(odbcStmt); // moving forward through the records
SQLNumResultCols(odbcStmt, &iCols) // getting the column count
nResult = SQLDescribeCol(odbcStmt, index, (unsigned char *)szBuf, 256,
&iNameLen, &iType, &iLength,
&iDec, &iNullable)); // get the column definitions
SQLGetData(odbcStmt, ([column index]), SQL_C_CHAR, szBuf, iLen,
(long *)&iLen); // get the data contained in the column.
PErsonally, I'm nesting these down inside Odbjective C classes,
ODBCColumn, ODBCRecordset, and ODBCField with the intent exposing the
entire thing using a nice clean interface that is easily consumed and
readily assimilated by new to Cocoa from the Win32 world developers.
Andy
On Jul 25, 2006, at 12:10 PM, Scotty's Lists wrote:
On Jul 24, 2006, at 5:37 PM, Brian Weitzner wrote:
Please forgive me as I am new to both Cocoa and databases. I am
running firebird database. So through terminal, I use:
$ isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'DbPATH' user UID password PASSWD;
to connect to my database. So in my project, in the class that is
responsible for accessing the database, i have #include
<isqlext.h> (which calls isql.h and iodbcunix.h) in the header and
implementation files. So if I use DbConn(db, UID, PASSWD), my app
crashes.... which tells me that this is not the right command or I
need to include more files. So my question has now evolved into
how do I use obj-C to communicate with my database? Is there any
documentation on how to accomplish this or has anyone had
experience with this? Thanks for the help and for your patience!
Actual Technologies has a Carbon example app using the Mac iODBC
libraries. They claim it's easy to translate into Cocoa/ObjC.
YMMV. It should at least give you some clues on how to set up the
Xcode project (i.e. what libraries/frameworks to include, etc.).
<http://www.actualtechnologies.com/example.php>
I found this reference on the iodbc-macosx list at sourceforge.
You might find some other clues there.
<http://sourceforge.net/mailarchive/forum.php?forum_id=38722>
Finally, if you post the crash log from your app, or some other
relevant snippet from Xcode of the feedback you get from the crash,
you might make it more enticing for the list to follow up on your
question. As it reads now, you're asking the list to help you
program obj-C, and I'm guessing no one has time to debug that one.
-s
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Cocoa-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Cocoa-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
| References: | |
| | >ODBC (From: Brian Weitzner <email@hidden>) |
| | >Re: ODBC (From: "Scotty's Lists" <email@hidden>) |
| | >Re: ODBC (From: Brian Weitzner <email@hidden>) |
| | >Re: ODBC (From: "Scotty's Lists" <email@hidden>) |