Click here to Skip to main content
Click here to Skip to main content

SQL Syntax Validator

, 7 Aug 2001
Rate this:
Please Sign up or sign in to vote.
How to validate SQL Queries Prior to executing them
<!-- Download Links --> <!-- Main HTML starts here -->

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Santosh Rao
Architect
India India
1993 started with Computers
 
BE(Computer Science) and MS (Software Systems)
 
Industry Experience: 10 Years
 
C, C++, VC++(MFC), .NET, C#, MTS, Queuing, ASP.NET, AJAX, Java, J2EE, SunOne, JMS
 
Banking, Insurance & Pension,Health Care

Comments and Discussions

 
GeneralMy vote of 1 Pinmembersenthamizhselvaraj7-Oct-10 21:45 
GeneralAhh very useful little script Pinmemberjasonp122-Jan-10 16:02 
GeneralValidate SQL before executing it (SQL Server 2000 / 2005) Pinmembermanish_gcet5-Mar-08 1:39 
GeneralOne Problem! PinmemberAnonymous10-Aug-01 9:09 
GeneralOLEDB Consumer or ADO PinmemberJohn Smith9-Aug-01 6:14 
GeneralRe: OLEDB Consumer or ADO PinmemberSantosh Rao9-Aug-01 6:36 
GeneralGood concept but dangerous PinmemberJason Z9-Aug-01 0:21 
GeneralRe: Good concept but dangerous PinmemberSantosh Rao9-Aug-01 1:02 
True, depends on the case in which you want to do this.
 
In our case this is purely for a reporting tool, where user has select only access to some tables to some reporting tables, where he needs to generate reports based on these tables.
 
So we went ahead and did this.
GeneralRe: Good concept but dangerous PinmemberAnonymous10-Aug-01 7:53 
GeneralQuery Builder PinmemberAnonymous8-Aug-01 21:19 
GeneralRe: Query Builder PinmemberSantosh Rao8-Aug-01 22:19 
GeneralRe: Query Builder PinmemberAnonymous9-Aug-01 20:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 8 Aug 2001
Article Copyright 2001 by Santosh Rao
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid