ODBC Class generator

(Designed and developed by Shekar Narayanan)

I have developed this Class Generator after using the CRecordset for quite some time. CRecordset is great but it has lot of limitations and is SLOW.

Most of the time, I had to display thousands of records in a list view. Using the CRecordset, it takes a lot of time. MFC also provides a way to do ‘bulk fetch’ but it is tedious to implement.

So, I created a class which will do the bulk fetching and also allow easy inserts, update and delete. After creating tens of these kind of classes, it was no difficult to automate the class creation process.

A simple comparison:

The code generated by this tool along with virtual list view gets and displays 5000 records from SQL server database in just one second! Whereas, using the CRecordset, it takes around 20 seconds. These numbers are based on 266 MHz Pentium with 64MB RAM..

 

Features

  • Easily create a class which uses Bulk Row Fetching for faster data retrieval
  • Uses direct ODBC SDK calls which are easy to understand and flexible to modify
  • More methods can be easily added to the object
  • Creating Joins and using other SQL features is very easy, since the object uses direct SQL statements

 

Usage of the Product

Using the Class Generator is easy.

  • Click Open to display the list of Data Sources
  • Select the Data Source and click Next
  • Select the Table or View you want to use and click Next
  • Click Finish

A list view displays the columns in the table along with the default member variables. You can easily change the name of the member variable and if you want to exclude any column from the object, you can do that by simply clicking Remove button.

 After changing /removing the variable names, click the Create button to open the Generate Dialog box. It shows the default values based the on the table name. After modifying the values click the OK button to generate the code. You can also print this list.

 

Following is a sample header file generated by this tool:

/*

**ConsultantsSet.h

**CONSULTANTS Definition file

*/

#if !defined(__DB_CONSULTANTS_FIELDS__)

#define __DB_CONSULTANTS_FIELDS__

#ifndef __AFXTEMPL_H__

#pragma message("Include AfxTempl.h in StdAfx.h for faster Compilation")

#include <afxtempl.h>

#endif

#define CONSULTANTS_NAME_SIZE 31

#define CONSULTANTS_HOME_ADDRESS_SIZE 81

#define CONSULTANTS_CLIENT_NAME_SIZE 31

#define CONSULTANTS_CLIENT_ADDRESS_SIZE 81

 

//Internal Cache for the data

typedef struct

{

long m_ID;

SDWORD m_IDInd;

char m_Name[CONSULTANTS_NAME_SIZE];

SDWORD m_NameInd;

char m_HomeAddress[CONSULTANTS_HOME_ADDRESS_SIZE];

SDWORD m_HomeAddressInd;

char m_ClientName[CONSULTANTS_CLIENT_NAME_SIZE];

SDWORD m_ClientNameInd;

char m_ClientAddress[CONSULTANTS_CLIENT_ADDRESS_SIZE];

SDWORD m_ClientAddressInd;

}FF_DB_CONSULTANTS_FIELDS, *pFF_DB_CONSULTANTS_FIELDS;

 

//structure to hold the final data

typedef struct

{

long m_ID;

CString m_Name;

CString m_HomeAddress;

CString m_ClientName;

CString m_ClientAddress;

}DB_CONSULTANTS_FIELDS, *pDB_CONSULTANTS_FIELDS;

 

//Class Definition

class CConsultantsSet

{

public:

//Standard constructor

CConsultantsSet(CDatabase* pDB = NULL);

//Standard Destructor

~CConsultantsSet();

//Operations

bool GetSpecific(pDB_CONSULTANTS_FIELDS);

bool Insert(pDB_CONSULTANTS_FIELDS);

bool Update(pDB_CONSULTANTS_FIELDS);

bool Delete(pDB_CONSULTANTS_FIELDS);

int Load();

//Attributes

CTypedPtrArray <CPtrArray, pDB_CONSULTANTS_FIELDS> m_aData;

private:

CDatabase* m_pDatabase;

HSTMT m_hstmt;

int m_nRowSetSize;

};

#endif

/*

** end file

*/

 

The header file contains two structures FF_DB_CONSULTANTS_FIELDS and DB_CONSULTANTS_FIELDS. The first one is used for bulk row fetching. The second is used to in a CTypedPtrArray and contains the actual data.

 

Using the Generated Code in your Application

In order to use this class in your application, create a regular MFC application with database header support. The View can be either CFormView or CListView . In this example, m_List is a CListCtrl.

#include "ConsultantSet.h"

 

void OnInitialUpdate()

