// **************************************************************************** //
//                                                                              //
//  Northgate Information Solutions                                             //
//  -------------------------------                                             //
//                                                                              //
//  Source File: RealDbTest.c                                                   //
//                                                                              //
//  Description: This is a sample 'C' program (unsupported) designed to test    //
//               an ODBC connection to a Reality database.                      //
//               SQL statements are executed and the results displayed.         //
//                                                                              //
//               To establish a connection there must be a valid PCSNI          //
//               system entry (winsni.ini) pointing to a Reality database       //
//               i.e.                                                           //
//                                                                              //
//               [DBASE90]                                                      //
//               protocol=DDATCP                                                //
//               host=magpie                                                    //
//               dbase=dbase9.0                                                 //
//               port=1203                                                      //
//                                                                              //
//               The user must also supply either...                            //
//                                                                              //
//               (a) A USER or SYSTEM DSN (Data Source Name).                   //
//               (b) A CONSTR (ConnectString).                                  //
//                                                                              //
//               Only one type maybe supplied.                                  //
//                                                                              //
//               Note                                                           //
//               ----                                                           //
//               File DSN's are not regonised.                                  // 
//               The CONSTR allows a connection to be tested without            //
//               having an ODBC Data Source (DSN) defined.                      //
//                                                                              //
//  Date       : 4th July 2001                                                  //
//                                                                              //
// **************************************************************************** //

#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

#define REALSQL_DRIVER "DRIVER=RealSQL Driver 32bit"

#define CREATE_TABLE   "CREATE TABLE"
#define DROP_TABLE     "DROP TABLE"
#define INSERT_ROW     "INSERT INTO"
#define UPDATE_COLUMN  "UPDATE"
#define DELETE_ROW     "DELETE FROM"

#define MAX_ERRMSG_LEN 255

#define VERBOSE if (dBase.bVerbose == true) printf
#define TRACE printf
#define SKIP_WHITE_SPACE(x) while (*x == '\0') x++
#define ISALPHA(x) if (isalnum(*x) != 1) usage()
#define CHECK_OPT(x, y) if (x == true) usage(); else y = true;
#define SET_PARAM(x) x = &argv[i][2]

// Structure containing column info returned in result set.
struct COLUMN
{
    SWORD   iColumnNo;
    SDWORD  iColumnWidth;
    SWORD   pcbDesc;
    PTR     rgbDesc[BUFSIZ];  
};

// Structure containing database connection and execution details.
struct DATABASE
{
    HENV    hEnv;
	HDBC    hDbc;
	HSTMT   hStmt;    
    SWORD   iColumnCount;
    SDWORD  iRowCount;
    RETCODE retCode;
    COLUMN *pColumns;
    CHAR   *szDsn;
    CHAR   *szStmt;
    CHAR   *szConStr;    
    CHAR   *szUserId;
    CHAR   *szUserPwd;
    CHAR   *szAccount;
    CHAR   *szAccountPwd; 
    bool    bDsn;
    bool    bConStr;
    bool    bVerbose;
    bool    bCreate;
    bool    bDrop;
    bool    bInsert;
    bool    bUpdate;
    bool    bDelete;
    bool    bProperties;
    
} dBase;

// **************************************************************************** 
//                                                                              
//  Method     : usage                                                         
//                                                                              
//  Description: display program usage and exit.                                
// 
// **************************************************************************** 
//
void usage()
{
    TRACE("\nUsage: RealDbTest -v -p [-d \"DSN\" | -c \"CONSTR\"] -s \"SQL statement\"\n");
    TRACE("\n");
    TRACE("where -v        - Verbose output.\n");
    TRACE("where -p        - Driver and database properties.\n");
    TRACE("      -d DSN    - ODBC Data Source Name (USER or SYSTEM).\n");
    TRACE("      -c CONSTR - A full connection string using the format\n");
    TRACE("           \"System=sss;Account=aaa;UID=uuu;PWD=ppp;{Logoption=[1|2|3]}\"\n");
    TRACE("                   sss=system name in winsni.ini\n");
    TRACE("                   aaa=Reality account name\n");
    TRACE("                   uuu=Database userid\n");
    TRACE("                   ppp=User password\n");
    TRACE("                   lll=Host logging option\n");

    exit(0);
}

