The Problem
In my previous article "Validation of printf() function", I described the technique that can be used for validation of various syntax constructions that cannot be checked by C++ compiler. I call this technique a startup validation. I remind here its properties:
- The code with the error does not need to run to detect the error.
- All functions and files where the error can happen are analyzed automatically. Even if the function with the error is never called, the error is detected as soon as the file that contains the function compiles.
- The whole validation procedure is started by a simple C++ function that should be called explicitly as soon as application starts, probably after some initialization.
- If running under Visual Studio, validation procedure reports errors in Output window. By double-clicking on the error line Visual Studio C++ editor opens the file with the error and positions cursor on the line that contains the error.
The exercise with printf() while useful by itself was a really good warm-up for more ambitious goal – validation of SQL statements.
Let me describe the problem. In old days applications that access databases widely used embedded SQL. The term embedded SQL refers to SQL statements that are embedded in another programming language, such as C or COBOL. Example of embedded SQL:
EXEC SQL PREPARE S1 FROM "INSERT INTO EMP VALUES (?, ?)"
while(GetInput(name, &dnum))
EXEC SQL EXECUTE S1 USING :name, :dnum
An embedded SQL application is compiled in two stages. First the application is precompiled by a precompiler provided by the DBMS vendor and then the output of the precompiler is compiled by the normal compiler for the host programming language. The output of the precompiler is the same as the original program, but the embedded SQL statements (every line that begins with EXEC SQL) are replaced by function calls to the runtime library, provided by the DBMS vendor. During the precompilation the syntax of SQL statements can be checked by the DBMS parser. The semantics of SQL statements could be also checked. For example, DBMS can verify that any table whose name is referenced actually exists in the database. That means that precompiler should make a connection to the database and query its schema. Precompilation is handled differently by different vendors, but at least some of them provide both syntax and semantic checks at compile time. As a result after program successfully compiled, all SQL statements contained in it are fully validated against the particular database.
Embedded SQL is not very popular now. The reason is that dependency on a precompiler is too heavy. For M number of DBMS and N number of host languages there should be M*N variants of precompiler. If we decide to changes DBMS or host language, there is a good chance that the precompiler that we need is not available. With precompiler we also cannot construct dynamic SQL statements at runtime. Developers now prefer to use various vendor-independent libraries: ODBC, JDBC, OLE DB, ADO, ADO.NET, etc. These libraries provide a layer between the DBMS-specific driver and a client application. DBMS change becomes much less of a problem. In the best case only the connection string should be changed.
However, this freedom comes at a cost. When using vendor-independent libraries, SQL statements are considered just as strings. They can be hard-coded or loaded at runtime from somewhere, but in all cases compiler does not know anything about them and there is no tool in place that allows to validate their syntax and semantics. If SQL statement contains an error, developer cannot know about it until application connects to the database and the particular line of the code is executed. This makes programming the database access much more painful. If organization uses many versions of the same database this problem accelerates much more. Even if all pieces of data access code were covered by unit tests and application passed rigorous QA testing, the error might happen at deployment. If application in production connects to a wrong version of the database, we don’t know about it until something bad happen.
Requirements
As a developer who often programs database access logic, I realized that my life could be much easier if I had a tool that could validate all my SQL statements and at the same time I still can use DBMS-independent code. I decided to build such a tool. Since I use mostly C++ language, the ideal tool for me should be a connectivity C++ class library that I can easily integrate with my code. I set the following targets that this library ideally should meet:
- The library should provide convenient connectivity interface. It should have methods for connecting to database, disconnecting from the database, attaching parameters to SQL statement, executing of SQL statement, reading the result set and output parameters.
- The syntax of all SQL statements should be checked.
- The semantics of all SQL statements should be validated against a specific database schema. It should be possible to check that referenced tables, columns and stored procedures are present in the database.
- If using parameterized SQL statements, it should be possible to check that the types of parameter objects match the SQL types in the SQL expression.
- If using stored procedures with parameters, it should be possible to check that the types of parameter objects match the SQL types of the parameters in the stored procedure.
- If binding parameters by number, it should be possible to check that the parameter numbers are correct.
- If binding parameters by name, it should be possible to check that the parameter names are correct.
- Optionally, it should be possible to check that no parameter is missing.
- All types of parameters that DBMS supports should be supported: input, output, input-output parameters, named parameters, default parameters.
- If using result sets, it should be possible to check that the types of column objects match the SQL types of columns returned in result set. This includes checking the syntax like:
SELECT * FROM … - If binding columns by number, it should be possible to check that the column numbers are correct.
- If binding columns by name, it should be possible to check that the column names are correct.
- It should be possible to access columns by names in the result set.
Choosing ODBC
First I had to choose the underlying database library that I would be using. In the process of validation my library should parse SQL statements to check SQL syntax, extract parameters information and so on. I could:
- write SQL parser by myself
- find third party open-source SQL parser
- leave the parsing task to DBMS
I decided to use approach 3) because it looked as the easiest one. Also, since my library anyway should make a connection to a database to query the database schema, SQL syntax could be checked by DBMS at this time as well. In reality it turned out that I used a combination of approaches 1) and 3).
ODBC library has a mechanism, called prepared execution. With prepared execution, SQL statement is first prepared by SQLPrepare() function. During the preparation DBMS driver compiles SQL statement into an access plan. The access plan is then can be executed one or more times at a later time by SQLExecute() function. When the statement is compiled, its SQL syntax is checked by the driver. Since access plan is generated, the semantics of the statement is also checked. If an error is detected in the statement, normally we can expect the driver to report this error somehow, however that depends on a driver’s implementation. After the statement is prepared, the application can retrieve useful metadata information from it, such as parameters and columns in the result set. This feature is also driver-dependent. Prepared execution is recommended for repeatedly executing the same, parameterized SQL statement. For my validation purposes I didn’t need repeated execution. What I needed was:
- syntax and semantic checks
- error reporting
- metadata retrieval
Prepared execution mechanism was the main reason that I chose ODBC. The other reasons were:
- ODBC is very widely supported by DBMS vendors
- it is fast if properly used
- there are available cross-platform implementations of ODBC
SQL Statements
As with printf() validation, I had to design a mechanism that would capture each SQL statement as a string, all types of parameters used with the statement and all types of columns used with the statement’s result set. The file names and line numbers of the code where errors might happen should also be captured. The capture mechanism should be launched automatically at the application startup and collect all the information into some memory storage, easily accessible by validation procedure.
The most important object in my library obviously should be SQL statement itself. The text of the statement should be read at least twice. First - when it is validated at the application startup. Second - when it is executed at runtime. For these 2 separate tasks I created 2 separate base classes. The first one - Statement is for execution at runtime. It is a part of the library’s API and should be used by client code directly. The second one – StatementValidated is for validation purposes only. It is used internally by the library.
class Statement
{
public:
virtual TCHAR* GetText() =0;
};
class StatementValidated
{
public:
const TCHAR* GetText() const
{
return m_Text.c_str();
}
protected:
std::basic_string<TCHAR> m_Text;
};
To invoke the necessary code at the application startup I was going to use my already tested C++ template-based machinery with helper macros that I described in the first article. Simplified version of the macro that wraps SQL statement:
#define DECLARE_STATEMENT(Stmt, stmt_text) \
class Stmt : public ChkdSQL::Statement \
{ \
public: \
TCHAR* GetText() \
{ \
return (stmt_text); \
} \
};
As you can see, this macro defines a class that is derived from Statement base class and that implements its virtual function GetText(). This function just returns the SQL statement text, which is passed as a second parameter of the macro.
The first parameter gives the derived class its name. I used here slightly different approach from that I had used for sprintf(). In case of sprintf() I used a local class in the isolated scope to hide its name. I didn’t need this name because I almost never reuse formatting string in different sprintf() calls. In case of SQL statement the situation is different and I might want to reuse SQL statements, for example put them into a C++ header file. Reusable class should have a name, so I had to provide the class name as a macro parameter.
The next step was to pass SQL statement text to StatementValidated class. That code had to be invoked automatically at the application startup.
template <typename Statement>
class StatementGlobal : public StatementValidated
{
public:
StatementGlobal()
{}
StatementGlobal(bool CreateInstance)
: StatementValidated(CreateInstance)
{
Statement stmt;
m_Text=stmt.GetText();
}
private:
static StatementGlobal s_Instance;
};
template <typename Statement>
StatementGlobal<Statement> StatementGlobal<Statement>::s_Instance(true);
#define DECLARE_STATEMENT(Stmt, stmt_text) \
class Stmt : public ChkdSQL::Statement \
{ \
public: \
TCHAR* GetText() \
{ \
return (stmt_text); \
} \
}; \
\
\
template class ChkdSQL::StatementGlobal<Stmt>;
DECLARE_STATEMENT() macro is now extended by one last line. This line uses the C++ language feature called explicit template instantiation. C++ explicit template instantiation is nothing more than a request to C++ compiler to compile the specified template class or function with the given template parameter. It comes handy when a template with the template with this parameter is not directly used anywhere in the code and therefore implicit instantiation cannot be used. Also, with implicit instantiation only class members that somehow are referenced in the code are compiled, while with explicit instantiation all class members are compiled. Here I want template class StatementGlobal to be compiled with the template parameter of Stmt class – the class whose name I provide in the macro. Template class StatementGlobal is derived from StatementValidated. It has a static member s_Instance of the same type – StatementGlobal. The definition of s_Instance uses overloaded constructor that takes a boolean parameter. This constructor creates an instance of the template parameter Statement class, queries its statement text string and assigns this string to the protected member of m_Text of the base class StatementValidated. For each macro declaration, one separate class Stmt is created, one distinct instantiation of StatementGlobal class is compiled, one object of static member s_Instance is created by CRT at startup time and one object of StatementValidated<code> base class is created by overloaded constructor and saves the statement text into the m_Text member variable.
The next step was to capture file name and line number of the macro declaration. I needed this information for the proper error reporting. I needed also to store the objects of StatementValidated class somewhere that they can be easily accessed by validation procedure.
std::vector<StatementValidated*<& GetAllStatements()
{
static std::vector<StatementValidated*> pStatements;
return pStatements;
}
class StatementValidated
{
public:
const TCHAR* GetText() const
{
return m_Text.c_str();
}
protected:
StatementValidated(bool)
{
GetAllStatements().push_back(this);
}
std::basic_string<TCHAR> m_Text;
std::basic_string<TCHAR> m_Location;
};
template <typename Statement>
class StatementGlobal : public StatementValidated
{
public:
StatementGlobal()
{}
StatementGlobal(bool CreateInstance)
: StatementValidated(CreateInstance)
{
Statement stmt;
m_Text=stmt.GetText();
TCHAR buffer[1024];
_sntprintf(buffer, sizeof(buffer)/sizeof(TCHAR)-1, TEXT("%s(%d)"),
Statement::File(), Statement::Line());
m_Location=buffer;
}
private:
static StatementGlobal s_Instance;
};
template <typename Statement>
StatementGlobal<Statement> StatementGlobal<Statement>::s_Instance(true);
#define DECLARE_STATEMENT(Stmt, stmt_text) \
class Stmt : public ChkdSQL::Statement \
{ \
public: \
TCHAR* GetText() \
{ \
return (stmt_text); \
} \
static const TCHAR* File() { return TEXT(__FILE__); }
static int Line(int line=__LINE__) { return line; } \
}; \
\
\
template class ChkdSQL::StatementGlobal<Stmt>;
DECLARE_STATEMENT() macro now captures the file name and the line number as static member functions of Stmt class. Then the object of the class StatementGlobal in its overloaded constructor extracts the file name and line number from its template parameter and combines them into a single string as FileName(LineNumber). This string is stored in m_Location member of StatementValidated base class.
When created by overloaded protected constructor, the object of StatementValidated class pushes a pointer to itself into a vector of pointers. This vector of pointers is stored as a local static variable pStatements and the reference of it is returned by the function GetAllStatements(). The task of capturing of SQL statement information is now completed.
Parameters
The next big task was to handle parameterized SQL statements. ODBC uses "?" sign as a parameter marker. Example of the parameterized SQL statement in ODBC syntax:
INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)
The ODBC API that deals with parameters is large and complicated. However, the minimum functionality that is necessary can be covered by small number of functions. The simplest way to attach parameters to SQL statement in ODBC is parameters binding. With this approach, no separate parameter object is created. The application just tells the driver the address of the memory variable where it should read an input parameter from or write an output parameter to. The type of the parameter should also be described as well as the length of the variable, if the type is of variable length.SQLBindParameter() function does this job. For each parameter marker in the SQL statement a separate parameter should be bound by SQLBindParameter(). SQLBindParameter() binds parameters by number. Its second argument - ParameterNumber corresponds to the ordinal number of the parameter marker in the SQL statement starting with 1. If any parameter binding is missing, the driver might use the default value for this parameter. This could be a source of errors. To change the parameter value, application should just change the value of the underlying memory variable. Next time the statement will be executed, the driver will grab the new value. Alternatively, application can rebind the parameter – call SQLBindParameter() again, providing the address of the new variable that the driver will use.
In SQLBindParameter() arguments #4 - ValueType, #8 – ParameterValuePtr and #9 – BufferLength describe the memory variable in the application that holds the parameter value. ODBC supports several types of application-side parameters called C Data Types. Some of them are built-in C language types and some of them are special types like SQL_NUMERIC_STRUCT structure. Arguments #3 - InputOutputType, #5 -ParameterType, #6 – ColumnSize and #7 - DecimalDigits on the other hand describe the parameter on the database side. ParameterType argument describes parameter type on the database side called SQL Data Type. There are special rules in ODBC for converting data from C to SQL types. Argument #10 - StrLen_or_IndPtr tells if parameter is a null-terminated string or if it is NULL or if it holds a default value.
While developer might know database-side parameter information in advance, it is also possible to query the database to get it from. This is done by function SQLDescribeParam(). Its arguments #3 - DataTypePtr, #4 – ParameterSizePtr and #5 – DecimalDigitsPtr correspond to arguments #5 - ParameterType, #6 – ColumnSize and #7 – DecimalDigits in SQLBindParameter() accordingly. Argument #6NullablePtr tells if parameter can be NULL. There is no argument in SQLDescribeParam() that corresponds to InputOutputType in SQLBindParameter(). We will talk about output parameters later. SQLDescribeParam() should be called for a statement that is already prepared. Also, SQLDescribeParam() belongs to QDBC conformance Level 2. That means that not all drivers support it. The number of parameters in the statement is given by SQLNumParams() function.
In my library, classes supporting SQL parameters are split into 2 families. First family - let’s call it back-end, takes parameters information from the database using SQLDescribeParam() function. Second family - let’s call it front-end, takes parameters information from the C++ code. Both parts complement each other and the combined information is passed to SQLBindParameter(). Simplified back-end parameters classes:
struct ParamInfo
{
ParamInfo()
: m_InputOutputType(SQL_PARAM_INPUT)
, m_DataType(0)
, m_ParameterSize(0)
, m_DecimalDigits(0)
, m_Nullable(0)
{}
SQLSMALLINT m_InputOutputType; SQLSMALLINT m_DataType; SQLULEN m_ParameterSize;
SQLSMALLINT m_DecimalDigits;
SQLSMALLINT m_Nullable;
};
class ParamInfos
{
public:
size_t GetSize() const
{
return m_VecParam.size();
}
ParamInfo& GetParamInfo(size_t idx)
{
return m_VecParam[idx];
}
const ParamInfo& GetParamInfo(size_t idx) const
{
return m_VecParam[idx];
}
private:
std::vector<ParamInfo> m_VecParam;
};
ParamInfos collection is stored in the StatementValidated class:
class StatementValidated
{
protected:
ParamInfos m_ParamInfos;
};
Simplified version of main front-end parameter structure:
struct Param
{
Param()
: m_ParamValuePtr(0)
, m_ValueType(SQL_C_SLONG)
, m_BuffLen(0)
, m_StrLen_or_Ind(0)
{}
SQLPOINTER m_ParamValuePtr;
SQLSMALLINT m_ValueType; SQLINTEGER m_BuffLen;
mutable SQLLEN m_StrLen_or_Ind;
Param(SQLCHAR* value);
Param(char* value);
Param(SQLWCHAR* value);
Param(SQLSMALLINT& value);
Param(SQLUSMALLINT& value);
Param(SQLINTEGER& value);
Param(int& value);
Param(SQLUINTEGER& value);
Param(unsigned int& value);
Param(SQLREAL& value);
Param(SQLDOUBLE& value);
Param(SQLSCHAR& value);
Param(char& value);
Param(SQLCHAR& value);
Param(SQLBIT_TYPE& value);
Param(SQLBIGINT& value);
Param(SQLUBIGINT& value);
Param(SQLBINARY_TYPE& value);
Param(SQL_DATE_STRUCT& value);
Param(SQL_TIME_STRUCT& value);
Param(SQL_TIMESTAMP_STRUCT& value);
Param(SQL_NUMERIC_STRUCT& value);
Param(SQLGUID& value);
Param& operator = (SQLCHAR* value);
Param& operator = (char* value);
Param& operator = (SQLWCHAR* value);
Param& operator = (SQLSMALLINT& value);
Param& operator = (SQLUSMALLINT& value);
Param& operator = (SQLINTEGER& value);
Param& operator = (int& value);
Param& operator = (SQLUINTEGER& value);
Param& operator = (unsigned int& value);
Param& operator = (SQLREAL& value);
Param& operator = (SQLDOUBLE& value);
Param& operator = (SQLSCHAR& value);
Param& operator = (char& value);
Param& operator = (SQLCHAR& value);
Param& operator = (SQLBIT_TYPE& value);
Param& operator = (SQLBIGINT& value);
Param& operator = (SQLUBIGINT& value);
Param& operator = (SQLBINARY_TYPE& value);
Param& operator = (SQL_DATE_STRUCT& value);
Param& operator = (SQL_TIME_STRUCT& value);
Param& operator = (SQL_TIMESTAMP_STRUCT& value);
Param& operator = (SQL_NUMERIC_STRUCT& value);
Param& operator = (SQLGUID& value);
};
Param structure stores the pointer to the parameter value in m_ParamValuePtr member, the type of the parameter in m_ValueType member and the length of the parameter value in m_BuffLen member. m_StrLen_or_Ind member corresponds to StrLen_or_IndPtr argument in SQLBindParameter() function. Param structure has several overloaded constructors and several overloaded assignment operators for each possible C Type, supported by ODBC. One of the overloaded constructors:
Param::Param(SQLCHAR* value)
: m_ParamValuePtr(value)
, m_ValueType(SQL_C_CHAR)
, m_BuffLen(0)
, m_StrLen_or_Ind(SQL_NTS) {}
I use C++ overloading rules to automatically assign correct value to the parameter type.
The length of the parameter value cannot be determined at compile time. However, it is necessary only for strings that are not null-terminated and for binary data. I decided not to support not-null-terminated strings in the library. For binary data then I created custom helper class SQLBINARY_TYPE. The length of binary data should be put in its m_BuffLen member by the client code. This way I got rid of explicitly providing the parameter length as well.
class Statement
{
public:
Param& SetParam(const ParamIndex& param_index, const Param& param0, int BuffLen)
{
Param& param=m_Params[param_index];
param=param0;
param.m_BuffLen = BuffLen;
return param;
}
typedef std::map<ParamIndex, Param, ParamIndex::Less> PARAMS;
void ClearParams()
{
m_Params.clear();
}
const PARAMS& GetParams() const
{
return m_Params;
}
protected:
PARAMS m_Params;
};
Parameters in the Statement class are stored in a map. ParamIndex at this stage is just a parameter number. Member function SetParam() populates the parameters map. Its second argument does not need to be of Param type, but can be any C-type supported by ODBC because Param has overloaded constructors for each C type.
Member functions SetParam(), ClearParams() are not supposed to be called directly from the client code. For parameters bindings my library provides several macros. Simplified version of macro that wrap SetParam() and ClearParams() functions:
#define BEGIN_PARAMS(statement) \
\
statement.ClearParams();
#define SET_PARAM(statement, num, param) \
{ \
\
statement.SetParam(num, param, 0); \
}
#define END_PARAMS(statement) \
}
Each parameters binding sequence should start with BEGIN_PARAMS() macro. The first parameter of all macros should be the statement variable declared by DECLARE_STATEMENT() macro. Each parameter should be bound to the statement by SET_PARAM() macro. The second argument of this macro is the ordinal number of the parameter. The third argument is a parameter value. It can be any variable of C-type supported by ODBC. The order of several SET_PARAM() calls does not matter as soon as parameter numbers are correct. The binding sequence should end with the END_PARAMS() macro. I will explain its purpose in a minute.
As stated in the optional target 8) I wanted to be able to determine the situation when some parameter is missing. This was not an easy task, because parameters could be rebound and the parameters binding commands and execution commands can interleave in the client code. To address this target I made the following design decisions:
- All parameters should be bound explicitly. If the value of some parameter does not matter, it still should be bound explicitly with the default value.
- If parameters are rebound, all of them should be rebound. If some parameters should still be bound to the old values, they should be rebound explicitly to the old values.
By sticking to these 2 rules I was able to create a binding sequence scopethat allows me to check that no parameter is missing in the binding. I will explain how it works later, for now I also want to emphasize that BEGIN_PARAMS() and END_PARAMS() denote a real C++ scope. That is, BEGIN_PARAMS() opens a curly bracket and END_PARAMS() closes it.
In reality this checking is incomplete, because it does not protect from the situation when execute statement occurs before the parameters binding or when parameter binding statement is in conditional if () scope and the condition is false at runtime. It only alarms when any parameter is completely forgotten in the code.
For NULL parameters, my library provides a special type - SQLNULL_TYPE:
SET_PARAM(insert_stmt, 8, SQLNULL_TYPE())
Overloaded member function SetParam() for SQLNULL_TYPE parameter:
Param& Statement::SetParam(const ParamIndex& param_index, const SQLNULL_TYPE&, int)
{
Param& param=m_Params[param_index];
param.m_StrLen_or_Ind=SQL_NULL_DATA;
return param;
}
For default parameters I provided a special type - SQLDEFAULT_TYPE. It can be used when you don’t care about a parameter value. It will set the value of StrLen_or_IndPtr argument in SQLBindParameter() to SQL_DEFAULT_PARAM. However, you should be careful with it. The default values of database objects are stored in the database schema, but they can be retrieved only for stored procedures parameters. If for example, you have a table column with a default value of 5 and you use a parameterized SQL statement to insert value into this column, the default value of the parameter will not yield the value of 5.
Now let’s dive into parameters capture details. The most important thing about the parameter is its type. In the Param structure I used overloaded constructors for type detection. These constructors run at execution time accepting variables of C types as the arguments. Since capture code runs at startup time, I could not use the same mechanism here. Instead of Param structure, for capture purposes I used another ParamTypeInfo structure.
struct ParamTypeInfoBase
{
bool m_Null;
bool m_Default;
bool m_Output;
};
struct ParamTypeInfo : public ParamTypeInfoBase
{
SQLSMALLINT m_ValueType;
ParamTypeInfo(SQLSMALLINT ValueType=0) : m_ValueType(ValueType) {}
};
Just as Param structure, it also contains C type code in its member variable m_ValueType. However, its value is not assigned in the constructor. For the purpose of mapping of a C type to C type code I created a separate helper template structure ParamTypeInfoCreator. Here is a part of its code:
template <typename T>
struct ParamTypeInfoCreator
{ static ParamTypeInfo GetParamTypeInfo(); };
template <> struct ParamTypeInfoCreator<SQLCHAR>
{ static ParamTypeInfo GetParamTypeInfo() { return ParamTypeInfo(SQL_C_UTINYINT); } };
template <> struct ParamTypeInfoCreator<SQLCHAR*>
{ static ParamTypeInfo GetParamTypeInfo() { return ParamTypeInfo(SQL_C_CHAR); } };
template <int N> struct ParamTypeInfoCreator<SQLCHAR[N]>
{ static ParamTypeInfo GetParamTypeInfo() { return ParamTypeInfo(SQL_C_CHAR); } };
template <> struct ParamTypeInfoCreator<SQLSMALLINT>
{ static ParamTypeInfo GetParamTypeInfo() { return ParamTypeInfo(SQL_C_SSHORT); } };
ParamTypeInfoCreator uses the template specialization technique. If you are not fully familiar with it, I described it in my first article of the series – "Validation of printf() function". Note that the general template version of ParamTypeInfoCreator does not have default implementation of GetParamTypeInfo() function – all implementations are in specializations. Also, note the usage of partial specialization for pointers and arrays.
ParamTypeInfo structure derives from ParamTypeInfoBase structure. The later structure contains 3 boolean member variables: m_Null, m_Default, m_Output. They used as flags that the parameter is NULL or has a default value or is an output parameter. To set "null" and "default" flags, the special types SQLNULL_TYPE and SQLDEFAULT_TYPE should be used just as with Param structure. Specializations of ParamTypeInfoCreator for these types:
template <> struct ParamTypeInfoCreator<SQLNULL_TYPE>
{
static ParamTypeInfo GetParamTypeInfo()
{
ParamTypeInfo param_type_info;
param_type_info.m_Null=true;
return param_type_info;
}
};
template <> struct ParamTypeInfoCreator<SQLDEFAULT_TYPE>
{
static ParamTypeInfo GetParamTypeInfo()
{
ParamTypeInfo param_type_info;
param_type_info.m_Default=true;
return param_type_info;
}
};
We will talk about output parameters later.
Besides the parameter type, once again for error reporting purposes I needed the file and the line number of the code that binds the parameter to the statement. The structure ParamTypeInfoAt contains the parameter type and the both the file and the line number in a single string.
struct ParamTypeInfoAt
{
ParamTypeInfoAt(const ParamTypeInfo& param_type_info, const TCHAR* location)
: m_ParamTypeInfo(param_type_info)
, m_Location(location)
{}
ParamTypeInfo m_ParamTypeInfo;
std::basic_string<TCHAR> m_Location;
};
Here is the prototype of the ParamGlobal template class used for parameter information capture:
template <typename Placement, typename ParamTyped >
class ParamGlobal
{
public:
ParamGlobal()
{}
ParamGlobal(bool )
{
ParamTypeInfo param_type_info=ParamTypeInfoCreator<ParamTyped>::GetParamTypeInfo();
TCHAR buffer[1024];
memset(buffer, 0, sizeof(buffer));
_sntprintf(buffer, sizeof(buffer)/sizeof(TCHAR)-1, TEXT("%s(%d)"),
Placement::File(), Placement::Line());
ParamTypeInfoAt param_type_info_at(param_type_info, buffer);
}
void Register() const
{ s_Instance.Compile();
}
void Compile() const
{ }
private:
static ParamGlobal s_Instance;
};
template <typename Placement, typename ParamTyped >
ParamGlobal<Placement, ParamTyped > GetParamGlobal(const ParamTyped& )
{
return ParamGlobal<Placement, ParamTyped >();
}
template <typename Placement, typename ParamTyped >
ParamGlobal<Placement, ParamTyped >
ParamGlobal<Placement, ParamTyped >::s_Instance(true);
The first template argument for ParamGlobalclass gives its placement in the code – the file name and the line number. The second argument is a parameter type. The code that extracts the file name and the line number and combines them into a string should be already familiar. The role of the static member s_Instance and the overloaded constructor should also be clear now. The new things are Register() and Compile() functions and a global function GetParamGlobal() that is used as a factory for ParamGlobal classes. Let’s look at the extended version of SET_PARAM() macro first:
#define SET_PARAM(statement, num, param) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
ChkdSQL::GetParamGlobal<Placement>(param).Register(); \
\
statement.SetParam(num, param, 0); \
}
The Placement structure holds the file name and the line number. This structure used as a template parameter for the GetParamGlobal() template function and then in it as a template parameter for the ParamGlobal class. Now the question is – how to get the named type of the param variable? We need this type as a second template parameter of the ParamGlobal class. It would be nice if we could use the code like that:
Type T=typeof(param);
ParamGlobal<T>::SomeCall();
Unfortunately, this is not a valid C++ code. To get the type from the object, I use the template deduction technique. GetParamGlobal() template function in its simplified version has 2 template parameters. The first one is provided explicitly, while the second one is deduced by the argument type. GetParamGlobal() just returns the class object of the necessary type. Now when I have an object I can call its instance methods. In reality I don’t need to call any methods, I just want to instantiate ParamGlobal template class for these concrete parameter types and to make its s_Instance static member be compiled. Here I cannot use the explicit template instantiation as with the StatementGlobal. The reason is that explicit template instantiation should be used in the same scope as the template class declaration. In this case I declare ParamGlobal class in a global scope, but in this scope I don’t know the template parameter that I am going to use. I deduce this type only in the local scope of GetParamGlobal() function. If explicit template instantiation cannot be used, I resort to the implicit template instantiation. What I need is to call come method of ParamGlobal class and reference its s_Instance static member.
extern volatile int g_Phony;
template <typename Placement, typename ParamTyped >
class ParamGlobal
{
void Register() const
{ s_Instance.Compile();
}
void Compile() const
{ g_Phony = g_Phony ? 0 : 1;
}
};
The macro calls Register() method of ParamGlobal. Register() method references s_Instance member – calls Compile() method on it. The trick here is to make C++ compiler to compile the code that is not doing anything useful at runtime. It is not easy though. Because C++ compiler is so smart, it can easily detects that the code has zero effect at runtime and eliminate the variable assignment or the whole method call during optimization. This happening when the Release version of the code is compiled. Here I change volatile global variable g_Phony back and forth to make the Compile() method be compiled.
Now let’s move to the classes for parameters collections in binding sequence. For each binding sequence, there is a separate parameter collections class declared inside BEGIN_PARAMS() macro. This parameter collection should gather information about all parameters in the binding sequence and of course this code should execute at startup time. The collection should also "know" about the SQL statement and check that no parameters of the statement are missing. The error report of the missing parameter should point to the closing macro – END_PARAMS(). Here is the simplified version of the base class ParamsCollValidated and the derived template class ParamsCollGlobal:
class ParamsCollValidated
{
public:
ParamsCollValidated()
{}
void SetParamTypeInfoAt(const ParamIndex& param_index,
const ParamTypeInfoAt& param_type_info_at)
{
m_ParamTypeInfos.insert
(PARAM_TYPE_INFOS::value_type(param_index, param_type_info_at));
}
void SetLocation(const TCHAR* location)
{
m_Location=location;
}
protected:
ParamsCollValidated(bool)
{
GetAllParamsColls().push_back(this);
}
typedef std::multimap<ParamIndex, ParamTypeInfoAt, ParamIndex::Less> PARAM_TYPE_INFOS;
PARAM_TYPE_INFOS m_ParamTypeInfos;
std::basic_string<TCHAR> m_Location;
};
std::vector<ParamsCollValidated*>& GetAllParamsColls()
{
static std::vector<ParamsCollValidated*> AllParamsColls;
return AllParamsColls;
}
template <typename StatementTyped, typename ParamsCollTyped>
class ParamsCollGlobal : public ParamsCollValidated
{
public:
ParamsCollGlobal()
{}
static ParamsCollGlobal& Instance()
{
return s_Instance;
}
void Register() const
{ s_Instance.Compile();
}
void Compile() const
{ g_Phony = g_Phony ? 0 : 1;
}
private:
ParamsCollGlobal(bool CreateInstance)
: ParamsCollValidated(CreateInstance)
{}
static ParamsCollGlobal s_Instance;
};
template <typename StatementTyped, typename ParamsCollTyped>
ParamsCollGlobal<StatementTyped, ParamsCollTyped> ParamsCollGlobal<StatementTyped,
ParamsCollTyped>::s_Instance(true);
template <typename ParamsCollTyped, typename StatementTyped>
ParamsCollGlobal<StatementTyped, ParamsCollTyped> GetParamsCollGlobal(
const StatementTyped& )
{
return ParamsCollGlobal<StatementTyped, ParamsCollTyped>();
}
Class ParamsCollValidated stores ParamTypeInfoAt collection in m_ParamTypeInfos member of multimap type. The key of the multimap - ParamIndex can be considered as a parameter number for now. You might ask why I chose multimap and not map if the parameter number should be unique. The parameter number is unique at runtime, however 2 parameters with the same number can be bound in conditional statement like this:
if ()
SET_PARAM(stmt, 1, SomeParam)
else
SET_PARAM(stmt, 1, AnotherParam)
In this case the validation procedure would record both parameters in the collection and verify that both types are correct. m_ParamTypeInfos is populated by SetParamTypeInfoAt() member function. m_Location member stores the file name and the line number as a single string. The rest of the code should be already familiar.
The full version of BEGIN_PARAMS() macro:
#define BEGIN_PARAMS(statement) \
{ \
\
struct ParamsCollLocal\
{}; \
ChkdSQL::GetParamsCollGlobal<ParamsCollLocal>(statement).Register(); \
\
statement.ClearParams();
ParamsCollGlobal template class has 2 template parameters. The first one is the statement – the one that is declared by DECLARE_STATEMENT() macro. The second one, provided by the macro is ParamsCollLocal local structure. It is empty, because it’s needed only to compile a separate instantiation of ParamsCollGlobal template class for each binding sequence.
The full version of END_PARAMS() macro:
#define END_PARAMS(statement) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
ChkdSQL::GetParamsEndGlobal<Placement, ParamsCollLocal>(statement).Register(); \
} \
}
It uses helper class ParamsEndGlobal:
template <typename Placement, typename StatementTyped, typename ParamsCollTyped>
class ParamsEndGlobal
{
public:
ParamsEndGlobal()
{}
ParamsEndGlobal(bool )
{
TCHAR buffer[1024];
memset(buffer, 0, sizeof(buffer));
_sntprintf(buffer, sizeof(buffer)/sizeof(TCHAR)-1, TEXT("%s(%d)"), Placement::File(),
Placement::Line());
ParamsCollGlobal<StatementTyped, ParamsCollTyped>::Instance().SetLocation(buffer);
}
void Register() const
{ s_Instance.Compile();
}
void Compile() const
{ g_Phony = g_Phony ? 0 : 1;
}
private:
static ParamsEndGlobal s_Instance;
};
template <typename Placement, typename ParamsCollTyped, typename StatementTyped>
ParamsEndGlobal<Placement, StatementTyped, ParamsCollTyped> GetParamsEndGlobal(
StatementTyped& )
{
return ParamsEndGlobal<Placement, StatementTyped, ParamsCollTyped>();
}
template <typename Placement, typename StatementTyped, typename ParamsCollTyped>
ParamsEndGlobal<Placement, StatementTyped, ParamsCollTyped> ParamsEndGlobal<Placement,
StatementTyped, ParamsCollTyped>::s_Instance(true);
The sole purpose of this class is to capture the file name and the line number with the END_PARAMS() macro, convert them to a string and pass this string to the class ParamsCollGlobal class. It will be stored in the m_Location member of its base class ParamsCollValidated. As I already mentioned, it is better to report "missing parameter" error showing the end of the binding scope as the source code line. Since ParamsCollGlobal class is declared at the beginning of the binding scope, I need to assign the location of the end of the scope by this separate step.
Now let’s return to the declaration of the ParamGlobal class. There was a stubbed piece of code in it:
ParamTypeInfoAt param_type_info_at(param_type_info, buffer);
We can now replace the stub with the real code.
template <typename Placement, typename StatementTyped, typename ParamsCollTyped,
typename ParamIndexOrName, typename ParamTyped >
class ParamGlobal
{
ParamTypeInfoAt param_type_info_at(param_type_info, buffer);
ParamsCollGlobal<StatementTyped, ParamsCollTyped>::Instance().SetParamTypeInfoAt(
ParamIndexOrName::Value(), param_type_info_at);
};
As you see, class ParamGlobal has now more template arguments (and that’s not all of them yet). ParamsCollTyped argument is a local ParamsCollGlobal structure declared in BEGIN_PARAMS() macro. StatementTyped argument gives the statement type. Having these 2 template arguments, the concrete type of the collection class for this binding could be specified: ParamsCollGlobal<StatementTyped, ParamsCollTyped>.
ParamIndexOrName argument gives the parameter index or its name. Now it is time to implement binding parameters by name. In ODBC, named parameters are used for stored procedures only. There is a built-in functionality in ODBC for binding of stored procedure parameters by name. It operates on the statement’s field descriptors. I don’t use this functionality, because there is another way to query the stored procedure parameters names. Let’s put the database logic aside for a while and concentrate on the parameters names support in our classes. ParamIndex structure encapsulates either numerical index or name of the parameter.
struct ParamIndex
{
ParamIndex()
: m_Index(0)
, m_ByName(false)
{}
ParamIndex(unsigned int Index)
: m_Index(Index)
, m_ByName(false)
{}
ParamIndex(const std::basic_string<TCHAR>& Name)
: m_Index(0)
, m_ByName(true)
, m_Name(Name)
{}
unsigned int m_Index;
bool m_ByName;
std::basic_string<TCHAR> m_Name;
struct Less : public std::binary_function<ParamIndex, ParamIndex, bool>
{
bool operator ()(const ParamIndex& first, const ParamIndex& second) const;
{
if (!first.m_ByName && second.m_ByName)
{
return true;
}
else if (first.m_ByName && !second.m_ByName)
{
return false;
}
else if (!first.m_ByName && !second.m_ByName)
{
return (first.m_Index < second.m_Index);
}
else {
return (first.m_Name < second.m_Name);
}
}
};
};
ParamInfos class now should be extended to allow access parameters by number or by name.
class ParamInfos
{
void ParamInfos::Init(size_t initial_size)
{
m_VecParam.clear();
m_NamedParamsMap.clear();
m_VecParam.resize(initial_size);
}
int FindParamIndex(const ParamIndex& param_index) const
{
if (param_index.m_ByName)
{
std::map<std::basic_string<TCHAR>,
size_t>::const_iterator iter=m_NamedParamsMap.find(
param_index.m_Name);
if (iter!=m_NamedParamsMap.end())
{
return (int) iter->second;
}
else
{
return -1;
}
}
else
{ int idx=param_index.m_Index-1;
if (idx>=0 && idx<(int) m_VecParam.size())
{
return idx;
}
else
{
return -1;
}
}
}
void SetParamName(size_t idx, const std::basic_string<TCHAR>& name)
{
assert(idx<m_VecParam.size());
m_NamedParamsMap[name]=idx;
}
private:
std::vector<ParamInfo> m_VecParam;
std::map<std::basic_string<TCHAR>, size_t> m_NamedParamsMap;
};
All parameters are stored in m_VecParam vector. For parameters bound by name, m_NamedParamsMap contains mapping between parameter name and vector index. Member function FindParamIndex() retrieves parameter vector index by ordinal number or by name. Note, that ordinal parameter number is 1-based, while vector index is 0-based. Member function SetParamName() maps parameter name to its number. It is called during validation after querying stored procedure parameters information.
The extended version of SET_PARAM() macro:
#define SET_PARAM(statement, num, param) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
typedef struct \
{ \
static int Value() { return num; } \
static bool ByName() { return false; } \
} Index; \
ChkdSQL::GetParamGlobal<Placement,
ParamsCollLocal, Index >(statement, param).Register(); \
\
statement.SetParam(num, param, 0); \
}
It declares another local structure Index that wraps parameter number. For binding parameters by name there is another macro:
#define SET_PARAM_BY_NAME(statement, name, param) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
typedef struct \
{ \
static std::basic_string<TCHAR> Value() { return (name); } \
static bool ByName() { return true; } \
} Index; \
ChkdSQL::GetParamGlobal<Placement,
ParamsCollLocal, Index >(statement, param).Register(); \
\
statement.SetParam(Index::Value(), param, 0); \
}
It also declares local structure Index, but in this macro it wraps the parameter name.
Output parameters
The next task was the output parameters support. Output parameters can be used only with stored spocedures. In ODBC the support of output parameters is also belongs to conformance level #2. Just as input parameters, output parameters are bound to the SQL statement by SQLBindParameter() function. In this case argument #3 InputOutputType should indicate that parameter is output. After executing the statement the driver will write the output parameter value into the same parameter variable as used for input parameters. Another piece of information is returned in argument # 10 - StrLen_or_IndPtr. It will store the real parameter length for parameter types of variable length of the special SQL_NULL_DATA value showing that parameter is NULL.
For this extra returned information my library uses the following simple structure:
struct ParamOutputData
{
ParamOutputData()
: m_StrLen_or_Ind(0)
{}
bool IsNull() const
{
return m_StrLen_or_Ind==SQL_NULL_DATA;
}
mutable SQLLEN m_StrLen_or_Ind;
};
This structure is a part of library’s API and is intended for use in the client code. The extended version of Param structure:
struct Param
{
Param()
: m_ParamValuePtr(0)
, m_ValueType(SQL_C_SLONG)
, m_BuffLen(0)
, m_StrLen_or_Ind(0)
, m_pOut_StrLen_or_Ind(NULL)
{}
SQLPOINTER m_ParamValuePtr;
SQLSMALLINT m_ValueType; SQLINTEGER m_BuffLen;
mutable SQLLEN m_StrLen_or_Ind;
SQLLEN* m_pOut_StrLen_or_Ind;
};
While for input parameters the address of internal m_StrLen_or_Ind member is passed to SQLBindParameter() function, for the output parameters there should be passed the pointer to the external variable - m_pOut_StrLen_or_Ind. For output parameters assignment Statement class uses SetOutputParam() function.
void Statement::SetOutputParam(const ParamIndex& param_index, const Param& param0,
int BuffLen, ParamOutputData& param_output_data)
{
Param& param=SetParam(param_index, param0, BuffLen);
param_output_data.m_StrLen_or_Ind = param.m_StrLen_or_Ind;
param.m_pOut_StrLen_or_Ind=¶m_output_data.m_StrLen_or_Ind;
}
Note that if the parameter is input-output I still need to pass the correct input value for the driver in m_StrLen_or_Ind member of ParamOutputData. Also, in case of output parameters for null-terminated character strings I cannot use SQL_NTS value anymore in m_BuffLen member of Param structure. The driver should know the exact length of the buffer where it will write the output string. There is a special macro for binding output parameters:
#define SET_OUTPUT_PARAM(statement, num, param, BufLen_or_Flags, param_output_data) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
typedef struct \
{ \
static int Value() { return num; } \
static bool ByName() { return false; } \
} Index; \
ChkdSQL::GetParamGlobal<Placement, ParamsCollLocal, Index, true>(statement,
param, ChkdSQL::GetOutputParamFlags(BufLen_or_Flags)).Register(); \
\
statement.SetOutputParam(num, param, BufLen_or_Flags, param_output_data); \
}
Argument param is a variable that will store the output parameter value. Argument param_output_data should be a variable of ParamOutputData type that will store the output parameter length or NULL flag. Argument BufLen_or_Flags should be either the parameter length or for input-output parameter that is NULL on input it should be a variable of SQLNULL_TYPE type; or for input-output parameter that has a default value on input it should be a variable of SQLDEFAULT_TYPE type. Function GetOutputParamFlags() has now 2 more template arguments as well as ParamGlobal class. One extra argument – Output is of boolean type. It is a flag that the parameter is output. Another extra argument –OutputFlags is a flag that the input-output parameter is NULL or has a default value on input. This information is extracted by GetOutputParamFlags() from BufLen_or_Flags argument type by template specialization technique.
template <bool Null=false, bool Default=false>
struct OutputParamFlags
{
static const bool s_Null=Null;
static const bool s_Default=Default;
};
inline OutputParamFlags<false, false> GetOutputParamFlags(int )
{
return OutputParamFlags<false, false>();
}
inline OutputParamFlags<true, false> GetOutputParamFlags(const SQLNULL_TYPE&)
{
return OutputParamFlags<true, false>();
}
inline OutputParamFlags<false, true> GetOutputParamFlags(const SQLDEFAULT_TYPE&)
{
return OutputParamFlags<false, true>();
}
Similar macro is provided for binding output parameters by name - SET_OUTPUT_PARAM_BY_NAME(). This is it for parameters binding.
Columns
Next task was to provide columns binding support. ODBC supports binding of columns in result set to an application variable by function SQLBindCol(). This function is similar to SQLBindParameter(), but it is more simple. Argument # 2 in SQLBindParameter() is a column number, argument # 3 – TargetType, # 4 – TargetValuePtr and argument # 5 – BufferLength give column variable’s C-type, address and length accordingly. Argument # 6 StrLen_or_Ind contains column real length for columns of variable types or flag that column is NULL.
For retrieving result set’s metadata ODBC provides function SQLDescribeCol(). This function is similar to SQLDescribeParam(), but it allows to query column name as well. This is a very handy feature and I am using it for binding columns by name. SQLDescribeCol() should be called for a statement that is already prepared or executed at least once. Function SQLNumResultCols() gives the number of columns in the result set.
In my library classes for columns binding support are very similar to classes for parameters binding support. ColumnInfo structure holds column information obtained from the database by SQLDescribeCol() function.
struct ColumnInfo
{
ColumnInfo()
: m_DataType(0)
, m_ColumnSize(0)
, m_DecimalDigits(0)
, m_Nullable(0)
{}
std::basic_string<TCHAR> m_Name;
SQLSMALLINT m_DataType; SQLULEN m_ColumnSize;
SQLSMALLINT m_DecimalDigits;
SQLSMALLINT m_Nullable;
};
Here is the simplified version of ColumnInfos class that contains a collection of ColumnInfo objects and allows access to the column by number or by name:
class ColumnInfos
{
public:
void Init(size_t initial_size)
{
m_VecColumn.clear();
m_NamedColumnsMap.clear();
m_VecColumn.resize(initial_size);
}
const ColumnInfo& GetColumnInfo(size_t idx) const
{
return m_VecColumn[idx];
}
int FindParamIndex(const ParamIndex& param_index) const;
void SetColumnInfo(const ColumnInfo& column_info, size_t idx)
{
assert(idx<m_VecColumn.size());
m_VecColumn[idx]=column_info;
m_NamedColumnsMap[column_info.m_Name]=idx;
}
private:
std::vector<ColumnInfo> m_VecColumn;
std::map<std::basic_string<TCHAR>, size_t> m_NamedColumnsMap;
};
ColumnInfos collection is stored in the StatementValidated class.
class StatementValidated
{
public:
const ColumnInfos& GetColumnInfos() const
{
return m_ColumnInfos;
}
protected:
ColumnInfos m_ColumnInfos;
};
ColumnTypeInfo structure encapsulates the type of a column variable. It is obtained from C++ code and used by validation procedure.
struct ColumnTypeInfo
{
SQLSMALLINT m_ValueType;
ColumnTypeInfo(SQLSMALLINT ValueType) : m_ValueType(ValueType) {}
};
Column structure holds all necessary information for the function SQLBindCol().It is also obtained from C++ code, but used at execution time.
struct Column
{
Column()
: m_TargetValuePtr(0)
, m_ValueType(SQL_C_SLONG)
, m_BuffLen(0)
, m_pOut_StrLen_or_Ind(NULL)
{}
SQLPOINTER m_TargetValuePtr;
SQLSMALLINT m_ValueType; SQLINTEGER m_BuffLen;
SQLLEN* m_pOut_StrLen_or_Ind;
Column(SQLCHAR* value);
Column(char* value);
Column(SQLWCHAR* value);
Column(SQLSMALLINT& value);
Column(SQLUSMALLINT& value);
Column(SQLINTEGER& value);
Column(int& value);
Column(SQLUINTEGER& value);
Column(unsigned int& value);
Column(SQLREAL& value);
Column(SQLDOUBLE& value);
Column(SQLSCHAR& value);
Column(char& value);
Column(SQLCHAR& value);
Column(SQLBIT_TYPE& value);
Column(SQLBIGINT& value);
Column(SQLUBIGINT& value);
Column(SQLBINARY_TYPE& value);
Column(SQL_DATE_STRUCT& value);
Column(SQL_TIME_STRUCT& value);
Column(SQL_TIMESTAMP_STRUCT& value);
Column(SQL_NUMERIC_STRUCT& value);
Column(SQLGUID& value);
};
A collection of columns for binding is stored in the Statement class.
class Statement
{
public:
void BindColumn(const ParamIndex& column_index, const Column& column0,
ColumnOutputData& column_output_data)
{
Column& column=m_Columns[column_index];
column=column0;
column.m_pOut_StrLen_or_Ind=&column_output_data.m_StrLen_or_Ind;
}
typedef std::map<ParamIndex, Column, ParamIndex::Less> COLUMNS;
void ClearColumns()
{
m_Columns.clear();
}
const COLUMNS& GetColumns() const
{
return m_Columns;
}
protected:
COLUMNS m_Columns;
};
ColumnOutputData structure holds the value returned by SQLBindCol() function in its StrLen_or_Ind argument.
struct ColumnOutputData
{
ColumnOutputData()
: m_StrLen_or_Ind(0)
{}
bool IsNull() const
{
return m_StrLen_or_Ind==SQL_NULL_DATA;
}
mutable SQLLEN m_StrLen_or_Ind;
};
Macros for columns binding:
#define BEGIN_BIND_COLUMNS(statement) \
{ \
\
struct ColumnsCollLocal\
{}; \
ChkdSQL::GetColumnsCollGlobal<ColumnsCollLocal>(statement).Register(); \
\
statement.ClearColumns();
#define BIND_COLUMN(statement, num, var, column_output_data, buf_len) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
typedef struct \
{ \
static int Value() { return num; } \
static bool ByName() { return false; } \
} Index; \
ChkdSQL::GetColumnGlobal<Placement,
ColumnsCollLocal, Index>(statement, var).Register(); \
\
ChkdSQL::Column column(var); \
column.m_BuffLen=buf_len; \
statement.BindColumn(num, column, column_output_data); \
}
#define BIND_COLUMN_BY_NAME(statement, name, var, column_output_data, buf_len) \
{ \
\
typedef struct \
{ \
static const TCHAR* File() \
{ return TEXT(__FILE__); } \
static int Line(int line=__LINE__) \
{ return line; } \
} Placement; \
typedef struct \
{ \
static std::basic_string<TCHAR> Value() { return (name); } \
static bool ByName() { return true; } \
} Index; \
ChkdSQL::GetColumnGlobal<Placement,
ColumnsCollLocal, Index>(statement, var).Register(); \
\
ChkdSQL::Column column(var); \
column.m_BuffLen=buf_len; \
statement.BindColumn(Index::Value(), column, column_output_data); \
}
#define END_BIND_COLUMNS(statement) \
}
The columns template classes for invoking startup code are similar to the parameters ones and I don’t show them here.
Stored procedures
Before diving into the validation procedure itself, we need to do one more thing. I was planning to use SQLDescribeParam() function to retrieve parameters information from the database. However, this function is not very useful for stored procedure parameters. It cannot tell if the parameter is input or output. Neither it gives the parameters names. There is another function in ODBC – SQLProcedureColumns(). This function returns different information about the stored procedure parameters including parameter number, name, input or output type and other things. However, there is a problem with SQLProcedureColumns(). It is a catalog function that queries database schema and it is unrelated to any SQL statement. It accepts stored procedure name as an argument. If we have an SQL statement as a string that calls the stored procedure, how do we know the stored procedure name? We have to extract it from the statement string manually.
"{CALL [AdventureWorks].[dbo].[uspGetEmployeeManagers](?, ?)}"
Another problem is more subtle. Suppose we have a stored procedure Foo with 3 parameters: param1, param2 and param3. Now, suppose we have the statement:
"{CALL Foo(?, 0, ?)}"
Here, parameter #2 is hardcoded in the statement. SQLNumParams() function will return only 2 parameters for this statement and only 2 parameters could be described by SQLDescribeParam() function. Here, parameter #2 of the statement corresponds to parameter #3 in the stored procedure, but ODBC API won’t tell this to us.
To deal with this sort of problems I decided to parse stored procedure calls manually. In the validation procedure I first pre-parse the SQL statement and determine if it is a stored procedure call. If it is, I fully parse it manually. Fortunately, the stored procedure call syntax in ODBC is pretty simple and can be described by regular expressions. I chose Boost Regex library to parse regular expressions. From the stored procedure call string I extract the fully-qualified stored procedure name that contains the name itself and may also contain catalog and schema name. SP_Name structure holds fully-qualified stored procedure name.
struct SP_Name
{
std::basic_string<TCHAR> m_Catalog;
std::basic_string<TCHAR> m_Schema;
std::basic_string<TCHAR> m_Procedure;
};
I also extract all the information about parameter markers. The types for parameter markers description:
enum SP_ParamMarkerType
{
eQuestionMark, eHardCoded, eMissing };
struct SP_ParamMarker
{
SP_ParamMarker()
: m_ParamMarkerType(eQuestionMark)
{}
SP_ParamMarkerType m_ParamMarkerType;
std::basic_string<TCHAR> m_Marker;
};
typedef std::vector<SP_ParamMarker> SP_ParamMarkers;
SPCall_Struct structure contains fully parsed stored procedure call.
struct SPCall_Struct
{
SPCall_Struct()
: m_IsReturnValue(false)
{}
SP_Name m_Name;
SP_ParamMarkers m_ParamMarkers;
bool m_IsReturnValue;
};
m_IsReturnValue member tells if the stored procedure call contains return value. If we want to read the value returned from the stored procedure, we should use the following ODBC syntax:
{? = call GetCustID(?, ?)}
DetermineSPCall() function recognizes SQL statement as a stored procedure call.
int ChkdSQL::DetermineSPCall(const TCHAR* expr, std::exception* pEx)
{
try
{
boost::REGEX pattern(
TEXT("(?i)") TEXT("^") TEXT("[{]\\s*") TEXT("([?]\\s*[=]\\s*)?") TEXT("call\\s+") TEXT(".*") TEXT("[}]") TEXT("$") , boost::match_extra);
boost::CMATCH matches;
bool match=boost::regex_match(expr, matches, pattern, boost::match_extra);
if (match)
{
return 1;
}
else
{
return 0;
}
}
catch(std::exception& e)
{
if (pEx)
{
*pEx=e;
}
return -1;
}
}
Because different DBMS have different naming conventions, the regular expression pattern that parses the stored procedure call is DMBS-dependent. This should not be surprising, because my parsing mechanism to some degree extends the real ODBC driver. Here is the regular expression pattern for MS SQL ODBC driver:
extern const TCHAR* const SP_CALL_PATTERN=
TEXT("(?i)") TEXT("^") TEXT("[{]\\s*") TEXT("(?<return_value?>[?]\\s*[=]\\s*)?") TEXT("(?:call\\s+)") TEXT("((") TEXT("(??<cat_schema1_1?>[a-zA-Z_@#][a-zA-Z_0-9_@#$]*)") TEXT("|(\\[(??<cat_schema2_1?>([^\\[\\]])+)\\])") TEXT("|([\"](??<cat_schema3_1?>([^\\\"])+)[\"])") TEXT(")\\s*[.]\\s*)?") TEXT("((") TEXT("(??<cat_schema1_2?>[a-zA-Z_@#][a-zA-Z_0-9_@#$]*)") TEXT("|(\\[(??<cat_schema2_2?>([^\\[\\]])+)\\])") TEXT("|([\"](??<cat_schema3_2?>([^\\\"])+)[\"])") TEXT("|(??<cat_schema4_2?>)") TEXT(")\\s*[.]\\s*)?") TEXT("(") TEXT("(??<proc_name1?>[a-zA-Z_@#][a-zA-Z_0-9_@#$]*)") TEXT("|(\\[(??<proc_name2?>([^\\[\\]])+)\\])") TEXT("|([\"](??<proc_name3?>([^\\\"])+)[\"])") TEXT(")\\s*") TEXT("([(]\\s*(") TEXT("((??<param1?>") TEXT("[?]|") TEXT("['][^']*[']|") TEXT("[-]?((\\d+[.]?)|(\\d*[.]\\d+))([eE][-]?\\d+)?|") TEXT("") TEXT(")\\s*)?") TEXT("(,\\s*") TEXT("((??<paramN?>") TEXT("[?]|") TEXT("['][^']*[']|") TEXT("[-]?((\\d+[.]?)|(\\d*[.]\\d+))([eE][-]?\\d+)?|") TEXT("") TEXT(")\\s*)?") TEXT("\\s*)*") TEXT(")[)])?\\s*") TEXT("[}]") TEXT("$");
ParseSPCall() function performs stored procedure call parsing.
int ChkdSQL::ParseSPCall(const TCHAR* expr, SPCall_Struct& sp, std::exception* pEx)
{
try
{
boost::REGEX pattern(SP_CALL_PATTERN, boost::match_extra);
boost::CMATCH matches;
bool match=boost::regex_match(expr, matches, pattern, boost::match_extra);
if (match)
{
std::basic_string<TCHAR> s_proc_name;
std::basic_string<TCHAR> s_cat_name;
std::basic_string<TCHAR> s_schema_name;
boost::CSUB_MATCH proc_name1=matches[TEXT("proc_name1")];
boost::CSUB_MATCH proc_name2=matches[TEXT("proc_name2")];
boost::CSUB_MATCH proc_name3=matches[TEXT("proc_name3")];
if (proc_name1.matched)
{
s_proc_name=proc_name1.str();
}
else if (proc_name2.matched)
{
s_proc_name=proc_name2.str();
}
else if (proc_name3.matched)
{
s_proc_name=proc_name3.str();
}
boost::CSUB_MATCH cat_schema1_1=matches[TEXT("cat_schema1_1")];
boost::CSUB_MATCH cat_schema2_1=matches[TEXT("cat_schema2_1")];
boost::CSUB_MATCH cat_schema3_1=matches[TEXT("cat_schema3_1")];
boost::CSUB_MATCH cat_schema1_2=matches[TEXT("cat_schema1_2")];
boost::CSUB_MATCH cat_schema2_2=matches[TEXT("cat_schema2_2")];
boost::CSUB_MATCH cat_schema3_2=matches[TEXT("cat_schema3_2")];
boost::CSUB_MATCH cat_schema4_2=matches[TEXT("cat_schema4_2")];
if ((cat_schema1_1.matched || cat_schema2_1.matched || cat_schema3_1.matched) && (
cat_schema1_2.matched || cat_schema2_2.matched || cat_schema3_2.matched))
{ if (cat_schema1_1.matched)
{
s_cat_name=cat_schema1_1.str();
}
else if (cat_schema2_1.matched)
{
s_cat_name=cat_schema2_1.str();
}
else if (cat_schema3_1.matched)
{
s_cat_name=cat_schema3_1.str();
}
if (cat_schema1_2.matched)
{
s_schema_name=cat_schema1_2.str();
}
else if (cat_schema2_2.matched)
{
s_schema_name=cat_schema2_2.str();
}
else if (cat_schema3_2.matched)
{
s_schema_name=cat_schema3_2.str();
}
}
else if ((cat_schema1_1.matched || cat_schema2_1.matched ||
cat_schema3_1.matched) && (
cat_schema4_2.matched))
{ if (cat_schema1_1.matched)
{
s_cat_name=cat_schema1_1.str();
}
else if (cat_schema2_1.matched)
{
s_cat_name=cat_schema2_1.str();
}
else if (cat_schema3_1.matched)
{
s_cat_name=cat_schema3_1.str();
}
}
else if ((cat_schema1_1.matched || cat_schema2_1.matched ||
cat_schema3_1.matched) && (
!cat_schema1_2.matched && !cat_schema2_2.matched && !cat_schema3_2.matched))
{
if (THREE_PART_QUALIFIED_NAMES)
{
if (cat_schema1_1.matched)
{
s_schema_name=cat_schema1_1.str();
}
else if (cat_schema2_1.matched)
{
s_schema_name=cat_schema2_1.str();
}
else if (cat_schema3_1.matched)
{
s_schema_name=cat_schema3_1.str();
}
}
else
{
if (cat_schema1_1.matched)
{
s_cat_name=cat_schema1_1.str();
}
else if (cat_schema2_1.matched)
{
s_cat_name=cat_schema2_1.str();
}
else if (cat_schema3_1.matched)
{
s_cat_name=cat_schema3_1.str();
}
}
}
sp.m_Name.m_Procedure = s_proc_name;
sp.m_Name.m_Catalog = s_cat_name;
sp.m_Name.m_Schema = s_schema_name;
ChkdSQL::SP_ParamMarker marker;
int num_param_markers=0;
boost::CSUB_MATCH return_value=matches[TEXT("return_value")];
if (return_value.matched)
{
sp.m_IsReturnValue=true;
marker.m_ParamMarkerType=ChkdSQL::eQuestionMark;
sp.m_ParamMarkers.push_back(marker);
num_param_markers++;
}
boost::CSUB_MATCH param1=matches[TEXT("param1")];
boost::CSUB_MATCH paramN=matches[TEXT("paramN")];
if (param1.matched)
{
marker.m_Marker=param1.str();
if (marker.m_Marker==TEXT("?"))
{
marker.m_ParamMarkerType=ChkdSQL::eQuestionMark;
}
else if (marker.m_Marker==TEXT(""))
{
marker.m_ParamMarkerType=ChkdSQL::eMissing;
}
else
{
marker.m_ParamMarkerType=ChkdSQL::eHardCoded;
}
sp.m_ParamMarkers.push_back(marker);
num_param_markers++;
if (paramN.matched)
{
for (boost::CSUB_MATCH::capture_sequence_type::const_iterator iter =
paramN.captures().begin(); iter!=paramN.captures().end(); iter++)
{
marker.m_Marker=iter->str();
if (marker.m_Marker==TEXT("?"))
{
marker.m_ParamMarkerType=ChkdSQL::eQuestionMark;
}
else if (marker.m_Marker==TEXT(""))
{
marker.m_ParamMarkerType=ChkdSQL::eMissing;
}
else
{
marker.m_ParamMarkerType=ChkdSQL::eHardCoded;
}
sp.m_ParamMarkers.push_back(marker);
num_param_markers++;
}
}
}
return 1;
}
else
{
return 0;
}
}
catch(std::exception& e)
{
if (pEx)
{
*pEx=e;
}
return -1;
}
}
To make this code work, the Boost version should be at least 1.41. Also, Boost Regex library should be built with the following preprocessor symbol defined:
#define BOOST_REGEX_MATCH_EXTRA
The easiest way to define this symbol is to uncomment it in the file: regex\user.hpp.
The only parameter placeholder that creates a real dynamic parameter is a question-mark. Now I can easily map dynamic parameters in the SQL statement to the stored-procedure parameters and get extra information about parameters by SQLDescribeParam() function.
struct SPColumnOrParamInfo
{
std::basic_string<TCHAR> m_ColumnOrParamName;
SQLSMALLINT m_ColumnOrParamType;
SPColumnOrParamInfo()
: m_ColumnOrParamType(SQL_PARAM_TYPE_UNKNOWN)
{}
};
typedef std::vector<SPColumnOrParamInfo> SPColumnOrParamInfos;
int ChkdSQL::GetProcedureColumns(HDBC hdbc, const std::basic_string<TCHAR>& catalog,
const std::basic_string<TCHAR>& schema, const std::basic_string<TCHAR>& procedure,
SPColumnOrParamInfos& infos)
{
TCHAR Catalog[256]={0};
TCHAR Schema[256]={0};
TCHAR ProcedureName[256]={0};
int ret=1;
RETCODE retcode=SQL_SUCCESS;
HSTMT hstmt=0;
_tcsncpy(Catalog, catalog.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
_tcsncpy(Schema, schema.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
_tcsncpy(ProcedureName, procedure.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
SQLAllocStmt(hdbc, &hstmt);
retcode = SQLProcedureColumns(hstmt, (_TUCHAR*)Catalog, SQL_NTS, (_TUCHAR*)Schema,
SQL_NTS, (_TUCHAR*)ProcedureName, SQL_NTS, NULL, 0);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
ret = -1;
goto END;
}
TCHAR ColumnOrParamName[256]={0};
SQLSMALLINT ColumnOrParamType=SQL_PARAM_TYPE_UNKNOWN;
SQLSMALLINT Nullable=SQL_NULLABLE_UNKNOWN;
SQLSMALLINT SQLDataType=0;
ChkdSQL::Param params[2];
params[0]=ColumnOrParamName;
params[0].m_BuffLen=sizeof(ColumnOrParamName);
params[1]=ColumnOrParamType;
SQLSMALLINT param_cols[4];
param_cols[0]=4;
param_cols[1]=5;
infos.clear();
for (int i=0; i<2; i++)
{
retcode = SQLBindCol(hstmt, param_cols[i], params[i].m_ValueType,
params[i].m_ParamValuePtr, params[i].m_BuffLen, ¶ms[i].m_StrLen_or_Ind);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
ret = -1;
goto END;
}
}
while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
SPColumnOrParamInfo info;
if (params[0].m_StrLen_or_Ind!=SQL_NULL_DATA)
{
info.m_ColumnOrParamName = ColumnOrParamName;
}
if (params[1].m_StrLen_or_Ind!=SQL_NULL_DATA)
{
info.m_ColumnOrParamType = ColumnOrParamType;
}
infos.push_back(info);
}
if (retcode != SQL_NO_DATA)
{
ret = -1;
goto END;
}
END:
if (hstmt)
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
return ret;
}
StatementValidated class now contains the flag that tells if the statement is a stored procedure call and the stored procedure call structure.
class StatementValidated
{
bool m_IsSP;
SPCall_Struct m_SpStruct;
};
Giving ODBC driver a little help
As I already mentioned that main SQL statement validation workhorse supposed to be SQLPrepare() function. The main idea for the validation procedure was to call this function for each SQL statement and it would fail for the wrong statement and report the error. However this behavior is driver-dependent and unfortunately it does not work ideally even for the very "respectful" DBMS.
I expected that SQLPrepare() function will check if the stored procedure in the call exists in the database, but discovered that often this is not the case. So I decided to do the job myself. IsProcedureExists() function checks if the stored exists in the database.
int ChkdSQL::IsProcedureExists(HDBC hdbc, std::basic_string<TCHAR>& catalog,
std::basic_string<TCHAR>& schema, const std::basic_string<TCHAR>& procedure)
{
TCHAR Catalog[256]={0};
TCHAR Schema[256]={0};
TCHAR ProcedureName[256]={0};
int ret=0;
RETCODE retcode=SQL_SUCCESS;
HSTMT hstmt=0;
if (catalog==TEXT(""))
{
ret=GetDefaultCatalog(hdbc, catalog);
if (ret<0)
{
goto END;
}
}
_tcsncpy(Catalog, catalog.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
_tcsncpy(Schema, schema.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
_tcsncpy(ProcedureName, procedure.c_str(), sizeof(Catalog)/sizeof(TCHAR)-1);
SQLAllocStmt(hdbc, &hstmt);
retcode = SQLProcedures(hstmt, (_TUCHAR*)Catalog, SQL_NTS, (_TUCHAR*)Schema, SQL_NTS,
(_TUCHAR*)ProcedureName, SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
ret = -1;
goto END;
}
ChkdSQL::Param params[3];
params[0]=Catalog;
params[1]=Schema;
params[2]=ProcedureName;
for (int i=0; i<3; i++)
{
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)(i+1), params[i].m_ValueType,
params[i].m_ParamValuePtr, params[i].m_BuffLen, ¶ms[i].m_StrLen_or_Ind);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
ret = -1;
goto END;
}
}
retcode = SQLFetch(hstmt);
if ( (retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO) )
{
ret = 1;
goto END;
}
else if (retcode == SQL_NO_DATA)
{
ret = 0;
goto END;
}
else
{
ret = -1;
goto END;
}
END:
if (hstmt)
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
return ret;
}
Here is another example of the limited capabilities of SQLPrepare(). The following statement is obviously not a valid SQL statement.
SQLPrepare(hstmt, "GARBAGE", SQL_NTS);
However, all drivers that I tested do not give an error for this statement. Only when trying to execute the statement with SQLExecute() function it fails. To address this issue, I created a very simple parser of non-stored procedure statements only to see if they at least start with the valid SQL keyword. This is done by IsNonSPStmt() function.
int ChkdSQL::IsNonSPStmt(const TCHAR* expr, std::exception* pEx)
{
try
{
boost::REGEX pattern(
TEXT("(?i)") TEXT("^") TEXT("\\s*") TEXT("(select|insert|update|delete)\\s+") TEXT(".*") TEXT("$") , boost::match_extra);
boost::CMATCH matches;
bool match=boost::regex_match(expr, matches, pattern, boost::match_extra);
if (match)
{
return 1;
}
else
{
return 0;
}
}
catch(std::exception& e)
{
if (pEx)
{
*pEx=e;
}
return -1;
}
}
For simplicity it assumes that the only DML statements – SELECT, INSERT, UPDATE, DELETE can be used in the client code. If this is not the case, the regular expression should be extended.
The validation procedure
Finally I can present the validation procedure itself.
bool StatementValidated::Validate
(HDBC hdbc, const std::basic_string<TCHAR>& default_schema)
{
TCHAR buf_err[1024];
memset(buf_err, 0, sizeof(buf_err));
RETCODE retcode=SQL_SUCCESS;
TCHAR* text=NULL;
HSTMT hstmt=0;
int parse_res=0;
std::exception ex;
parse_res=DetermineSPCall(m_Text.c_str(), &ex);
if (parse_res<0)
{
goto FAILED;
}
if (parse_res==1)
{
m_IsSP=true;
parse_res=ParseSPCall(m_Text.c_str(), m_SpStruct, &ex);
if (parse_res<0)
{
goto FAILED;
}
else if (parse_res==0)
{
goto FAILED;
}
}
else
{
parse_res=IsNonSPStmt(m_Text.c_str(), &ex);
if (parse_res<0)
{
goto FAILED;
}
else if (parse_res==0)
{
goto FAILED;
}
}
bool ret=true;
SQLAllocStmt(hdbc, &hstmt);
SQLSetStmtAttr(hstmt, SQL_SOPT_SS_DEFER_PREPARE, SQL_DP_OFF, SQL_IS_UINTEGER);
text=_tcsdup(m_Text.c_str());
retcode=SQLPrepare(hstmt, (_TUCHAR*)text, SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
goto FAILED;
}
SQLSMALLINT num_params;
retcode=SQLNumParams(hstmt, &num_params);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
goto FAILED;
}
m_ParamInfos.Init(num_params);
for (SQLUSMALLINT i=1; i<=num_params; i++)
{
SQLUSMALLINT vi=i-1;
ParamInfo& param_info=m_ParamInfos.GetParamInfo(vi);
retcode=SQLDescribeParam(hstmt, i,
¶m_info.m_DataType, ¶m_info.m_ParameterSize,
¶m_info.m_DecimalDigits, ¶m_info.m_Nullable);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) break;
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
goto FAILED;
}
if (m_IsSP)
{
if (m_SpStruct.m_Name.m_Schema==TEXT(""))
{
m_SpStruct.m_Name.m_Schema=default_schema;
}
int chk_proc=IsProcedureExists(hdbc, m_SpStruct.m_Name.m_Catalog,
m_SpStruct.m_Name.m_Schema, m_SpStruct.m_Name.m_Procedure);
if (chk_proc<0)
{
goto FAILED;
}
if (chk_proc==0)
{
goto FAILED;
}
SPColumnOrParamInfos ColumnOrParamInfos;
int ret=GetProcedureColumns(hdbc, m_SpStruct.m_Name.m_Catalog,
m_SpStruct.m_Name.m_Schema, m_SpStruct.m_Name.m_Procedure, ColumnOrParamInfos);
if (ret<0)
{
goto FAILED;
}
if (SP_PARAMS_VALIDATE)
{
std::basic_string<TCHAR> error;
ret=ValidateSPParamInfos(ColumnOrParamInfos, error);
if (ret!=1)
{
"\n%s : error : Stored procedure %s parameters validation failed: %s\n",
m_Location.c_str(), m_SpStruct.m_Name.m_Procedure.c_str(), error.c_str());
goto FAILED;
}
}
}
if (!m_IsSP)
{
SQLSMALLINT num_columns;
retcode=SQLNumResultCols(hstmt, &num_columns);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
goto FAILED;
}
m_ColumnInfos.Init(num_columns);
for (SQLUSMALLINT i=1; i<=num_columns; i++)
{
SQLUSMALLINT vi=i-1;
ColumnInfo column_info;
TCHAR ColumnName[256]={0};
SQLSMALLINT NameLength=0;
retcode=SQLDescribeCol(hstmt, i, (_TUCHAR*)ColumnName, sizeof(ColumnName),
&NameLength, &column_info.m_DataType, &column_info.m_ColumnSize,
&column_info.m_DecimalDigits, &column_info.m_Nullable);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) break;
column_info.m_Name=ColumnName;
m_ColumnInfos.SetColumnInfo(column_info, vi);
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
goto FAILED;
}
}
goto END;
FAILED:
ret=false;
goto END;
END:
if (hstmt)
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
if (text)
{
free(text);
}
return ret;
}
In this snippet, I use pseudo-function ErrorReport(). The real code when running under Visual Studio will report the error in the Output window and if the user clicks on the error line, Visual Studio editor will open the file with the source code and position the cursor on the line that contains this error.
As you can see, SQL statements for the stored are processed somewhat differently than the normal statements. One of the differences is that the columns in the result set for the stored procedures are not analyzed. The reason is that I haven’t found the way to do it. For the ODBC drivers that I tested,SQLNumResultCols() function returns 0 for the SQL statement that calls stored procedure if this statement has not been yet executed. Also, SQLProcedureColumns() does not return any information about columns - only about parameters. That means that for the stored procedures returning result set, columns binding cannot be validated at startup time.
For non-stored procedure statements the validation procedure besides validation itself, also assigns names to columns stored in m_ColumnInfos member of StatementValidated class. This is absolutely necessary for binding columns by names feature. For the stored procedure columns it will be done at runtime after a statement is executed. Any errors in columns binding will be reported as runtime errors in this case.
The stored procedure parameters are validated by ValidateSPParamInfos() function.
int StatementValidated::ValidateSPParamInfos(const SPColumnOrParamInfos& sp_param_infos,
std::basic_string<TCHAR>& error)
{
TCHAR buf_err[1024];
SP_ParamMarkers& param_markers=m_SpStruct.m_ParamMarkers;
size_t param_infos_size=m_ParamInfos.GetSize();
size_t param_markers_size=param_markers.size();
std::vector<int> param_st_map;
param_st_map.resize(param_markers_size);
size_t num_sp_qm_markers_params=0;
for (size_t i=0; i<param_markers_size; i++)
{
int qm_param_idx=-1;
const SP_ParamMarker& mk=param_markers[i];
if (mk.m_ParamMarkerType==eQuestionMark)
{
qm_param_idx=(int) num_sp_qm_markers_params++;
}
param_st_map[i]=qm_param_idx;
}
if (param_infos_size!=num_sp_qm_markers_params)
{
_sntprintf(buf_err, sizeof(buf_err)/sizeof(TCHAR)-1, TEXT(
"Number of ? parameter markers - %d does not match number of ODBC parameters - %d"),
num_sp_qm_markers_params, param_infos_size);
error=buf_err;
return -1;
}
bool IsReturnValue=false;
size_t num_sp_params=0;
size_t marker_idx=0;
for (size_t j=0; j<sp_param_infos.size(); j++)
{
const SPColumnOrParamInfo& sp_param_info=sp_param_infos[j];
if (sp_param_info.m_ColumnOrParamType==SQL_PARAM_INPUT ||
sp_param_info.m_ColumnOrParamType==SQL_PARAM_INPUT_OUTPUT ||
sp_param_info.m_ColumnOrParamType==SQL_PARAM_OUTPUT ||
sp_param_info.m_ColumnOrParamType==SQL_RETURN_VALUE)
{
if (sp_param_info.m_ColumnOrParamType==SQL_RETURN_VALUE)
{
IsReturnValue=true;
if (num_sp_params>0)
{ error=TEXT(
"RETURN_VALUE should always be the
1-st parameter in SP parameters collection");
return -1;
}
}
if ( !(sp_param_info.m_ColumnOrParamType==SQL_RETURN_VALUE &&
!m_SpStruct.m_IsReturnValue) )
{ if (marker_idx<param_markers_size)
{ int qm_param_idx=param_st_map[marker_idx];
if (qm_param_idx>=0)
{ SQLSMALLINT InputOutputType=sp_param_info.m_ColumnOrParamType;
if (InputOutputType==SQL_RETURN_VALUE)
{
InputOutputType=SQL_PARAM_OUTPUT;
}
m_ParamInfos.GetParamInfo(qm_param_idx).m_InputOutputType=InputOutputType;
m_ParamInfos.SetParamName(qm_param_idx, sp_param_info.m_ColumnOrParamName);
}
marker_idx++;
}
}
num_sp_params++;
}
}
if (param_markers_size>num_sp_params)
{
_sntprintf(buf_err, sizeof(buf_err)/sizeof(TCHAR)-1, TEXT(
"Number of parameter markers - %d is greater than number of SP parameters - %d"),
param_markers_size, num_sp_params);
error=buf_err;
return 0;
}
if (!IsReturnValue && m_SpStruct.m_IsReturnValue)
{ error=TEXT("There is a return value marker in SP call, but no RETURN_VALUE" +
"parameter found in SP parameters");
return 0;
}
if (IsReturnValue && !m_SpStruct.m_IsReturnValue && param_markers_size==num_sp_params)
{ _sntprintf(buf_err, sizeof(buf_err)/sizeof(TCHAR)-1, TEXT(
"Number of parameter markers - %d is equal to the number of SP parameters, but no" +
"return value marker is found in SP call"), param_markers_size);
error=buf_err;
return 0;
}
return 1;
}
Besides different checks this function does a very important thing – it assigns names and input or output types to the parameters stored in the m_ParamInfos collection of StatementValidated class.
As I already mentioned, each parameter in ODBC is described by 2 type codes: SQL Data Type and C Data Type>. When executing an SQL statement, driver converts the parameter value from client-side representation to database-side representation and vice versa. There are compatibility rules in ODBC that defines which C types are compatible with which SQL types. To enforce these rules during my validation procedure, I created the following collection:
CompatibleTypes CompatibleTypesMap[]=
{
{SQL_CHAR, SQL_C_CHAR},
{SQL_VARCHAR, SQL_C_CHAR},
{SQL_LONGVARCHAR, SQL_C_CHAR},
{SQL_WCHAR, SQL_C_WCHAR},
{SQL_WVARCHAR, SQL_C_WCHAR},
{SQL_WLONGVARCHAR, SQL_C_WCHAR},
{SQL_NUMERIC, SQL_C_NUMERIC},
{SQL_NUMERIC, SQL_C_FLOAT},
{SQL_NUMERIC, SQL_C_DOUBLE},
{SQL_DECIMAL, SQL_C_NUMERIC},
{SQL_DECIMAL, SQL_C_FLOAT},
{SQL_DECIMAL, SQL_C_DOUBLE},
{SQL_BIT, SQL_C_BIT},
{SQL_TINYINT, SQL_C_STINYINT},
{SQL_TINYINT, SQL_C_UTINYINT},
{SQL_SMALLINT, SQL_C_SSHORT},
{SQL_SMALLINT, SQL_C_USHORT},
{SQL_INTEGER, SQL_C_SLONG},
{SQL_INTEGER, SQL_C_ULONG},
{SQL_BIGINT, SQL_C_SBIGINT},
{SQL_BIGINT, SQL_C_UBIGINT},
{SQL_REAL, SQL_C_FLOAT},
{SQL_FLOAT, SQL_C_DOUBLE},
{SQL_DOUBLE, SQL_C_DOUBLE},
{SQL_BINARY, SQL_C_BINARY},
{SQL_VARBINARY, SQL_C_BINARY},
{SQL_VARBINARY, SQL_C_BINARY},
{SQL_TYPE_DATE, SQL_C_TYPE_DATE},
{SQL_TYPE_TIME, SQL_C_TYPE_TIME},
{SQL_TYPE_TIMESTAMP, SQL_C_TYPE_TIMESTAMP},
{SQL_GUID, SQL_C_GUID}
};
However, I use much more restrictive rules that ODBC. For example, ODBC can convert SQL_DOUBLE to SQL_C_LONG type. At runtime it might result in a precision lost and I don’t want this. If I need to do a conversion, I prefer to do it explicitly in my C++ code. However, I allow QDBC driver to do a tedious job of converting of SQL_NUMERIC type into SQL_C_DOUBLE and SQL_C_FLOAT. If you don’t like my approach, you can extend the CompatibleTypesMap collection. Parameters binding validation is performed by ParamsCollValidated::Validate() function.
bool ParamsCollValidated::Validate()
{
TCHAR buf_err[1024];
memset(buf_err, 0, sizeof(buf_err));
bool ret=true;
size_t num_param_infos=GetParamInfos().GetSize();
int cur_param_num=0;
std::vector<bool> mask(num_param_infos, false);
for (PARAM_TYPE_INFOS::const_iterator iter=m_ParamTypeInfos.begin();
iter!=m_ParamTypeInfos.end(); iter++)
{
const ParamIndex& param_index=iter->first;
const ParamTypeInfoAt& param_type_info_at=iter->second;
cur_param_num=GetParamInfos().FindParamIndex(param_index);
if (cur_param_num<0)
{
if (!param_index.m_ByName)
{
}
else
{
if (!SP_PARAMS_VALIDATE)
{
}
else if (!GetIsSP())
{
}
else
{
}
}
return false;
}
if (!SP_OUTPUT_PARAMS)
{
if (param_type_info_at.m_ParamTypeInfo.m_Output)
{
return false;
}
}
mask[cur_param_num]=true;
if (PARAMS_TYPES_VALIDATE)
{
const ParamInfo& param_info=GetParamInfos().GetParamInfo(cur_param_num);
const ParamTypeInfo& param_type_info=param_type_info_at.m_ParamTypeInfo;
if (param_type_info.m_Default)
{
if (param_info.m_InputOutputType==SQL_PARAM_OUTPUT)
{
return false;
}
}
else if (param_type_info.m_Null)
{
if (!param_info.m_Nullable)
{
return false;
}
}
else
{
if (!IsTypesCompatible(param_info.m_DataType, param_type_info.m_ValueType))
{
return false;
}
}
if (param_type_info.m_Output &&
param_info.m_InputOutputType!=SQL_PARAM_INPUT_OUTPUT &&
param_info.m_InputOutputType!=SQL_PARAM_OUTPUT &&
param_info.m_InputOutputType!=SQL_RETURN_VALUE)
{
return false;
}
}
}
for (size_t idx=0; idx<num_param_infos; idx++)
{
if (!mask[idx])
{
return false;
}
}
return ret;
}
Columns binding validation is performed by ColumnsCollValidated::Validate() member function.
bool ColumnsCollValidated::Validate()
{
if (GetIsSP())
{ return true;
}
TCHAR buf_err[1024];
memset(buf_err, 0, sizeof(buf_err));
bool ret=true;
int cur_column_num=0;
for (COLUMN_TYPE_INFOS::const_iterator iter=m_ColumnTypeInfos.begin();
iter!=m_ColumnTypeInfos.end(); iter++)
{
const ParamIndex& column_index=iter->first;
const ColumnTypeInfoAt& column_type_info_at=iter->second;
cur_column_num=GetColumnInfos().FindParamIndex(column_index);
if (cur_column_num<0)
{
if (!column_index.m_ByName)
{
}
else
{
}
return false;
}
const ColumnInfo& column_info=GetColumnInfos().GetColumnInfo(cur_column_num);
const ColumnTypeInfo& column_type_info=column_type_info_at.m_ColumnTypeInfo;
if (!IsTypesCompatible(column_info.m_DataType, column_type_info.m_ValueType))
{
return false;
}
}
return ret;
}
The whole validation procedure is called by StatementValidated ::ValidateAll() static member function.
bool StatementValidated::ValidateAll(HDBC hdbc,
const std::basic_string<TCHAR>& default_schema)
{
std::vector<StatementValidated*>& AllStatements=GetAllStatements();
bool Ok=true;
for (size_t i=0; i<AllStatements.size(); i++)
{
StatementValidated* CurStatement=AllStatements[i];
if (!CurStatement->Validate(hdbc, default_schema))
{
Ok=false;
}
}
return Ok;
}
The first parameter of this function is ODBCconnection handle. The second parameter is a default schema. This parameter is used for those DBMS that use database object names consisting of 3 parts: catalog name, schema name and object name. Catalog name and schema name can be omitted in the SQL statement, in this case their default values are used. While it is possible to query the default catalog name via ODBC SQLGetConnectAttr() function, I didn’t find the easy way to query the default schema. That is why the default schema is passed as a parameter. Of course, the connection provided to the validation procedure should be to the same database that is used in the runtime code. If application is connecting to different databases, this approach will not work in the given form and should be changed.
The validation procedure in general is DBMS dependent. While the code itself is common for all DBMS, different features of it can be affected by several settings.
extern const bool PARAMS_VALIDATE;
extern const bool PARAMS_TYPES_VALIDATE;
extern const bool SP_PARAMS_VALIDATE;
extern const bool SP_OUTPUT_PARAMS;
extern const bool THREE_PART_QUALIFIED_NAMES;
extern const TCHAR* const SP_CALL_PATTERN;
These settings reflect different behaviors for different DBMS and ODBC drivers. Setting PARAMS_VALIDATE defines if parameter validation makes sense for this driver at all. Setting PARAMS_TYPES_VALIDATE defines if parameter types should be validated. Setting SP_PARAMS_VALIDATE defines if the stored procedure parameters should be validated. Setting SP_OUTPUT_PARAMS defines if the driver supports output parameters for stored procedures. Setting THREE_PART_QUALIFIED_NAMES defines if the DBMS supports three-part fully qualified names of the database objects or only two-part names. Setting SP_CALL_PATTERN defines regex pattern for the stored procedure calls parsing.
Runtime Classes
We are finished with validation. Now I will describe the classes that the library uses at execution time. Besides already described Statement and macro for parameters and columns there are only 2 wrapper classes used. The first one is Connection.
class Connection
{
public:
Connection(const TCHAR* connecttion_string,
const std::basic_string<TCHAR>& DefaultSchema=TEXT(""))
: m_Henv(0)
, m_Hdbc(0)
, m_DefaultSchema(DefaultSchema)
{
if (connecttion_string)
{
const TCHAR* connecttion_string_end=connecttion_string+_tcslen(connecttion_string);
m_ConnectionString.assign(connecttion_string, connecttion_string_end);
}
m_ConnectionString.push_back(TEXT('\0'));
}
~Connection()
{
Disconnect();
}
int Connect()
{
RETCODE retcode=SQL_SUCCESS;
SQLTCHAR szOutConn[1024];
SQLSMALLINT nOutConn=0;
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &m_Henv);
if( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS))
{
return(-1);
}
retcode = SQLSetEnvAttr(m_Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER);
if( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS))
{
return(-1);
}
retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_Henv, &m_Hdbc);
if( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS))
{
return(-1);
}
retcode=SQLDriverConnect(m_Hdbc, NULL, (_TUCHAR*)GetConnectionString(), SQL_NTS
,szOutConn, 1024, &nOutConn, SQL_DRIVER_NOPROMPT);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return(-1);
}
return retcode;
}
void Disconnect()
{
if (m_Hdbc)
{
SQLDisconnect(m_Hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, m_Hdbc);
m_Hdbc=0;
}
if (m_Henv)
{
SQLFreeHandle(SQL_HANDLE_ENV, m_Henv);
m_Henv=0;
}
}
HDBC GetHDBC() const
{
return m_Hdbc;
}
const std::basic_string<TCHAR>& GetDefaultSchema() const
{
return m_DefaultSchema;
}
TCHAR* GetConnectionString()
{
return &m_ConnectionString[0];
}
private:
std::vector<TCHAR> m_ConnectionString;
HENV m_Henv;
HDBC m_Hdbc;
std::basic_string<TCHAR> m_DefaultSchema;
};
The second class is Command.
class Command
{
public:
Command(Statement& stmt)
: m_Hdbc(0)
, m_Hstmt(0)
, m_Stmt(stmt)
, m_Prepared(false)
, m_ParamsBound(false)
{}
~Command()
{
Close();
}
void Open(const Connection& conn)
{
m_Hdbc=conn.GetHDBC();
SQLAllocStmt(m_Hdbc, &m_Hstmt);
}
void Close()
{
if (m_Hstmt)
{
SQLFreeHandle(SQL_HANDLE_STMT, m_Hstmt);
m_Hstmt=0;
}
}
RETCODE Prepare()
{
RETCODE retcode=SQL_SUCCESS;
retcode=SQLPrepare(m_Hstmt, (_TUCHAR*)m_Stmt.GetText(), SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
m_Prepared=true;
return retcode;
}
RETCODE Execute()
{
RETCODE retcode=SQL_SUCCESS;
if (!m_Prepared)
{
retcode=Prepare();
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) return retcode;
}
retcode=BindParameters();
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
retcode=SQLExecute(m_Hstmt);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
retcode=BindColumns();
return retcode;
}
RETCODE ExecuteDirect()
{
RETCODE retcode=SQL_SUCCESS;
retcode=BindParameters();
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
retcode=SQLExecDirect(m_Hstmt, (_TUCHAR*)m_Stmt.GetText(), SQL_NTS);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
retcode=BindColumns();
return retcode;
}
RETCODE Fetch()
{
RETCODE retcode = SQLFetch(m_Hstmt);
if (retcode != SQL_SUCCESS &&
retcode != SQL_SUCCESS_WITH_INFO && retcode != SQL_NO_DATA)
{
}
return retcode;
}
RETCODE CloseCursor()
{
RETCODE retcode=SQLCloseCursor(m_Hstmt);
if (retcode != SQL_SUCCESS &&
retcode != SQL_SUCCESS_WITH_INFO && retcode != SQL_NO_DATA)
{
}
return retcode;
}
void ResetParameters()
{
m_ParamsBound=false;
}
private:
RETCODE BindParameters()
{
if (m_ParamsBound)
{
return SQL_SUCCESS;
}
RETCODE retcode=SQL_SUCCESS;
for (ChkdSQL::Statement::PARAMS::const_iterator iter=m_Stmt.GetParams().begin();
iter!=m_Stmt.GetParams().end(); iter++)
{
const ChkdSQL::ParamIndex& param_index=iter->first;
const ChkdSQL::Param& param=iter->second;
const ChkdSQL::ParamInfos&
param_infos=m_Stmt.GetStatementValidated().GetParamInfos();
const ParamInfo* pparam_info=NULL;
int i=param_infos.GetParamInfo(param_index, pparam_info);
if (i<0)
{ TCHAR buf_err[1024];
if (!param_index.m_ByName)
{
}
else
{
}
return -1;
}
i++;
if (pparam_info->m_InputOutputType==SQL_PARAM_INPUT_OUTPUT ||
pparam_info->m_InputOutputType==SQL_PARAM_OUTPUT)
{
retcode=SQLBindParameter(m_Hstmt,
(SQLUSMALLINT)i, pparam_info-> m_InputOutputType,
param.m_ValueType, pparam_info->m_DataType, pparam_info-> m_ParameterSize,
pparam_info->m_DecimalDigits, param.m_ParamValuePtr, param.m_BuffLen,
param.m_pOut_StrLen_or_Ind);
}
else
{
retcode=SQLBindParameter(m_Hstmt,
(SQLUSMALLINT)i, pparam_info-> m_InputOutputType,
param.m_ValueType, pparam_info->m_DataType, pparam_info-> m_ParameterSize,
pparam_info->m_DecimalDigits, param.m_ParamValuePtr, param.m_BuffLen,
¶m.m_StrLen_or_Ind);
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
break;
}
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
ProcessLogMessages(SQL_HANDLE_STMT, m_Hstmt);
}
else
{
m_ParamsBound=true;
}
return retcode;
}
RETCODE BindColumns()
{
RETCODE retcode=SQL_SUCCESS;
if (m_Stmt.GetColumns().size()>0)
{
const ChkdSQL::ColumnInfos* pcolumn_infos=NULL;
ChkdSQL::ColumnInfos column_infos;
if (m_Stmt.GetStatementValidated().GetIsSP())
{ pcolumn_infos=&column_infos;
SQLSMALLINT num_columns;
retcode=SQLNumResultCols(m_Hstmt, &num_columns);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
column_infos.Init(num_columns);
for (SQLUSMALLINT i=1; i<=num_columns; i++)
{
SQLUSMALLINT vi=i-1;
ColumnInfo column_info;
TCHAR ColumnName[256]={0};
SQLSMALLINT NameLength=0;
retcode=SQLDescribeCol(m_Hstmt, i, (_TUCHAR*)ColumnName, sizeof(ColumnName),
&NameLength, &column_info.m_DataType, &column_info.m_ColumnSize,
&column_info.m_DecimalDigits, &column_info.m_Nullable);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) break;
column_info.m_Name=ColumnName;
column_infos.SetColumnInfo(column_info, vi);
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
return retcode;
}
}
else
{
pcolumn_infos=&m_Stmt.GetStatementValidated().GetColumnInfos();
}
for (ChkdSQL::Statement::COLUMNS::const_iterator iter=m_Stmt.GetColumns().begin();
iter!=m_Stmt.GetColumns().end(); iter++)
{
const ChkdSQL::ParamIndex& column_index=iter->first;
const ChkdSQL::Column& column=iter->second;
int i=-1;
if (m_Stmt.GetStatementValidated().GetIsSP())
{ const ColumnInfo* pcolumn_info=NULL;
i=pcolumn_infos->GetColumnInfo(column_index, pcolumn_info);
if (i>=0)
{
if (!IsTypesCompatible(pcolumn_info->m_DataType, column.m_ValueType))
{
return -1;
}
}
}
else
{
i=pcolumn_infos->FindParamIndex(column_index);
}
if (i<0)
{
if (!column_index.m_ByName)
{
}
else
{
}
return -1;
}
i++;
retcode=SQLBindCol(m_Hstmt, (SQLUSMALLINT)i, column.m_ValueType,
column.m_TargetValuePtr, column.m_BuffLen, column.m_pOut_StrLen_or_Ind);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
break;
}
}
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
}
}
return retcode;
}
Statement& m_Stmt;
HDBC m_Hdbc;
HSTMT m_Hstmt;
bool m_Prepared;
bool m_ParamsBound;
private:
Command(const Command&);
Command& operator = (const Command&);
};
In the Command class’ member functions Prepare() and Execute() used for prepared execution. It is not necessary to always callPrepare() before Execute() though. If the statement has not been prepared yet, Prepare() will be called automatically. Function ExecuteDirect() is used for direct execution. Function Fetch() is used for fetching rows from the result set. After result set was processed and if command will be used further, a client should call CloseCursor() function. Function ResetParameters() should be called if parameters are rebound to the SQL statement. Another moment – as I mentioned before, for the stored procedure columns bindings are validated not at startup time, but at runtime. This is done inside protected member function BindColumns().
Using the library
Pseudo-code in the snippet below illustrates the usage of Connection and Command classes as well as macros for statements and parameters.
Connection conn(connection_string);
conn.Connect();
DECLARE_STATEMENT(statement_text, Stmt)
Stmt stmt;
BEGIN_PARAMS(stmt)
SET_PARAM(stmt, 1, param1)
END_PARAMS(stmt)
BEGIN_BIND_COLUMNS(stmt)
BIND_COLUMN (stmt, 1, column1, column_output1, 0)
END_BIND_COLUMNS(stmt)
Command command(stmt);
command.Open(conn);
command.Execute();
RETCODE fetchcode = SQL_SUCCESS;
while ((fetchcode = command.Fetch()) == SQL_SUCCESS || fetchcode == SQL_SUCCESS_WITH_INFO)
{
}
command.CloseCursor();
command.Execute();
BEGIN_PARAMS(stmt)
SET_PARAM(stmt, 1, another_param1)
END_PARAMS(stmt)
command.ResetParameters();
command.Execute();
command.ExecuteDirect();
command.Close()
conn.Close()
Testing
The library was tested for 3 DBMS: MS SQL 2005 Developer Edition, IBM DB2 Express C 9.7.1 and MySQL 5.1. The library itself was compiled as a static library. For each DBMS a special test project was created. In each project there is a special SQL_Driver_DriverName.cpp file with the definitions of driver-specific settings. The test application should be linked to Boost Regex library. As I already mentioned, Boost Regex library should be built with the following pre-processor definition:
#define BOOST_REGEX_MATCH_EXTRA
You also need to change the include path and library path for all the projects to add the proper Boost location.
Also, in order to display the full paths to the files in the Output window, the compiler setting /FC should be put in the "Project Properties" – "C/C++" – "Advanced" field. The test program consists of several test functions. Some of them are "positive tests" – they test different execution scenarios. Some of them are "negative tests". They are never called, but they contain different errors that the validation procedure detects. When running under Visual Studio, the errors are reported in the Output window. You can jump to the location of each error by double-clicking on the error line in the Output window.
The connection strings were hardcoded for simplicity and you should change them accordingly when you will run the code.
MS SQL was tested mainly on the AdventureWorks database that comes with the MS SQL 2005 installation. One stored procedure was developed for testing purposes. You can find its SQL code in SQL_Scripts.sql file. I have found that ODBC driver for Microsoft SQL is doing a pretty good job. It detects almost all errors in SQL statements before they are executed either when calling SQLPrepare() or when calling SQLBindParameter(). One of few things that it cannot do is to detect an error with the hardcoded parameter. The following test statement has an invalid first column name, however the test passes.
DECLARE_STATEMENT(Stmt_Wrong_5,
TEXT("INSERT INTO [AdventureWorks].[Sales].[CreditCard] ([Type] ,[CardNumber] ,
[ExpMonth] ,[ExpYear] ,[ModifiedDate]) VALUES ('Vista', ?, ?, ?, ?)"))
Also, it does not validate well the stored procedure calls if they are used without parameters, in this case my custom validation of stored procedure calls catches up and does the job.
IBM DB2 was tested mainly on SAMPLE database that comes with IBM DB2 Express C installation. One stored procedure also was developed for testing purposes. You can find its SQL code in SQL_Scripts.sql file. ODBC driver for IBM DB2 is also doing a descent job. Some of the shortcomings that I have found are the following: 1) it does not check hardcoded parameters types; 2) in some cases it flops the proper syntax checking and database object checking when there are no parameters in the statement. The statements shown in the code snippet below contain errors. The first one uses TO clause instead of INTO in the INSERT statement. The second one refers to non-existent database object. Nevertheless, both tests pass for IBM DB2 ODBC driver.
DECLARE_STATEMENT(Stmt_Wrong1, TEXT("INSERT TO SAMPLE.ALEX.EMPLOYEE"))
DECLARE_STATEMENT(Stmt_Wrong2, TEXT("DELETE FROM WrongTable"))
MySQL driver was tested on custom database - test. SQL_Scripts.sql file contains scripts that populate this very simple database. I found that MySQL ODBC driver – MyODBC is not fully conformant to the Level 2. In my tests function SQLDescribeParam() in MyODBC returns nvarchar(255) type for all parameters - so parameter types validation was out of question. On the positive side, SQLBindParameter() function seems doesn’t care about parameter types as well, so binding using the type nvarchar(255) returned from SQLDescribeParam() still works. SQLProcedureColumns() function does not return any results for the stored procedure parameters - they should be left non-validated. I also found problems with the stored procedure output parameters in MySQL, so I had to disable them in the library. Overall, the using of the validation library is more limited in MySQL, but some nice features – like columns binding by name still work.
Finally I want to tell about the limitations of the described approach.
- Only one database per application session is supported
- Dynamic SQL statements either on the server side or on the client side cannot be validated.
- Hardcoded parameters cannot be checked
- Some ODBC functionality like cursor library, array parameters, "data at execution" access are not supported. In these cases, the native ODBC API functions should be used
Conclusion
In this article, I presented a technique for using C++ templates for validation of SQL statements used in C++ code. To facilitate this technique a simple ODBC wrapper class library was developed with the several macros. The library greatly simplifies ODBC API using. With almost zero runtime cost the library provides deep level of startup validation of SQL statements and plumbing of them with the client code. It also provides some nice features like binding the columns in the res set by name. The library was tested for 3 different DBMS. It proved to be most useful for the ODBC drivers, fully conformant to the Level 2.
If you have any comments or suggestions about this article or the source code, you can leave a message in the forum below. Happy error-free database programming!
History
- 28th October, 2010: Initial post
- 30th October, 2010: Article updated
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
