Click here to Skip to main content
11,644,731 members (67,285 online)
Click here to Skip to main content

SQL Syntax Validator

, 7 Aug 2001 CPOL 133.7K 2.6K 37
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)

Share

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

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
senthamizhselvaraj7-Oct-10 21:45
membersenthamizhselvaraj7-Oct-10 21:45 
GeneralAhh very useful little script Pin
jasonp122-Jan-10 16:02
memberjasonp122-Jan-10 16:02 
GeneralValidate SQL before executing it (SQL Server 2000 / 2005) Pin
manish_gcet5-Mar-08 1:39
membermanish_gcet5-Mar-08 1:39 
GeneralOne Problem! Pin
Anonymous10-Aug-01 9:09
memberAnonymous10-Aug-01 9:09 
GeneralOLEDB Consumer or ADO Pin
John Smith9-Aug-01 6:14
memberJohn Smith9-Aug-01 6:14 
GeneralRe: OLEDB Consumer or ADO Pin
Santosh Rao9-Aug-01 6:36
memberSantosh Rao9-Aug-01 6:36 
Okay but this was something which i thought worth sharing from one my projects which was MFC based.

Anyway let me give a try to do the same using ADO minus MFC. No time frame, but i promise will give a try once i get free. Wink | ;)

My initial inspection on ADO methods/properties havent led far on this by the way.

BTW in ADO you can do this using a MS SQL7 specific featue.
By passing the SET NOEXEC command.
GeneralGood concept but dangerous Pin
Jason Z9-Aug-01 0:21
memberJason Z9-Aug-01 0:21 
GeneralRe: Good concept but dangerous Pin
Santosh Rao9-Aug-01 1:02
memberSantosh Rao9-Aug-01 1:02 
GeneralRe: Good concept but dangerous Pin
Anonymous10-Aug-01 7:53
memberAnonymous10-Aug-01 7:53 
GeneralQuery Builder Pin
Anonymous8-Aug-01 21:19
memberAnonymous8-Aug-01 21:19 
GeneralRe: Query Builder Pin
Santosh Rao8-Aug-01 22:19
memberSantosh Rao8-Aug-01 22:19 
GeneralRe: Query Builder Pin
Anonymous9-Aug-01 20:04
memberAnonymous9-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 | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 8 Aug 2001
Article Copyright 2001 by Santosh Rao
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid