Help! ADO Connection Problem..
From: matt (matthias.aerni_at_cablecom.ch)
Date: 03/26/04
- Previous message: alex: "RE: Create Connectionless Recordset Errors"
- Next in thread: Val Mazur: "Re: Help! ADO Connection Problem.."
- Reply: Val Mazur: "Re: Help! ADO Connection Problem.."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 26 Mar 2004 18:33:36 +0100
Hello,
I wote an ISAPI Application(C++) with ADO access to MS SQL-Server.
While testing I did not have any Problems.. everything worked great!
But now on the productive system with many concurrent Users I get
a lot of Errors!
com_error
" (0x800A0E78) Operation is not allowed when the object is closed."
or
" (0x800A0E7D)
The connection cannot be used to perform this operation. It is either
closed or invalid in this context."
and when there a lot of Errors occur even my ASP-Pages fail..
Set adoCon = Server.CreateObject("ADODB.Connection")
Failed to create Object.
I tried to be smart an put a reconnect() method into the prepare
function see below..(UserManager.cpp)
now it works a bit better but there are still a lot of these errors I
think I do something completly wrong..
I use the Class like this..
UserManager userMgr(dbDataSource,db,dbUserId,dbPwd);
...
..
userMgr.updateLastMessage(username);
..
..
StringMap bannedUser=userMgr.getBannedUserList(username);
..
..
userMgr.closeConnetion()
sometimes I loose the connection even between
prepareCommand() and. getOpenRecordSet()!?
I have no idea!
any help would be great..
thanks,
matt
------------------------- UserManager.cpp ----------------------
// ADO User Manager
//
_ConnectionPtr pConnection;
HRESULT hr;
inline void TESTHR(HRESULT x)
{
if FAILED(x)
_com_issue_error(x);
};
UserManager::UserManager(string in_dataSource,string in_db,string
in_user,string in_pwd)
{
dataSource=in_dataSource;
db=in_db;
user=in_user;
pwd=in_pwd;
currentDateTime = COleDateTime::GetCurrentTime();
setConnection(dataSource,db,user,pwd);
hr = S_OK;
CoInitialize(NULL);
pConnection = NULL;
try
{
//Open a connection
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
pConnection->Open(dbConnection.data(),"","",adConnectUnspecified);
}
catch(_com_error &e)
{
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
//throw ("DB Error in UserManager::UserManager ->open
connection");
}
catch(...)
{
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
//throw ("Unhandled Exception in
UserManager::UserManager -> open connection");
}
}
UserManager::~UserManager(void)
{
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
}
void UserManager::reconnect() {
setConnection(dataSource,db,user,pwd);
hr = S_OK;
CoInitialize(NULL);
pConnection = NULL;
try
{
//Open a connection
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
pConnection->Open(dbConnection.data(),"","",adConnectUnspecified);
}
catch(_com_error &e)
{
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
}
catch(...)
{
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
}
}
void UserManager::closeConnection() {
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
::CoUninitialize();
}
void UserManager::setConnection(string aDataSource,string aDb,string
aUser,string aPwd)
{
dbConnection="Provider=sqloledb;";
dbConnection.append("Data
Source=").append(aDataSource).append(";");
dbConnection.append("Initial
Catalog=").append(aDb).append(";");
dbConnection.append("User Id=").append(aUser).append(";");
dbConnection.append("Password=").append(aPwd).append(";");
}
void UserManager::cleanUp(_RecordsetPtr pRecordset) {
cleanUp(pRecordset,NULL);
}
void UserManager::cleanUp(_CommandPtr pCmd) {
cleanUp(NULL,pCmd);
}
void UserManager::cleanUp(_RecordsetPtr pRecordset,_CommandPtr pCmd) {
if (pRecordset)
if (pRecordset->State == adStateOpen)
pRecordset->Close();
if (pCmd)
if (pCmd->State == adStateOpen)
pCmd->Release();
}
_CommandPtr UserManager::prepareCommand(CString& strQuery)
{
_CommandPtr pCmd;
try
{
if (pConnection) {
if (pConnection->State != adStateOpen)
reconnect();
}else{
reconnect();
}
pCmd.CreateInstance(_uuidof(Command));
pCmd->ActiveConnection=pConnection;
pCmd->CommandText= (_bstr_t) strQuery;
}
catch(_com_error &e)
{
cleanUp(pCmd);
//throw ("DB Fehler in UserManager::isInBanList");
//PrintComError(e);
}
catch(...)
{
cleanUp(pCmd);
//throw ("Unhandled Exception in
UserManager::isInBanList");
}
return pCmd;
}
_RecordsetPtr UserManager::getOpenRecordSet(_CommandPtr pCmd)
{
_RecordsetPtr pRecordset;
try
{
pRecordset.CreateInstance(__uuidof(Recordset));
pRecordset->CursorLocation=adUseClient;
pRecordset->Open((IDispatch*)pCmd,
vtMissing,
adOpenStatic,
adLockOptimistic,
adCmdUnknown);
}
catch(_com_error &e)
{
cleanUp(pRecordset);
//throw ("DB Fehler in
UserManager::getOpenRecordSet");
}
catch(...)
{
cleanUp(pRecordset);
//throw ("Unhandled Exception in
UserManager::getOpenRecordSet");
}
return pRecordset;
}
void UserManager::doExecuteSql(CString &strQuery,CString &methodeName)
{
FILETIME ftStart = getFileTime();
_RecordsetPtr pRecordSet=NULL;
_CommandPtr pCmd=NULL;
try
{
pCmd=prepareCommand(strQuery);
pRecordSet=getOpenRecordSet(pCmd);
cleanUp(pRecordSet,pCmd);
}
catch(_com_error &e)
{
cleanUp(pRecordSet,pCmd);
}
catch(...)
{
cleanUp(pRecordSet,pCmd);
}
}
StringMap UserManager::getBannedUserList(long id) {
FILETIME ftStart = getFileTime();
StringMap banList;
CString strQuery;
strQuery.Format("SELECT * FROM stUserUserBan WHERE Author_ID =
%d AND Type=1",id);
_RecordsetPtr pRecordset=NULL;
_CommandPtr pCmd=NULL;
try
{
pCmd=prepareCommand(strQuery);
pRecordset=getOpenRecordSet(pCmd);
_variant_t vBanUsername;
while (!pRecordset->ADOEOF)
{
vBanUsername=pRecordset->Fields->GetItem(L"BanUsername")->GetValue();
string banUser=static_cast<char
*>(_bstr_t(vBanUsername));
banList.insert(value_type (string
(banUser.data()),string (banUser.data())));
pRecordset->MoveNext();
}
cleanUp(pRecordset,pCmd);
}
catch(_com_error &e)
{
cleanUp(pRecordset,pCmd);
//throw ("DB Fehler in
UserManager::getBannedUserList");
}
catch(...)
{
cleanUp(pRecordset,pCmd);
};
return banList;
}
void UserManager::updateLastMessage(char* username)
{
CString methodeName("updateLastMessage");
string sUsername=username;
int id=lookupUserAccountID(sUsername);
CString strQuery;
strQuery.Format("Execute UpdateLastWrite
@strUsername='%s'",username);
doExecuteSql(strQuery,methodeName);
}
void UserManager::updateUserDetail(UserDetail &aUserDetail)
{
CString methodeName("updateUserDetail");
COleDateTime out=aUserDetail.lastLogout;
COleDateTime in=aUserDetail.lastLogin;
COleDateTime last=aUserDetail.lastMessage;
//prepare sql statement
CString strQuery;
strQuery.Format("Update stUserDetail"
" SET Retry_Counter=%d,"
" GrantedLogins=%d,"
" Last_Login='%d-%d-%d %d:%d:%d',"
" Last_Logout='%d-%d-%d %d:%d:%d',"
" LastMessage='%d-%d-%d %d:%d:%d'"
" WHERE Author_Id=%d"
,aUserDetail.retryCounter
,aUserDetail.grantedLogins
,in.GetYear(),in.GetMonth(),in.GetDay(),in.GetHour(),in.GetMinute(),in.GetSecond()
,out.GetYear(),out.GetMonth(),out.GetDay(),out.GetHour(),out.GetMinute(),out.GetSecond()
,last.GetYear(),last.GetMonth(),last.GetDay(),last.GetHour(),last.GetMinute(),last.GetSecond()
,aUserDetail.getAuthorId());
//execute
doExecuteSql(strQuery,methodeName);
}
UserAccount UserManager::getUserAccount(const char* username)
{
FILETIME ftStart = getFileTime();
UserAccount useraccount;
_RecordsetPtr pRecordset=NULL;
_CommandPtr pCmd=NULL;
//Interface Pointer declared.(VC++ Extensions)
IADORecordBinding *picAuthorRs = NULL;
try
{
//C++ class object
CAuthorRs authrs;
CString strQuery;
strQuery.Format("SELECT * FROM tblAuthor WHERE
username='%s'",username);
pCmd=prepareCommand(strQuery);
pRecordset=getOpenRecordSet(pCmd);
//Open an IADORecordBinding interface pointer which
we'll use for Binding
//Recordset to a class
TESTHR(pRecordset->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&picAuthorRs));
//Bind the Recordset to a C++ Class here
TESTHR(picAuthorRs->BindToRecordset(&authrs));
//Get the Fields collection from the recordset
FieldsPtr pFields=pRecordset->Fields;
//From the Fields collection, get the desired Field
FieldPtr pFieldId=pFields->GetItem("author_id");
_variant_t vId;
if (!pRecordset->ADOEOF)
{
//useraccount.setId(authrs.m_id);
vId=pFieldId->Value;
useraccount.setId(static_cast<int>(int(vId)));
useraccount.setUsername(authrs.m_username);
useraccount.userCode=authrs.m_User_code;
useraccount.setPassword(authrs.m_Password);
useraccount.setSalt(authrs.m_Salt);
useraccount.author_email=authrs.m_Author_email;
useraccount.active=bool((_variant_t)authrs.m_active);
if (picAuthorRs)
picAuthorRs->Release();
cleanUp(pRecordset,pCmd);
}; //end if (!pRecordset->ADOEOF)
}
catch(_com_error &e)
{
if (picAuthorRs)
picAuthorRs->Release();
cleanUp(pRecordset,pCmd);
//throw ("DB Fehler in UserManager::getUserAccount");
}
catch(...)
{
if (picAuthorRs)
picAuthorRs->Release();
cleanUp(pRecordset,pCmd);
//throw ("Unhandled Exception in
UserManager::getUserAccount");
};
return useraccount;
}
------------------------- UserManager.cpp ----------------------
- Previous message: alex: "RE: Create Connectionless Recordset Errors"
- Next in thread: Val Mazur: "Re: Help! ADO Connection Problem.."
- Reply: Val Mazur: "Re: Help! ADO Connection Problem.."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
