Example #1
0
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char sql[100000];
	char *sqlend;
	int i;

	test_connect();

	rc = SQLAllocStmt(conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	rc = SQLTables(hstmt, "", SQL_NTS,
				   "public", SQL_NTS,
				   "%", SQL_NTS,
				   "TABLE", SQL_NTS);

	CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();
}
Example #2
0
static void
execWithParam(HSTMT hstmt, char *param)
{
	SQLLEN cbParam1;
	int rc;

	/* bind param  */
	cbParam1 = SQL_NTS;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,		/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 'foo' UNION ALL SELECT ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
}
Example #3
0
int main(int argc, char **argv)
{
	int			rc;
	HSTMT		hstmt = SQL_NULL_HSTMT;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/*
	 * Execute an erroneous query, and call SQLGetDiagRec twice on the
	 * statement. Should get the same result both times; SQLGetDiagRec is
	 * not supposed to change the state of the statement.
	 */
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "broken query ", SQL_NTS);
	print_diag("SQLExecDirect", SQL_HANDLE_STMT, hstmt);
	print_diag("get same message again", SQL_HANDLE_STMT, hstmt);

	rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_ROLLBACK);
	CHECK_STMT_RESULT(rc, "SQLEndTran failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_DROP);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* kill this connection */
	printf ("killing connection...\n");
	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "select pg_terminate_backend(pg_backend_pid()) ", SQL_NTS);
	print_diag(NULL, SQL_HANDLE_STMT, hstmt);

	/*
	 * Test SQLGetDiagRec on the connection, after the backend connection is
	 * dead. Twice, again to check that the first call doesn't clear the
	 * error.
	 */
	print_diag("SQLGetDiagRec on connection says:", SQL_HANDLE_DBC, conn);
	print_diag("SQLGetDiagRec called again:", SQL_HANDLE_DBC, conn);

	return 0;
}
Example #4
0
int main(int argc, char **argv)
{
	SQLRETURN rc;
	HSTMT hstmt = SQL_NULL_HSTMT;

	test_connect();

	rc = SQLAllocStmt(conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Create a table to test with */
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE testtbl(t varchar(40))", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/**** A simple query against the table, fetch column info ****/

	rc = SQLExecDirect(hstmt, "SELECT * FROM testtbl", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Get column metadata */
	print_result_meta(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Alter the table */
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "ALTER TABLE testtbl ALTER COLUMN t TYPE varchar(80)", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Run the query again, check if the metadata was updated */

	rc = SQLExecDirect(hstmt, "SELECT * FROM testtbl", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Get column metadata */
	print_result_meta(hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #5
0
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char sql[100000];
	char *sqlend;
	int i;
	char *strings[] = {
		"param'quote",
		"param\\backslash",
		"ends with backslash\\",
		NULL };

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SET standard_conforming_strings=on", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	for (i = 0; strings[i] != NULL; i++)
		execWithParam(hstmt, strings[i]);

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SET standard_conforming_strings=off", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	for (i = 0; strings[i] != NULL; i++)
		execWithParam(hstmt, strings[i]);

	/* Clean up */
	test_disconnect();
}
Example #6
0
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	SQLUSMALLINT info;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/*
	 * Print out the current SQL_CURSOR_COMMIT_BEHAVIOR and
	 * SQL_CURSOR_ROLLBACK settings. The result of this test case depends on
	 * those.
	 */
	rc = SQLGetInfo(conn, SQL_CURSOR_COMMIT_BEHAVIOR, &info, sizeof(info), NULL);
	CHECK_STMT_RESULT(rc, "SQLGetInfo failed", hstmt);
	printf("SQL_CURSOR_COMMIT_BEHAVIOR: %s\n", sql_commit_behavior_str(info));

	rc = SQLGetInfo(conn, SQL_CURSOR_ROLLBACK_BEHAVIOR, &info, sizeof(info), NULL);
	CHECK_STMT_RESULT(rc, "SQLGetInfo failed", hstmt);
	printf("SQL_CURSOR_ROLLBACK_BEHAVIOR: %s\n\n", sql_commit_behavior_str(info));

	/* Run three variations of the test */
	testLargeResult(hstmt, 0);
	testLargeResult(hstmt, 1);
	testLargeResult(hstmt, 2);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #7
0
static void print_all_results(HSTMT hstmt)
{
	int i;
	int rc = SQL_SUCCESS;
	for (i = 1; rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO; i++)
	{
		printf("--%d ", i);
		print_result(hstmt);

		rc = SQLMoreResults(hstmt);
	}
	if (rc != SQL_NO_DATA)
		CHECK_STMT_RESULT(rc, "SQLMoreResults failed", hstmt);
}
Example #8
0
void
error_rollback_init(char *options)
{
	SQLRETURN rc;

	/* Error if initialization is already done */
	if (hstmt != SQL_NULL_HSTMT)
	{
		printf("Initialization already done, leaving...\n");
		exit(1);
	}

	test_connect_ext(options);
	rc = SQLAllocStmt(conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Disable autocommit */
	rc = SQLSetConnectAttr(conn,
						   SQL_ATTR_AUTOCOMMIT,
						   (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
						   SQL_IS_UINTEGER);

	/* Create a table to use */
	rc = SQLExecDirect(hstmt,
			   (SQLCHAR *) "CREATE TEMPORARY TABLE errortab (i int4)",
			   SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* And of course commit... */
	rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
	CHECK_STMT_RESULT(rc, "SQLEndTran failed", hstmt);
}
Example #9
0
void
error_rollback_clean(void)
{
	SQLRETURN rc;

	/* Leave if trying to clean an empty handle */
	if (hstmt == SQL_NULL_HSTMT)
	{
		printf("Handle is NULL, leaving...\n");
		exit(1);
	}

	/* Clean up everything */
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
	test_disconnect();
	hstmt = SQL_NULL_HSTMT;
}
Example #10
0
void
error_rollback_print(void)
{
	SQLRETURN rc;

	/* Leave if executing with an empty handle */
	if (hstmt == SQL_NULL_HSTMT)
	{
		printf("Cannot execute query with NULL handle\n");
		exit(1);
	}

	/* Create a table to use */
	rc = SQLExecDirect(hstmt,
			   (SQLCHAR *) "SELECT i FROM errortab",
			   SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Show results */
	print_result(hstmt);
}
Example #11
0
void
error_rollback_exec_success(void)
{
	SQLRETURN rc;

	/* Leave if executing with an empty handle */
	if (hstmt == SQL_NULL_HSTMT)
	{
		printf("Cannot execute query with NULL handle\n");
		exit(1);
	}

	printf("Executing query that will succeed\n");

	/* Now execute the query */
	rc = SQLExecDirect(hstmt,
					   (SQLCHAR *) "INSERT INTO errortab VALUES (1)",
					   SQL_NTS);

	/* Print error if any, but do not exit */
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
}
Example #12
0
int main(int argc, char **argv)
{
	int			rc;
	HSTMT		hstmt = SQL_NULL_HSTMT;
	/*
	 * NOTE: in the psqlodbc, we assume that SQL_C_LONG actually means a
	 * variable of type SQLINTEGER. They are not the same on platforms where
	 * "long" is a 64-bit integer. That seems a bit bogus, but it's too late
	 * to change that without breaking applications that depend on it.
	 * (on little-endian systems, you won't notice the difference if you reset
	 * the high bits to zero before calling SQLBindCol.)
	 */
	SQLINTEGER	longvalue;
	SQLLEN		indLongvalue;
	char		charvalue[100];
	SQLLEN		indCharvalue;
	int			rowno;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &longvalue, 0, &indLongvalue);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

	rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, &charvalue, sizeof(charvalue), &indCharvalue);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

	rc = SQLExecDirect(hstmt, (SQLCHAR *)
					   "SELECT id, 'foo' || id FROM generate_series(1, 10) id", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	printf("Result set:\n");
	rowno = 0;
	while(1)
	{
		rc = SQLFetch(hstmt);
		if (rc == SQL_NO_DATA)
			break;
		if (rc == SQL_SUCCESS)
		{
			printf("%ld %s\n", (long) longvalue, charvalue);
		}
		else
		{
			print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt);
			exit(1);
		}

		/*
		 * At row 3, unbind the text field. At row 5, bind it again.
		 * At row 7, unbind both columns with SQLFreeStmt(SQL_UNBIND).
		 * At row 9, bind text field again.
		 */
		rowno++;
		if (rowno == 3)
		{
			rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, NULL, 0, NULL);
			CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
		}
		if (rowno == 7)
		{
			rc = SQLFreeStmt(hstmt, SQL_UNBIND);
			CHECK_STMT_RESULT(rc, "SQLFreeStmt(SQL_UNBIND) failed", hstmt);
		}
		if (rowno == 5 || rowno == 9)
		{
			rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, &charvalue, sizeof(charvalue), &indCharvalue);
			CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
		}
	}

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #13
0
int main(int argc, char **argv)
{
	SQLRETURN rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char param1[20] = { 1, 2, 3, 4, 5, 6, 7, 8 };
	SQLLEN cbParam1;
	SQLSMALLINT colcount;
	SQLSMALLINT dataType;
	SQLULEN paramSize;
	SQLSMALLINT decDigits;
	SQLSMALLINT nullable;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/**** Query with a bytea param ****/

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, t FROM byteatab WHERE t = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	cbParam1 = 8;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_BINARY,	/* value type */
						  SQL_BINARY,	/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*** Test SQLBindParameter with SQLExecDirect ***/
	printf("\nTesting SQLBindParameter with SQLExecDirect...\n");

	/* bind param  */
	strcpy(param1, "bar");
	cbParam1 = SQL_NTS;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,		/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 'foo' UNION ALL SELECT ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*** Test SQLDescribeParam ***/
	printf("\nTesting SQLDescribeParam...\n");

	rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, t FROM testtab1 WHERE id = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	rc = SQLDescribeParam(hstmt, 1, &dataType, &paramSize, &decDigits, &nullable);
	CHECK_STMT_RESULT(rc, "SQLDescribeParams failed", hstmt);
	printf("Param 1: type %s; size %u; dec digits %d; %s\n",
		   datatype_str(dataType), (unsigned int) paramSize, decDigits, nullable_str(nullable));
	/* bind param  */
	strcpy(param1, "3");
	cbParam1 = SQL_NTS;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,		/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	int			i;
	SQLINTEGER	colvalue;
	SQLLEN		indColvalue;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/*
	 * Initialize a table with some test data.
	 */
	printf("Creating test table pos_update_test\n");
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE pos_update_test(i int4, orig int4)", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "INSERT INTO pos_update_test SELECT g, g FROM generate_series(1, 10) g", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	printf("Opening a cursor for update, and fetching 10 rows\n");

	rc  = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
						 (SQLPOINTER) SQL_CONCUR_ROWVER, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
	rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
						(SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);

	rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &colvalue, 0, &indColvalue);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM pos_update_test ORDER BY orig", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	for (i = 0; i < 5; i++)
	{
		rc = SQLFetch(hstmt);
		if (rc == SQL_NO_DATA)
			break;
		if (rc == SQL_SUCCESS)
		{
			char buf[40];
			int col;
			SQLLEN ind;

			for (col = 1; col <= 2; col++)
			{
				rc = SQLGetData(hstmt, col, SQL_C_CHAR, buf, sizeof(buf), &ind);
				if (!SQL_SUCCEEDED(rc))
				{
					print_diag("SQLGetData failed", SQL_HANDLE_STMT, hstmt);
					exit(1);
				}
				if (ind == SQL_NULL_DATA)
					strcpy(buf, "NULL");
				printf("%s%s", (col > 1) ? "\t" : "", buf);
			}
			printf("\n");
		}
		else
		{
			print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt);
			exit(1);
		}
	}

	/* Do a positioned update and delete */
	printf("Updating result set\n");
	colvalue += 100;
	rc = SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
	CHECK_STMT_RESULT(rc, "SQLSetPos failed", hstmt);

	rc = SQLFetch(hstmt);
	CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);

	rc = SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE);
	CHECK_STMT_RESULT(rc, "SQLSetPos failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* See if the update took effect */
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM pos_update_test ORDER BY orig", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #15
0
int main(int argc, char **argv)
{
	SQLRETURN rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char *param1, *param2;
	SQLLEN cbParam1, cbParam2;
	SQLLEN param1bytes, param2bytes;
	PTR paramid;
	SQLLEN str_ind_array[2];
	SQLUSMALLINT status_array[2];
	SQLULEN nprocessed = 0;
	int i;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/****
	 * Bind with data-at-execution params. (VARBINARY)
	 */

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id FROM byteatab WHERE t = ? OR t = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* prepare the parameter values */
	param1 = "bar";
	param1bytes = strlen(param1);
	cbParam1 = SQL_DATA_AT_EXEC;
	param2 = "foobar";
	param2bytes = strlen(param2);
	cbParam2 = SQL_DATA_AT_EXEC;

	/* bind them. */
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_BINARY,	/* value type */
						  SQL_VARBINARY, /* param type */
						  param1bytes,	/* column size */
						  0,			/* dec digits */
						  (void *) 1,	/* param value ptr. For a data-at-exec
										 * param, this is a "parameter id" */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
						  SQL_C_BINARY,	/* value type */
						  SQL_VARBINARY, /* param type */
						  param2bytes,	/* column size */
						  0,			/* dec digits */
						  (void *) 2,	/* param value ptr. For a data-at-exec
										 * param, this is a "parameter id" */
						  0,			/* buffer len */
						  &cbParam2		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Execute */
	rc = SQLExecute(hstmt);
	if (rc != SQL_NEED_DATA)
		CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* set parameters */
	paramid = 0;
	while ((rc = SQLParamData(hstmt, &paramid)) == SQL_NEED_DATA)
	{
	  if (paramid == (void *) 1)
	  {
		  rc = SQLPutData(hstmt, param1, param1bytes);
		  CHECK_STMT_RESULT(rc, "SQLPutData failed", hstmt);
	  }
	  else if (paramid == (void *) 2)
	  {
		  rc = SQLPutData(hstmt, param2, param2bytes);
		  CHECK_STMT_RESULT(rc, "SQLPutData failed", hstmt);
	  }
	  else
	  {
		  printf("unexpected parameter id returned by SQLParamData: %p\n", paramid);
		  exit(1);
	  }
	}
	CHECK_STMT_RESULT(rc, "SQLParamData failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);


	/****
	 * Array binding with data-at-execution params.
	 */

	/* prepare the parameter values */
	str_ind_array[0] = SQL_DATA_AT_EXEC;
	str_ind_array[1] = SQL_DATA_AT_EXEC;

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id FROM byteatab WHERE t = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, status_array, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &nprocessed, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) 2, 0);

	/* bind the array. */
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_BINARY,	/* value type */
						  SQL_VARBINARY, /* param type */
						  5,			/* column size */
						  0,			/* dec digits */
						  (void *) 1,	/* param value ptr. For a data-at-exec
										 * param, this is "parameter id" */
						  0,			/* buffer len */
						  str_ind_array	/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Execute */
	rc = SQLExecute(hstmt);
	if (rc != SQL_NEED_DATA)
		CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* set parameters */
	paramid = 0;
	while ((rc = SQLParamData(hstmt, &paramid)) == SQL_NEED_DATA)
	{
		if (nprocessed == 1)
			rc = SQLPutData(hstmt, "foo", strlen("foo"));
		else if (nprocessed == 2)
			rc = SQLPutData(hstmt, "barf", strlen("barf"));
		else
		{
			printf("unexpected # of rows processed after SQL_NEED_DATA: %u\n", (unsigned int) nprocessed);
			exit(1);
		}
		CHECK_STMT_RESULT(rc, "SQLPutData failed", hstmt);
	}
	CHECK_STMT_RESULT(rc, "SQLParamData failed", hstmt);

	/* Fetch results */
	printf("Parameter	Status\n");
	for (i = 0; i < nprocessed; i++)
	{
		switch (status_array[i])
		{
			case SQL_PARAM_SUCCESS:
			case SQL_PARAM_SUCCESS_WITH_INFO:
				break;

			case SQL_PARAM_ERROR:
				printf("%d\tError\n", i);
				break;

			case SQL_PARAM_UNUSED:
				printf("%d\tUnused\n", i);
				break;

			case SQL_PARAM_DIAG_UNAVAILABLE:
				printf("%d\tDiag unavailable\n", i);
				break;
		}
	}

	printf ("Fetching result sets for array bound (%u results expected)\n",
			(unsigned int) nprocessed);
	for (i = 1; rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO; i++)
	{
		printf("%d: ", i);
		print_result(hstmt);

		rc = SQLMoreResults(hstmt);
	}
	if (rc != SQL_NO_DATA)
		CHECK_STMT_RESULT(rc, "SQLMoreResults failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #16
0
int
main(int argc, char **argv)
{
	SQLRETURN rc;

	/*
	 * Test for protocol at 0.
	 * Do nothing when error occurs and let application do necessary
	 * ROLLBACK on error.
	 */
	printf("Test for rollback protocol 0\n");
	error_rollback_init("Protocol=7.4-0");

	/* Insert a row correctly */
	error_rollback_exec_success();

	/* Now trigger an error, the row previously inserted will disappear */
	error_rollback_exec_failure();

	/*
	 * Now rollback the transaction block, it is the responsability of
	 * application.
	 */
	printf("Rolling back with SQLEndTran\n");
	rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_ROLLBACK);
	CHECK_STMT_RESULT(rc, "SQLEndTran failed", hstmt);

	/* Insert row correctly now */
	error_rollback_exec_success();

	/* Not yet committed... */
	rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
	CHECK_STMT_RESULT(rc, "SQLEndTran failed", hstmt);

	/* Print result */
	error_rollback_print();

	/* Clean up */
	error_rollback_clean();

	/*
	 * Test for rollback protocol 1
	 * In case of an error rollback the entire transaction.
	 */
	printf("Test for rollback protocol 1\n");
	error_rollback_init("Protocol=7.4-1");

	/*
	 * Insert a row, trigger an error, and re-insert a row. Only one
	 * row should be visible here.
	 */
	error_rollback_exec_success();
	error_rollback_exec_failure();
	error_rollback_exec_success();
	error_rollback_print();

	/* Clean up */
	error_rollback_clean();

	/*
	 * Test for rollback protocol 2
	 * In the case of an error rollback only the latest statement.
	 */
	printf("Test for rollback protocol 2\n");
	error_rollback_init("Protocol=7.4-2");

	/*
	 * Similarly to previous case, do insert, error and insert. This
	 * time two rows should be visible.
	 */
	error_rollback_exec_success();
	error_rollback_exec_failure();
	error_rollback_exec_success();
	error_rollback_print();

	/* Clean up */
	error_rollback_clean();

	return 0;
}
Example #17
0
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char *param1;
	SQLLEN cbParam1;
	char *param2;
	SQLLEN cbParam2;
	SQLSMALLINT colcount;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/*** Simple multi-statement with two queries ***/

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 1; SELECT 2", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_all_results(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*** More queries ***/

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 1; SELECT 'foo', 'bar'; SELECT 3; SELECT 4", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_all_results(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*** Spurious semicolons ***/

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 'foo', 'bar';;; SELECT 'foobar'; ", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_all_results(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*** Prepare/Execute a multi-statement with parameters ***/

	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT 'first result set', id, t FROM testtab1 WHERE t = ?; SELECT 'second result set', t FROM testtab1 WHERE t = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind params */
	param1 = "foo";
	cbParam1 = 8;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,	/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
	param2 = "bar";
	cbParam2 = 8;
	rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,	/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param2,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam2		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);
	print_all_results(hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #18
0
static void
testLargeResult(HSTMT hstmt, int betweenstmts)
{
	int rc;
	int i;

	/*
	 * Fetch a large result set without cursor (in Declare/fetch mode, it will
	 * be fetched in chunks)
	 */
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 'foo' || g FROM generate_series(1, 3210) g", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Fetch the first 10 rows */
	for (i = 0; i < 10; i++)
	{
		char buf[40];
		SQLLEN ind;

		rc = SQLFetch(hstmt);
		if (rc != SQL_SUCCESS)
		{
			CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
		}

		rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &ind);
		CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
		printf("%s ", buf);
	}

	/* Now Commit, Rollback or do nothing depending on the argument */
	switch (betweenstmts)
	{
		case 1:
			printf("\nCommit\n");
			rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
			CHECK_STMT_RESULT(rc, "SQLEndTran failed\n", hstmt);
			break;

		case 2:
			printf("\nRollback\n");
			rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_ROLLBACK);
			CHECK_STMT_RESULT(rc, "SQLEndTran failed\n", hstmt);
			break;
		default:
			/* do nothing */
			break;
	}

	/*
	 * Try to fetch the rest of the result set.
	 * (Will fail in SQL_CB_CLOSE mode and succeed in SQL_CB_PRESERVE).
	 */
	for (;; i++)
	{
		char buf[40];
		SQLLEN ind;

		rc = SQLFetch(hstmt);
		if (rc == SQL_NO_DATA)
			break;
		if (rc != SQL_SUCCESS)
		{
			char sqlstate[32] = "";
			SQLINTEGER nativeerror;
			SQLSMALLINT textlen;

			SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &nativeerror,
						  NULL, 0, &textlen);
			if (strcmp(sqlstate, "HY010") == 0)
			{
				/* This is expected in SQL_CB_CLOSE mode */
				printf("SQLFetch failed with HY010 (which probably means that the cursor was closed at commit/rollback)");
				break;
			}
			else
				CHECK_STMT_RESULT(rc, "SQLGetDiagRec failed", hstmt);
		}

		rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &ind);
		CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
		if (i == 20)
			printf("... ");
		else if (20 < i && i <= 3205)
		{
			/* skip printing, to keep the output short */
		}
		else
			printf("%s ", buf);
	}
	printf("\nFetched %d rows altogether\n\n", i);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
}
int main(int argc, char **argv)
{
	SQLRETURN rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char *sql;

	int i;

	SQLUINTEGER int_array[ARRAY_SIZE];
	SQLCHAR str_array[ARRAY_SIZE][30];
	SQLCHAR str_array2[ARRAY_SIZE][6];
	SQLLEN int_ind_array[ARRAY_SIZE];
	SQLLEN str_ind_array[ARRAY_SIZE];
	SQLUSMALLINT status_array[ARRAY_SIZE];
	SQLULEN nprocessed;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	sql = "CREATE TEMPORARY TABLE tmptable (i int4, t text)";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating temp table", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/****
	 * 1. Test column-wise binding
	 */
	for (i = 0; i < ARRAY_SIZE; i++)
	{
		int_array[i] = i;
		int_ind_array[i] = 0;
		sprintf(str_array[i], "columnwise %d", i);
		str_ind_array[i] = SQL_NTS;
	}

	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, status_array, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &nprocessed, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) ARRAY_SIZE, 0);

	/* Bind the parameter arrays. */
	SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
					 int_array, 0, int_ind_array);
	SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 29, 0,
					 str_array, 30, str_ind_array);

	/* Execute */
	sql = "INSERT INTO tmptable VALUES (?, ?)";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Fetch results */
	printf("Parameter	Status\n");
	for (i = 0; i < nprocessed; i++)
	{
		switch (status_array[i])
		{
			case SQL_PARAM_SUCCESS:
			case SQL_PARAM_SUCCESS_WITH_INFO:
				break;

			case SQL_PARAM_ERROR:
				printf("%d\tError\n", i);
				break;

			case SQL_PARAM_UNUSED:
				printf("%d\tUnused\n", i);
				break;

			case SQL_PARAM_DIAG_UNAVAILABLE:
				printf("%d\tDiag unavailable\n", i);
				break;
		}
	}

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*
	 * Free and allocate a new handle for the next SELECT statement, as we don't
	 * want to array bind that one. The parameters set with SQLSetStmtAttr
	 * survive SQLFreeStmt.
	 */
	rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	CHECK_STMT_RESULT(rc, "SQLFreeHandle failed", hstmt);

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Check that all the rows were inserted */
	sql = "SELECT COUNT(*) FROM tmptable";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check the contents of a few rows */
	sql = "SELECT * FROM tmptable WHERE i IN (0, 1, 100, 9999, 10000) ORDER BY i";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/****
	 * 2. Test column-wise binding. With a column_size=5 VARCHAR param - that
	 * causes the driver to do a server-side prepare, assuming BoolsAsChar=1.
	 */

	/* a small array will do for this test */
	for (i = 0; i < ARRAY_SIZE_SMALL; i++)
	{
		sprintf(str_array2[i], "%d", 100+i);
		str_ind_array[i] = SQL_NTS;
	}

	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, status_array, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &nprocessed, 0);
	SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) ARRAY_SIZE_SMALL, 0);

	/* Bind the parameter array. */
	SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 5, 0,
					 str_array2, 6, str_ind_array);

	/* Execute */
	sql = "DELETE FROM tmptable WHERE i = ? RETURNING (t)";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Fetch results */
	printf("Parameter	Status\n");
	for (i = 0; i < nprocessed; i++)
	{
		switch (status_array[i])
		{
			case SQL_PARAM_SUCCESS:
			case SQL_PARAM_SUCCESS_WITH_INFO:
				break;

			case SQL_PARAM_ERROR:
				printf("%d\tError\n", i);
				break;

			case SQL_PARAM_UNUSED:
				printf("%d\tUnused\n", i);
				break;

			case SQL_PARAM_DIAG_UNAVAILABLE:
				printf("%d\tDiag unavailable\n", i);
				break;
		}
	}

	printf ("Fetching result sets for array bound (%d results expected)\n",
			nprocessed);
	for (i = 1; rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO; i++)
	{
		printf("%d: ", i);
		print_result(hstmt);

		rc = SQLMoreResults(hstmt);
	}
	if (rc != SQL_NO_DATA)
		CHECK_STMT_RESULT(rc, "SQLMoreResults failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*
	 * Free and allocate a new handle for the next SELECT statement, as we don't
	 * want to array bind that one. The parameters set with SQLSetStmtAttr
	 * survive SQLFreeStmt.
	 */
	rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	CHECK_STMT_RESULT(rc, "SQLFreeHandle failed", hstmt);

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Check that all the rows were inserted */
	printf("Number of rows in table:\n");
	sql = "SELECT COUNT(*) FROM tmptable";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check the contents of a few rows */
	sql = "SELECT * FROM tmptable WHERE i IN (0, 1, 100, 9999, 10000) ORDER BY i";
	rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #20
0
int main(int argc, char **argv)
{
	SQLRETURN rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char *param1;
	SQLLEN cbParam1;
	SQLINTEGER longparam;
	SQL_INTERVAL_STRUCT intervalparam;
	SQLSMALLINT colcount;
	char		byteaParam[5000];
	int			i;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/**** A simple query with one text param ****/

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SET intervalstyle=postgres_verbose", SQL_NTS);
	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, t FROM testtab1 WHERE t = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	param1 = "bar";
	cbParam1 = SQL_NTS;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_CHAR,		/* param type */
						  20,			/* column size */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/**** A query with an integer param ****/

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, t FROM testtab1 WHERE id = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	longparam = 3;
	cbParam1 = sizeof(longparam);
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_SLONG,	/* value type */
						  SQL_INTEGER,	/* param type */
						  0,			/* column size (ignored for SQL_INTEGER) */
						  0,			/* dec digits */
						  &longparam,	/* param value ptr */
						  sizeof(longparam), /* buffer len (ignored for SQL_INTEGER) */
						  &cbParam1		/* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/**** Test a query with a bytea param of various sizes.  ****/

	/*
	 * The driver has some special handling for byteas, as it sends them in
	 * binary mode. This particular test case exercises an old bug where
	 * the bind packet size was calculated incorrectly, and there was an
	 * out-of-bounds write of two bytes when the total packet size was exactly
	 * 4097 bytes. So, exercise packet sizes near that boundary.
	 */

	rc = SQLExecDirect(hstmt,
					   (SQLCHAR *) "CREATE TEMPORARY TABLE btest (len int4, b bytea)",
					   SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "INSERT INTO btest VALUES(?, ?)", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* fill in test data */
	for (i = 0; i < sizeof(byteaParam); i++)
		byteaParam[i] = (char) i;

	printf ("inserting bytea values...");
	for (i = 4000; i < 4100; i++)
	{
		printf(" %d", i); fflush(stdout);
		/* bind int param  */
		longparam = i;
		cbParam1 = sizeof(longparam);
		rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
							  SQL_C_SLONG,	/* value type */
							  SQL_INTEGER,	/* param type */
							  0,			/* column size (ignored for SQL_INTEGER) */
							  0,			/* dec digits */
							  &longparam,	/* param value ptr */
							  sizeof(longparam), /* buffer len (ignored for SQL_INTEGER) */
							  &cbParam1		/* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
		CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);

		cbParam1 = i;
		rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
							  SQL_C_BINARY,	/* value type */
							  SQL_VARBINARY,	/* param type */
							  sizeof(byteaParam), /* column size */
							  0,			/* dec digits */
							  byteaParam,	/* param value ptr */
							  sizeof(byteaParam), /* buffer len */
							  &cbParam1		/* StrLen_or_IndPtr */);
		CHECK_STMT_RESULT(rc, "\nSQLBindParameter failed", hstmt);

		/* Execute */
		rc = SQLExecute(hstmt);
		CHECK_STMT_RESULT(rc, "\nSQLExecute failed", hstmt);
	}
	printf(" done!\n");
	printf("Now reading them back...\n");

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check the inserted data */
	rc = SQLExecDirect(hstmt,
					   (SQLCHAR *) "SELECT len, length(b) FROM btest",
					   SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/**** A query with an interval param (SQL_C_INTERVAL_SECOND) ****/

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, iv, d FROM intervaltable WHERE iv < ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	intervalparam.interval_type = SQL_IS_SECOND;
	intervalparam.interval_sign = 0;
	intervalparam.intval.day_second.day = 1;
	intervalparam.intval.day_second.hour = 2;
	intervalparam.intval.day_second.minute = 3;
	intervalparam.intval.day_second.second = 4;
	intervalparam.intval.day_second.fraction = 5;

	cbParam1 = sizeof(intervalparam);
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_INTERVAL_SECOND,	/* value type */
						  SQL_INTERVAL_SECOND,	/* param type */
						  0,			/* column size (ignored for SQL_INTERVAL_SECOND) */
						  0,			/* dec digits */
						  &intervalparam, /* param value ptr */
						  sizeof(intervalparam), /* buffer len (ignored for SQL_C_INTERVAL_SECOND) */
						  &cbParam1 /* StrLen_or_IndPtr (ignored for SQL_C_INTERVAL_SECOND) */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);


	/****
	 * With BoolsAsChar=1, a varchar param with column_size=5 forces a
	 * server-side Prepare. So test that.
	 */

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT id, t FROM testtab1 WHERE id = ?", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	param1 = "2";
	cbParam1 = SQL_NTS;
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_CHAR,	/* value type */
						  SQL_VARCHAR,	/* param type */
						  5,			/* column size. 5 Triggers special
										 * behavior with BoolsAsChar=1 */
						  0,			/* dec digits */
						  param1,		/* param value ptr */
						  0,			/* buffer len */
						  &cbParam1		/* StrLen_or_IndPtr */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("SQLFreeStmt failed", SQL_HANDLE_STMT, hstmt);
		exit(1);
	}

	/* Clean up */
	test_disconnect();

	return 0;
}
int main(int argc, char **argv)
{
	int			rc;
	HSTMT		hstmt = SQL_NULL_HSTMT;
	int			rows;

	/****
     * Run this test with UseDeclareFetch = 1 and Fetch=1, so that we fetch
     * one row at a time. Protocol=-2 is required so that the cursors survives
	 * the commit.
     */
	test_connect_ext("UseDeclareFetch=1;Fetch=1;Protocol=7.4-2");

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Disable autocommit, and execute a dummy UPDATE to start a transaction */
	rc = SQLSetConnectAttr(conn,
						   SQL_ATTR_AUTOCOMMIT,
						   (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
						   SQL_IS_UINTEGER);
	CHECK_STMT_RESULT(rc, "SQLSetConnectAttr failed", hstmt);

	SQLExecDirect(hstmt, (SQLCHAR *) "update testtbl1 set t = t where id = 123456", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/**** Start a cursor ****/

	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT * FROM generate_series(1, 5) g ", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */

	printf("Result set:\n");
	rows = 0;
	while(1)
	{
		rc = SQLFetch(hstmt);
		if (rc == SQL_NO_DATA)
			break;
		if (rc == SQL_SUCCESS)
		{
			char buf[40];
			SQLLEN ind;

			rc = SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &ind);
			if (!SQL_SUCCEEDED(rc))
			{
				print_diag("SQLGetData failed", SQL_HANDLE_STMT, hstmt);
				exit(1);
			}
			printf("%s", buf);

			printf("\n");
		}
		else
		{
			print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt);
			exit(1);
		}

		/* In the middle of the result set, COMMIT */
		if (rows == 2)
		{
			rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
			CHECK_STMT_RESULT(rc, "SQLEndTran failed\n", hstmt);
		}
		rows++;
	}

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #22
0
int
main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	/* Cases where output is limited to relevant information only */
	SQLSMALLINT sql_tab_privileges_ids[6] = {1, 2, 3, 4, 6, 7};
	SQLSMALLINT sql_column_ids[6] = {1, 2, 3, 4, 5, 6};

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Check for SQLGetTypeInfo */
	printf("Check for SQLTypeInfo\n");
	rc = SQLGetTypeInfo(hstmt, SQL_VARCHAR);
	CHECK_STMT_RESULT(rc, "SQLGetTypeInfo failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLTables */
	printf("Check for SQLTables\n");
	rc = SQLTables(hstmt, NULL, 0,
				   (SQLCHAR *) "public", SQL_NTS,
				   (SQLCHAR *) "%", SQL_NTS,
				   (SQLCHAR *) "TABLE", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLColumns */
	printf("Check for SQLColumns\n");
	rc = SQLColumns(hstmt,
					NULL, 0,
					(SQLCHAR *) "public", SQL_NTS,
					(SQLCHAR *) "%", SQL_NTS,
					NULL, 0);
	CHECK_STMT_RESULT(rc, "SQLColumns failed", hstmt);
	print_result_meta(hstmt);
	/*
	 * Print only the 6 first columns, we do not want for example
	 * to get the OID in output, and this information looks to be
	 * enough.
	 */
	print_result_series(hstmt, sql_column_ids, 6);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLColumnPrivileges */
	//printf("Check for SQLColumnPrivileges\n");
	//rc = SQLColumnPrivileges(hstmt,
	//						 NULL, 0,
	//						 (SQLCHAR *) "public", SQL_NTS,
	//						 (SQLCHAR *) "testtab1", SQL_NTS,
	//						 (SQLCHAR *) "id", SQL_NTS);
	//CHECK_STMT_RESULT(rc, "SQLColumnPrivileges failed", hstmt);
	//print_result_meta(hstmt);
	//print_result(hstmt);
	//rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	//CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLSpecialColumns */
	printf("Check for SQLSpecialColumns\n");
	rc = SQLSpecialColumns(hstmt, SQL_ROWVER,
						   NULL, 0,
						   (SQLCHAR *) "public", SQL_NTS,
						   (SQLCHAR *) "testtab1", SQL_NTS,
						   SQL_SCOPE_SESSION,
						   SQL_NO_NULLS);
	CHECK_STMT_RESULT(rc, "SQLSpecialColumns failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*
	 * Check for SQLStatistics. It is important to note that this function
	 * returns statistics like the number of pages used and the number of
	 * index scans.
	 */
	printf("Check for SQLStatistics\n");
	rc = SQLStatistics(hstmt,
					   NULL, 0,
					   (SQLCHAR *) "public", SQL_NTS,
					   (SQLCHAR *) "testtab1", SQL_NTS,
					   0, 0);
	CHECK_STMT_RESULT(rc, "SQLStatistics failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLPrimaryKeys */
	printf("Check for SQLPrimaryKeys\n");
	rc = SQLPrimaryKeys(hstmt,
						NULL, 0,
						(SQLCHAR *) "public", SQL_NTS,
						(SQLCHAR *) "testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrimaryKeys failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLForeignKeys */
	printf("Check for SQLForeignKeys\n");
	rc = SQLForeignKeys(hstmt,
						NULL, 0,
						(SQLCHAR *) "public", SQL_NTS,
						(SQLCHAR *) "testtab1", SQL_NTS,
						NULL, 0,
						(SQLCHAR *) "public", SQL_NTS,
						(SQLCHAR *) "testtab_fk", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLForeignKeys failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLProcedures */
	printf("Check for SQLProcedures\n");
	rc = SQLProcedures(hstmt,
					   NULL, 0,
					   (SQLCHAR *) "public", SQL_NTS,
					   (SQLCHAR *) "simple_add", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLProcedures failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLProcedureColumns */
	printf("Check for SQLProcedureColumns\n");
	rc = SQLProcedureColumns(hstmt,
							 NULL, 0,
							 (SQLCHAR *) "public", SQL_NTS,
							 (SQLCHAR *) "simple_add", SQL_NTS,
							 NULL, 0);
	CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
	print_result_meta(hstmt);
	print_result(hstmt);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Check for SQLTablePrivileges */
	printf("Check for SQLTablePrivileges\n");
	rc = SQLTablePrivileges(hstmt,
							NULL, 0,
							(SQLCHAR *) "public", 0,
							(SQLCHAR *) "testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLTablePrivileges failed", hstmt);
	print_result_meta(hstmt);
	print_result_series(hstmt, sql_tab_privileges_ids, 6);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*
	 * Extra tests.
	 * Older versions of the driver had a bug in handling table-types lists
	 * longer than 32 entries. Check for that.
	 */
	rc = SQLTables(hstmt, "", SQL_NTS,
				   "public", SQL_NTS,
				   "testtab%", SQL_NTS,
				   "1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5, TABLES", SQL_NTS);

	CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}
Example #23
0
int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char sql[100000];
	char *sqlend;
	int i;

	test_connect();

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/* Test VACUUM */
	printf("Testing VACUUM with SQLExecDirect...\n");
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "vacuum (analyze) testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Same with SQLPrepare/SQLExecute */
	printf("Testing VACUUM with SQLPrepare/SQLExecute...\n");
	rc = SQLPrepare(hstmt, (SQLCHAR *) "VACUUM ANALYZE testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/*
	 * Now the same with autocommit disabled. The driver should recognize
	 * that the commands are VACUUMs, which cannot be run in a transaction
	 * block, and not issue a BEGIN even it normally would in autocommit
	 * mode. In other words, these commands should behave the same with or
	 * without autocommit. But if you issued a normal query, like a SELECT,
	 * first in the same transaction, and then tried to run a VACUUM, it
	 * would fail with "VACUUM cannot run inside a transaction block" error.
	 */
	printf("Disabling autocommit...\n");

	rc = SQLSetConnectAttr(conn,
						   SQL_ATTR_AUTOCOMMIT,
						   (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
						   SQL_IS_UINTEGER);
	CHECK_STMT_RESULT(rc, "SQLSetConnectAttr failed", hstmt);

	/* Test VACUUM */
	printf("Testing VACUUM with SQLExecDirect...\n");
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "vacuum analyze testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Same with SQLPrepare/SQLExecute */
	printf("Testing VACUUM with SQLPrepare/SQLExecute...\n");
	rc = SQLPrepare(hstmt, (SQLCHAR *) "VACUUM (ANALYZE) testtab1", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();
}
Example #24
0
int main(int argc, char **argv)
{
	int			rc;
	HSTMT		hstmt = SQL_NULL_HSTMT;
	int			i;
	SQLINTEGER	colvalue1;
	SQLINTEGER	colvalue2;
	SQLLEN		indColvalue1;
	SQLLEN		indColvalue2;
	char		bookmark[8];
	SQLLEN		bookmark_ind;

	char		saved_bookmarks[3][8];
	SQLLEN		saved_bookmark_inds[3];
	SQLINTEGER	colvalues1[3];
	SQLINTEGER	colvalues2[3];
	SQLLEN		indColvalues1[3];
	SQLLEN		indColvalues2[3];

	memset(bookmark, 0x7F, sizeof(bookmark));
	memset(saved_bookmarks, 0xF7, sizeof(saved_bookmarks));

	test_connect_ext("UpdatableCursors=1;UseDeclareFetch=0");

	rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	/*
	 * Initialize a table with some test data.
	 */
	printf("Creating test table bulkoperations_test\n");
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "CREATE TEMPORARY TABLE bulkoperations_test(i int4, orig int4)", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "INSERT INTO bulkoperations_test SELECT g, g FROM generate_series(1, 10) g", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	printf("Opening a cursor for update, and fetching 10 rows\n");

	rc  = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY,
						 (SQLPOINTER) SQL_CONCUR_ROWVER, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);
	rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
						(SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);

	/* Enable bookmarks */
	rc = SQLSetStmtAttr(hstmt, SQL_ATTR_USE_BOOKMARKS,
						(SQLPOINTER) SQL_UB_VARIABLE, SQL_IS_UINTEGER);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);

	rc = SQLBindCol(hstmt, 0, SQL_C_VARBOOKMARK, &bookmark, sizeof(bookmark), &bookmark_ind);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
	rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &colvalue1, 0, &indColvalue1);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
	rc = SQLBindCol(hstmt, 2, SQL_C_LONG, &colvalue2, 0, &indColvalue2);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM bulkoperations_test ORDER BY orig", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

	for (i = 1; i <= 5; i++)
	{
		rc = SQLFetch(hstmt);
		if (rc == SQL_NO_DATA)
			break;
		if (rc == SQL_SUCCESS)
			printCurrentRow(hstmt);
		else
		{
			print_diag("SQLFetch failed", SQL_HANDLE_STMT, hstmt);
			exit(1);
		}

		/* Save row # 2's bookmark for fetch test */
		if (i == 2)
		{
			memcpy(saved_bookmarks[0], bookmark, bookmark_ind);
			saved_bookmark_inds[0] = bookmark_ind;
		}
	}

	/* Do a positioned update and delete */
	printf("\nUpdating result set\n");
	colvalue1 += 100;

	rc = SQLBulkOperations(hstmt, SQL_UPDATE_BY_BOOKMARK);
	CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);

	/* Have to use an absolute position after SQLBulkOperations. */
	rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 8);
	CHECK_STMT_RESULT(rc, "SQLFetchScroll failed", hstmt);

	rc = SQLBulkOperations(hstmt, SQL_DELETE_BY_BOOKMARK);
	CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);

	/* Have to use an absolute position after SQLBulkOperations. */
	rc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 5);
	CHECK_STMT_RESULT(rc, "SQLFetchScroll failed", hstmt);

	/* Print the updated row */
	printCurrentRow(hstmt);

	/* remember its bookmark for later fetch */
	memcpy(saved_bookmarks[1], bookmark, bookmark_ind);
	saved_bookmark_inds[1] = bookmark_ind;

	/* Perform an insertion */
	colvalue1 = 1234;
	colvalue2 = 5678;
	rc = SQLBulkOperations(hstmt, SQL_ADD);
	CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);

	/* Remember the bookmark of the inserted row */
	memcpy(saved_bookmarks[2], bookmark, bookmark_ind);
	saved_bookmark_inds[2] = bookmark_ind;

	/**** Test bulk fetch *****/
	printf("Testing bulk fetch of original, updated, and inserted rows\n");
	rc = SQLBindCol(hstmt, 0, SQL_C_VARBOOKMARK, saved_bookmarks, sizeof(bookmark), saved_bookmark_inds);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
	rc = SQLBindCol(hstmt, 1, SQL_C_LONG, colvalues1, 0, indColvalues1);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
	rc = SQLBindCol(hstmt, 2, SQL_C_LONG, colvalues2, 0, indColvalues2);
	CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

	rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 3, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);

	/*
	 * FIXME: Disabled, because this doesn't currently seem to produce the
	 * right results.
	 */
#ifdef BROKEN
	rc = SQLBulkOperations(hstmt, SQL_FETCH_BY_BOOKMARK);
	CHECK_STMT_RESULT(rc, "SQLBulkOperations failed", hstmt);

	printf ("row no #2: %d - %d\n", colvalues1[0], colvalues2[0]);
	printf ("updated row: %d - %d\n", colvalues1[1], colvalues2[1]);
	printf ("inserted row: %d - %d\n", colvalues1[2], colvalues2[2]);
#endif

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 1, 0);
	CHECK_STMT_RESULT(rc, "SQLSetStmtAttr failed", hstmt);

	/**** See if the updates really took effect ****/
	printf("\nQuerying the table again\n");
	rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT * FROM bulkoperations_test ORDER BY orig", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
	print_result(hstmt);

	/* Clean up */
	test_disconnect();

	return 0;
}