This.
There is a huge heap of different solutions implemented just to perform this task. One can find them here on CodeProject but also on the Internet (some free, and some are not). MFC solutions for this task are the well-known CDatabase and CRecordset classes which give us a lot of freedom when working with ODBC data-sources and are well documented in the MSDN. So, now comes the question, why use this library when you already have these classes? The fact is, if you work under MFC, you would probably be satisfied since you have all things served, but what if you don't? Next, for each table in the database you want to access, you will have to connect your project to it and the Class Wizzard will add a new class to your project each time. But what if I have a database with a 100 or more tables? And, the most important thing (the reason why the DataLayer library was developed), what if you modify your tables during the development of the project? You will probably have to hand-code all modifications to the generated classes that represent the database tables. It can be very boring, right?
CDatabase
CRecordset
What if you want to connect to some data-source at run-time and you want to know what tables there are, what columns the tables have, and finally, take a look at a different type of data that is inside the table rows? Maybe, you would like to create tables or alter them (or delete) at run-time?
You would need a tool for such data-source access and it is not a pleasant thing to use the ODBC SDK in your everyday job. This library represents such a wrapper, so you could perform all database tasks with very little effort. Sure, you must be familiar with the SQL syntax in order to execute queries.
Please note that there isn't any good reason why I picked these names starting with _Z in the implementation. So, you will meet _ZConnection, _ZRecord, _ZColumnInfo, and _ZTableInfo classes and structures. Anyway, if you don't like it, you are free to rename it.
_ZConnection
_ZRecord
_ZColumnInfo
_ZTableInfo
To use this DLL library in your projects, you will have to include a header file DataLayer.h and copy the compiled module named DataLayer.dll where your executable output is. Or, you can copy it to the Windows folder. Anyhow, now you will be able to instantiate some (but not all) classes from the DataLayer library. The last thing you should do is link your project to the static library DataLayer.lib. You'll find the compiled library modules in the download section (above), and a test project with no additional modules, so you'll have to download both to build the test solution. Or, you can download just the compiled modules and build your own solution. You'll also find there a link to the source of the library to compile it yourself.
#include "DataLayer.h"
There is a simple class called _ZConnection that will provide you this:
// Connect to the data source
_ZConnection zConnection;
BOOL bConnected = zConnection.Connect(dsn, user, authorization);
Arguments in this call are the ODBC data-source name (dsn), username (user), and an authorization (authorization). the method will return TRUE if the connection is established or FALSE in the other case. You are also free to check the status of the connection at any time by calling:
dsn
user
authorization
TRUE
FALSE
BOOL bConnected = zConnection.IsConnected();
The most important arguments in the connection method are dsn and user (if it is required, otherwise it can be NULL). If authorization is not required, it can be NULL.
NULL
To disconnect, simply use:
zConnection.Disconnect();
This will free all the resources allocated in the connection object. But, don't forget to call it.
Maybe, you would like to know what tables are there in the data-source. To find out, you will have to allocate some memory for the buffer of _ZTableInfo type, and pass it as an argument, like in the example:
_ZTableInfo tableInfo[100];
int tiSize;
BOOL bResult = zConnection.GetTableInfo(tableInfo, tiSize);
In the tiSize variable, after this call, you will obtain the exact number of tables in the data-source, and tableInfo buffer will be filled with information about each table present in the data-source. This structure is defined as follows:
tiSize
tableInfo
/* _ZTableInfo struct definition */
struct _ZTableInfo
{
SQLVARCHAR catalogName[1024];
SQLVARCHAR schemaName[1024];
SQLVARCHAR tableName[1024];
SQLVARCHAR tableType[1024];
SQLVARCHAR tableRemarks[1024];
};
This is a simple way for enumerating the data-source tables.
It makes sense if you would like to modify data in the database tables. It doesn't make any sense if you want to get a result set back. So, you could write something like this:
// sqlStatement variable is defined somewhere else
BOOL bResult = zConnection.ExecuteSQL(sqlStatement);
Primarily, this is meant to be used in INSERT, UPDATE, or DELETE statements. You can also use it in SELECT statements but there is a small problem: you won't see any result. So, now you have seen how you can add, update, or remove records from the database. Now, it is time for a new DataLayer library object to be used to retrieve data from the data-source.
INSERT
UPDATE
DELETE
The object is called _ZRecord and it is used for data retrieval, like in the following example:
_ZRecord zRecord(zConnection);
BOOL bResult = zRecord.ExecuteSQL(sqlStatement);
I suggest you to use here a SELECT statement. Then, if the query succeeds, the return value is TRUE and you should be able to access the returned data. Use a simple loop like this:
// data1 and data2 are variables of the type that is compatible
// with the type of the data in the requested column
while (zRecord.Move())
{
// columnName variable is a known name
// of the column in the returned result set
data1 = (data1_type)zRecord.ColumnByName(columnName);
// columnIndex variable is a known index
// of the column in the returned result set
data2 = (data2_type)zRecord.ColumnByIndex(columnIndex);
}
If you are confused after this piece of code, I will explain. You will keep calling the Move() method of the _ZRecord object until you reach the end of the returned result set. This is a straightforward method (_ZRecord object does not behave like CRecordset object in the manner that you can get the first, last, or the previous record, and after each call, you will have the next record from the result set) which means that you want to go from the beginning to the end of the result set in a single pass.
Move()
Next, if you want your data, you will have to provide variables to hold it. These are provided by data1 and data2 in the above example. They must be of a type compatible to the type of the returned data in each column of the result set. Then you either call the ColumnByName() or the ColumnByIndex() method of the _ZRecord object. In the first, you pass the exact (but not case-sensitive) name of the column in the result set, and in the second, you pass a column index which should be less than the number of returned columns in the result set. Don't worry, if you make a mistake, you will have a NULL value. Anyway, an explicit conversion is required because these methods return an object on any type (VOID).
data1
data2
ColumnByName()
ColumnByIndex()
VOID
If you would like to know the exact number of columns in the returned result set, do this:
int columnNumber = zRecord.GetColumnNumber();
The above code will be fine if you know the name of the columns in the result set. But what if you don't? In that case, you can use one of these methods:
_ZColumnInfo columnInfo1, columnInfo2;
columnInfo1 = zRecord.GetColumnInfo(columnName);
columnInfo2 = zRecord.GetColumnInfo(columnIndex);
The _ZColumnInfo struct is shown below:
/* _ZColumnInfo struct definition */
struct _ZColumnInfo
{
SQLCHAR columnName[256];
SQLSMALLINT columnDataType;
SQLSMALLINT columnNullable;
SQLUINTEGER columnDataSize;
};
As you can see, in this way, you will have all necessary information about columns.
As you can see, this small library will provide you enough freedom to perform different database tasks with no trouble. It has almost all built-in functionality as MFC ODBC database classes, and some more. It is not of a cursor-type. It can be applied to all data-sources with an existing ODBC driver. I have tested it on MS Access and MySQL database systems, and I found no problems performing simple tasks, but keep in mind that there could be some exceptions, so in that case, write to me.
The library is also light-weight, because (you'll for sure notice this) there are no error descriptions in the return values of the different functions. It is built to be as simple as it can, but next time, I might complicate things a bit.
Working on this project, I found out that the ODBC SDK is not such a pleasant thing to work with, but it can have its bright sides. Anyway, with this implementation, my code will shrink in future and I will have more freedom working with returned result sets. My next step will be implementing the same thing you have seen here, but this time using the OLEDB SDK.
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
connection.Connect("testdata","data","data");
_ZRecord zRecord(connection);
zRecord.ExecuteSQL("SELECT * FROM muutot");
while(zRecord.Move())
{
cout<<*(char*)zRecord.ColumnByName("starttime")<<endl;
}
LPTSTR
LPCTSTR
WTL::CString
SQLPOINTER _ZRecord::operator[] (LPCTSTR str)
{
return GetColumnByName(str);
}
SQLPOINTER _ZRecord::operator[] (int index)
{
return GetColumnByIndex(index);
}
_ZRecord record(...);
...
int var1 = (int)record[_T("my_field")];
int var2 = (int)record[2];
_ZRecord record(...);
...
double d = (double)record.GetColumnByName(_T("my_field"));
SQLPOINTER
void*
_ZRecord record(...);
...
int i = *(int*)record.GetColumnByIndex(1);
_ZRecord record(...);
...
double d = *(double*)record.GetColumnByIndex(1);
_ZConnection zConnection;
BOOL bConnected = zConnection.Connect("DSN","user","pass");
bConnected = zConnection.IsConnected();
_ZRecord zRecord(zConnection);
if (zRecord.ExecuteSQL("select * from TABLE"))
{
char *data1;
while (zRecord.Move())
{
data1 = (char*)zRecord.ColumnByIndex("FIELD");
}
}
_ZColumn* m_ColumnArray;
CString Driver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString dsn;
CString File = "c:\\Test.mdb";
// Build ODBC connection string
dsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", Driver, File
BOOL bConnected = zConnection.Connect(dsn, user, authorization);
SQLDriverConnect(hdbc,
hwnd,
(SQLTCHAR*) "DRIVER={SQL Native Client};" SQL_NTS,
szOutConn,
MAX_CONN_OUT,
&cbOutConn,
SQL_DRIVER_COMPLETE);
int MyClass::myMemberFunction()
{
_ZConnection m_conn;
// Connect to the data source
if (!m_conn.Connect("FSAS Database", "dba", "SQL")) {
AfxMessageBox(_T("Unable to connect to the data source!"), MB_OK );
return -1;
}
_ZRecord rec(m_conn);
rec.ExecuteSQL(QuerySQL_str);
while (rec->Move())
{ // do whatever .....}
if (m_conn.IsConnected())
m_conn.Disconnect();
}
/* _ZRecord destructor */
_ZRecord::~_ZRecord()
{
if(!m_Connection.IsConnected()){
if ( m_hstmt )
{
// Free statement handle
SQLFreeHandle( SQL_HANDLE_STMT, m_hstmt );
m_hstmt = NULL;
}
}
// Free column array
FreeColumnArray();
}
General News Suggestion Question Bug Answer Joke Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.