Working with Oracle DB from Xcode

I wanted to learn how to work with oracle from xcode, because I did not find a ready-made framework from apple for working with the oracle database directly.
To get started, I tried to create a test project and connect an instantclient from oracle to it. I also used a test case from Oracle (cdemo81.c), included its code in the xcode project, and tested it. Yes, everything works, although I had to beat a little shaman tambourine from google. However, using the oci library directly is difficult, since you need to implement an interface, and this is similar to the invention of the bicycle.
Then I decided to try using the cross-platform ocilib library ( http://orclib.sourceforge.net )
Further in the text is a step-by-step instruction on how to make a test project on cocoa and use this library. The goal of the test project is to connect the library to receive data from the oracle server.

1) Download the library itself http://orclib.sourceforge.net/download/
2) Download instantclient from oracle for macosx http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html , Me in this interested in Version 11.2.0.3.0 (64-bit), Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications ( http://download.oracle.com/otn/mac/instantclient /11203/instantclient-basic-macos.x64-11.2.0.3.0.zip) To download, registration is required, but it is free. Header files will also be required to compile ocilib, so download the “Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client”, in my case this one is instantclient-sdk-macos.x64-11.2.0.3 .0.zip ( http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html )
3) Unpack instantclient into a folder, for example / usr / local / lib / instantclient_11_2
4) Also, unpack SDK c header files inside the folder with instantclient, for example like this - / usr / local / lib / instantclient_11_2 / sdk
5) Now, you need to create a symlink for the library in the instantclient folder, in the console go to the / usr / local / lib / instantclient_11_2 folder, then make a symlink - “ln -s libclntsh.dylib.11.1 libclntsh.dylib”
6) Next, unpack the archive with the ocilib library, for example, in the folder /Users/username/Downloads/ocilib-3.12.1
7) Open the terminal and go to the folder with ocilib, for example - cd /Users/username/Downloads/ocilib-3.12.1
8) In the console, collect the library

./configure --with-oracle-lib-path=/usr/local/lib/instantclient_11_2/ —with-oracle-headers-path=/usr/local/lib/instantclient_11_2/sdk/include
make
sudo make install


After that, the library should be installed in the / usr / local / lib folder, check for the presence of files in the folder, such as libocilib *
The first step in preparing the libraries has been completed. Now try to create a test project in

Xcode 1) Create a new project in Xcode (OS X -> Cocoa Application)
By default, Xcode (I have version 5.1 installed) creates an empty project with MainMenu.xib in which there is an empty view.
2) First, add the ocilib library file to the header project. We open the folder /Users/username/Downloads/ocilib-3.12.1/include and copy the ocilib.h file from there to the folder of our project for Xcode, and add this file to our project (in xcode, inside the project folder do file-> add files to "project name" and select our ocilib.h
3) Next you need to add links for libraries to the project, for this we open the Build Settings of the xcode project, look for Other Linker Flags and add two parameters there - “-locilib” and “-lclntsh” - the first, link to the ocilib library, the second to instantclient . If we try to compile the project now, we get an error - “ld: library not found for -lclntsh”. The fact is that when compiling xcode does not know where exactly instantclient oracle lies, you need to specify a path to it.
4) Specify the path to the libraries - for this, open the Build Settings of the xcode project, look for “Library Search Path” and add the path to instantclient and ocilib, we press two parameters “/ usr / local / lib / instantclient_11_2” and “/ usr / local / lib "

The second stage of preparation was completed, the project saw the library. Let's try to compile the project, everything should work without errors. If there are no errors, congratulations, a third of the way is behind. There are little things left - to connect to the database and get any information.

1) In AppDelegate.h we will write #include "ocilib.h"
2) In AppDelegate.m we will change the code

- (void)applicationDidFinishLaunching:(NSNotification *)aNotification {
    OCI_Connection* cn;
    OCI_Statement* st;
    OCI_Resultset* rs;
    OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
    cn = OCI_ConnectionCreate("orcl", "USERNAME", "PASSWORD", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);
    OCI_ExecuteStmt(st, "select id,name from test_encoding_table");
    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs)) {
        printf("%i - %s\n", OCI_GetInt(rs, 1), OCI_GetString(rs,2));
    }
    OCI_Cleanup(); 
}


Where username, password is the name and password for connecting to our database, and orcl is the instance name registered in tnsnames.ora (how to register, more on that later)
If we try to start the project now, we will get an error, this one - “dyld: Library not loaded: /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1
Referenced from: / Users / chepil / Library / Developer / Xcode / DerivedData / test3-hdexygntscvmwahcgsfolpqrkldi / Build / Products / Debug / test3. app / Contents / MacOS / test3
Reason: image not found ”

