All geek questions in one place

SQL Server CE 3.5 Update Line Error DB_E_ERRORSOCCURRED Error is DBSTATUS_E_SCHEMAVIOLATION

I am studying moving a small and simple SQL Server database to SQL Server CE and am currently using a small prototype to study basic operations with SQL Server CE, taking into account the following operations: (1) programmatically create a table (2)) insert new records, (3 ) read existing records and (4) update existing records.

The prototype has a problem updating existing records when using Accessor and associated Accessor structure members. The select statement correctly returns a string along with the data. I can update the associated Accessor members, however, when I use the method SetData()to update the string, the return value HRESULTis equal DB_E_ERRORSOCCURRED. Then I look through the variables DBSTATUSand I can see the error code DBSTATUS_E_SCHEMAVIOLATION.

What does it mean DBSTATUS_E_SCHEMAVIOLATIONand what do I need to change for it to SetData()work?

If I change the SQL query used in OLEDB, so that instead of executing, SELECTinstead of the UPDATEline, the row selected by the sentence WHERE UPDATEwill be changed correctly. The problem is with the functionality SetData()and binding logic. When I did the same with SQL Server Express, I did not see an error. I see the same error with both SQL Server CE 3.5 and SQL Server Mobile for Visual Studio 2005.

In the output window of the Visual Studio 2005 IDE, I see the following lines. The two rows marked with a sign <<<<< ATLTRACE2are output from the ATLTRACE2 macros to display 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().

. SQL Server CE, , .

// 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;
}
+4
1

MSDN , DBSTATUS_E_SCHEMAVIOLATION ,

.

DBSTATUS_E_SCHEMAVIOLATION .

, , Count, null.

OLE DB Compact Edition SQL Server, , . T-SQL "" "exec". UPDATE.

, . myTable.Open SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103', , . , , , .

+1

Source: https://habr.com/ru/post/1539184/

More articles:


All Articles