ALTER VIEW "USER1"."VIEW1" as
select ID1,
      ID2,
      convert(varchar( 100 ), NAME1 || ' - ' || DESC1) as TYPE_NAME
from USER1.TABLE1 where ID3 >  0 

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

#include <sql.h>
#include <sqlext.h>
#include <sqldef.h>
#include <odbcinst.h>
#include <asaodbc.h>

BOOLEAN ConnectToDatabase(SQLCHAR *InConnectString);
void DisconnectFromDatabase(void);
void CloseCursor(void);
BOOLEAN Send(char *Format, ...);
BOOLEAN Fetch(void);
BOOLEAN ShowSqlError(char *FunctionName);

int GetColumnsCount(void);
char *GetColumnName(int ColumnIndex);
char *GetColumnAsString(int ColumnIndex);
char *GetFieldAsString(char *FieldName);
int GetColumnAsInteger(int ColumnIndex);
int GetFieldAsInteger(char *FieldName);
BOOLEAN IsColumnNull(int ColumnIndex);
BOOLEAN IsFieldNull(char *FieldName);

///// SqlUtils.c
#include "LogWindow.h"
#include "SqlUtils.h"

static HDBC hDbc;
static HENV hEnv;
HSTMT hStmt;
RETCODE RetCode;
static SQLCHAR SqlState[ 6 ], SqlMessage[SQL_MAX_MESSAGE_LENGTH];
static SQLINTEGER SqlError;
static SQLSMALLINT MessageLen;
static char Command[0x4000];

typedef struct {
	SQLSMALLINT ColumnType;
	SQLCHAR	ColumnName[ 64 ];
	char Data[ 8192 ];
	SQLINTEGER ColumnSize;
	SQLSMALLINT DecimalPoint;
	SQLLEN DataRetrieved;
} Column;

static Column *ResultSet;
static SQLSMALLINT ColumnsCount;


void SQL_CALLBACK my_msgproc(
   void *    sqlca,
   unsigned char     msg_type,
   long              code,
   unsigned short    len,
   char*             msg )
{
	if (msg_type == MESSAGE_TYPE_STATUS) {
		msg[len]= 0 ;
		Log(LOG_ITALIC, "%s\n", msg);
	}
}

BOOLEAN ConnectToDatabase(SQLCHAR *InConnectString) {
	SQLCHAR OutConnectString[ 1024 ];
	SQLCHAR FunctionName[ 32 ];
	int i;

	Log(LOG_DEBUG | LOG_ITALIC, "before decrypt: %s\n", InConnectString);
	//for (i=0; InConnectString[i]; i++) InConnectString[i] ^= 0xAA;
	Log(LOG_DEBUG | LOG_ITALIC, "after decrypt: %s\n", InConnectString);

	RetCode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
	if (RetCode != SQL_SUCCESS) {
		strcpy(FunctionName, "SQLAllocHandle(ENV)");
ENV_Error:
		for (i= 1 ; SQLGetDiagRec(SQL_HANDLE_ENV, hEnv, i, SqlState, &SqlError,
							SqlMessage, sizeof(SqlMessage), &MessageLen) != SQL_NO_DATA; i++) {
			Log(LOG_RED, "%s failed: %s %d %s\n", FunctionName, SqlState, SqlError, SqlMessage);
		}
		return FALSE;
	}

	RetCode = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3,  0 );
	if ((RetCode != SQL_SUCCESS) && ((RetCode != SQL_SUCCESS_WITH_INFO))) {
		strcpy(FunctionName, "SQLSetEnvAttr");
		goto ENV_Error;
	}

	RetCode = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
	if ((RetCode != SQL_SUCCESS) && ((RetCode != SQL_SUCCESS_WITH_INFO))) {
		strcpy(FunctionName, "SQLAllocHandle(DBC)");
DBC_Error:
		for (i= 1 ; SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, i, SqlState, &SqlError,
							SqlMessage, sizeof(SqlMessage), &MessageLen) != SQL_NO_DATA; i++) {
			Log(LOG_RED, "%s failed(%d): %s %d %s\n", FunctionName, RetCode, SqlState, SqlError, SqlMessage);
		}
		return FALSE;
	}

	RetCode = SQLDriverConnect( hDbc, NULL, InConnectString, SQL_NTS,
								OutConnectString, sizeof(OutConnectString), &MessageLen,
								SQL_DRIVER_NOPROMPT );
	if ((RetCode != SQL_SUCCESS) && ((RetCode != SQL_SUCCESS_WITH_INFO))) {
		strcpy(FunctionName, "SQLDriverConnect");
		goto DBC_Error;
	}

	RetCode = SQLSetConnectOption(hDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
	if ((RetCode != SQL_SUCCESS) && ((RetCode != SQL_SUCCESS_WITH_INFO))) {
		strcpy(FunctionName, "SQLSetConnectOption");
		goto DBC_Error;
	}

	RetCode = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
	if ((RetCode != SQL_SUCCESS) && ((RetCode != SQL_SUCCESS_WITH_INFO))) {
		strcpy(FunctionName, "SQLAllocHandle(STMT)");
		goto DBC_Error;
	}

	ColumnsCount= 0 ;
	ResultSet=NULL;

	// Support for ASA extension statement: Message to Client
	RetCode = SQLSetConnectAttr(hDbc, ASA_REGISTER_MESSAGE_CALLBACK,
								(SQLPOINTER) &my_msgproc, SQL_IS_POINTER );
	if( RetCode != SQL_SUCCESS ) {
		strcpy(FunctionName, "SQLSetConnectAttr");
		goto DBC_Error;
	}

//	SQLGetInfo(hDbc, SQL_DRIVER_ODBC_VER, OutConnectString, sizeof(OutConnectString), &i);
//	Log(LOG_ITALIC, "ODBC version = %s\n", OutConnectString);

	return TRUE;
}

