Re: [sqlite] LevelDB benchmark

[Afriza N. Arief]

On Fri, Jul 29, 2011 at 8:53 AM, Richard Hipp  wrote:

> On Thu, Jul 28, 2011 at 12:27 AM,  wrote:
>
> > they used
> >
> > CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
> > CREATE INDEX keyindex ON test (key)
> >
>
> Notice the inefficiencies inherent in this schema.
>
> (1) A primary key on a BLOB?  Really?
> (2) They create an redundant index on the primary key.  They would double
> the write performance with no impact on read performance simply be omitting
> the index.
>
> I propose a new set of benchmarks, SQLite vs. LevelDB, with the following
> schema:
>
>   CREATE TABLE test(key INTEGER PRIMARY KEY, value BLOB);
>
> I'm thinking SQLite will do much better in that case, and may well exceed
> the performance of LevelDB in most cases.  (This is a guess - I have not
> actually tried it.)
>
> Of course, if you really do need a blob-to-blob mapping, then I suppose
> LevelDB might be a better choice.  But not many applications do that kind
> of
> thing.  What SQL database (other than SQLite) even allows an index or
> primary key on a blob???


Actually as per their blog-post (
http://google-opensource.blogspot.com/2011/07/leveldb-fast-persistent-key-value-store.html
) .
They probably want to simulate "an ordered mapping from string keys to
string values" and to test "batch updates that modify many keys scattered
across a large key space".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ Sample Code

[Afriza N. Arief]

The official programming API is in C and Tcl.

If you want C++ API, you need to use 3rd party wrappers or write your own.

To use C API, go to "SQLite Programming Interfaces" section at
http://sqlite.org/docs.html
On May 15, 2011 5:39 PM, "Don Ireland" <sql...@donireland.com> wrote:
>
> Thanks for taking the time to respond.  But I'd prefer to learn to use
SQLite.  If I use one of these wrappers, I have to depend on someone to
maintain it anytime SQLite is updated.  And from what I can tell, the last
update to the wrapper code was I n 2007.
>

The official programming API is in C and Tcl.

If you want C++ API, you need to use 3rd party wrappers or write your own.

To use C or Tcl API, go to "SQLite Programming Interfaces" section at
http://sqlite.org/docs.html

If you want C++/CLI API, refer to Microsoft ADO.NET API or Entity Framework.

Regards

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Visual Studio EE 2010

[Afriza N. Arief]

On May 14, 2011 12:31 AM, "Don Ireland" <sql...@donireland.com> wrote:
>
> It compiles fine, but I get a runtime error:
>
> An unhandled exception of type 'System.DllNotFoundException' occured in
System.Data.SQLite.dll
>
> Additional information:  Unable to load DLL 'SQLite.Interop.DLL':  The
specified module could not be found.  (Exception from HRESULT:  0x8007007E)

Then copy 'SQLite.Interop.DLL' as well to project directory.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite Status

[Afriza N. Arief]

On Fri, May 13, 2011 at 3:24 PM, Clay Fowler <paul.clay.fow...@gmail.com>wrote:

> The downloads at
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wikiappear
> broken in various ways:
>
> The precompiled binaries fail to work on desktop from Mono on OS X or on
> Microsoft's CLR from Windows (even with the included test.exe) because they
> attempt to load SQLite.Interop.dll. According to the bundled readme.html,
> this should not happen (it says SQLite.Interop.dll is only needed when
> deploying on the compact framework and that normally only
> System.Data.SQLite.dll itself should be deployed with desktop apps). But
> even with the SQLite.Interop.dll present, it still fails to load.
>

I am able to use the pre-compiled binaries on Windows XP x86 including
test.exe
And it looks like test.exe does require SQLite.Interop.dll

Meanwhile, the source download is broken because core projects within the
> solution are completely empty (no source files), such as the main
> System.Data.SQLite assembly. This is true for both the 2008 and 2010
> solutions in the .zip.
>

I can open SQLite.NET.2010.sln and build the solution except for
the SQLite.Designer
But the error seems to be caused by damaged .NET Framework installation in
my computer.


> So it doesn't appear possible to get anything that works either from binary
> or source at the moment, or I am overlooking something.


