An Oracle OCI Data Source Class for Ultimate Grid, Part 3, Sample Application

• Download sample application - 755.1 KB
• Download VS2010 Project Files - 328 KB
• Download VS2013 Project Files - 165.7 KB

Introduction

This article is Part 3 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 use tables in an Oracle database to provide the data for the grid.

In Part 1, we built the Ultimate Grid control as an external DLL, so it could be used by other applications.

In Part 2, we built the data source class. We set up the OCI environment, defining a class with the ability to obtain metadata from the database, opening a connection to the database, obtaining the select list and making it available for use by the Ultimate Grid control.

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 an application capable of displaying any table in an Oracle database.

Background

The sample application included with this article accesses the EMP table from the scott/tiger schema. Using the Visual Studio project, also provided, I will show how the code can be modified to display data from any Oracle schema.

Points of Interest

Dressing up the Grid's Appearance

The Ultimate Grid is a very flexible and versatile control, one of the things I wanted to show in this sample application, is how the appearance of the grid can be customized. All of these customizations are done in the OnSetup()function of the MyCug.cpp implementation file.

In the sample application, I define and use several different colors. Colors are defined using the RGB macro. While developing my original application, I experimented with several different color schemes and have included all the colors I defined, even though they are not all used in the sample program. Here are the colors I defined:

COLORREF cHeading = GetSysColor(COLOR_BTNFACE);
COLORREF cBlack = RGB(0, 0, 0);
COLORREF cBlue = RGB(0, 0, 128);
COLORREF cGreen = RGB(0, 128, 0);
COLORREF cMilitaryGreen = RGB(64, 128, 128);
COLORREF cLtBlue = RGB(110, 180, 255);
COLORREF cWine = RGB(128, 0, 0);
COLORREF cYellow = RGB(255, 255, 0);
COLORREF cRed = RGB(255, 0, 0);
COLORREF cBuff = RGB(250, 230, 176);
COLORREF cGold = RGB(240, 194, 16);
COLORREF cWhite = RGB(255, 255, 255);

These color definitions are used to set the default column heading text and background colors, as well as the data cell text and background colors.

When setting the heading colors, first a cell object is defined, then the default heading attributes are placed in this cell object by a call to GetHeadingDefault(&cell). The desired colors are then set using SetTextColor and SetBackColor and then setting the heading defaults. Here is the code:

CUGCell cell;

GetHeadingDefault(&cell);
cell.SetBackColor(cWine);
cell.SetTextColor(cYellow);
SetHeadingDefault(&cell);

Setting the colors for the data cells is very similar. First, the number of columns is obtained from the data source. Then the column defaults are retrieved into the cell object. Then we loop through the columns and set the colors for text and background, and set the column defaults.

 int cols = GetNumberCols();

for (x=0; x<cols; x++)
{
    GetColDefault(x, &cell);
    cell.SetTextColor(cYellow);
    cell.SetBackColor(cBlue);
    SetColDefault(x, &cell);
}

Another statement I use in the setup is SetUniformRowHeight. This function is used to force all the rows to be the same height, even if the user resizes a row in the grid. The default is "off", so I turn it on in the OnSetup() routine.

The final customization in the sample is setting the column width. When I first displayed the EMP table in the grid, The JOB and HIREDATE columns were cutting off a few pixels of the data fields. This is fixed by calling the BestFit() function.

The signature for BestFit looks like this:

int CUGCtrl::BestFit(int startCol, int endCol, int calcRange, int flag);

There are two options for calculating the best fit; basing it on the column headings, or calculating the average width based on the data contained in a specified number of rows. Using the column headings requires setting the flag parameter to UG_BESTFIT_TOPHEADINGS. Setting the flag to UG_BESTFIT_AVERAGE, and supplying a value for the CalcRange parameter causes the grid to base the column width on an average for the number of rows specified. The documentation points out the larger number of rows, the better the fit will be. But there could be a performance cost.

This is how the grid looked without using BestFit():

Here is the code to size the columns appropriately:

BestFit(0, 0, cols, UG_BESTFIT_TOPHEADINGS);
BestFit(1, 6, cols, UG_BESTFIT_AVERAGE);
BestFit(7, 7, cols, UG_BESTFIT_TOPHEADINGS);

