Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

SQL Syntax Validator

Rate me:
Please Sign up or sign in to vote.
4.36/5 (9 votes)
7 Aug 2001CPOL2 min read 174.5K   3.1K   38   13
How to validate SQL Queries Prior to executing them

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)


Written By
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

 
QuestionShall we get powershell script for the syntax check Pin
Member 1297170231-Jan-17 19:50
Member 1297170231-Jan-17 19:50 
GeneralMy vote of 1 Pin
senthamizhselvaraj7-Oct-10 21:45
senthamizhselvaraj7-Oct-10 21:45 
GeneralAhh very useful little script Pin
jasonp122-Jan-10 16:02
jasonp122-Jan-10 16:02 
GeneralValidate SQL before executing it (SQL Server 2000 / 2005) Pin
manish_gcet5-Mar-08 1:39
manish_gcet5-Mar-08 1:39 
GeneralOne Problem! Pin
10-Aug-01 9:09
suss10-Aug-01 9:09 
It will not work for all ODBC drivers. From the ODBC documentation (SQLPrepare) and past experience:

"Some drivers cannot return syntax errors or access violations when the application calls SQLPrepare. A driver can handle syntax errors and access violations, only syntax errors, or neither syntax errors nor access violations. Therefore, an application must be able to handle these conditions when calling subsequent related functions such as SQLNumResultCols, SQLDescribeCol, SQLColAttribute, and SQLExecute."

GeneralOLEDB Consumer or ADO Pin
John Smith9-Aug-01 6:14
John Smith9-Aug-01 6:14 
GeneralRe: OLEDB Consumer or ADO Pin
Santosh Rao9-Aug-01 6:36
Santosh Rao9-Aug-01 6:36 
GeneralGood concept but dangerous Pin
9-Aug-01 0:21
suss9-Aug-01 0:21 
GeneralRe: Good concept but dangerous Pin
Santosh Rao9-Aug-01 1:02
Santosh Rao9-Aug-01 1:02 
GeneralRe: Good concept but dangerous Pin
10-Aug-01 7:53
suss10-Aug-01 7:53 
GeneralQuery Builder Pin
8-Aug-01 21:19
suss8-Aug-01 21:19 
GeneralRe: Query Builder Pin
Santosh Rao8-Aug-01 22:19
Santosh Rao8-Aug-01 22:19 
GeneralRe: Query Builder Pin
9-Aug-01 20:04
suss9-Aug-01 20:04 

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

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