void DisconnectFromDatabase() {
	SQLDisconnect( hDbc );
	SQLFreeConnect( hDbc );
	SQLFreeEnv( hEnv );
}

void CloseCursor() {
	SQLCloseCursor(hStmt);
	Log(LOG_DEBUG | LOG_ITALIC, "previous cursor closed, free %d columns\n", ColumnsCount);
	if (ResultSet != NULL) {
		free(ResultSet);
	}
	ResultSet=NULL;
	ColumnsCount= 0 ;
	Log(LOG_DEBUG | LOG_ITALIC, "Columns and ResultSet cleared\n");
}

BOOLEAN Send(char *Format, ...) {
	int i;
    va_list ap;

	// clean previous resultset	
	if (ResultSet != NULL) {
		CloseCursor();
	}

    va_start(ap, Format);
    vsprintf(Command, Format, ap);
    va_end(ap);
	Log(LOG_DEBUG | LOG_ITALIC, "Command created\n");

	Log(LOG_DEBUG | LOG_ITALIC, "%s\n", Command);
	RetCode = SQLExecDirect( hStmt, (SQLCHAR FAR*)Command, SQL_NTS );
	if( (RetCode != SQL_SUCCESS) && (RetCode != SQL_SUCCESS_WITH_INFO) && (RetCode != SQL_NO_DATA_FOUND) ) {
		return ShowSqlError("SQLExecDirect");
	}
	Log(LOG_DEBUG | LOG_ITALIC, "Command send\n");

	// if last command returns result set - prepare to fetch it.
	RetCode = SQLNumResultCols(hStmt, &ColumnsCount);
	if((RetCode != SQL_SUCCESS) && (RetCode != SQL_SUCCESS_WITH_INFO)) return ShowSqlError("SQLNumResultCols");
	if (ColumnsCount> 0 ) {
		SQLSMALLINT dummy;

		ResultSet = (Column*)malloc(ColumnsCount * sizeof(Column));
		for(i= 0 ; i<ColumnsCount; i++) {
			RetCode = SQLDescribeCol(hStmt, i+ 1 , ResultSet[i].ColumnName, sizeof(ResultSet[i].ColumnName),
						&dummy, &(ResultSet[i].ColumnType),
						&(ResultSet[i].ColumnSize), &(ResultSet[i].DecimalPoint), &dummy);
			if( (RetCode != SQL_SUCCESS) && (RetCode != SQL_SUCCESS_WITH_INFO) )
				return ShowSqlError("SQLDescribeCol");
			Log(LOG_DEBUG | LOG_ITALIC, "%s %d %d\n", ResultSet[i].ColumnName, ResultSet[i].ColumnType, ResultSet[i].ColumnSize);

			switch(ResultSet[i].ColumnType) {
				case SQL_VARCHAR:
				case SQL_LONGVARCHAR:
				case SQL_NUMERIC:
					ResultSet[i].ColumnType = SQL_CHAR;
					break;
				case SQL_BIGINT:
					ResultSet[i].ColumnType = SQL_C_UBIGINT;
			}

			// and bind column to created buffer
			RetCode = SQLBindCol(hStmt, i+ 1 , ResultSet[i].ColumnType, (SQLPOINTER)(ResultSet[i].Data),
								sizeof(ResultSet[i].Data), &(ResultSet[i].DataRetrieved));
			if( (RetCode != SQL_SUCCESS) && (RetCode != SQL_SUCCESS_WITH_INFO) )
				return ShowSqlError("SQLBindCol");
		}
	}

	return TRUE;
}

BOOLEAN Fetch() {
	if(ResultSet==NULL) return FALSE;

	RetCode=SQLFetch(hStmt);
	if( (RetCode == SQL_SUCCESS) || (RetCode == SQL_SUCCESS_WITH_INFO) ) return TRUE;
	else if (RetCode == SQL_NO_DATA) {
		SQLCloseCursor(hStmt);
		return FALSE;
	}
	else return ShowSqlError("SQLFetch");
}

int GetColumnsCount(void) { return ColumnsCount; }

