/*
 * Test whether SQLCancel() really works
 * from another thread on a blocked transaction.
 *
 * Create and populate a test table in advance:
 *
 * CREATE mytable1 (id serial primary key, t varchar(10));
 * INSERT INTO mytable1 (t) values ('A');
 * INSERT INTO mytable1 (t) values ('B');
 * INSERT INTO mytable1 (t) values ('C');
 * INSERT INTO mytable1 (t) values ('D');
 *
 * and create an ODBC DSN four your database.
 *
 * Compilation:
 *
 * gcc4 -Wall -o test-sqlcancel test-sqlcancel.c -lpthread -lodbc
 *
 * Usage:
 *
 * ./test-sqlcancel DSN USERNAME PASSWORD
 *
 */

#define _REENTRANT

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <sql.h>
#include <sqlext.h>
#include <pthread.h>

#define ODBC2	1


SQLHENV		hEnv1, hEnv2;
SQLHDBC		hCon1, hCon2;
SQLHSTMT	hStmt1, hStmt2;
pthread_t	thr1, thr2, thr3;
int		begintran, endtran, canceltran, asynctran;
char		*sqlstmt = "select t from mytable1 where id=2 for update;";
char		*dsn, *uid, *pwd;

void *upd_thread1(void *dummy) {
  SQLRETURN     ret;
  SQLHSTMT	hStmt_tr;
  char		t[64];
  
  memset(t, 0, sizeof(t));
#if 0 /* This doesn't work here. */
#if ODBC2
  ret = SQLTransact(SQL_NULL_HDBC, hCon1, SQL_ROLLBACK);
#else
  ret = SQLEndTran(SQL_HANDLE_DBC, hCon1, SQL_ROLLBACK);
#endif
  printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
#endif
  if (begintran) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt_tr);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"begin work;", SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
    printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt1);

  if (asynctran) {
    ret = SQLSetStmtOption(hStmt1, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
  printf("%s():%d after SQLSetStmtOption() ret=0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLExecDirect(hStmt1, (SQLPOINTER)sqlstmt, SQL_NTS);
  printf("%s():%d before SQLFetch()\n", __FUNCTION__, __LINE__);

  ret = SQLBindCol(hStmt1, 1, SQL_C_CHAR, (SQLPOINTER)t, sizeof(t), NULL);

  ret = SQLFetch(hStmt1);
  printf("%s():%d after SQLFetch(), ret = 0x%02x, t='%s'\n", __FUNCTION__, __LINE__, ret, t);

  usleep(10000000);
  printf("%s():%d after usleep(10000000)\n", __FUNCTION__, __LINE__);

  if (endtran == 2) {
#if ODBC2
    ret = SQLTransact(SQL_NULL_HDBC, hCon1, SQL_ROLLBACK);
#else
    ret = SQLEndTran(SQL_HANDLE_DBC, hCon1, SQL_ROLLBACK);
#endif
  } else if (endtran == 1) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt_tr);
    printf("%s():%d after AllocHandle for ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"rollback;", SQL_NTS);
    printf("%s():%d after ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
  }

  SQLFreeHandle(SQL_HANDLE_STMT, hStmt1);
  printf("%s():%d after SQLFreeHandle()\n", __FUNCTION__, __LINE__);

  pthread_exit(0);
  return NULL;
}

void *upd_thread2(void *dummy) {
  SQLRETURN     ret;
  SQLHSTMT	hStmt_tr;
  char		t[64];
  
  memset(t, 0, sizeof(t));

#if 0 /* This doesn't work here. */
#if ODBC2
  ret = SQLTransact(SQL_NULL_HDBC, hCon2, SQL_ROLLBACK);
#else
  ret = SQLEndTran(SQL_HANDLE_DBC, hCon2, SQL_ROLLBACK);
#endif
  printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
#endif
  if (begintran) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt_tr);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"begin work;", SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
    printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt2);

  if (asynctran) {
    ret = SQLSetStmtOption(hStmt2, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
  printf("%s():%d after SQLSetStmtOption() ret=0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLExecDirect(hStmt2, (SQLPOINTER)sqlstmt, SQL_NTS);
  printf("%s():%d before SQLFetch()\n", __FUNCTION__, __LINE__);

  ret = SQLBindCol(hStmt2, 1, SQL_C_CHAR, (SQLPOINTER)t, sizeof(t), NULL);

  ret = SQLFetch(hStmt2);
  printf("%s():%d after SQLFetch(), ret = 0x%02x, t='%s'\n", __FUNCTION__, __LINE__, ret, t);

  if (endtran == 2) {
#if ODBC2
    ret = SQLTransact(SQL_NULL_HDBC, hCon2, SQL_ROLLBACK);
#else
    ret = SQLEndTran(SQL_HANDLE_DBC, hCon2, SQL_ROLLBACK);
#endif
    printf("%s():%d after ROLLBACK, ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  } else if (endtran == 1) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt_tr);
    printf("%s():%d after AllocHandle for ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"rollback;", SQL_NTS);
    printf("%s():%d after ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
  }

  SQLFreeHandle(SQL_HANDLE_STMT, hStmt2);
  printf("%s():%d after SQLFreeHandle()\n", __FUNCTION__, __LINE__);

  pthread_exit(0);
  return NULL;
}

void *cancel_thread(void *dummy) {
  SQLRETURN	ret;

  printf("%s():%d before SQLCancel()\n", __FUNCTION__, __LINE__);
  ret = SQLCancel(hStmt2);
  printf("%s():%d after SQLCancel() ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);

  pthread_exit(0);
  return NULL;
}

int main(int argc, char **argv) {
  SQLRETURN	ret;
  int		err, i;

  if (argc != 4 && argc != 5) {
    printf("Usage: test-sqlcancel dsn uid pwd [B[R[O]]]\n");
    printf("B = issue BEGIN WORK before the SELECT FOR UPDATE\n");
    printf("R = issue ROLLBACK after the SELECT FOR UPDATE\n");
    printf("O = issue ROLLBACK using SQLEndTran(), implicitely activates R\n");
    printf("C = try to cancel the second (blocked) connection\n");
    printf("A = execute SELECTs asynchronously\n");
    return 0;
  }
  dsn = argv[1];
  uid = argv[2];
  pwd = argv[3];
  begintran = endtran = canceltran = asynctran = 0;
  if (argc == 5) {
    for (i = 0; i < strlen(argv[4]); i++) {
      switch (argv[4][i]) {
      case 'B':
        begintran = 1;
        break;
      case 'R':
        if (!endtran)
          endtran = 1;
        break;
      case 'O':
        if (endtran != 2)
          endtran = 2;
        break;
      case 'C':
        canceltran = 1;
        break;
      case 'A':
        asynctran = 1;
        break;
      default:
        break;
      }
    }
  }

  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv1);
  if (ret == SQL_ERROR)
    return 0;

  ret = SQLSetEnvAttr(hEnv1, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
  if (ret == SQL_ERROR)
    goto out_henv1;

  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv2);
  if (ret == SQL_ERROR)
    goto out_henv1;

  ret = SQLSetEnvAttr(hEnv2, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
  if (ret == SQL_ERROR)
    goto out_henv2;

  ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv1, &hCon1);
  if (ret == SQL_ERROR)
    goto out_henv2;

  ret = SQLConnect(hCon1,
          (SQLPOINTER)dsn, SQL_NTS,
          (SQLPOINTER)uid, SQL_NTS,
          (SQLPOINTER)pwd, SQL_NTS);
  if (ret == SQL_ERROR)
    goto out_hcon1;

  ret = SQLSetConnectAttr(hCon1, SQL_ATTR_AUTOCOMMIT,
          (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
  if (ret == SQL_ERROR)
    goto out_discon1;

  ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv2, &hCon2);
  if (ret == SQL_ERROR)
    goto out_discon1;

  ret = SQLConnect(hCon2,
          (SQLPOINTER)dsn, SQL_NTS,
          (SQLPOINTER)uid, SQL_NTS,
          (SQLPOINTER)pwd, SQL_NTS);
  if (ret == SQL_ERROR)
    goto out_hcon2;

  ret = SQLSetConnectAttr(hCon2, SQL_ATTR_AUTOCOMMIT,
          (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
  if (ret == SQL_ERROR)
    goto out_discon2;

  err = pthread_create(&thr1, NULL, upd_thread1, NULL);
  usleep(2000000);
  err = pthread_create(&thr2, NULL, upd_thread2, NULL);

  if (canceltran) {
    usleep(2000000);
    err = pthread_create(&thr3, NULL, cancel_thread, NULL);  
    pthread_join(thr3, NULL);
  }

  pthread_join(thr2, NULL);
  pthread_join(thr1, NULL);

  printf("threads exited\n");

out_discon2:
  SQLDisconnect(hCon2);
out_hcon2:
  SQLFreeHandle(SQL_HANDLE_DBC, hCon2);
out_discon1:
  SQLDisconnect(hCon1);
out_hcon1:
  SQLFreeHandle(SQL_HANDLE_DBC, hCon1);
out_henv2:
  SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
out_henv1:
  SQLFreeHandle(SQL_HANDLE_ENV, hEnv1);
  return 0;
}
