// CLOBTest.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#define _AFXDLL
#ifndef _AFX_NO_DB_SUPPORT
#include <afxdb.h>			// MFC ODBC database classes
#endif // _AFX_NO_DB_SUPPORT

bool HandleError(SQLRETURN rcode, HSTMT hStmt);
int MyLOBTest();

int _tmain(int argc, _TCHAR* argv[])
{
    int nRet = MyLOBTest();

    printf("Return code is %d. Press <ENTER>...", nRet); getchar();
    return nRet;
}

int MyLOBTest()
{
    HENV hDBEnv;
    HDBC hDBConnection;
	RETCODE rcode = ::SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hDBEnv);
    if (rcode!=SQL_SUCCESS)
		return -1;
	
    rcode = ::SQLSetEnvAttr(hDBEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    if (rcode!=SQL_SUCCESS)
		return -1;

	rcode = ::SQLAllocHandle(SQL_HANDLE_DBC, hDBEnv, &hDBConnection);
    if (rcode!=SQL_SUCCESS)
		return -1;
	
    //SQLCHAR* szConnectionString = (SQLCHAR*)"DRIVER={MySQL ODBC 5.1 Driver};SERVER=cmbte0042;PORT=3306;DATABASE=prove;OPTION=134217728;UID=provetech;PWD=PROVEtech:TA";
    //SQLCHAR* szConnectionString = (SQLCHAR*)"DRIVER={MySQL ODBC 3.51 Driver};SERVER=cmbte0042;PORT=3306;DATABASE=prove;OPTION=134217728;UID=provetech;PWD=PROVEtech:TA";
    SQLCHAR* szConnectionString = (SQLCHAR*)"DRIVER={MySQL ODBC 3.51 Driver};SERVER=cmbte0042;PORT=3306;DATABASE=prove;UID=provetech;PWD=PROVEtech:TA";
    unsigned char conStringOut[2048];
	rcode = ::SQLDriverConnect(hDBConnection, 0, szConnectionString, SQL_NTS, conStringOut, 2048, NULL, SQL_DRIVER_NOPROMPT);
    if (rcode!=SQL_SUCCESS)
		return -1;

    SQLUINTEGER uPacketSize;
    SQLINTEGER nLen;
    rcode = ::SQLGetConnectAttr(hDBConnection, SQL_ATTR_PACKET_SIZE, &uPacketSize, 0, &nLen);
    if (rcode!=SQL_SUCCESS)
		return -1;

    rcode = ::SQLSetConnectAttr(hDBConnection, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
    if (rcode!=SQL_SUCCESS)
		return -1;

        // Drop the table
	HSTMT hStmt;
    rcode = ::SQLAllocHandle(SQL_HANDLE_STMT, hDBConnection, &hStmt);
	if (!HandleError(rcode, hStmt))
		return -1;
	rcode = ::SQLExecDirect(hStmt, (SQLCHAR*)"DROP TABLE mytable", SQL_NTS);
	if (!HandleError(rcode, hStmt))
		printf("Table mytable may not exist!\r\n");
    rcode = ::SQLFreeStmt(hStmt, SQL_CLOSE);
	if (!HandleError(rcode, hStmt))
		return -1;

    rcode = ::SQLEndTran(SQL_HANDLE_DBC, hDBConnection, SQL_COMMIT);
	if (!HandleError(rcode, hStmt))
		return -1;

    // Create the table
    rcode = ::SQLAllocHandle(SQL_HANDLE_STMT, hDBConnection, &hStmt);
	if (!HandleError(rcode, hStmt))
		return -1;
	rcode = ::SQLExecDirect(hStmt, (SQLCHAR*)"CREATE TABLE mytable(id INTEGER, descr_l MEDIUMTEXT COLLATE Latin1_General_CS)", SQL_NTS);
	if (!HandleError(rcode, hStmt))
		return -1;
    rcode = ::SQLFreeStmt(hStmt, SQL_CLOSE);
	if (!HandleError(rcode, hStmt))
		return -1;

    // Create one dataset
    rcode = ::SQLAllocHandle(SQL_HANDLE_STMT, hDBConnection, &hStmt);
	if (!HandleError(rcode, hStmt))
		return -1;
	rcode = ::SQLExecDirect(hStmt, (SQLCHAR*)"INSERT INTO mytable(id, descr_l) VALUES (5, '')", SQL_NTS);
	if (!HandleError(rcode, hStmt))
		return -1;
    rcode = ::SQLFreeStmt(hStmt, SQL_CLOSE);
	if (!HandleError(rcode, hStmt))
		return -1;

    rcode = ::SQLEndTran(SQL_HANDLE_DBC, hDBConnection, SQL_COMMIT);
	if (!HandleError(rcode, hStmt))
		return -1;

    // Update CLOB
    rcode = ::SQLAllocHandle(SQL_HANDLE_STMT, hDBConnection, &hStmt);
	if (!HandleError(rcode, hStmt))
		return -1;

    rcode = ::SQLPrepare(hStmt, (SQLCHAR*)"UPDATE mytable SET descr_l=? WHERE id=5", SQL_NTS);
	if (!HandleError(rcode, hStmt))
		return -1;

    SQLINTEGER cbIndicator;
    rcode = ::SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WLONGVARCHAR, 0, 0, (SQLPOINTER) 1, 0, &cbIndicator);
	if (!HandleError(rcode, hStmt))
		return -1;

    cbIndicator = SQL_LEN_DATA_AT_EXEC(0);
	if (!HandleError(rcode, hStmt))
		return -1;

    rcode = ::SQLExecute(hStmt);
    if (rcode!=SQL_NEED_DATA) {
	    if (!HandleError(rcode, hStmt))
		    return -1;
        printf("No data needed\r\n");
        return -1;
    }

    SQLPOINTER pToken;
    rcode = ::SQLParamData(hStmt, &pToken);
    // In my main application the return code is SQL_SUCCESS here with no change in 'pToken'. But I expected SQL_NEED_DATA and pToken==1 !
    if (rcode!=SQL_NEED_DATA) {
        if (!HandleError(rcode, hStmt))
            return -1;
        printf("No data needed\r\n");
        return -1;
    }

    const char* szMyString = "Hello world!";
    //const char* szMyString = "";

    do
    {
        rcode = ::SQLPutData(hStmt, (void*)szMyString, strlen(szMyString));
        if (!HandleError(rcode, hStmt))
            return -1;
    }
    while (false);  // quick and dirty

    rcode = ::SQLParamData(hStmt, &pToken);
	if (!HandleError(rcode, hStmt))
		return -1;

    if (rcode==SQL_NEED_DATA)
        return -1;

    rcode = ::SQLEndTran(SQL_HANDLE_DBC, hDBConnection, SQL_COMMIT);
	if (!HandleError(rcode, hStmt))
		return -1;

    rcode = ::SQLDisconnect(hDBConnection);
    if (rcode!=SQL_SUCCESS)
		return -1;
        
    rcode = ::SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
    if (rcode!=SQL_SUCCESS)
		return -1;
	
    rcode = ::SQLFreeHandle(SQL_HANDLE_ENV, hDBEnv);
    if (rcode!=SQL_SUCCESS)
		return -1;
    return 0;
}


bool HandleError(SQLRETURN rcode, HSTMT hStmt)
{
	if (rcode==SQL_NO_DATA) {
        printf("No data\r\n");
        return false;
	}

	if (rcode==SQL_INVALID_HANDLE) {
        printf("Invalid handle\r\n");
        return false;
	}

	if (rcode!=SQL_SUCCESS_WITH_INFO && rcode!=SQL_ERROR)
		return true;

    SQLSMALLINT MsgLen, i = 1;
    SQLCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER NativeError;
    SQLRETURN rc2 = ::SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, i, SqlState, &NativeError, Msg, ARRAYSIZE(Msg), &MsgLen);

    if (rcode==SQL_SUCCESS_WITH_INFO) {
        printf("Info: %s (%ld)", Msg, NativeError);
        return true;
    }

    printf("Error: %s (%ld)", Msg, NativeError);
    return false;
}