testodbc.c

在Linux/Unix下面访问WINDOWS SQLSERVER 的ODBC驱动程序

	strcpy((char *) (queryString), "INSERT INTO #pet( name, owner, species, sex, age ) \
                         VALUES ( 'Fang', 'Mike', 'dog', 'm', 12 );");
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Insert row 1 failed"));
		goto odbcfail;
	}

	AB_PRINT(("Insert row 2"));

	/*
	 * Ok - new row with explicit GUID, but parameterised age.
	 */
	strcpy((char *) (queryString), "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
                         VALUES ( 'Splash', 'Dan', 'fish', 'm', ?, \
                         '12345678-1234-1234-1234-123456789012' );");

	lenOrInd = 0;
	age = 3;
	if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &age, 0, &lenOrInd)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		goto odbcfail;
	}

	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Insert row 2 failed"));
		goto odbcfail;
	}
	if (SQLFreeStmt(Statement, SQL_CLOSE) != SQL_SUCCESS) {
		AB_ERROR(("Free statement failed (5)"));
		goto odbcfail;
	}

	AB_PRINT(("Insert row 3"));
	/*
	 * Ok - new row with parameterised GUID.
	 */
	strcpy((char *) (queryString), "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
                         VALUES ( 'Woof', 'Tom', 'cat', 'f', 2, ? );");

	lenOrInd = SQL_NTS;
	strcpy((char *) (guid), "87654321-4321-4321-4321-123456789abc");

	if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		goto odbcfail;
	}
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Insert row 3 failed"));
		goto odbcfail;
	}

	AB_PRINT(("Insert row 4"));
	/*
	 * Ok - new row with parameterised GUID.
	 */
	strcpy((char *) (queryString), "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
                         VALUES ( 'Spike', 'Diane', 'pig', 'f', 4, ? );");

	lenOrInd = SQL_NTS;
	strcpy((char *) (guid), "1234abcd-abcd-abcd-abcd-123456789abc");

	if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 36, 0, guid, 0, &lenOrInd)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		goto odbcfail;
	}
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Insert row 4 failed"));
		goto odbcfail;
	}

	AB_PRINT(("Insert row 5"));
	/*
	 * Ok - new row with parameterised GUID.
	 */
	strcpy((char *) (queryString), "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
                         VALUES ( 'Fluffy', 'Sam', 'dragon', 'm', 16, ? );");

	sqlguid.Data1 = 0xaabbccdd;
	sqlguid.Data2 = 0xeeff;
	sqlguid.Data3 = 0x1122;
	sqlguid.Data4[0] = 0x11;
	sqlguid.Data4[1] = 0x22;
	sqlguid.Data4[2] = 0x33;
	sqlguid.Data4[3] = 0x44;
	sqlguid.Data4[4] = 0x55;
	sqlguid.Data4[5] = 0x66;
	sqlguid.Data4[6] = 0x77;
	sqlguid.Data4[7] = 0x88;

	lenOrInd = 16;
	strcpy((char *) (guid), "1234abcd-abcd-abcd-abcd-123456789abc");

	if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_GUID, SQL_GUID, 16, 0, &sqlguid, 16, &lenOrInd)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		goto odbcfail;
	}
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Insert row 5 failed"));
		AB_ERROR(("Sadly this was expected in *nix ODBC. Carry on."));
	}

	/*
	 * Now retrieve rows - especially GUID column values.
	 */
	AB_PRINT(("retrieving name and guid"));
	strcpy((char *) (queryString), "SELECT name, guid FROM #pet");
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("SELECT failed"));
		goto odbcfail;
	}
	while (SQLFetch(Statement) == SQL_SUCCESS) {
		count++;
		if (SQLGetData(Statement, 1, SQL_CHAR, name, 20, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, name column failed", count));
			goto odbcfail;
		}
		if (SQLGetData(Statement, 2, SQL_CHAR, guid, 37, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, guid column failed", count));
			goto odbcfail;
		}

		AB_PRINT(("name: %-10s guid: %s", name, guid));
	}

	/*
	 * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
	 * state if we try SELECT again).
	 */
	if (SQLFreeStmt(Statement, SQL_CLOSE) != SQL_SUCCESS) {
		AB_ERROR(("Free statement failed (5)"));
		goto odbcfail;
	}
	if (SQLAllocHandle(SQL_HANDLE_STMT, Connection, &Statement)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLAllocStmt failed(1)"));
		goto odbcfail;
	}


	/*
	 * Now retrieve rows - especially GUID column values.
	 */

	AB_PRINT(("retrieving name and guid again"));
	strcpy((char *) (queryString), "SELECT name, guid FROM #pet");
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("SELECT failed"));
		goto odbcfail;
	}
	while (SQLFetch(Statement) == SQL_SUCCESS) {
		count++;
		if (SQLGetData(Statement, 1, SQL_CHAR, name, 20, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, name column failed", count));
			goto odbcfail;
		}
		if (SQLGetData(Statement, 2, SQL_GUID, &sqlguid, 16, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, guid column failed", count));
			goto odbcfail;
		}

		AB_PRINT(("%-10s %08X-%04X-%04X-%02X%02X-%02X%02X%02X%02X%02X%02X",
			  name,
			  (int) (sqlguid.Data1), sqlguid.Data2,
			  sqlguid.Data3, sqlguid.Data4[0], sqlguid.Data4[1],
			  sqlguid.Data4[2], sqlguid.Data4[3], sqlguid.Data4[4],
			  sqlguid.Data4[5], sqlguid.Data4[6], sqlguid.Data4[7]));
	}

	/*
	 * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
	 * state if we try SELECT again).
	 */
	if (SQLFreeStmt(Statement, SQL_CLOSE) != SQL_SUCCESS) {
		AB_ERROR(("Free statement failed (5)"));
		goto odbcfail;
	}
	if (SQLAllocHandle(SQL_HANDLE_STMT, Connection, &Statement)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLAllocStmt failed(1)"));
		goto odbcfail;
	}

	/*
	 * Now retrieve rows via stored procedure passing GUID as param.
	 */
	AB_PRINT(("retrieving name and guid"));

	strcpy((char *) (queryString), "{call GetGUIDRows(?)}");
	lenOrInd = SQL_NTS;
	strcpy((char *) (guid), "87654321-4321-4321-4321-123456789abc");

	if (SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		goto odbcfail;
	}
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("SELECT failed"));
		goto odbcfail;
	}
	while (SQLFetch(Statement) == SQL_SUCCESS) {
		count++;
		if (SQLGetData(Statement, 1, SQL_CHAR, name, 20, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, name column failed", count));
			goto odbcfail;
		}
		if (SQLGetData(Statement, 2, SQL_CHAR, guid, 37, 0)
		    != SQL_SUCCESS) {
			AB_ERROR(("Get row %d, guid column failed", count));
			goto odbcfail;
		}

		AB_PRINT(("%-10s %s", name, guid));
	}

	/*
	 * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
	 * state after a previous SELECT has occurred).
	 */
	if (SQLFreeStmt(Statement, SQL_CLOSE) != SQL_SUCCESS) {
		AB_ERROR(("Free statement failed (5)"));
		goto odbcfail;
	}
	if (SQLAllocHandle(SQL_HANDLE_STMT, Connection, &Statement)
	    != SQL_SUCCESS) {
		AB_ERROR(("SQLAllocStmt failed(1)"));
		goto odbcfail;
	}

	/* cleanup */
	CommandWithResult(Statement, "DROP PROC GetGUIDRows");

	/* CLOSEDOWN */

	Disconnect();

	AB_FUNCT(("TestRawODBCGuid (out): ok"));
	return TRUE;

      odbcfail:
	DispODBCErrs(Environment, Connection, Statement);
	DispODBCDiags(Statement);
	AB_FUNCT(("TestRawODBCGuid (out): error"));
	return FALSE;
}

