Classes for direct SQL calls with ODBC

.

During my current project, I found that I needed more functionality with my sql calls than CRecordset or CDAORecordset had to offer. These classes wrap the low-level ODBC API calls and act simular to a CRecordset, but allow me to execute any SQL statement without having to bind variables,etc.

The main class for doing this is CSQLDirect. CSQLDirect has the following attributes/functions:

  • CSQLDirect::Connect - Connects to a datasource.
  • CSQLDirect::ExecuteSQL - this is the main function that is used for handling the SQL statement.
  • CSQLDirect::GetCol - Will return a column from a table in the resulting cursor.
  • CSQLDirect::GetError - Provides detailed error messages in case something went wrong.
  • CSQLDirect::GetColumnType - Provides information about a cursor's column.
  • CSQLDirect::Fetch - Will properly execute a SQLFetch command with error handling.
  • CSQLDirect::Close - Closes the connection to the datasource.

The other class CSQLColumn is a support class for CSQLDirect. Since multiple queries to a cursor's column will result in a NULL value being returned, I found it necessary to keep track of the columns as they are used. This is stored in a CPtrArray within CSQLDirect and is cleaned up after each time the cursor is requeried/closed.

An example of using this class to make SQL Calls:

CSQLDirect SQLDirect( "TestData" );

if( SQLDirect.ExecuteSQL( "SELECT * FROM Employee" )==SQL_SUCCESS ) {
	while( SQLDirect.Fetch()==SQL_SUCCESS ) {
		.
		// Do some stuff here
		.
	}
}

That's it!

The great thing about this class is you no longer have need for a huge assortment of CRecordset classes for every table/query.

Anyways I hope this can be of help to anyone that uses the site. Don't hesitate to give me a shout if anyone has any questions/comments.

Thanks for all the help that CodeGuru has given my over the last few months!

Download Source 3K



Comments

  • GetCol bug

    Posted by Legacy on 02/09/2004 12:00am

    Originally posted by: Jay

    There seems to be a bug where if you access columns out of order, you get junk data. For example, I did:
    
    

    m_dbSandy.ExecuteSQL( "SELECT * FROM sandy_stats_branches WHERE month=10 AND fiscal_year='2002-2003'")

    The first columns it returns are:

    ala_checkins, ala_new_cards, ala_checkouts, ala_holds, ala_internet_cards

    And if I access them in that order it works fine. But if I access them in some other order, such as:

    m_sALACheckins = m_dbSandy.GetCol("ala_checkins");

    m_sALACards = m_dbSandy.GetCol("ala_new_cards");

    m_sALAInet = m_dbSandy.GetCol("ala_internet_cards");

    m_sALACheckouts = m_dbSandy.GetCol("ala_checkouts");

    m_sALAHolds = m_dbSandy.GetCol("ala_holds");

    The first ones that are in order will be assigned valid data, but the first one out of order (And everything after that) will have junk data instead.

    In my example above, the first two variables get valid data, and the last three are filled with junk.

    • GetCol bug

      Posted by Miros on 06/25/2004 03:02pm

      I've found just the same, but only with MS SQL db connection. With MS Access db it works.

      Reply
    Reply
  • How to use the "SQLColumns()" in Visual Basic

    Posted by Legacy on 10/28/2003 12:00am

    Originally posted by: Tony

    I want to get the information of a table , but don't know how to use the "SQLColumns()" in Visual Basic.Can you tell me the method and thank a lot .If you can give one example,it's very wonderful!

    Reply
  • How to read and write binary data by ODBC API

    Posted by Legacy on 07/14/2003 12:00am

    Originally posted by: shenzheng

    How to read and write binary data by ODBC API
    

    Reply
  • Anyone knows how to connect the mysql to my programme in Linux?

    Posted by Legacy on 07/10/2003 12:00am

    Originally posted by: Laux

    Hi

    I am a student in Hong Kong. I need to check the data stored in mysql (Linux) from my programme (written in C++). Do anyone knows how I can do it?

    I have read the site www.mysql.com, but the site only told me some C API, but it didn't tell me which header file and object file needed to be includeed.... Can anyone help me?
    Thx

    Reply
  • SELECT MAX Doesn't work!

    Posted by Legacy on 05/25/2003 12:00am

    Originally posted by: Ke

    I have an error message when I use this SQL command:
    SELECT MAX(ID_COL) FROM MY_TABLE;

    Where is the problem? Does anyone have a solution?

    Reply
  • What about LOBs ???

    Posted by Legacy on 05/20/2003 12:00am

    Originally posted by: d00guy

    I'm trying to use blob and clob in my application but I encounter certain problems to make INSERT. I need some help !!
    Thanks

    d00guy

    Reply
  • THANK YOU -- Supports MySQL

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

    Originally posted by: Mike Aerni

    I had a b$(%tch of a time trying to get another library (mysql++) working, but kept getting all kinds of link errors and/or application crashing when executing mysql commands. Your library made it SO EASY!!!! The only change I had to make was to add username/password to the CSQLDirect constructor and the Connect method. Thank you, your work is very much appreciated.

    Reply
  • And mySQL?

    Posted by Legacy on 01/22/2003 12:00am

    Originally posted by: Marian Trizuliak

    Hi,

    can you tell what about mySQL? I use it, so I want to know some info about a connection through ODBC with mySQL and using of this example for that connection and executing SQL commands. Thx.

    Reply
  • How to upload Excel and upload excel data in MSSQL

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

    Originally posted by: Srikanth

    How to upload Excel and upload excel data in MSSQL

    Reply
  • Great work !

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

    Originally posted by: Jerry

    It is very helpful and easy to use.
    Very nice !


    Jerry

    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 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds