SQL or Some Database - Brand Newbie

[Lamblion]

I have never programmed a databse. I want to do a SIMPLE database using ONLY VC++ 2010. I am ASSUMINIG that I should use SQL. If I'm wrong, please correct me.

In any case, I haven't a clue how to start. Could someone write me a SIMPLE program -- as absolutely simple as possible -- showing me how to do a simple database with one or two entries in it.

I don't care if it's Win32 or Win Console. I just need a basic skeleton and then I can figure it out from there.

[sohguanh]

First and foremost you need to decide on which database to use. SQL aka Structured Query Language are supported in most database to provide developers/users an easy to way to query/insert/update/delete data in the database.

Once you have decided on the database, then check to see what libaries or API they offer for your program to 'call' them. Programming language may matter in some cases.

Popular Open Source database include MySQL and SQLite (this came to my attention in my Android development but I think it's SQL support is still in it's infancy compared to MySQL)

[Lamblion]

I just want a simple database to keep track of, say, a name and a topic and a quotation to match with that name and topic, and to be able to search the database to do that. I'm fine with using SQL, but I don't know how to even BEGIN to start coding that in VC++ 2010.

[freddie1]

I just converted some code to as simple of an example as I can produce. I'll test it some more in a bit to see what sort of warts it develops with MS C++ 9 (I don't have VS 2010. I have VS 2008 Pro). I used Code::Blocks MinGW 10.05. It uses ODBC to create an MS Access 2000 database named TestData.mdb. Having succeeded at that, it creates a table in the database named Table1. Having further succeeded at that, it writes four records to the table as follows...

                                                         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

This should nicely show how to insert several types of variables. Also, the program should work even if you don't have Microsoft Access. By default any installation of a Microsoft operating system installs drivers for SQL Server, MS Access, and several other formats. However, if you do have Access or an Access Viewer you'll be able to open the database after a successful run of the program.

Its a C++ console mode program. Don't create the project for it under Documents and Settings or Users in Win7. A better place would be a directory directly off of your C or D drives. Microsoft's operating systems can be cranky as to where they'll create databases. I'll start posting the code next post...

[freddie1]

//Main.cpp

#include <windows.h>
#include <stdio.h>
#include <odbcinst.h>
#include <sql.h>
#include <sqlext.h>
#include "MySql.h"

unsigned int iInstallerError()
{
 DWORD pErr;
 char 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;
}


unsigned int iCreateDB(char const* szDBName)    //To create a Microsoft Access Database from
{                                               //scratch, you use SQLConfigDataSource().  I
 char szCreate[256];                            //believe if the database already exists at the
                                                //specific location SQLInstallerError() returns
 strcpy(szCreate,"CREATE_DB=");                 //'11'.
 strcat(szCreate,szDBName);
 if(SQLConfigDataSource(0,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb)",szCreate))
    return TRUE;
 else
    return iInstallerError();
}


TIMESTAMP_STRUCT ParseDate(char* szDate, char* szFormat, char* szDelimiter)
{
 UINT i=0,j=0,k=0;
 TIMESTAMP_STRUCT ts;
 char buf[3][8];             //buf[0] for month, buf[1] for day, buf[2] for year
 char* p;

 memset(buf,0,sizeof(buf));  //zero out buf[]
 p=szDate;
 for(i=0;i<strlen((char*)szDate);i++)
 {
     if(*p!=*szDelimiter)
     {
        buf[j][k++]=*p;
        buf[j][k+1]='\0';
     }
     else
     {
        j++;
        k=0;
     }
     p++;
 }
 if(!stricmp((char*)szFormat,"MDY"))
 {
    ts.month=(short)atoi(buf[0]);
    ts.day=(short)atoi(buf[1]);
    ts.year=(short)atoi(buf[2]);
 }
 if(!stricmp((char*)szFormat,"DMY"))
 {
    ts.day=(short)atoi(buf[0]);
    ts.month=(short)atoi(buf[1]);
    ts.year=(short)atoi(buf[2]);
 }
 if(!stricmp((char*)szFormat,"YMD"))
 {
    ts.year=(short)atoi(buf[0]);
    ts.month=(short)atoi(buf[1]);
    ts.day=(short)atoi(buf[2]);
 }

 return ts;
}


void MkDate(TIMESTAMP_STRUCT& ts, char* szBuffer)
{
 char szMonth[4],szDay[4],szYear[8];

 sprintf(szMonth,"%u",ts.month);
 sprintf(szDay,"%u",ts.day);
 sprintf(szYear,"%u",ts.year);
 strcpy(szBuffer,szMonth);
 strcat(szBuffer,"/");
 strcat(szBuffer,szDay);
 strcat(szBuffer,"/");
 strcat(szBuffer,szYear);

 return;
}


UINT blnMakeTable(SQL& Sql)          //Uses SQL Create Table statement to add table
{                                    //to database represented by sql->hConn
 char szQuery[256];
 SQLHSTMT hStmt;

 strcpy(szQuery,"CREATE TABLE Table1 (Id LONG  NOT NULL PRIMARY KEY, Float_Point DOUBLE, Date_Field DATETIME, Text_Field CHAR(30));");
 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);
}


