Selecting Data from SQL CE DB

Dear all,
I'm new to SQl CE 2.0 programming.
We are using eVc++3.0 environment to build the application. We have managed to create DB and then tables and also to insert records into the DB.
But when i tried to retrieve the rows i inserted into the table i was not able to get it.
I tried with the following code and was not able to retrieve the data.

This code irrespective of the query i pass this is returning all the rows present in the table.
How could i retireve the data returned by the query i passed in the setcommandtext.
Can i get some sample code for the same

hr = CoCreateInstance( CLSID_SQLSERVERCE_2_0,
0,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void**)&pIDBInitialize);
if(FAILED(hr))
{
goto Exit;
}

// Initialize a property with name of database
//
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal= SysAllocString(L"\\Storage Card\\My Documents\\SQLDataBasetestnew.sdf");
if(NULL == dbprop[0].vValue.bstrVal)
{
hr = E_OUTOFMEMORY;
goto Exit;
}

// Initialize the property set
//
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = dbprop;
dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);

//Set initialization properties.
//
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if(FAILED(hr))
{
goto Exit;
}

// Sets properties in the Data Source and initialization property groups
//
hr = pIDBProperties->SetProperties(1, dbpropset);
if(FAILED(hr))
{
goto Exit;
}

// Initializes a data source object
//
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
goto Exit;
}

// Get IDBCreateSession interface
//
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,(void**)&pIDBCreateSession);

if(FAILED(hr))
{
goto Exit;
}

hr = pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown**)&pIOpenRowset);
if(FAILED(hr))
{
goto Exit;
}

hr = pIOpenRowset->QueryInterface(IID_ITransactionLocal, (void**)&pITxnLocal);
if(FAILED(hr))
{
goto Exit;
}

hr = pIOpenRowset->QueryInterface(IID_IDBCreateCommand, (void**) &pIDBCrtCmd);
if(FAILED(hr))
goto Exit;

// Create a command
//
hr = pIDBCrtCmd->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICmdText);
if(FAILED(hr))
goto Exit;


//hr = pICmdText->SetCommandText(DBGUID_SQL, L"Select test.col1, test2.col1 from test inner join test2 on test.col1 = test2.col1");
hr = pICmdText->SetCommandText(DBGUID_SQL, L"Select SubPcNo from item where itemcode = 10000296");
if(FAILED(hr))
goto Exit;

//hr = pICmdText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown **) &m_pIRowSet);
hr = pICmdText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown **) &m_pIRowSet);
if(FAILED(hr))
goto Exit;


TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = L"Item";

IndexID.eKind = DBKIND_NAME;
IndexID.uName.pwszName = L"PK_Item";

// Request ability to use IRowsetIndex interface
rowsetpropset[0].cProperties = 2;
rowsetpropset[0].guidPropertySet = DBPROPSET_ROWSET;
rowsetpropset[0].rgProperties = rowsetprop;

rowsetprop[0].dwPropertyID = DBPROP_IRowsetIndex;
rowsetprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rowsetprop[0].vValue.vt = VT_BOOL;
rowsetprop[0].vValue.boolVal = VARIANT_TRUE;

rowsetprop[1].dwPropertyID = DBPROP_IRowsetChange;
rowsetprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rowsetprop[1].vValue.vt = VT_BOOL;
rowsetprop[1].vValue.boolVal = VARIANT_TRUE;

hr = pIOpenRowset->OpenRowset(NULL, &TableID, &IndexID, IID_IRowset, 1, rowsetpropset, (IUnknown**)&m_pIRowSet);
if(FAILED(hr))
goto Exit;

hr = m_pIRowSet->QueryInterface(IID_IRowsetIndex, (void**)&pIRowsetIndex);
if(FAILED(hr))
goto Exit;

hr = m_pIRowSet->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
if(FAILED(hr))
{
goto Exit;
}

// Get the column metadata
//
hr = pIColumnsInfo->GetColumnInfo(&ulNumCols, &pDBColumnInfo, &pStringsBuffer);
if(FAILED(hr) || 0 == ulNumCols)
{
goto Exit;
}
dwBindingSize = ulNumCols - 1;
rgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
if (NULL == rgBinding)
{
hr = E_OUTOFMEMORY;
goto Exit;
}

