sqlexecutor.cpp

很好用的网络封装库,不熟悉网络编程的人也可以使用。使用风格良好的标准c++编写。

					chr[2], 
					3,
					&lengths[2]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLGetData(hstmt, 
					(SQLUSMALLINT) 4, 
					SQL_C_SLONG, 
					&fourth, 
					4,
					&lengths[3]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLGetData(hstmt, 
					(SQLUSMALLINT) 5, 
					SQL_C_FLOAT, 
					&fifth, 
					5,
					&lengths[4]);
				assert (SQL_SUCCEEDED(rc));
			}

			assert (0 == strncmp("111", chr[0], 3));
			assert (0 == strncmp("222", chr[1], 3));
			assert (0 == strncmp("333", chr[2], 3));
			assert (4 == fourth);
			assert (1.5 == fifth);

			rc = SQLCloseCursor(hstmt);
			assert (SQL_SUCCEEDED(rc));

			sql = "DROP TABLE Test";
			pStr = (POCO_SQLCHAR*) sql.c_str();
			rc = SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
			assert (SQL_SUCCEEDED(rc));

			rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			assert (SQL_SUCCEEDED(rc));

		// Connection end
		rc = SQLDisconnect(hdbc);
		assert (SQL_SUCCEEDED(rc));
		rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		assert (SQL_SUCCEEDED(rc));

	// Environment end
	rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
	assert (SQL_SUCCEEDED(rc));
}


void SQLExecutor::simpleAccess()
{
	std::string funct = "simpleAccess()";
	std::string lastName = "lastName";
	std::string firstName("firstName");
	std::string address("Address");
	int age = 133132;
	int count = 0;
	std::string result;

	count = 0;
	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age), now;  }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now;  }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	assert (count == 1);

	try { *_pSession << "SELECT LastName FROM PERSON", into(result), now;  }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (lastName == result);

	try { *_pSession << "SELECT Age FROM PERSON", into(count), now;  }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == age);
}


void SQLExecutor::complexType()
{
	std::string funct = "complexType()";
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(p1), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(p2), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	Person c1;
	Person c2;
	try { *_pSession << "SELECT * FROM PERSON WHERE LastName = 'LN1'", into(c1), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (c1 == p1);
}


void SQLExecutor::simpleAccessVector()
{
	std::string funct = "simpleAccessVector()";
	std::vector<std::string> lastNames;
	std::vector<std::string> firstNames;
	std::vector<std::string> addresses;
	std::vector<int> ages;
	std::string tableName("Person");
	lastNames.push_back("LN1");
	lastNames.push_back("LN2");
	firstNames.push_back("FN1");
	firstNames.push_back("FN2");
	addresses.push_back("ADDR1");
	addresses.push_back("ADDR2");
	ages.push_back(1);
	ages.push_back(2);
	int count = 0;
	std::string result;

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::vector<std::string> lastNamesR;
	std::vector<std::string> firstNamesR;
	std::vector<std::string> addressesR;
	std::vector<int> agesR;
	try { *_pSession << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (ages == agesR);
	assert (lastNames == lastNamesR);
	assert (firstNames == firstNamesR);
	assert (addresses == addressesR);
}


void SQLExecutor::complexTypeVector()
{
	std::string funct = "complexTypeVector()";
	std::vector<Person> people;
	people.push_back(Person("LN1", "FN1", "ADDR1", 1));
	people.push_back(Person("LN2", "FN2", "ADDR2", 2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::vector<Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result == people);
}


void SQLExecutor::insertVector()
{
	std::string funct = "insertVector()";
	std::vector<std::string> str;
	str.push_back("s1");
	str.push_back("s2");
	str.push_back("s3");
	str.push_back("s3");
	int count = 100;

	{
		Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(str)));
		try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
		catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
		catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
		assert (count == 0);

		try { stmt.execute(); }
		catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

		try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
		catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
		catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
		assert (count == 4);
	}
	count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 4);
}


void SQLExecutor::insertEmptyVector()
{
	std::string funct = "insertEmptyVector()";
	std::vector<std::string> str;

	try
	{
		*_pSession << "INSERT INTO Strings VALUES (?)", use(str), now;
		fail("empty collections should not work");
	}
	catch (Poco::Exception&)
	{
	}
}


void SQLExecutor::insertSingleBulk()
{
	std::string funct = "insertSingleBulk()";
	int x = 0;
	Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(x)));

	for (x = 0; x < 100; ++x)
	{
		int i = stmt.execute();
		assert (i == 0);
	}
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 100);

	try { *_pSession << "SELECT SUM(str) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == ((0+99)*100/2));
}


void SQLExecutor::floats()
{
	std::string funct = "floats()";
	float data = 1.5f;
	float ret = 0.0f;

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 1);

	try { *_pSession << "SELECT str FROM Strings", into(ret), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (ret == data);
}


void SQLExecutor::doubles()
{
	std::string funct = "floats()";
	double data = 1.5;
	double ret = 0.0;

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 1);

	try { *_pSession << "SELECT str FROM Strings", into(ret), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (ret == data);
}


void SQLExecutor::insertSingleBulkVec()
{
	std::string funct = "insertSingleBulkVec()";
	std::vector<int> data;
	
	for (int x = 0; x < 100; ++x)
		data.push_back(x);

	Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(data)));
	stmt.execute();

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	assert (count == 100);
	try { *_pSession << "SELECT SUM(str) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == ((0+99)*100/2));
}


void SQLExecutor::limits()
{
	std::string funct = "limit()";
	std::vector<int> data;
	for (int x = 0; x < 100; ++x)
	{
		data.push_back(x);
	}

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	std::vector<int> retData;
	try { *_pSession << "SELECT * FROM Strings", into(retData), limit(50), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (retData.size() == 50);
	for (int x = 0; x < 50; ++x)
	{
		assert(data[x] == retData[x]);
	}
}


void SQLExecutor::limitZero()
{
	std::string funct = "limitZero()";
	std::vector<int> data;
	for (int x = 0; x < 100; ++x)
	{
		data.push_back(x);
	}

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	std::vector<int> retData;
	try { *_pSession << "SELECT * FROM Strings", into(retData), limit(0), now; }// stupid test, but at least we shouldn't crash
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (retData.size() == 0);
}


void SQLExecutor::limitOnce()
{
	std::string funct = "limitOnce()";
	std::vector<int> data;
	for (int x = 0; x < 101; ++x)
	{
		data.push_back(x);
	}

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	std::vector<int> retData;
	Statement stmt = (*_pSession << "SELECT * FROM Strings", into(retData), limit(50), now);
	assert (!stmt.done());
	assert (retData.size() == 50);
	stmt.execute();
	assert (!stmt.done());
	assert (retData.size() == 100);
	stmt.execute();
	assert (stmt.done());
	assert (retData.size() == 101);

	for (int x = 0; x < 101; ++x)
	{
		assert(data[x] == retData[x]);
	}
}


void SQLExecutor::limitPrepare()
{
	std::string funct = "limitPrepare()";
	std::vector<int> data;
	for (int x = 0; x < 100; ++x)
	{
		data.push_back(x);
	}

	try { *_pSession << "INSERT INTO Strings VALUES (?)", use(data), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	std::vector<int> retData;