Subscribe to News
Search by index non correlative columns on an SQL CE data base
Author : Ester Artieda
From TechnologicalWiki
Contents |
[edit] Introduction
This articule describes the way to use indexes to get non correlative colums as a result. This is very useful because we return only the columns we want without taking into account the original distribution or design of the table.
[edit] Background
Data bases are very useful tools when we want to manage structurated information. As it happens on PC, mobile devices have their own data bases. These data bases are smaller than PC's ones and have reduced options (security, multi-user...), but they have the capability of saving structurated data.
Particularly, on this article, we are going to use sql-ce, because is one of the most common data bases on windows mobile devices.
Altought it isn't installed by default on all the versions of windows mobile we can get its dlls and some samples on http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en. One of these samples is the start point to this article, Northwind.sdf.
[edit] Code
This code help us to get only data we need independently of the distribution of columns on the table of the database. The trick is to add a new accessor to get the results which is different to the index accessor.
Initialization of variables:
wstring profileTable::LoadTableInfo(WCHAR* login,wchar_t* table, wchar_t* index, wstring pwszProfiles,int numberOfFields)
{
HRESULT hr = NOERROR; // Error code reporting
DBBINDING *prgBinding = NULL; // Binding used to create accessor
DBBINDING *prgBinding2= NULL; // Binding used to create accessor2
HROW rghRows[1]; // Array of row handles obtained from the rowset object
HROW *prghRows = rghRows; // Row handle(s) pointer
DBID TableID; // Used to open/create table
DBID IndexID; // Used to create index
DBPROPSET rowsetpropset[1]; // Used when opening integrated index
DBPROP rowsetprop[1]; // Used when opening integrated index
ULONG cRowsObtained = 0; // Number of rows obtained from the rowset object
DBOBJECT dbObject; // DBOBJECT data.
DBCOLUMNINFO *pDBColumnInfo = NULL; // Record column metadata
BYTE *pData = NULL; // record data
WCHAR *pStringsBuffer = NULL;
DWORD dwBindingSize = 0;
DWORD dwIndex = 0;
DWORD dwOffset = 0;
DWORD dwOrdinal = 0;
ULONG ulNumCols;
IOpenRowset *pIOpenRowset = NULL; // Provider Interface Pointer
IRowset *pIRowset = NULL; // Provider Interface Pointer
IRowsetIndex *pIRowsetIndex = NULL; // Provider Interface Pointer
IAccessor *pIAccessor = NULL; // Provider Interface Pointer
IAccessor *pIAccessor2= NULL; // Provider Interface Pointer
ILockBytes *pILockBytes = NULL; // Provider Interface Pointer
IColumnsInfo *pIColumnsInfo = NULL; // Provider Interface Pointer
HACCESSOR hAccessor = DB_NULL_HACCESSOR; // Accessor handle
HACCESSOR hAccessor2 = DB_NULL_HACCESSOR; // Accessor handle
Initialize data to search:
wstring resul=L"";
WCHAR* pwszProfile[50]={L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",L"",...,L""};
int endFields=0;
const wchar_t* auxFields=L"";
for (int i=0;i<numberOfFields;i++)
{
endFields=pwszProfiles.find(L";");
wstring auxFields1(pwszProfiles.substr(0,endFields));//=(wchar_t*)malloc(pwszProfiles.substr(0,endFields).length());
auxFields=auxFields1.c_str();
wstring pwszProfiles1(pwszProfiles.substr(endFields+1));
pwszProfiles=pwszProfiles1;
pwszProfile[i]=new WCHAR[lstrlen(auxFields)+1];
wcscpy(pwszProfile[i],auxFields);
}
VariantInit(&rowsetprop[0].vValue);
Create session and set up its properties:
if (NULL == m_pIDBCreateSession)
{
hr = E_POINTER;
goto Exit;
}
// Create a session object
hr = m_pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown**) &pIOpenRowset);
if(FAILED(hr))
{
goto Exit;
}
// Set up information necessary to open a table
// using an index and have the ability to seek.
//
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = table;
IndexID.eKind = DBKIND_NAME;
IndexID.uName.pwszName = index;
Prepare the table to access with an index:
// Request ability to use IRowsetChange interface
//
rowsetpropset[0].cProperties = 1;
rowsetpropset[0].guidPropertySet= DBPROPSET_ROWSET;
rowsetpropset[0].rgProperties = rowsetprop;
rowsetprop[0].dwPropertyID = DBPROP_IRowsetIndex;
rowsetprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rowsetprop[0].colid = DB_NULLID;
rowsetprop[0].vValue.vt= VT_BOOL;
rowsetprop[0].vValue.boolVal= VARIANT_TRUE;
// Open the table using the index
//
hr = pIOpenRowset->OpenRowset( NULL,
&TableID,
&IndexID,
IID_IRowsetIndex,
sizeof(rowsetpropset)/sizeof(rowsetpropset[0]),
rowsetpropset,
(IUnknown**) &pIRowsetIndex);
if(FAILED(hr))
{
goto Exit;
}
// Get IRowset interface
//
hr = pIRowsetIndex->QueryInterface(IID_IRowset, (void**) &pIRowset);
if(FAILED(hr))
{
goto Exit;
}
// Get IColumnsInfo interface
//
hr = pIRowset->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
if(FAILED(hr))
{
goto Exit;
}
Save space to work with data base structure
// Get the column metadata
//
hr = pIColumnsInfo->GetColumnInfo(&ulNumCols, &pDBColumnInfo, &pStringsBuffer);
if(FAILED(hr) || 0 == ulNumCols)
{
goto Exit;
}
// Create a DBBINDING array.
//
dwBindingSize = numberOfFields;
prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
if (NULL == prgBinding)
{
hr = E_OUTOFMEMORY;
goto Exit;
}
// Set initial offset for binding position
//
dwOffset = 0;
// Prepare structures to create the accessor
//
for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
{
if (!GetColumnOrdinal(pDBColumnInfo, ulNumCols, pwszProfile[dwIndex], &dwOrdinal))
{
hr = E_FAIL;
goto Exit;
}
// Prepare structures to create the accessor
//
prgBinding[dwIndex].iOrdinal = dwOrdinal;
prgBinding[dwIndex].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
prgBinding[dwIndex].obLength = dwOffset;
prgBinding[dwIndex].obStatus = prgBinding[dwIndex].obLength + sizeof(ULONG);
prgBinding[dwIndex].obValue = prgBinding[dwIndex].obStatus + sizeof(DBSTATUS);
prgBinding[dwIndex].pTypeInfo = NULL;
prgBinding[dwIndex].pBindExt = NULL;
prgBinding[dwIndex].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
prgBinding[dwIndex].dwFlags = 0;
prgBinding[dwIndex].bPrecision = pDBColumnInfo[dwOrdinal].bPrecision;
prgBinding[dwIndex].bScale = pDBColumnInfo[dwOrdinal].bScale;
switch(pDBColumnInfo[dwOrdinal].wType)
{
case DBTYPE_BYTES: // Column "Photo" binding (BLOB)
// Set up the DBOBJECT structure.
//
dbObject.dwFlags = STGM_READ;
dbObject.iid = IID_ILockBytes;
prgBinding[dwIndex].pObject = &dbObject;
prgBinding[dwIndex].cbMaxLen = sizeof(IUnknown*);
prgBinding[dwIndex].wType = DBTYPE_IUNKNOWN;
break;
case DBTYPE_WSTR:
prgBinding[dwIndex].pObject = NULL;
prgBinding[dwIndex].wType = pDBColumnInfo[dwOrdinal].wType;
prgBinding[dwIndex].cbMaxLen= sizeof(WCHAR)*(pDBColumnInfo[dwOrdinal].ulColumnSize + 1);// +1=>null termination
break;
default:
prgBinding[dwIndex].pObject = NULL;
prgBinding[dwIndex].wType = pDBColumnInfo[dwOrdinal].wType;
prgBinding[dwIndex].cbMaxLen = pDBColumnInfo[dwOrdinal].ulColumnSize;
break;
}
// Calculate new offset
//
dwOffset = prgBinding[dwIndex].obValue + prgBinding[dwIndex].cbMaxLen;
// Properly align the offset
//
dwOffset = ROUND_UP(dwOffset, COLUMN_ALIGNVAL);
}
Prepare structures to create the second accessor:
ULONG ncol;
DBINDEXCOLUMNDESC* pColDesc;
ULONG nPropSet;
DBPROPSET* pPropSet;
hr = pIRowsetIndex->GetIndexInfo(&ncol, // Number of index columns
&pColDesc, // Names of columns
&nPropSet, // Don't care
&pPropSet); // Don't care
if(FAILED(hr))
goto Exit;
prgBinding2 = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING) * ncol);
if(prgBinding2)
{
DWORD dwOffset = 0,
dwOrdinal;
for(int i = 0; i < ncol; ++i)
{
if (!GetColumnOrdinal(pDBColumnInfo, ulNumCols,pColDesc[i].pColumnID->uName.pwszName, &dwOrdinal))
{
hr = E_FAIL;
goto Exit;
}
prgBinding2[i].iOrdinal = dwOrdinal;
prgBinding2[i].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
prgBinding2[i].obLength = dwOffset;
prgBinding2[i].obStatus = prgBinding2[i].obLength + sizeof(ULONG);
prgBinding2[i].obValue = prgBinding2[i].obStatus + sizeof(DBSTATUS);
prgBinding2[i].pTypeInfo = NULL;
prgBinding2[i].pObject = NULL;
prgBinding2[i].pBindExt = NULL;
prgBinding2[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
prgBinding2[i].dwFlags = 0;
prgBinding2[i].wType = pDBColumnInfo[dwOrdinal].wType;
prgBinding2[i].bPrecision = pDBColumnInfo[dwOrdinal].bPrecision;
prgBinding2[i].bScale = pDBColumnInfo[dwOrdinal].bScale;
prgBinding2[i].pTypeInfo = (ITypeInfo*)dwOffset;
switch(prgBinding2[i].wType)
{
case DBTYPE_WSTR:
prgBinding2[i].cbMaxLen = sizeof(wchar_t)*(pDBColumnInfo[dwOrdinal].ulColumnSize + 1);//+1=>nul termination
break;
default:
prgBinding2[i].cbMaxLen = pDBColumnInfo[dwOrdinal].ulColumnSize;
break;
}
// Calculate new offset
//
dwOffset = prgBinding2[i].obValue + prgBinding2[i].cbMaxLen;
// Properly align the offset
//
dwOffset = ROUND_UP(dwOffset, COLUMN_ALIGNVAL);
}
//pData = (BYTE*)CoTaskMemAlloc(dwOffset);
pData = (BYTE*)CoTaskMemAlloc(1000);
if(NULL == pData)
goto Exit;
// Set data buffer to zero
//
memset(pData, 0, dwOffset);
}
Create accessor to seek:
// Get IAccessor interface
//
hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor2);
if(FAILED(hr))
{
goto Exit;
}
// Create accessor.
//
hr = pIAccessor2->CreateAccessor(DBACCESSOR_ROWDATA,
dwBindingSize,
prgBinding,
0,
&hAccessor,
NULL);
if(FAILED(hr))
{
goto Exit;
}
// Create accessor2.
//
hr = pIAccessor2->CreateAccessor(DBACCESSOR_ROWDATA,
ncol,
prgBinding2,
0,
&hAccessor2,
NULL);
if(FAILED(hr))
{
goto Exit;
}
Prepare structure to seek and seek data:
// Set data buffer for seek operation
//
*(DBSTATUS*)(pData+prgBinding2[0].obStatus) = DBSTATUS_S_OK;
wcscpy((WCHAR*)(pData+prgBinding2[0].obValue),login);
*(ULONG*)(pData+prgBinding2[0].obLength) = wcslen((WCHAR*)(pData+prgBinding2[0].obValue))*sizeof(WCHAR);
// Position at a key value within the current range
//
hr = pIRowsetIndex->Seek(hAccessor2, 1, pData, DBSEEK_FIRSTEQ);
if(FAILED(hr))
{
goto Exit;
}
Get data if it has been found:
// Retrieve a row handle for the row resulting from the seek
//
hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);
if(FAILED(hr))
{
goto Exit;
}
if (DB_S_ENDOFROWSET != hr)
{
// Fetch actual data
//
hr = pIRowset->GetData(prghRows[0], hAccessor, pData);
if (FAILED(hr))
{
goto Exit;
}
for(int i=0;i<dwBindingSize;i++)
{
if (DBSTATUS_S_ISNULL != *(DBSTATUS *)(pData+prgBinding[i].obStatus) &&
DBSTATUS_S_OK == *(DBSTATUS *)(pData+prgBinding[i].obStatus))
{
wchar_t* aux=(WCHAR*)(pData+prgBinding[i].obValue);
resul=resul+wstring(aux)+L";";
}
}
}
// Release the rowset.
//
pIRowset->ReleaseRows(1, prghRows, NULL, NULL, NULL);
Free memory:
Exit:
// Clear Variants
//
VariantClear(&rowsetprop[0].vValue);
// Free allocated DBBinding memory
//
if (prgBinding)
{
CoTaskMemFree(prgBinding);
prgBinding = NULL;
}
if (prgBinding2)
{
CoTaskMemFree(prgBinding2);
prgBinding = NULL;
}
// Free allocated column info memory
//
if (pDBColumnInfo)
{
CoTaskMemFree(pDBColumnInfo);
pDBColumnInfo = NULL;
}
// Free allocated column string values buffer
//
if (pStringsBuffer)
{
CoTaskMemFree(pStringsBuffer);
pStringsBuffer = NULL;
}
// Free data record buffer
//
if (pData)
{
CoTaskMemFree(pData);
pData = NULL;
}
// Release interfaces
//
if(pILockBytes)
{
pILockBytes->Release();
}
if(pIAccessor2)
{
pIAccessor2->ReleaseAccessor(hAccessor, NULL);
pIAccessor2->ReleaseAccessor(hAccessor2, NULL);
pIAccessor2->Release();
}
if (pIColumnsInfo)
{
pIColumnsInfo->Release();
}
if(pIRowset)
{
pIRowset->Release();
}
if (pIRowsetIndex)
{
pIRowsetIndex->Release();
}
if(pIOpenRowset)
{
pIOpenRowset->Release();
}
return resul;
}
[edit] References
If you need more info about sql-ce databases you could find it on:
http://nativemobile.blogspot.com/search/label/SQL%20Compact
