//SqlProcs.h
#ifndef SQL_PROCS_H
#define SQL_PROCS_H
unsigned int iInstallerError();
void MkDate(TIMESTAMP_STRUCT&, TCHAR*);
TIMESTAMP_STRUCT ParseDate(TCHAR*, TCHAR*, TCHAR*);
#endif
//SqlProcs.cpp
#include <windows.h>
//#define _UNICODE
#include <tchar.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "Strings.h"
#include "SqlProcs.h"
unsigned int iInstallerError()
{
DWORD pErr;
TCHAR szErrMsg[512];
WORD cbMsgBuffer=512;
WORD cbRet;
WORD wErrNum=1;
while(SQLInstallerError(wErrNum,&pErr,szErrMsg,cbMsgBuffer,&cbRet)!=SQL_NO_DATA)
{
wErrNum++;
};
return (unsigned int)pErr;
}
void MkDate(TIMESTAMP_STRUCT& ts, TCHAR* szBuffer)
{
TCHAR szMonth[4],szDay[4],szYear[8];
_stprintf(szMonth,_T("%u"),ts.month);
_stprintf(szDay,_T("%u"),ts.day);
_stprintf(szYear,_T("%u"),ts.year);
_tcscpy(szBuffer,szMonth);
_tcscat(szBuffer,_T("/"));
_tcscat(szBuffer,szDay);
_tcscat(szBuffer,_T("/"));
_tcscat(szBuffer,szYear);
return;
}
TIMESTAMP_STRUCT ParseDate(TCHAR* szDate, TCHAR* szFormat, TCHAR* szDelimiter)
{
UINT i=0,j=0,k=0;
TIMESTAMP_STRUCT ts;
TCHAR buf[3][8]; //buf[0] for month, buf[1] for day, buf[2] for year
TCHAR* p;
memset(buf,0,sizeof(buf)); //zero out buf[]
p=szDate;
for(i=0;i<_tcslen((TCHAR*)szDate);i++)
{
if(*p!=*szDelimiter)
{
buf[j][k++]=*p;
buf[j][k+1]=_T('\0');
}
else
{
j++;
k=0;
}
p++;
}
if(!_tcsicmp((TCHAR*)szFormat,_T("MDY")))
{
ts.month=(short)_ttoi(buf[0]);
ts.day=(short)_ttoi(buf[1]);
ts.year=(short)_ttoi(buf[2]);
}
if(!_tcsicmp((TCHAR*)szFormat,_T("DMY")))
{
ts.day=(short)_ttoi(buf[0]);
ts.month=(short)_ttoi(buf[1]);
ts.year=(short)_ttoi(buf[2]);
}
if(!_tcsicmp((TCHAR*)szFormat,_T("YMD")))
{
ts.year=(short)_ttoi(buf[0]);
ts.month=(short)_ttoi(buf[1]);
ts.day=(short)_ttoi(buf[2]);
}
return ts;
}
//Access.h
#ifndef MSACCESS_H
#define MSACCESS_H
void ErrorMemFree(TCHAR**, unsigned int);
long blnLineFailed(HWND, SQL&, TCHAR**, unsigned int&, TCHAR*);
unsigned int iCreateDB(TCHAR*);
unsigned int blnMakeTable(SQL&);
int GetRecordCount(SQL&, unsigned int&, LPCRITICAL_SECTION);
UINT blnInsert(SQL&, TCHAR**, unsigned int&, HWND, int, LPCRITICAL_SECTION);
UINT blnDumpData(SQL&, TCHAR**, unsigned int&, HWND, LPCRITICAL_SECTION);
void btnAccess_OnClick(lpWndEventArgs Wea);
#endif
//Access.cpp
#include <windows.h>
//#define _UNICODE
#include <tchar.h>
#include <stdio.h>
#include <string.h>
#include <process.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "WinTypes.h"
#include "SqlProcs.h"
#include "Strings.h"
#include "Sql.h"
#include "Main.h"
#include "Access.h"
#if defined(MY_DEBUG)
extern FILE* fp;
#endif
void ErrorMemFree(TCHAR** pLns, unsigned int iNum) //If there are any memory allocation errors
{ //anywhere along the way, this little thingy
unsigned int i,blnFree=0; //unravels all the allocations done up to the
//point where the 1st allocation error occurred.
for(i=0;i<=iNum;i++)
blnFree=(unsigned int)GlobalFree(pLns[i]);
blnFree=(unsigned int)GlobalFree(pLns);
}
long blnLineFailed(HWND hWnd, SQL& Sql, TCHAR** ptrLines, unsigned int& iLine, TCHAR* pBuffer)
{
ptrLines[iLine]=(TCHAR*)GlobalAlloc(GPTR,(_tcslen(pBuffer)+1)*sizeof(TCHAR));
if(!ptrLines[iLine])
{
MessageBox(hWnd,_T("Memory Allocation Error!"),_T("Not Good!"),MB_ICONERROR);
ErrorMemFree(ptrLines,iLine);
Sql.ODBCDisconnect(); //This procedure does a nice job of taking care of
return TRUE; //the rather monotonous sequence of operations
} //necessary allocate memory for a line of text, copy
_tcscpy(ptrLines[iLine],pBuffer); //it to the allocated address, and increment the iLine
iLine++; //count variable
return FALSE;
}
unsigned int iCreateDB(TCHAR* szDBName) //To create a Microsoft Access Database from
{ //scratch, you use SQLConfigDataSource(). I
TCHAR szCreate[256]; //believe if the database already exists at the
//specific location SQLInstallerError() returns
_tcscpy(szCreate,_T("CREATE_DB=")); //'11'.
_tcscat(szCreate,szDBName);
if(SQLConfigDataSource(0,ODBC_ADD_DSN,_T("Microsoft Access Driver (*.mdb)"),szCreate))
return TRUE;
else
return iInstallerError();
}
UINT blnMakeTable(SQL& Sql) //Uses SQL Create Table statement to add table
{ //to database represented by sql->hConn
TCHAR szQuery[256];
SQLHSTMT hStmt;
_tcscpy(szQuery,_T("CREATE TABLE Table1 (Id LONG NOT NULL PRIMARY KEY, Float_Point DOUBLE, Date_Field DATETIME, Text_Field CHAR(16));"));
SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==0)
{
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return(TRUE);
}
else
return(FALSE);
}
int GetRecordCount(SQL& Sql, unsigned int& iLine)
{
unsigned int iRecCt=0;
TCHAR szQuery[128];
SQLHSTMT hStmt;
long iJnk;
_tcscpy(szQuery,_T("SELECT Count(*) As RecordCount From Table1;"));
SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
SQLBindCol(hStmt,1,SQL_C_ULONG,&iRecCt,0,&iJnk);
if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)!=SQL_SUCCESS)
{
SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,Sql.szErrCode,&Sql.iNativeErrPtr,Sql.szErrMsg,512,&Sql.iTextLenPtr);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return -1;
}
else
{
SQLFetch(hStmt);
SQLCloseCursor(hStmt);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return iRecCt;
}
}
UINT blnInsert(SQL& sql, TCHAR** ptrLines, unsigned int& iLine, HWND hWnd, int iCountExistingRecords)
{
TCHAR* szStr[]={_T("My Birthday"),_T("Walk On Moon?"),_T("Some String"),_T("April Fools Day")};
TCHAR* szDate[]={_T("11/15/1952"),_T("6/30/1969"),_T("1/1/2006"),_T("4/1/2006")};
double dblNum[]={3.14159,1.23456,15.1234,0.54321};
TCHAR szQuery[100],szString[32],szBuffer[128]; //Let me give you a hint about something. If you decide
SQLINTEGER iNts=SQL_NTS; //to use raw ODBC as your database access methodology, the
UINT i,id,iRet=FALSE; //hard part is SQLBindParameter() for inserting prepared
TIMESTAMP_STRUCT ts; //SQL statements, and SQLBindCol() for selecting data. These
SQLINTEGER iJnk; //will inevitably take you some time to learn. I chose an
SQLHSTMT hStmt; //integer, a double, a data, and a char string so as to get
double dbl; //you started on the most common data types.
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Entering blnInsert()\n"));
_ftprintf(fp,_T(" iCountExistingRecords = %u\n"),iCountExistingRecords);
#endif
if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
{
_tcscpy((TCHAR*)szQuery,_T("INSERT INTO Table1(Id, Float_Point, Date_Field, Text_Field) VALUES(?,?,?,?);"));
if(blnLineFailed(hWnd,sql,ptrLines,iLine,szQuery))
return FALSE;
iLine++;
if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T(" SQLExecute(hStmt)")))
return FALSE;
if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("iId Double Date String 0=SQL_SUCCESS")))
return FALSE;
if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("========================================================================")))
return FALSE;
if(SQLPrepare(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
{
SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&id,0,&iJnk);
SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_DOUBLE,SQL_DOUBLE,0,0,&dbl,0,&iJnk);
SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_TIMESTAMP,16,0,&ts,0,&iJnk);
SQLBindParameter(hStmt,4,SQL_PARAM_INPUT,SQL_C_TCHAR,SQL_CHAR,31,0,szString,32,&iNts);
#if defined(MY_DEBUG)
_ftprintf(fp,_T("\n SQLExecute(hStmt)\n"));
_ftprintf(fp,_T(" iId Double Date String 0=SQL_SUCCESS \n"));
_ftprintf(fp,_T(" ========================================================================\n"));
#endif
for(i=0;i<4;i++)
{
id=i+iCountExistingRecords, dbl=dblNum[i];
ts=ParseDate(szDate[i],_T("mdy"),_T("/"));
_tcscpy(szString,szStr[i]);
if(SQLExecute(hStmt)==SQL_SUCCESS)
{
memset(szBuffer,0,128);
_stprintf(szBuffer,_T("%-6u%8.2f %-12.10s %-20s%6u"),id,dbl,szDate[i],szString,SQL_SUCCESS);
if(blnLineFailed(hWnd,sql,ptrLines,iLine,szBuffer))
return FALSE;
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" %u\t%f\t%s\t%s\t\t%u\n"),id,dbl,szDate[i],szString,SQL_SUCCESS);
#endif
}
else
{
SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,sql.szErrCode,&sql.iNativeErrPtr,sql.szErrMsg,512,&sql.iTextLenPtr);
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" sql.dr.szErrCode = %s\n"),sql.szErrCode);
_ftprintf(fp,_T(" sql.dr.szErrMsg = %s\n"),sql.szErrMsg);
#endif
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
}
iRet=TRUE;
#if defined(MY_DEBUG)
_ftprintf(fp,_T("\n"));
#endif
}
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
}
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Leaving blnInsert()\n\n"));
#endif
return iRet;
}
UINT blnDumpData(SQL& sql, TCHAR** ptrLines, unsigned int& iLine, HWND hWnd)
{
TCHAR szQuery[100],szBuffer[128],szDate[12];
SQLTCHAR szString[16];
TIMESTAMP_STRUCT ts;
SQLINTEGER iJnk;
SQLHSTMT hStmt;
double dblNum;
UINT iId;
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Entering blnDumpData()\n"));
#endif
if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
{
_tcscpy(szQuery,_T("SELECT Table1.Id,Table1.Float_Point,Table1.Date_Field,Table1.Text_Field FROM Table1;"));
SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,&iJnk);
SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,&iJnk);
SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,&iJnk);
SQLBindCol(hStmt,4,SQL_C_TCHAR,szString,sizeof(TCHAR)*16,&iJnk);
if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
{
if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("iId Double Date String 0=SQL_SUCCESS")))
return FALSE;
if(blnLineFailed(hWnd,sql,ptrLines,iLine,_T("========================================================================")))
return FALSE;
do
{
if(SQLFetch(hStmt)==SQL_NO_DATA)
break;
memset(szBuffer,0,sizeof(TCHAR)*128);
MkDate(ts,szDate);
_stprintf(szBuffer,_T("%-6u%8.2f %-12.10s %-16s %6u"),iId,dblNum,szDate,szString,SQL_SUCCESS);
if(blnLineFailed(hWnd,sql,ptrLines,iLine,szBuffer))
return FALSE;
} while(TRUE);
}
SQLCloseCursor(hStmt);
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Leaving blnDumpData()\n\n"));
#endif
return TRUE;
}
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Leaving blnDumpData()\n\n"));
#endif
return FALSE;
}
void AccessThread(void* pVoid)
{
unsigned int iLine=0,iScreenLinesNeeded=12,iReturn=0;
unsigned int iDatabaseReturn=0;
TCHAR** ptrLines=NULL;
HWND hMainWnd,hOutput;
TCHAR lpBuffer[512];
HCURSOR hArrow;
int iRecCt=0;
String s1;
SQL Sql;
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Entering AccessThread()\n"));
#endif
hOutput=FindWindowEx(0,0,_T("frmOutput"),_T("Data Dump Of Access Database With ODBC"));
hMainWnd=FindWindowEx(0,0,TEXT("SqlDemo"),TEXT("ODBC Demo"));
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" hMainWnd = %u\n"),hMainWnd);
_ftprintf(fp,_T(" hOutput = %u\n"),hOutput);
#endif
if(hOutput)
{
Sql.strDriver=_T("Microsoft Access Driver (*.mdb)");
iReturn=GetCurrentDirectory(512,lpBuffer);
Sql.strDBQ=lpBuffer;
Sql.strDBQ = Sql.strDBQ + _T("\\") + _T("TestData.mdb");
iDatabaseReturn=iCreateDB(Sql.strDBQ.lpStr());
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" iDatabaseReturn = %u\n"),iDatabaseReturn);
#endif
Sql.ODBCConnect();
if(iDatabaseReturn==1) //Created New Access Database OK
{
if(Sql.blnConnected==TRUE)
{
if(blnMakeTable(Sql))
iRecCt=GetRecordCount(Sql,iLine);
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" iRecCt = %u\n"),iRecCt);
#endif
iScreenLinesNeeded=iScreenLinesNeeded+iRecCt+12;
ptrLines=(TCHAR**)GlobalAlloc(GPTR,iScreenLinesNeeded*sizeof(TCHAR*));
if(!ptrLines)
{
MessageBox(hMainWnd,_T("Memory Allocation Error!"),_T("Not Good!"),MB_ICONERROR);
Sql.ODBCDisconnect();
return;
}
SetWindowLong(hOutput,0,(long)iScreenLinesNeeded);
SetWindowLong(hOutput,4,(long)ptrLines);
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,lpBuffer))
return;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,Sql.strConnectionString.lpStr()))
return;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("ODBC Connection Succeeded, Database Successfully Created, Table1 Successfully Created!")))
return;
if(blnInsert(Sql,ptrLines,iLine,hMainWnd,++iRecCt))
{
iLine++;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("blnInsert() Succeeded!")))
return;
}
else
{
iLine++;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("blnInsert() Failed!")))
{
hArrow=LoadCursor(NULL,IDC_ARROW);
SetClassLong(hMainWnd, GCL_HCURSOR, (LONG) hArrow);
return;
}
}
iLine++;
blnDumpData(Sql,ptrLines,iLine,hMainWnd);
Sql.ODBCDisconnect();
}
return;
}
if(iDatabaseReturn==11) //Database Apparently Already Exists!!!
{
if(Sql.blnConnected==TRUE)
{
iRecCt=GetRecordCount(Sql,iLine);
#if defined(MY_DEBUG)
_ftprintf(fp,_T(" iRecCt = %u\n"),iRecCt);
#endif
iScreenLinesNeeded=iScreenLinesNeeded+iRecCt+12;
ptrLines=(TCHAR**)GlobalAlloc(GPTR,iScreenLinesNeeded*sizeof(TCHAR*));
if(!ptrLines)
{
MessageBox(hMainWnd,_T("Memory Allocation Error!"),_T("Not Good!"),MB_ICONERROR);
Sql.ODBCDisconnect();
return;
}
SetWindowLong(hOutput,0,(long)iScreenLinesNeeded);
SetWindowLong(hOutput,4,(long)ptrLines);
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,lpBuffer))
return;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,Sql.strConnectionString.lpStr()))
return;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("ODBC Connection Succeeded, Database Successfully Created, Table1 Successfully Created!")))
return;
if(blnInsert(Sql,ptrLines,iLine,hMainWnd,++iRecCt))
{
iLine++;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("blnInsert() Succeeded!")))
return;
}
else
{
iLine++;
if(blnLineFailed(hMainWnd,Sql,ptrLines,iLine,_T("blnInsert() Failed!")))
return;
}
iLine++;
blnDumpData(Sql,ptrLines,iLine,hMainWnd);
Sql.ODBCDisconnect();
return;
}
else
{
MessageBox
(
hMainWnd,
_T("There is a problem. You may be trying to run SqlDemo from a directory ")
_T("where Windows Won't allow databases to be created. Documents and Settings ")
_T("and Program Files Are two such places. Try putting SqlDemo.exe into some.")
_T("folder which isn't one of Windows 'Special' folders."),
_T("Couldn't Open Database. It May Not Exist!"), MB_ICONERROR
);
hArrow=LoadCursor(NULL,IDC_ARROW);
SetClassLong(hMainWnd,GCL_HCURSOR,(LONG)hArrow);
return;
}
}
}
}
void btnAccess_OnClick(lpWndEventArgs Wea)
{
unsigned long hThread;
TCHAR lpBuffer[512];
HCURSOR hCursor;
HWND hOutput;
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Entering btnAccess_OnClick()\n"));
#endif
EnableWindow(GetDlgItem(Wea->hWnd,IDC_MS_ACCESS),FALSE);
hCursor=LoadCursor(NULL,IDC_WAIT);
SetClassLong(Wea->hWnd, GCL_HCURSOR, (LONG) hCursor);
SetCursor(hCursor);
_tcscpy(lpBuffer,_T("Data Dump Of Access Database With ODBC"));
hOutput=CreateWindowEx(0,_T("frmOutput"),lpBuffer,WS_OVERLAPPEDWINDOW|WS_VSCROLL,200,500,775,275,HWND_DESKTOP,0,GetModuleHandle(NULL),Wea->hWnd);
hThread=_beginthread(AccessThread,0,0);
WaitForSingleObject((HANDLE)hThread,INFINITE);
ShowWindow(hOutput,SW_SHOWNORMAL);
hCursor=LoadCursor(NULL,IDC_ARROW);
SetClassLong(Wea->hWnd,GCL_HCURSOR,(LONG)hCursor);
SetCursor(hCursor);
EnableWindow(GetDlgItem(Wea->hWnd,IDC_MS_ACCESS),TRUE);
#if defined(MY_DEBUG)
_ftprintf(fp,_T("Leaving btnAccess_OnClick()\n\n"));
#endif
}