YOU CAN CODE!

 

With The Case Of UCanCode.net  Release The Power OF  Visual C++ !   HomeProducts | PurchaseSupport | Downloads  
Download Evaluation
Pricing & Purchase?
E-XD++Visual C++/ MFC Products
Overview
Features Tour 
Electronic Form Solution
Visualization & HMI Solution
Power system HMI Solution
CAD Drawing and Printing Solution

Bar code labeling Solution
Workflow Solution

Coal industry HMI Solution
Instrumentation Gauge Solution

Report Printing Solution
Graphical modeling Solution
GIS mapping solution

Visio graphics solution
Industrial control SCADA &HMI Solution
BPM business process Solution

Industrial monitoring Solution
Flowchart and diagramming Solution
Organization Diagram Solution

Graphic editor Source Code
UML drawing editor Source Code
Map Diagramming Solution

Architectural Graphic Drawing Solution
Request Evaluation
Purchase
ActiveX COM Products
Overview
Download
Purchase
Technical Support
  General Q & A
Discussion Board
Contact Us

Links

Get Ready to Unleash the Power of UCanCode .NET


UCanCode Software focuses on general application software development. We provide complete solution for developers. No matter you want to develop a simple database workflow application, or an large flow/diagram based system, our product will provide a complete solution for you. Our product had been used by hundreds of top companies around the world!

"100% source code provided! Free you from not daring to use components because of unable to master the key technology of components!"


MFC Source Code: SQLPrepare and SQLAllocStmt with CDBException and SQL Query and ExecuteSQL

 
 

Introduction

I had one of my requirements in my application to store some custom SQL Queries into our System. These SQL Queries would be provided by the end user who is kind of well versed at least with SQL. Our system later would execute these queries to perform various aspects of the system.

So this obviously had a requirement of testing the Syntax of the SQL Stored against the database it was being executed.

So one could definitely just think of creating the database and using the CDatabase's ExecuteSQL method which would throw a CDBException but potential harm lay wherein these Queries would be fired where as our only intention was to test the Syntax.

At the same time leaving the Application to Store un-verified SQL statements and later coming out with errors was not a happy scenario on part of the Configuration Application that accepted the SQL Statement in the first place.

One more way to do that would be to simply use a BeginTrans and RollBack. But this was resource expensive or a kind of resource misuse and also would make the assumption that the target Database supported Transactions.

After playing with the CDatabase and CRecordset source code in MFC Source code (DBCore.Cpp).

I saw the ::SQLPrepare API which just prepares an SQL Statement for execution and parses it causing errors if any. I then encapsulated this in to a class CSQLSyntaxValidator.

A Quick Peak into the source code is there below.

In Brief the code below Allocates a HSTMT using the ::SQLAllocStmt API. It then calls the ::SQLPrepare API. It then takes Checks the Return Code of the the API to obtain the error and store it in the szError return value.

The Check Function is same as that in DBCore.Cpp and also the macros AFX_SQL_SYNC and AFX_ODBC_CALL are used which are defined in AFXDB.H.

Collapse Copy Code
BOOL CSQLSyntaxValidator::VerifySQL(CDatabase *pDb,CString szSQL,CString &szError)
{
	USES_CONVERSION;
	szSQL.TrimLeft();
	szSQL.TrimRight();
	if(szSQL.IsEmpty())
		return TRUE;
	HSTMT hstmt = SQL_NULL_HSTMT;
	ASSERT(pDb->IsOpen()); 
	RETCODE	nRetCode;
	AFX_SQL_SYNC(::SQLAllocStmt(pDb->m_hdbc, &hstmt));
	if (!Check(pDb,hstmt,nRetCode))
	{
		CDBException e(nRetCode);
		e.BuildErrorString(pDb, hstmt);
		szError = 
		e.m_strError; 
		#ifdef _DEBUG 
			if (afxTraceFlags & traceDatabase)
				TRACE0(e.m_strError);
		#endif
	}
	pDb->OnSetOptions(hstmt);
	
	AFX_ODBC_CALL(::SQLPrepare(hstmt,
		(UCHAR*)T2A(szSQL.GetBuffer(szSQL.GetLength())), SQL_NTS));
	szSQL.ReleaseBuffer();
	if (!Check(pDb,hstmt,nRetCode))
	{
		CDBException e(nRetCode);
		e.BuildErrorString(pDb, hstmt);
		szError = e.m_strError;
		#ifdef _DEBUG
			if (afxTraceFlags & traceDatabase)
				TRACE0(e.m_strError);
		#endif
		return FALSE;
	}
	return TRUE;
}

BOOL CSQLSyntaxValidator::Check(CDatabase *pDb,HSTMT &hstmt,RETCODE nRetCode)
{
	switch (nRetCode)
	{
	case SQL_SUCCESS_WITH_INFO:
	#ifdef _DEBUG
		if (afxTraceFlags & traceDatabase)
		{
			CDBException e(nRetCode);
			TRACE0("Warning: ODBC Success With Info, ");
			e.BuildErrorString(pDb, hstmt);
		}
	#endif
		
		// Fall through
		
	case SQL_SUCCESS:
	case SQL_NO_DATA_FOUND:
	case SQL_NEED_DATA:
		return TRUE;
	}
	
	return FALSE;
}

A Quick Peak into the usage of the is there below.

Usage is really pretty simple. Just call the CSQLSyntaxValidator::VerifySQL method. All one needs is to pass the database pointer, the SQL statement whose syntax is to be verified and a error variable to obtain the error. The function would return a TRUE or a FALSE on the basis of whether the SQL Statement is proper or not.

Collapse Copy Code
	try
	{
		CDatabase db;
		if(db.OpenEx(""))
		{
			CString szSQL,szError;
			szSQL =   _T("Select x from y");
			if(!CSQLSyntaxValidator::VerifySQL(&db,szSQL,szError)) 
			{ 
				//Give Error Message
				AfxMessageBox("Failed");
				AfxMessageBox("szError");
			}
			else
			{
				AfxMessageBox("Success");
			}
		} 
		else 
			AfxMessageBox("DB Not Opened"); 
	}
	catch(CDBException *dbe)
	{
		dbe->ReportError();
		dbe->Delete();
	}

That is just about it. Hope it helps some of you folks out there.

 

 

 

Copyright ?1998-2009 UCanCode.Net Software , all rights reserved.
Other product and company names herein may be the trademarks of their respective owners.

Please direct your questions or comments to webmaster@ucancode.net