Work with Oracle DB from Xcode

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

1) Download the library itself http://orclib.sourceforge.net/download/
2) Downloading the oracle instantclient for macosx http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html , In this case I'm 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 ) Registration is required for download, but it is free. You will also need header files to compile the ocilib, so download the “Instant Client Package - SDK: an additional header file for developing Oracle applications with the 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) Extract the instantclient to a folder, for example / usr / local / lib / instantclient_11_2
4) Also, unpack the SDK c header files into the inside 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, go to the / usr / local / lib / instantclient_11_2 folder in the console, then we make a symlink - "ln -s libclntsh.dylib.11.1 libclntsh.dylib"
6) Next, unpack the archive with the library of ocilib, for example in the folder / Users / username / Downloadloads / soc-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 we sequentially assemble 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 files in the folder, such as libocilib *
The first stage of the preparation of libraries is 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 installed version 5.1) creates an empty project with MainMenu.xib in which there is an empty view.
2) First, add the library file ocilib to the project header. Open the /Users/username/Downloads/ocilib-3.12.1/include folder 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 we make file-> add files to "project name" and choose our ocilib.h
3) Next you need to add links for libraries to the project, to do this, open the xcode project's Build Settings, look for Other Linker Flags and add two parameters there - “-locilib” and “-lclntsh” - first, link to the ocilib library, second to instantclient . If we now try to compile the project, we get an error - "ld: library not found for -lclntsh". The fact is that when compiling xcode, it doesn’t know where exactly the instantclient oracle is located, you need to specify the path to it.
4) Specify the path to the libraries - to do this, open the xcode project's Build Settings, look for the “Library Search Path” and add the path to the instantclient and ocilib, we press the 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 mistakes, congratulations, there are a third way behind. Little things left - to connect to the database and get any information.

1) In AppDelegate.h we write #include "ocilib.h"
2) In AppDelegate.m 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 to connect to our database, and orcl is the name of the instance registered in tnsnames.ora (how to register, more on that later)
If we now try to start the project, we get an error, like this - “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 suggests that when you run the project (yes, it compiled normally), the application does not know where the library to which we refer lies. Since it is one thing to compile a project, it is another thing to execute it.
To correct the error, either run the project from the console, setting the necessary environment variables, or add the necessary variables directly to xcode.
To do this, click on the project name in the upper left corner of the project window (unfortunately, I don’t know how to do it through the menu), and select “Edit Scheme ...”
Select the startup scheme (in my case, “Run test3.app”) and open the Arguments tab, in which we will write several “Environment Variables” - environment variables. You need to understand that these variables are valid only when you start a project from Xcode, and will cease to act as soon as we allow our app file to go public ...

3) Add the DYLD_LIBRARY_PATH environment variable 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 run the project. The project started, but nothing happened to the console. It is logical, because firstly, there was no connection to the database, and secondly, the test_encoding_table table is probably not in your database (I hope you guessed it to replace the query with its value). However, what is missing to connect to the database? That's right, the environment variable ORACLE_HOME, in which the network / admin / tnsnames.ora should be located

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

 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) Let's write a new environment variable for our application to run (in the same place where we registered DYLD_LIBRARY_PATH). The new variable will be named 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 start receiving data from the oracle server. Also, it was possible to register the value of the variable is 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 table contains test records in Russian and Japanese, in UTF8 encoding. When requesting this data in xcode, I got this result -
one - ???????????? ???????? ????
...

It is clear that this is a question mark, and we do not need the data, which means that something is wrong.
For correct data return, you need to specify in which encoding we work

8) Let's write another environment variable to start the application. As well as DYLD_LIBRARY_PATH, ORACLE_HOME, create a variable NLS_LANG and assign a value to it (for example RUSSIAN_CIS.UTF8)
Run the project and get the output to the project console -

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

Q.E.D.

It remains - little things. Let's now display the result in NSTextView in the main program window. For this
Modify the MainMenu.xib file created by default, put a new NSTextView on the window
Change AppDelegate.h

 #import <Cocoa/Cocoa.h> #include "ocilib.h" @interface AppDelegate : NSObject <NSApplicationDelegate> { 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 the AppDelegate and link the outlet textView to our new NSTextView, which we put in our window.

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

Last clarification. If we want to start the program (in my case test3.app) from the console, and not from xcode, then we will need to set the environment variables.
For the test, create the file test.sh, give it 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 

we start the project from the console -

 ./test3.sh 

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

Finally, I would like to ask, maybe there are other libraries for working with Oracle. I am at the beginning of the project development and I need to decide on the expediency of working with this library. In fact, the library of ocilib is a wrapper around OCI from Oracle, and you do not want to reinvent your bicycle. Who has experience with orakl from xcode? What comments are there on the text of the article?
Thanks for the constructive criticism!