// **************************************************************************** 
//                                                                              
//  Method     : getparams                                                      
//                                                                              
//  Description: initialise connection parameters.                              
//               parse command line arguments.                                  
//                                                                              
//  Parameters : argc - number of command lind arguments.                       
//               argv - pointer to command line arguments.                      
// 
//  Return     : none.  
//                                                          
// **************************************************************************** 
//
void getparams(int argc, char *argv[])
{
    // Initialisation
    dBase.bDsn = false;
    dBase.bConStr = false;
    dBase.bVerbose = false;
    dBase.bCreate = false;
    dBase.bDrop = false;
    dBase.bInsert = false;
    dBase.bUpdate = false;
    dBase.bDelete = false;
    dBase.iRowCount = 0;
    dBase.iColumnCount = 0;

    // Parse command line
    for (int i=1; i<argc; ++i)
    {
        if (argv[i][0] != '-')
            continue;

        switch (argv[i][1])
        {
            case 'd':   // DSN = ODBC Data Source Name, No ConnectString allowed.
                CHECK_OPT(dBase.bConStr, dBase.bDsn);
                SET_PARAM(dBase.szDsn);
                SKIP_WHITE_SPACE(dBase.szDsn);
                VERBOSE("Found DSN '%s'\n", dBase.szDsn);
                ISALPHA(dBase.szDsn);
                break;

            case 'c':   // ConnectString, no DSN allowed.
                CHECK_OPT(dBase.bDsn, dBase.bConStr);
                SET_PARAM(dBase.szConStr);
                SKIP_WHITE_SPACE(dBase.szConStr);
                VERBOSE("Found CONSTR '%s'\n", dBase.szConStr);
                ISALPHA(dBase.szConStr);
                break;

            case 's':   // SQL statement
                SET_PARAM(dBase.szStmt);
                SKIP_WHITE_SPACE(dBase.szStmt);
                VERBOSE("Found SQL statement '%s'\n", dBase.szStmt);
                ISALPHA(dBase.szStmt);
                break;

            case 'u':   // UserId and Pwd
                SET_PARAM(dBase.szUserId);
                SKIP_WHITE_SPACE(dBase.szUserId);
                VERBOSE("Found UserId, argv=%s\n", dBase.szUserId);
                ISALPHA(dBase.szStmt);
                dBase.szUserPwd = strchr(dBase.szUserId, ',');
                if (dBase.szUserPwd != NULL)
                {
                    *dBase.szUserPwd = '\0';
                    dBase.szUserPwd++;
                }
                VERBOSE("UserId=%s~%s\n", dBase.szUserId, dBase.szUserPwd);
                break;

            case '?':
            case 'h':
            case 'q':   // Help
                usage();
                break;

            case 'p':   // Properties
                dBase.bProperties = true;
                VERBOSE("Found Properties '%i'\n", dBase.bProperties);
                break;

            case 'v':   // Verbose
                dBase.bVerbose = true;
                VERBOSE("Found Verbose '%i'\n", dBase.bVerbose);
                break;

            default :
                usage();
        }
    }

    // Validate options.
    if (dBase.bProperties && (dBase.bDsn == true || dBase.bConStr == true))
        return;

    if ( (dBase.bDsn == false && dBase.bConStr == false) || dBase.szStmt == NULL )
        usage();

    // Check for update statement.
    if (_memicmp(dBase.szStmt, CREATE_TABLE, strlen(CREATE_TABLE)) == 0)
        dBase.bCreate = true;
    else if (_memicmp(dBase.szStmt, DROP_TABLE, strlen(DROP_TABLE)) == 0)
        dBase.bDrop = true;
    else if (_memicmp(dBase.szStmt, INSERT_ROW, strlen(INSERT_ROW)) == 0)
        dBase.bInsert = true;
    else if (_memicmp(dBase.szStmt, UPDATE_COLUMN, strlen(UPDATE_COLUMN)) == 0)
        dBase.bUpdate = true;
    else if (_memicmp(dBase.szStmt, DELETE_ROW, strlen(DELETE_ROW)) == 0)
        dBase.bDelete = true;
   
}

