Luis Neri was kind enough to translate my "Getting Started with OCCI (Windows Version)" post into Spanish. Below is his translation of the original post. Thanks very much to Luis for this. However, please note that I do not speak Spanish, so I won't be able to respond to any comments in that language. - Mark La Oracle C++ Call Interface, también conocida como OCCI, es una API construida sobre otras API's de bajo nivel de Oracle. Uno de los objetivos de OCCI es ofrecer a los programadores de C++ una forma de acceso fácil a las bases de datos de Oracle en una forma similar a la que tienen los programadores de Java con "Java Database Connectivity (JDBC) ". Este documento trata de dar una vista rápida para empezar con esta tecnología, la cual puede ser incorporada a los desarrollos y aplicaciones GIS del SIRAN; si se desea, existe más información en la documentación en línea de Oracle. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm Este "Empezando con OCCI" tiene el objeto de dar un método para la puesta del ambiente el cual usa OCCI en los desarrollos con C++ bajo Windows para acceder a las bases de datos de Oracle. El ambiente El ambiente en que se realizarón las pruebas para la puesta en funcionamiento de esta tecnología es el que abajo se detalla, tenga en cuenta que a este ambiente se pueden hacer pequeñas adaptaciones para que funcione en el suyo en particular. Información importante Uno de las características más importantes al trabajar con OCCI es que se debe asegurar que todos los componentes del ambiente de desarrollo y de runtime están soportados en las combinaciones y tener las versiones correctas. Se hace un fuerte énfasis en esto, si usted no observa con atención esta recomendación seguramente encontrara problemas. Para lograr esto por favor encuentre las combinaciones correctas de las versiones en la pagina de Oracle Technology Network (OTN). Descargue los componentes correctos Al momento de escribir este documento se encontraron para el ambiente descrito arriba los siguientes componentes Estos componentes se deben descargar en la máquina de desarrollo. Yo lo hice en el escritorio. Instalación de Instant Client La instalación es tan simple como descomprimir el archivo, descomprímalo sobre C:\ , el resultado será una carpeta C:\instantclient_11_1 con las subcarpetas "sdk", "vc8" y "vc7", las cuales no serán utilizadas en el desarrollo sobre nuestro ambiente. Instalación del OCCI Package Al igual que la instalación de Instant Client, los paquetes Occi pueden ser descomprimidos, pero sin embargo en vez de crear un directorio sobre c:\, los descomprimí sobre el escritorio, una vez descomprimidos, me desvié un poco de lo quel archivo occivc9_111060_readme.txt dice, y realice los siguiente: Cree un directorio "vc9" dentro del directorio "sdk" como sigue: C:\instantclient_11_1\sdk\lib\msvc\vc9 Cree una carpeta "vc9" dentro de "instantclient_11_1" como sigue: C:\instantclient_11_1\vc9 Borre el archivo oraocci11.dll y oraocci11.sym del directorio C:\instantclient_11_1. Estos archives no están hechos para construir o compilar con Visual Studio 2008 y como se dijo anteriormente es importante que se empaten las versiones. Se extrajeron los archivos OCCI en la carpeta del escritorio moviendo los dos archivos a la carpeta C:\instantclient_11_1\sdk\lib\msvc\vc9 antes creada: de la misma carpeta en el escritorio, se mueven los siguientes archivos a la carpeta C:\instantclient_11_1\vc9 antes creada: Finalmente se borra el archivo oraocci11.lib de: C:\instantclient_11_1\sdk\lib\msvc Otra vez este archivo no es compatible con nuestro ambiente. Despues de realizar estos pasos, los archivos .lib deben estar bajo la carpeta C:\instantclient_11_1\sdk\lib\msvc y los archivos .dll y .manifest deben estar bajo la carpeta C:\instantclient_11_1. Esto pasos parecen trabajo extra, pero resultan de una separación completa de las varias versiones OCCI y lo hace más fácil y explicito. Para especificar que versión de librerías OCCI usar, añada estas carpetas al path del sistema. Estas dos carpetas deben estar al inicio del path: C:\instantclient_11_1\vc9;C:\instantclient_11_1;{y lo demás del path…} Configuración de Visual Studio El ambiente Windows ha sido configurado para el uso de los nuevos paquetes OCCI y Instan Client (los adecuados para nuestro ambiente), pero antes de empezar un desarrollo en Visual Studio, es necesario establecer algunas opciones. Sin estas opciones Visual Studio es incapaz de encontrar los archivos correctos y construir aplicaciones. Hay dos opciones las cuales necesitan ser especificadas: UsandoVisual C++ 2008, las rutas de menu donde se especifican estas opciones son: Cree un proyecto simple de prueba Ya que se ha hecho todo la puesta en funcionamiento el ambiente está configurado!, Utilice la siguiente proyecto como prueba para verificar que todo funciona como se espera. Otra vez, este es un simple ejemplo para verificar que las cosas funcionan correctamente. No es un templeate de desarrollo. Cree en Visual C++ 2008 un proyecto seleccionando File –> New –> Project… del menú principal, seleccione "Win32" como tipo de proyecto, seleccione "Win32 Console Application", dele un nombre al proyecto (yo usé prueba_occi), seleccione un carpeta donde guardar deseleccione "Create directory for solution", y presione OK. Presione Next en el Wizard, deseleccione Precompiled header, presione Empty project, y presione Finish. En el explorador de la solucion, click-izquierdo en Header Files, seleccione Add, seleccione New Item… En Add New Item, seleccione Header File (.h), introduzca Employees.h (o cualquier otro nombre) en nombre y presione Add. /* #include <occi.h> using namespace oracle::occi; class Employees { void List(); private: string user; en Solution Explorer, click-izquierdo en Source Files, seleccione Add, seleccione New Item… en Add New Item, seleccione C++ File (.cpp), introduzca Employees.cpp ((o cualquier otro nombre) en nombre y presione Add. Este es el contenido de mi archivo en el sistema: /* #include "Employees.h" using namespace std; int main (void) Employees *pEmployees = new Employees(); pEmployees->List(); delete pEmployees; cout << "ENTER to continue..."; cin.get(); return 0; Employees::Employees() user = "hr"; env = Environment::createEnvironment(Environment::DEFAULT); try exit(EXIT_FAILURE); Employees::~Employees() Environment::terminateEnvironment (env); void Employees::List() Statement *stmt = NULL; try if (stmt) rs = stmt->executeQuery(); if (rs) while (rs->next()) { cout << endl; stmt->closeResultSet(rs); con->terminateStatement(stmt); Antes de construir el ejemplo (build), se necesita añadir la librería OCCI a la lista de entradas del linker: Seleccione Project –> prueba_occi Properties... del menu (Sustituya el nombre por el proio si es necesario) Expanda el nodo Configuration Properties, expanda el nodo Linker, seleccione Input item, introduzca "oraocci11d.lib" para debug build o "oraocci11.lib" para release build. Seleccione Build –> Build Solution del menú para construir la solución. Si todo está puesto correctamente no debería haber errores. Si existen error busque donde pueden estar y corrija. La pantalla de ejecución seria como esta: ID FIRST NAME LAST NAME [ snip ] 120 Matthew Weiss ENTER to continue... Si eres nuevo en el uso de OCCI sobre Windows con Visual Studio 2008, quizá el ejemplo de arriba puede ser de ayuda al inicio.
* A simple OCCI test application
* This file contains the Employees class declaration
*/
#include <iostream>
#include <iomanip>
using namespace std;
public:
Employees();
virtual ~Employees();
Environment *env;
Connection *con;
string passwd;
string db;
};
* A simple OCCI test application
* This file contains the Employees class implementation
*/
using namespace oracle::occi;
{
/*
* create an instance of the Employees class,
* invoke the List member, delete the instance,
* and prompt to continue...
*/
}
{
/*
* connect to the test database as the HR
* sample user and use the EZCONNECT method
* of specifying the connect string. Be sure
* to adjust for your environment! The format
* of the string is host:port/service_name
*/
passwd = "hr";
db = "oel01:1521/OEL11GR1.SAND";
{
con = env->createConnection(user, passwd, db);
}
catch (SQLException& ex)
{
cout << ex.getMessage();
}
}
{
env->terminateConnection (con);
}
{
/*
* simple test method to select data from
* the employees table and display the results
*/
ResultSet *rs = NULL;
string sql = "select employee_id, first_name, last_name " \
"from employees order by last_name, first_name";
{
stmt = con->createStatement(sql);
}
catch (SQLException& ex)
{
cout << ex.getMessage();
}
{
try
{
stmt->setPrefetchRowCount(32);
}
catch (SQLException& ex)
{
cout << ex.getMessage();
}
{
cout << endl << setw(8) << left << "ID"
<< setw(22) << left << "FIRST NAME"
<< setw(27) << left << "LAST NAME"
<< endl;
cout << setw(8) << left << "======"
<< setw(22) << left << "===================="
<< setw(27) << left << "========================="
<< endl;
cout << setw(8) << left << rs->getString(1)
<< setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
<< setw(27) << left << rs->getString(3)
<< endl;
}
}
}
}
====== ==================== =========================
174 Ellen Abel
166 Sundar Ande
130 Mozhe Atkinson
105 David Austin
204 Hermann Baer
116 Shelli Baida
167 Amit Banda
172 Elizabeth Bates
200 Jennifer Whalen
149 Eleni Zlotkey
Tuesday, July 28, 2009
Empezando con OCCI
Saturday, July 11, 2009
SQL*Plus, EZCONNECT, Password Prompt, and ORA-12504
I use SQL*Plus a great deal and I find the "Easy Connect Naming Method" a convenient option.
Before you get any further in this post, let me be perfectly clear that this post only addresses ORA-12504 in the context of using the "Easy Connect Naming Method" with SQL*Plus and password prompting. It does not address ORA-12504 outside of this context. The documentation (see the link above) has several examples of using and configuring this method, so I won't go into them all here. This method is also known as "EZCONNECT" presumably because "Z" in the American dialect is pronounced like "zee" rather than "zed".
You are likely already familiar with the password prompting behavior of SQL*Plus. For example, when using a tnsnames.ora file alias to connect to a database, if the password is not specified, SQL*Plus will prompt you for it (i.e. "Enter password:"):
[oracle@liverpool ~]$ sqlplus hr@orademo
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:42:08 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Nice and easy.
So, let's try the equivalent using EZCONNECT:
[oracle@liverpool ~]$ sqlplus hr@liverpool:1521/DEMO
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:43:20 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Enter user-name:
Hmm... well, that's interesting. Rather than getting prompted for the password we get an error (ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA) and then we get presented with the "Enter user-name:" prompt again. Funny that the error is saying the SERVICE_NAME was not specified, but it sure is there - DEMO in this case. This is giving us a clue though...
OK, so let's go ahead and specify the password:
[oracle@liverpool ~]$ sqlplus hr/hr@liverpool:1521/DEMO
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:44:02 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Much better, it works, but what if you (reasonably) don't want to specify the password on the command-line?
Taking a look at the documentation (really - that stuff does come in handy!) for the CONNECT SQL*Plus command we can see this:
username[/password] [@connect_identifier]
Pay particular attention that the "/" precedes the password. In the case of using EZCONNECT we have:
sqlplus hr@liverpool:1521/DEMO
So, in this case the "/" precedes the SERVICE_NAME and not the password. How should we tell SQL*Plus that the "/" is not preceding the password? We quote the connection string!
[oracle@liverpool ~]$ sqlplus hr@\"liverpool:1521/DEMO\"
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:45:43 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Now that's more like it! Notice that I used "\" (backslash) to escape the double-quote characters on the command-line (I'm using the bash shell for this).
If you are already in SQL*Plus you do not need to use the backslash to escape the double-quote characters:
[oracle@liverpool ~]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:46:31 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> connect hr@"liverpool:1521/DEMO"
Enter password:
Connected.
SQL>
This same technique can also be used for privileged connections:
[oracle@liverpool ~]$ sqlplus sys@\"liverpool:1521/DEMO\" as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 11 11:47:23 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
If you use SQL*Plus as much as I do, perhaps this will be a handy tip.
Monday, July 06, 2009
Getting Started with OCCI (Windows Version)
The Oracle C++ Call Interface, also known as OCCI, is an application programming interface (API) built upon the Oracle Call Interface (OCI - another lower level API from Oracle). One of the goals of OCCI is to offer C++ programmers easy access to Oracle Database in a fashion similar to what Java Database Connectivity (JDBC) affords Java developers. If you would like to learn more about what OCCI is (and isn't), pay a visit to the OCCI documentation on Oracle Technology Network (OTN) here:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28390/toc.htm
My goal with this "Getting Started" post is to give you one method of setting up an environment in which you can use OCCI to develop C++ applications under Windows that access Oracle Database. I am not in any way covering all possible scenarios or delving deep into OCCI itself. Please note that the database itself can be on any supported host.
The Environment
Your environment is likely to differ from mine; however, it is important to be familiar with the various components in the environment used here so that you can make adaptations as necessary for your specific environment.
- Oracle Database Server/Host: oel01 (Oracle Enterprise Linux 32-bit server)
- Oracle Database: SID value of OEL11GR1, Service Name value of OEL11GR1.SAND, version 11.1.0.7
- Development Machine: Hostname of CHEPSTOW, Windows XP Professional 32-bit
- Development IDE: Microsoft Visual C++ 2008 Express Edition (Windows SDK also installed)
- Oracle Client: Oracle Instant Client with OCCI
Important Information
One of the most crucial attributes of working with OCCI is that you must ensure that all of the components of the development environment and the runtime environment are supported combinations and correct versions. I can not emphasize this enough. If you deviate from this, you will almost certainly find trouble! In order to find the correct combinations of products and versions, see the following links on OTN:
- Oracle C++ Call Interface
- OCCI Downloads page
- Oracle Database Instant Client
- Instant Client Downloads
- Instant Client Downloads for Windows 32-bit
Download the Correct Packages
At the time of this writing, the following are the component versions supported for the environment listed above:
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
- Instant Client Version 11.1.0.6
From the download links above, you should download the following components to your development machine. I downloaded them to the C:\Temp directory.
- OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit]) - occivc9win32_111060.zip
- Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
- Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip
- Instant Client Package - SQL*Plus: instantclient-sqlplus-win32-11.1.0.6.0.zip (optional, but I always install it)
Install the Instant Client Packages
Installing the Instant Client packages is simply a matter of unzipping them – not much to wrong here! I unzipped them all to the C:\ directory on Chepstow. This resulted in a new top-level directory - C:\instantclient_11_1 with "sdk", "vc8", and "vc7" directories underneath. The "vc8" and "vc7" directories should be ignored in the context of the environment created here.
Install the OCCI Package
Much like the Instant Client packages, the OCCI package should be unzipped; however, rather than unzipping it to the C:\ directory, I unzipped it to the C:\Temp directory. Once unzipped, review the occivc9_111060_readme.txt file for information; however, I deviate from the directories listed in the file.
I create a "vc9" directory under the "sdk" directory as follows:
C:\instantclient_11_1\sdk\lib\msvc\vc9
I create a "vc9" directory under the "instantclient_11_1" directory as follows:
C:\instantclient_11_1\vc9
I delete the oraocci11.dll and oraocci11.sym files from the C:\instantclient_11_1 directory. These files are not built/linked with the runtime libraries used by Visual Studio 2008 and, as mentioned above, it is critical that component versions match!
From the extracted OCCI files in the C:\Temp directory, move the following two files to the C:\instantclient_11_1\sdk\lib\msvc\vc9 directory previously created:
- oraocci11.lib
- oraocci11d.lib
From the extracted OCCI files in the C:\Temp directory, move the following four files to the C:\instantclient_11_1\vc9 directory previously created:
- oraocci11.dll
- oraocci11.dll.manifest
- oraocci11d.dll
- oraocci11d.dll.manifest
Finally, delete the oraocci11.lib file from the C:\instantclient_11_1\sdk\lib\msvc directory. Again, this file is not compatible with the environment created here.
After performing these steps, the .lib files should only be in directories under C:\instantclient_11_1\sdk\lib\msvc and the .dll and .manifest files should only be in directories under the C:\instantclient_11_1 directory. While this may seem like extra unneeded work, it results in complete separation of the various versions of the OCCI components making it easier (and explicit) which version is to be used.
To specify which version of the OCCI libraries are used, add the directory to the system path. You also add the the Instant Client directory to the path. Both of these directories should be added to the beginning of the system path:
C:\instantclient_11_1\vc9;C:\instantclient_11_1;{rest of path follows…}
Configure Visual Studio
The Windows environment has been configured to use the new OCCI and Instant Client packages but before you can begin developing in Visual Studio, you need to set a few options. Without these options Visual Studio will be unable to find the correct files and build your applications. There are two options that need to be specified:
- Include files – allows Visual Studio to find the header files for OCCI
- Library files – allows Visual Studio to find the library files for OCCI
Using Visual C++ 2008 Express Edition, the menu paths to specify these options are as follows:
- Tools –> Options… Expand "Projects and Solutions" node, select "VC++ Directories", under "Show directories for:" select "Include files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\include" and press enter
- Under "Show directories for:" select "Library files", double-click under the last entry to open a new box to enter a path, enter "C:\instantclient_11_1\sdk\lib\msvc\vc9" and press enter
- Click OK to save the settings
Create a Simple Test Project
All the setup work is now complete and the environment is configured! If needed, you can use the following (very!) basic application as a simple test to verify things are working as expected. Again, this is a simple example only to verify things are setup correctly. It is not intended to be a complete template for "proper" code development, etc.
Create the Visual C++ 2008 Express Edition project by selecting File –> New –> Project… from the main menu, select "Win32" as the project type, select "Win32 Console Application", give the project a name (I used OCCITest), select a location (I used C:\Projects), I unchecked "Create directory for solution", and then click OK.
Click Next in the Application Wizard, uncheck Precompiled header, click Empty project, and click Finish.
In Solution Explorer, right-click Header Files, select Add, select New Item…
In Add New Item, select Header File (.h), enter Employees.h (or any name you prefer) in Name, and click Add.
Here's the content of the file on my system:
/*
* A simple OCCI test application
* This file contains the Employees class declaration
*/
#include <occi.h>
#include <iostream>
#include <iomanip>
using namespace oracle::occi;
using namespace std;
class Employees {
public:
Employees();
virtual ~Employees();
void List();
private:
Environment *env;
Connection *con;
string user;
string passwd;
string db;
};
In Solution Explorer, right-click Source Files, select Add, select New Item…
In Add New Item, select C++ File (.cpp), enter Employees.cpp (or any name you prefer) in Name, and click Add.
Here's the content of the file on my system:
/*
* A simple OCCI test application
* This file contains the Employees class implementation
*/
#include "Employees.h"
using namespace std;
using namespace oracle::occi;
int main (void)
{
/*
* create an instance of the Employees class,
* invoke the List member, delete the instance,
* and prompt to continue...
*/
Employees *pEmployees = new Employees();
pEmployees->List();
delete pEmployees;
cout << "ENTER to continue...";
cin.get();
return 0;
}
Employees::Employees()
{
/*
* connect to the test database as the HR
* sample user and use the EZCONNECT method
* of specifying the connect string. Be sure
* to adjust for your environment! The format
* of the string is host:port/service_name
*/
user = "hr";
passwd = "hr";
db = "oel01:1521/OEL11GR1.SAND";
env = Environment::createEnvironment(Environment::DEFAULT);
try
{
con = env->createConnection(user, passwd, db);
}
catch (SQLException& ex)
{
cout << ex.getMessage();
exit(EXIT_FAILURE);
}
}
Employees::~Employees()
{
env->terminateConnection (con);
Environment::terminateEnvironment (env);
}
void Employees::List()
{
/*
* simple test method to select data from
* the employees table and display the results
*/
Statement *stmt = NULL;
ResultSet *rs = NULL;
string sql = "select employee_id, first_name, last_name " \
"from employees order by last_name, first_name";
try
{
stmt = con->createStatement(sql);
}
catch (SQLException& ex)
{
cout << ex.getMessage();
}
if (stmt)
{
try
{
stmt->setPrefetchRowCount(32);
rs = stmt->executeQuery();
}
catch (SQLException& ex)
{
cout << ex.getMessage();
}
if (rs)
{
cout << endl << setw(8) << left << "ID"
<< setw(22) << left << "FIRST NAME"
<< setw(27) << left << "LAST NAME"
<< endl;
cout << setw(8) << left << "======"
<< setw(22) << left << "===================="
<< setw(27) << left << "========================="
<< endl;
while (rs->next()) {
cout << setw(8) << left << rs->getString(1)
<< setw(22) << left << (rs->isNull(2) ? "n/a" : rs->getString(2))
<< setw(27) << left << rs->getString(3)
<< endl;
}
cout << endl;
stmt->closeResultSet(rs);
}
con->terminateStatement(stmt);
}
}
Before you can build the sample, you need to add the OCCI library to the input list for the linker:
Select Project –> OCCITest Properties... from the menu (substitute your project name if different)
Expand Configuration Properties node, expand Linker node, select Input item, enter "oraocci11d.lib" for a debug build or "oraocci11.lib" for a release build.
Select Build –> Build Solution from the menu to build the solution. If everything is setup correctly, there should be no errors during the build. If you receive errors, investigate and correct them.
Executing the sample should result in output as follows:
ID FIRST NAME LAST NAME
====== ==================== =========================
174 Ellen Abel
166 Sundar Ande
130 Mozhe Atkinson
105 David Austin
204 Hermann Baer
116 Shelli Baida
167 Amit Banda
172 Elizabeth Bates
[ snip ]
120 Matthew Weiss
200 Jennifer Whalen
149 Eleni Zlotkey
ENTER to continue...
If you are new to using OCCI on Windows with Visual Studio, perhaps the above will be helpful in getting started!
