sqlcesvr.cpp

对ce sqlsvr的一系列操作

										(void **)&pISSCECompact);
	// Validation
	if(FAILED(hr))
		goto CleanExit;

	// Initialize Property with name of new compacted database
	compactdbprop[0].dwPropertyID	= DBPROP_INIT_DATASOURCE;
	compactdbprop[0].dwOptions		= DBPROPOPTIONS_REQUIRED;
	compactdbprop[0].vValue.vt		= VT_BSTR;

	// Create new database name
	memset(szNewDBName, TEXT('\0'), 256);
	wsprintf(szNewDBName, TEXT("%sx"), lpszDBName);
	// new name for compacted database
	compactdbprop[0].vValue.bstrVal = SysAllocString(szNewDBName);

	// Initialize property set
	compactdbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	compactdbpropset[0].rgProperties	= compactdbprop;
	compactdbpropset[0].cProperties		= sizeof(compactdbprop)/sizeof(compactdbprop[0]);

	// Compact the database using the provider-specific interface
	hr = pISSCECompact->Compact(sizeof(compactdbpropset)/sizeof(compactdbpropset[0]),
								compactdbpropset);

	// Release the interface
	pISSCECompact->Release();
	pISSCECompact = NULL;

	// Disconnect the database/reset the OLE DB variable
	DisconnectDB(lpszDBName);

	// Overwrite the original database
	if (!CopyFile(szNewDBName, lpszDBName, false))
	{
		// Set the return value
		hr = E_FAIL;
		goto CleanExit;
	}

	// Delete the temp database...
	DeleteDB(szNewDBName);


CleanExit:
	// Release the used memory
	VariantClear(&dbprop[0].vValue);

	// Only execute the following command when either one of the
	// above command fail.
	if (FAILED(hr))
		// Disconnect the database/reset the OLE DB variable
		DisconnectDB(lpszDBName);

	return hr;

}

extern  "C" SQLCESVR_API HRESULT ExecuteSQL (LPTSTR lpszQuery)
{
	//  PURPOSE:  
	//		- Execute the given SQL statement.
	//  PARAMETERS:
	//		- lpszQuery	:: SQL query command string.
	//  OPERATION:
	//		- ...
	//  RETURN VALUE:
	//      - HRESULT

	HRESULT hr = NOERROR;

	// Set the SQL query statement
	hr = pICmdText->SetCommandText(DBGUID_SQL,
								   lpszQuery); 
	if(FAILED(hr))
		goto CleanExit;

	// Execute the SQL query statement without return any Rowset
	hr = pICmdText->Execute(NULL,
							IID_NULL,
							NULL,
							NULL,
							NULL);

CleanExit:


	return hr;
}

extern  "C" SQLCESVR_API HRESULT GetRowsetEx (LPTSTR lpszQuery,HWND hWndList1,int iskip)
{
	//::MessageBox(NULL,_T("GetRowsetEx"),_T("GetRowsetEx"),MB_OK); 
	//  PURPOSE:  
	//		- Execute the given SQL statement and return a RowSet object.
	//  PARAMETERS:
	//		- lpszQuery	:: SQL query command string.
	//  OPERATION:
	//		- ...
	//  RETURN VALUE:
	//      - HRESULT

	// NOTE:
	//		THE FOLLOWING CODE SHOW HOW TO CREATE A ROWSET OBJECT
	//		WITH COMMAND OBJECT (SQL STATEMENT).

	HRESULT			hr = NOERROR;
	
	IRowset			*pIRowset		= NULL;
	ICommandText	*pICommandText	= NULL;


	// Create a command object pointer
	hr = pIDBCrtCmd->CreateCommand(NULL,
								   IID_ICommandText,
								   (IUnknown**)&pICommandText);
	// Validation
	if(FAILED(hr))
		goto CleanExit;

	// Set the SQL query statement
	hr = pICommandText->SetCommandText(DBGUID_SQL,
									   lpszQuery); 
	
	if(FAILED(hr))
		goto CleanExit;

	// Execute the SQL query statement
	hr = pICommandText->Execute(NULL,
								IID_IRowset,
								NULL,
								NULL,
								(IUnknown **)&pIRowset);

	if (!FAILED(hr))
	{
		if(NULL != pIRowset)
		{
			// Proceed to walk through the retrieve Rowset object
			ProcessRowsetEx(pIRowset,hWndList1,iskip);
			//ProcessRowset(pIRowset,hWndList1);
			//::MessageBox(NULL,lpszQuery,lpszQuery,MB_OK); 
		}
	}
	
CleanExit:

	if (NULL != pIRowset)
	{
		pIRowset->Release();
		pIRowset = NULL;
	}

	if (NULL != pICommandText)
	{
		pICommandText->Release();
		pICommandText = NULL;
	}

	return hr;
}