UINT blnInsert(SQL& sql)
{
 char* szStr[]={(char*)"My Birthday",(char*)"Walk On Moon?",(char*)"Some String",(char*)"April Fools Day"};
 char* szDate[]={(char*)"11/15/1952",(char*)"6/30/1969",(char*)"1/1/2006",(char*)"4/1/2006"};
 double dblNum[]={3.14159,1.23456,15.1234,0.54321};
 char  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(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    strcpy((char*)szQuery,"INSERT INTO Table1(Id, Float_Point, Date_Field, Text_Field) VALUES(?,?,?,?);");
    printf("                                                         SQLExecute(hStmt)\n");
    printf("iId      Double           Date           String           0=SQL_SUCCESS\n");
    printf("========================================================================\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);
       for(i=0;i<4;i++)
       {
           id=i+1, dbl=dblNum[i];
           ts=ParseDate(szDate[i],(char*)"mdy",(char*)"/");
           strcpy(szString,szStr[i]);
           if(SQLExecute(hStmt)==SQL_SUCCESS)
           {
              memset(szBuffer,0,128);
              printf("%-6u%8.2f           %-12.10s  %-20s%6u\n",id,dbl,szDate[i],szString,SQL_SUCCESS);
           }
           else
           {
              SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,sql.szErrCode,&sql.iNativeErrPtr,sql.szErrMsg,512,&sql.iTextLenPtr);
              printf("  sql.dr.szErrCode = %s\n",sql.szErrCode);
              printf("  sql.dr.szErrMsg  = %s\n",sql.szErrMsg);
              SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
              return FALSE;
           }
       }
       iRet=TRUE;
       printf("\n");
    }
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
 }

 return iRet;
}


UINT blnDumpData(SQL& sql)
{
 char szQuery[100],szBuffer[128],szDate[12];
 SQLTCHAR szString[20];
 TIMESTAMP_STRUCT ts;
 SQLINTEGER iJnk;
 SQLHSTMT hStmt;
 double dblNum;
 UINT iId;

 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    strcpy(szQuery,"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,20,&iJnk);
    if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       printf("iId      Double           Date           String           0=SQL_SUCCESS\n");
       printf("========================================================================\n");
       do
       {
          if(SQLFetch(hStmt)==SQL_NO_DATA)
             break;
          memset(szBuffer,0,128);
          MkDate(ts,szDate);
          printf("%-6u%8.2f           %-12.10s  %-20s%6u\n",iId,dblNum,szDate,szString,SQL_SUCCESS);
       }  while(TRUE);
    }
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return TRUE;
 }

 return FALSE;
}


int main()
{
 char szBuffer[512];
 std::string s1;
 SQL Sql;

 Sql.strDriver="Microsoft Access Driver (*.mdb)";
 GetCurrentDirectory(512,szBuffer);
 s1=szBuffer;
 Sql.strDBQ=s1+"\\"+"TestData.mdb";
 printf("Sql.strDBQ = %s\n",Sql.strDBQ.c_str());
 if(iCreateDB(Sql.strDBQ.c_str())==TRUE)
 {
    Sql.ODBCConnect();
	   if(Sql.blnConnected==TRUE)
    {
       printf("Sql.blnConnected = True\n");
       if(blnMakeTable(Sql))
       {
          printf("blnMakeTable() Succeeded!\n");

          if(blnInsert(Sql))
          {
             printf("blnInsert() Succeeded!\n\n\n");
             blnDumpData(Sql);
          }
       }
       Sql.ODBCDisconnect();
    }
 }
 getchar();

 return 0;
}

[freddie1]

Here is MySql.h

//MySql.h
#if !defined(SQL_H)
#define SQL_H
#include <string>
#include <sqltypes.h>

class SQL
{
 public:
 SQL();
 ~SQL();
 void MakeConnectionString(void);
 void ODBCConnect(void);
 void ODBCDisconnect(void);

 public:
 std::string       strConnectionString;
 std::string       strDatabase;
 std::string       strDriver;
 std::string       strServer;
 std::string       strDBQ;
 char              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 

...and Sql.cpp

//Sql.cpp
#include  <windows.h>
#include  <string>
#include  <cstdio>
#include  <odbcinst.h>
#include  <sqltypes.h>
#include  <sql.h>
#include  <sqlext.h>
#include  "MySql.h"


SQL::SQL() //Constructor
{
 ZeroMemory(szCnOut, 512);
 ZeroMemory(szErrMsg,512);
 strDBQ="";
}


SQL::~SQL()
{
 //Sql Destructor
}


void SQL::MakeConnectionString(void)
{
 if(strDriver=="SQL Server")
 {
    if(strDBQ=="")
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";";
    }
    else
    {
       strConnectionString="DRIVER=";
       strConnectionString=strConnectionString+strDriver+";"+"SERVER="+strServer+";"+ \
       "DATABASE=" + strDatabase + ";" + "DBQ=" + strDBQ + ";";
    }
 }
 else if(strDriver=="Microsoft Access Driver (*.mdb)")
 {
    strConnectionString="DRIVER=";
    strConnectionString=strConnectionString+strDriver+";"+"DBQ="+strDBQ+";";
 }
 else if(strDriver=="Microsoft Excel Driver (*.xls)")
 {
    strConnectionString="DRIVER=";
    strConnectionString=strConnectionString+strDriver+";"+"DBQ="+strDBQ+";";
 }
}


