I know many will start with ODBC, but slow down ;)
I have searched for ODBC and all I find is UnixODBC. Is this the same as ODBC?
Also how stable it is? I will be happy to know some apps/companies behind it!
I need support for MySQL, PostGreSQL, DB2, Oracle backends.
Thanks and sorry for being little funny!
Stefano Mtangoo 455 Senior Poster
Frederick2 189 Posting Whiz
I've been using ODBC for years. Its the very lowest level Api you can use. I'm pretty sure all other SQL Relational database access technologies are built on top of it. Perhaps ADO isn't; not sure of that.
Its platform independent. If you use it on Unix/Linux to access a database, and you would later want to access SQL Server or MS Access, there wouldn't probably be too much to change. For these reasons I'd highly recommend it. Far as I know Microsoft is still maintaining it; I think they have to.
Most newer folks don't like it because its procedural. No OOP. I've written very thin class wrappers around it that work very well for me. I have to admit I've never used it with anything other than Microsoft Access and SQL Server, plus some incidental use with Microsoft Excel. If you want my class wrapper to start with I'd be happy to post it. Actually, I'd like to see an example of a connection string for some of those databases you listed.
Stefano Mtangoo 455 Senior Poster
Thanks Fred!
Where do I download headers/libraries plus examples?
Is it the same as UnixODBC or are different?
I would like to see your wrapper!
Frederick2 189 Posting Whiz
I'm sure there is an implementation of ODBC for Unix. Its a database standard which applies to all the databases you listed. However, there are various levels of 'conformance' to the standard. That does not mean that some won't work with it, but rather that some optional features might not (most definitely aren't!) be implemented on all databases.
If you have Windows or access to it here is a little program that creates a Microsoft Access database ( a TestData.mdb file ), creates a Table1 in the database, writes a few records to the Table1, then dumps the data. If you have Microsoft Access you can open the database after the program runs. Its named TestData.mdb. It should be in whatever directory you are running the program from. Don't put this code in C:\Program Files or C:\Documents And Setting though. I don't believe it will work. At least not on XP. I'd recommend somewhere right off your C drive such as C:\OdbcDemo or C:\Code\OdbcDemo or something like that. This is a Microsoft thing I can't explain. Oh, one other thing. This program has to link to the two ODBC libraries, which in Visual Studio are ODBC32.lib and ODBCCP32.lib. With CodeBlocks or any of the GNU it will be libODBC32.a and libODBCCP32.a. This is important. It won't compile without static linking to those libs. I'll start posting the code now...
//Main.cpp
#include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "Strings.h"
#include "Sql.h"
#include "SqlProcs.h"
#if defined(MY_DEBUG)
FILE* fp=NULL;
#endif
int main()
{
unsigned int iReturn=0,iDatabaseReturn=0;
TCHAR lpBuffer[512];
String s1;
SQL Sql;
#if defined(MY_DEBUG)
fp=_tfopen(_T("Output.txt"),_T("w"));
#endif
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());
Sql.ODBCConnect();
if(iDatabaseReturn==1) //Created New Access Database OK
{
_tprintf(_T("TestData.mdb Successfully Created!\n"));
if(blnMakeTable(Sql))
{
_tprintf(_T("blnMakeTable() Succeeded! You Should Have A Table1 In TestData.Mdb!\n"));
if(blnInsert(Sql))
{
_tprintf(_T("\nblnInsert() Succeeded! You Should Now Have Data In Table1!\n\n"));
if(blnDumpData(Sql))
{
_tprintf(_T("\nKind Of Looks Like Everything Worked! No?"));
}
}
}
}
Sql.ODBCDisconnect();
#if defined(MY_DEBUG)
fclose(fp);
#endif
getchar();
return 0;
}
/*
Output
TestData.mdb Successfully Created!
blnMakeTable() Succeeded! You Should Have A Table1 In TestData.Mdb!
szQuery = INSERT INTO Table1(Id, Float_Point, Date_Field, Text_Field) VALUES(?,?,?,?);
SQLExecute(hStmt)
iId Double Date String 0=SQL_SUCCESS
========================================================================
1 3.14 11/15/1952 My Birthday 0
2 1.23 6/30/1969 Walk On Moon? 0
3 15.12 1/1/2006 Some String 0
4 0.54 4/1/2006 April Fools Day 0
blnInsert() Succeeded! You Should Now Have Data In Table1!
iId Double Date String 0=SQL_SUCCESS
========================================================================
1 3.14 11/15/1952 My Birthday 0
2 1.23 6/30/1969 Walk On Moon? 0
3 15.12 1/1/2006 Some String 0
4 0.54 4/1/2006 April Fools Day 0
Kind Of Looks Like Everything Worked! No?
*///Strings.cpp
#include <windows.h>
#include <tchar.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "Strings.h"
String::String() //Uninitialized Constructor
{
pStrBuffer=new TCHAR[MINIMUM_ALLOCATION];
pStrBuffer[0]='\0';
this->iAllowableCharacterCount=MINIMUM_ALLOCATION-1;
}
String::String(const TCHAR ch) //Constructor: Initializes with TCHAR
{
pStrBuffer=new TCHAR[MINIMUM_ALLOCATION];
pStrBuffer[0]=ch;
pStrBuffer[1]='\0';
iAllowableCharacterCount=MINIMUM_ALLOCATION-1;
}
String::String(const TCHAR* pStr) //Constructor: Initializes with TCHAR*
{
int iLen,iNewSize;
iLen=_tcslen(pStr);
iNewSize=(iLen/16+1)*16;
pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
_tcscpy(pStrBuffer,pStr);
}
String::String(const String& s) //Constructor Initializes With Another String, i.e., Copy Constructor
{
int iLen,iNewSize;
iLen=_tcslen(s.pStrBuffer);
iNewSize=(iLen/16+1)*16;
this->pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
_tcscpy(this->pStrBuffer,s.pStrBuffer);
}
String::String(const int iSize) //Constructor Creates String With Custom Sized
{ //Buffer (rounded up to paragraph boundary)
int iNewSize;
iNewSize=(iSize/16+1)*16;
pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
this->pStrBuffer[0]=_T('\0');
}
String::String(const TCHAR ch, int iCount)
{
int iNewSize;
iNewSize=(iCount/16+1)*16;
pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
for(int i=0; i<iCount; i++)
pStrBuffer[i]=ch;
pStrBuffer[iCount]=_T('\0');
}
String& String::operator=(const TCHAR ch) //Overloaded operator = for assigning a TCHAR to a String
{
this->pStrBuffer[0]=ch;
this->pStrBuffer[1]=_T('\0');
return *this;
}
String& String::operator=(const TCHAR* pStr) //Constructor For If Pointer To Asciiz String Parameter
{
int iLen,iNewSize;
iLen=_tcslen(pStr);
if(iLen<this->iAllowableCharacterCount)
_tcscpy(pStrBuffer,pStr);
else
{
delete [] pStrBuffer;
iNewSize=(iLen/16+1)*16;
pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
_tcscpy(pStrBuffer,pStr);
}
return *this;
}
String& String::operator=(const String& strRight) //Overloaded operator = for
{ //assigning another String to
int iRightLen,iNewSize; //a String
if(this==&strRight)
return *this;
iRightLen=_tcslen(strRight.pStrBuffer);
if(iRightLen < this->iAllowableCharacterCount)
_tcscpy(pStrBuffer,strRight.pStrBuffer);
else
{
iNewSize=(iRightLen/16+1)*16;
delete [] this->pStrBuffer;
this->pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
_tcscpy(pStrBuffer,strRight.pStrBuffer);
}
return *this;
}
bool String::operator==(const String strCompare)
{
if(_tcscmp(this->pStrBuffer,strCompare.pStrBuffer)==0) //_tcscmp
return true;
else
return false;
}
String& String::operator+(const TCHAR ch) //Overloaded operator + (Puts TCHAR in String)
{
int iLen,iNewSize;
TCHAR* pNew;
iLen=_tcslen(this->pStrBuffer);
if(iLen<this->iAllowableCharacterCount)
{
this->pStrBuffer[iLen]=ch;
this->pStrBuffer[iLen+1]='\0';
}
else
{
iNewSize=((this->iAllowableCharacterCount*EXPANSION_FACTOR)/16+1)*16;
pNew=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
_tcscpy(pNew,this->pStrBuffer);
delete [] this->pStrBuffer;
this->pStrBuffer=pNew;
this->pStrBuffer[iLen]=ch;
this->pStrBuffer[iLen+1]='\0';
}
return *this;
}
String& String::operator+(const TCHAR* pChar) //Overloaded operator + (Adds TCHAR literals
{ //or pointers to Asciiz Strings)
int iLen,iNewSize;
TCHAR* pNew;
iLen=_tcslen(this->pStrBuffer)+_tcslen(pChar);
if(iLen<this->iAllowableCharacterCount)
{
if(this->pStrBuffer)
_tcscat(this->pStrBuffer,pChar);
else
_tcscpy(this->pStrBuffer, pChar);
}
else
{
iNewSize=(iLen*EXPANSION_FACTOR/16+1)*16;
pNew=new TCHAR[iNewSize];
this->iAllowableCharacterCount = iNewSize-1;
if(this->pStrBuffer)
{
_tcscpy(pNew,this->pStrBuffer);
delete [] pStrBuffer;
_tcscat(pNew,pChar);
}
else
_tcscpy(pNew,pChar);
this->pStrBuffer=pNew;
}
return *this;
}
String& String::operator+(const String& strRight) //Overloaded operator + Adds
{ //Another String to the left
int iLen,iNewSize; //operand
TCHAR* pNew;
iLen=_tcslen(this->pStrBuffer) + _tcslen(strRight.pStrBuffer);
if(iLen < this->iAllowableCharacterCount)
{
if(this->pStrBuffer)
_tcscat(this->pStrBuffer,strRight.pStrBuffer);
else
_tcscpy(this->pStrBuffer,strRight.pStrBuffer);
}
else
{
iNewSize=(iLen*EXPANSION_FACTOR/16+1)*16;
pNew=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
if(this->pStrBuffer)
{
_tcscpy(pNew,this->pStrBuffer);
delete [] pStrBuffer;
_tcscat(pNew,strRight.pStrBuffer);
}
else
_tcscpy(pNew,strRight.pStrBuffer);
this->pStrBuffer=pNew;
}
return *this;
}
String String::Left(unsigned int iNum)
{
unsigned int iLen,i,iNewSize;
String sr;
iLen=_tcslen(this->pStrBuffer);
if(iNum<iLen)
{
iNewSize=(iNum*EXPANSION_FACTOR/16+1)*16;
sr.iAllowableCharacterCount=iNewSize-1;
sr.pStrBuffer=new TCHAR[iNewSize];
for(i=0;i<iNum;i++)
sr.pStrBuffer[i]=this->pStrBuffer[i];
sr.pStrBuffer[iNum]='\0';
return sr;
}
else
{
sr=*this;
return sr;
}
}
String String::Right(unsigned int iNum) //Returns Right$(strMain,iNum)
{
unsigned int iLen,i,j,iNewSize;
String sr;
iLen=_tcslen(this->pStrBuffer);
if(iNum<iLen)
{
iNewSize=(iNum*EXPANSION_FACTOR/16+1)*16;
sr.iAllowableCharacterCount=iNewSize-1;
sr.pStrBuffer=new TCHAR[iNewSize];
j=0;
for(i=iLen-iNum;i<=iLen;i++)
{
sr.pStrBuffer[j]=this->pStrBuffer[i];
j++;
}
sr.pStrBuffer[iNum]='\0';
return sr;
}
else
{
sr=*this;
return sr;
}
}
String String::Mid(unsigned int iStart, unsigned int iCount)
{
unsigned int iLen,i,j,iNewSize;
String sr;
iLen=_tcslen(this->pStrBuffer);
if(iStart && iStart<=iLen)
{
if(iCount && iStart+iCount-1<=iLen)
{
iNewSize=(iCount*EXPANSION_FACTOR/16+1)*16;
sr. iAllowableCharacterCount=iNewSize-1;
sr.pStrBuffer=new TCHAR[iNewSize];
j=0;
sr.pStrBuffer=new TCHAR[iNewSize];
for(i=iStart-1;i<iStart+iCount-1;i++)
{
sr.pStrBuffer[j]=this->pStrBuffer[i];
j++;
}
sr.pStrBuffer[iCount]='\0';
return sr;
}
else
{
sr=*this;
return sr;
}
}
else
{
sr=*this;
return sr;
}
}
String& String::Make(const TCHAR ch, int iCount) //Creates (Makes) a String with iCount TCHARs
{
if(iCount>this->iAllowableCharacterCount)
{
delete [] pStrBuffer;
int iNewSize=(iCount*EXPANSION_FACTOR/16+1)*16;
this->pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
}
for(int i=0; i<iCount; i++)
pStrBuffer[i]=ch;
pStrBuffer[iCount]=_T('\0');
return *this;
}
String& String::GetModulePath()
{
TCHAR szFileName[MAX_PATH];
TCHAR* pChar=NULL;
DWORD dwRet=0;
dwRet=GetModuleFileName(NULL,szFileName,MAX_PATH);
if(dwRet)
{
pChar=_tcsrchr(szFileName,(int)_T('\\')); //wcsrchr
szFileName[pChar-(TCHAR*)szFileName]=_T('\0');
if(dwRet>(unsigned)this->iAllowableCharacterCount)
{
delete [] pStrBuffer;
int iNewSize=(dwRet*EXPANSION_FACTOR/16+1)*16;
this->pStrBuffer=new TCHAR[iNewSize];
this->iAllowableCharacterCount=iNewSize-1;
}
_tcscpy(this->pStrBuffer,szFileName);
}
return *this;
}
String String::Remove(const TCHAR* pToRemove, bool blnCaseSensitive)
{
int i,j,iParamLen,iReturn=0;
bool blnFound=false;
if(*pToRemove==0)
return *this;
iParamLen=_tcslen(pToRemove);
i=0, j=0;
do
{
if(pStrBuffer[i]==0)
break;
if(blnCaseSensitive)
iReturn=_tcsncmp(pStrBuffer+i,pToRemove,iParamLen); //_tcsncmp
else
iReturn=_tcsnicmp(pStrBuffer+i,pToRemove,iParamLen); //__tcsnicmp
if(iReturn!=0)
{
if(blnFound)
pStrBuffer[j]=pStrBuffer[i];
j++, i++;
}
else //made a match
{
blnFound=true;
i=i+iParamLen;
pStrBuffer[j]=pStrBuffer[i];
j++, i++;
}
}while(1);
if(blnFound)
pStrBuffer[i-iParamLen]=_T('\0');
String sr=pStrBuffer;
return sr;
}
String String::Remove(TCHAR* pStr)
{
unsigned int i,j,iStrLen,iParamLen;
TCHAR *pThis, *pThat, *p;
bool blnFoundBadChar;
iStrLen=this->LenStr(); //The length of this
String sr((int)iStrLen); //Create new String big enough to contain original String (this)
iParamLen=_tcslen(pStr); //Get length of parameter (pStr) which contains chars to be removed
pThis=this->pStrBuffer;
p=sr.lpStr();
for(i=0; i<iStrLen; i++)
{
pThat=pStr;
blnFoundBadChar=false;
for(j=0; j<iParamLen; j++)
{
if(*pThis==*pThat)
{
blnFoundBadChar=true;
break;
}
pThat++;
}
if(!blnFoundBadChar)
{
*p=*pThis;
p++;
*p=_T('\0');
}
pThis++;
}
return sr;
}
String String::Retain(TCHAR* pStr)
{
unsigned int i,j,iStrLen,iParamLen;
TCHAR *pThis, *pThat, *p;
bool blnFoundGoodChar;
iStrLen=this->LenStr(); //The length of this
String sr((int)iStrLen); //Create new String big enough to contain original String (this)
iParamLen=_tcslen(pStr); //Get length of parameter (pStr) which contains chars to be retained
pThis=this->pStrBuffer; //pThis will point to this String's buffer, and will increment through string.
p=sr.lpStr(); //p will start by pointing to new String's buffer and will increment through new string
for(i=0; i<iStrLen; i++)
{
pThat=pStr;
blnFoundGoodChar=false;
for(j=0; j<iParamLen; j++)
{
if(*pThis==*pThat)
{
blnFoundGoodChar=true;
break;
}
pThat++;
}
if(blnFoundGoodChar)
{
*p=*pThis;
p++;
*p=_T('\0');
}
pThis++;
}
return sr;
}
/*
String& String::Retain(TCHAR* pStr)
{
unsigned int i,j,blnFoundGoodChar,iStrLen;
TCHAR *pNewStr,*p,*r,*pOrigNewStr;
pNewStr=new TCHAR[_tcslen(this->pStrBuffer)+1];
pOrigNewStr=pNewStr;
memset(pNewStr,_T('\0'),_tcslen(this->pStrBuffer));
p=pStrBuffer;
iStrLen=_tcslen(pStr);
for(i=0; i<_tcslen(this->pStrBuffer); i++)
{
r=pStr;
blnFoundGoodChar=0;
for(j=0;j<iStrLen;j++)
{
if(*p==*r)
{
blnFoundGoodChar=1;
break;
}
r++;
}
if(blnFoundGoodChar)
{
*pNewStr=*p;
pNewStr++;
p++;
*pNewStr=0;
}
else
p++;
}
delete [] pStrBuffer;
pStrBuffer=pOrigNewStr;
return *this;
}
*/
int String::InStr(const TCHAR ch)
{
int iLen,i;
iLen=_tcslen(this->pStrBuffer);
for(i=0;i<iLen;i++)
{
if(this->pStrBuffer[i]==ch)
return (i+1);
}
return 0;
}
int String::InStr(const TCHAR* pStr, bool blnCaseSensitive)
{
int i,iParamLen,iRange;
if(*pStr==0)
return 0;
iParamLen=_tcslen(pStr);
iRange=_tcslen(pStrBuffer)-iParamLen;
if(iRange>=0)
{
for(i=0;i<=iRange;i++)
{
if(blnCaseSensitive)
{
if(_tcsncmp(pStrBuffer+i,pStr,iParamLen)==0) //_tcsncmp
return i+1;
}
else
{
if(_tcsnicmp(pStrBuffer+i,pStr,iParamLen)==0) //__tcsnicmp
return i+1;
}
}
}
return 0;
}
int String::InStr(const String& s, bool blnCaseSensitive)
{
int i,iParamLen,iRange,iLen;
iLen=_tcslen(s.pStrBuffer);
if(iLen==0)
return 0;
iParamLen=iLen;
iRange=_tcslen(pStrBuffer)-iParamLen;
if(iRange>=0)
{
for(i=0;i<=iRange;i++)
{
if(blnCaseSensitive)
{
if(_tcsncmp(pStrBuffer+i,s.pStrBuffer,iParamLen)==0) //_tcsncmp
return i+1;
}
else
{
if(_tcsnicmp(pStrBuffer+i,s.pStrBuffer,iParamLen)==0) //__tcsnicmp
return i+1;
}
}
}
return 0;
}
void String::LTrim()
{
unsigned int i,iCt=0,iLenStr;
iLenStr=this->LenStr();
for(i=0;i<iLenStr;i++)
{
if(pStrBuffer[i]==32||pStrBuffer[i]==9)
iCt++;
else
break;
}
if(iCt)
{
for(i=iCt;i<=iLenStr;i++)
pStrBuffer[i-iCt]=pStrBuffer[i];
}
}
void String::RTrim()
{
unsigned int iCt=0, iLenStr;
iLenStr=this->LenStr()-1;
for(unsigned int i=iLenStr; i>0; i--)
{
if(this->pStrBuffer[i]==9||this->pStrBuffer[i]==10||this->pStrBuffer[i]==13||this->pStrBuffer[i]==32)
iCt++;
else
break;
}
this->pStrBuffer[this->LenStr()-iCt]=0;
}
void String::Trim()
{
this->LTrim();
this->RTrim();
}
unsigned int String::ParseCount(const TCHAR c) //returns one more than # of
{ //delimiters so it accurately
unsigned int iCtr=0; //reflects # of strings delimited
TCHAR* p; //by delimiter.
p=this->pStrBuffer;
while(*p)
{
if(*p==c)
iCtr++;
p++;
}
return ++iCtr;
}
void String::Parse(String* pStr, TCHAR delimiter)
{
unsigned int i=0;
TCHAR* pBuffer=0;
TCHAR* c;
TCHAR* p;
pBuffer=new TCHAR[this->LenStr()+1];
if(pBuffer)
{
p=pBuffer;
c=this->pStrBuffer;
while(*c)
{
if(*c==delimiter)
{
pStr[i]=pBuffer;
//printf("Assigned pStr[%u] In Parse()\n",i);
//printf("pStr[%u]=%s\n\n",i,pStr[i].lpStr());
p=pBuffer;
i++;
}
else
{
*p=*c;
p++;
*p=0;
}
c++;
}
pStr[i]=pBuffer;
delete [] pBuffer;
}
}
int String::iVal()
{
return _ttoi(this->pStrBuffer); //_ttoi
}
String String::CStr(const int iNum)
{
String sr;
_stprintf(sr.pStrBuffer,_T("%d"),iNum);
return sr;
}
String String::CStr(const unsigned int iNum)
{
String sr;
_stprintf(sr.pStrBuffer,_T("%u"),iNum);
return sr;
}
String String::CStr(const DWORD dwNum)
{
String sr;
_stprintf(sr.pStrBuffer,_T("%u"),(unsigned)dwNum);
return sr;
}
String String::CStr(const short int iNum)
{
String sr;
_stprintf(sr.pStrBuffer,_T("%d"),iNum);
return sr;
}
String String::CStr(const double dblNum)
{
String sr(32);
_stprintf(sr.pStrBuffer,_T("%f"),dblNum);
return sr;
}
int String::LenStr(void)
{
return _tcslen(this->pStrBuffer);
}
TCHAR* String::lpStr()
{
return pStrBuffer;
}
TCHAR String::GetChar(unsigned int iOffset)
{
return this->pStrBuffer[iOffset-1];
}
void String::SetChar(unsigned int iOneBasedOffset, TCHAR tcChar)
{
if((int)iOneBasedOffset<=this->iAllowableCharacterCount)
this->pStrBuffer[iOneBasedOffset-1]=tcChar;
}
void String::Print(bool blnCrLf)
{
_tprintf(_T("%s"),pStrBuffer);
if(blnCrLf)
_tprintf(_T("\n"));
}
String::~String() //String Destructor
{
delete [] pStrBuffer;
pStrBuffer=0;
}//SqlProcs.cpp
#include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "Strings.h"
#include "Sql.h"
#include "SqlProcs.h"
extern FILE* fp;
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;
}
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 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)
{
const TCHAR* szStr[]={_T("My Birthday"),_T("Walk On Moon?"),_T("Some String"),_T("April Fools Day")};
TCHAR* szDate[]={(TCHAR*)_T("11/15/1952"),(TCHAR*)_T("6/30/1969"),(TCHAR*)_T("1/1/2006"),(TCHAR*)_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"));
#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(?,?,?,?);"));
_tprintf(_T("szQuery = %s\n\n"),szQuery);
_tprintf(_T(" SQLExecute(hStmt)\n"));
_tprintf(_T("iId Double Date String 0=SQL_SUCCESS\n"));
_tprintf(_T("========================================================================\n"));
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+1;
dbl=dblNum[i];
ts=ParseDate(szDate[i],(TCHAR*)_T("mdy"),(TCHAR*)_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);
_tprintf(_T("%s\n"),szBuffer);
#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 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)
{
_tprintf(_T("iId Double Date String 0=SQL_SUCCESS\n"));
_tprintf(_T("========================================================================\n"));
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);
_tprintf(_T("%s\n"),szBuffer);
} 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;
}//Sql.cpp
#include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "Strings.h"
#include "Sql.h"
#if defined(MY_DEBUG)
extern FILE* fp;
#endif
SQL::SQL() //Constructor
{
ZeroMemory(szCnOut, 512);
ZeroMemory(szErrMsg,512);
strDBQ=_T("");
}
SQL::~SQL()
{
//Sql Destructor
}
void SQL::MakeConnectionString(void)
{
if(strDriver==_T("SQL Server"))
{
if(strDBQ==_T(""))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("SERVER=")+strServer+_T(";");
}
else
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("SERVER=")+strServer+_T(";")+ \
_T("DATABASE=") + strDatabase + _T(";") + _T("DBQ=") + strDBQ + _T(";");
}
}
else if(strDriver==_T("Microsoft Access Driver (*.mdb)"))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("DBQ=")+strDBQ+_T(";");
}
else if(strDriver==_T("Microsoft Excel Driver (*.xls)"))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("DBQ=")+strDBQ+_T(";");
}
}
void SQL::ODBCConnect(void)
{
TCHAR szCnIn[512];
UINT iResult;
MakeConnectionString();
SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
_tcscpy(szCnIn,strConnectionString.lpStr());
iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)_tcslen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
if(iResult==0 || iResult==1)
blnConnected=TRUE;
else
{
SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
blnConnected=FALSE;
SQLDisconnect(hConn);
SQLFreeHandle(SQL_HANDLE_DBC,hConn);
SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
_tprintf(_T("szErrCode = %s\n"),szErrCode);
_tprintf(_T("szErrMsg = %s\n"),szErrMsg);
}
}
void SQL::ODBCDisconnect(void)
{
if(blnConnected==TRUE)
{
SQLDisconnect(hConn);
SQLFreeHandle(SQL_HANDLE_DBC,hConn);
SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
blnConnected=FALSE;
}
}//Sql.h
#if !defined(SQL_H)
#define SQL_H
class SQL
{
public:
SQL();
~SQL();
void MakeConnectionString(void);
void ODBCConnect(void);
void ODBCDisconnect(void);
public:
String strConnectionString;
String strDatabase;
String strDriver;
String strServer;
String strDBQ;
TCHAR szCnOut[512];
short int iBytes;
SWORD swStrLen;
SQLHENV hEnvr;
SQLHDBC hConn;
SQLINTEGER iNativeErrPtr;
SQLSMALLINT iTextLenPtr;
SQLTCHAR szErrMsg[512];
SQLTCHAR szErrCode[8];
unsigned int blnConnected;
};
#endif//SqlProcs.h
#ifndef SqlProcs_h
#define SqlProcs_h
//#define MY_DEBUG //unremark for debug
unsigned int iInstallerError();
void MkDate(TIMESTAMP_STRUCT&, TCHAR*);
TIMESTAMP_STRUCT ParseDate(TCHAR*, TCHAR*, TCHAR*);
unsigned int iCreateDB(TCHAR*);
UINT blnMakeTable(SQL&);
int GetRecordCount(SQL&);
UINT blnInsert(SQL&);
UINT blnDumpData(SQL&);
#endif//Strings.h
#if !defined(STRINGS_H)
#define STRINGS_H
#define EXPANSION_FACTOR 2
#define MINIMUM_ALLOCATION 8
class __declspec(dllexport) String
{
public:
String(); //Uninitialized Constructor
String(const TCHAR); //Constructor Initializes String With TCHAR
String(const TCHAR*); //Constructor Initializes String With TCHAR*
String(const String&); //Constructor Initializes String With Another String (Copy Constructor)
String(const int); //Constructor Initializes Buffer To Specific Size
String(const TCHAR ch, const int iCount); //Constructor initializes String with int # of chars
String& operator=(const TCHAR); //Assigns TCHAR To String
String& operator=(const TCHAR*); //Assigns TCHAR* To String
String& operator=(const String&); //Assigns one String to another (this one)
String& operator+(const TCHAR); //For adding TCHAR to String
bool operator==(const String); //For comparing Strings
String& operator+(const TCHAR*); //For adding null terminated TCHAR array to String
String& operator+(const String&); //For adding one String to Another
String Left(unsigned int); //Returns String of iNum Left Most TCHARs of this
String Right(unsigned int); //Returns String of iNum Right Most TCHARs of this
String Mid(unsigned int, unsigned int); //Returns String consisting of number of TCHARs from some offset
String& Make(const TCHAR ch, int iCount); //Creates (Makes) a String with iCount TCHARs
String& GetModulePath(); //Returns reference To String Containing Path of Exe/dll running
String Remove(const TCHAR*, bool); //Returns A String With A Specified TCHAR* Removed
String Remove(TCHAR* pStr); //Returns A String With All The TCHARs In A TCHAR* Removed (Individual char removal)
String Retain(TCHAR* pStr); //Seems to return a String with some characters retained???
int InStr(const TCHAR); //Returns one based offset of a specific TCHAR in a String
int InStr(const TCHAR*, bool); //Returns one based offset of a particular TCHAR pStr in a String
int InStr(const String&, bool); //Returns one based offset of where a particular String is in another String
void LTrim(); //Returns String with leading spaces/tabs removed
void RTrim(); //Returns String with spaces/tabs removed from end
void Trim(); //Returns String with both leading and trailing whitespace removed
unsigned int ParseCount(const TCHAR); //Returns count of Strings delimited by a TCHAR passed as a parameter
void Parse(String*, TCHAR); //Returns array of Strings in first parameter as delimited by 2nd TCHAR delimiter
String CStr(const int); //Converts String to integer
String CStr(const unsigned int); //Converts String to unsigned int
String CStr(const DWORD);
String CStr(const short int); //Converts String to 16 bit int
String CStr(const double); //Converts String to double
int iVal(); //Returns int value of a String
int LenStr(void); //Returns length of string
TCHAR* lpStr(); //Returns address of pStrBuffer member variable
TCHAR GetChar(unsigned int); //Returns TCHAR at one based index
void SetChar(unsigned int, TCHAR); //Sets TCHAR at one based index
void Print(bool); //Outputs String to Console with or without CrLf
~String(); //String Destructor
private:
TCHAR* pStrBuffer;
int iAllowableCharacterCount;
};
#endif Stefano Mtangoo 455 Senior Poster
thanks again Freddy. AFAIK, Odbc is not part of C++ standard so it is neither in Mingw or Linux GCC. So where do I download headers/sources to compile? Or even ready .a and .h?
Thanks
Frederick2 189 Posting Whiz
AFAIK, Odbc is not part of C++ standard so it is neither in Mingw or Linux GCC. So where do I download headers/sources to compile? Or even ready .a and .h?
In terms of relational databases, what is in the C++ standard? My guess is nothing, but don't rely on me as being knowledeable about the C++ standard because I've never read it.
Having said that I've never used a Windows computer nor a 32 bit programming language where I couldn't easily use ODBC to connect to relational databases, among other things. I use PowerBASIC a lot...
and they have headers that allow me to use ODBC. Years ago I used to use the GNU / Bloodshed Dev-C++ and the necessary libs and headers were installed as part of the default install. Likewise Visual Studio 6 (circa 1999), and Visual Studio 2008. I now use the new cross platform Code::Blocks a lot too. That code I just posted was written in CodeBlocks.
The most basic Windows installation will install ODBC drivers for most databases; I'm even pretty sure for Oracle. I wouldn't bet my life on that one, but I've seen Oracle Drivers on my systems, and I've never installed any Oracle products.
I have some programming familiarity with Linux, but I've never done any database work on it, so I can't guide you there. If you are only using Linux, why don't you check the /bin and /lib subdirectories for those libs I mentioned and these includes...
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
I can't imagine you have any C++ installation without these files.
Stefano Mtangoo 455 Senior Poster
thanks Fred, I will check gcc installation
Stefano Mtangoo 455 Senior Poster
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>I can't imagine you have any C++ installation without these files.
They are there On Mingw. I hope there will be too in my GCC in Ubuntu!
Thanks a lot.
Now I have to find ODBC tutorial!
Frederick2 189 Posting Whiz
I've posted various tutorial material on the subject in various places. Here is about the same as I posted here but in C instead of C++, but I believe the code is somewhat more heavily commented...
http://www.powerbasic.com/support/pbforums/showthread.php?t=24912&highlight=ODBC+Demo
And here is a rather complex demo that is GUI but shows how to connect to Microsoft SQL Server, Microsoft Access, and Microsoft Excel. Also, it shows how to use SQLDrivers() to dump the ODBC database drivers on your system....
http://www.jose.it-berater.org/smfforum/index.php?board=378.0
As I mentioned, I never tried it on Linux. I have Ubuntu too; just never checked it out. These ODBC functions (the ODBC Api) should work exactly the same on Linux/Unix as on Windows though. If you get it working with My Sql or anything like that I'd appreciate knowing how to change or add to my cnnection string wrappers so as to include it so to speak. The way things are set up now, I have that MakeConnectionString() function...
void SQL::MakeConnectionString(void)
{
if(strDriver==_T("SQL Server"))
{
if(strDBQ==_T(""))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("SERVER=")+strServer+_T(";");
}
else
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("SERVER=")+strServer+_T(";")+ \
_T("DATABASE=") + strDatabase + _T(";") + _T("DBQ=") + strDBQ + _T(";");
}
}
else if(strDriver==_T("Microsoft Access Driver (*.mdb)"))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("DBQ=")+strDBQ+_T(";");
}
else if(strDriver==_T("Microsoft Excel Driver (*.xls)"))
{
strConnectionString=_T("DRIVER=");
strConnectionString=strConnectionString+strDriver+_T(";")+_T("DBQ=")+strDBQ+_T(";");
}
}where you choose SQL Server, Microsoft Access Driver, or Excel Driver, and that function puts together a connection string. That's the function that needs added to to support other databases.
When I first learned ODBC I printed out Microsoft's entire documentation, which comes to 1500 pages or so. Also, I bought a book. It is hard to learn. I want to emphasize though that even though the documentation is from Microsoft, it is fully usable from Linux. The function calls and parameters will be exactly the same. It was designed to be cross platform.
Good Luck.
fmadsen 0 Newbie Poster
A general solution is to use a class library such as Qt4 available from http://qt.nokia.com/. Here you'll find general classes for accessing all major RDBMS's (Oracle, MySQl, SQlite etc...). Qt is great...
Specifically for Oracle, a more difficult, but high performance method is the OCI or OCCI API available from oracles website for free.
Stefano Mtangoo 455 Senior Poster
A general solution is to use a class library such as Qt4 available from http://qt.nokia.com/. Here you'll find general classes for accessing all major RDBMS's (Oracle, MySQl, SQlite etc...). Qt is great...
Specifically for Oracle, a more difficult, but high performance method is the OCI or OCCI API available from oracles website for free.
The problem is, I have learned great deal of wxWidgets and I don't plan to learn QT for now. I have invested a lot of time in learning wx. May be when only alternative is QT ;)
Thanks
Frederick2 189 Posting Whiz
I find it hard to believe you are finding it hard to find links on ODBC. Here is a Wikipedial link...
http://en.wikipedia.org/wiki/Open_Database_Connectivity
I imagine wxWidgets must have some class wrappers on ODBC. If not you can use what I provided as a start. All those databases you listed in your first post are supported for sure. The beauty of ODBC is that you can use close to the same code for all. I say 'close to the same' because the standards allow for different levels of support. ODBC contains various functions that query the underlying proprietary database driver for functionalities supported. In that way your code can act appropriately to the context of its present environment.
Stefano Mtangoo 455 Senior Poster
thanks friend
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.