{

    . . .

    CConsultantSet tblSet(&theApp.m_DB);

    int nCount = tblSet.Load();

    for (int I = 0; I < nCount; I++)

    {

        pDB_CONSULTANT_FIELDS pdbFields = tblSet.m_aData.GetAt(I);

m_List.InsertItem(I, pdbFields->m_Name;

m_List.SetItemText(I, pdbFields->m_ClientName);

    }

}

 

If you want to insert an item to the table,

    CConsultantSet tblSet(&theApp.m_DB);

    DB_CONSULTANT_FIELDS dbFields;

    dbFields.m_ID = 100;

    dbFields.m_Name = "Some Name";

    // Fill in rest of the fields

    tblSet.Insert(&dbFields);

 

 

Rest of the functions, follow the same standard and very easy to understand. I always use a single CDatabase object in the Application class. Create a CDatabase member variable in the CWinApp derived class and also make the 'theApp' as extern in the header file. This way, you don't have to use AfxGetApp() and typecast it every time.

 

This tool generates the code which can be compiled in VC++ 5.0. With minor modification, it can be used with any C++ compiler.

 I have tested this tool with MS Access and SQL Server only. So there may be a few bugs. If you encounter any bugs, please email me.

I would appreciate your ideas for enhancements.

 

Acknowledgments:

I would like to thank the CodeGuru contributors for List Control and Bitmap buttons I have used in this tool.

Download 274K



Comments

  • "Driver Not Capable !!"

    Posted by Legacy on 08/01/2002 12:00am

    Originally posted by: TVK

    After i have selected the table to be modeled, i get the "Driver Not Capable" message... Any ideas as to why ? i run on VC6 and Oracle 8.07

    Reply
  • New version

    Posted by Legacy on 11/18/2000 12:00am

    Originally posted by: jack zhang

    AppBuilder--A complete truly visual C/C++ IDE.it offers Microsoft Foundation Class developers a complete library of MFC extension classes that implement Authentic Looking GUIs like those seen in Microsoft money 2000? AppBuilder can help developers develop application with database.The classes fit seamlessly with MFC and inherit many of MFC's existing classes. You can now easily give your database application the MS money look, without going broke! that's not all,AppBuilder is also a code generater,it can generate database code based on ODBC API or MFC ODBC, this can help you develop manage information system easily and quickly. if you want learn more about appbuilder,Please visit: http://www.ucancode.net

    Reply
  • When is new version available & Where????

    Posted by Legacy on 04/01/2000 12:00am

    Originally posted by: Mike Malter

    When and where is the new version going to be available?

    Reply
  • Good Program, but more bugs

    Posted by Legacy on 03/19/2000 12:00am

    Originally posted by: Tim Hein

    If you use the program to generate a table or query class, you will need to modify the:
    
    

    if (ReturnCode == SQL_ERROR)

    line in the Load() method, just after the while (true) and the SQLExtendedFetch calls to the following:

    if (ReturnCode != SQL_SUCCESS)

    The code as generated from the program will put you in an infinate loop if you do a lookup or query for records that aren't in the database. The other option is to set the RowsFetched variable to 0 before the call and take your chances that the odbc driver doesn't change the value when the call fails (if there isn't any data in the database).

    Tim

    Reply
  • Great program. I did find some bugs though.

    Posted by Legacy on 03/10/2000 12:00am

    Originally posted by: PeterK

    I did contact Shekar with this information months ago, but I thought I should save some people some headaches.

    The memcpy commands need to be implemented somewhat differently. One variable is a character string (char *) the other is a CString.

    Here is an example of what the memcpy should look like:

    memset( HostData.m_Type, 0x00, sizeof(HostData.m_Type) );
    memcpy(HostData.m_Type, (LPCTSTR)pData->m_Type,
    min(pData->m_Type.GetLength(), sizeof(HostData.m_Type) - 1));
    HostData.m_TypeInd = SQL_NTS;

    Also the SQL indicator for variables that are numbers are not initialized.

    The easiest way to insure your program does what you expect it to do is to memset the entire HostData structure to NULL before calling the desited function from the ODBC API. This will also save you many lines of code because you no longer have to initialize each member of the HostData structure.

    Other than those two small problems this is a great program, very handy.

    Reply
  • New Version Available!

    Posted by Legacy on 03/06/2000 12:00am

    Originally posted by: Shekar Narayanan

    Download the latest version from

    http://personal.bellsouth.net/~surya/interest.htm

    Sorry! Due to some problem with the ISP, this location is not available. I'll post the new version in some other location. Check later.

    Thanks

    Reply
  • ODBC Generator

    Posted by Legacy on 08/12/1999 12:00am

    Originally posted by: Pat Sherrill

    nCount seems to always return 0(using Version 2). What am I doing wrong?

    As an aside the SQL Query generator forces upper case. I beleive this should be case sensitive for column name containing upper and lower case. I have many file handlers whose fields/columns are case sensitive (eg Ctree MySql).

    Reply
  • Thanks

    Posted by Legacy on 05/14/1999 12:00am

    Originally posted by: zhouuashan

    I'm looking for a tool to like crecordset to manage my gis data.
    This is just i need!
    thank you!

    Reply
  • Great wizard!

    Posted by Legacy on 05/12/1999 12:00am

    Originally posted by: Jay Wheeler

    I'm using it in a current SQL Server 7 project, and
    it makes the whole process of working with direct SQL
    a lot easier!

    The only problem I have had with it is that it does not
    like the SQL type "nvarchar", but this is of little
    consequence to me, as the tables are really "char" of
    fixed type.

    Thanks for a great tool!

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • Live Event Date: November 13, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT APIs can be a great source of competitive advantage. The practice of exposing backend services as APIs has become pervasive, however their use varies widely across companies and industries. Some companies leverage APIs to create internal, operational and development efficiencies, while others use them to drive ancillary revenue channels. Many companies successfully support both public and private programs from the same API by varying levels …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds