Exception error SQL_NO_DATA when data on server changed

Hello,
I finally managed to track the problem. If one program issues a
transaction which changes a record and a second program already defined
a recordset including this record, then the second program will receive
SQL_NO_DATA error when moving to this record.
The problem appeared with ODBC connection to SQL Server and to MySQL.
The following piece of code demonstrates the problem:
{
srand(time(NULL));
CDatabase db1, db2;
db1.Open("DBTEST");
db2.Open("DBTEST");
CTblTest rs1(&db1), rs2(&db2);
rs1.m_strFilter = "ID=3";
rs2.m_strFilter = "ID>=2 AND ID<5";
rs2.m_strSort = "ID";
db1.BeginTrans();
rs2.Open();
rs1.Open();
rs1.Edit();
rs1.m_TEXT = (char)(rand() % 26 + 'A');
rs1.SetFieldDirty(&rs1.m_BIN);
rs1.Update();
db1.CommitTrans();
while(!rs2.IsEOF())
{
OutputDebugString(rs2.m_TEXT + "\r\n");
try
{
rs2.MoveNext();
}
catch(CDBException *e)
{
char err[512];
CString msg;
err[0] = '\0';
e->GetErrorMessage(err, sizeof(err));
msg.Format("Exception error (%d) %s, %s, %s\r\n",
e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
e->Delete();
OutputDebugString(msg);
}
}
rs1.Close();
rs2.Close();
OutputDebugString("Done\r\n");
}
Record 2 is shown
Exception 100 for record 3
Record 4 is shown
So, it seems all I need to do is move to the next record and ignore the
error. What do you think?
Thanks, Reuven
Thanks for your followup Reuven,
I think this should be the case, and the behavior is likely due to the
SQLServer or ODBC driver's internal implementation for such concurrent
condition... since the recordset you opened hold a live connection and at
the sametime another program connection is manipulating the same
table/records, the recordset return NO_DATA for that record... If it
dosn't broke your program, you can just catch the SQL_NO_DATA and continue
as you mentioned...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Date: Wed, 18 Jan 2006 23:08:05 +0200
| From: Reuven Nisser <rnisser@.newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Exception error SQL_NO_DATA when data on server changed
| References: <43C24382.9050505@.newsgroup.nospam>
<9jWc#HbFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<uN8YSWjFGHA.376@.TK2MSFTNGP12.phx.gbl>
<TqKcHPqFGHA.3696@.TK2MSFTNGXA02.phx.gbl>
<#r9xOl#FGHA.3936@.TK2MSFTNGP12.phx.gbl>
<E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| In-Reply-To: <E7L1jknGGHA.3680@.TK2MSFTNGXA02.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-8-I; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <eMbcLNHHGHA.1032@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: cbl217-132-80-84.bb.netvision.net.il 217.132.80.84
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:44651
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello,
| I finally managed to track the problem. If one program issues a
| transaction which changes a record and a second program already defined
| a recordset including this record, then the second program will receive
| SQL_NO_DATA error when moving to this record.
| The problem appeared with ODBC connection to SQL Server and to MySQL.
| The following piece of code demonstrates the problem:
|
| {
| srand(time(NULL));
| CDatabase db1, db2;
| db1.Open("DBTEST");
| db2.Open("DBTEST");
|
| CTblTest rs1(&db1), rs2(&db2);
| rs1.m_strFilter = "ID=3";
| rs2.m_strFilter = "ID>=2 AND ID<5";
| rs2.m_strSort = "ID";
|
| db1.BeginTrans();
| rs2.Open();
|
| rs1.Open();
| rs1.Edit();
| rs1.m_TEXT = (char)(rand() % 26 + 'A');
| rs1.SetFieldDirty(&rs1.m_BIN);
| rs1.Update();
|
| db1.CommitTrans();
|
| while(!rs2.IsEOF())
| {
| OutputDebugString(rs2.m_TEXT + "\r\n");
| try
| {
| rs2.MoveNext();
| }
| catch(CDBException *e)
| {
| char err[512];
| CString msg;
| err[0] = '\0';
| e->GetErrorMessage(err, sizeof(err));
| msg.Format("Exception error (%d) %s, %s, %s\r\n",
| e->m_nRetCode, err, e->m_strError, e->m_strStateNativeOrigin);
| e->Delete();
| OutputDebugString(msg);
| }
| }
|
| rs1.Close();
| rs2.Close();
| OutputDebugString("Done\r\n");
| }
|
|
| Record 2 is shown
| Exception 100 for record 3
| Record 4 is shown
|
| So, it seems all I need to do is move to the next record and ignore the
| error. What do you think?
|
| Thanks, Reuven
|