This error indicates that when the project starts (yes, it compiled normally), the application does not know where the library we are referring to lies. Since it is one thing to compile a project, it is another thing to execute it.
To fix the error, either start the project from the console by setting the necessary environment variables, or add the necessary variables directly to xcode.
To do this, click on the name of the project in the upper left corner of the project window (unfortunately, I don’t know how to do this through the menu), and select “Edit Scheme ...”
Select the startup scheme (in my case, “Run test3.app”) and open Arguments tab, in which we write a few “Environment Variables” - environment variables. You need to understand that these variables are valid only when starting a project from Xcode, and will cease to act as soon as we launch our app file in open swimming ...

3) Add the environment variable DYLD_LIBRARY_PATH with the value / usr / local / lib: / usr / local / lib / instantclient_11_2. As you can see, to add multiple values, you need to use a colon. Let's try to start the project. The project started, but nothing came out of the console. It is logical, since in the first place, there was no connection to the database, and in the second place, your test_encoding_table table is probably missing from your database (I hope you guessed it to replace the query with your value). However, what is missing to connect to the database? That's right, the environment variable ORACLE_HOME, which should contain network / admin / tnsnames.ora

4) create the folders / Users / username / oracle_home / network / admin
5) create the file /Users/username/oracle_home/network/admin/tnsnames.ora
tnsnames.ora file contents:

orcl=
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=orclserver.myaddress.com) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))


In your case, the connection may be different; the settings for connecting to the oracle server are beyond the scope of this article. The connection above is just an example.

6) We will write for our application a new environment variable to run (in the same place where we wrote DYLD_LIBRARY_PATH). The new variable will have the name ORACLE_HOME and have the value / Users / username / oracle_home.
After that, when you start the application and create the correct query (to your table), you will begin to receive data from the server. Also, it was possible to register the value of the variable not ORACLE_HOME, but TNS_ADMIN and specify the path to the tnsnames.ora file (Oracle administrators and programmers know what it is about, I will not chew)

7) In my case, the test_encoding_table contains test records in Russian and Japanese, in UTF8 encoding. When requesting this data in xcode, I got this result -
1 - ????????????? ???????? ????
...

It is clear that these are questions, and not the data we need, which means that something is working wrong.
For the correct return of data, you need to specify in which encoding we are working

8) We will register the next environment variable to launch the application. As well as DYLD_LIBRARY_PATH, ORACLE_HOME, create the NLS_LANG variable and write the value to it (for example, RUSSIAN_CIS.UTF8)
Run the project and get the output in the project console -

1 - testing Russian names
2 - 日本語 で 利用 し た い の で す が

What was required.

Remained - little things. Let's now output the result to NSTextView in the main program window. To do this,
change the MainMenu.xib file created by default, put a new NSTextView on the window,
change the AppDelegate.h

#import 
	#include "ocilib.h"
	@interface AppDelegate : NSObject  {
    		IBOutlet NSTextView *textView;
	}
	@property (assign) IBOutlet NSWindow *window;
	@property (nonatomic,retain) IBOutlet NSTextView *textView;
	@end


3) Change AppDelegate.m

#import "AppDelegate.h"
@implementation AppDelegate
@synthesize textView;
- (void)applicationDidFinishLaunching:(NSNotification *)aNotification
{
    // Insert code here to initialize your application
   NSString *str = @"";
    OCI_Connection* cn;
    OCI_Statement* st;
    OCI_Resultset* rs;
    OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
    cn = OCI_ConnectionCreate("orcl", "username", "password", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);
    OCI_ExecuteStmt(st, "select id,name from test_encoding_table");
    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs))
    {
        printf("%i - %s\n", OCI_GetInt(rs, 1), OCI_GetString(rs,2));
        NSString *str1 = [NSString stringWithUTF8String:OCI_GetString(rs,2)];
        str = [NSString stringWithFormat:@"%@\n%i: %@",str,OCI_GetInt(rs, 1),str1];
    }
    OCI_Cleanup();
   [textView setString:str];
}
@end


4) on MainMenu.xib, open AppDelegate and link the output textView to our new NSTextView, which we put in our window.

Launch the application.
Now the query output to the database is duplicated in the project console and in NSTextView.

Last clarification. If we want to run the program (in my case test3.app) from the console, and not from xcode, then we will need to register environment variables.
For the test, create the file test.sh, give it the rights
chmod +x test.sh
and write inside something like:

#!/bin/sh
export DYLD_LIBRARY_PATH=/usr/local/lib:/usr/local/lib/instantclient_11_2
export ORACLE_HOME=/Users/chepil/oracle_home
export NLS_LANG=RUSSIAN_CIS.UTF8
open test3.app


run the project from the console -

./test3.sh


the application opens, a window in it, a request to the database is made and the result is displayed in a textview on the screen. Everything works, as required.

In the end, I would like to ask, maybe there are other libraries for working with Oracle. I am at the beginning of the development of the project and I need to make a decision on the advisability of working with this particular library. In fact, the ocilib library is a wrapper around OCI from Oracle, and I don’t want to reinvent the wheel. Who has any experience with Oracle from xcode? What are the comments on the text of the article?
Thanks for the constructive criticism!

Also popular now: