//
// Copyright(c) Vieka 2004
//
// Vieka eSQL sample code
//
// isql is an interactive command line tool for standard SQL queries
// using ODBC.
//
// isql usage:
//	isql -u none -p none -d "dsn=esql;database=c:\animal.db;timeout=100"
//

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

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

#define MAX_ROW_SIZE 4096
#define MAX_COL_SIZE 40

#define USAGE "Usage: %s [-u Username] [-p Password] [-d DSN]\n"

#define MIN(x,y) ((x>y) ? (y) : (x))

void eSQLDisplayError(HENV henv, HDBC hdbc, HSTMT hstmt)
{
	SQLCHAR		szSqlstate[16];
	SQLCHAR		szMessageText[SQL_MAX_MESSAGE_LENGTH];
	SQLINTEGER	nNativeError;
	SQLSMALLINT	nTextLength;
	RETCODE		rc;
	
	while (1)
	{
		rc = SQLError(henv, hdbc, hstmt,
			szSqlstate, &nNativeError, szMessageText,
			SQL_MAX_MESSAGE_LENGTH - 1, &nTextLength);
		if (rc == SQL_NO_DATA_FOUND)
			break;
		printf("SQLSTATE = %s\n",szSqlstate);
		printf("NATIVE ERROR = %d\n",nNativeError);
		szMessageText[nTextLength] = '\0';
		printf("MESSAGE = %s\n\n",szMessageText);
	}
}

void eSQLDisconnect(HENV henv, HDBC hdbc, HSTMT hstmt)
{
	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);
}

RETCODE eSQLConnect(char *szDSN, char *szUsername, char *szPassword, HENV *henv, HDBC *hdbc, HSTMT *hstmt)
{
	HENV		hEnv = SQL_NULL_HENV;
	HDBC		hDbc = SQL_NULL_HDBC;
	HSTMT		hStmt = SQL_NULL_HSTMT;
	RETCODE		rc;
	
	rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		printf("Failed to allocate environment handle\n");
		exit(-1);
	}
	
	rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
		(SQLPOINTER)SQL_OV_ODBC3,
		SQL_IS_INTEGER);
	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
		if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
		{
			printf("Failed to allocate connection handle\n");
			exit(-1);
		}
	}
	
	printf("Connecting to '%s' ...\n", szDSN);
	rc = SQLConnect(hDbc,szDSN,SQL_NTS,szUsername,SQL_NTS,
		szPassword,SQL_NTS);
	if ((rc != SQL_SUCCESS) &&(rc != SQL_SUCCESS_WITH_INFO))
	{
		printf("Failed to connect to '%s'\n", szDSN);
		eSQLDisplayError(hEnv, hDbc, SQL_NULL_HSTMT);
		return (SQL_INVALID_HANDLE);
	}
	
	printf("Connected to '%s'\n", szDSN);

	rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	{
		printf("Failed to allocate statement handle\n");
		eSQLDisplayError(hEnv, hDbc, hStmt);
		eSQLDisconnect(hEnv, hDbc, hStmt);
		exit(-1);
	}

	*henv = hEnv;
	*hdbc = hDbc;
	*hstmt = hStmt;

	return(rc);
}

void eSQLDisplayHeader(HSTMT hstmt, int nColumns)
{
	SQLCHAR			szColumnName[SQL_MAX_COLUMN_NAME_LEN];
	SQLUSMALLINT	nCol;
	SQLSMALLINT		nNameLength;
	SQLSMALLINT		nDataType;
	SQLUINTEGER		nColumnSize;
	SQLSMALLINT		nDecimalDigits;
	SQLSMALLINT		nNullable;
	RETCODE			rc;
	char			szBorder[MAX_ROW_SIZE];
	UINT			i;

	memset(szBorder, 0, sizeof(MAX_ROW_SIZE));

	for (nCol = 1; nCol <= nColumns; nCol++)
	{
		rc = SQLDescribeCol(hstmt, nCol, szColumnName, 
				SQL_MAX_COLUMN_NAME_LEN, &nNameLength, &nDataType, 
				&nColumnSize, &nDecimalDigits, &nNullable);
		
		nColumnSize = MIN(nColumnSize, MAX_COL_SIZE);
		printf(" %-*s ", nColumnSize, szColumnName);

		for (i=0; i<nColumnSize+2; i++)
		{
			if (i==0 || i==(nColumnSize + 1))
				strcat(szBorder, " ");
			else
				strcat(szBorder, "-");
		}

		if (nCol == nColumns)
			printf("\n");
	}
	
	printf("%s\n", szBorder);
}