/**
 * Array of tests.
 */
static DbTestEntry _dbTests[] = {
	/* 1 */ {TestRawODBCDirectQuery, "Raw ODBC direct query"},
	/* 2 */ {TestRawODBCPreparedQuery, "Raw ODBC prepared query"},
	/* 3 */ {TestRawODBCGuid, "Raw ODBC GUID"},
	/* end */ {0, 0}
};

static DbTestEntry *tests = _dbTests;

/**
 * Code to iterate through all tests to run.
 *
 * \return
 *      TRUE if all tests pass, FALSE if any tests fail.
 */
static int
RunTests(void)
{
	unsigned int i;
	unsigned int passes = 0;
	unsigned int fails = 0;

	i = 0;
	while (tests[i].testFn) {
		printf("Running test %2d: %s... ", i + 1, tests[i].description);
		fflush(stdout);
		if (tests[i].testFn()) {
			printf("pass\n");
			passes++;
		} else {
			printf("fail\n");
			fails++;
		}
		i++;
	}

	if (fails == 0) {
		printf("\nAll %d tests passed.\n\n", passes);
	} else {
		printf("\nTest passes: %d, test fails: %d\n\n", passes, fails);
	}

	/* Return TRUE if there are no failures */
	return (!fails);
}

int
main(int argc, char *argv[])
{
	use_odbc_version3 = 1;

	if (RunTests()) {
		return 0;	/* Success */
	} else {
		return 1;	/* Error code */
	}
}