With the sizing done, the grid now looks like this:

When I just did the averaging, the EMPNO and DEPTNO fields were too narrow to see much of the column headings, since the data values were much smaller than the column headings. I used the UG_BESTFIT_TOPHEADINGS for those two columns and used UG_BESTFIT_AVERAGE for all others.

This customization was hard coded in the sample application, because I knew I would be showing the EMP table. If you modify the project code to display other tables, you will need to either adjust these lines, or eliminate them completely.

Date Formats

I mention this in part two, but it's worth repeating, the data source class I built is expecting the Oracle DATE field and the Oracle TIMESTAMP field to be formatted in a specific way. This is done by setting the environment variable NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT. I used a date format of 'MM/DD/YYYY' and a timestamp format of 'MM/DD/YYYY HH24:MI:SS.FF'.

If you use a different date format, you may have to modify the amount of memory allocated for these two data values. If your date format is greater than 10, or your timestamp greater than 30, then you will have to increase the count in the malloc() calls of the get_result_list() function in the OciDtSrc.cpp implementation file. See Part 2 for a discussion of how to build the data source class.

Window Persistence

I use the code to persist the main window size and location, described in Part 1, in this sample application as well. It is in the MainFrm.h and MainFrm.cpp files, in the calls to PreCreateWindow() and OnClose().

Using the Code

Using the Sample Application

The zip file included with this article contains three files; UGMFC.dll, the release version of the grid control built in part 1, OciDtSrc.dll, the release version of the data source class built in part 2, and UGApp2.exe, the sample application currently being discussed.

Unzip these files into the same directory and run UGApp2.exe. It should display the EMP table from the scott/tiger schema, if your service name is ORCL.

If your service name is not ORCL, you can still use the sample by making a copy of the service you do have, if it accesses the instance of the Oracle database containing the scott/tiger schema. Then modify the address name to ORCL and it should work.

For example, if the entry for your Oracle instance with the scott schema installed, in your TNSNAMES.ORA file looks like this:

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = myservice)
    )
  )

Then make a copy of that entry and change the address name to ORCL, as shown below, and the sample application should work without modification.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = myservice)
    )
  )

Your second option would be to modify the code and rebuild the project, as shown below. You would probably want to do this anyhow, to display an Oracle table with more meaning for you.

Using the Project Files

This article also includes a zip file containing the Visual Studio 2013 project files, and one with the Visual Studio 2010 project files. If you unzip and open the solution file with the appropriate version of Visual Studio, you can modify the code to suit your needs.

This sample application is an SDI program, using MFC's document/view architecture. The code to instantiate the Ultimate Grid control is in the CView implementation and is identical to the approach used in Part 1 of this series.

The area of particular interest will be the document class, because it is here you can modify the User ID, password, service name, and table name to be accessed.

If you look at UGApp2Doc.cpp, in the OnNewDocument()routine, this is where those parameters are set and passed to the data source.

BOOL CUGApp2Doc::OnNewDocument()
{
    if (!CDocument::OnNewDocument())
        return FALSE;

    int rc = 0;
    CString msg;

    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;
    }

    return TRUE;
}

An instance of the data source class is defined in UGApp2Doc.h, like this:

// Attributes
public:
    COci DtSrc;

In order to access a different service, schema or table, change the appropriate parameters in the call to SetPassword() or Open() and you should be able to access any Oracle table for which you have authorization.

One thing I plan to do in the future is to build a much more flexible user interface. It would not be that difficult to use a dialog to obtain user Id, password and service name. The Oracle DescribeAny() call could then be used to populate a combobox with the table names contained in the selected schema.

Summary

Once again, I have only scratched the surface of the capabilities of the Ultimate Grid control and Oracle OCI product. Another project I may undertake, would be to do an MDI application in which multiple document objects could be used, each containing a different table's data. There are also many more customizations the grid control supports. There is an easily implemented feature to display a context menu when the user right-clicks in the grid. I have thought about using this feature to pull up a related record from the database. For example, right click on a row in the EMP table to display the related DEPT record.

In any event, I hope you have enjoyed reading this series of articles as much as I have enjoyed working on them.

History

• May 18, 2014: Original release