sqlexecutor.cpp

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

//
// SQLExecutor.cpp
//
// $Id: //poco/Main/DataConnectors/ODBC/testsuite/src/SQLExecutor.cpp#14 $
//
// Copyright (c) 2006, Applied Informatics Software Engineering GmbH.
// and Contributors.
//
// Permission is hereby granted, free of charge, to any person or organization
// obtaining a copy of the software and accompanying documentation covered by
// this license (the "Software") to use, reproduce, display, distribute,
// execute, and transmit the Software, and to prepare derivative works of the
// Software, and to permit third-parties to whom the Software is furnished to
// do so, all subject to the following:
// 
// The copyright notices in the Software and this entire statement, including
// the above license grant, this restriction and the following disclaimer,
// must be included in all copies of the Software, in whole or in part, and
// all derivative works of the Software, unless such copies or derivative
// works are solely in the form of machine-executable object code generated by
// a source language processor.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT
// SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE
// FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE,
// ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.
//


#include "CppUnit/TestCase.h"
#include "SQLExecutor.h"
#include "Poco/String.h"
#include "Poco/Format.h"
#include "Poco/Tuple.h"
#include "Poco/Any.h"
#include "Poco/Exception.h"
#include "Poco/Data/Common.h"
#include "Poco/Data/BLOB.h"
#include "Poco/Data/StatementImpl.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/ODBC/Connector.h"
#include "Poco/Data/ODBC/Utility.h"
#include "Poco/Data/ODBC/Diagnostics.h"
#include "Poco/Data/ODBC/Preparation.h"
#include "Poco/Data/ODBC/ODBCException.h"
#include "Poco/Data/ODBC/ODBCStatementImpl.h"
#include <sqltypes.h>
#include <iostream>


using namespace Poco::Data;
using Poco::Data::ODBC::Utility;
using Poco::Data::ODBC::Preparation;
using Poco::Data::ODBC::ConnectionException;
using Poco::Data::ODBC::StatementException;
using Poco::Data::ODBC::DataTruncatedException;
using Poco::Data::ODBC::StatementDiagnostics;
using Poco::format;
using Poco::Tuple;
using Poco::Any;
using Poco::AnyCast;
using Poco::NotFoundException;
using Poco::InvalidAccessException;
using Poco::BadCastException;
using Poco::RangeException;


struct Person
{
	std::string lastName;
	std::string firstName;
	std::string address;
	int age;
	Person(){age = 0;}
	Person(const std::string& ln, const std::string& fn, const std::string& adr, int a):lastName(ln), firstName(fn), address(adr), age(a)
	{
	}
	bool operator==(const Person& other) const
	{
		return lastName == other.lastName && firstName == other.firstName && address == other.address && age == other.age;
	}

	bool operator < (const Person& p) const
	{
		if (age < p.age)
			return true;
		if (lastName < p.lastName)
			return true;
		if (firstName < p.firstName)
			return true;
		return (address < p.address);
	}

	const std::string& operator () () const
		/// This method is required so we can extract data to a map!
	{
		// we choose the lastName as examplary key
		return lastName;
	}
};


namespace Poco {
namespace Data {


template <>
class TypeHandler<Person>
{
public:
	static void bind(std::size_t pos, const Person& obj, AbstractBinder* pBinder)
	{
		// the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
		poco_assert_dbg (pBinder != 0);
		pBinder->bind(pos++, obj.lastName);
		pBinder->bind(pos++, obj.firstName);
		pBinder->bind(pos++, obj.address);
		pBinder->bind(pos++, obj.age);
	}

	static void prepare(std::size_t pos, const Person& obj, AbstractPreparation* pPrepare)
	{
		// the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
		poco_assert_dbg (pPrepare != 0);
		pPrepare->prepare(pos++, obj.lastName);
		pPrepare->prepare(pos++, obj.firstName);
		pPrepare->prepare(pos++, obj.address);
		pPrepare->prepare(pos++, obj.age);
	}

	static std::size_t size()
	{
		return 4;
	}

	static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor* pExt)
	{
		poco_assert_dbg (pExt != 0);
		if (!pExt->extract(pos++, obj.lastName))
			obj.lastName = defVal.lastName;
		if (!pExt->extract(pos++, obj.firstName))
			obj.firstName = defVal.firstName;
		if (!pExt->extract(pos++, obj.address))
			obj.address = defVal.address;
		if (!pExt->extract(pos++, obj.age))
			obj.age = defVal.age;
	}

private:
	TypeHandler();
	~TypeHandler();
	TypeHandler(const TypeHandler&);
	TypeHandler& operator=(const TypeHandler&);
};


} } // namespace Poco::Data


SQLExecutor::SQLExecutor(const std::string& name, Poco::Data::Session* pSession): 
	CppUnit::TestCase(name),
	_pSession(pSession)
{
}


SQLExecutor::~SQLExecutor()
{
}


void SQLExecutor::bareboneODBCTest(const std::string& dbConnString, 
	const std::string& tableCreateString, 
	SQLExecutor::DataBinding bindMode, 
	SQLExecutor::DataExtraction extractMode)
{
	SQLRETURN rc;
	SQLHENV henv = SQL_NULL_HENV;
	SQLHDBC hdbc = SQL_NULL_HDBC;
	SQLHSTMT hstmt = SQL_NULL_HSTMT;

	// Environment begin
	rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	assert (SQL_SUCCEEDED(rc));
	rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
	assert (SQL_SUCCEEDED(rc));

		// Connection begin
		rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
		assert (SQL_SUCCEEDED(rc));

		POCO_SQLCHAR connectOutput[512] = {0};
		SQLSMALLINT result;
		rc = SQLDriverConnect(hdbc
			, NULL
			,(POCO_SQLCHAR*) dbConnString.c_str()
			,(SQLSMALLINT) SQL_NTS
			, connectOutput
			, sizeof(connectOutput)
			, &result
			, SQL_DRIVER_NOPROMPT);
		assert (SQL_SUCCEEDED(rc));

			// Statement begin
			rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
			assert (SQL_SUCCEEDED(rc));

			std::string sql = "DROP TABLE Test";
			POCO_SQLCHAR* pStr = (POCO_SQLCHAR*) sql.c_str();
			SQLExecDirect(hstmt, pStr, (SQLINTEGER) sql.length());
			//no return code check - ignore drop errors

			sql = tableCreateString;
			pStr = (POCO_SQLCHAR*) sql.c_str();
			rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
			assert (SQL_SUCCEEDED(rc));

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

			sql = "INSERT INTO Test VALUES (?,?,?,?,?)";
			pStr = (POCO_SQLCHAR*) sql.c_str();
			rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
			assert (SQL_SUCCEEDED(rc));

			std::string str[3] = { "111", "222", "333" };
			int fourth = 4;
			float fifth = 1.5;
			SQLLEN li = SQL_NTS;
			SQLINTEGER size = (SQLINTEGER) str[0].size();
			
			if (SQLExecutor::PB_AT_EXEC == bindMode)
				li = SQL_LEN_DATA_AT_EXEC(size);

			rc = SQLBindParameter(hstmt, 
				(SQLUSMALLINT) 1, 
				SQL_PARAM_INPUT, 
				SQL_C_CHAR, 
				SQL_VARCHAR, 
				(SQLUINTEGER) size,
				0,
				(SQLPOINTER) str[0].c_str(), 
				size, 
				&li);
			assert (SQL_SUCCEEDED(rc));

			size = (SQLINTEGER) str[1].size();
			if (SQLExecutor::PB_AT_EXEC == bindMode)
				li = SQL_LEN_DATA_AT_EXEC(size);

			rc = SQLBindParameter(hstmt, 
				(SQLUSMALLINT) 2, 
				SQL_PARAM_INPUT, 
				SQL_C_CHAR, 
				SQL_VARCHAR, 
				(SQLUINTEGER) size,
				0,
				(SQLPOINTER) str[1].c_str(), 
				size, 
				&li);
			assert (SQL_SUCCEEDED(rc));

			size = (SQLINTEGER) str[2].size();
			if (SQLExecutor::PB_AT_EXEC == bindMode)
				li = SQL_LEN_DATA_AT_EXEC(size);
			else li = size;

			rc = SQLBindParameter(hstmt, 
				(SQLUSMALLINT) 3, 
				SQL_PARAM_INPUT, 
				SQL_C_BINARY, 
				SQL_LONGVARBINARY, 
				(SQLUINTEGER) size,
				0,
				(SQLPOINTER) str[2].c_str(), 
				size, 
				&li);
			assert (SQL_SUCCEEDED(rc));

			rc = SQLBindParameter(hstmt, 
				(SQLUSMALLINT) 4, 
				SQL_PARAM_INPUT, 
				SQL_C_SLONG, 
				SQL_INTEGER, 
				0,
				0,
				(SQLPOINTER) &fourth, 
				0, 
				0);
			assert (SQL_SUCCEEDED(rc));

			rc = SQLBindParameter(hstmt, 
				(SQLUSMALLINT) 5, 
				SQL_PARAM_INPUT, 
				SQL_C_FLOAT, 
				SQL_REAL, 
				0,
				1,
				(SQLPOINTER) &fifth, 
				0, 
				0);
			assert (SQL_SUCCEEDED(rc));

			size = 0;
			if (SQLExecutor::PB_AT_EXEC == bindMode)
				li = SQL_LEN_DATA_AT_EXEC((int) sizeof(int));

			rc = SQLExecute(hstmt);
			assert (SQL_NEED_DATA == rc || SQL_SUCCEEDED(rc));

			if (SQL_NEED_DATA == rc)
			{
				SQLPOINTER pParam = 0;
				int i = 0;
				rc = SQLParamData(hstmt, &pParam);
				do
				{
					SQLINTEGER dataSize = 0;
					// Data size should be ignored for non-null, 
					// non-variable length fields, but SQLite ODBC 
					// driver insists on it always being the actual 
					// data length
					if (i < 3)
						dataSize = (SQLINTEGER) str[i++].size();
					else if (3 == i) dataSize = sizeof(int);
					else if (4 == i) dataSize = sizeof(float);

					rc = SQLPutData(hstmt, pParam, dataSize);
				}while (SQL_NEED_DATA == (rc = SQLParamData(hstmt, &pParam)));
			}
			assert (SQL_SUCCEEDED(rc));

			sql = "SELECT * FROM Test";
			pStr = (POCO_SQLCHAR*) sql.c_str();
			rc = SQLPrepare(hstmt, pStr, (SQLINTEGER) sql.length());
			assert (SQL_SUCCEEDED(rc));

			char chr[3][5] = {{ 0 }};
			SQLLEN lengths[5] = { 0 };
			fourth = 0;
			fifth = 0.0f;

			if (SQLExecutor::DE_BOUND == extractMode)
			{
				rc = SQLBindCol(hstmt, 
					(SQLUSMALLINT) 1, 
					SQL_C_CHAR, 
					(SQLPOINTER) chr[0], 
					(SQLINTEGER) 4, 
					&lengths[0]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLBindCol(hstmt, 
					(SQLUSMALLINT) 2, 
					SQL_C_CHAR, 
					(SQLPOINTER) chr[1], 
					(SQLINTEGER) 4, 
					&lengths[1]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLBindCol(hstmt, 
					(SQLUSMALLINT) 3, 
					SQL_C_BINARY, 
					(SQLPOINTER) chr[2], 
					(SQLINTEGER) 4, 
					&lengths[2]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLBindCol(hstmt, 
					(SQLUSMALLINT) 4, 
					SQL_C_SLONG, 
					(SQLPOINTER) &fourth, 
					(SQLINTEGER) 0, 
					&lengths[3]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLBindCol(hstmt, 
					(SQLUSMALLINT) 5, 
					SQL_C_FLOAT, 
					(SQLPOINTER) &fifth, 
					(SQLINTEGER) 0, 
					&lengths[4]);
				assert (SQL_SUCCEEDED(rc));
			}
			
			rc = SQLExecute(hstmt);
			assert (SQL_SUCCEEDED(rc));
			rc = SQLFetch(hstmt);
			assert (SQL_SUCCEEDED(rc));

			if (SQLExecutor::DE_MANUAL == extractMode)
			{
				rc = SQLGetData(hstmt, 
					(SQLUSMALLINT) 1, 
					SQL_C_CHAR, 
					chr[0], 
					4,
					&lengths[0]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLGetData(hstmt, 
					(SQLUSMALLINT) 2, 
					SQL_C_CHAR, 
					chr[1], 
					4,
					&lengths[1]);
				assert (SQL_SUCCEEDED(rc));

				rc = SQLGetData(hstmt, 
					(SQLUSMALLINT) 3, 
					SQL_C_BINARY,