extern  "C" SQLCESVR_API HRESULT GetRowset (LPTSTR lpszQuery,HWND hWndList1)
{
	//MessageBox(NULL,_T("GetRowset"),lpszQuery,MB_OK);
	//  PURPOSE:  
	//		- Execute the given SQL statement and return a RowSet object.
	//  PARAMETERS:
	//		- lpszQuery	:: SQL query command string.
	//  OPERATION:
	//		- ...
	//  RETURN VALUE:
	//      - HRESULT

	// NOTE:
	//		THE FOLLOWING CODE SHOW HOW TO CREATE A ROWSET OBJECT
	//		WITH COMMAND OBJECT (SQL STATEMENT).

	HRESULT			hr = NOERROR;
	
	IRowset			*pIRowset		= NULL;
	ICommandText	*pICommandText	= NULL;


	// Create a command object pointer
	hr = pIDBCrtCmd->CreateCommand(NULL,
								   IID_ICommandText,
								   (IUnknown**)&pICommandText);
	// Validation
	if(FAILED(hr))
		goto CleanExit;

	// Set the SQL query statement
	hr = pICommandText->SetCommandText(DBGUID_SQL,
									   lpszQuery); 
	
	if(FAILED(hr))
		goto CleanExit;

	// Execute the SQL query statement
	hr = pICommandText->Execute(NULL,
								IID_IRowset,
								NULL,
								NULL,
								(IUnknown **)&pIRowset);

	if (!FAILED(hr))
	{
		if(NULL != pIRowset)
		{
			// Proceed to walk through the retrieve Rowset object
			ProcessRowset(pIRowset,hWndList1);
			//::MessageBox(NULL,lpszQuery,lpszQuery,MB_OK); 
		}
	}
	
CleanExit:

	if (NULL != pIRowset)
	{
		pIRowset->Release();
		pIRowset = NULL;
	}

	if (NULL != pICommandText)
	{
		pICommandText->Release();
		pICommandText = NULL;
	}

	return hr;
}

