[sqlapi-announce] SQLAPI++ 3.7.21 MS SQLServer fix for option "ICommandPrepare" = "SetParameterInfo"
- From: Sergey Chumakov <support@xxxxxxxxxx>
- To: sqlapi-announce@xxxxxxxxxxxxx
- Date: Tue, 05 Jun 2007 08:40:19 +0300
Hi,
The problem was found with Microsoft SQLServer SACommand option
"ICommandPrepare" = "SetParameterInfo" (SQLOLEDB connection).
This option controls when SQLAPI++ should use
ICommandWithParameters::SetParameterInfo() to describe query parameters (this
allows to fix SQLOLEDB bug KB235053). However when SetParameterInfo() is
executed SQLOLEDB uses passed parameter sizes, precisions and scales for every
executing of the prepared statement. This produces many problems with string
and numeric parameters. The sources were modified to re-prepare such
statements before each executing.
Many thanks to Chris Hecker.
The patch is attached.
--
Best regards,
Sergey Chumakov, SQLAPI++ development team
*** ssOleDbClient.cpp 17 May 2007 07:33:58 -0000 1.28
--- ssOleDbClient.cpp 1 Jun 2007 07:17:51 -0000 1.31
***************
*** 607,613 ****
void DescribeParamSP_GetParamCount(
bool &bRet, // has return value?
DBCOUNTITEM &cParams); // without return value if any
!
public:
IssOleDbCursor(
IssOleDbConnection *pIssOleDbConnection,
--- 607,614 ----
void DescribeParamSP_GetParamCount(
bool &bRet, // has return value?
DBCOUNTITEM &cParams); // without return value if any
! void SetParameterInfo(int nPlaceHolderCount,
! saPlaceHolder **ppPlaceHolders);
public:
IssOleDbCursor(
IssOleDbConnection *pIssOleDbConnection,
***************
*** 1655,1724 ****
return sSQL;
}
! // prepare statement (also convert to native format if needed)
! /*virtual */
! void IssOleDbCursor::Prepare(
! const SAString &sStmt,
! SACommandType_t eCmdType,
! int nPlaceHolderCount,
saPlaceHolder **ppPlaceHolders)
{
- SAString sStmtOleDb;
- int nPos = 0;
- int i;
- switch(eCmdType)
- {
- case SA_CmdSQLStmt:
- // replace bind variables with '?' place holder
- for(i = 0; i < nPlaceHolderCount; i++)
- {
- sStmtOleDb += sStmt.Mid(nPos,
ppPlaceHolders[i]->getStart()-nPos);
- sStmtOleDb += "?";
- nPos = ppPlaceHolders[i]->getEnd() + 1;
- }
- // copy tail
- if(nPos < sStmt.GetLength())
- sStmtOleDb += sStmt.Mid(nPos);
- break;
- case SA_CmdStoredProc:
- sStmtOleDb = CallSubProgramSQL();
- break;
- case SA_CmdSQLStmtRaw:
- sStmtOleDb = sStmt;
- break;
- default:
- assert(false);
- }
-
- // now actually prepare
- BSTR bsStmtOleDb = SA_SysAllocString(sStmtOleDb);
- try
- {
- Check(m_handles.pICommandText->SetCommandText(
- DBGUID_DEFAULT, bsStmtOleDb),
- m_handles.pICommandText, IID_ICommandText);
- ::SysFreeString(bsStmtOleDb);
- }
- catch(SAException &)
- {
- ::SysFreeString(bsStmtOleDb);
- throw;
- }
-
- SAString sOption = m_pCommand->Option(_TSA("ICommandPrepare"));
-
- // we have an option to skip preparation at all
- // This can be usefull in two cases:
- // 1) You run your query only once - saves time
- // 2) To avoid access violation bug in SQLOLEDB - example query
(subselect and bind):
- // Select state, (Select count(*) from authors where state = a.state)
- // from authors a where state = :1
- if(sOption.CompareNoCase(_TSA("skip")) == 0)
- return;
-
- if( nPlaceHolderCount > 0 && SA_CmdSQLStmt == eCmdType &&
- 0 == sOption.CompareNoCase(_TSA("SetParameterInfo")) )
- {
ICommandWithParameters *pICommandWithParameters = NULL;
Check(m_handles.pICommandText->QueryInterface(
IID_ICommandWithParameters,
--- 1656,1664 ----
return sSQL;
}
! void IssOleDbCursor::SetParameterInfo(int nPlaceHolderCount,
saPlaceHolder **ppPlaceHolders)
{
ICommandWithParameters *pICommandWithParameters = NULL;
Check(m_handles.pICommandText->QueryInterface(
IID_ICommandWithParameters,
***************
*** 1733,1739 ****
malloc(sizeof(DBPARAMBINDINFO) * cParams);;
memset(pParamBindInfo, 0, sizeof(DBPARAMBINDINFO) * cParams);
! for(i = 0; i < nPlaceHolderCount; i++)
{
SAParam *param = ppPlaceHolders[i]->getParam();
pParamOrdinals[i] = i + 1;
--- 1673,1679 ----
malloc(sizeof(DBPARAMBINDINFO) * cParams);;
memset(pParamBindInfo, 0, sizeof(DBPARAMBINDINFO) * cParams);
! for(int i = 0; i < nPlaceHolderCount; i++)
{
SAParam *param = ppPlaceHolders[i]->getParam();
pParamOrdinals[i] = i + 1;
***************
*** 1754,1760 ****
pParamBindInfo[i].ulParamSize = InputBufferSize(*param);
! SADataType_t eDataType = param->ParamType();
switch( IssOleDbConnection::CnvtStdToNative(
eDataType == SA_dtUnknown? SA_dtString :
eDataType) )
{
--- 1694,1700 ----
pParamBindInfo[i].ulParamSize = InputBufferSize(*param);
! SADataType_t eDataType = param->DataType();
switch( IssOleDbConnection::CnvtStdToNative(
eDataType == SA_dtUnknown? SA_dtString : eDataType) )
{
***************
*** 1782,1789 ****
break;
case DBTYPE_NUMERIC:
pParamBindInfo[i].pwszDataSourceType =
L"DBTYPE_NUMERIC";
! pParamBindInfo[i].bPrecision =
(BYTE)param->ParamPrecision();
! pParamBindInfo[i].bScale =
(BYTE)param->ParamScale();
break;
case DBTYPE_DBTIMESTAMP:
// The DBTIMESTAMP typedef defined in OLEDB.H
header file.
--- 1722,1729 ----
break;
case DBTYPE_NUMERIC:
pParamBindInfo[i].pwszDataSourceType =
L"DBTYPE_NUMERIC";
! pParamBindInfo[i].bPrecision =
(BYTE)param->asNumeric().precision;
! pParamBindInfo[i].bScale =
(BYTE)param->asNumeric().scale;
break;
case DBTYPE_DBTIMESTAMP:
// The DBTIMESTAMP typedef defined in OLEDB.H header
file.
***************
*** 1793,1813 ****
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize = 4000;
pParamBindInfo[i].pwszDataSourceType =
L"DBTYPE_WSTR";
break;
case DBTYPE_STR:
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize = 8000;
pParamBindInfo[i].pwszDataSourceType =
L"DBTYPE_STR";
break;
case DBTYPE_BYTES:
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize = 8000;
pParamBindInfo[i].pwszDataSourceType =
L"DBTYPE_BYTES";
break;
default:
--- 1733,1756 ----
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize =
! (DBLENGTH)param->asString().GetLength();
pParamBindInfo[i].pwszDataSourceType = L"DBTYPE_WSTR";
break;
case DBTYPE_STR:
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize =
! (DBLENGTH)param->asString().GetLength();
pParamBindInfo[i].pwszDataSourceType = L"DBTYPE_STR";
break;
case DBTYPE_BYTES:
if( isLongOrLob(eDataType) )
pParamBindInfo[i].ulParamSize |= ~0;
else
! pParamBindInfo[i].ulParamSize =
!
(DBLENGTH)param->asString().GetBinaryLength();
pParamBindInfo[i].pwszDataSourceType = L"DBTYPE_BYTES";
break;
default:
***************
*** 1832,1839 ****
--- 1775,1866 ----
pICommandWithParameters->Release();
free(pParamOrdinals);
free(pParamBindInfo);
+
+ ICommandPrepare *pICommandPrepare = NULL;
+ Check(m_handles.pICommandText->QueryInterface(
+ IID_ICommandPrepare, (void**)&pICommandPrepare),
+ m_handles.pICommandText, IID_ICommandText);
+
+ try
+ {
+ pICommandPrepare->Unprepare();
+ Check(pICommandPrepare->Prepare(0),
+ pICommandPrepare, IID_ICommandPrepare);
+ }
+ catch(SAException &)
+ {
+ pICommandPrepare->Release();
+ throw;
+ }
+
+ pICommandPrepare->Release();
+ }
+
+ // prepare statement (also convert to native format if needed)
+ /*virtual */
+ void IssOleDbCursor::Prepare(
+ const SAString &sStmt,
+ SACommandType_t eCmdType,
+ int nPlaceHolderCount,
+ saPlaceHolder **ppPlaceHolders)
+ {
+ SAString sStmtOleDb;
+ int nPos = 0;
+ int i;
+ switch(eCmdType)
+ {
+ case SA_CmdSQLStmt:
+ // replace bind variables with '?' place holder
+ for(i = 0; i < nPlaceHolderCount; i++)
+ {
+ sStmtOleDb += sStmt.Mid(nPos,
ppPlaceHolders[i]->getStart()-nPos);
+ sStmtOleDb += "?";
+ nPos = ppPlaceHolders[i]->getEnd() + 1;
+ }
+ // copy tail
+ if(nPos < sStmt.GetLength())
+ sStmtOleDb += sStmt.Mid(nPos);
+ break;
+ case SA_CmdStoredProc:
+ sStmtOleDb = CallSubProgramSQL();
+ break;
+ case SA_CmdSQLStmtRaw:
+ sStmtOleDb = sStmt;
+ break;
+ default:
+ assert(false);
}
+ // now actually prepare
+ BSTR bsStmtOleDb = SA_SysAllocString(sStmtOleDb);
+ try
+ {
+ Check(m_handles.pICommandText->SetCommandText(
+ DBGUID_DEFAULT, bsStmtOleDb),
+ m_handles.pICommandText, IID_ICommandText);
+ ::SysFreeString(bsStmtOleDb);
+ }
+ catch(SAException &)
+ {
+ ::SysFreeString(bsStmtOleDb);
+ throw;
+ }
+
+ SAString sOption = m_pCommand->Option(_TSA("ICommandPrepare"));
+ // we have an option to skip preparation at all
+ // This can be usefull in two cases:
+ // 1) You run your query only once - saves time
+ // 2) To avoid access violation bug in SQLOLEDB - example query
(subselect and bind):
+ // Select state, (Select count(*) from authors where state = a.state)
+ // from authors a where state = :1
+ if(sOption.CompareNoCase(_TSA("skip")) == 0)
+ return;
+
+ if( nPlaceHolderCount > 0 && SA_CmdSQLStmt == eCmdType &&
+ 0 == sOption.CompareNoCase(_TSA("SetParameterInfo")) )
+ // the statement will be re-prepared at Execute()
+ return;
+
ICommandPrepare *pICommandPrepare = NULL;
Check(m_handles.pICommandText->QueryInterface(
IID_ICommandPrepare, (void**)&pICommandPrepare),
***************
*** 2249,2254 ****
--- 2276,2287 ----
{
assert(m_handles.pIMultipleResults == NULL);
+ SAString sOption = m_pCommand->Option(_TSA("ICommandPrepare"));
+ if( nPlaceHolderCount > 0 &&
+ SA_CmdSQLStmt == m_pCommand->CommandType() &&
+ 0 == sOption.CompareNoCase(_TSA("SetParameterInfo")) )
+ SetParameterInfo(nPlaceHolderCount, ppPlaceHolders);
+
if(nPlaceHolderCount)
Bind(nPlaceHolderCount, ppPlaceHolders);
***************
*** 2257,2263 ****
dbparam.cParamSets = 1;
dbparam.hAccessor = m_hAccessorParamData;
! SAString sOption = m_pCommand->Option(_TSA("UseDynamicCursor"));
if(!sOption.IsEmpty() &&
(sOption.CompareNoCase(_TSA("1")) == 0 ||
sOption.CompareNoCase(_TSA("TRUE")) == 0))
{
--- 2290,2296 ----
dbparam.cParamSets = 1;
dbparam.hAccessor = m_hAccessorParamData;
! sOption = m_pCommand->Option(_TSA("UseDynamicCursor"));
if(!sOption.IsEmpty() &&
(sOption.CompareNoCase(_TSA("1")) == 0 ||
sOption.CompareNoCase(_TSA("TRUE")) == 0))
{
Other related posts:
- » [sqlapi-announce] SQLAPI++ 3.7.21 MS SQLServer fix for option "ICommandPrepare" = "SetParameterInfo"
