testodbc.c

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

/*
 * Code to test ODBC implementation.
 *  - David Fraser, Abelon Systems 2003.
 */

/* 
 * TODO
 * remove Northwind dependency
 */

#include "common.h"

static char software_version[] = "$Id: testodbc.c,v 1.9 2005/06/29 07:21:24 freddy77 Exp $";
static void *no_unused_var_warn[] = { software_version, no_unused_var_warn };

#ifdef DEBUG
# define AB_FUNCT(x)  do { printf x; printf("\n"); } while(0)
# define AB_PRINT(x)  do { printf x; printf("\n"); } while(0)
#else
# define AB_FUNCT(x)
# define AB_PRINT(x)
#endif
#define AB_ERROR(x)   do { printf("ERROR: "); printf x; printf("\n"); } while(0)

#undef TRUE
#undef FALSE
enum
{ FALSE, TRUE };
typedef int DbTestFn(void);

static int RunTests(void);

typedef struct
{
	DbTestFn *testFn;
	const char *description;
} DbTestEntry;

/*
 * Output ODBC errors.
 */
static void
DispODBCErrs(SQLHENV envHandle, SQLHDBC connHandle, SQLHSTMT statementHandle)
{
	SQLCHAR buffer[256];
	SQLCHAR sqlState[16];

	/* Statement errors */
	if (statementHandle) {
		while (SQLError(envHandle, connHandle, statementHandle, sqlState, 0, buffer, sizeof(buffer), 0) == SQL_SUCCESS) {
			AB_ERROR(("%s, SQLSTATE=%s", buffer, sqlState));
		}
	}

	/* Connection errors */
	while (SQLError(envHandle, connHandle, SQL_NULL_HSTMT, sqlState, 0, buffer, sizeof(buffer), 0) == SQL_SUCCESS) {
		AB_ERROR(("%s, SQLSTATE=%s", buffer, sqlState));
	}

	/* Environmental errors */
	while (SQLError(envHandle, SQL_NULL_HDBC, SQL_NULL_HSTMT, sqlState, 0, buffer, sizeof(buffer), 0) == SQL_SUCCESS) {
		AB_ERROR(("%s, SQLSTATE=%s", buffer, sqlState));
	}
}

/*
 * Output ODBC diagnostics. Only used for 'raw' ODBC tests.
 */
static void
DispODBCDiags(SQLHSTMT statementHandle)
{
	SQLSMALLINT recNumber;
	SQLCHAR sqlState[10];
	SQLINTEGER nativeError = -99;
	SQLCHAR messageText[500];
	SQLSMALLINT bufferLength = 500;
	SQLSMALLINT textLength = -99;
	SQLRETURN status;

	recNumber = 1;

	AB_FUNCT(("DispODBCDiags (in)"));

	do {
		status = SQLGetDiagRec(SQL_HANDLE_STMT, statementHandle, recNumber,
				       sqlState, &nativeError, messageText, bufferLength, &textLength);
		if (status != SQL_SUCCESS) {
			/* No data mean normal end of iteration. Anything else is error. */
			if (status != SQL_NO_DATA) {
				AB_ERROR(("SQLGetDiagRec status is %d", status));
			}
			break;
		}
		printf("DIAG #%d, sqlState=%s, nativeError=%d, message=%s\n", recNumber, sqlState, (int) nativeError, messageText);
		recNumber++;
	} while (status == SQL_SUCCESS);

	AB_FUNCT(("DispODBCDiags (out)"));
}

/*
 * Test that makes a parameterized ODBC query using SQLPrepare and SQLExecute
 */
