How do I connect to an Oracle cloud database within a C++ application?

Question

I have a cloud database with Oracle and I'm trying to learn how to execute SQL commands within my c++ application in Windows.

Here's what I've tried.

1) Using Instant Client (OCCI)

• I have tried to follow these docs posted by Oracle
• I downloaded instant client, unzipped, and put it under the directory called Oracle
• I downloaded the SDK for instant client and put it under the same directory
• I downloaded the wallet files and put them under network/admin directory
• I set the path variable to the directory Oracle/instant_client and created a user variable called ORACLE_HOME and set it to the same directory
• Created a TNS_ADMIN user variable and set it to network/admin
• Created an empty visual studio project and added the sdk to the additional include dependencies and added the sdk to the additional library directories under the project properties
• Added the .lib files to the additional dependencies under the project properties (linker -> input)
• Then I wrote this code

    Environment* env;
    Connection* conn;

    env = Environment::createEnvironment(Environment::DEFAULT);
    conn = env->createConnection ("username", "password", "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myserver111) (PORT=5521))(CONNECT_DATA = (SERVICE_NAME = bjava21)))");
    
    env->terminateConnection(conn);
    Environment::terminateEnvironment(env);

• It compiles, runs, and builds the environment successfully but an error occurs when it tries to create a connection

2) Using ODBC

• Downloaded the ODBC Oracle Driver
• Added a new data source name to the system dsn
• Tested connection successfully
• Abandoned efforts as I couldn't find helpful and simple docs to help me with my project

3) Using Oracle Developer Tools for Visual Studio

• I downloaded the developer tools and added them to visual studio
• Connected to my Oracle database using Server Explorer
• Was able to see my tables and data and modify them using the Server Explorer
• Was unable to find docs or be able to add code that allowed me to execute SQL queries

Update

• I have removed ORACLE_HOME and TNS_ADMIN as environment variables
• I've tried to connect to my remote database using Instant Client SDK but no luck
• I have tried the following and nothing has worked

createConnection("username", "password", "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myserver111) (PORT=5521))(CONNECT_DATA = (SERVICE_NAME = bjava21)))")
createConnection("username", "password", "//host:[port][/service name]")
createConnection("username", "password", "xxx_low")
createConnection("username", "password", "protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N")
createConnection("username", "password", "username/password@xxx_low")

• I'm able to connect to SQLPlus in a variety of ways but not in my c++ application

Error While Debugging:

Unhandled exception in exe: Microsoft C++ exception: oracle::occi::SQLException at memory location

Full Code

#include <occi.h>
using namespace oracle::occi;

int main() {
    Environment* env;
    Connection* conn;

    env = Environment::createEnvironment(Environment::DEFAULT);
    conn = env->createConnection("username", "password", "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myserver111) (PORT=5521))(CONNECT_DATA = (SERVICE_NAME = bjava21)))");

    env->terminateConnection(conn);
    Environment::terminateEnvironment(env);

    return 0;
}

Answer 1

With Instant Client in general:

• don't set ORACLE_HOME. This can have side effects.
• you don't need to set TNS_ADMIN since you put the unzipped network files in the default directory.

For cloud:

• In the app, use one of the network aliases from the tnsnames.ora file (eg. xxxx_low). You can see the descriptors have extra info that your hard coded descriptor is missing.

ODBC will be exactly the same. Once you have the wallet files extracted to the default network/admin subdirectory you just need to connect with the DB credentials and use a network alias from the tnsnames.ora file.

More info is in my blog post How to connect to Oracle Autonomous Cloud Databases.

Official doc is in Connect to Autonomous Database Using Oracle Database Tools