Oracle OCCI for Visual Studio 2010

I wrote a sort of hello world application using OCCI for Oracle 11 and VC++ 2010. A simple example that runs a select statement on the standard Oracle HR schema and print on the console the resulting rows. If you have a previous experience developing with Oracle OCI you would certainly appreciate the difference.

I'd say the development team has made a good job, but still both the setup and the usage require a bit of attention.

Firstly, I warmly suggest you to use Oracle Database Instant Client, since it makes much easier the job. To be used with VC++ 9 and 10, you need also to download a patch.

Secondly, you should ensure your application has a way to access the tnsnames.ora file where is defined the SID for the database you want to access; you can do that ensuring that ORACLE_HOME or TNS_ADMIN is defined as environment variable, and it is associated with the right path.

If ORACLE_HOME is used, it should be something like c:\ ... \oracle\product\11.2.0\server, where under server you should have network\admin, and in admin you should find tnsnames.ora - TNS_ADMIN should be the path containing tnsnames.ora.

Third step is about setting the Property Pages for the VC++ project that is going to actually use OCCI.

In the Configuration Properties:

1) C/C++
a) General - Additional Include Directories
Add the include directory for OCI. You should find it in you Oracle 11 server installation, something like ... \server\oci\include.
b) Code Generation - Runtime Library
Ensure ist value is set to /MDd or /MD, meaning Multithreaded (Debug) DLL.

2) Linker
a) Additional Library Directories
It should point to the directory where you put the VC++ 10 patch for the instant client.
b) Input - Additional Dependencies
Here you add the library, oraocci11.lib or oraocci11d.lib, specific for VC++ 10 and Oracle 11.

Fourth step is about making available instant client DLLs (generic and specific ones), adding their paths to the PATH environment variable, or copying them in the directory where the executable resides.

Once we have done all this stuff, it is relatively easy writing the code we talked about at the beginning of the post:
#include <iostream>
#include <occi.h>

namespace oc = oracle::occi;

int main()
{
   try
   {
      // setup
      oc::Environment* env = oc::Environment::createEnvironment(); // 1
      oc::Connection* conn = env->createConnection("hr", "password", "MYDB"); // 2
      oc::Statement* stmt = conn->createStatement("select * from countries where country_name like 'A%'"); // 3

      // execution
      oc::ResultSet* res = stmt->executeQuery(); // 4
      while(res->next()) // 5
         std::cout << res->getString(1) << ' ' << res->getString(2) << ' ' << res->getInt(3) << std::endl;

      // cleanup
      stmt->closeResultSet(res); // 6
      conn->terminateStatement(stmt);
      env->terminateConnection(conn);
      oc::Environment::terminateEnvironment(env);
   }
   catch(const oc::SQLException& e) // 7
   {
      std::cout << "Exception: " << e.what() << std::endl;
   }

   system("pause");
}
1. All the connections are created in an environment, that could have different setups. Here, we create a plain environment, that is enough for the current task.
2. Given an environment, we can create a connection. The third parameter is the SID identifying the database in the tnsnames.ora file, the first two parameters represent user and password.
3. Once we have a connection, we can create a statement in it. Here it is a select query.
4. When the statement is a query, we can execute it, getting back as a result a resultset.
5. Now we can loop on the resultset to access all the found rows.
6. Inverting the construction order, we cleanup the object we created: resultset, statement, connection and, finally, the environment.
7. Catching the exceptions that our OCCI calls could throw.

Not bad, if you compare this code with what you should write to get the same result using OCI. But it is a bit painful to see all these pointers, and thinking what happens in case of an exception, say, just after [3.] - the answer is: no cleanup function is called. A bit of RAII would help to make it more robust.