// Set initial offset for binding position
//
dwOffset = 0;
for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
{
rgBinding[dwIndex].iOrdinal = pDBColumnInfo[dwIndex + 1].iOrdinal;
rgBinding[dwIndex].pTypeInfo = NULL;
rgBinding[dwIndex].pBindExt = NULL;
rgBinding[dwIndex].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBinding[dwIndex].dwFlags = 0;
rgBinding[dwIndex].bPrecision = pDBColumnInfo[dwIndex + 1].bPrecision;
rgBinding[dwIndex].bScale = pDBColumnInfo[dwIndex + 1].bScale;
rgBinding[dwIndex].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBinding[dwIndex].obLength = dwOffset;
rgBinding[dwIndex].obStatus = rgBinding[dwIndex].obLength + sizeof(ULONG);
rgBinding[dwIndex].obValue = rgBinding[dwIndex].obStatus + sizeof(DBSTATUS);

switch(pDBColumnInfo[dwIndex + 1].wType)
{
case DBTYPE_WSTR:
rgBinding[dwIndex].pObject = NULL;
rgBinding[dwIndex].wType = pDBColumnInfo[dwIndex + 1].wType;
rgBinding[dwIndex].cbMaxLen = sizeof(WCHAR)*(pDBColumnInfo[dwIndex + 1].ulColumnSize + 1); // Extra buffer for null terminator
break;

default:
rgBinding[dwIndex].pObject = NULL;
rgBinding[dwIndex].wType = pDBColumnInfo[dwIndex + 1].wType;
rgBinding[dwIndex].cbMaxLen = pDBColumnInfo[dwIndex + 1].ulColumnSize;
break;
}
dwOffset = rgBinding[dwIndex].obValue + rgBinding[dwIndex].cbMaxLen;

// Properly align the offset
//
dwOffset = ROUND_UP(dwOffset, COLUMN_ALIGNVAL);
}

// Get IAccessor
//
//hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
hr = m_pIRowSet->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
if(FAILED(hr))
goto Exit;

hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
dwBindingSize,
rgBinding,
0,
&hAccessor,
NULL);
if(FAILED(hr))
goto Exit;
*(DBSTATUS*)(pBeginKeyData+rgBinding[0].obStatus) = DBSTATUS_S_ISNULL;

// Set up data buffer for seek operation
//
*(ULONG*)(pEndKeyData+rgBinding[0].obLength) = 4;
*(DBSTATUS*)(pEndKeyData+rgBinding[0].obStatus) = DBSTATUS_S_OK;
*(LONG*)(pEndKeyData+rgBinding[0].obValue) = 3; // Value used to seek

// Set the index key range to begin with NULL and end with less than 3
//
hr = pIRowsetIndex->SetRange(hAccessor, 1, pBeginKeyData, 1, pEndKeyData, DBRANGE_EXCLUSIVEEND);
if(FAILED(hr))
goto Exit;
hr = m_pIRowSet->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);

if ( !FAILED( hr))
{
if ( DB_S_ENDOFROWSET != hr)
{
// Allocate record data buffer
//
pDataRecord = (BYTE *) CoTaskMemAlloc( rgBinding[dwIndex].obLength + sizeof(ULONG) + sizeof(LONG));
if ( NULL == pDataRecord)
{
hr = E_OUTOFMEMORY;
goto Exit;
}

// Fetch every row until end of rowset
//
do
{
++ulRow;
wcscpy(wszOutput,L"");
// Fetch actual data
hr = m_pIRowSet->GetData( prghRows[0], hAccessor, pDataRecord);

// hr = pICmdText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown **) &m_pIRowSet);
if ( SUCCEEDED( hr))
{
for (dwCol = 0; dwCol < dwBindingSize; ++dwCol)
{
if ( DBSTATUS_S_ISNULL == *(DBSTATUS *)(pDataRecord+rgBinding[dwCol].obStatus))
{
// Column is NULL
wsprintf( wszTemp, L"Row %lu=NULL\n", ulRow);
}
else
{
wsprintf( wszTemp, L"Row %lu=%ld\n", ulRow,*(int *)(pDataRecord+rgBinding[dwCol].obValue));
//*(LONG *)(pDataRecord+rgBinding[0].obValue));
}
wcscat( wszOutput, wszTemp);
}


}

// Release row handle because we can one outstanding row handle at a time
//
hr = m_pIRowSet->ReleaseRows(1, prghRows, NULL, NULL, NULL);
if(FAILED(hr))
goto Exit;

hr = m_pIRowSet->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);
} while ( !FAILED( hr) && DB_S_ENDOFROWSET != hr);

wsprintf( wszTemp, L"\nNumber of rows fetched=%lu", ulRow);
wcscat( wszOutput, wszTemp);

MessageBox( NULL, wszOutput, L"SQL CE SEEK", MB_OK);
}
}



From http://www.developmentnow.com/g/97_0_0_0_0_0/sql-server-ce.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
.

Relevant Pages

  • Cannot access ISSCEErrors object for more error info
    ... I have a simple Pocket PC app written in C++ on eVC that has a stand ... The native error for a corrupt DB is ... The error occurs as I open the DB during the Initialize: ...
    (microsoft.public.sqlserver.ce)
  • Re: SQL Server CE 2.0 and Memory
    ... my Initialize code. ... free the memory afterwards. ... I found that documentation for that for OLEDB was pretty poor. ...
    (microsoft.public.sqlserver.ce)
  • Re: Selecting Data from SQL CE DB
    ... If I was your manager I would get you to switch to a ... Hitchhiker's Guide to Visual Studio and SQL Server ... // Initialize a property with name of database ...
    (microsoft.public.sqlserver.ce)
Loading