/**********************************************************************
*   odbc_insert_update.c
*
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
void print_odbc_error(char *fn, SQLHANDLE handle, SQLSMALLINT type);

#define LNAME_LEN  255
#define FNAME_LEN 255
#define EMAIL_LEN 255
#define TRUE 1
#define FALSE 0

SQLLEN newID;
SQLLEN uczestnikID;
SQLCHAR fname[FNAME_LEN];
SQLCHAR lname[LNAME_LEN];
SQLCHAR email[EMAIL_LEN];
SQLCHAR updtemail[EMAIL_LEN];

SQLLEN  lennewID=0, lenFirstName=0, lenLastName=0, lenEmail=0;

int main () {

	// deklaracje uchwytow - handle for: 
    SQLHENV  henv  = SQL_NULL_HENV;   // Environment
    SQLHDBC  hdbc  = SQL_NULL_HDBC;   // Connection 
    SQLHSTMT hInsertStmt = SQL_NULL_HSTMT;  // Insert statement 
    SQLHSTMT hUpdateStmt = SQL_NULL_HSTMT;  // Update statement 
    SQLRETURN retcode;

    // Allocate environment
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLAllocHandle(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV);
	goto exit;
    }
    

    // Set ODBC version
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
		(SQLPOINTER*)SQL_OV_ODBC3, 0);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);
	goto exit;
    }                                                
    

    // Allocate connection
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLAllocHandle(SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }
    

    // Set Login Timeout
    retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }

        //retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)TRUE, 0);
        retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)FALSE, 0);

    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }

    // Connect to DSN
    retcode = SQLConnect(hdbc, (SQLCHAR*) "BD1Lab", SQL_NTS,
                               (SQLCHAR*) NULL, 0, NULL, 0);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLConnect(DSN BD1Lab)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }

    // Allocate Statement Handle
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hInsertStmt);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLAllocHandle(SQL_HANDLE_STMT)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }


    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hUpdateStmt);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLAllocHandle(SQL_HANDLE_STMT)", hUpdateStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    // Bind Parameters to all fields
    retcode = SQLBindParameter(hInsertStmt, 1,
                               SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, FNAME_LEN, 0,
                               fname, FNAME_LEN, &lenFirstName);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(1)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLBindParameter(hInsertStmt, 2,
                               SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, LNAME_LEN, 0,
                               lname, LNAME_LEN, &lenLastName);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(2)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLBindParameter(hInsertStmt, 3,
                               SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, EMAIL_LEN, 0,
                               email, EMAIL_LEN, &lenEmail);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(3)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLBindParameter(hInsertStmt, 4,
                               SQL_PARAM_INPUT, SQL_C_ULONG,
                               SQL_INTEGER, 0, 0,
                               &newID, 0, NULL);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(4)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }


    retcode = SQLBindParameter(hUpdateStmt, 1,
                               SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_VARCHAR, EMAIL_LEN, 0,
                               updtemail, EMAIL_LEN, &lenEmail);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(1)", hUpdateStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLBindParameter(hUpdateStmt, 2,
                               SQL_PARAM_INPUT, SQL_C_ULONG,
                               SQL_INTEGER, 0, 0,
                               &uczestnikID, 0, NULL);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(2)", hUpdateStmt, SQL_HANDLE_STMT);
	goto exit;
    }
    

    // Prepare INSERT AND UPDATE
    retcode = SQLPrepare(hUpdateStmt, (SQLCHAR*)
        "UPDATE lab11.person SET email = ? WHERE id = ?", SQL_NTS);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLPrepare(UPDATE)", hUpdateStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLPrepare(hInsertStmt, (SQLCHAR*)
        "INSERT INTO lab11.person (lname, fname, email, id)"
                                                "VALUES (?, ?, ?, ?)", SQL_NTS);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLPrepare(INSERT)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    strcpy(fname, "Adam");
    strcpy(lname, "Kowalski");
    strcpy(email, "kowalski@student.agh.edu.pl");
    strcpy(updtemail, "kowalski@fis.agh.edu.pl");
    newID = 5;
    uczestnikID = 5;

    // Set lengths to field lengths
    lenFirstName=sizeof(fname);
    lenLastName=sizeof(lname);
    lenEmail=sizeof(email);

    retcode = SQLExecute(hInsertStmt);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLExecute(INSERT)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }


    retcode = SQLExecute(hUpdateStmt);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLExecute(UPDATE)", hUpdateStmt, SQL_HANDLE_STMT);
	goto exit;
    }


//    retcode = SQLExecute(hInsertStmt);
//    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
//    {
//	print_odbc_error("SQLExecute(INSERT)", hInsertStmt, SQL_HANDLE_STMT);
//	goto exit;
//    }


    retcode = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLEndTran(SQL_COMMIT)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }
    printf ("Transaction Committed (status %i)\n", retcode);


            //retcode = SQLEndTran (SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
//    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
//    {
//	print_odbc_error("SQL_HANDLE_DBC(SQL_ROLLBACK)", hdbc, SQL_HANDLE_DBC);
//	goto exit;
//    }
            //printf ("Rollback Transaction (status %i)\n", retcode);
    

    // free handles
    retcode = SQLFreeStmt(hInsertStmt, SQL_CLOSE);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLFreeStmt()", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    // Read modified data
    retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)TRUE, 0);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)", hdbc, SQL_HANDLE_DBC);
	goto exit;
    }

    retcode = SQLBindParameter(hInsertStmt, 1,
                               SQL_PARAM_INPUT, SQL_C_ULONG,
                               SQL_INTEGER, 0, 0,
                               &newID, 0, NULL);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLBindParameter(1)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }

    retcode = SQLExecDirect(hInsertStmt, (SQLCHAR *)
              "SELECT id, lname, fname, email FROM lab11.person WHERE id = ?", SQL_NTS);
    if (retcode!=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
	print_odbc_error("SQLExecDirect(SELECT)", hInsertStmt, SQL_HANDLE_STMT);
	goto exit;
    }
    for ( retcode = SQLFetch(hInsertStmt) ;
          retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
          retcode = SQLFetch(hInsertStmt) ) {
        memset(fname, ' ', FNAME_LEN);
        retcode = SQLGetData(hInsertStmt, 1, SQL_C_USHORT,
                            (SQLPOINTER)&newID, 0, NULL);
        retcode = SQLGetData(hInsertStmt, 2, SQL_C_CHAR,
                             fname, FNAME_LEN, &lenFirstName);
        retcode = SQLGetData(hInsertStmt, 3, SQL_C_CHAR,
                             lname,  LNAME_LEN,  &lenLastName);
        retcode = SQLGetData(hInsertStmt, 4, SQL_C_CHAR,
                            email,   EMAIL_LEN,   &lenEmail);
        
        printf ("\nid  : %i", (int)newID);
        printf ("\nimie       : %.30s", fname);
        printf ("\nnazwisko   : %.30s", lname);
        printf ("\nemail      : %.50s", email);
        
    }

exit:

    printf ("\nUFF.\n");

    // Free handles
    // Statement
    if (hInsertStmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hInsertStmt);
    if (hUpdateStmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hUpdateStmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}


// print_error
void print_odbc_error(char *fn, SQLHANDLE handle, SQLSMALLINT type)
{
    SQLINTEGER i = 0;
    SQLINTEGER NativeError;
    SQLCHAR SQLState[ 7 ];
    SQLCHAR MessageText[256];
    SQLSMALLINT TextLength;
    SQLRETURN ret;

    fprintf(stderr, "\nError %s\n", fn);
    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, SQLState, &NativeError,
                            MessageText, sizeof(MessageText), &TextLength);
        if (SQL_SUCCEEDED(ret)) {
            printf("%s:%ld:%ld:%s\n",
                        SQLState, (long) i, (long) NativeError, MessageText);
        }
    }
    while( ret == SQL_SUCCESS );
}