Introduction
This article is Part 2 of a three part series showing how to build a custom data source class
for use with the Ultimate Grid control. The custom data source will use Oracle Call Interface (OCI)
to supply the data for the grid, from an Oracle database.
In Part 1, we built the Ultimate Grid control as an external DLL so it can
be included in the data source class.
In Part 2, the data source class will be built. We will be setting up the OCI environment, obtaining metadata from the
database, retrieving a result list, and providing the data for the Ultimate grid control to display.
In Part 3, we will put it all together in a sample application that will use the
grid control DLL built in part 1, and the data source class built in part 2, to
develop a sample application displaying data from the EMP table of the scott sample schema, in the Ultimate Grid Control.
Background
If you are faced with the task of browsing a large amount of data stored in an Oracle database
from a Windows client, as I was, then you know how challenging this can be. It's not so much
that it can't be done, as there are so many different ways to do it. Oracle offers several integration
products, Oracle Objects for OLE (OO4O), the data provider for .NET, the Pro*C pre-compiler, and
OCI. I decided to go with OCI for this application because it offered a good trade-off between control and ease of use.
Points of Interest
One of the things that has always caused me problems in the past when extracting data from an
Oracle database, has been the handling of Oracle DATE fields. Typically, the default format is set to
display dates as 20-APR-2014 for instance. I much prefer 04/20/2014. In the past, I would use the
TO_CHAR function to reformat the date in the SELECT statement. While working on this project,
I came across some Oracle documentation that said for Windows clients, the format is controlled
by the NLS_DATE_FORMAT environment variable. So I set it to "MM/DD/YYYY" on my development
machine and now I get the format I want every time, without having to code the TO_CHAR function.
I used this approach when coding the data source class. Instead of telling the grid to expect
a DATE field, I have specified the column type as SQLT_STR. This results in the database converting
the DATE field for me, using the format I specified in the NLS_DATE_FORMAT environment variable.
The grid doesn't have to be concerned with how to handle an Oracle DATE field, and I always get
the date formatted the way I want it.
Building the Data Source Class
To develop the data source class, I started by deriving a class from the CUGDataSource class provided by the Ultimate Grid product. This class has many virtual functions defined giving a very good idea of what needs to be implemented. There are also several sample programs showing data source implementations for other sources. I used the one for SQL Server as a basic guide.
The definition of the class was put into a header file named ocicalls.h, with the following virtual functions from the base class implemented:
public:
COci(void);
~COci(void);
virtual int Open(LPCTSTR name,LPCTSTR option);
virtual BOOL IsOpen();
virtual int SetPassword(LPCTSTR user,LPCTSTR pass);
virtual int Close();
int GetCell(int col,long row,CUGCell *cell);
int SetCell(int col,long row,CUGCell *cell);
virtual long GetNumRows();
virtual int GetNumCols();
virtual int GetColName(int col,CString * string);
virtual int GetColType(int col,int *type);
int SetStatement(CString SqlStatement);
In addition, some internal functions are needed to support OCI:
private:
int initialize();
int logon();
int describe_column(int numcols);
int get_row_count();
int get_result_list();
CString GetOciError();
CString GetTableName(void);
The Oracle Call Interface uses pointers to handle in its implementation. The starting point is the OCI Environment handle, which is returned by a call to OCIEnvCreate. This call initializes the OCI libraries and sets up the environment.
Other handles to be used are the server and server context handles, an error handle, a statement handle, a describe handle and a session handle. Pointer variables are defined in ocicall.h:
OCIEnv *m_envhp;
OCIServer *m_srvhp;
OCIError *m_errhp;
OCISvcCtx *m_svchp;
OCIStmt *m_stmhp;
OCIDescribe *m_dschp;
OCISession *m_authp;
desc_parms *m_Parms;
The use of each will be explained as we look at the code itself.
In order to store the column attributes such as column name, column type, etc. and to store the data values returned by the SQL SELECT statement, I defined a structure name desc_parms, because these attributes are supplied by the OCI implicit describe functionality. The structure is defined like this:
typedef struct
{
char *ColName;
ub2 collen;
ub2 coltyp;
ub1 precision;
sb1 scale;
void *pValue;
sb4 value_size;
ub2 rlenp;
sb2 indp;
OCIDefine *defnpp;
} desc_parms;
An array of structures will be used, one occurrence for each column. The number of columns will first be determined, and then
the proper amount of memory allocated dynamically.
One final point on the ocicalls.h file, remember in part 1 I mentioned I would be using a define similar to the one the developers of the Ultimate Grid used. This will enable this header file to be used both by the project to build the DLL and any project using it. The define looks like this:
#ifndef OCI_CLASS_DECL
#ifdef _BUILD_OCI_INTO_EXTDLL
#define OCI_CLASS_DECL AFX_CLASS_EXPORT
#elif defined _LINK_TO_OCI_IN_EXTDLL
#define OCI_CLASS_DECL AFX_CLASS_IMPORT
#else
#define OCI_CLASS_DECL
#endif
#endif
The class definition then is done this way: class OCI_CLASS_DECL COci : public CUGDataSource, and the preprocessor definition controls whether OCI_CLASS_DECL gets defined as AFX_CLASS_EXPORT or AFX_CLASS_IMPORT.
When building the DLL, we add a preprocessor definition of _BUILD_OCI_INTO_EXTDLL to export the functions. In any project where we are going to use the DLL, we add a preprocessor definition of _LINK_TO_OCI_IN_EXTDLL.
The implementation of the class is done in the ocicalls.cpp file. There is a lot going on here so I’m going to cover the OCI related functions first and then cover the Ultimate Grid related functions.
Using the Oracle Call Interface Routines
There are several steps involved in using OCI. First, as mentioned above, the OCI libraries and environment have to be initialized. Next all the handles required for the functions we will be using have to be allocated. Then the application has to be initialized, a connection to the database established and a session created. One of the virtual functions I implemented in my data source class is the Open() function. In this function I take care of all of these tasks, as well as those required to obtain the data for the grid.
The call to OCIEnvCreate is done in the initialize function. This sets up the OCI environment and no other OCI calls can be done until this has happened. This function, if successful, gives us a pointer to the environment handle, stored in the pointer variable m_envhp. Once we have obtained the pointer to this handle, we can allocate the server and service context handles, and the error handle.
The server handle is used in the call to OCIServerAttach. This is when the actual connection to the Oracle database is made. In addition to the server handle, we also pass the error handle, a text string containing the Oracle service name we want to establish a connection with, the length of the service name string, and the mode value OCI_DEFAULT is used. The only other mode option is OCI_CPOOL, to use connection pooling. But since I’m only using one connection, I went with the default mode.
A member variable m_Service stores the Oracle service name. This has to be obtained from the calling process, and having a value supplied for this parameter is checked at the top of the Open() function. This value is the address name as defined in the TNSNAMES.ORA file.
In my case, the entry in
TNSNAMES.ORA looks like this:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl.home)
)
)
The calling process would supply ORCL as the service name. This parameter is not case sensitive and could also be entered as orcl.
When successful, this call initializes the server handle, which is then associated to the service context handle, by a call to OCIAttrSet. Once both these steps have been carried out, it is then possible to make OCI calls to the Oracle database.
Here is the complete code for the Initialize() function:
int COci::initialize()
{
int retval = 0;
ub4 service_len = (ub4)strlen(m_Service);
retval = OCIEnvCreate((OCIEnv **)&m_envhp,
(ub4)OCI_DEFAULT,
(void *)0, (void * (*)(void *, size_t))0,
(void * (*)(void *, void *, size_t))0,
(void (*)(void *, void *))0,
(size_t)0, (void **)0);
if (retval != OCI_SUCCESS)
return -1;
retval = OCIHandleAlloc ((dvoid *) m_envhp, (dvoid **) &m_svchp, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);
retval = OCIHandleAlloc ((dvoid *) m_envhp, (dvoid **) &m_srvhp, OCI_HTYPE_SERVER,
(size_t) 0, (dvoid **) 0);
retval = OCIHandleAlloc ((dvoid *) m_envhp, (dvoid **) &m_errhp, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);
retval = OCIServerAttach(m_srvhp, m_errhp, (text *)m_Service, service_len, OCI_DEFAULT);
if (retval == OCI_SUCCESS)
{
(void) OCIAttrSet ((dvoid *) m_svchp, OCI_HTYPE_SVCCTX, (dvoid *)m_srvhp,
(ub4) 0, OCI_ATTR_SERVER, (OCIError *) m_errhp);
}
else
retval = OCI_ERROR;
return retval;
}
At this point, I feel I need to say something about error handling in an OCI program. One of the handles we had to allocate was the error handle. This handle was then used as a parameter in the call to OCIServerAttach(). OCI uses the error handle to store information in the case of an OCI error. The error information available includes the all-to-familiar Oracle error codes. A call to an OCI function will return OCI_ERROR in the case of an error condition. This return code can then be examined and an error handler called.
The Open() function can result in success, a missing or invalid parameter, or an OCI error during a call to one of the OCI functions. A return code is used to indicate which of these conditions occurs. I used a return code of 9 to indicate an OCI error has occurred. The calling routine is then able to call my error handler GetOciError() to obtain the error code and description.
Here is the code for the error handler:
CString COci::GetOciError()
{
text errbuf[512];
sb4 errcode = 0;
CString OciErrorMsg;
int x;
(void) OCIErrorGet ((dvoid *)m_errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
if (errcode == 1406) {
for (x=0; x<m_cols; x++)
{
if (m_Parms[x].indp == -2)
OciErrorMsg.Format("Data Truncation on column %d, exceeds max size", x);
else
if (m_Parms[x].indp > 0)
OciErrorMsg.Format("Data Truncation on column %d, actual size %d",
x, m_Parms[x].indp);
else
OciErrorMsg.Format("Data truncation on column %d reported, but no size indicator", x);
}
}
else
OciErrorMsg.Format("%s", (char *)errbuf);
return OciErrorMsg;
}
Let’s defer the discussion of why we need special handling of the ORA-1406 error until we get into the discussion of how we obtain data for the grid.
Getting back to the Open() function, the next routine called is the logon() function. I separated these calls from the initialize routine because the OCI calls to initialize the application only need to be done once, but I could foresee the need to have multiple logon sessions.
In this function, we allocate the authentication handle, associate the Oracle user ID and password with this handle, using calls to OCIAttrSet, and then we establish a session using the OCISessionBegin call. If all goes well up to this point, we now have signed on to the Oracle database as the ID supplied by the calling routine.
int COci::logon()
{
int retval = 0;
(void) OCIHandleAlloc ((dvoid *) m_envhp, (dvoid **)&m_authp,
(ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
(void) OCIAttrSet ((dvoid *)m_authp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)m_UserID, (ub4)strlen(m_UserID),
OCI_ATTR_USERNAME, m_errhp);
(void) OCIAttrSet ((dvoid *)m_authp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)m_Password, (ub4)strlen(m_Password),
OCI_ATTR_PASSWORD, m_errhp);
retval = OCISessionBegin (m_svchp, m_errhp, m_authp, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT);
if (retval == OCI_SUCCESS)
{
(void) OCIAttrSet ((dvoid *) m_svchp, (ub4) OCI_HTYPE_SVCCTX,
(dvoid *) m_authp, (ub4) 0,
(ub4) OCI_ATTR_SESSION, m_errhp);
}
else
retval = OCI_ERROR;
return retval;
}
Now we start to get into the heavy duty OCI stuff. One of the things Ultimate Grid likes to know about the data to be presented is the number of rows. There are several ways to obtain this value, but they all involve executing the SQL query to obtain the result set, and then some other function to obtain the row count from the result set. I didn’t really like this approach, so I decided to do a SQL query to obtain the row count. The SQL statement select count(*) from table was used to accomplish this. In a real world application, you would also have to replicate any where clause to be used to obtain the result set. But for purposes of this article, I’m only doing a select * from table. Using this approach, I can obtain and store the actual row count for future reference.
If you recall from our discussion of ocicalls.h, one of the virtual functions from CUGDataSource I decided to implement was the GetNumRow() function. I use a member variable m_rows to store this value so the grid has access to it. The function get_row_count() was used to obtain this value. This is accomplished by a series of OCI calls. These calls will be very similar to how we will obtain the data for the grid, so a discussion of how it all works seems appropriate at this time.
It takes three OCI calls to obtain the row count; OCIStmtPrepare(), OCIDefineByPos() and OCIStmtExecute.
OCIStmtPrepare is used to initialize the settings required for use when we bind values returned by the database to our program variables. In simple terms, we pass OCI the text of the SQL statement we plan to execute, and it returns a statement handle to be used in subsequent calls.
OCIDefineByPos associates an item from the SQL statement’s select list with a data buffer used to store the value retrieved when the statement is executed. The member variable definition int m_rows. Will be used as the data buffer for storing the value returned by our SQL statement. The first parameter is the statement handle returned by our call to OCIStmtPrepare. The second is a pointer to a define handle, used to store the define information for the column being described. I don’t need this information in this case, so I have set it to NULL. The third parameter is our error handle pointer variable. The fourth parameter is the position of the column being described within the select list. In this case, there is only one column, the count(*) field, so it is set to 1. The position parameters are 1-based, not zero-based. The fifth parameter is a pointer to our data buffer, the sixth gives the size of the buffer, and the seventh gives the data type of the value being returned. The eighth parameter is a pointer to an indicator variable. I have set it to a null value, because I don’t need it in this case, but we will have much more to say about indicator variables when we describe the actual data columns to be retrieved for use by the grid. The same applies to the remaining parameters. I have set them to nulls or default values because they are not needed in this situation.
Simply put, the OCIDefineByPos tells OCI where we want to store the value returned by the SQL statement specified in the call to OCIStmtPrepare.
In the call to OCIStmtExecute, we see all of the previous steps come together and actually retrieve data from the Oracle database.
The service context handle, used to define our session with the database, is the first parameter used in this call. We then have the statement handle, initialized by OCIStmtPrepare, and used by OCIDefineByPos, as the second parameter. Our error handle is then passed. The fourth parameter tells OCI how many rows to fetch in each execution of the statement. When this parameter is non-zero for SQL Select statements, defines are required for the statement handle, which we did in our call to OCIDefineByPos. The next two parameters deal with snapshots to provide consistent views of the data base when multiple server contexts are being used. They don’t apply in this case, so they are set to nulls. The final parameter is the mode parameter, and by setting this to OCI_DEFAULT, we are telling OCI we want the statement to actually be executed and to store implicit describe information for the select list. This doesn’t have much bearing on this statement, but again, when we get to the statement used to retrieve the actual table data, we will have a more lengthy discussion of this parameter.
Upon successful execution of this call, m_rows will contain the number of rows we are expecting the grid to contain. Here is the complete code of this function:
int COci::get_row_count()
{
sword rc = OCI_SUCCESS;
OCIDefine *defnp = NULL;
char sql_count[100];
strcpy(sql_count, "select count(*) from ");
strcat(sql_count, m_Table);
rc = OCIStmtPrepare(m_stmhp, m_errhp, (text *)sql_count, (ub4)strlen(sql_count)+1,
OCI_NTV_SYNTAX, OCI_DEFAULT);
if (rc != OCI_SUCCESS)
return OCI_ERROR;
rc = OCIDefineByPos(m_stmhp, &defnp, m_errhp, (ub4) 1,
(void *) &m_rows, (sb4) sizeof(m_rows), SQLT_INT,
(void *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);
if (rc != OCI_SUCCESS)
return OCI_ERROR;
rc = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, (ub4) 1, (ub4) 0,
(OCISnapshot *) 0, (OCISnapshot *) 0, OCI_EXACT_FETCH);
if (rc != OCI_SUCCESS)
return OCI_ERROR;
return rc;
}
Upon successful execution of this function, the member variable m_rows, the fifth parameter in the OCIDefineByPos call, will contain the count returned by our SELECT statement.
Implicit Describe
If you’re still with me at this point, you have my undying admiration. Just bear with me through this next section, it will be very detailed, with some heavy OCI considerations. But once we get through it, I’m sure you will find it well worth the effort. Especially when we finally get to part 3, and you see how easy it is to develop an MFC application to retrieve data from an Oracle data base.
The last function called from inside the Open function, is the call to get_result_list(). Here is where we set everything up so we can retrieve data from the Oracle database, and make it available to the grid control. In the process, we provide the grid with everything it needs to know about each column, to be able to load it successfully into each cell of the grid.
The first thing I have to mention is this statement: AFX_MANAGE_STATE(AfxGetStaticModuleState()); you will see at the beginning of the function. There is a warning comment in the App Wizard generated code explaining the need for this statement. Any time you are using an MFC element in a DLL, this statement is required. I use CWaitCursor in this function, because it could take quite a while to execute, depending on how big a result set is being retrieved, and this will display the system wait cursor while that is taking place. Any time I use an MFC component, or even think I may use one, I included this statement. I did not do any experimenting to see what would happen if I left it out.
Once again, we call OCIStmtPrepare to set the statement handle. This time I’m using the SQL statement select * from table, where table is supplied by the calling process. I’m doing this to keep things simple for purposes of this article. One of the virtual functions I have implemented however, is the SetStatement() function, which can be used to build a more meaningful statement from user input. The advantage to doing the implicit describe is the fact the column attributes are obtained for whatever columns are in the select list, regardless of what is actually in the base table.
The first call to OCIStmtExecute doesn’t actually return the data. Instead, I have set the mode to OCI_DESCRIBE_ONLY, which does not execute the statement, but does return the select list description. The column count can then be obtained by a call to OCIAttrGet, with the attribute type parameter set to OCI_ATTR_PARAM_COUNT. When this is done using a statement handle as the input handle, as I’m doing here, the Param Count is the number of columns in the select list.
Memory Allocation for the Array of Column Attributes
One of the design considerations when I started on this project, was a way to be able to retrieve the data from any table in my data base. The problem is there has to be an array of column attributes, one occurrence for each column in the select list. I’m using the structure desc_parms for this purpose. This structure has elements to store attributes such as the column name, the column length as defined in the database, the column type and other attributes needed by the grid so it knows how to display the data.
Thus I need an array of desc_parms structures to store the column attributes. There is a member variable defined in ocicall.h that looks like this desc_parms *m_Parms. So I have a member variable that is a pointer to the desc_parms structure. In order to be able to dynamically set up the array of structures, I used the C calloc() statement.
Since the compiler knows what a desc_parms is, it can allocate memory based on the number of occurrences I request and any pointer arithmetic is done in terms of structure occurrences, not bytes. Thus *m_Parms+1 references the second occurrence of my array of desc_parm structures. But the real advantage to this approach is that array notation can also be used. I can refer to an occurrence in my array as m_Parms[1] and obtain the same result. This gave me the ability to dynamically allocate memory based on the number of columns in the select list.
The statement m_Parms = (desc_parms *)calloc(m_cols, sizeof(desc_parms)); accomplishes this.
I ran into the same dilemma with the column names. I first tried to use a CString, but I couldn’t get the OCI data types to play nicely with the MFC class. So I again resorted to the familiar and did them as character pointers and used malloc to allocate 31 bytes for each column name attribute. The for loop below accomplishes this. Since malloc() doesn't initialize memory the way calloc() does, I do that myself. To be safe, I also initialize the OCIDefine pointers to null in the same loop.
for (x=0; x<m_cols; x++)
{
m_Parms[x].ColName = (char *)malloc(31);
memset(m_Parms[x].ColName, 0, 31);
m_Parms[x].defnpp = (OCIDefine *)0;
}
I believe Oracle has a limit of 30 characters for column names, but I’m not sure this hasn’t been eliminated in the more recent releases, but I enforce this limit anyhow to prevent buffer overruns.
Obtaining Column Attributes
With the memory allocations accomplished, the column attributes are now obtained. This is accomplished by a call to the describe_column() function.
The describe_column() function executes a for loop to obtain the column attributes for each column in the select list. By passing the pointer to the statement handle (void *)m_stmhp, we are returned a pointer to an OCIParam pointer, (void **)&parmdp. The OCIParam pointer is then used to obtain all the column attributes we need, using calls to the OCIAttrGet() function. The attributes thus obtained, are stored in the member variables of the desc_parms structure.
In describe_column(), we obtain the maximum size of the data element as defined in the database, the column name, the data type, and the precision and scale attributes.
When obtaining the column name, I first check the length and truncate it at 30 characters if it is longer, to prevent a buffer overrun. The precision and scale are important for Oracle NUMBER data types. It will allow us to distinguish between integers and floating point data types, and set the grid property accordingly.
Finally, at the bottom of the loop, we call the OCI function OCIDescriptorFree() to free up the resources used for the structures referenced by the OCIParam handle. The OCI Programmers Guide warns to do this, otherwise there will be a memory leak on each subsequent call to OCIParamGet() for each column.
Here is the code for the describe_column routine:
int COci::describe_column(int numcols)
{
sword retval;
text *namep;
ub4 sizep;
OCIParam *parmdp;
ub4 pos;
ub4 parmcnt = numcols;
OCIDefine *defnp = NULL;
for (pos = 1; pos <= parmcnt; pos++)
{
if ((retval = OCIParamGet((void *)m_stmhp, (ub4)OCI_HTYPE_STMT, m_errhp,
(void **)&parmdp, (ub4) pos)) != OCI_SUCCESS)
return OCI_ERROR;
if ((retval = OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &m_Parms[pos-1].collen, (ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE, (OCIError *)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
if ((retval = OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &namep, (ub4 *) &sizep,
(ub4) OCI_ATTR_NAME, (OCIError *)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
if (sizep > 30)
sizep = 30;
if (sizep)
{
strncpy((char *)m_Parms[pos-1].ColName, (char *)namep, (size_t) sizep);
m_Parms[pos-1].ColName[sizep] = '\0';
}
if ((retval = OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &m_Parms[pos-1].coltyp, (ub4 *) 0,
(ub4) OCI_ATTR_DATA_TYPE, (OCIError *)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
if ((retval = OCIAttrGet ((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &m_Parms[pos-1].precision, (ub4 *) 0,
(ub4) OCI_ATTR_PRECISION, (OCIError *)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
if ((retval = OCIAttrGet ((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &m_Parms[pos-1].scale, (ub4 *) 0,
(ub4) OCI_ATTR_SCALE, (OCIError *)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
OCIDescriptorFree((void *)parmdp, OCI_DTYPE_PARAM);
}
return retval;
}
Memory Allocation for Bind Variables
Now that we have obtained all of the column attributes, we can allocate the buffers to hold the actual data values returned by the data base.
Once again, we loop through each column in the select list. Here is the desc_parms structure again:
typedef struct
{
char *ColName;
ub2 collen;
ub2 coltyp;
ub1 precision;
sb1 scale;
void *pValue;
sb4 value_size;
ub2 rlenp;
sb2 indp;
OCIDefine *defnpp;
} desc_parms;
The void pointer pValue is used to store a pointer to the actual data returned by the SQL select statement. Before we can do that, we need to allocate memory. We use the information obtained by the call to describe_column for this purpose. Depending on the data type of the column, we allocate the proper amount of memory for each column.
The switch statement checks the column type of each column, and does the appropriate memory allocation in each case.
If you look at the EMP table, it is defined in the Oracle database like this:
Name Null? Type
----------------------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
In my testing, not just with the EMP table, but my own tables as well, I never saw SQL_INT being returned, but it is listed in the OCI Programmers Guide as a possible date type, so I allowed for it.
I mentioned earlier how I used the environment variables NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT to control the presentation of Oracle DATE and TIMESTAMP data types. In the case of DATE, I allocate 11 bytes of memory and set the data type to SQL_STR, which is the designation for a C-style, null-terminated string. When the select statement is executed, OCI will use this information to convert the DATE field from the Oracle internal date format, to an easy to manage string. The same holds true for the Oracle TIMESTAMP data type.
So if you use my data source class, and you haven't set the NLS_DATE_FORMAT environment variable,
you will get the database default value for any Oracle DATE fields. This will cause an access violation
if the date format exceeds 10 characters.
This holds true for Oracle TIMESTAMP fields as well. Their format is determined by the NLS_TIMESTAMP_FORMAT
environment variable. Mine is set to ‘MM/DD/YYYY HH24:MI:SS.FF’. If your timestamp format exceeds 30 characters,
you will get an access violation. This can be fixed by adjusting the number of bytes requested in the
call to malloc().
Handling Oracle NUMBER Data Types
As you can see from the description of the EMP table above, Oracle NUMBER data types can be defined with, or without, digits to the right of the decimal point. We use the precision and scale attributes, obtained earlier, to distinguish which case we have. If OCI reported the data type of a column as SQLT_NUM, we need to determine if we have an integer, that is, no digits to the right of the decimal point, or a floating point value.
If the column was defined in the Oracle table as NUMBER, without any precision or scale given, the precision attribute is set to zero and the scale is set to -127. This indicates the value is stored in the Oracle database as a floating point number. This caused me a problem because the grid would like to know how many decimal places a column requires, but the NUMBER definition does not give any indication. In my testing with the EMP table, and other tables I had access to, I only had one case where the database column had been defined this way, and the data contained did not have any digits after the decimal point. I arbitrarily set the scale, which will be used later in the GetCell function, to 5. The column type member variable is set to SQLT_BDOUBLE, indicating a binary double value, and memory is allocated accordingly.
In the case of columns defined like EMPNO, with no precision given, OCI sets the scale attribute to zero and we know we have an integer. The column type is set to SQLT_INT, and memory allocated accordingly. If the scale is not equal to zero, we have a floating point number with the number of digits to the right of the decimal point specified, so we retain the scale, set the column type to SQLT_BDOUBLE and allocate memory accordingly.
For all other cases, the data is treated as character data and the column type is set to SQLT_STR, which is the OCI designation for a C-style, null terminated string.
Here is the complete code for the buffer allocation routine:
for (x=0; x<m_cols; x++)
{
switch(m_Parms[x].coltyp)
{
case SQLT_INT:
m_Parms[x].pValue = (int *)malloc(sizeof(int));
m_Parms[x].value_size = sizeof(int);
break;
case SQLT_DAT:
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char *)malloc(11);
m_Parms[x].value_size = 11;
break;
case SQLT_TIMESTAMP:
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char *)malloc(30);
m_Parms[x].value_size = 30;
break;
case SQLT_NUM:
if ((m_Parms[x].precision == 0) && (m_Parms[x].scale == -127))
{
m_Parms[x].coltyp = SQLT_BDOUBLE;
m_Parms[x].pValue = (double *)malloc(sizeof(double));
m_Parms[x].value_size = sizeof(double);
m_Parms[x].scale = 5;
}
else
{
if (m_Parms[x].scale == 0)
{
m_Parms[x].coltyp = SQLT_INT;
m_Parms[x].pValue = (int *)malloc(sizeof(int));
m_Parms[x].value_size = sizeof(int);
}
else
{
m_Parms[x].coltyp = SQLT_BDOUBLE;
m_Parms[x].pValue = (double *)malloc(sizeof(double));
m_Parms[x].value_size = sizeof(double);
}
}
break;
default:
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char *)malloc(m_Parms[x].collen + 1);
m_Parms[x].value_size = m_Parms[x].collen + 1;
break;
}
}
Host Variable Binding
We are now ready to indicate to OCI where we want column values stored. This is done using the OCI OCIDefineByPos function. Here is the function prototype, as found in the OCI Programmers Guide:
sword OCIDefineByPos(OCIStmt *stmtp,
OCIDefine **defnpp,
OCIError *errhp,
ub4 position,
void *valuep,
sb4 value_sz,
ub2 dty,
void *indp,
ub2 *rlenp,
ub2 *rcodep,
ub4 mode);
The first parameter is our statement handle, obtained previously using the OCIStmtPrepare call.
The second parameter is a pointer to a pointer to a define handle. If you recall, I set each of these member variables to null, because we will not be using the handle returned in any subsequent calls.
The third parameter is our error handle.
The fourth parameter is the column's position within the select list. These positions are 1-based, so the first column in the select list is position 1, not zero, which makes for some interesting C statements, since C array elements are zero based. In my call, I am using x+1 for this parameter, where x is my index variable.
The fifth parameter is a void pointer to the buffer allocated to store this column’s data. We allocated these in the for loop just discussed.
The sixth parameter is the size of each buffer. We set this at the same time we allocated the memory for each column’s buffer.
The seventh parameter is the data type of the column, which we also set based on the data type attribute we obtained earlier.
The eighth parameter is a very important one, and one we will have to spend some time discussing.
Oracle refers to this as an indicator variable and uses this to tell the calling program when something OCI doesn’t like about the column’s data occurs, most notable if the column value is null.
As it applies to our data source for the Ultimate Grid, an Oracle Indicator Variable is used to signal two conditions, a null column value, or column data exceeding the buffer size set up to contain it. Without using an indicator variable, if any column value contains a null, an Oracle ORA-1405 error occurs. An ORA-1406 occurs if the data in a column exceeds the size of the buffer allocated for it. Since I don’t want my routines to fail if these errors occur, I set up indicator variables as members of the desc_parms structure. For now, we have to pass the address of each respective column's indicator variable to OCIDefineByPos, so when our SELECT statement is executed, OCI will know which indicator variable to set when one of these conditions occurs. We will see how the indicator variable are used when we look at the GetCell() function, which is used to supply the grid with data obtained by all of our efforts.
The ninth parameter is a pointer to a field to store the length of data fetched. The calloc() statement used above to allocate memory for the array of desc_parm structures, would have initialized these variables to zero. I never found a need to use these values.
The tenth parameter is a pointer to an array of column level return codes. I didn’t use these values either, so I set this parameter to a null pointer.
The final parameter is the mode OCIDefineByPos should use. I didn’t need any of the features provided by the other modes, so I set it to OCI_DEFAULT.
OCIStmtExecute Revisited
When OCIDefineByPos returns successfully, we have given OCI the information it needs to return data into the column value buffers. We are now ready to execute our SELECT statement again, only this time we will actually be retrieving data from the Oracle database.
The second call to OCIStmtExecute is very similar to the first, with some notable exceptions. Instead of zero, we set the fourth parameter to 1. We have set up buffers to store one row’s worth of column data, and the mode parameter is changed from OCI_DESCRIBE_ONLY to OCI_STMT_SCROLLABLE_READONLY.
I’m using OCI_STMT_SCROLLABLE_READONLY because I wanted to be able to scroll backwards and forwards through the result list and Ultimate Grid makes this easy. The GetCell function, which we will be looking at next, requests the data by row and column. With only 14 rows in the EMP table, this behavior will not be noticeable in this sample, but I used it with much larger tables, one with almost three million rows, and the performance on my 16 GB Windows 8.1 PC was quite acceptable.
OCI does have a pre-fetch capability, in which more than one record can be retrieved at a time, but this requires an array of buffers. Which, in my case, would have meant a two-dimensional array of desc_parm structures. The array I set up stored all the column data for one row. I would have had to set up a second dimension to store an array of column values for each row.
Using this approach, the pre-fetch attribute is set after the call to OCIStmtPrepare, to get the statement handle, but before the call to OCIStmtExecute. The fourth parameter in the call to OCIStmtExecute would then be set to the pre-fetch count. I looked into this approach, but since the performance was satisfactory for my purposes, I didn’t want to over-complicate what I was doing.
With the successful completion of OCIStmtExecute, our result list is retrieved from the database and we are ready to load the data into the grid.
The GetCell virtual function
The GetCell virtual function needs to be implemented in our data source class to provide the grid control with the data to be displayed. The grid calls this function, passing a column number, a row number and a pointer to the cell object to be populated.
Setting Row and Column Headings
Rows and columns are zero-based, so the first cell to be populated would be row 0, column 0. Row -1 refers to the column headings, and Column -1 is the row headings. Cell (-1, -1) is the corner button. Looking at the code for GetCell, I check for the corner button and just return because I don’t want to do anything with it. However, I do want to use the row number as the row heading, but this row count will be 1-based, since most people don’t expect their first row to be number 0. So if col is less than zero, but row is not, I set the text to the row count + 1.
If the row parameter is less than zero, this is the column heading, and so the column name is retrieved from the data source. GetColName() is one of the virtual functions implemented in the data source. It returns the column name we worked so hard to obtain when we did the implicit describe.
Just to be safe, the column parameter is checked to be sure it is not outside the range of columns we have data for. If it is UG_NA is returned.
Fetching Data
Now we are ready to actually retrieve some data from the result list we obtained above. The OCIStmtFetch2 is used to accomplish this. We pass as arguments our statement handle, the error handle and the value 1 to indicate we want one row returned. The next parameter Oracle calls the orientation, and we set this to OCI_FETCH_ABSOLUTE, to fetch the row specified in the next parameter, the fetchOffset. The fetchOffset parameter is set to row + 1, because OCI rows are 1-based. At this time, there is only one acceptable value for the mode attribute and that is OCI_DEFALUT.
Based on the data type of the column, the number of decimals, alignment and cell value are set. We also check the indicator variable for each column to see if a null value was present, a value of -1 indicates this. It means the value received from the database was an Oracle null, which is not the same as a null value in C. It also means whatever was in the buffer from the previous fetch is unchanged. For numeric values, I set the cell value to zero. For string values, I set it to an empty string.
Here is the code for the GetCell function:
int COci::GetCell(int col, long row, CUGCell *cell)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
int retval;
CString celltext;
sb4 fetchOffset = row + 1;
if (col < 0)
{
if (row < 0)
return UG_SUCCESS;
celltext.Format("%d", row+1);
cell->SetText(celltext);
return UG_SUCCESS;
}
if (row < 0)
{
CString string;
GetColName(col, &string);
cell->SetText(string);
return UG_SUCCESS;
}
if (col >= m_cols)
return UG_NA;
retval = OCIStmtFetch2(m_stmhp, m_errhp, (ub4) 1,
OCI_FETCH_ABSOLUTE, fetchOffset, OCI_DEFAULT);
if (retval != OCI_SUCCESS)
return 1;
cell->SetCellType(UGCT_NORMAL);
if (m_Parms[col].coltyp == SQLT_INT)
{
cell->SetNumberDecimals(0);
cell->SetAlignment(UG_ALIGNRIGHT);
if (m_Parms[col].indp < 0)
cell->SetNumber(0);
else
cell->SetNumber(*(int *)m_Parms[col].pValue);
}
else
{
if (m_Parms[col].coltyp == SQLT_BDOUBLE)
{
cell->SetNumberDecimals(m_Parms[col].scale);
cell->SetAlignment(UG_ALIGNRIGHT);
if (m_Parms[col].indp < 0)
cell->SetNumber(0);
else
cell->SetNumber(*(double *)m_Parms[col].pValue);
}
else
{
if (m_Parms[col].indp < 0)
cell->SetText("");
else
{
celltext.Format("%s", (char *)m_Parms[col].pValue);
cell->SetText(celltext);
}
}
}
return UG_SUCCESS;
}
Because of all the work done doing the OCI describe, I was also able to implement other virtual functions such as GetNumRows(), GetNumCols(), GetColName() and GetColType() rather easily.
Using the Code
Two zip files are included with this article. One contains the project files for the Visual Studio 2013 version of the data source DLL. The other contains the project files for the Visual Studio 2010 version. The VS2010 project has configuration settings for both 32 and 64 bit builds, debug and release. Pre-built .lib and .dll files are included in the zip file, so no building is necessary unless you want to make your own modifications.
One thing I need to mention, the 64-bit debug version of the VS2010 project has a custom build step where I copy the DLL to c:\apps\Ultimate Grid\DLLs. I did this while I was doing my development, because I included this directory in my PATH environment variable. This way, any application I develop will look in this directory for the data source DLL. Similarly, the debug version of the VS2013 project has a custom build step to copy the DLL to D:\apps\Ultimate Grid\DLLs. If this doesn’t suit your needs, you will have to edit the command line of the custom build step, or just delete it entirely.
In order to use the data source as input to the Ultimate Grid control, first copy the OciDtSrc.dll file of your choice to a location included in your PATH environment variable. Next, you need to declare an instance of the data source class:
public:
COci DtSrc;
Depending on the kind of app you are developing, this will go in a different file. For a dialog application, it could be placed in the AppDlg.h file. In Part 3, I do an SDI application and in this case, I put this declaration in the Document.h file.
To obtain the data from the Oracle database, this is the code:
DtSrc.SetPassword("scott", "tiger");
rc = DtSrc.Open("orcl", "emp");
if (rc != OCI_SUCCESS)
{
if (rc == OCI_ERROR)
msg = DtSrc.GetOciError();
else
if (rc < 9)
msg.Format("Missing Argument: %d", rc);
else
if (rc == 16)
msg.Format("Couldn't allocate memory for value buffers!");
else
msg.Format("Unknown Error!");
AfxMessageBox(msg);
return FALSE;
}
In the sample program in Part 3, I have placed this code in the OnNewDocument() routine in the Document.cpp file. This is the code you could change to access any table in any schema. Replace scott/tiger with the appropriate user ID and password. Change the service name from “orcl” to your service name and replace “emp” with the desired table. Regardless of the number of columns or data types, the data source should be able to handle it.
Finally, you have to attach the data source to the grid. In the sample program in Part 3, this is done in the OnInitialUpdate() routine of the View.cpp file. Here is the code:
if (m_pDocument->DtSrc.IsOpen())
{
int index = m_grid.AddDataSource(&m_pDocument->DtSrc);
m_grid.SetDefDataSource(index);
m_grid.SetGridUsingDataSource(index);
m_grid.SetNumberRows(GetDocument()->DtSrc.GetNumRows());
m_grid.SetNumberCols(GetDocument()->DtSrc.GetNumCols());
m_grid.OnSetup();
}
That’s it. This should give you everything needed to build your own application using the Ultimate Grid control. However, if you want to see this all come together, check out Part 3 where I develop a sample application combining the Ultimate Grid DLL we built in Part 1 with the data source class we did here in Part 2.
Summary
In Part 2, we used the Oracle OCI library to develop our own custom data source for use with the Ultimate Grid control. We really have just barely scratched the surface of the capabilities of the grid control and the data source. I did not get into the use of the data source to update the database. This is something that would not be that difficult to do, but for my purposes I only needed the ability to browse tables, not update them. The Ultimate Grid documentation does a good job of outlining the steps needed and there is plenty of sample code, so I would encourage anyone who has been looking for a powerful, versatile datagrid control to explore things further.
History
- 5/18/2014: Initial release
