NOTE: I have edited the intro text here from the original source as a result of some discussions I've had. These discussions are ongoing so I can't post the results as of yet. (3-June-2009 approximately 5:00 PM).
I thought I would take an introductory look at using the Oracle Pro*C precompiler using Express Edition products. Here are the components I will use for this purpose (links valid at time of posting):
- Oracle Database 10g Express Edition (available here)
- Oracle Enterprise Linux (available here)
- Oracle Instant Client 11.1.0.7 Packages for Microsoft Windows 32-bit (available here)
- Instant Client Package – Basic
- Instant Client Package – SDK
- Instant Client Package – Precompiler
- Instant Client Package - SQL*Plus
- Microsoft Windows XP Professional 32-bit with Service Pack 3
- Microsoft Visual C++ 2008 Express Edition (available here)
- Windows SDK for Windows Server 2008 and .NET Framework 3.5 (available here)
For the purposes of this discussion you will need to have already installed (or have access to) Oracle Database with the HR sample schema. You will also need to have installed Visual C++ 2008 Express Edition and the Windows SDK on the machine you will use as your development machine. For a walkthrough of installing Visual C++ 2008 Express Edition and the Windows SDK, please see this link. Note that even though the SDK seems to be only for Windows Server 2008 (based on the name) it is supported on XP, Vista, and Windows Server.
In my environment I have installed Oracle Database 10g Express Edition on a host running Oracle Enterprise Linux. The host name is "oel02" (not especially clever, I realize). The Windows XP machine that I will use as the development machine is named "chepstow" (perhaps marginally more clever) and Visual C++ Express Edition and the Windows SDK are already installed. I have downloaded the four Instant Client packages listed above to the "c:\temp" directory on chepstow. The SQL*Plus package is not required; however, I find it convenient so I always install it. So, since I already have a database server and the Microsoft tools are installed, all that remains is to install the Instant Client packages.
Installing the Instant Client Packages
It is incredibly easy to install the Instant Client packages – simply unzip them! I chose to unzip them (on chepstow, my development machine) to the "c:\" directory and this created a new "c:\instantclient_11_1" directory and various sub-directories. I then added the following two directories to the system path:
- C:\instantclient_11_1
- C:\instantclient_11_1\sdk
NOTE: I added the two directories to the beginning of the system path and had no other Oracle products installed. See comments for more information about this. (Added 29 June 2009 approximately 11:30 AM)
Setting up the Pro*C Configuration File
I know it is easy to skip reading a README file, but it is important that you do read the PRECOMP_README file in the Instant Client root directory. Pro*C will, by default, look for a configuration file named "pcscfg.cfg" when it is invoked. In the environment that I have created (default installs of all products) Pro*C will want to find this file in the "c:\instantclient_11_1\precomp\admin" directory. However, if you look at your install (if you have done the same as me) you will notice there is no such directory! Therefore you should create this directory ("c:\instantclient_11_1\precomp\admin"). You should then copy the "pcscfg.cfg" file from the "c:\instantclient_11_1\sdk\demo" directory to the "c:\instantclient_11_1\precomp\admin" directory.
The "pcscfg.cfg" file will initially contain the following single line:
define=(WIN32_LEAN_AND_MEAN)
Below this line you add the following four lines:
sys_include=C:\PROGRA~1\MICROS~1.0\VC\include\sys
include=C:\PROGRA~1\MICROS~3\Windows\v6.1\Include
include=C:\PROGRA~1\MICROS~1.0\VC\include
include=C:\instantclient_11_1\sdk\include
Save the file and exit your editor.
Be sure to note that the directory names above are the "short" names to ensure they do not contain spaces. If the directory names contain spaces this will cause problems with the Pro*C precompiler. To help "translate" the directories above, here are the long versions (be sure you do not enter these):
sys_include=C:\Program Files\Microsoft Visual Studio 9.0\VC\include\sys
include=C:\Program Files\Microsoft SDKs\Windows\v6.1\Include
include=C:\Program Files\Microsoft Visual Studio 9.0\VC\include
include=C:\instantclient_11_1\sdk\include
You can find the short names by using "dir /x" in a command-prompt window.
Adding Directories to Visual Studio C++ 2008 Express Edition
Next you should add the Oracle Instant Client include and library directories to Visual Studio. To do this, simply perform the following steps:
- Select Tools –> Options to open the Options dialog
- Expand the "Projects and Solutions" node
- Click the "VC++ Directories" item
- Under "Show directories for:" select "Include files"
- Click underneath the last entry in the list (you should get a highlighted line with no text)
- Click the folder button to create a new line
- Enter "c:\instantclient_11_1\sdk\include" and press Enter
- Under "Show directories for:" select "Library files"
- Click underneath the last entry in the list (you should get a highlighted line with no text)
- Click the folder button to create a new line
- Enter "c:\instantclient_11_1\sdk\lib\msvc" and press Enter
- Click the OK button to save the changes
Create a New Project
WARNING: You should create your project in a directory (and path) that has no spaces in it. If you create the project in a directory or path with spaces you will receive errors during the precompile phase. I used "c:\temp" for this example.
Now create a new project in Visual Studio:
- Select File –> New Project to open the New Project dialog
- Select "Win32" as the project type
- Select "Win32 Console Application" under "Templates"
- Give the project a name (I used "proctest" in keeping with my clever naming tradition)
- I always choose to de-select "Create directory for solution" and click OK
- Click the "Next" button in the application wizard
- Click the "Empty project" checkbox under "Additional options"
- Click the "Finish" button
Create the Pro*C Source File
To create the Pro*C source file, perform the following steps:
- Right-click "Source Files" and select Add –> New Item… from the context menu
- Select "Code" under "Visual C++"
- Select "C++ File (.cpp)" under "Visual Studio installed templates" (note that you will not actually create C++ code in this example)
- Give the file a name such as "proctest.pc" and click "Add"
Here's the Pro*C source I used for this example (this is clearly sample code and lots is left out!):
/*
** suppress certain warnings
*/
#ifdef WIN32
#define _CRT_SECURE_NO_DEPRECATE 1
#endif
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>
EXEC SQL BEGIN DECLARE SECTION;
/*
** defines for VARCHAR lengths.
*/
#define UNAME_LEN 30
#define PWD_LEN 30
#define DB_LEN 48
#define FNAME_LEN 32
#define LNAME_LEN 32
/*
** variables for the connection
*/
VARCHAR username[UNAME_LEN];
VARCHAR password[PWD_LEN];
VARCHAR dbname[DB_LEN];
/*
** variables to hold the results
*/
int ctr;
int empid;
VARCHAR fname[FNAME_LEN];
VARCHAR lname[LNAME_LEN];
EXEC SQL END DECLARE SECTION;
/*
** declare error handling function
*/
void sql_error(char *msg)
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
/*
** Copy the username into the VARCHAR.
*/
strncpy((char *) username.arr, "hr", UNAME_LEN);
username.len = strlen("hr");
username.arr[username.len] = '\0';
/*
** Copy the password.
*/
strncpy((char *) password.arr, "hr", PWD_LEN);
password.len = strlen("hr");
password.arr[password.len] = '\0';
/*
** copy the dbname (using EZCONNECT syntax)
*/
strncpy((char *) dbname.arr, "oel02/XE", DB_LEN);
dbname.len = strlen("oel02/XE");
dbname.arr[dbname.len] = '\0';
/*
** register sql_error() as the error handler.
*/
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
/*
** Connect to database. Will call sql_error()
** if an error occurs when connecting.
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :dbname;
printf("\nConnected to ORACLE as user: %s\n\n", username.arr);
/*
** simple select statement
*/
EXEC SQL DECLARE emps CURSOR FOR
SELECT employee_id,
first_name,
last_name
FROM employees
ORDER BY last_name,
first_name;
/*
** open the cursor
*/
EXEC SQL OPEN emps;
/*
** when done fetching break out of the for loop
*/
EXEC SQL WHENEVER NOT FOUND DO break;
/*
** simple counter variable
*/
ctr = 0;
/*
** print a little header
*/
printf("Employee ID First Name Last Name\n");
printf("=========== ==================== =========================\n");
/*
** fetch all the rows
*/
for (;;)
{
EXEC SQL FETCH emps into :empid, :fname, :lname;
/*
** null-terminate the string values
*/
fname.arr[fname.len] = '\0';
lname.arr[lname.len] = '\0';
/*
** print the current values
*/
printf("%-13d%-22s%-25s\n", empid, fname.arr, lname.arr);
ctr++;
}
/*
** close the cursor
*/
EXEC SQL CLOSE emps;
/*
** provide simple feedback on how many rows fetched
*/
printf("\nFetched %d employees.\n", ctr);
/*
** disconnect from database
*/
EXEC SQL ROLLBACK WORK RELEASE;
/*
** have a nice day
*/
exit(EXIT_SUCCESS);
}
Add a Reference to the Generated C Source File
The output of the Pro*C precompiler is either C or C++ source code (C in this case). However, because we are working with only a Pro*C source file we need to tell Visual Studio about the file that will be generated. To do this we add a reference to the not yet generated file:
- Select Project –> Add New Item to open the Add New Item dialog
- Select "Code" under "Visual C++"
- Select "C++ File (.cpp)" under "Visual Studio installed templates"
- Type "proctest.c" in the "Name" textbox and click "Add"
- Next close the (empty) file after it is created
Add the Pro*C Library File to the Project
- Select Project –> <project name> Properties… to open the Property Pages dialog
- Expand the "Configuration Properties" node
- Expand the "Linker" node
- Click the "Input" item
- In the "Additional Dependencies" type "orasql11.lib" and click "OK" to save the changes
Add the Custom Build Step
In order for Visual Studio to be able to invoke the Pro*C executable (proc.exe) to create the C source code file, a custom build step needs to be created:
- Right-click "proctest.pc" in the Solution Explorer and select "Properties" from the context menu
- Select "Custom Build Step"
- For "Command Line" type "proc.exe $(ProjectDir)$(InputName).pc"
- For "Outputs" type "$(ProjectDir)$(InputName).c"
- Click "OK" to save the custom build step
This step will cause Visual Studio to invoke proc.exe on the input file (proctest.pc) and create an output file called "proctest.c" which will then be compiled as normal. This is really the key step in the whole process I suppose. This custom build step is the "integration" of Pro*C into Visual Studio.
Build the Sample and Verify
All the hard work is now done and it is time to build the sample!
- Select Build –> Build Solution
If all has gone well you should see output similar to the following in the output window:
proctest - 0 error(s), 0 warning(s)
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
If there are errors reported you will need to investigate and correct the cause.
Upon completion of a successful build, you can execute the program and verify the results:
C:\temp\proctest\Debug>proctest
Connected to ORACLE as user: hr
Employee ID First Name Last Name
=========== ==================== =========================
174 Ellen Abel
166 Sundar Ande
130 Mozhe Atkinson
[ snip ]
120 Matthew Weiss
200 Jennifer Whalen
149 Eleni Zlotkey
Fetched 107 employees.
C:\temp\proctest\Debug>
Conclusion
Whew! That's a lot of work! As I mentioned at the beginning of this post, this is intended to be an introductory look at using Pro*C and Visual C++ 2008 Express Edition. There is, of course, much more that Pro*C can do and this simple example of selecting from the "employees" table in the "hr" schema is exactly that: a simple example. It is not intended to be a complete tutorial but perhaps it will be helpful in working with Pro*C and Visual Studio if you choose to do so. You should be able to adapt the steps here to using the "full" version of Visual Studio or Oracle Client.
If you made it this far, thanks for stopping by. I hope this was helpful in some regard.
NOTE: Some comments below were recently deleted by me at the request of the poster. I have, therefore, deleted my responses to those comments as they made no sense on their own. (1-June-2009 approximately 1:10 PM)