30 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Thank you for the great post. But i Get a heap corruption when I try and do something like this:
    cout<getServerVersion()<<endl;

    This suggests that I might be using the wrong libs. but I'm linking against the libs in \sdk\lib\msvc, with visual studio 2010. Is this correct?

    ReplyDelete
    Replies
    1. Thank you for reading and commenting. Have you downloaded the patch, too? Is it possible that you mixed up different versions (Oracle, VS or Windows 32/64-bit)?

      Delete
  3. I tried a couple of times but it didn't work so far.

    Now I'm getting the errors below:

    main.obj : error LNK2001: unresolved external symbol "public: static void __cdecl oracle::occi::Environment::terminateEnvironment(class oracle::occi::Environment *)" (?terminateEnvironment@Environment@occi@oracle@@SAXPAV123@@Z)
    1>main.obj : error LNK2001: unresolved external symbol "public: static class oracle::occi::Environment * __cdecl oracle::occi::Environment::createEnvironment(enum oracle::occi::Environment::Mode,void *,void * (__cdecl*)(void *,unsigned int),void * (__cdecl*)(void *,void *,unsigned int),void (__cdecl*)(void *,void *))" (?createEnvironment@Environment@occi@oracle@@SAPAV123@W4Mode@123@PAXP6APAX1I@ZP6APAX11I@ZP6AX11@Z@Z)

    I already linked the oraocci11.lib to the project but the errors still persist.
    Do you have any idea?

    ReplyDelete
    Replies
    1. My mistake!! A huge mistake... I was using VC10 x86 and the occi x64..

      Thanks anyway for the amazing post.. it was really helpfull! Congrats!

      Delete
    2. Don't worry, it is common mistake. Thank you for sharing your experience.

      Delete
    3. thanks for the comment, it helped me a lot.

      Delete
  4. I am connecting using a remote connection to a database. Where do I find the TNS_ADMIN file?

    My program compiles but does not appear to get past the createEnviornment() call. The error that spits out to exception handling does not contain anything legible either.

    ReplyDelete
    Replies
    1. Have you installed Oracle on your local machine? At least a client setup is required. I have written in the post where to find the tnsnames.ora file, worst case scenario, if you won't find it, just perform a file search for it in the oracle folder.

      If you don't set the environment correctly, you should expect to get some obscure error message.

      Delete
  5. I tried to do it myself, but without success :(
    With your help, i will do this just in a moment.
    Well done...
    Thanks for shring this knowledge

    ReplyDelete
    Replies
    1. My pleasure! Thank you for your feedback :)

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Hi Manny, I have tried what you have stated, but may not have fully understood everything. I have done what you have told and installed instant client and the patch for VC++10. I did as what you told, added the occi.h location into the additional include directories in configuration properties. I put it as C:\app\Jeremy\product\11.2.0\dbhome_2\OCI\include
    I also did the linker with additional library directories as C:\app\Jeremy\product\11.2.0\dbhome_2\OCI\lib\MSVC\vc10
    vc10 is the folder which includes oraocci11.dll, .lib, oraocci11d.dll, .lib, of which was downloaded from the VC++10 patch.
    Then, I added C:\app\Jeremy\product\11.2.0\dbhome_2\bin
    to the Path in environmental variables, put it all the way in the front.
    I also added C:\instantclient\instantclient_11_2
    at the end of the Path. It is the folder where I put the oracle database instance client in.

    Those are what I did. If I misunderstood what I was supposed to do, can you pinpoint where?
    The error I get when I ran the above code is a link error


    1>------ Build started: Project: Library Automation System, Configuration: Debug Win32 ------
    1> library_automation_system.cpp
    1> LINK : C:\Users\Jeremy\documents\visual studio 2010\Projects\Library Automation System\Debug\Library Automation System.exe not found or not built by the last incremental link;
    performing full link
    1>library_automation_system.obj : error LNK2019: unresolved external symbol "public: static void __cdecl oracle::occi::Environment::terminateEnvironment(class oracle::occi::Environment *)" (?terminateEnvironment@Environment@occi@oracle@@SAXPAV123@@Z) referenced in function _main
    1>library_automation_system.obj : error LNK2019: unresolved external symbol "public: static class oracle::occi::Environment * __cdecl oracle::occi::Environment::createEnvironment(enum oracle::occi::Environment::Mode,void *,void * (__cdecl*)(void *,unsigned int),void * (__cdecl*)(void *,void *,unsigned int),void (__cdecl*)(void *,void *))" (?createEnvironment@Environment@occi@oracle@@SAPAV123@W4Mode@123@PAXP6APAX1I@ZP6APAX11I@ZP6AX11@Z@Z) referenced in function _main
    1>C:\Users\Jeremy\documents\visual studio 2010\Projects\Library Automation System\Debug\Library Automation System.exe : fatal error LNK1120: 2 unresolved externals
    ========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========


    Thanks for your time.

    ReplyDelete
    Replies
    1. Hello stranger, thank you for spending some time in this blog. The error you get looks suspiciously similar to the one José Augusto reported in his comment, see above. Is it possible that you mixed up 32 and 64 bit libraries?

      Delete
    2. I think that was it. I think VC++10 is 32 bit, and I'm trying to use the 64 bit library. Thanks a lot though.

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Dear Manny, I follow all steps, but when I execute my application, I get the following error: "Information not available, no symbols loaded for oraocci11.dll" (line stmt->executeQuery()).

    ReplyDelete
    Replies
    1. Hello, thank you for passing by. Please have a look at the comments above, I am suspecting that you are having a similar problem.

      Delete
  12. First, thanks very much for your help.

    I have a problem. I can successfully connect with the database. But when I was doing the example query statement, only one record shows up! I tried several other select statement. The same result. And some other select statement will cause the exception ora 32109 invalid column or parameter position. I don't know why. Please help!!

    ReplyDelete
    Replies
    1. Hello Lu. Sorry but it is a long while since I worked with OCCI, I can't be helpful right now. The exception 32109 means that you made a mistake somewhere writing the select statement or accessing the result.

      Delete
    2. Thank you for your reply, Manny. I have fixed that problem. But there is also another exception. res->next() can only execute one loop and show one line of record. Then it will throw a 0xc0000005 exception: access violation reading location. Did the same thing happen when you ran this code? Is this probably the problem of configuration? I think the code is right!

      Delete
    3. I remember it took some effort to write this piece of code. The main issue I think was properly setting the environment. In any case, the final result that you can see in the post was thoroughly tested and worked fine.

      Delete
    4. Thank you again! I really appreciate your help!

      Delete
  13. Hello,

    Really helpful post.

    I am using Visual Studio 2012. I am using oracle instant client 12.1.0.0.

    My program crashes at the below line:
    oc::Environment* env = oc::Environment::createEnvironment(); // 1

    Awaiting a help from you.

    regards,
    Ritesh

    ReplyDelete
    Replies
    1. Hello Ritesh, thank you for your kind feedback. About your issue, please have a look at the previous comments. Mixing 32 and 64 bit libraries is a common source of problems. Other obscure crashes could come from an improper environment setup.

      Delete