问题描述
我正在研究将一个小型且简单的 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.
原型在使用访问器和访问器结构的绑定成员时更新现有记录有问题.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 由 WHEREUPDATE 的 code> 子句已正确修改.问题似乎出在 SetData() 功能和绑定逻辑上.当我用 SQL Server Express 做同样的事情时，我没有看到错误.我在 SQL Server CE 3.5 和 SQL Server Mobile for Visual Studio 2005 中看到相同的错误.
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 宏的输出，用于显示各个列的状态值.从网上查到的，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
", myTable.m_dwIdNumberStatus);
ATLTRACE2(" myTable.m_dwCountStatus = %d
", 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 说 DBSTATUS_E_SCHEMAVIOLATION 意味着
MSDN says that DBSTATUS_E_SCHEMAVIOLATION means that
数据值违反了该列的架构约束.
只需在该页面上搜索 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！