char *GetColumnName(int ColumnIndex) {
	if (ColumnIndex>=ColumnsCount) {
		//Log(LOG_RED, "Result set has only %d columns, can not read column %d\n", ColumnsCount, ColumnIndex);
		return NULL;
	}
	return ResultSet[ColumnIndex].ColumnName;
}


BOOLEAN IsColumnNull(int ColumnIndex) {
	if (ResultSet[ColumnIndex].DataRetrieved == SQL_NULL_DATA) {
		return TRUE;
	}
	return FALSE;
}

char *GetColumnAsString(int ColumnIndex) {
	if (ColumnIndex>=ColumnsCount) {
		Log(LOG_RED, "Result set has only %d columns, can not read column %d\n", ColumnsCount, ColumnIndex);
		return NULL;
	}

	if (ResultSet[ColumnIndex].DataRetrieved == SQL_NULL_DATA) {
		SqlMessage[ 0 ]= 0 ;
		return SqlMessage;
	}

	switch(ResultSet[ColumnIndex].ColumnType) {
	case SQL_C_UBIGINT:
		sprintf(SqlMessage, "%lu", *((unsigned _int64*)(ResultSet[ColumnIndex].Data)));
		return (char*)(SqlMessage);

	case SQL_INTEGER:
		sprintf(SqlMessage, "%ld", *((long int*)(ResultSet[ColumnIndex].Data)));
		return (char*)(SqlMessage);
		

	case SQL_DOUBLE:
		sprintf(SqlMessage, "%lg",
			*((long double*)(ResultSet[ColumnIndex].Data)));
		return (char*)(SqlMessage);

	case SQL_DATE:
		if(	(((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->year ==  0 ) &&
			(((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->month ==  0 ) &&
			(((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->day ==  0 )) {
			SqlMessage[ 0 ]= 0 ;
		} else {
			sprintf(SqlMessage, "%04d-%02d-%02d", 
				((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->year,
				((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->month,
				((TIMESTAMP_STRUCT*)(ResultSet[ColumnIndex].Data))->day);
		}
		return (char*)(SqlMessage);

	case SQL_NUMERIC:
	case SQL_CHAR:
		//Log(LOG_DEBUG | LOG_ITALIC, "%s\n", (char*)(ResultSet[ColumnIndex].Data));
		return (char*)(ResultSet[ColumnIndex].Data);

	default:
		Log(LOG_RED, "Do not now how to convert field %s, type %d to string\n",
			ResultSet[ColumnIndex].ColumnName, ResultSet[ColumnIndex].ColumnType);
		return NULL;
	}
}

BOOLEAN IsFieldNull(char *ColumnName) {
	int i;
	for(i= 0 ; i<ColumnsCount; i++) {
		if (strcmpi(ColumnName, ResultSet[i].ColumnName)== 0 ) {
			if (ResultSet[i].DataRetrieved == SQL_NULL_DATA) {
				return TRUE;
			} else {
				return FALSE;
			}
		}
	}
	Log(LOG_RED, "Can not find column %s in current result set\n", ColumnName);
	return TRUE;
}

char *GetFieldAsString(char *ColumnName) {
	int i;
	for(i= 0 ; i<ColumnsCount; i++) {
		if (strcmpi(ColumnName, ResultSet[i].ColumnName)== 0 )
			return GetColumnAsString(i);
	}
	Log(LOG_RED, "Can not find column %s in current result set\n", ColumnName);
	return NULL;
}


int GetColumnAsInteger(int ColumnIndex) {
	if (ColumnIndex>=ColumnsCount) {
		Log(LOG_RED, "Result set has only %d columns, can not read column %d\n", ColumnsCount, ColumnIndex);
		return  0 ;
	}

	switch(ResultSet[ColumnIndex].ColumnType) {
	case SQL_C_UBIGINT:
		return (int)(*((unsigned _int64*)(ResultSet[ColumnIndex].Data)));

	case SQL_INTEGER:
		return *((long int*)(ResultSet[ColumnIndex].Data));

	case SQL_NUMERIC:
	case SQL_CHAR:
		return atoi((char*)(ResultSet[ColumnIndex].Data));

	default:
		Log(LOG_RED, "Do not now how to convert field %s, type %d to integer\n",
			ResultSet[ColumnIndex].ColumnName, ResultSet[ColumnIndex].ColumnType);
		return  0 ;
	}
}

int GetFieldAsInteger(char *ColumnName) {
	int i;
	for(i= 0 ; i<ColumnsCount; i++) {
		if (strcmpi(ColumnName, ResultSet[i].ColumnName)== 0 )
			return GetColumnAsInteger(i);
	}
	Log(LOG_RED, "Can not find column %s in current result set\n", ColumnName);
	return  0 ;
}



BOOLEAN ShowSqlError(char *FunctionName) {
	int i;
	for (i= 1 ; SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, i, SqlState, &SqlError,
						SqlMessage, sizeof(SqlMessage), &MessageLen) != SQL_NO_DATA; i++) {
		Log(LOG_RED, "%s failed: %s %d %s\n%s\n", FunctionName, SqlState, SqlError, SqlMessage, Command);
	}
	return FALSE;
}