static int
TestRawODBCPreparedQuery(void)
{
	SQLRETURN status;
	SQLCHAR queryString[200];
	SQLLEN lenOrInd = 0;
	SQLSMALLINT supplierId = 4;
	int count;

	AB_FUNCT(("TestRawODBCPreparedQuery (in)"));

	/* INIT */

	Connect();

	/* MAKE QUERY */

	Command(Statement, "CREATE TABLE #Products ("
		"ProductID int NOT NULL ,"
		"ProductName varchar (40) ,"
		"SupplierID int NULL ,"
		"CategoryID int NULL ,"
		"QuantityPerUnit varchar (20)  ,"
		"UnitPrice money NULL ,"
		"UnitsInStock smallint NULL ,"
		"UnitsOnOrder smallint NULL ,"
		"ReorderLevel smallint NULL ,"
		"Discontinued bit NOT NULL "
		") "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(9,'Mishi Kobe Niku',4,6,'18 - 500 g pkgs.',97.00,29,0,0,1) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(10,'Ikura',4,8,'12 - 200 ml jars',31.00,31,0,0,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(74,'Longlife Tofu',4,7,'5 kg pkg.',10.00,4,20,5,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(11,'Queso Cabrales',5,4,'1 kg pkg.',21.00,22,30,30,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(12,'Queso Manchego La Pastora',5,4,'10 - 500 g pkgs.',38.00,86,0,0,0)");
	while (SQLMoreResults(Statement) == SQL_SUCCESS);

	strcpy((char *) (queryString), "SELECT * FROM #Products WHERE SupplierID = ?");

	status = SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		DispODBCErrs(Environment, Connection, Statement);
		DispODBCDiags(Statement);
		AB_FUNCT(("TestRawODBCPreparedQuery (out): error"));
		return FALSE;
	}

	status = SQLPrepare(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Prepare failed"));
		AB_FUNCT(("TestRawODBCPreparedQuery (out): error"));
		return FALSE;
	}

	status = SQLExecute(Statement);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Execute failed"));
		DispODBCErrs(Environment, Connection, Statement);
		DispODBCDiags(Statement);
		AB_FUNCT(("TestRawODBCPreparedQuery (out): error"));
		return FALSE;
	}

	count = 0;

	while (SQLFetch(Statement) == SQL_SUCCESS) {
		count++;
	}
	AB_PRINT(("Got %d rows", count));

	if (count != 3) {
		/*
		 * OK - so 3 is a magic number - it's the number of rows matching
		 * this query from the MS sample Northwind database and is a constant.
		 */
		AB_ERROR(("Expected %d rows - but got %d rows", 3, count));
		AB_FUNCT(("TestRawODBCPreparedQuery (out): error"));
		return FALSE;
	}

	/* CLOSEDOWN */

	Disconnect();

	AB_FUNCT(("TestRawODBCPreparedQuery (out): ok"));
	return TRUE;
}

/*
 * Test that makes a parameterized ODBC query using SQLExecDirect.
 */
static int
TestRawODBCDirectQuery(void)
{
	SQLRETURN status;
	SQLCHAR queryString[200];
	SQLLEN lenOrInd = 0;
	SQLSMALLINT supplierId = 1;
	int count;

	AB_FUNCT(("TestRawODBCDirectQuery (in)"));

	/* INIT */

	Connect();

	/* MAKE QUERY */

	Command(Statement, "CREATE TABLE #Products ("
		"ProductID int NOT NULL ,"
		"ProductName varchar (40) ,"
		"SupplierID int NULL ,"
		"CategoryID int NULL ,"
		"QuantityPerUnit varchar (20)  ,"
		"UnitPrice money NULL ,"
		"UnitsInStock smallint NULL ,"
		"UnitsOnOrder smallint NULL ,"
		"ReorderLevel smallint NULL ,"
		"Discontinued bit NOT NULL "
		") "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(1,'Chai',1,1,'10 boxes x 20 bags',18.00,39,0,10,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(2,'Chang',1,1,'24 - 12 oz bottles',19.00,17,40,25,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(3,'Aniseed Syrup',1,2,'12 - 550 ml bottles',10.00,13,70,25,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(4,'Chef Anton''s Cajun Seasoning',2,2,'48 - 6 oz jars',22.00,53,0,0,0) "
		"INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(5,'Chef Anton''s Gumbo Mix',2,2,'36 boxes',21.35,0,0,0,1) ");
	while (SQLMoreResults(Statement) == SQL_SUCCESS);

	strcpy((char *) (queryString), "SELECT * FROM #Products WHERE SupplierID = ?");

	status = SQLBindParameter(Statement, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("SQLBindParameter failed"));
		DispODBCErrs(Environment, Connection, Statement);
		DispODBCDiags(Statement);
		AB_FUNCT(("TestRawODBCDirectQuery (out): error"));
		return FALSE;
	}

	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS) {
		AB_ERROR(("Execute failed"));
		DispODBCErrs(Environment, Connection, Statement);
		DispODBCDiags(Statement);
		AB_FUNCT(("TestRawODBCDirectQuery (out): error"));
		return FALSE;
	}

	count = 0;

	while (SQLFetch(Statement) == SQL_SUCCESS) {
		count++;
	}
	AB_PRINT(("Got %d rows", count));

	if (count != 3) {
		/*
		 * OK - so 3 is a magic number - it's the number of rows matching
		 * this query from the MS sample Northwind database and is a constant.
		 */
		AB_ERROR(("Expected %d rows - but got %d rows", 3, count));
		AB_FUNCT(("TestRawODBCDirectQuery (out): error"));
		return FALSE;
	}

	/* CLOSEDOWN */

	Disconnect();

	AB_FUNCT(("TestRawODBCDirectQuery (out): ok"));
	return TRUE;
}

/*
 * Test that show what works and what doesn't for the poorly
 * documented GUID.
 */
static int
TestRawODBCGuid(void)
{
	SQLRETURN status;

	SQLCHAR queryString[300];
	SQLLEN lenOrInd;
	SQLSMALLINT age;
	SQLCHAR guid[40];
	SQLCHAR name[20];

	SQLGUID sqlguid;
	int count = 0;

	AB_FUNCT(("TestRawODBCGuid (in)"));

	Connect();
	
	if (!db_is_microsoft()) {
		Disconnect();
		return TRUE;
	}

	AB_PRINT(("Creating #pet table"));

	strcpy((char *) (queryString), "CREATE TABLE #pet (name VARCHAR(20), owner VARCHAR(20), "
	       "species VARCHAR(20), sex CHAR(1), age INTEGER, " "guid UNIQUEIDENTIFIER DEFAULT NEWID() ); ");
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS && status != SQL_NO_DATA) {
		AB_ERROR(("Create table failed"));
		goto odbcfail;
	}

	CommandWithResult(Statement, "DROP PROC GetGUIDRows");

	AB_PRINT(("Creating stored proc GetGUIDRows"));

	strcpy((char *) (queryString), "CREATE PROCEDURE GetGUIDRows (@guidpar uniqueidentifier) AS \
                SELECT name, guid FROM #pet WHERE guid = @guidpar");
	status = SQLExecDirect(Statement, queryString, SQL_NTS);
	if (status != SQL_SUCCESS && status != SQL_NO_DATA) {
		AB_ERROR(("Create procedure failed"));
		goto odbcfail;
	}

	AB_PRINT(("Insert row 1"));