Bug #4432 Successful status but record not updated
Submitted: 7 Jul 2004 10:11 Modified: 30 May 2013 5:28
Reporter: Nicolas Loechner Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.07 OS:Windows (Windows)
Assigned to: Matthew Lord CPU Architecture:Any

[7 Jul 2004 10:11] Nicolas Loechner
Description:
I'm maintaining a unicode application written in Visual .NET C++ with an ODBC database connection.
The problem arises when there are 3 indexes in a table, on field A, on field B and on both fields (A,B), and when there is at least one record with empty(NULL?) values. I search for any record (not necessarily this one with empty values), call Edit(), make changes and call Update() ; ODBC reports no error, yet the record is not updated in the database.
I'm not sure if this is a problem with the motor, ODBC, .NET, unicode, or anything else. Also, I'm not sure defining an index on A, B and (A,B) is useful (I'm no database/SQL guru, I'm just maintaining the application).

How to repeat:
I created a test database called NAMES and the associated DSN. Note that the 3rd element has empty values. Deleting it "solves" the problem.

CREATE TABLE t_name (
  NUM_NAME int(11) default NULL,
  STR_NAME varchar(50) default NULL,
  INFO int(11) default NULL,
  ID varchar(10) default NULL,
  UNIQUE KEY IDX_NAME_NUM_NAME (NUM_NAME),
  KEY idx_info_id (INFO,ID),
  KEY IDX_INFO (INFO),
  KEY IDX_ID (ID)
) TYPE=MyISAM;

--
-- Dumping data for table `t_name`
--

INSERT INTO t_name VALUES (1,'wasntme',231223,'er');
INSERT INTO t_name VALUES (2,'yo!',1,'er');
INSERT INTO t_name VALUES (3,'heyheyhey',0,'');

C++ Code :

void CTestDBDlg::OnBnClickedButtonTest()
{
	CDatabase *db = NULL;
	Ct_name *pName = NULL;
	try
	{
		db = new CDatabase ();
		int nFlags = CDatabase::noOdbcDialog | CDatabase::useCursorLib;
		CString sDSN (_T("DSN=names"));
		bool bConnected = db->OpenEx (sDSN, nFlags) ? true : false;
		if (!bConnected)
		{
			TRACE (_T("OpenEx\n"));
			delete db;
			return;
		}

		pName = new Ct_name (db);
		pName->m_strFilter.Format (_T("NUM_NAME=%d"), 2);
		pName->m_strSort = _T("");
		pName->Open ();
		if (!pName->IsOpen ())
		{
			TRACE (_T("IsOpen\n"));
			delete pName;
			delete db;
			return;
		}
		if (pName->IsEOF ())
		{
			TRACE (_T("IsEOF\n"));
			pName->Close ();
			delete pName;
			delete db;
			return;
		}
		pName->Edit ();

		pName->m_STR_NAME = _T("zog?");
		pName->m_INFO = 31;

		pName->SetFieldDirty(NULL,TRUE);
		if (!pName->Update ()) MessageBox (_T("UPDATE RETURNS 0 (T_NAME).\n"));
		pName->Close ();
		delete pName;

		delete db;
		TRACE (_T("Successful.\n"));
	}
	catch (CDBException *e)
	{
		CString	tmp;
		tmp.Format(_T("Error number %d : %s\n"), e->m_nRetCode, ODBC_ERROR_STRING(e));
		TRACE (_T("DBException : %s\n"), tmp);

		if (pName) delete pName;
		if (db) delete db;
	}
}

Ct_name is the standard .NET generated CRecordset class on T_NAME.

Suggested fix:
I solved my problem by deleting the 3rd index (on both fields (A,B)).
[1 Oct 2004 22:59] Matthew Lord
Dear Sir,

Thank you for your bug report!

For this kind of general, application layer, row editing to work the table will need 
a primary key so that each row can be uniquely identified.  Try making the unique
index the primary key or making the primary key be a combination of two or more
of the other indexed columns.  The main difference between a primary key and a 
unique index is that a primary key does not allow null values as it needs to be truly
unique and cannot allow multiple null or undefined values.

Best Regards
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 May 2013 5:28] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.