extern  "C" SQLCESVR_API HRESULT ProcessRowset (IRowset *pIRowset,HWND hWndList1)
{
	//  PURPOSE:  
	//		- Retrieve and display data resulting from
	//		  a query specified in GetRowset function
	//  PARAMETERS:
	//		- NIL
	//  OPERATION:
	//		- ...
	//  RETURN VALUE:
	//      - HRESULT

	// FOR LISTVIEW USED ONLY
	LVCOLUMN	pcol;
	LVITEM		pitem;
	long		idx=0;
	long		lTotalRows	= 0;
	static long	lTotalCols	= 0;
	long		t1=0;
	long		t2=0;
	long		elapse=0;
	int			iHour, iMinute, iSecond, iMiliSec;

	HRESULT			hr = NOERROR;

	ULONG			lColumn		= 0;
	ULONG			lNumCols	= 0;
	ULONG			lCount		= 0;
	ULONG           lNumRowsRetrieved = 0;
	ULONG           ConsumerBufColOffset = 0;
	
	IAccessor		*pIAccessor    = NULL;
	IColumnsInfo	*pIColumnsInfo = NULL;
	DBCOLUMNINFO	*pDBColumnInfo = NULL;
	DBBINDING		*pBindings	   = NULL;

	HACCESSOR       hAccessor	   = NULL;
	HROW            hRows[10];
	HROW			*pRows		   = &hRows[0];
	BYTE			*pBuffer	   = NULL;

	WCHAR			*pStringsBuffer = NULL;

	TCHAR			szBuffer[1024];

	// NOTE:
	//		THE FOLLOWING CODE SHOW THE GENERATE METHOD TO DISPLAY
	//		THE READED ROWSET COLUMN DATA INTO LISTVIEW.
	//		HENCE, FOR BETTER EFFICIENTCY, THE FOLLOWING CODE
	//		MUST BE MODIFIED TO SUITE THE NEED WITH REFERENCE TO
	//		THE RESPECTIVE ROWSET.
	
	// Get the start time (CPU Tick)
	t1 = GetTickCount();
	
    // Obtain access to the IColumnInfo interface, from the Rowset object.
    hr = pIRowset->QueryInterface(IID_IColumnsInfo,
								  (void **)&pIColumnsInfo);
	// Validation
    if(FAILED(hr))
    {
		// Update status
		//UpdateList(TEXT("Failed to query IColumnsInfo interface!"));
		// Terminate the current routine
		goto CleanExit;
    }

    // Retrieve the column information.
    pIColumnsInfo->GetColumnInfo(&lNumCols,
								 &pDBColumnInfo,
								 &pStringsBuffer);

    // Free the column information interface.
	  
    pIColumnsInfo->Release();

    // Create a DBBINDING array.
    pBindings = new DBBINDING[lNumCols];

    // Using the ColumnInfo structure, fill out the pBindings array.
    for(lCount=0; lCount<lNumCols; lCount++)
	{
		pBindings[lCount].iOrdinal		= lCount+1;
		pBindings[lCount].obValue		= ConsumerBufColOffset;
		pBindings[lCount].pTypeInfo		= NULL;
		pBindings[lCount].pObject		= NULL;
		pBindings[lCount].pBindExt		= NULL;
		pBindings[lCount].dwPart		= DBPART_VALUE;
		pBindings[lCount].dwMemOwner	= DBMEMOWNER_CLIENTOWNED;
		pBindings[lCount].eParamIO		= DBPARAMIO_NOTPARAM;
		// NOTE:
		//		DUE TO THE OUTPUT DATA TYPE OF EACH FIELDS WAS
		//		CONVERTED INTO "DBTYPE_WSTR" WITHIN THE SQL SERVER CE
		//		(HARDCODED) INSTEAD OF USING THE ORIGINAL DATA TYPE AS:
		//			pBindings[lCount].wType
		//		AS A RESULT, IT WILL NO LONGER FOLOOW THE VALUE STORE
		//		IN pDBColumnInfo[lCount].ulColumnSize
		//
		//		HENCE, THE MAXIMUM COLUMN SIZE WAS SET TO 48BYTES
		//		IT CAN BE ANY VALUE, AS LONG AS IT IS LARGE ENOUGH
		//		TO HOLD THE CONVERTED DATA FOR ALL THE READ COLUMNS.
		pBindings[lCount].cbMaxLen		= 400;//48;//pColumnsInfo[nCol].ulColumnSize
		pBindings[lCount].dwFlags		= 0;
		//TCHAR temp[100];
		//wsprintf(temp,_T("%d"),pDBColumnInfo[lCount].ulColumnSize);
		//::MessageBox(NULL,temp,temp,MB_OK); 

		// NOTE:
		//		DUE TO DATA CONVERSION ERROR, SO WE HARDCODED THE
		//		DATA TYPE TO DBTYPE_WSTR INSTEAD OF USING THE DATA
		//		TYPE OBTAIN FROM THE DBCOLUMNINFO STRUCTURE AS:
		//			DBColumnInfo[lCount].wType
		//		THROUGH THE GetColumnInfo INTERFACE
		pBindings[lCount].wType			= DBTYPE_WSTR;
		pBindings[lCount].bPrecision	= pDBColumnInfo[lCount].bPrecision;
		pBindings[lCount].bScale		= pDBColumnInfo[lCount].bScale;
		// NOTE:
		//		DUE TO THE DATA TYPE WAS HARDCODED TO DBTYPE_WSTR. HENCE
		//		THE "ColumnSize" VALUE IN THE DBCOLUMNINFO STRUCTURE AS:
		//			pDBColumnInfo[lCount].ulColumnSize
		//		WILL NO LONGER APPLICABLE AND THE NEW HARDCODED SIZE OF
		//		48 BYTES WAS USED IN THIS CASE.
		//		THIS VALUS SHOULD BE CHANGE ARCCODING TO THE DEFINE DATA
		//		TYPE AND NOT NECESSARY MUST BE 48 BYTES.
 
		// Compute the next buffer offset.
		ConsumerBufColOffset += 400;	//pDBColumnInfo[lCount].ulColumnSize;
		//ConsumerBufColOffset += (pDBColumnInfo[lCount].ulColumnSize*2);
    };

    // Get the IAccessor interface.
    hr = pIRowset->QueryInterface(IID_IAccessor,
								  (void **)&pIAccessor);
	// Validation
    if(FAILED(hr))
    {
		// Update status
		//UpdateList(TEXT("Failed to query IAccessor interface!"));
		// Terminate the current routine
		goto CleanExit;
    }

	// Create an accessor from the set of bindings (pBindings).
    pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
                               lNumCols,
                               pBindings,
                               0,
                               &hAccessor,
                               NULL);

	// ------------------------------------------------------------
	// NOTE:
	//		THE FOLLOWING CODE CAN BE REMOVE, IF THE COLUMN CAPTION
	//		DOES NOT REQUIRE.
	// ------------------------------------------------------------