You may want to try to reinstall / repair .net framework installation and
then reinstall pre-compiled SQLite.

Reboot your PC if necessary.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Visual Studio EE 2010

[Afriza N. Arief]

On Fri, May 13, 2011 at 10:10 AM, Don Ireland <sql...@donireland.com> wrote:

> I'm using C++/CLI & downloaded/installed the dotnet file at the link you
> provided.
>
> After doing so, I found several DLL files in two folders:  bin & GAD (I
> believe that's the name).
>
> Don Ireland
>
>
Hi,

Try creating a new C++/CLI Console project (
http://img24.imageshack.us/img24/316/cppcliproj.png ) and then paste the
code in the gist ( https://gist.github.com/969954 ).

You need the System.Data.SQLite.dll copied to the project folder.

warning: my first time writing C++/CLI code

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Visual Studio EE 2010

[Afriza N. Arief]

On Thu, May 12, 2011 at 11:18 PM, Don Ireland <sql...@donireland.com> wrote:

> I downloaded and installed sqlite-dotnet-x86-3070600.zip.
>
> But when I attempt to add it as preference in VSEE2010, I find several dll
> files.  But none of them results in me being able to include a SQLite.h
> file.
>
>
What kind of environment you are developing for? Are you doing .NET
development (as in C#, C++/CLI, etc) or native development in C/C++ ?

For C/C++, get the downloads from http://sqlite.org/download.html and for
.NET, get the downloads from
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

It might be easier for .NET development to use the pre-compiled binaries.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Afriza N. Arief]

On May 10, 2011 4:09 PM, "Lynton Grice" <lynton.gr...@logosworld.com> wrote:
>
>  Hi there,
>
> how can I implement /
> mimic a type of "rotating log"?
>
> So in my mind I am thinking that perhaps I can LIMIT the size of the
> SQLIte DB to say 5 MB? And once the DB reaches that size it starts
> INSERTING new logs over the earliest records in the database?
>

What I did was to create a separate thread / process which is suspended most
of the time. Every certain interval, it wakes up and do clean up according
to some conditions.

For my case, it will DELETE records older than 60 days by using the
timestamp column I added. I used REAL for the timestamp as I read somewhere
that it will save space and be faster for sorting. It also used its own DB
connection so SQLite will deal with any concurrency issues.

DELETE FROM logs WHERE timestamp < julianday('now','-60 days')

Alternatively, you can keep the last N number of records, for example 1000.

DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY timestamp
DESC LIMIT 1000,-1)

By issuing DELETE statement, you will actually reuse the storage space for
subsequent new data. Issue VACUUM if you want to slowly reclaim unused
space.

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to detect existing TRANSACTION before performingSAVEPOINT?

[Afriza N. Arief]

On Thu, Mar 17, 2011 at 8:05 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Thu, Mar 17, 2011 at 12:30 AM, Igor Tandetnik <itandet...@mvps.org
> >wrote:
>
> > Afriza N. Arief <afriza...@gmail.com> wrote:
> > > What is the correct way of detecting existing transaction?
> >
> > sqlite3_get_autocommit
> >
>
> Correct.  Or, you could just always use SAVEPOINT which works the same as
> BEGIN if you are not already in a transaction.
>
>
My understanding is that using [SAVEPOINT's automatic] BEGIN is more prone
to deadlocks than BEGIN IMMEDIATE if there are many read-then-write
transactions; and SAVEPOINT only allows automatic BEGIN DEFERRED. This is
because multiple transactions may hold the SHARED locks and multiple of them
may then tries to get RESERVED in the same transaction. I am not using WAL
and want to rely on sqlite3_busy_timeout() instead of using more complex
error handling.

This understanding leads me to always use BEGIN IMMEDIATE for
read-then-maybe-write transactions as well as read-then-always-write
transactions.

Is my understanding correct?

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to detect existing TRANSACTION before performing SAVEPOINT?

[Afriza N. Arief]

Hello,

Currently I am relying on the return code of sqlite3_exec(db,"BEGIN
IMMEDIATE",0,0,0) == SQLITE_ERROR as an indication that there is already a
transaction in progress and then in such cases, I will then call
sqlite3_exec(db,"SAVEPOINT mySavePoint",0,0,0) as a mean of nested
transaction.

Is this good enough? What is the correct way of detecting existing
transaction?


void func(sqlite3* db) {
  int rc, active = 0, savepoint=0;
  rc=sqlite3_exec(db,"BEGIN IMMEDIATE",0,0,0);
  if (rc==SQLITE_OK) {
active=1;
  }
  else if (rc==SQLITE_ERROR) { /* is this a good test condition? */
rc=sqlite3_exec(db,"SAVEPOINT mySavePoint",0,0,0);
if(rc==SQLITE_OK) {
  savepoint = active = 1;
}
  }

  /* do something */

  if (active) {
if (savepoint) {
  rc=sqlite3_exec(db,"RELEASE mySavePoint",0,0,0);
}
else {
  rc=sqlite3_exec(db,"END",0,0,0);
}
  }
}


Similar functions are called outside and inside TRANSACTIONs as well as
SAVEPOINTs.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress

[Afriza N. Arief]

On Sat, Feb 19, 2011 at 6:27 AM, Samuel Adam <a...@certifound.com> wrote:

> A FAQ[2] isn’t enough, as we can see.
>
> To put it another way:  Bug reporters should have probable cause before
> they bug others.  A compiler warning is only a reasonable articulable
> suspicion.  Note that “probable cause” doesn’t imply computer expertise;
> “it crashes” is probable cause.  But a compiler warning only means that a
> dumb piece of melted sand (i.e., a computer) running a static analysis
> suggested there might be perhaps something wrong with it, maybe.  Not that
> any actual misbehavior was observed.  Relying on a compiler warning means
> abdicating wetware to kneel in thrall at the feet of silicon dioxide.
> It’s wrong and it’s stupid.
>
> N.b., I am not accusing hereby Mr. Black of so relying; I just happened to
> reply to his message, because the uninitialized-memory trick seemed
> apropos of his message and I think he as a C coder would duly appreciate
> the argument (whether or not he agrees).  But the original poster, Mr.
> Arief, posted an apparent copy-and-paste of such warnings with aught other
> said but a helpful link to where we can download MSVC Express.  It happens
> here every few months; I am sick of it, ten thousand other list readers
> are probably sick of it, and it peeves the SQLite team sufficiently that
> they have a FAQ[2,idem] on the topic.
>

Please forgive me from being a beginner and I am really sorry if I peeve
SQLite team and list readers. But then, I think the FAQ[2] really needs to
be updated because my original post was motivated by the following sentence:
"Compiler warnings only arise from compilers that the developers do not have
access to."

The above sentence should probably be removed and replaced with "For more
information, please refer to our testing process[3]" linked to the
corresponding web page[3].


> > [snip]
> > If you're going to decide to ignore it then put a comment in the code
> > that says "don't bother to use options X/Y/Z to look for warnings...we
> > have chosen to ignore them".
>
> Much as I regularly take issue with other items therein, when both the
> FAQ[2,idem] and another document thereby referenced[3] explain SQLite’s
> position on compiler warnings, it is futile to expect that somebody who
> missed that would read a source code comment.  Did I mention the
> FAQ[2,supra]?
>
> Very truly,
>

Now, I actually did read Testing Process Page[3] a few months back when I
first knew about SQLite but unfortunately it was not carved into my brain
since I didn't face any warning when compiling SQLite at that time.

I have googled the warning I found and search the mailing list but did not
find satisfying result. During my short time searching, I only found the
FAQ[2] which again, I think should be revised to refer to Testing Process
Page[3].

Did I mention that I actually read a bit of the code around the warning and
thought of a way to fix it? Nah, I believe I haven't mention it. I gave up
because I afraid my fix would actually introduce bugs.

And unfortunately I didn't find/read Testing Process Page[3] during my
recent search.

Thank you,

Afriza N. Arief

[1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg56813.html
> [2] http://www.sqlite.org/faq.html#q17
> [3] http://www.sqlite.org/testing.html#staticanalysis
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiler warnings in R-Tree code under Visual Studio Express

[Afriza N. Arief]

Hi,

Microsoft Visual Studio Express C++ is available for free from
http://www.microsoft.com/express/Downloads/#2010-Visual-CPP

I tried to compile SQLite 3.7.5 with SQLITE_ENABLE_RTREE=1 and got the
following warnings:

sqlite3.c(120736): warning C4244: '=' : conversion from 'double' to 'float',
possible loss of data
sqlite3.c(120749): warning C4244: '+=' : conversion from 'double' to
'float', possible loss of data
sqlite3.c(120834): warning C4244: '=' : conversion from 'double' to 'float',
possible loss of data
sqlite3.c(121803): warning C4244: '+=' : conversion from 'double' to
'float', possible loss of data
sqlite3.c(121804): warning C4244: '+=' : conversion from 'double' to
'float', possible loss of data
sqlite3.c(121808): warning C4244: '=' : conversion from 'double' to 'float',
possible loss of data
sqlite3.c(121815): warning C4244: 'initializing' : conversion from 'double'
to 'float', possible loss of data
sqlite3.c(121914): warning C4244: 'function' : conversion from 'i64' to
'int', possible loss of data
sqlite3.c(121917): warning C4244: 'function' : conversion from 'i64' to
'int', possible loss of data

Thank you,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases

[Afriza N. Arief]

On Tue, Feb 1, 2011 at 3:38 AM, Tito Ciuro  wrote:

> Hello,
>
> The following code snippet runs fine on Mac OS X, but fails on the iOS
> simulator:
>
>// Obtain a path for the database
>NSString *docs =
> [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,
> YES) lastObject];
>NSString *path = [[docs stringByAppendingPathComponent:@
> "myDB.sqlite"]fileSystemRepresentation];
>
>// Open the database
>sqlite3 *db = NULL;
>int statusOpen = sqlite3_open_v2( fileSystemRepresentation, ,
>   SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL);
>

Do you need UTF8String for the sqlite3_open_v2() ?


>
>// Build the first statement
>sqlite3_stmt *oneStatement = NULL;
>const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@,
> %@, %@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue,
> NSFDatatype]UTF8String];
>int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL),
> , );
>
>
>// Build the second statement
>sqlite3_stmt *twoStatement = NULL;
>const char *twoSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@,
> %@, %@, %@) VALUES (?,?,?,?);", NSFKeys, NSFKey, NSFPlist, NSFCalendarDate,
> NSFObjectClass]UTF8String];
>int statusTwo = sqlite3_prepare_v2(db, twoSQL, (int)strlen(twoSQL),
> , );
>
> What I see is that statusTwo returns 1, and I have no idea why. What is
> really puzzling is that if I open the database  in memory or temporary mode,
> it works fine in both Mac OS X and iOS!
>
> So my question I have is, why would the second sqlite3_prepare_v2 statement
> fail only on path-based iOS apps? :-/
>
> Thanks in advance,
>
> -- Tito
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8

[Afriza N. Arief]

@Cory: correct.

Fixed Code:

HWND hWnd = /* from somewhere */;
int nLen = GetWindowTextLength(hWnd) + 1;
WCHAR* lpszText = (WCHAR*)malloc(nLen*sizeof(WCHAR));
// std::wstring str; str.resize(nLen);
nLen = GetWindowText(hWnd,lpszText,nLen);
// str.resize(nLen=GetWindowText(hWnd,[0],nLen));
sqlite3_bind_text16(stmt,1,lpszText,nLen*sizeof(WCHAR),SQLITE_TRANSIENT);
//  (.., str.data(),nLen*sizeof(std::wstring::value_type),SQLITE_TRANSIENT);
free(lpszText); // not needed for std::wstring

When you first time create the database, use sqlite3_open() to store
the data as utf8. I believe bind_text16() will convert from utf16 to
utf8 for you.

See:
- GetWindowTextLength() http://msdn.microsoft.com/en-us/library/ms633521.aspx
- GetWindowText() http://msdn.microsoft.com/en-us/library/ms633520.aspx
- sqlite3_bind_text16() http://www.sqlite.org/c3ref/bind_blob.html

Note:
- You need your project to be compiled in UNICODE

On Fri, Dec 17, 2010 at 12:24 AM, Cory Nelson  wrote:
>
> Just a quick note -- TCHAR is not always UTF-16, and
> sqlite3_bind_text16 takes a void* so it will happily take whatever you
> give it.  You should be using wchar_t directly instead of TCHAR, so an
> error can be caught when you use GetWindowText.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF-8

