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
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))
{
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.