SQL Syntax Validator

• Download source files - 1 Kb
• Download demo project - 7 Kb

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.

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.

	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.