// **************************************************************************** 
//                                                                              
//  Method     : disconnect                                                      
//                                                                              
//  Description: free resources and disconnect from the database.                                                                
//                                                                              
//  Parameters : none.                                           
// 
//  Return     : none.  
//                                                          
// **************************************************************************** 
//
void disconnect()
{
    GlobalFree(GlobalHandle(dBase.pColumns));

    if (dBase.hStmt != NULL)
		SQLFreeStmt(dBase.hStmt, SQL_DROP);

    if (dBase.hDbc != NULL)
	    SQLDisconnect(dBase.hDbc);

    if (dBase.hDbc != NULL)
		SQLFreeConnect(dBase.hDbc);

    if (dBase.hEnv != NULL)
		SQLFreeEnv(dBase.hEnv);

    VERBOSE("Disconnected.\n");
}

// **************************************************************************** 
//                                                                              
//  Method     : error                                                      
//                                                                              
//  Description: extract and display SQL error message.
//                                                                              
//  Parameters : none.                                           
// 
//  Return     : SQL_SUCCESS if there are no errors or data truncation 
//               occurred; SQL_ERROR for any other result.
//                                                          
// **************************************************************************** 
//
RETCODE error()
{
    UCHAR  szSqlState[16];
    UCHAR  szErrorMsg[MAX_ERRMSG_LEN];
    SDWORD pfNativeError;
    SWORD  cbErrorMsgMax = MAX_ERRMSG_LEN-1;
    SWORD  pcbErrorMsg;

    dBase.retCode = SQLError(dBase.hEnv, dBase.hDbc, dBase.hStmt, 
        (UCHAR FAR*)szSqlState, (SDWORD FAR*)&pfNativeError, 
        (UCHAR FAR*)szErrorMsg, cbErrorMsgMax, (SWORD FAR*)&pcbErrorMsg);

    if (dBase.retCode == SQL_NO_DATA_FOUND)
        return SQL_SUCCESS;

    if (strcmp((char*)szSqlState, "01004") == 0) // Data truncated.
        return SQL_SUCCESS;

    TRACE("SQL_ERROR: SQLState='%s'\n           Message ='%s'\n", szSqlState, szErrorMsg);

    return SQL_ERROR;
}

// **************************************************************************** 
//                                                                              
//  Method     : getInfo                                                      
//                                                                              
//  Description: extract database specific information.
//                                                                              
//  Parameters : fInfoType      - type of information to return. 
//               rgbInfoValue   - pointer to storage for the information.
//               cbInfoValueMax - maximum length of the rgbInfoValue buffer. 
//               pcbInfoValue   - total number of bytes (excluding the null 
//                                termination byte for character data) 
//                                available to return in rgbInfoValue.                                        
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool getInfo(UWORD fInfoType, PTR rgbInfoValue, SWORD cbInfoValueMax, SWORD FAR *pcbInfoValue)
{

    dBase.retCode = SQLGetInfo(dBase.hDbc, fInfoType, rgbInfoValue, cbInfoValueMax, pcbInfoValue);

    if (dBase.retCode == SQL_SUCCESS)
        return true;
    else
    {
        error();
        return false;
    }
}