[Afriza N. Arief]

Try this:

HWND hWnd = /* from somewhere */;
int nLen = GetWindowTextLength(hWnd) + 1;
TCHAR* lpszText = (TCHAR*)malloc(nLen*sizeof(TCHAR)); // std::wstring str;
str.resize(nLen);
nLen = GetWindowText(hWnd,lpszText,nLen); //
str.resize(nLen=GetWindowText(hWnd,[0],nLen));

sqlite3_bind_text16(stmt,1,lpszText,nLen*sizeof(TCHAR),SQLITE_TRANSIENT);
//  (.., str.data(),nLen*sizeof(std::wstring::value_type),SQLITE_TRANSIENT);
free(lpszText); // not needed for std::wstring

When you first time create the database, use sqlite3_open() to store the
data as utf8. I believe bind_text16() will convert from utf16 to utf8 for
you.

See:
- GetWindowTextLength()
http://msdn.microsoft.com/en-us/library/ms633521.aspx
- GetWindowText() http://msdn.microsoft.com/en-us/library/ms633520.aspx
- sqlite3_bind_text16() http://www.sqlite.org/c3ref/bind_blob.html


On Thu, Dec 16, 2010 at 10:51 PM, Pavel Ivanov  wrote:

> > std::string strText = GetWindowsTitle(...);
> > the GetWindowsTitle occupied sometime with the umlaut.
> > so how will you do, if you want save std::string into sqlite with keeped
> > umlaut?
>
>
> Pavel
>
> On Thu, Dec 16, 2010 at 8:40 AM, Ming Lu  wrote:
> > i tried again with the firefox plugin works very well with the umlaut.(i
> > update the feld with a "ä", it can been shown correctly).
> >
> > in my code:
> >
> > std::string strText = GetWindowsTitle(...);
> >
> > the GetWindowsTitle occupied sometime with the umlaut.
> >
> > so how will you do, if you want save std::string into sqlite with keeped
> > umlaut?
> >
> >
> > On 16.12.2010 14:26, Martin Engelschalk wrote:
> >> Hello Ming,
> >>
> >> sqlite does nothing to transform data between codepages, and it assumes
> >> that data you insert is passed in UTF8.
> >> However, sqlite will acept any data and store it.
> >>
> >> If the firefox plugin does not show you data correctly, then you
> >> problably did not pass correct UTF8 to sqlite. Can you check this?
> >>
> >> I just checked the firefox (0.6.5) plugin with my databases, it works
> >> correctly for me.
> >>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A Bug? HAVE_LOCALTIME_S=0 under WinCE causes compile error

[Afriza N. Arief]

A quick fix/work-around will be to add the following at the top of sqlite3.c

#define HAVE_LOCALTIME_S 0
#include 
struct tm *__cdecl localtime(const time_t *t);

- afriza

On Thu, Nov 18, 2010 at 3:38 PM, Afriza N. Arief <afriza...@gmail.com>wrote:

> In my WinCE SDK, HAVE_LOCALTIME_S is defined to 1
>
> This causes a compile error since the headers and libraries don't seem to
> have localtime_s()
>
> I then #define HAVE_LOCALTIME_S 0  but another compile error pops up.
>
> This time the error is because localtime() is used at line 12970 before it
> is actually defined at line 29714. I am using SQLite 3.7.3 amalgamation with
> VS2008 and VS2005.
>
> Regards,
>
> Afriza N. Arief
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A Bug? HAVE_LOCALTIME_S=0 under WinCE causes compile error

[Afriza N. Arief]

In my WinCE SDK, HAVE_LOCALTIME_S is defined to 1

This causes a compile error since the headers and libraries don't seem to
have localtime_s()

I then #define HAVE_LOCALTIME_S 0  but another compile error pops up.

This time the error is because localtime() is used at line 12970 before it
is actually defined at line 29714. I am using SQLite 3.7.3 amalgamation with
VS2008 and VS2005.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] File Locking in WinCE