void SQL::ODBCConnect(void)
{
 char szCnIn[512];
 unsigned int 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);
 strcpy(szCnIn,strConnectionString.c_str());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)strlen(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);
 }
}


void SQL::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
    blnConnected=FALSE;
 }
}

[freddie1]

A successful run on my Windows 7 Pro 64 bit OS looks like this...

Sql.strDBQ = C:\Code\CodeBlks\MkAccessDB\TestData.mdb
Sql.blnConnected = True
blnMakeTable() Succeeded!
                                                         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!


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

[cls20433]

really, this guy gets all kind of replies that quick and I can't get a single one on echo printing or end-of-file loops .. thought this place was good for c++ hep

[freddie1]

This code is an extreme simplification of code I have posted here...

http://www.jose.it-berater.org/smfforum/index.php?board=378.0

There is quite a bit of tutorial material there. However, at that location I have the example terribly complicated in that I used all the TCHAR macros; it is a GUI example; it has buttons to connect and do the same thing with MS Access, SQL Server, and MS Excel; it uses threads; and lastly, it uses an old version of my personal string class instead of the std lib string class. In the code above I modified it to use the std lib string class so folks wouldn't have to find mine and include and compile that with it too.

[freddie1]

One last thing (well, maybe not the last). To get it to link you need to link against odbc32.lib and odbc32cp.lib. For MinGW these would be libodbc32.a and libodbccp32.a. Let me know how it works.

[freddie1]

Just tested with VS 2008 Pro and it worked fine. These are some additional considerations for best results in that environment...

1) Change stricmp() in Main.cpp to _stricmp (there are three in ParseDate()). It decided it doesn't like stricmp anymore;

2) It doesn't like a lot of my code. To get rid of more warnings paste this in Project Properties - C++ tab - Preprocessor Definitions

_CRT_SECURE_NO_WARNINGS

3) In project properties change character set from UNICODE to 'not set'.

Oh! One other thing! Once you get it working you can only run it once because it will fail at creating another instance of the same database at the same location. To run it again you have to delete the TestData.mdb file. In my complicated example at the link I gave I added code to account for that and just keep adding records to the same database and table, but this simplified version doesn't do that.

[Lamblion]

WOW!!! Thanks VERY much freddie1.

I'm going to copy your code and play with it. This should give me a good start as to how to go about it. And I have Access and Office 2010, so I should also have no trouble viewing it. Thanks again. This is really good.

[Lamblion]

I've also bookmarked your tutorial. From just a quick perusal, that appears to have some very good info as well, even if it may be more complicated.

[freddie1]

Just a note abbout ODBC. Its an old but still widely used database standard. It was the first database standard, I believe, and came about in the early 90s. The way it works is that every database vendor that subscribes to the standard is free to develop their database any way they wish in terms of its internal coding, structure, and plumbing, but they must implement ODBC conformant drivers that implement the ODBC Api. That way, if an end user wishes to change database backends, none or little of the client code needs to be changed (at least in theory).

My SQL class is a very thin wrapper on the low level and exceedingly ugly Sql functions. Its primary purpose is to wrap SQLDriverConnect(). However, I added some nice OOP functionality to it to make connecting easy once you know your driver and database name. I've never worked with SQL Lite or MySql. I'd like to add functionality to it to set up those connection strings, but just never got around to it!

Don't feel dumb if it looks like a lot to learn. It kind of is!

[andywestken]

If you're using Windows and Access, MSSQL Express, etc, you might want to consider using ADO rather than ODBC. ADO and OLE DB (for the hardcore) have taken over from ODBC in the Windows world.

Microsoft ActiveX Data Objects (ADO)
http://msdn.microsoft.com/en-us/library/ms675532%28v=VS.85%29.aspx

ADO Code Examples in Visual C++
http://msdn.microsoft.com/en-us/library/ms677563%28v=VS.85%29.aspx

If you want a lightweight, local only SQL implementation, you might be interested in SQLite
http://www.sqlite.org
SQLite Tutorial
http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

I'm not sure if SQLte is the friendliest of APIs, but it is light! (And quite popular, I gather).

Regards

Andy

[Lamblion]

Thanks, freddie1. You've been extremely helpful to me, not only in this, but in other stuff in the past, and I appreciate it. You've given me at least a handle on how to start.

Andy I appreciate those references. I'm going to go through them carefully because ultimately I'm targeting this for a Windows app.

I could just write my own files, which I've done a fair amount of in the past, but a database simply makes a whole lot more sense for what I want to do, so I'm just going to bite the bullet and become proficient in it, just like I did with pointers.