void eSQLFetchData(HSTMT hstmt, HENV henv, HDBC hdbc)
{
	SQL_TIMESTAMP_STRUCT	sqlTimeStamp;
    SQL_TIME_STRUCT			sqlTime;
	long					sqlInt;
	float					sqlFloat = 0.0f;
	double					sqlDouble = 0.0f;
	char					szColumnName[SQL_MAX_COLUMN_NAME_LEN];
	char					szData[1024];
	char					*szBuffer = NULL;
	short					nColumns=0;
	SQLUSMALLINT			nCol = 0;
	SQLINTEGER				nRow = 0;
	SQLSMALLINT				nDataType;
	SQLSMALLINT				nNameLength;
	SQLUINTEGER				nColumnSize;
	SQLSMALLINT				nDecimalDigits;
	SQLSMALLINT				nNullable;
	SQLINTEGER				nDataLength = SQL_NULL_DATA;
    RETCODE					rc;
	char					sz[8];

	SQLNumResultCols(hstmt, &nColumns);
	if (nColumns == 0)
	{
		// Not a SELECT statement
		SQLRowCount(hstmt, &nRow);
		if (nRow != 0)
		{
			printf("%d %s affected\n", nRow,(nRow > 1) ? "rows" : "row");
		}
		SQLCloseCursor(hstmt);
		return;
	}
	else 
	{
		// SELECT statement	
		eSQLDisplayHeader(hstmt, nColumns);
		for (nRow = 0; ; nRow++)
		{
			rc = SQLFetch(hstmt);
			if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
				break;
			for (nCol=1; nCol <= nColumns; nCol++)
			{
				rc = SQLDescribeCol(hstmt, nCol, szColumnName, 
					SQL_MAX_COLUMN_NAME_LEN, &nNameLength, &nDataType, 
					&nColumnSize, &nDecimalDigits, &nNullable);
		
				nColumnSize = MIN(nColumnSize,MAX_COL_SIZE);
				switch (nDataType)
				{		
				case SQL_DECIMAL:
				case SQL_NUMERIC:
					SQLGetData(hstmt, nCol, SQL_C_DEFAULT, szData, 512, &nDataLength);
					printf(" %-*.*s ", nColumnSize, nColumnSize, szData);
					break;
				case SQL_CHAR:
				case SQL_VARCHAR:
				case SQL_LONGVARCHAR:
	                SQLGetData(hstmt, nCol, SQL_C_CHAR, sz, 0, &nDataLength);
					if (nDataLength > 0)
					{
						szBuffer = (char*)calloc(nDataLength +1, sizeof(char));
						if (SQLGetData(hstmt, nCol, SQL_C_CHAR, szBuffer, nDataLength + 1, 
							&nDataLength) == SQL_SUCCESS)
						{
							if ((SQLUINTEGER)nDataLength > nColumnSize)
							{
								nColumnSize = nDataLength;
							}
							printf(" %-*s ", nColumnSize, szBuffer);
						}
						free(szBuffer);
					}
					break;
				case SQL_TINYINT:
				case SQL_INTEGER:
				case SQL_SMALLINT:
					rc = SQLGetData(hstmt, nCol, SQL_C_LONG, &sqlInt, sizeof(sqlInt), NULL);
					if (rc == SQL_SUCCESS)
						printf(" %-*d ", nColumnSize, sqlInt);
					break;
				case SQL_TYPE_TIMESTAMP:
					if (SQLGetData(hstmt, nCol, SQL_C_TYPE_TIMESTAMP, &sqlTimeStamp,
						sizeof(sqlTimeStamp), NULL) != SQL_ERROR)
					{
						sprintf(szData, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d", 
							sqlTimeStamp.year, 
							sqlTimeStamp.month, 
							sqlTimeStamp.day,
							sqlTimeStamp.hour,
							sqlTimeStamp.minute,
							sqlTimeStamp.second);
						printf(" %-*s ", nColumnSize, szData);
					}  
					break;
				case SQL_TYPE_TIME:
					if (SQLGetData(hstmt, nCol, SQL_C_TYPE_TIME, &sqlTime, sizeof(sqlTime), NULL) != SQL_ERROR)
					{
						sprintf(szData, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d", 
							sqlTimeStamp.year, 
							sqlTimeStamp.month, 
							sqlTimeStamp.day,
							sqlTimeStamp.hour,
							sqlTimeStamp.minute,
							sqlTimeStamp.second);
						printf(" %-*s ", nColumnSize, szData);
					}
					break;
				case SQL_FLOAT:
				case SQL_DOUBLE:
					if (SQLGetData(hstmt, nCol, SQL_C_DOUBLE, &sqlDouble,
						sizeof(sqlDouble),
						NULL) != SQL_ERROR)
					{
						printf(" %-*.8f ", nColumnSize, sqlDouble);
					}
					break;
				case SQL_REAL:
					if (SQLGetData(hstmt, nCol, SQL_C_FLOAT, &sqlFloat,
						sizeof(sqlFloat), NULL) != SQL_ERROR)
					{
						printf(" %-*.8f ", nColumnSize, sqlDouble);
					}
					break;
				default:
					break;
				}
		
				if (nCol==nColumns)
					printf("\n");
			}
		}
		SQLCloseCursor(hstmt);
		printf("\n%d %s affected\n", nRow,(nRow > 1) ? "rows" : "row");
	}	
}

void eSQLExec(HENV henv, HDBC hdbc, HSTMT hstmt, char *sqlStmt)
{
	RETCODE              rc;
	SQLINTEGER           nRow = 0;    
	SWORD                nColumns = 0; 
	
	printf("Executing ...\n\n");
	rc = SQLExecDirect(hstmt, sqlStmt, SQL_NTS);
	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		eSQLFetchData(hstmt, henv, hdbc);
	}
	else
	{
		eSQLDisplayError(henv, hdbc, hstmt);
		SQLCloseCursor(hstmt);
	}
}

char *readline(char *szPrompt)
{
	char *szLine;
	int nLine;
	int n;
	int eol;
	
	if (szPrompt && *szPrompt)
	{
		printf("%s",szPrompt);
		fflush(stdout);
	}
	nLine = 100;
	szLine = malloc(nLine);
	if (szLine==0) 
		return 0;
	n = 0;
	eol = 0;
	while(!eol)
	{
		if (n+100>nLine)
		{
			nLine = nLine*2 + 100;
			szLine = realloc(szLine, nLine);
			if (szLine==0) 
				return 0;
		}
		if (fgets(&szLine[n], nLine - n, stdin)==0)
		{
			if (n==0)
			{
				free(szLine);
				return 0;
			}
			szLine[n] = 0;
			eol = 1;
			break;
		}
		while(szLine[n])
		{ 
			n++; 
		}
		if (n>0 && szLine[n-1]=='\n')
		{
			n--;
			szLine[n] = 0;
			eol = 1;
		}
	}
	szLine = realloc(szLine, n+1);
	return szLine;
}

void eSQLProcessQuery(HENV henv, HDBC hdbc, HSTMT hstmt)
{
	char   *sqlStmt = NULL;
	
	while(1)
	{
		sqlStmt = readline("eSQL> ");
		if (sqlStmt[0] == '.' &&
			strstr(sqlStmt, ".q") || 
			strstr(sqlStmt, ".quit") || 
			strstr(sqlStmt, ".exit"))
		{
			if (sqlStmt)
				free(sqlStmt);

			break;
		}
		
		eSQLExec(henv, hdbc, hstmt, sqlStmt);
		if (sqlStmt)
			free(sqlStmt);
	}
}

int main(int argc, char * argv[])
{
	HDBC    hdbc;
	HENV    henv;
	HSTMT   hstmt;
	RETCODE rc;
	char	szUsername[32];
	char	szPassword[32];
	char	szDSN[1024];
	int		i;

	if (argc <= 1 || argc > 7)
	{
		printf(USAGE, argv[0]);
		return(1);
	}

	for (i=1;i<argc;i++) 
	{
		if (!stricmp(argv[i], "-h"))
		{
			printf(USAGE, argv[0]);
			return(1);
		}
		else if (!stricmp(argv[i], "-p"))
			strcpy((char*)szPassword, argv[++i]);
		else if (!stricmp(argv[i], "-u"))
			strcpy((char*)szUsername, argv[++i]);
		else if (!stricmp(argv[i], "-d")) 
			strcpy((char*)szDSN, argv[++i]);
	}
	
	rc = eSQLConnect(szDSN, szUsername, szPassword, &henv, &hdbc, &hstmt);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
		return(-1);     
	
	eSQLProcessQuery(henv, hdbc, hstmt);
	eSQLDisconnect(henv, hdbc, hstmt);
	
	return(0);
}