[Afriza N. Arief]

On Thu, Oct 14, 2010 at 2:41 PM, Afriza N. Arief <afriza@gmail.com>wrote:

> ... sometimes my WinCE application has a problem where the changes made in
> one instance a of sqlite3 database not reflected in the other instance b of
> the same database file even though they exist in the same process.
>
> Thread A:
> initialize sqlite3 instance a
> initialize sqlite3 instance b
> Thread B:
> modify via instance a
> Thread A:
> read via instance b
> Thread B:
> modify via instance a
> Thread A:
> close sqlite3 instance b
> initialize sqlite3 instance b
> read via instance b // the changes from a is sometimes not reflected
>
> I fixed the above problem by using instance a for both Thread A and Thread
> B since they are in the same process and hence eliminating instance b.
>


After exploring more on SQLite documentations, especially
http://sqlite.org/lang_transaction.html and
http://www.sqlite.org/lockingv3.html#transaction_control , it looks like the
problem was caused by pending queries in the database connection. Since
there were some pending/unfinished queries, the implicit transaction in one
db connections keeps the lock(s) and the transaction becomes long lived.
Other connections to the db may not see the changes since they also have
pending queries and thus have long-lived transactions as well.

Here is roughly how my code was:

bool SomeClass::ProductWithTypeExists(const char* type)
{
sqlite3_reset();
sqlite3_bind();
if (sqlite3_step() == SQLITE_ROW)
return true;
return false;
}

and now it becomes

bool SomeClass::ProductWithTypeExists(const char* type)
{
sqlite3_reset();
sqlite3_bind();
bool result = sqlite3_step() == SQLITE_ROW
sqlite3_reset(); // always reset() statements after use to mark it as
finished and allow implicit transaction to end.
return result;
}

Regards,

Afriza
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] handling of BLOB bound parameters

[Afriza N. Arief]

On Thursday, October 14, 2010, Jens Miltner  wrote:
> I just stumbled across a problem where sqlite would be stuck for quite a long 
> time inside sqlite3VdbeExpandSql when using bound BLOB parameters, i.e. my 
> query looks like
>
> INSERT INTO foo VALUES (?,?,?,?...)
>
> and one of the parameters is a BLOB of about 700k.
>
> What I found is that when this query is executed, SQLite will actually 
> produce a string representation of the BLOB,

What SQL statement did you use to create the table and how do you bind the blob?

-afriza
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] File Locking in WinCE

[Afriza N. Arief]

Hi,

When I browse SQLite amalgamation file sqlite3.c , I found the following
comment:

/*

** WinCE lacks native support for file locking so we have to fake it

** with some code of our own.

*/

#if SQLITE_OS_WINCE

typedef struct winceLock {

  int nReaders;   /* Number of reader locks obtained */

  BOOL bPending;  /* Indicates a pending lock has been obtained */

  BOOL bReserved; /* Indicates a reserved lock has been obtained */

  BOOL bExclusive;/* Indicates an exclusive lock has been obtained */

} winceLock;

#endif


and I also see some other functions in sqlite3.c that look like replacements
for LockFile(), UnlockFile() and LockFileEx().

Is there any problem with the existing LockFileEx() <
http://msdn.microsoft.com/en-us/library/ee489737.aspx > and UnlockFileEx()<
http://msdn.microsoft.com/en-us/library/ee490757.aspx >?

I am asking this because sometimes my WinCE application has a problem where
the changes made in one instance a of sqlite3 database not reflected in the
other instance b of the same database file even though they exist in the
same process.

Thread A:
initialize sqlite3 instance a
initialize sqlite3 instance b
Thread B:
modify via instance a
Thread A:
read via instance b
Thread B:
modify via instance a
Thread A:
close sqlite3 instance b
initialize sqlite3 instance b
read via instance b // the changes from a is sometimes not reflected

I fixed the above problem by using instance a for both Thread A and Thread B
since they are in the same process and hence eliminating instance b. but now
I plan open the same database from other process so I am a bit worried about
the concurrency in WinCE. I am using WinCE 6.

Thank you,

Afriza
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users