Databases on wince: sqlce and sqlite

Recently, due to project requirements, the query of large data volumes on wince4.2, wince5.0, and mobile models is estimated to be between 50-records.
In the past, because the database project of mobile5 was mainly written in C #, and the data volume was tens of thousands of records, in order to ensure that the project would not take a detour in the future, I specifically tested the performance of mobile databases. The following is the work record for this week. For your reference.
1. Since wince4.2 does not support. net when packaging the OS, you can only use C ++ for the convenience of porting to various platforms.
We initially consider using one of sqlce and sqlite. sqlce is ms, which is relatively stable. Sqlite is open-source, easy to use, and fast to query. Therefore, I did a test on both of them.
1. sqlce development environment:
EVC4 + sp4. VS2005. SQL 3.0
1) I first wrote a small program with VS2005 to generate 0.5 million pieces of simulation data. (Install a sqlce package to operate the sdf file in vs2005)
2). If you use sqlce on wince, copy the following files to the windows directory (My wince does not have its own sqlce package ):
Sqlcese30.dll, sqlceqp30.dll, sqlceoledb30.dll, sqlceme30.dll, sqlceer30CN. dll, and register sqlceoledb30.dll to operate the database normally. (Or directly install the SQL cab package)
3). Compile the program under wince to operate the sqlce database.
: CoInitializeEx (NULL, COINIT_MULTITHREADED );
// Register sqlce dll
LRESULT (CALLBACK * lpDllEntryPoint) (); // declare it before the CPP File
// Register
HINSTANCE hLib = LoadLibrary (L "http://www.cnblogs.com/Jade2009/admin/file://windows//sqlceoledb30.dll ");
If (hLib = NULL)
Return;
(FARPROC &) lpDllEntryPoint = GetProcAddress (hLib, _ T ("DllRegisterServer "));
(* LpDllEntryPoint )();
// The sqlce operations in OLEDB are related to some COM
HRESULT hr;
// TODO: Add your control notification handler code here
IDBInitialize * pIDBInitialize;
IDBCreateSession * pIDBCreateSession;
IDBCreateCommand * pIDBCreateCommand;
ICommandText * pICommandText;
IDBProperties * pIDBProperties;
IUnknown * pIUnknown;
DBPROP dbprop [1];
DBPROPSET dbpropset [1];

Hr = CoCreateInstance (CLSID_SQLSERVERCE_3_0, 0, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void **) & pIDBInitialize );

If (FAILED (hr ))
{
Return;
}

Dbprop [0]. dwPropertyID = DBPROP_INIT_DATASOURCE;
Dbprop [0]. dwOptions = DBPROPOPTIONS_REQUIRED;
Dbprop [0]. vValue. vt = VT_BSTR;
Dbprop [0]. vValue. bstrVal = SysAllocString (L "http://www.cnblogs.com/Jade2009/admin/file://db.sdf/"); // Need oleaut32.lib

If (dbprop [0]. vValue. bstrVal = NULL)
{
Return;
}

Dbpropset [0]. guidPropertySet = DBPROPSET_DBINIT; // # define DBINITCONSTANTS
Dbpropset [0]. cProperties = sizeof (dbprop)/sizeof (dbprop [0]);
Dbpropset [0]. rgProperties = dbprop;

Hr = pIDBInitialize-> QueryInterface (IID_IDBProperties, (void **) & pIDBProperties );

If (FAILED (hr ))
{
Return;
}

Hr = pIDBProperties-> SetProperties (sizeof (dbpropset)/sizeof (dbpropset [0]), dbpropset );

If (FAILED (hr ))
{
Return;
}

Hr = pIDBInitialize-> Initialize ();

If (FAILED (hr ))
{
Return;
}

Hr = pIDBProperties-> QueryInterface (IID_IDBCreateSession, (void **) & pIDBCreateSession );

If (FAILED (hr ))
{
Return;
}
Hr = pIDBCreateSession-> CreateSession (NULL, IID_IUnknown, & pIUnknown); // Need uuid. lib
If (FAILED (hr ))
{
Return;
}
Hr = pIUnknown-> QueryInterface (IID_IDBCreateCommand, (void **) & pIDBCreateCommand );
If (FAILED (hr ))
{
Return;
}
Hr = pIDBCreateCommand-> CreateCommand (NULL, IID_ICommandText, (IUnknown **) & pICommandText );
If (FAILED (hr ))
{
Return;
}
Hr = pICommandText-> SetCommandText (DBGUID_ SQL, L "select * from sninfo"); // execute the query
If (FAILED (hr ))
{
Return;
}
Hr = pICommandText-> Execute (NULL, IID_NULL, NULL, NULL );
If (FAILED (hr ))
{
Return;
}

I remember using ADO to operate sqlce on the Internet, but I heard that ms is no longer updated, but oledb is a little more troublesome. This will also help us understand some interfaces of sqlce.

2. Use the sqlite database on wince:
1. Environment: EVC4 + sp4
1). Download the DLL source code of SQLite for Windows CE in the http://sqlite-wince.sourceforge.net.
2) create a "WCE Dynamic-Link Library" project in eVC and name it sqlite.
3) in the following dialog box, select "An empty Windows ce dll project", click FINISH, and then click OK.
4) Copy all *. c *. h *. def in the source code to the project folder.
5) add all files except shell. c and tclsqlite. c to the project.
6) compile and generate sqlite. lib and sqlite. dll.

2. Download the C ++-encapsulated sqlite class from http://softvoile.com/development/cppsqlite3u/( Based on the sqlite. lib generated above)
Generally, we use sqlite databases on windows and import basic data. Copy the database to wince and then perform the operation.
This class is the encapsulated unicode function of sqlite and can be used normally in windows and windows, but note the following:
If you want to use it in windows, because VC6 is a _ MBCS project by default, and if you want to call the unicode function, generate utf16 data for normal display of Chinese Characters in windows, you need to extract the compilation parameters of the project from _ MBCS
Changed to UNICODE _ UNICODE.

3. Now we can operate the sqlite database in evc4:
// Open the database

Cppsqlitedb;
// Open or create a database
Db. open (L "http://www.cnblogs.com/Jade2009/admin/file://ding.db ");

// Create a table
Db.exe cDML (L "create table sninfo (id nchar (12), area nvarchar (6), info nvarchar (60), source nchar (6), target nchar (6 )) ");

// Query
CppSQLite3Query q = db.exe cQuery (L "select * from sninfo where area = 'guangzhou region '");
CString strTemp;
While (! Q. eof ())
{
StrTemp. format (_ T ("% s-% s"), q. fieldValue (0), q. fieldValue (3), q. fieldValue (4), q. fieldValue (1 ));
M_list.AddString (strTemp); // display it in The listbox control
Q. nextRow ();
}
Q. finalize ();

Db. close ();

4. I have used so many database operations on sqlite. I also need to learn more. To use sqlite in wince or mobile, pay attention to the following, do not include Chinese characters in the database path. Otherwise, opening fails. If you must use Chinese characters, convert it to UTF-8. sqlite reads data from the database through UTF-8.

Iii. Postscript
Compared the performance of sqlce and sqlite in querying 50 million records, it can be completed within 2 seconds (I didn't create an index at first, I checked for more than 4 minutes, Dizzy ), you must remember to create an index. Otherwise, query N is slow.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/allenray0202/archive/2009/01/05/3711751.aspx

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/lyx_wq/archive/2009/06/05/4245805.aspx

Category: Wince | added to souzang | browsing (225) | comments (0) Previous: Using WINCE on Windows Mobile... next article: Summary of driver interruptions of Wince 5.0

Recent readers:

After logging on, you will be here.