SQL Server CE 3.5更新行错误DB_E_ERRORSOCCURRED列错误为DBSTATUS_E_SCHEMAVIOLATION [英] SQL Server CE 3.5 update row error DB_E_ERRORSOCCURRED column error is DBSTATUS_E_SCHEMAVIOLATION
问题描述
我正在研究将一个小型且简单的SQL Server数据库移至SQL Server CE，并且目前正在使用一个小型原型来研究SQL Server CE的基本操作，并牢记以下操作:(1)以编程方式创建表，(2 )插入新记录，(3)读取现有记录，(4)更新现有记录.
I am investigating moving a small and simple SQL Server database to SQL Server CE and am currently using a small prototype to investigate basic operations with SQL Server CE with the following operations in mind: (1) programmatically create a table, (2) insert new records, (3) read existing records, and (4) update existing records.
使用Accessor和Accessor结构的绑定成员时，原型在更新现有记录时遇到问题. select语句可以正确返回行以及数据.我可以更新访问者绑定的成员，但是当我使用SetData()方法更新行时，返回的HRESULT值是DB_E_ERRORSOCCURRED.然后，我检查DBSTATUS变量，然后可以看到DBSTATUS_E_SCHEMAVIOLATION的错误代码.
The prototype is having a problem with updating existing records when using the Accessor and the bound members of the Accessor struct. The select statement works correctly returning the row along with the data. I can update the Accessor bound members however when I use the SetData() method to update the row, the HRESULT value returned is DB_E_ERRORSOCCURRED. I then examine the DBSTATUS variables and I can see the error code of DBSTATUS_E_SCHEMAVIOLATION.
DBSTATUS_E_SCHEMAVIOLATION是什么意思，我需要更改什么才能使SetData()工作?
What does DBSTATUS_E_SCHEMAVIOLATION mean and what do I need to change so that SetData() works?
如果我修改OLEDB中使用的SQL查询，而不是执行SELECT，那么我会执行UPDATE，而正确修改了由UPDATE的WHERE子句选择的行.问题似乎出在SetData()功能和绑定逻辑上.使用SQL Server Express做同样的事情时，看不到错误.我在用于Visual Studio 2005的SQL Server CE 3.5和SQL Server Mobile上看到相同的错误.
If I modify the SQL query used in the OLEDB so that rather than doing a SELECT I instead do an UPDATE the row selected by the WHERE clause of the UPDATE is modified correctly. The problem appears to be with the SetData() functionality and the binding logic. When I have done the same thing with SQL Server Express, I do not see an error. I see the same error with both SQL Server CE 3.5 and SQL Server Mobile for Visual Studio 2005.
在Visual Studio 2005 IDE的输出窗口中，我看到以下几行.从ATLTRACE2宏输出标记为<<<<< ATLTRACE2的两行，以显示各个列的状态值.根据我在互联网上可以找到的信息，First-chance异常日志是一个可以忽略的警告.
In the output window of the Visual Studio 2005 IDE I see the following lines. Two of the lines marked with <<<<< ATLTRACE2 are output from ATLTRACE2 macros to show the individual column status values. From what I can find on the internet, the First-chance exception log is a warning that can be ignored.
First-chance exception at 0x7c812fd3 in dblist_ce.exe: Microsoft C++ exception: long at memory location 0x0012f698..
OLE DB Error Record dump for hr = 0x80040e21
The thread 'Win32 Thread' (0x16dc) has exited with code 0 (0x0).
Row #: 0 Source: "Microsoft Cursor Engine" Description: "Multiple-step operation generated errors. Check each status value." Help File: "(null)" Help Context: 0 GUID: {00000000-0000-0000-0000-000000000000}
OLE DB Error Record dump end
myTable.m_dwIdNumberStatus = 8 <<<<< ATLTRACE2
myTable.m_dwCountStatus = 11 <<<<< ATLTRACE2
两个状态值(m_dwIdNumberStatus和m_dwCountStatus)具有来自枚举的值，以上两个值表示DBSTATUS_E_UNAVAILABLE = 8和DBSTATUS_E_SCHEMAVIOLATION = 11. IdNumber的状态为DBSTATUS_E_UNAVAILABLE，因为我在执行SetData()之前将其设置为忽略.
The two status values (m_dwIdNumberStatus and m_dwCountStatus) have values from an enum and the above two values represent DBSTATUS_E_UNAVAILABLE = 8 and DBSTATUS_E_SCHEMAVIOLATION = 11. The status for IdNumber is DBSTATUS_E_UNAVAILABLE because I am setting it to be ignored before doing the SetData().
原型的源代码如下.这样做是创建一个不存在的SQL Server CE数据库文件，然后用一组行填充它，然后尝试对一个特定的行进行更新.
The source code for the prototype follows. What this does is to create the SQL Server CE database file if it does not exist and then fills it with a set of rows and then tries to do an update on one particular row.
// dblist_ce.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include <string>
#include <iostream>
#define SQLSERVER_MOBILE L"Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\MyDatabase3.sdf"
#define SQLSERVER_CE_35 L"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\MyDatabase35.sdf"
#define SQL_SERVER_CONNECT_STRING SQLSERVER_MOBILE
#define SQL_SERVER_CE_FILENAME "C:\\MyDatabase3.sdf"
#if 0
#include "Table_1.h"
#else
// contents of include file Table_1.h follow
// Table_1.h : Declaration of the CTable_1
// code generated on Saturday, April 26, 2014, 11:23 AM
class CTable_1Accessor
{
public:
TCHAR m_IdNumber[11];
LONG m_Count;
// The following wizard-generated data members contain status
// values for the corresponding fields in the column map. You
// can use these values to hold NULL values that the database
// returns or to hold error information when the compiler returns
// errors. See Field Status Data Members in Wizard-Generated
// Accessors in the Visual C++ documentation for more information
// on using these fields.
// NOTE: You must initialize these fields before setting/inserting data!
DBSTATUS m_dwIdNumberStatus;
DBSTATUS m_dwCountStatus;
// The following wizard-generated data members contain length
// values for the corresponding fields in the column map.
// NOTE: For variable-length columns, you must initialize these
// fields before setting/inserting data!
DBLENGTH m_dwIdNumberLength;
DBLENGTH m_dwCountLength;
void GetRowsetProperties(CDBPropSet* pPropSet)
{
bool bRet;
bRet = pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
bRet = pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
bRet = pPropSet->AddProperty(DBPROP_IGetRow, true, DBPROPOPTIONS_OPTIONAL);
bRet = pPropSet->AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);
bRet = pPropSet->AddProperty(DBPROP_IRowsetUpdate, true, DBPROPOPTIONS_OPTIONAL);
bRet = pPropSet->AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
}
HRESULT OpenDataSource()
{
CDataSource _db;
HRESULT hr;
hr = _db.OpenFromInitializationString(SQL_SERVER_CONNECT_STRING);
if (FAILED(hr))
{
#ifdef _DEBUG
AtlTraceErrorRecords(hr);
#endif
return hr;
}
return m_session.Open(_db);
}
void CloseDataSource()
{
m_session.Close();
}
operator const CSession&()
{
return m_session;
}
CSession m_session;
DEFINE_COMMAND_EX(CTable_1Accessor, L" \
SELECT \
IdNumber, \
Count \
FROM Table_1")
// In order to fix several issues with some providers, the code below may bind
// columns in a different order than reported by the provider
BEGIN_COLUMN_MAP(CTable_1Accessor)
COLUMN_ENTRY_LENGTH_STATUS(1, m_IdNumber, m_dwIdNumberLength, m_dwIdNumberStatus)
COLUMN_ENTRY_LENGTH_STATUS(2, m_Count, m_dwCountLength, m_dwCountStatus)
END_COLUMN_MAP()
};
class CTable_1 : public CCommand<CAccessor<CTable_1Accessor> >
{
public:
HRESULT OpenAll()
{
HRESULT hr;
hr = OpenDataSource();
if (FAILED(hr))
return hr;
__if_exists(GetRowsetProperties)
{
CDBPropSet propset(DBPROPSET_ROWSET);
__if_exists(HasBookmark)
{
if( HasBookmark() )
propset.AddProperty(DBPROP_IRowsetLocate, true);
}
GetRowsetProperties(&propset);
return OpenRowset(&propset);
}
__if_not_exists(GetRowsetProperties)
{
__if_exists(HasBookmark)
{
if( HasBookmark() )
{
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetLocate, true);
return OpenRowset(&propset);
}
}
}
return OpenRowset();
}
HRESULT OpenRowset(DBPROPSET *pPropSet = NULL)
{
HRESULT hr = Open(m_session, NULL, pPropSet);
#ifdef _DEBUG
if(FAILED(hr))
AtlTraceErrorRecords(hr);
#endif
return hr;
}
void CloseAll()
{
Close();
ReleaseCommand();
CloseDataSource();
}
};
// ------ End of the content from include file Table_1.h
#endif
int _tmain(int argc, _TCHAR* argv[])
{
HRESULT hrResult = OleInitialize(NULL);
switch (hrResult)
{
case S_OK:
break;
default:
std::cout << "Ole Initialization Failed " << hrResult << std::endl;
return 1;
}
HRESULT hr;
CTable_1 myTable;
bool myTableNew = false;
hr = myTable.OpenAll ();
AtlTraceErrorRecords(hr);
if (hr == S_OK) {
int nItem = 0;
for (nItem = 0, hr = myTable.MoveFirst(); hr == S_OK; hr = myTable.MoveNext())
{
char szValueChar[12] = {0};
for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
std::string sTemp (szValueChar);
std::cout << nItem << " -> " << sTemp << " : " << myTable.m_Count << std::endl;
nItem++;
}
myTable.Close(); // close this row set.
} else if (hr == E_FAIL) {
FILE *hFile = fopen (SQL_SERVER_CE_FILENAME, "w");
if (hFile) {
fclose(hFile);
hr = myTable.OpenAll ();
if (hr == E_FAIL)
return 0;
}
}
if (hr == DB_E_NOTABLE) {
// The database file is empty meaning that there are no tables defined
// so we will create the table that we want to use.
myTable.Close(); // close this row set.
CDBPropSet m_pPropSet(DBPROPSET_ROWSET);
myTable.GetRowsetProperties (&m_pPropSet);
TCHAR *tcsQuery = L"create table Table_1 ([IdNumber] nchar(10) not null, [Count] int not null)";
hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, false);
myTable.Close(); // close this row set.
myTableNew = true;
}
CDBPropSet m_pPropSet(DBPROPSET_ROWSET);
myTable.GetRowsetProperties (&m_pPropSet);
TCHAR tcsQuery[256];
if (myTableNew) {
struct {
TCHAR IdNumber[11];
int iCount;
} myInsertData[] = {
{L"0000000101", 1001},
{L"0000000102", 1002},
{L"0000000103", 1003},
{L"0000000104", 1004},
{L"0000000105", 1005},
{L"0000000106", 1006},
{L"0000000107", 1007},
{L"0000000108", 1008},
{L"0000000109", 1009},
{L"0000000120", 1010}
};
std::cout << "-- New table so insert standard rows " << std::endl;
for (int i = 0; i < sizeof(myInsertData)/sizeof(myInsertData[0]); i++) {
_swprintf (tcsQuery, L"INSERT INTO Table_1 ( [IdNumber], [Count] ) VALUES ('%s', %d)", myInsertData[i].IdNumber, myInsertData[i].iCount);
hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, false);
myTable.Close(); // close this row set.
}
}
// Lets print out a list of the rows that we currently have in the database
wcscpy (tcsQuery, L"SELECT [IdNumber], [Count] from Table_1");
hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, true);
if (hr == S_OK) {
int nItem = 0;
for (nItem = 0, hr = myTable.MoveFirst(); hr == S_OK; hr = myTable.MoveNext())
{
char szValueChar[12] = {0};
for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
std::string sTemp (szValueChar);
std::cout << nItem << " -> " << sTemp << " : " << myTable.m_Count << std::endl;
nItem++;
}
myTable.Close(); // close this row set.
}
std::cout << " -- After insert now list the rows we have inserted" << std::endl;
wcscpy (tcsQuery, L"SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103'");
// wcscpy (tcsQuery, L"UPDATE Table_1 SET [Count]=[Count] + 1 where [IdNumber] = '0000000103'");
hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, true);
AtlTraceErrorRecords(hr);
if (hr == S_OK) {
if ((hr = myTable.MoveFirst()) == S_OK)
{
char szValueChar[12] = {0};
for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
std::string sTemp (szValueChar);
std::cout << " -> " << sTemp << " : " << myTable.m_Count << std::endl;
int iCountTemp = myTable.m_Count++;
std::cout << " increment count from " << iCountTemp << " to " << myTable.m_Count << std::endl;
myTable.m_dwIdNumberStatus = DBSTATUS_S_IGNORE;
myTable.m_dwCountStatus = DBSTATUS_S_OK;
hr = myTable.SetData ();
AtlTraceErrorRecords(hr);
if (hr != S_OK) {
ATLTRACE2(" myTable.m_dwIdNumberStatus = %d\n", myTable.m_dwIdNumberStatus);
ATLTRACE2(" myTable.m_dwCountStatus = %d\n", myTable.m_dwCountStatus);
if (myTable.m_dwIdNumberStatus != DBSTATUS_S_OK) {
std::cout << " error: m_dwIdNumberStatus = "<< myTable.m_dwIdNumberStatus << std::endl;
}
if (myTable.m_dwCountStatus != DBSTATUS_S_OK) {
std::cout << " error: m_dwCountStatus = "<< myTable.m_dwCountStatus << std::endl;
}
}
} else {
AtlTraceErrorRecords(hr);
}
}
myTable.Close(); // close this row set.
OleUninitialize ();
return 0;
}
推荐答案
MSDN says that DBSTATUS_E_SCHEMAVIOLATION means that
数据值违反了该列的架构约束.
The data value violated the schema's constraint for the column.
只需在该页面上搜索DBSTATUS_E_SCHEMAVIOLATION.
Just search for DBSTATUS_E_SCHEMAVIOLATION on that page.
这很奇怪，因为您对Count列的唯一约束是不为空".
It is strange because the only constraint that you have for the Count column is "not null".
就我个人而言，我从未将OLE DB与SQL Server精简版一起使用，也从未尝试像您一样更新行.我将所有T-SQL代码放入存储过程中，并使用"call"或"exec"进行调用.在存储过程内部，我使用标准的UPDATE语句.
Personally, I never used OLE DB with Compact Edition of SQL Server, and I never tried to update rows like you do. I put all my T-SQL code in stored procedures and call them using "call" or "exec". Inside stored procedures I use standard UPDATE statements.
在您的情况下，很可能您对游标的类型是正确的.当您调用myTable.Open来运行查询SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103'时，很可能会得到一个只读游标.我不知道如何检查它，但是在您的位置，我会尝试找到一种方法来确认游标是否首先是可更新的.
In your case it is very likely that you are right about the type of the cursor. When you call myTable.Open to run the query SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103' it is very likely that you are getting a read-only cursor. I don't know how to check it, but in your place I would try to find a way to confirm whether the cursor is updatable in the first place.
这篇关于SQL Server CE 3.5更新行错误DB_E_ERRORSOCCURRED列错误为DBSTATUS_E_SCHEMAVIOLATION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