bool displayProperties()
{
    char buf[BUFSIZ];
    short pcbInfoValue;

    // Driver info
    getInfo(SQL_DRIVER_NAME, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("Driver name        : '%s'\n", buf);

    getInfo(SQL_DRIVER_VER, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("Driver version     : '%s'\n", buf);

    getInfo(SQL_DRIVER_ODBC_VER, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("Driver ODBC version: '%s'\n", buf);

    TRACE("\n");

    // Database info
    getInfo(SQL_DATABASE_NAME, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("Database name      : '%s'\n", buf);

    getInfo(SQL_DBMS_NAME, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("DBMS name          : '%s'\n", buf);

    getInfo(SQL_DBMS_VER, buf, BUFSIZ-1, &pcbInfoValue);

    TRACE("DBMS version       : '%s'\n", buf);  

    return true;
}

// **************************************************************************** 
//                                                                              
//  Method     : connect                                                      
//                                                                              
//  Description: allocate a connection environment and coonect to the Reality
//               database. A connection maybe established using either an
//               pre-defined ODBC data source or by passing a ConnectString
//               containing all of the connection details, including a valid
//               PCSNI system entry to a database. 
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool connect()
{
    char szConnStrIn[BUFSIZ];
    char szConnStrOut[BUFSIZ];
    int pcbConnStrOut;

	dBase.retCode = SQLAllocEnv(&dBase.hEnv);              
	
    if (dBase.retCode == SQL_SUCCESS) 
	{
		dBase.retCode = SQLAllocConnect(dBase.hEnv, &dBase.hDbc); 
		
        if (dBase.retCode == SQL_SUCCESS) 
		{
            if (dBase.bConStr)
            {
                strcpy(szConnStrIn, REALSQL_DRIVER);
                strcat(szConnStrIn, ";");
                strcat(szConnStrIn, dBase.szConStr);

                dBase.retCode = SQLDriverConnect(dBase.hDbc, NULL, 
                    (UCHAR FAR*)szConnStrIn, SQL_NTS, (UCHAR FAR*)szConnStrOut, 
                        BUFSIZ, (SWORD FAR *)&pcbConnStrOut, SQL_DRIVER_NOPROMPT);
            }
            else
            {
			    dBase.retCode = SQLConnect(dBase.hDbc, (SQLCHAR *)dBase.szDsn,                                   
			        SQL_NTS, (SQLCHAR *)dBase.szUserId, SQL_NTS, 
				        (SQLCHAR *)dBase.szUserPwd, SQL_NTS);
            }

			if (dBase.retCode == SQL_SUCCESS || dBase.retCode == SQL_SUCCESS_WITH_INFO)
			{
				dBase.retCode = SQLSetConnectOption(dBase.hDbc,SQL_ACCESS_MODE,
                                          SQL_MODE_READ_ONLY); 
				
                if (dBase.retCode == SQL_SUCCESS )
                {
                    char buf[BUFSIZ];
                    short pcbInfoValue;

                    getInfo(SQL_DATABASE_NAME, buf, BUFSIZ-1, &pcbInfoValue);
                    
                    VERBOSE("Connected.\n");
					return true;
                }
                else
                {
                    error();
                    TRACE("Unable to set connection option ACCESS=READ_ONLY\n");
                    disconnect();
                }
            }
            else
            {
                error();
            }

            TRACE("\nUnable to connect.\n");
        }
    }

    return false;
}

// **************************************************************************** 
//                                                                              
//  Method     : execute                                                      
//                                                                              
//  Description: allocate a statement handle, and execute the SQL statement.
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool execute()
{
    
    dBase.retCode = SQLAllocStmt(dBase.hDbc, &dBase.hStmt);
    
    if (dBase.retCode == SQL_SUCCESS) 
    {
        VERBOSE("Statement handle allocated 0x%0x\n", dBase.hStmt);

		dBase.retCode = SQLExecDirect(dBase.hStmt, (SQLCHAR *)dBase.szStmt,SQL_NTS);
		
        if (dBase.retCode != SQL_SUCCESS) 
		{
            error();
            
            if (dBase.hStmt != NULL)
		        SQLFreeStmt(dBase.hStmt, SQL_DROP);

			TRACE("Unable to execute statement '%s'\n", dBase.szStmt);		
            
            return false;
		}

		return true;
    }
    else
    {
        error();

        TRACE("unable to allocate statement.\n");
    }

    error();

    return false;
}

// **************************************************************************** 
//                                                                              
//  Method     : getColumnCount                                                      
//                                                                              
//  Description: get the number of columns in the result set.
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool getColumnCount()
{
    dBase.retCode = SQLNumResultCols(dBase.hStmt, (SWORD FAR *)&dBase.iColumnCount);

    if (dBase.retCode == SQL_SUCCESS)
        return true;
    else
    {
        error();
        return false;
    }
}

// **************************************************************************** 
//                                                                              
//  Method     : getRowCount                                                      
//                                                                              
//  Description: get the number of rows affected in an update statement.
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool getRowCount()
{
    dBase.retCode = SQLRowCount(dBase.hStmt, (SDWORD FAR *)&dBase.iRowCount);

    if (dBase.retCode == SQL_SUCCESS)
        return true;
    else
    {
        error();
        return false;
    }
}

// **************************************************************************** 
//                                                                              
//  Method     : displayColumnNames                                                      
//                                                                              
//  Description: display the column names in the result set.
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
bool displayColumnNames()
{

    char buf[BUFSIZ];

    /* get some storage to hold the array of COLUMN structures */
    HANDLE hcolarray = (HANDLE)GlobalAlloc (GMEM_MOVEABLE | GMEM_ZEROINIT,
        sizeof(COLUMN)*(dBase.iColumnCount+1));

    if (hcolarray == NULL || 
        (dBase.pColumns = (COLUMN *) GlobalLock (hcolarray)) == NULL)
    {
        return false;
    }

    TRACE("\n");

    for (int i=0; i<dBase.iColumnCount; ++i)
    {
        dBase.pColumns[i].iColumnNo = i+1;

        dBase.retCode = SQLColAttributes(dBase.hStmt, i+1, SQL_COLUMN_NAME,
            dBase.pColumns[i].rgbDesc, BUFSIZ-1, 
            (SWORD FAR *)&dBase.pColumns[i].pcbDesc, NULL);

        dBase.retCode = SQLColAttributes(dBase.hStmt, i+1, SQL_COLUMN_DISPLAY_SIZE, //PRECISION, //DISPLAY_SIZE,
            NULL, 0, NULL, &dBase.pColumns[i].iColumnWidth);

        memset(buf, '.', BUFSIZ);

        memcpy(buf, (const void *)dBase.pColumns[i].rgbDesc, (size_t)dBase.pColumns[i].pcbDesc);

        buf[dBase.pColumns[i].iColumnWidth] = '\0';

        TRACE("%s ", buf);
    }

    TRACE("\n");

    return true;
}

// **************************************************************************** 
//                                                                              
//  Method     : displayData                                                      
//                                                                              
//  Description: fetch each row of the result; get the data for each column
//               and display its value.
//                                                                              
//  Parameters : none.                                       
// 
//  Return     : TRUE if call was successfull; else false.
//                                                          
// **************************************************************************** 
//
void displayData()
{
    char rgbValue[BUFSIZ];
    char buf[BUFSIZ];
    SDWORD pcbValue;

    while (dBase.retCode != SQL_NO_DATA)
    {
	    dBase.retCode = SQLFetch(dBase.hStmt);

	    if (dBase.retCode != SQL_SUCCESS && dBase.retCode != SQL_SUCCESS_WITH_INFO)
	    {
            if (dBase.retCode == SQL_NO_DATA)
                continue;

            error();
		    return;
	    }

	    for (int iCol=1; iCol <= dBase.iColumnCount; ++iCol)
        {
            dBase.retCode = SQLGetData(dBase.hStmt, iCol, SQL_C_CHAR, 
			    (PTR)rgbValue, (SDWORD)BUFSIZ-1, (SDWORD FAR *)&pcbValue);
	        
            if (dBase.retCode != SQL_SUCCESS) 
	        {	
		        if (error() == SQL_ERROR)
                {
                    TRACE("SQLGetData returned error.\n");
                    return;
                }
	        }

            memset(buf, ' ', BUFSIZ);

            if (pcbValue != SQL_NULL_DATA)
                memcpy(buf, (const void *)rgbValue, (size_t)pcbValue);

            buf[dBase.pColumns[iCol-1].iColumnWidth] = '\0';

            TRACE("%s ", buf);
        }

        dBase.iRowCount++;

        TRACE("\n");
    }

    TRACE("%d Rows Listed.\n", dBase.iRowCount);
}

// **************************************************************************** 
//                                                                              
//  Method     : main                                                      
//                                                                              
//  Description: connect, execute, and display result.
//                                                                              
//  Parameters : argc - number of command lind arguments.                       
//               argv - pointer to command line arguments.                                         
// 
//  Return     : 0
//                                                          
// **************************************************************************** 
//
int main(int argc, char *argv[])
{
    getparams(argc, argv);

    if (connect())
    {
        if (dBase.bProperties)
        {
            displayProperties();
            goto discon;
        }

        if (execute())
        {
            if (getColumnCount())
            {
                if (dBase.iColumnCount <= 0)
                {
                    // An update statement of some description.

                    if (getRowCount())
                    {
                        if (dBase.bCreate == true)
                            TRACE("Table created.\n");
                        else if (dBase.bDrop)
                            TRACE("Table dropped.\n");
                        else if (dBase.bInsert)
                            TRACE("%d Rows inserted.\n", dBase.iRowCount);
                        else if (dBase.bUpdate)
                            TRACE("%d Rows updated.\n", dBase.iRowCount);
                        else if (dBase.bDelete)
                            TRACE("%d Rows deleted.\n", dBase.iRowCount);
                    }
                }
                else
                {
                    displayColumnNames();

                    displayData();
                }
            }
        }
    }

discon:
    disconnect();

	return 0;
}