Re: [sqlite] System.Data.SQLite blob column exception

[Robert Simpson]

So you're always wanting to call GetBytes() even if the column is text?
What about calling GetFieldType() beforehand to determine whether or not to
call GetString() or GetBytes()?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
Sent: Friday, May 06, 2011 9:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite blob column exception

I am using a DbDataReader to return the columns for a SQL  SELECT
statement.  For the blob column, I am calling reader.GetOrdinal to get
the column number, calling reader.GetBytes to get the size of the
data, allocating the byte[], and then calling reader.GetBytes to read
into the buffer.

Tim

On Fri, May 6, 2011 at 11:12 AM, Robert Simpson <sqlite_l...@bcsft.com>
wrote:
> How are you fetching the data?  Are you using the generic reader[x] on a
> datareader?  Are you using a DataSet/DataTable?
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
> Sent: Friday, May 06, 2011 8:58 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] System.Data.SQLite blob column exception
>
> On Fri, May 6, 2011 at 7:44 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>>
>>> The blob values are always inserted and read as a byte[].  If those
>>> bytes are those of a text file, it fails.  If I add a leading 0x00
>>> byte to force it to be binary, it works.
>>
>> Are you binding your parameters ?  If so, check that you're using
> sqlite3_bind_blob() when binding something to your BLOB column.  Binding
it
> as text will not trigger an error, since SQLite is quite happy to bind
text
> into a BLOB column.  But if you take blobby data, and bind it using
> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
> retaining and reporting it as as blob, not as text.
>>
>> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
> binding parameters, them make sure you're using the correct format to
> specify you have blob data (is it \x ?) rather than just supplying text.
>
> I'm using the System.Data.SQLite interface and not the direct C
> interface methods.  All of my inserts are done using DbParameter (@p1,
> @p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
> the C# object type.  In the case of the blob column, that type is a C#
> byte[], which sets the parameter type to DbType.Binary whether the
> first byte is 0x00 or a more textual value.
>
> Tim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Tim Butterfield
http://www.timbutterfield.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] System.Data.SQLite blob column exception

[Robert Simpson]

How are you fetching the data?  Are you using the generic reader[x] on a
datareader?  Are you using a DataSet/DataTable?  


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
Sent: Friday, May 06, 2011 8:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite blob column exception

On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>
> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>
>> The blob values are always inserted and read as a byte[].  If those
>> bytes are those of a text file, it fails.  If I add a leading 0x00
>> byte to force it to be binary, it works.
>
> Are you binding your parameters ?  If so, check that you're using
sqlite3_bind_blob() when binding something to your BLOB column.  Binding it
as text will not trigger an error, since SQLite is quite happy to bind text
into a BLOB column.  But if you take blobby data, and bind it using
sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
retaining and reporting it as as blob, not as text.
>
> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
binding parameters, them make sure you're using the correct format to
specify you have blob data (is it \x ?) rather than just supplying text.

I'm using the System.Data.SQLite interface and not the direct C
interface methods.  All of my inserts are done using DbParameter (@p1,
@p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
the C# object type.  In the case of the blob column, that type is a C#
byte[], which sets the parameter type to DbType.Binary whether the
first byte is 0x00 or a more textual value.

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


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

Re: [sqlite] Sqlite .NET 4.0 provider available?

[Robert Simpson]

SQLite.NET is being transitioned to sqlite.org ... you'll notice the new
site is http://system.data.sqlite.org  -- it's being taken over by the fine
folks at sqlite.org, specifically Shane Harrelson who has the lead on the
project.  I've been working with Dr. Hipp and Shane on the transition.

I'll still be contributing to the codebase.

Robert

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Wednesday, February 16, 2011 3:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite .NET 4.0 provider available?


On 16 Feb 2011, at 9:08am, Matthew Adams wrote:

> On Tue, Feb 15, 2011 at 5:20 PM, Simon Slavin 
wrote:
> 
>>> When is official support for .NET 4.0 expected?
>> 
>> 'official' from the SQLite team ?  Never.  SQLite's official support is
for two sets of source code written in C.  No other forms of support are
provided, not for any specific platform or variation on C (e.g. C++,
Objective-C, C#).  A great deal of work goes into making sure that the C
source code compiles acceptably with a /lot/ of different C compilers on a
lot of different platforms so that other teams can easily build it into
their own projects, either prepare libraries from it.
>> 
> 
> I would have expected whomever the code is being transitioned to to be
> the group that is actively developing the .NET provider.  Since I'm
> new to the SQLite ecosystem, I'm not sure who that is, and it sounds
> like you're saying that the SQLite team is not maintaining it or
> enhancing it.  If that's true, who is?

You got it.  Developers of various different libraries toolkits and apps are
each free to update to newer versions of SQLite whenever they want, or to
skip versions, or not to update at all.  If you want to find out who is
working on a .NET provider (and I'll bet there's more than one) then look up
the name of that provider, not SQLite.  Googling on

sqlite .net provider

found me some useful-looking sites, it looks like at least two different
projects.  So the answer to your original question is "There are at least
two of them.".  But I know nothing about .NET so I don't know how to take it
further.

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


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

Re: [sqlite] SQLite 3.7.x for Pocket PC 2003 SDK (VS2005) does not compile

[Robert Simpson]

There have been several posts on this issue just this week, and suggested
workarounds.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joachim Wickman
Sent: Wednesday, August 25, 2010 12:37 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite 3.7.x for Pocket PC 2003 SDK (VS2005) does not
compile

 Hello,

FYI, Compiling for Pocket PC 2003 stopped to work since version 3.7.1
and 3.7.2. So using 3.6.23.1 for now until it gets fixed.


1>Compiling...
1>sqlite3.c
1>..\..\Common\sqlite3\sqlite3.c(30471) : error C2065:
'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier
1>..\..\Common\sqlite3\sqlite3.c(30478) : warning C4013: 'UnlockFileEx'
undefined; assuming extern returning int

Br,
Joachim Wickman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Proposed new sqlite3_open_v3() interface

[Robert Simpson]

My only request would be that you add some kind of flag that allows us to
specify opening the database in UTF-16 mode via this interface.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Monday, May 03, 2010 7:48 AM
To: sqlite-...@sqlite.org; General Discussion of SQLite Database
Subject: [sqlite] Proposed new sqlite3_open_v3() interface

Community feedback is requested for the following proposed new SQLite  
C API:

int sqlite3_open_v3(const char*, sqlite3**, int, const char*);

The new database connection constructor would work exactly like  
sqlite3_open_v2() with the following exceptions:

(1) Foreign Key constraints would be enabled by default.
(2) Recursive triggers would be enabled by default.
(3) The default database file format would be format 4 (meaning that  
new databases would be unreadable by versions of SQLite prior to 3.1.3).
(4) The misfeature of allowing double-quoted identifiers to fall back  
to being strings if no matching identifier could be found would be  
disabled.
(5) PRIMARY KEY columns would automatically be NOT NULL.  (The would  
not change the current INTEGER PRIMARY KEY behavior, only non-INTEGER  
PRIMARY KEYs.)

We are very hesitant to make any of the above changes to SQLite for  
the default case since they run the risk of breaking legacy code.  By  
making the newer behavior the default only for the new interface  
sqlite3_open_v3() and leaving the behavior unchanged for legacy  
constructors, we hope to encourage newer applications to use the newer  
features while avoiding breakage of older applications.

Question 1:  Are there any objections to this approach?

Question 2:  Are there other foibles that we could correct using  
sqlite3_open_v3?

D. Richard Hipp
d...@hwaci.com



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


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

Re: [sqlite] Question on the VdbeCursor structure changes

[Robert Simpson]

I was using it to get the rowid of a given cursor in a SQLite statement.

Given a table schema like CREATE TABLE foo (A,B)

And an arbitrary select such as SELECT * FROM foo

I was able to return the rowid as a hidden column for the statement.  This
included statements with multiple cursors (as a result of multiple joins).


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Saturday, April 17, 2010 11:08 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Question on the VdbeCursor structure changes


On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:

> Due to changes in VdbeCursor structure, this code for 3.6.16 won't  
> compile
> anymore:
>
> else if(pC->pseudoTable)
> {
>  *prowid = pC->iKey;
> }
>

Your application should not be messing with internal data structures  
of SQLite, all of which are subject to change without notice (as you  
have discovered.)

Perhaps if you explain to us what you are trying to accomplish we will  
be better able to help you.


> and for 3.6.23 would have to be replaced by something like this:
>
> else if(pC->pseudoTableReg>0)
> {
>  //*prowid = 
> }
>
> My problem is that after about an hour of reverse engineering I can't
> figure out a way to get the row id with the new code. Any help  
> appreciated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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


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

Re: [sqlite] Locking under various Windows versions

[Robert Simpson]

WinCE has no lockfile support, so it's fudged on the device by means of a
shared memory block to handle the locking.  If you open a SQLite database on
a network share from a CE device, then it will not be able to use the
network share's locking mechanisms.  In short, don't do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Noah Hart
Sent: Friday, April 16, 2010 3:48 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Locking under various Windows versions

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose, or take any action based on this message or
any information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. Thank
you for your cooperation.


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


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

Re: [sqlite] SQLite in C# .NET

[Robert Simpson]

This has been asked and answered several times in the forums:

http://sqlite.phxsoftware.com/forums


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew Pulis
Sent: Wednesday, November 11, 2009 6:40 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite in C# .NET

 Hi guys,

I am trying to do a small GIS for a mobile application. My application is
targeting Windows Mobile 6 Professional Edition. I am using CF 3.5  of .NET.


I have included the .dll from here:
http://freefr.dl.sourceforge.net/project/sqlite-dotnet2/SQLite%20for%20ADO.N
ET%202.0/1.0.65.0/SQLite-1.0.65.0-managedonly-binaries.zip
and
have included the System.Data.SQLite.dll and added the System.Data.SQLite
directive in my source code.

However when executing I am getting: Can't find PInvoke DLL
'SQLite.Interop.065.DLL'.


Any idea how to fix this please? Am I getting the right DLL and class?


Using this:

 using (SQLiteConnection connection = new SQLiteConnection
(connectionString))
{
connection.Open();

using (SQLiteCommand command =
connection.CreateCommand())
{
command.CommandText = @"SELECT load_extension
('libspatialite-1.dll');";
command.ExecuteScalar();

command.CommandText = "select name from
malta_speedcameras where oid=1";
lbCameraPosition.Text = (string)
command.ExecuteScalar();
}
}

Thanks :)

-- 
Matthew Pulis
URL : http://www.matthewpulis.info | http://www.solutions-lab.net
MSN : pulis_matth...@]hotmail.com
ICQ : 145951110
Skype : solutions-lab.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SQLite Encryption Extensions(SEE) for SQLite

[Robert Simpson]

If there were problems, then Dr. H wouldn't sell it ...

The SEE extensions add a couple new API calls, specifically sqlite_key() and
rekey() functions to specify the password (or change the password) for an
encrypted database.  Other than that, your code remains the same.

I'm pretty sure the license requires that you compile with extensions with
your app, or at the very least, if compiled in a library, your library wraps
your specific implementation.  In otherwords, no compiling a generic sqlite
library with SEE extensions and handing it out so everyone can use SEE for
free.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andi Suhandi
Sent: Saturday, October 03, 2009 11:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite Encryption Extensions(SEE) for SQLite

Dear All

Hi, I was using sqlite as a database for my program. My program is
based on C++ . Well, for my next project I want encryt the database,
and I read about SQLite Encryption Extensions(SEE).
My questions :
1. Are there problems if I add SEE ?
2. Do I have change a lot in my program ?
3. How to add SEE to my source code (including header, dll, etc that I
have to add and the function that I call sequencially)

Thank you
-- 
Andi Suhandi
Software Engineer Support
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Code problem

[Robert Simpson]

You've got single quotes in the body of your text that need to be doubled:

'Rule 404. Character Evidence not Admissible to  
Prove Conduct; Exceptions; Other Crimes(A)  
Character evidence generally. Evidence of a person'[<-- right here]s
character  .




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jimmy Verner
Sent: Sunday, September 20, 2009 4:30 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Code problem

I can't add this record (and several others) to my table.  Most of the  
records did load, both before and after this one.

If anyone can let me know what the problem is, I would be most  
grateful.  Here is the table setup:

CREATE TABLE "rules" ("number" NUMERIC PRIMARY KEY  NOT  
NULL ,"section" TEXT NOT NULL ,"type" CHAR NOT NULL ,"sub-section"  
TEXT,"title" TEXT NOT NULL ,"details" TEXT);

Here is the record:

INSERT INTO rules VALUES(404,'Article4','T','','Character Evidence Not  
Admissible to Prove Conduct; Exceptions; Other  
Crimes','Rule 404. Character Evidence not Admissible to  
Prove Conduct; Exceptions; Other Crimes(A)  
Character evidence generally. Evidence of a person's character  
or a trait of character is not admissible for the purpose of proving  
action in conformity therewith on a particular occasion, subject to  
the following exceptions:(1) Character of accused.  
Evidence of a pertinent trait of character offered by an accused, or  
by the prosecution to rebut the same is admissible; however, in  
prosecutions for rape, gross sexual imposition, and prostitution, the  
exceptions provided by statute enacted by the General Assembly are  
applicable.(2) Character of victim. Evidence of a  
pertinent trait of character of the victim of the crime offered by an  
accused, or by the prosecution to rebut the same, or evidence of a  
character trait of peacefulness of the victim offered by the  
prosecution in a homicide case to rebut evidence that the victim was  
the first aggressor is admissible; however, in prosecutions for rape,  
gross sexual imposition, and prostitution, the exceptions provided by  
statute enacted by the General Assembly are applicable.(3)  
Character of witness. Evidence of the character of a witness on  
the issue of credibility is admissible as provided in Rules 607, 608,  
and 609.(B) Other crimes, wrongs or acts. Evidence of  
other crimes, wrongs, or acts is not admissible to prove the character  
of a person in order to show action in conformity therewith. It may,  
however, be admissible for other purposes, such as proof of motive,  
opportunity, intent, preparation, plan, knowledge, identity, or  
absence of mistake or accident.');

TIA

JV

Jimmy Verner
www.vernerlegal.com



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


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

Re: [sqlite] How secure is encrypted sqlite database

[Robert Simpson]

The .NET provider uses RC4 encryption.  The entire file is encrypted,
including the metadata.  SQLite (the engine) may store unencrypted pages in
its internal cache, but I don't really know for sure.

The bottom line is that nothing is safe from a hacker who has your binaries
and database on his/her computer and is determined to read your data.

It is impossible to write an unhackable program that is designed to run on a
local computer.  Anytime a hacker has access to a debugger and
locally-running code, you are vulnerable in some fashion or another.

One could write a very simple MIIM (Man in the middle) attack by renaming
the sqlite DLL to something else and writing their own DLL that provides all
the same exports.

Heck, SQLite being open-source, one can just as easily recompile it and put
their own debugging code in it and trace all the calls in and out.

The best you can hope for is to eliminate the casual prying eyes.  You can't
shut out a determined hacker.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ram G
Sent: Tuesday, July 28, 2009 1:33 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How secure is encrypted sqlite database

Hi
I have modified (credit goes to System.Data.SQLite) SQlite library to
encrypt the database. File is encrypted and data insert/update/retrieval
works fine.

The question I have is, how secure is the encrypted database. Please correct
me if I am wrong, SQLite reads the file and stores some of the data pages in
memory. In the case of an encrypted database, the data cached in memory
pages is encrypted or clear text?

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


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

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

[Robert Simpson]

I believe the issue was resolved in this ticket:

http://www.sqlite.org/cvstrac/tktview?tn=3387

You're being bitten by Vista and Win2008's aggressive cacheing of the
database.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mark Spiegel
Sent: Wednesday, July 08, 2009 2:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repost: Accessing a DB while copying it causes Windows
to eat virtual memory


Stan Bielski wrote:
> Hello again,
>
> Copying the database with Explorer and accessing it via sqlite is just
> a contrived example that exhibits the same problem I'm having in my
> application. The app does make a copy of the DB, but it has app-layer
> locking that will prevent modifications unless someone decides to
> start fiddling with the DB outside of my software (in which case I
> have bigger problems). The app is multi-threaded, and a thread other
> than the copying thread may attempt to open the DB and read from it
> while the copy is occurring.
>
> My contrived example aside, I just discovered that issuing the VACUUM
> command on the same 20 GB DB in sqlite3 causes similar memory issues,
> even when another process is not accessing the database file.
> sqlite3.exe has a peak working set of 40 MB in Task Manager, but
> Resource Monitor reports 99% Used Physical Memory (of 4 GB). If it
> were all buffer cache, I'd expect that simply copying the file would
> result in the same amount of memory being used, but it doesn't. I'm
> going to head to a Windows forum to try to find out more about what's
> happening, but the list users may want to be aware of this if they
> plan on using large sqlite DBs with Windows 2008.
>
> Thanks,
> -Stan
>
>
>   

Maybe. 

First, forget what I said about mapping the file.  That didn't make 
sense just minutes after I hit the send button.  I was a few cups of 
coffee shy of fully awake.

I just haven't done enough work with Win 2008 yet to be able to say with 
certainty what is going on.  However, here are a couple more things to 
consider.  First, it used to be in windows that the amount of address 
space (and RAM) that could be used for various things was fixed.  This 
is no longer the case.  What you _may_ be seeing is that in trying to 
help, Windows is allocating as much address space (and RAM) as it can to 
the cache manager.  The file is not opened for unbuffered access so NTFS 
is going to try to use the cache manager on the file.  I don't know if 
any of the user mode tools will tell you this.  If you have a kernel 
debugger attached to the machine in this state, the "!VM" command 
_might_ shed light on how much address space is allocated for what.

Second, it occurred to me that in the nominal copy case where you don't 
see a lot of RAM being consumed, explorer, knowing that it is just 
sequentially copying a file, may have it opened for unbuffered access 
for both source and destination.  This would bypass the cache manager 
completely.  I've implemented copy this way in the past to prevent some 
of the adverse effects of large copies on the system, but I have no idea 
if explorer would use this technique.  The downside is that for files 
already in use, copy can be a bit slower.  You can find out with filemon.

Wish I could offer more in the way of a solution...


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


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

Re: [sqlite] ATTACH Database

[Robert Simpson]

What is D2.Table?

Also what is the _fileName referred to in the code?

And finally ... what platform?  Mono or Windows?  I'm just wondering about
the use of slashes instead of backslashes in the attach filename.

Oh, and http://sqlite.phxsoftware.com is more suited for handling
.Net-related questions on the SQLite engine.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carlton Guc
Sent: Monday, June 29, 2009 9:54 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] ATTACH Database

ATTACH database

I have a unique problem where ATTACH database seems to work fine when I'm
using a GUI tool, however when I pass the SQL string to the SQLite engine
using SqLiteCommand under .NET, it doesn't seem to work.I get an error
saying that D2.Table is not found.

SQL String:

ATTACH DATABASE 'F:/Users/name/Documents/ShowBuilder6/Database/2.sbx' AS
XYZ;
SELECT * from [EquipmentCatalog] union select * from XYZ.EquipmentCatalog


Code:
// SQLite database builder
using (SQLiteConnection cn = new
SQLiteConnection(SBConnection.GetDatabaseConnection(_fileName)))
{
cn.Open();
SQLiteCommand cmd;

ResourceManager resourceManager = new
ResourceManager("ShowBuilder6.Library.Properties.StoredProcedures",
Assembly.GetExecutingAssembly());
for (int i = versions.IndexOf(_currentVersion); i <
versions.Count - 1; i++)
{
try
{
cmd = new
SQLiteCommand(resourceManager.GetString(///  the above Attach string, cn);
cmd.ExecuteNonQuery();
}
catch
{
}
}
}


Does anyone have the "ATTACH" command working from a .NET interface?
Again, I can get it to work using SQLite Developer, but doesn't seem to work
in code.

Thoughts?

Carlton
SR




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


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

Re: [sqlite] Two simple questions

[Robert Simpson]

You'll have to call sqlite3_column_name() in a loop to find the named column
returned from a prepared select statement.

As for the default values, to get that information you'll have to execute a
"PRAGMA TABLE_INFO(tablename)" query and examine the 5th column "dflt_value"
for the row matching the column name you're interested in.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Wednesday, June 24, 2009 9:18 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Two simple questions

Just two quick and simple questions :)

 

In a prepared statement, is there a quick way to
determine what the index of a column with a specific name is or must I
just iterate through all columns and perform a string comparison?

 

How can I go by determining the default value of a
column?

 

-Shaun

 

 

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


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

Re: [sqlite] sqlite Exception System.InvalidCastException: Specified cast is not valid.

[Robert Simpson]

The best place to get answers to .NET-related issues with the
System.Data.SQLite provider is:
http://sqlite.phxsoftware.com

I've got plenty of forums over there so we don't clutter up the list with
wrapper issues unrelated to the engine itself.

Robert Simpson


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joel Lucsy
Sent: Wednesday, June 17, 2009 5:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite Exception System.InvalidCastException:
Specified cast is not valid.

On Wed, Jun 17, 2009 at 2:18 AM, Seysan<afsh...@gmail.com> wrote:
> I get the Exception there!  sqlite Exception
> System.InvalidCastException: Specified cast is not valid.
>
> The GetFieldType Shows: Systes.Byte[]

Couldn't find my code where I've done it at. Tho I did find that I
used a ExecuteScalar() that returns byte[].
Could it be that the field is null?
Also, what version of System.Data.Sqlite.dll are you running? I use
1.0.60 but haven't tested 1.0.61 yet.
-- 
Joel Lucsy
"The dinosaurs became extinct because they didn't have a space
program." -- Larry Niven
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SPAM: SQLLite support for Silverlight

[Robert Simpson]

It can't be done, sorry.  There's no support for P/Invoke in Silverlight
(requires full trust).  Since SQLite is a native library, there's no way to
use it without p/invoke unless someone took the entire engine and rewrote it
in fully managed code.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Sync
Sent: Thursday, March 05, 2009 10:57 AM
To: sqlite-...@sqlite.org; sqlite-users@sqlite.org
Subject: SPAM: [sqlite] SQLLite support for Silverlight

Hello,

Is there any way to support SQLLite in Silverlight? Let's say I have a
SQLLite database in Isolated Storage and want to connect that database from
Silverlight without using any service.

Do we need to create our own database driver? I'm also a developer but I'm
not familiar with that field so that I'm looking for some helps from you
guys. It would be really great if you guys can share me some concepts or
links or code or etc.

Thanks in advance.

Regards,
Michael Sync

-- 
Don't go the way life takes you.
Take life the way you go

http://michaelsync.net



-- 
Don't go the way life takes you.
Take life the way you go

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


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

Re: [sqlite] SPAM: Database path in widows

[Robert Simpson]

The best place to ask for help is in the forums
http://sqlite.phxsoftware.com

Is the path in your connection string relative or absolute?  If you're using
a relative path, check the Environment.CurrentDirectory and make sure its
pointing to the right place.  Things such as using an OpenFileDialog or
SaveFileDialog can change the current directory.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jibin Scaria
Sent: Tuesday, February 17, 2009 12:14 AM
To: sqlite-users@sqlite.org
Subject: SPAM: [sqlite] Database path in widows

Hi,

I am new to SQLite. I am trying to develop a small application in C# using
sqlite-dotnet2 

I am facing problem with spaces in the database path, able to open database
but queries are returning "no such table: table name".

Can somebody help me?

Thanks in advance

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


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

Re: [sqlite] SPAM: Re: Question on missing Entry Point for Sqlite 3

[Robert Simpson]

I can't build a native SQLite .so for all flavors of *nix and OSX and
include it in my package -- its just beyond the scope of what I'm trying to
do.  For Windows, .NET supports me merging the managed and unmanaged code
into a single mixed-mode assembly.  Mono doesn't support this at all, so I
have to provide a managed-only version that supports dynamic linking.

Unfortunately there's just a certain level of underlying API support
required by the provider -- and lacking that support, certain functionality
is bound to be missing.  The trick is attempting to fail gracefully if that
API call doesn't exist.  This is rather difficult when .NET is doing the
binding behind the scenes.

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of W Allan Edwards
Sent: Tuesday, February 10, 2009 6:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SPAM: Re: Question on missing Entry Point for Sqlite 3


Just food for thought.  
 
In that case, your group (assuming you are the sqlite ADO.NET guy) appears
to ship an all inclusive build of sqlite in the adapter, but then the fully
managed only libs that dynamically link of to the platform compiled binaries
for sqlite?  Why not include within the shipped build the binaries compiled
properly to allow your ado.net stuff to work properly.
 
Developers would be able to know for SURE that they have the right binaries
for both Linux and windows.
 
>From a developer "usability" standpoint, having the target binaries for all
platforms in your install would be really nice.  So this is just a humble
request.  We then just have to copy the right libs into our installer and
all will be happy.> From: sqlite_l...@bcsft.com> To:
sqlite-users@sqlite.org> Date: Tue, 10 Feb 2009 18:21:39 -0700> Subject: Re:
[sqlite] SPAM: Re: Question on missing Entry Point for Sqlite 3> > I can try
and make my System.Data.SQLite provider fail gracefully if this> API
function doesn't exist -- but it may not be an error I can catch easily.>
.NET tends to link these calls when the class is referenced, and it happens>
in a place at runtime that I don't think I can put try/catch blocks around.>
> Also, a lot of ADO.NET functionality will not be available if this>
preprocessor symbol is missing, and Entity Framework stuff will definitely>
not work properly.> > > -Original Message-> From:
sqlite-users-boun...@sqlite.org> [mailto:sqlite-users-boun...@sqlite.org] On
Behalf Of W Allan Edwards> Sent: Tuesday, February 10, 2009 5:00 PM> To:
sqlite-users@sqlite.org> Subject: SPAM: Re: [sqlite] Question on missing
Entry Point for Sqlite 3> > > I have emailed Miguel of Mono directly, the
mono contact form, and the> ado.net provider guys. I have not heard anything
back from anbody but this> almighty sqlite group.> > Whether the doctor
turns this code off by default or not I would hope> somebody makes the
provider behavior the same cross platform. : - )> > You guys are awesome at
support. Thanks again for exploration of this issue> with me.> > Allan>
Date: Mon, 9 Feb 2009 14:17:30 -0800> From: rog...@rogerbinns.com>> To:
sqlite-users@sqlite.org> Subject: Re: [sqlite] Question on missing Entry>
Point for Sqlite 3> > -BEGIN PGP SIGNED MESSAGE-> Hash: SHA1> > D.>
Richard Hipp wrote:> > These two factors are sufficient to convince me to>
leave it turned off > > by default.> > You are aware that last year you>
turned it on by default for the> libraries you build :-)> >>
http://www.sqlite.org/cvstrac/chngview?cn=5539> > I'd guess that this whole>
issue arose because the .net wrapper people> used the Windows SQLite DLL>
from sqlite.org and only tested on Windows> so they didn't realise the>
symbols were optional and not on by default> by anyone compiling using>
./configure such as Mac and Linux folk.> > Roger> -BEGIN PGP>
SIGNATURE-> Version: GnuPG v1.4.9 (GNU/Linux)> >>
iEYEARECAAYFAkmQq3YACgkQmOOfHg372QSfSgCgnxTy7YkajElmuwRYM783e/Ci>>
u6YAn0HFYiyYNd9MWXV+MovZkBP1jlXg> =K8D6> -END PGP SIGNATURE->>
___> sqlite-users mailing list>>
sqlite-users@sqlite.org>>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
___> sqlite-users mailing list>
sqlite-users@sqlite.org>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > >
___> sqlite-users mailing list>
sqlite-users@sqlite.org>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SPAM: Re: Question on missing Entry Point for Sqlite 3

[Robert Simpson]

I can try and make my System.Data.SQLite provider fail gracefully if this
API function doesn't exist -- but it may not be an error I can catch easily.
.NET tends to link these calls when the class is referenced, and it happens
in a place at runtime that I don't think I can put try/catch blocks around.

Also, a lot of ADO.NET functionality will not be available if this
preprocessor symbol is missing, and Entity Framework stuff will definitely
not work properly.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of W Allan Edwards
Sent: Tuesday, February 10, 2009 5:00 PM
To: sqlite-users@sqlite.org
Subject: SPAM: Re: [sqlite] Question on missing Entry Point for Sqlite 3


I have emailed Miguel of Mono directly, the mono contact form, and the
ado.net provider guys.  I have not heard anything back from anbody but this
almighty sqlite group.
 
Whether the doctor turns this code off by default or not I would hope
somebody makes the provider behavior the same cross platform. : - )
 
You guys are awesome at support.  Thanks again for exploration of this issue
with me.
 
Allan> Date: Mon, 9 Feb 2009 14:17:30 -0800> From: rog...@rogerbinns.com>
To: sqlite-users@sqlite.org> Subject: Re: [sqlite] Question on missing Entry
Point for Sqlite 3> > -BEGIN PGP SIGNED MESSAGE-> Hash: SHA1> > D.
Richard Hipp wrote:> > These two factors are sufficient to convince me to
leave it turned off > > by default.> > You are aware that last year you
turned it on by default for the> libraries you build :-)> >
http://www.sqlite.org/cvstrac/chngview?cn=5539> > I'd guess that this whole
issue arose because the .net wrapper people> used the Windows SQLite DLL
from sqlite.org and only tested on Windows> so they didn't realise the
symbols were optional and not on by default> by anyone compiling using
./configure such as Mac and Linux folk.> > Roger> -BEGIN PGP
SIGNATURE-> Version: GnuPG v1.4.9 (GNU/Linux)> >
iEYEARECAAYFAkmQq3YACgkQmOOfHg372QSfSgCgnxTy7YkajElmuwRYM783e/Ci>
u6YAn0HFYiyYNd9MWXV+MovZkBP1jlXg> =K8D6> -END PGP SIGNATURE->
___> sqlite-users mailing list>
sqlite-users@sqlite.org>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SPAM: Re: How to create a Datatable [first steps of newbie]

[Robert Simpson]

Since you're using a relative directory in your connection string (a
filename with no path), make sure your Environment.CurrentDirectory is
pointing to the right place at all times.

For example, if you use an OpenFileDialog or SaveFileDialog, those classes
change the CurrentDirectory.  Once your current directory changes, your code
will no longer be able to open the database.

I highly recommend using this datasource:

Data Source=|DataDirectory|database.db

That way |DataDirectory| will be expanded at runtime to an absolute path.




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alessio Forconi
Sent: Sunday, January 11, 2009 10:19 AM
To: General Discussion of SQLite Database
Subject: SPAM: Re: [sqlite] How to create a Datatable [first steps of
newbie]

It means that the connection that I use in the code that I showed you is 
the same as that used in other parts of the program, only that we do not 
work, I get that bug that I told you.

Thanks for your availability.

Roosevelt Anderson ha scritto:
> What does your connection string look like?
>
> On Fri, Jan 9, 2009 at 2:36 PM, Alessio Forconi 
wrote:
>   
>> Thanks for your suggestion, I do not think that is the problem because
>> the connection is the one used with success in other parts of the
>> program, you show the code that I wrote following your suggestions:
>>
>> string sqlString = "SELECT * FROM Proclamatori";
>> conn = new SQLiteConnection(connString);
>> try
>> {
>> conn.Open();
>> SQLiteDataAdapter apt = new SQLiteDataAdapter(sqlString, conn);
>> DataTable dtProclamatori = new DataTable();
>> apt.Fill(dtProclamatori);
>> }
>> catch (SQLiteException ex)
>> {
>>  MessageBox.Show(ex.Message);
>> }
>> finally
>> {
>> conn.Close();
>>  }
>>
>>
>> I know that there are groups for microsoft c # but that does not seem
>> like a software applications that are not microsoft: (
>>
>> Roosevelt Anderson ha scritto:
>> 
>>> It should be "Data Source=database.db" not "DataSource=database.db". I
>>> didn't realize this because of the line break in the email. This will
>>> create a new, empty database named database.db in the directory where
>>> your application is running.
>>>
>>> On Fri, Jan 9, 2009 at 7:32 AM, Alessio Forconi 
wrote:
>>>
>>>   
 Thanks...

 I get this error

 "Data Source cannot be empty. Use :memory: to open an in-memory
database"

 but the connection is sattamante the same as that used in other parts
of
 the program

 Roosevelt Anderson ha scritto:

 
> If you trying to extract data from a sqlite database to a DataTable
> here is how you do it using the .Net data provider:
>
>SQLiteConnection conn = new SQLiteConnection("Data
> Source=database.db");
> conn.Open();
>   SQLiteDataAdapter apt = new SQLiteDataAdapter("select * from
tbl", conn);
> DataTable dt = new DataTable();
> apt.Fill(dt);
>   conn.Close();
>
>
>
>
> On Thu, Jan 8, 2009 at 11:28 AM, Alessio Forconi 
wrote:
>
>
>   
>> Hello everyone,
>>
>> I am making the first steps with the programming and I want to help
>> create a DataTable from a database sqlite with C #.
>>
>> Can you give me an example of how do I create it without using a
>> dataset?
>>
>> Thank you very much for your help.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>   
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


___
sqlite-users 

Re: [sqlite] SPAM: sqlite3_open16 fails on Windows Vista 64b

[Robert Simpson]

This can happen if you're accessing the precompiled sqlite library from a
64-bit application.  The binary provided by sqlite.org is 32-bit.  A lot of
.NET managed people get tripped by this, since .NET programs are (by
default) compiled as "Any CPU" which means on a 64-bit machine, the .NET
program runs in 64-bit.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ti Ny
Sent: Tuesday, November 25, 2008 6:00 AM
To: sqlite-users@sqlite.org
Subject: SPAM: [sqlite] sqlite3_open16 fails on Windows Vista 64b


I am getting an exception: An attempt was made to load a program with a
incorrect format. (HRESULT: 0x8007000B) when I am trying to use
sqlite3_open16 on Windows Vista 64b. On 32b or XP 32bit it works correctly.

_
Invite your mail contacts to join your friends list with Windows Live
Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create_url=/friends.aspx;
mkt=en-us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] OLE DB provider for SQLite

[Robert Simpson]

Agreed.  There may be some scenarios in which the C interface or some
C++/OLEDB interface to SQLite outperforms the ADO.NET provider significantly
-- but in most cases there is only a negligible performance impact that can
only be measured through thousands of iterations.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Brad Stiles
Sent: Monday, November 17, 2008 6:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] OLE DB provider for SQLite

> PS Managed (ADO.NET) providers do not satisfy requirements for
> performance reasons.

That's a pretty blanket statement.  I've found Robert Simpson's
ADO.NET provider to be very performant.  He has a benchmarking suite
he wrote to compare various DBs.  It might still be available for
download.

http://sqlite.phxsoftware.com/forums/t/174.aspx (Desktop)
http://sqlite.phxsoftware.com/forums/t/175.aspx (Mobile)
http://sqlite.phxsoftware.com/forums/p/17/33.aspx#33 (Comparing C# to C++)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Trouble with Visual Studio Express

[Robert Simpson]

Yep.  VS has a hell of time debugging and stepping through the amalgamated
source.  I use the split source for debugging purposes.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Sunday, November 02, 2008 9:00 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Trouble with Visual Studio Express

Has anybody else had trouble debugging SQlite3 in Visual Studio Express?

Here's what I have: Made a Visual Studio Express project, put the SQLite3
code in, added shell.c, put a breakpoint in sqlite3_exec, and ran the
debugger (debugging argument goes through sqlite3_exec). No stop. Then I
put a breakpoint on the call to sqlite3_exec in main(), ran, got the stop
there, pressed Step Into (F11), and the current line jumped to comments in
sqlite3.c. I'm thinking it's the VS Express can't take the amalgamated code.

Before suggestions of other IDE's/compilers, did anybody else experience
this? Found workarounds?

Thanks,

Martin


mail2web.com - Enhanced email for the mobile individual based on MicrosoftR
Exchange - http://link.mail2web.com/Personal/EnhancedEmail


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


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

Re: [sqlite] Visual Basic Programming to SQlite

[Robert Simpson]

Depends on what version of VB and what type of data access underpinnings
you're using.  If it's ADO.NET, you'll want to use the free open-source
SQLite ADO.NET provider from http://sqlite.phxsoftware.com

Robert

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Steven Charest
Sent: Friday, October 03, 2008 4:54 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Visual Basic Programming to SQlite

I am a Visual Basic programmer who would like to port the Access Database
over to SQlite.  Is there any documentation on how to use this database with
Visual Basic?  I am difficulty finding any documentation on it.

 

Thanks

 

 

 

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


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

Re: [sqlite] Does sqlite caches data to speed up transaction

[Robert Simpson]

This isn't unexpected at all.  The overhead of using a database (even SQLite) 
is much much higher than seeking to an area of an open file and reading some 
bytes out of it.



From: devesh tiwari <[EMAIL PROTECTED]>
Sent: Monday, September 29, 2008 12:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Does sqlite caches data to speed up transaction 

I am not seeing this performance difference in write but in reading database. 
Following is the sequence i am performing for reading:

1. sqlite3_open()
2. sqlite3_prepare()
3. sqlite3_bind_int()
4. sqlite3_step()
5. sqlite3_column()
6. sqlite3_finalize()
7. sqlite3_close()

steps 1 , 2,6& 7 are done only once and i am also doing sqlite_reset() before i 
use sqlite3_bind_int() again to create a query.
the query is always of the form:
select column from table where index='index_no';
so i need to change table name and index_no only for the next query.

If i dont use sqlite than sequence of reading is:

1) open file
2) seek in the file(seek value is simply calculated as index*size_of_structure)
3) read
4)close file

In this case file is opened once and only steps 2 & 3 need to perform for 
reading data and this is 10 times faster than sqlite.

Thanks & Regards
Devesh Kumar Tewari

--- On Mon, 9/29/08, [EMAIL PROTECTED] wrote:

> From: [EMAIL PROTECTED] 
> Subject: sqlite-users Digest, Vol 9, Issue 88
> To: sqlite-users@sqlite.org
> Date: Monday, September 29, 2008, 9:30 PM
> Send sqlite-users mailing list submissions to
> sqlite-users@sqlite.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body
> 'help' to
> [EMAIL PROTECTED]
> 
> You can reach the person managing the list at
> [EMAIL PROTECTED]
> 
> When replying, please edit your Subject line so it is more
> specific
> than "Re: Contents of sqlite-users digest..."
> 
> 
> Today's Topics:
> 
> 1. Re: Duplicated primary key error (Mariano Martinez
> Peck)
> 2. Re: Duplicated primary key error (P Kishor)
> 3. Re: Duplicated primary key error (Mariano Martinez
> Peck)
> 4. Does sqlite caches data to speed up transaction time?
> (devesh tiwari)
> 5. Re: Does sqlite caches data to speed up transaction
> time?
> (Jay A. Kreibich)
> 6. Re: Duplicated primary key error (Dennis Cote)
> 
> 
> --
> 
> Message: 1
> Date: Sun, 28 Sep 2008 21:03:39 -0300
> From: "Mariano Martinez Peck"
> 
> Subject: Re: [sqlite] Duplicated primary key error
> To: sqlite-users@sqlite.org
> Message-ID:
> 
> Content-Type: text/plain; charset=ISO-8859-1
> 
> Please I need help with this :(
> 
> I am getting BUSY or IOERR_BLOCKED when inserting a
> duplicated pk through C
> interface.
> 
> If I do it through sqlite.exe I get: SQL error: PRIMARY KEY
> must be unique
> 
> Any ideas?
> 
> many thanks,
> 
> mariano
> 
> 
> 
> On Sun, Sep 21, 2008 at 9:05 PM, Mariano Martinez Peck <
> [EMAIL PROTECTED]> wrote:
> 
> > Hi everybody!
> >
> > This is my first post in this list! I am very newbie
> with Sqlite. This is
> > the first time I am trying to use it. I am using
> Sqlite3 trough C interface.
> > The problem I have is this: I have a table created,
> just like this:
> >
> > CREATE TABLE materia(
> > codigo INTEGER PRIMARY KEY,
> > nombre CHARACTER VARYING(50),
> > observaciones CHARACTER VARYING(255),
> > )
> >
> > Then I do 2 inserts one after the other, with the same
> data. For example:
> >
> > INSERT INTO materia(codigo, nombre, observaciones)
> VALUES (55, ''TADP'',
> > ''Nothing")
> >
> > After doing this, I thought the second query ( i am
> using sqlite3_step()
> > function ) will returns me a
> SQLITE_ERROR.
> > However, it SQLITE_IOERR_BLOCKED.
> >
> > Is this correct? what should sqlite3_step returns me
> in this case?
> >
> > Many thanks for the help,
> >
> > Mariano
> > .
> >
> >
> 
> 
> --
> 
> Message: 2
> Date: Sun, 28 Sep 2008 19:15:47 -0500
> From: "P Kishor" 
> Subject: Re: [sqlite] Duplicated primary key error
> To: "General Discussion of SQLite Database"
> 
> Message-ID:
> 
> Content-Type: text/plain; charset=ISO-8859-1
> 
> On 9/28/08, Mariano Martinez Peck
> wrote:
> > Please I need help with this :(
> >
> > I am getting BUSY or IOERR_BLOCKED when inserting a
> duplicated pk through C
> > interface.
> >
> > If I do it through sqlite.exe I get: SQL error:
> PRIMARY KEY must be unique
> >
> > Any ideas?
> 
> Yes. The PRIMARY KEY must be unique. In other words, it
> should not be
> a duplicate of one that already exists. That is the whole
> idea behind
> a PRIMARY KEY. Which part of that is causing confusion?
> 
> >
> > many thanks,
> >
> >
> > mariano
> >
> >
> >
> >
> > On Sun, Sep 21, 2008 at 9:05 PM, Mariano Martinez
> Peck <
> > [EMAIL PROTECTED]> wrote:
> >
> > > Hi everybody!
> > >
> > > This is my first post in this list! I am very
> newbie with Sqlite. This 

Re: [sqlite] Issue when enabling fts3 in sqlite3

[Robert Simpson]

and don't forget to call sqlite3_close() again after iterating and finalizing 
the remaining prepared statements.



From: "SQLite List" <[EMAIL PROTECTED]>
Sent: Saturday, September 27, 2008 9:48 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Issue when enabling fts3 in sqlite3 

Call sqlite3_close() first. If it returns SQLITE_BUSY, *then* iterate over the 
statements list and close what statements remain. sqlite3_close() will cleanup 
internal prepared statements for you and eliminate the error you're seeing.



From: junkJon 
Sent: Friday, September 26, 2008 6:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Issue when enabling fts3 in sqlite3 

Oh, I was using the amalgamate source code 3.6.3 ... so I believe the problem
was related to what Roger mentioned below.. Thanks though for the input. 

-Jon 

P Kishor-3 wrote:
> 
> On 9/26/08, junkJon wrote:
>>
>> Hello all,
>>
>> I had a small little database (without fts3) that I setup that worked
>> perfectly. I was reading into full-text searching and I realized it
>> would
>> be cool if I could use it for my database, so I decided to implement it.
>> However, once I setup fts3 by enabling (SQLITE_ENABLE_FTS3) in the build
>> script (I am using the amalgamate source) and converted all my VARCHAR
>> to
>> TEXT in my database,
> 
> You don't have to...
> 
>> I get segmentation faults now... I can't figure out
>> what the problem could be.. It bombs out when I am trying to close my
>> Db...
>> it has something to do with the full-text searching code :(
> 
> What version of the software are you using? I got segfaults using
> 3.6.1 (albeit via Perl). Upgraded to 3.6.2 and all is well.
> 
> 
>>
>> Any help would be greatly appreciate...
>>
>>
>> here is my sample DB:
>>
>> ORIGINAL TABLE:
>> --
>> CREATE VIRTUAL TABLE file USING FTS3(
>> file_id INTEGER NOT NULL PRIMARY KEY
>> AUTOINCREMENT,
>> sha2 TEXT NOT NULL,
>> )
>>
>> NEW ONE:
>> 
>> CREATE TABLE file (
>> file_id INTEGER NOT NULL PRIMARY KEY
>> AUTOINCREMENT,
>> sha2 BLOB NOT NULL,
>> )
>>
>>
>>
>>
>> here is the stack trace:
>> 
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0xb7c536c0 (LWP 12890)]
>> 0x080b18f9 in sqlite3_finalize ()
>> Current language: auto; currently asm
>> (gdb) bt
>> #0 0x080b18f9 in sqlite3_finalize ()
>> #1 0x080f2caa in fulltext_vtab_destroy ()
>> #2 0x080f3c55 in fulltextDisconnect ()
>> #3 0x080e24a0 in sqlite3VtabUnlock ()
>> #4 0x080e24cf in sqlite3VtabClear ()
>> #5 0x080c4e32 in sqlite3DeleteTable ()
>> #6 0x080ca878 in sqlite3SchemaFree ()
>> #7 0x080c4ac1 in sqlite3ResetInternalSchema ()
>> #8 0x080ed344 in sqlite3_close ()
>> #9 0x0808d4cf in MySqlite::Close ()
>> #10 0x0808d4eb in MySqlite::~CSqliteDb ()
>> #11 0x0808ccbd in MySqlite::~CSqlitePreparedConnection ()
>>
>>
>> here is how I close my DB:
>> 
>> void MySqlite::Close()
>> {
>> sqlite3_stmt *stmt;
>> while((stmt = sqlite3_next_stmt(m_db, 0)) != 0)
>> {
>> sqlite3_finalize(stmt);
>> }
>>
>> sqlite3_close(m_db);
>> m_db = NULL;
>> }
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Issue-when-enabling-fts3-in-sqlite3-tp19698347p19698347.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> -- 
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Issue-when-enabling-fts3-in-sqlite3-tp19698347p19698866.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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


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

Re: [sqlite] Crazy performance difference between Mac and Windows

[Robert Simpson]

Looks to me like you've forgotten the biggest performance factor of all ...
starting a transaction before you begin the loop and committing it
afterwards.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gavin Kistner
Sent: Monday, September 22, 2008 9:45 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Crazy performance difference between Mac and Windows

(Right off the bat, let me say that I'm not sure if the problem here  
is Sequel, sqlite3-ruby, or
sqlite. Just in case...)

I have a Ruby script to migrate data from an old sqlite DB to a new  
schema. It's quite simple, selecting rows from db 1 and creating  
records in db 2. (A rough representation of the script is at the end  
of this post.)

This script transforms a 2MB sqlite DB with about 5,000 rows into a  
1.8MB sqlite DB with about the same number of rows. (A few fields and  
tables get dropped along the way.)

On my mac laptop at home (2.3GHz Core 2 Duo, 2GB RAM, 5400 RPM drive)  
this script runs in 22 seconds. In 'better battery life' mode.

On my XP desktop at work (2GHz Dual Pentium, 2GB RAM, 7000 RPM drive)  
this same script on the same DB runs in 11 minutes. 30x slower.

It's the same version of Ruby (1.8.6 p111), same version of sqlite3  
(3.6.2), sqlite3-ruby (1.2.3), and same version of Sequel (2.5.0). I  
know that the One-Click Installer of Ruby I'm using on XP isn't as  
fast as some other builds, but 30x slower seems crazy.

If I turn off DB journaling on SQLite on Windows, I can get it down  
from 11 minutes to 4 minutes. Only
12x slower than the Mac. (But then, the Mac also runs faster without  
journaling.)

The only funky thing that I can point to is that the script uses two  
different ORMs (sqlite3-ruby on db 1 and Sequel on db 2). I don't have  
a really good reason for this, it's just how it happened to have been  
written. If
this slowdown ever becomes a big issue I could try Sequel for both and  
see if that helps in any way.

Mostly I'm sharing this as a curiosity, though I'm quite interested if  
anyone has a suggestion on why
this might be so much slower on a roughly equivalent machine differing  
only in OS.

Here's (roughly) what the script looks like:

   require 'rubygems'
   require 'sqlite3'
   require 'sequel'

   olddb = SQLite3::Database.new( OLD_FILE )
   olddb.results_as_hash = true

   newdb = Sequel.sqlite( NEW_FILE )
   newdb << IO.read( NEW_SCHEMA )

   # Do the following sort of thing for about 10 different tables
   new_table = newdb[ :users ]
   olddb.execute "SELECT * FROM users" do |user|
 new_table << {
   :id=> user['id'].to_i,
   :name  => user['name'],
   :active=> user['active']=='yes',
   :email => user['email']
 }
   end

(As you might expect, this results in a SELECT from one DB followed by  
N independent un-transactioned INSERTs run on the other DB.)

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


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

Designing operating systems is hard work, and I don't envy the teams that do
it.  I've found my Vista experience to be rather enjoyable, especially post
SP1.  I don't think there's an OS out today that isn't annoying in one
fashion or another.  Having an application or library implement a workaround
for an OS design flaw is not unique to Windows by any stretch.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams
Sent: Thursday, September 18, 2008 8:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

Is a sad day when an application program is forced to compensate for pitiful
OS design and performance :-(

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
Sent: Thursday, September 18, 2008 10:31 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations


After watching Molly Brown's Channel9 videos on the cache manager, I'm
convinced the behavior for SQLite should be to not give the filesystem any
hints about caching and let the cache manager itself figure it out.  The
exception being Windows CE, where we can confirm that when this flag is not
set, the device will use compression in memory and degrade performance.

If that's the general consensus, I'll open a ticket.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
Sent: Thursday, September 18, 2008 7:56 AM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
cache manager (CC) in Windows and the underlying file system(s).  With
respect to the cache manager, it is going to affect whether or not there
is read ahead, how much read ahead will be used, and how long data will
remain in the cache (or another way, how quickly it will be dropped).
It has been some time since I've talked to the Queen of Cache Manger
about this, but as I recall CC will try to figure out what you are doing
if you don't give it a hint.  If you do give it a hint, then it is going
to run with that hint no matter what the cost.  Note that CC or the file
system are perfectly within their right to ignore your hints.  CC
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are
fairly random.  Database files also have the "nice" property that read
and writes are often sector (page) aligned.  Journal files should be
opened for sequential scan and are generally not sector (page) aligned.
Setting SQLite aside for a moment, for very large files that are only
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
huge performance gains.  However, if most or all of a file is going to
be touched, even in random order, then it doesn't get you much and can
hurt you.  Most SQLite data bases _probably_ fall into that second
case.  If you have enough memory and a small enough file such that the
cache manager can hold the entire file, you are golden.  That's why some
people see such great SQLite performance by just sequentially reading
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount
of RAM in the system and 2) the other applications running.  Windows
will try to share its resources among all the applications running as
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a
reasonable hint for the general case.  To be fair however, I should note
that I have my own VFS.  It does unbuffered I/O for database files and
sequential, cached I/O for journal files.  If you think you can get
better performance with different flags, create your own VFS, starting
with the Windows VFS and make the changes.  You can get as sophisticated
with your hints as you want.  You can write your own caching system if
you've ingested way too much caffeine.  (Did I mention that the VFS
stuff is great!)

I would not as a general rule advise people (customers) to change the
way their Windows system caches globally for the benefit of one of your
applications.  Eventually, that is going to bite you with some support
calls.

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the
wall:
>
>
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>>
>
>
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>>
>
>   SQLite is not "lying."  After 

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

[Robert Simpson]

You can't.  The memory pStmt points to is freed and invalid after the call
to finalize.  Worse, that freed memory could've already been reallocated for
some other purpose by the time the call to finalize() returns control to
you.

So your solution is to NULL your pointer after calling finalize() and don't
call finalize() again if your pointer is NULL.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joanne Pham
Sent: Thursday, September 18, 2008 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize
so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


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



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



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


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

A full table scan of 16 million rows and 55 columns on Vista with
FILE_FLAG_RANDOM_ACCESS finished in 600 seconds (cold) and 499 seconds
(warm) and turned Vista into a brick. The same test with the flag removed
ran in 119 seconds (cold) and 99 seconds (warm), and caused no adverse
problems with the OS.

Not even Sql Server could do a full table scan of the same data that fast!
Same test on Sql Server 2005 on the same machine ran in a minimum of 180
seconds, and averaged 220 seconds!  Oh and Sql Server's memory usage shot to
3gb as well.  Fun stuff.

I ran the same tests with SQLite on an XP laptop.  I got 618 seconds with
FILE_FLAG_RANDOM_ACCESS, and 599 seconds without the flag.  Not so dramatic
of a difference, but still in the right direction.

So I'd agree that the hint should only be used for CE, and no other
platforms.  Roger quoted the CE compression blurb from this MSDN page:
http://msdn.microsoft.com/en-us/library/ms923949.aspx

Robert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Wednesday, September 17, 2008 4:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> Ideally, at least on non-CE platforms, I'd like see SQLite not give the OS
> any hints about caching.  However, I'm not sure what kind of performance
hit
> (if any) that would have on Windows.  It's already been proven that
> providing the hint on WinCE is beneficial.

I agree.  Open a ticket and include measurements in it.

With Windows CE, setting the flag prevents compression:

  Using the FILE_FLAG_RANDOM_ACCESS flag in the RAM file
  system, which places files in the object store, will
  prevent a file from being compressed. If performance is
  an issue, this could be the correct solution. Read and
  write operations to a compressed file are slower than
  read and write operations to an uncompressed file.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI0ZGemOOfHg372QQRAtHSAJ9fSAXY3ekwrK4g9/eTrcYYQInW6QCgvSkB
e00SnZbtTmQQ1G8ZxEVTK1M=
=Y3uW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

Ideally, at least on non-CE platforms, I'd like see SQLite not give the OS
any hints about caching.  However, I'm not sure what kind of performance hit
(if any) that would have on Windows.  It's already been proven that
providing the hint on WinCE is beneficial.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Wednesday, September 17, 2008 2:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> The purpose of a cache is to improve performance and responsiveness.  Any
> cache that uses all physical memory, forces all other apps to the paging
> file 

All current operating systems do this, using heuristics to determine how
much of each running application to leave in memory while using "spare"
memory for filesystem cache and dynamically changing allocation based on
demand.  Sometimes they mess up to the detriment of idle applications.
However that is not the bit I am talking about as it is not controlled
by flags when opening files and instead is part of the core operating
system code.  (You often see this issue when copying files larger than RAM).

What is under discussion is how the operating system is using the cache
that it does decide to allocate for SQLite.  If you tell it that a file
is sequential access then that means that read ahead is good and that
data already read can be discarded.  If you tell it that a file is
random access then read ahead is bad (it has the disk occupied when the
next random request comes in) and already read data should be kept.
SQLite does not know and should be leaving it up to the operating
system.  Your tests prove that when that is done on Vista, performance
of SQLite is better and other applications are less adversely affected.

> The real frustration is that this seems to be a rather obvious bug in
Vista,
> and definitely not SQLite's responsibility. 

This is conflating two issues.  One is the tradeoff between RAM usage
for cache vs idle applications.  You can argue that is a bug, or more
accurately there are circumstances under which the tradeoff picks wrong
values, and is very hard to get right.  Example underlying details are
at http://blogs.technet.com/markrussinovich/archive/2008/02/04/2826167.aspx

The second issue is the performance differences when random access,
sequential and no flags are given while opening the file and the
resulting performance.  In this case a file is accessed mostly
sequentially but the random access flag is given.  Performance was worse
than letting the operating system use its own heuristics.  That is
hardly surprising or a bug.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI0XBYmOOfHg372QQRAmKfAJ4pWVfZ8LS2ET+Y55FGT3Am9wCfZg4e
McaZrcLUXQJhU7i1Gw5+cRk=
=CQg5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

I've run the tests with superfetch and prefetch disabled and enabled.
Results are consistent with or without these running.  The only thing that
has any affect is the FILE_FLAG_RANDOM_ACCESS flag.  And only on Vista.

For now I'm thinking of overriding the default Windows VFS and redirecting
the open function just for Vista so it doesn't use that flag.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Virgilio Alexandre
Fornazin
Sent: Wednesday, September 17, 2008 9:14 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

Could not this bug be related with Vista feature called 'Superfetch' ?
It tries to keep in memory the most accessed files for user, avoiding
disk for read access.

If you disable (or stop) this service, the problem remains or not ?


 
 
Virgilio Alexandre Fornazin
High performance and realtime systems development

Rua Brigadeiro Vicente Faria Lima, 268
Bela VistaLeme-SPCEP 13611-485
Phone: +55 19 3571-5573
Cell: +55 19 8111-4053
+55 11 8357 1491
Mail: [EMAIL PROTECTED]
Web: http://www.fornazinconsultoria.com.br


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich
Sent: quarta-feira, 17 de setembro de 2008 13:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall:
> Robert Simpson wrote:
> > To me this seems like an obvious bug in Vista,
> 
> Actually I'd argue that it is behaving as designed.

  You could argue it is behaving as designed, but I'd still argue it is
  behaving poorly.

  Further, if a system component who's sole purpose is to increase
  performance-- as all cache systems are-- has the overall effect of
  decreasing performance, not only of the process it is trying to speed
  up, but of the whole system, it is pretty easy to argue that's a serious
  functional bug.


  Given the speed of most storage systems, filesystem cache management
  is an important component of overall system performance.  However, if
  the cache system is grabbing so much physical memory (and, apparently,
  refusing to let go of it) that processes are forced to aggressively
  page and the net result is a massive performance loss, then something
  isn't right.

  As with so many things, cache management (and, indeed, the whole
  concept of caches) tends to be a huge web of compromises.  It is
  extremely difficult, if not impossible, to cover all cases.  But
  these things are not exactly new, and it should be easy enough to
  never get in a situation where things are actually made worse--
  especially that they're not made worse for the whole system.

> Generally
> filesystem code will try to detect what is going on under the hood.  In
> particular if it looks like you are doing sequential access(*) then they
> will start doing read ahead, whereas read ahead is a waste for random
> access. 

  Not to get into a whole argument about cache strategies, but this
  often not true.  If we assume free memory isn't a big concern,
  when a process opens a file for random-access we can either
  read-ahead the whole thing or we can read blocks here and there until
  (if the process touches the majority of the file) we have the whole
  thing in memory.  Both systems, in the end, will result in the same
  memory usage.

  However, if I'm going to be doing random access on a file of moderate
  or smaller size, it is much cheaper for the OS to just suck the whole
  thing into memory via one bulk read operation than it is to grab it
  piecemeal.

  The whole trick is defining "moderate" both in terms of first-return
  read times (time to return the block the process actually asked for,
  which might not be the first block pulled off disk) vs how likely the
  process is to touch the majority of file blocks (something that is
  somewhat less likely as the file gets bigger).  

  As the file gets larger, there is also the real-world issue of how
  much RAM the system has, and how much of it is actually in-use with
  process and OS pages.  This is true of both sequential AND random
  access, although memory usage is generally easier to control in
  sequential patterns.

  This is where Vista appears to be breaking down and making very poor
  decisions.  It seems to be giving cache pages more priority than
  process and OS system pages, and generally that should never happen.
  If we're correctly understanding what is going on, Vista might very
  well be paging out SQLite's internal page cache to fit a few extra file
  blocks in RAM.  How much sense does that make?

> By using the sequential or random flags you are explicitly
> telling the filesystem to ignore its heuristics and do as you say only.

  Even if that's true (most APIs present the flags as "hints" not
  absolute truths), the worst an incorr

Re: [sqlite] Vista frustrations

[Robert Simpson]

It's been reproduced on Vista x64 and x86 OS's.  I don't have a pre-SP1
installation to verify if it happens against pre-patched versions.

Getting a database should be easy enough ...

CREATE TABLE FOO(a, b, c, d, e, f);

Then do this about 16 million times or so ... whatever it takes to pump the
db up to the 4-5gb mark:

INSERT INTO FOO VALUES(random(), random(), random(), randomblob(16),
randomblob(16), randomblob(32));




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sherief N. Farouk
Sent: Wednesday, September 17, 2008 12:38 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations


> I ran some tests using the command-line sqlite3.exe, and observed that
> Windows Vista (SP1) is actually trying to cache the entire 5gb file
> into
> memory during the table scan!  The system slows to a complete crawl and
> becomes unresponsive.  The sqlite3.exe's memory remains very minimal,
> but
> Vista itself eats every last scrap of physical memory, forcing all
> other
> apps to the paging file trying to cache the contents of the database.

[Sherief N. Farouk] 

Any chance you could make a small C program to reproduce this case (via
procedurally creating a similarly-sized database)? Out of curiosity, was
that on x86 or x64?

- Sherief

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


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

The real frustration is that this seems to be a rather obvious bug in Vista,
and definitely not SQLite's responsibility.  IMO setting the flag is the
"right thing to do" -- but at the same time, I don't expect any favors from
Microsoft in fixing this any time soon.  Meanwhile all those poor Vista
people need SQLite to work well with their OS.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Wednesday, September 17, 2008 1:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> To me this seems like an obvious bug in Vista,

Actually I'd argue that it is behaving as designed.  Generally
filesystem code will try to detect what is going on under the hood.  In
particular if it looks like you are doing sequential access(*) then they
will start doing read ahead, whereas read ahead is a waste for random
access.  By using the sequential or random flags you are explicitly
telling the filesystem to ignore its heuristics and do as you say only.

Since SQLite cannot tell in advance whether access is almost entirely
random or almost entirely sequential, it makes far more sense to let the
operating system use its builtin heuristics and optimise accordingly.

(*) Sun's ZFS can even detect strided sequential access - ie reading X
amount of data every Y kilobytes.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI0L0vmOOfHg372QQRAk+7AKCEloS0d+xB+M2C/Bap38ilZZ8tVACfciC4
vMfbYMNVV9k6CNR7hpSQo6A=
=AXGU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Vista frustrations

[Robert Simpson]

The purpose of a cache is to improve performance and responsiveness.  Any
cache that uses all physical memory, forces all other apps to the paging
file and turns the operating system into a brick is definitely not working
as designed.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Wednesday, September 17, 2008 1:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> To me this seems like an obvious bug in Vista,

Actually I'd argue that it is behaving as designed.  Generally
filesystem code will try to detect what is going on under the hood.  In
particular if it looks like you are doing sequential access(*) then they
will start doing read ahead, whereas read ahead is a waste for random
access.  By using the sequential or random flags you are explicitly
telling the filesystem to ignore its heuristics and do as you say only.

Since SQLite cannot tell in advance whether access is almost entirely
random or almost entirely sequential, it makes far more sense to let the
operating system use its builtin heuristics and optimise accordingly.

(*) Sun's ZFS can even detect strided sequential access - ie reading X
amount of data every Y kilobytes.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI0L0vmOOfHg372QQRAk+7AKCEloS0d+xB+M2C/Bap38ilZZ8tVACfciC4
vMfbYMNVV9k6CNR7hpSQo6A=
=AXGU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] Vista frustrations

[Robert Simpson]

I recently had a user of the ADO.NET provider report a problem on Vista.
His database is 5gb, and he's doing a full table scan of 16 million rows.
Yea.

 

I ran some tests using the command-line sqlite3.exe, and observed that
Windows Vista (SP1) is actually trying to cache the entire 5gb file into
memory during the table scan!  The system slows to a complete crawl and
becomes unresponsive.  The sqlite3.exe's memory remains very minimal, but
Vista itself eats every last scrap of physical memory, forcing all other
apps to the paging file trying to cache the contents of the database.

 

It took about 500 seconds to scan the entire table, which isn't all that bad
given the circumstances.  Unfortunately Vista's cache remained high and
continued to make the entire system unresponsive until I quit out of the
command-line.

 

After some research, I commented out the FILE_FLAG_RANDOM_ACCESS flag in
os_win.c and re-ran the test.

 

It completed in 99 seconds.  System remained highly responsive, and Vista
never blew out the memory trying to cache the entire file.

 

Now, I know Windows CE benefits greatly from having this flag hint - but has
anyone tested normal desktop performance with or without it?  To me this
seems like an obvious bug in Vista, but the chances of getting Microsoft to
fix it are slim to none.

 

Robert

 

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

Re: [sqlite] SQLite 3.6.1 memory leak?

[Robert Simpson]

There's not enough information in your post for us to comment -- which is
probably why nobody responded earlier.  The unit tests for SQLite create
thousands of connections and run hundreds of thousands of commands without
leaking.  So there's a probability that you may be doing something wrong,
yes.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ulric Auger
Sent: Friday, August 29, 2008 11:13 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite 3.6.1 memory leak?

Hi,

Since I updated to SQLite 3.6.1 I have a memory leak when my application
exits.

If I compile using SQLite 3.5.8 I don't have the memory leak.

 

VS 2005 dump:

Detected memory leaks!

Dumping objects ->

c:\dev\mescruiser\lib\sqlite\sqlite3.c(11938) : {4754} normal block at
0x01BFC460, 48 bytes long.

 Data: <(   > 28 00 00 00 00 00 00 00 B0 B8 15 00 FF FF FF FF 

Object dump complete.

 

Using LeakFinder/StackWalker it seem that the memory leak originate from
sqlite3_open.

 

Is this a real memory leak or I'm doing something wrong?

 

Thanks

 

Ulric

 

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


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

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

[Robert Simpson]

Slight change so you can see the order by results more clearly:

(Sql Server 2005)
CREATE TABLE #t1(a int, b int);
INSERT INTO #t1 VALUES(1,6);
INSERT INTO #t1 VALUES(9,5);

SELECT a AS b, b AS a FROM #t1 ORDER BY a;
b   a
--- ---
9   5
1   6


SELECT b AS a, a AS b FROM #t1 ORDER BY a;
a   b
--- ---
5   9
6   1

SELECT a, b AS a FROM #t1 ORDER BY a;
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'a'.

SELECT a AS x, b AS x FROM #t1 ORDER BY x;
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'x'.

SELECT a AS b, b AS a FROM #t1 WHERE a=1;
b   a
--- ---
1   6

SELECT a AS b, b AS a FROM #t1 WHERE a=6;
b   a
--- ---

SELECT a AS x, b AS x FROM #t1 WHERE x=1;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'x'.




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 19, 2008 2:00 PM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a
SELECT?

Consider a table:

 CREATE TABLE t1(a INTEGER, b INTEGER);
 INSERT INTO t1 VALUES(1,2);
 INSERT INTO t1 VALUES(9,8);

I am reworking (read: trying to fix) the name resolution algorithm in  
SQLite and I am wondering what is the correct behavior of the  
following queries?  Can anybody tell me what PostgreSQL or MySQL or  
SQL Server or Oracle do with these?

 SELECT a AS b, b AS a FROM t1 ORDER BY a;
 SELECT b AS a, a AS b FROM t1 ORDER BY a;
 SELECT a, b AS a FROM t1 ORDER BY a;
 SELECT a AS x, b AS x ORDER BY x;

 SELECT a AS b, b AS a WHERE a=1;
 SELECT a AS b, b AS a WHERE a=2;
 SELECT a AS x, b AS x WHERE x=1;




D. Richard Hipp
[EMAIL PROTECTED]



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


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

Re: [sqlite] SPAM: Correct SQL name resolution on AS clauses in a SELECT?

[Robert Simpson]

Sql Server 2005:

SELECT a AS b, b AS a FROM #t1 ORDER BY a;
b   a
--- ---
1   2
9   8


SELECT b AS a, a AS b FROM #t1 ORDER BY a;
a   b
--- ---
2   1
8   9

SELECT a, b AS a FROM #t1 ORDER BY a;
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'a'.

SELECT a AS x, b AS x FROM #t1 ORDER BY x;
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'x'.

SELECT a AS b, b AS a FROM #t1 WHERE a=1;
b   a
--- ---
1   2

SELECT a AS b, b AS a FROM #t1 WHERE a=2;
b   a
--- ---

SELECT a AS x, b AS x FROM #t1 WHERE x=1;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'x'.



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 19, 2008 2:00 PM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a
SELECT?

Consider a table:

 CREATE TABLE t1(a INTEGER, b INTEGER);
 INSERT INTO t1 VALUES(1,2);
 INSERT INTO t1 VALUES(9,8);

I am reworking (read: trying to fix) the name resolution algorithm in  
SQLite and I am wondering what is the correct behavior of the  
following queries?  Can anybody tell me what PostgreSQL or MySQL or  
SQL Server or Oracle do with these?

 SELECT a AS b, b AS a FROM t1 ORDER BY a;
 SELECT b AS a, a AS b FROM t1 ORDER BY a;
 SELECT a, b AS a FROM t1 ORDER BY a;
 SELECT a AS x, b AS x ORDER BY x;

 SELECT a AS b, b AS a WHERE a=1;
 SELECT a AS b, b AS a WHERE a=2;
 SELECT a AS x, b AS x WHERE x=1;




D. Richard Hipp
[EMAIL PROTECTED]



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


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

Re: [sqlite] "Database is Locked"

[Robert Simpson]

Show and tell time!  Lets see some code :)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 8:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "Database is Locked"

I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are
there
any problems with creating the database with 3.5.7 and then reading it with
3.6.1?

Thanks

>  ---Original Message---
>  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 14:38
>  
>  
>  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  wrote:
>  
>  > I currently have a project that runs on a Windows XP machine. The  
>  > project was working with
>  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  > have been getting
>  > an "Database is Locked" error message on any type of query that is  
>  > ran after the database is
>  > "opened". I am assuming that when the database is opened it is being  
>  > locked somehow. I am
>  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  > execute a simple PRAGMA
>  > statement. If anyone knows of a solution to this problem I would  
>  > appreciate the help.
>  >
>  
>  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  
>  I do not recall making any changes to locking behavior between 3.5.7  
>  and 3.6.1
>  
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>  
>  
>  
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] ANN: SQLite ADO.NET Provider 1.0.56.0

[Robert Simpson]

Coinciding with Visual Studio 2008 SP1 going RTM today, I've put out a new
version of the SQLite ADO.NET provider.  The LINQ bits are fairly solid, but
still officially in beta.  Nonetheless, it gains the distinction of being
the very first ADO.NET provider publically released (other than Sql Server)
that supports LINQ on the RTM Entity Framework.

 

This version also introduces full Mono support via a 100% managed provider
that back-ends into the official SQLite distribution.  Requires 3.6.1 or
higher of the shared library (Linux) or the Windows sqlite3.dll.  Although
the Mono.Data.Sqlite code was based on my provider, it hadn't been updated
in 17 months and didn't work all that well.

 

http://sqlite.phxsoftware.com

 

Robert

  

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

Re: [sqlite] c# .net beginTrans...where is endtrans?

[Robert Simpson]

using (DbTransaction trans = connection.BeginTransaction())
{
  // loop goes here
  //
  // loop ends here

  trans.Commit();
}

Best place to get answers to .NET-related SQLite questions (and more) is at
http://sqlite.phxsoftware.com/forums

Robert

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of danblack101
Sent: Friday, August 08, 2008 2:01 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] c# .net beginTrans...where is endtrans?


Hi,
I have a c#  program which has to process a large amount of updates at one
time.
I have previously used:

connection.beginTransaction()
commandObject

  update SQLite with command object

commandObject.Transaction.Commit

This worked nicely.

But now I have modulised my code so that I have a generic
updateFunction(String) inside this function I create a commandObject and do
the update which is fine, but

Now I have reached a point where I want to process a large amount of updates
therefore need transaction boundaries. So now I have 

connection.beginTransaction()

updateFunction(String)




How can I commit, I dont have a command object that performs the
commit/endTrans, I dont want to commit inside the updateFunction ?


Any help is greatly appreciated 
Dan




-- 
View this message in context:
http://www.nabble.com/c--.net-beginTrans...where-is-endtrans--tp18898927p188
98927.html
Sent from the SQLite mailing list archive at Nabble.com.

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


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

Re: [sqlite] SPAM: Proposed removal of (mis-)feature

[Robert Simpson]

I have never really liked #3 and agree wholeheartedly with its demise.
Removing it in a subsequent release will force all those people using them
for string literals to fix their code -- which isn't a bad idea at all!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 10:26 AM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



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


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

Re: [sqlite] Realtime backup of database

[Robert Simpson]

The new VFS implementation is probably the way to go.  If you wrote some
wrapper code around the default VFS, you could capture all the writes that
go to the main db and clone/wirexfer those writes to a 2nd sync file.

Just an idea -- haven't worked with VFS's yet, though I plan on implementing
them in the ADO.NET provider.  SQLite may be one of the very few database
engines that can work on Microsoft's "Isolated Storage" mechanism fairly
soon!  If it works as well as I think, I could probably implement several
different VFS implementations in the provider for doing sync stuff, SQLite
over streams, etc.

Robert

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Till Steinbach
Sent: Tuesday, August 05, 2008 9:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Realtime backup of database

Hi everyone,
i need to backup a sqlite database on a remote device for  
configuration redundancy purposes. Due to a really slow connection  
between the devices triggering rsync is not the best solution yet.  
Although rsync is efficient it is transfering kilobytes of data to see  
what it has to update. Because the updates are always one-way (from  
the live database to the backup database) it must be sufficient only  
to transfer the updates.
I don't get the right idea for my application. Something like  
capturing the querys that update the database and transmit them to the  
remote system would fit the purpose, but that seems to me very  
complicated to teach the database.

I'm stuck with my problem so perhaps here is someone with a really  
clever idea.

Sorry for my bad english,
greetings from hamburg, germany
Till
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Resources for newbies.

[Robert Simpson]

The problem is that sqlite3_exec() is doing several steps and its difficult
to isolate which of those steps could be the failure point.

For testing purposes, it would help if you instead used sqlite3_prepare(),
sqlite3_step() and sqlite3_finalize() on the "Select" statement.  That way
you're using a lower-level API and can isolate exactly which API call is
returning an error.

Robert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
Sent: Saturday, August 02, 2008 3:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Resources for newbies.

callback is being called when i get a query match... i think the problem is
in
the data and i've re posted it here... it only has a few records, and my app
gets error with 'select * from Event;'

this is a link to the database
http://www.savoysoft.com/EvtMgrRequestx05A

you have all given me more help then i deserve... very appreciative
thanks

On Sat, Aug 2, 2008 at 5:17 PM, Robert Simpson <[EMAIL PROTECTED]>wrote:

> Nothing stands out ... is your callback being called at all?  What's it
> look
> like?
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
> Sent: Saturday, August 02, 2008 2:00 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Resources for newbies.
>
> here's the create code:
>if( sqlite3_exec( m_pdbData,
>"create table Event( Key TEXT[unique], DateTime DATE, Event
> blob, MotionData1 blob, MotionData2 blob, MotionData3 blob, MotionData4
> blob, MotionData5 blob, MotionData6 blob, MotionData7 blob, MotionData8
> blob, MotionData9 blob, MotionData10 blob, MotionData11 blob, MotionData12
> blob, MotionData13 blob, MotionData14 blob, MotionData15 blob,
MotionData16
> blob, PRIMARY KEY (Key) );", , this,  ) != 0 )
>  (have also used TEXT instead of blob)
>
> here I add a record:
>strSql.Format( "insert or replace into Event values( '%s',
> '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
> '%s', '%s', '%s', '%s', '%s', '%s' );",
>strKey, szSQLDateTime, strArgs + " /t " + szDateTime,
>  m_strMotionData[0],
>  m_strMotionData[1],
>  m_strMotionData[2],
>  m_strMotionData[3],
>  m_strMotionData[4],
>  m_strMotionData[5],
>  m_strMotionData[6],
>  m_strMotionData[7],
>  m_strMotionData[8],
>  m_strMotionData[9],
>  m_strMotionData[10],
>  m_strMotionData[11],
>  m_strMotionData[12],
>  m_strMotionData[13],
>  m_strMotionData[14],
>  m_strMotionData[15]
>   );
> sqlite3_open( m_strFile, _pdbData );
> sqlite3_exec( m_pdbData, strSql, , this,  );
>  sqlite3_close( m_pdbData );
> (note m_strMotionData are strings -- small values work, and around 64
chars
> each the following query fails)
>
>
> here's the query:
>   sqlite3_open( m_strFile, _pdbData );
>   sqlite3_exec( m_pdbData, "select * from Event" , ,
this,
>  );
>sqlite3_close( m_pdbData );
>
> err msg: "SQL logic error or bad database"
>
> thanks again,
> dave
>
>
> On Sat, Aug 2, 2008 at 4:44 PM, Robert Simpson <[EMAIL PROTECTED]
> >wrote:
>
> > Can we see the code you're using that's failing?
> >
> > Also, I'm unclear ... you're calling sqlite3_exec() to select the rows
as
> > well as insert them?
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
> > Sent: Saturday, August 02, 2008 1:04 PM
> > To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> > Subject: Re: [sqlite] Resources for newbies.
> >
> > thanks for your efforts... much appreciated
> >
> > (I'm a C++ developer of many decades, so i know my way around
> > the client code... will keep debugging, must be simple/stupid problem)
> >
> > On Sat, Aug 2, 2008 at 3:59 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> >
> > > On 8/2/08, David Nelson <[EMAIL PROTECTED]> wrote:
> > > > this is a link to the database
> > > >  http://www.savoysoft.com/EvtMgrRequestx05A
> > > >
> > >
> > >
> > > Works fine for me.
> > >
> > > I tried
> > >
> > > SELECT * FROM event WHERE event LIKE '%Vid%';
> > >
> > >

Re: [sqlite] Resources for newbies.

[Robert Simpson]

Can we see the code you're using that's failing?

Also, I'm unclear ... you're calling sqlite3_exec() to select the rows as
well as insert them?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
Sent: Saturday, August 02, 2008 1:04 PM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Resources for newbies.

thanks for your efforts... much appreciated

(I'm a C++ developer of many decades, so i know my way around
the client code... will keep debugging, must be simple/stupid problem)

On Sat, Aug 2, 2008 at 3:59 PM, P Kishor <[EMAIL PROTECTED]> wrote:

> On 8/2/08, David Nelson <[EMAIL PROTECTED]> wrote:
> > this is a link to the database
> >  http://www.savoysoft.com/EvtMgrRequestx05A
> >
>
>
> Works fine for me.
>
> I tried
>
> SELECT * FROM event WHERE event LIKE '%Vid%';
>
> and got a boatload of rows back.
>
> Has to be something wrong with your compilation. Rest assured, the
> problem is not with SQLite or with your db, but with your build of
> SQLite or with your program with which you are querying.
>
> Maybe some Windows folks will be able to help you.
>
> >
> >
> >  On Sat, Aug 2, 2008 at 3:03 PM, Robert Simpson <[EMAIL PROTECTED]
> >wrote:
> >
> >
> > > Can you give us a download link to the database?  Does your select
> >  > statement
> >  > fail in the sqlite3.exe command-line util?
> >  >
> >  > -Original Message-
> >  > From: [EMAIL PROTECTED]
> >  > [mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
> >  > Sent: Saturday, August 02, 2008 11:44 AM
> >  > To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> >  > Subject: Re: [sqlite] Resources for newbies.
> >  >
> >  > OK, P Kishor, here is my problem:
> >  >
> >  > Getting "logic error or bad data" when using largw data strings:
> >  >
> >  >
> >  > I create the database as follows:
> >  >create table Event( Key TEXT[unique], DateTime DATE, Event TEXT,
> PRIMARY
> >  > KEY (Key) )
> >  >
> >  >
> >  > I add records to the database as follows:
> >  >insert or replace into Event values( 'strKey', '07/25/2008
> 08:00:00',
> >  > 'data string' )
> >  >
> >  > And I query the database as follows:
> >  >SELECT * FROM [Event]  WHERE([Event] LIKE "*foo*" AND [Event]
> LIKE
> >  > "*bar*") AND[Date/Time] BETWEEN DateAdd("n",-5,Now()) AND Now()
> >  >
> >  >
> >  >
> >  > The query works fine when my inserted data strings are small, like <
> 256
> >  > bytes.
> >  >
> >  > However, I get 'logic error' on the query if my data strings are
> largere,
> >  > like 2048 bytes.
> >  >
> >  >
> >  > Can someone explain what I need to do to support larger data strings?
> >  >
> >  > (using C++ under Win/XP and amalgom version of sqlite compiled
> directly in
> >  > the app)
> >  >
> >  >
> >  >
> >  >
> >  > On Sat, Aug 2, 2008 at 2:39 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> >  >
> >  > > On 8/2/08, David Nelson <[EMAIL PROTECTED]> wrote:
> >  > > > Rich,
> >  > > >  Thanks for your response... I appreciate your suggested
> references.
> >  > > >
> >  > > >  The problem is that while I am an expert on some/many things,
> SQLite
> >  > > >  and SQL in general is not among them.  Nor do I have a strong
> interest
> >  > > >  in becoming an expert -- I simply have a simple logging
> application
> >  > > which
> >  > > >  has worked fine under Windows Access, but fails under SQLite
with
> an
> >  > > >  error message which is less than enlightening.
> >  > > >
> >  > > >  I was hoping to find an interactive blog where experts would
> enjoy
> >  > > helping
> >  > > >  newbies
> >  > > >  with specific suggestions.
> >  > >
> >  > > This *is* such an "interactive blog." This is the place where
> experts
> >  > > and non-experts enjoy helping experts and non-experts with specific
> >  > > and generic suggestions.
> >  > >
> >  > > Just ask, and ye shall be rewarded.
> >  > >
> >  > >
> >  > > >
> >  > > >  Thanks again.
> >  > > >

Re: [sqlite] Resources for newbies.

[Robert Simpson]

Can you give us a download link to the database?  Does your select statement
fail in the sqlite3.exe command-line util?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Nelson
Sent: Saturday, August 02, 2008 11:44 AM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Resources for newbies.

OK, P Kishor, here is my problem:

Getting "logic error or bad data" when using largw data strings:


I create the database as follows:
create table Event( Key TEXT[unique], DateTime DATE, Event TEXT, PRIMARY
KEY (Key) )


I add records to the database as follows:
insert or replace into Event values( 'strKey', '07/25/2008 08:00:00',
'data string' )

And I query the database as follows:
SELECT * FROM [Event]  WHERE([Event] LIKE "*foo*" AND [Event] LIKE
"*bar*") AND[Date/Time] BETWEEN DateAdd("n",-5,Now()) AND Now()



The query works fine when my inserted data strings are small, like < 256
bytes.

However, I get 'logic error' on the query if my data strings are largere,
like 2048 bytes.


Can someone explain what I need to do to support larger data strings?

(using C++ under Win/XP and amalgom version of sqlite compiled directly in
the app)




On Sat, Aug 2, 2008 at 2:39 PM, P Kishor <[EMAIL PROTECTED]> wrote:

> On 8/2/08, David Nelson <[EMAIL PROTECTED]> wrote:
> > Rich,
> >  Thanks for your response... I appreciate your suggested references.
> >
> >  The problem is that while I am an expert on some/many things, SQLite
> >  and SQL in general is not among them.  Nor do I have a strong interest
> >  in becoming an expert -- I simply have a simple logging application
> which
> >  has worked fine under Windows Access, but fails under SQLite with an
> >  error message which is less than enlightening.
> >
> >  I was hoping to find an interactive blog where experts would enjoy
> helping
> >  newbies
> >  with specific suggestions.
>
> This *is* such an "interactive blog." This is the place where experts
> and non-experts enjoy helping experts and non-experts with specific
> and generic suggestions.
>
> Just ask, and ye shall be rewarded.
>
>
> >
> >  Thanks again.
> >  dave
> >
> >  On Sat, Aug 2, 2008 at 10:03 AM, Rich Shepard <[EMAIL PROTECTED]
> >wrote:
> >
> >
> >  > On Fri, 1 Aug 2008, David Nelson wrote:
> >  >
> >  > > You addressed Dwight's example, but not his question -- which is
> whether
> >  > > there is a resource for answering questions like this.
> >  >
> >  > dave,
> >  >
> >  >   I thought that my recommendation of Mike Owens' book was a pointer
> to a
> >  > reference that would answer questions such as this.
> >  >
> >  >   Regardless, here's another one: Rick van der Laan's "Introduction
to
> SQL,
> >  > 4th Edition." It is an outstanding introduction to DDL (Data
> Definition
> >  > Language; that is, how to design and normalize a database schema) and
> DML
> >  > (Data Manipulation Language; that is, how to enter, retrieve, and
> alter
> >  > data
> >  > in tables).
> >  >
> >  >   And for those who want to refine their knowledge and skills, I
> highly
> >  > reccomend any of Joe Celko's books. I read his columns in Data Based
> >  > Advisor
> >  > in the 1980s and 1990s as well as his books. Last year I had a need
to
> >  > better understand time-based queries so I wrote to him for pointers
to
> >  > resources. He recommended Rick's book.
> >  >
> >  >   As an aside, most dbms have datetime data types but do not fully
(or
> even
> >  > adequately) support time as presented in the SQL standard. Because
> most
> >  > business (and scientific) databases have a very strong time element
> >  > associated with queries and reports this lack surprises me.
> >  >
> >  >   Anyway, Dave, if that's not sufficient, please ask and I'll try to
> >  > provide
> >  > better answers.
> >  >
> >  > Rich
> >  >
> >  > --
> >  > Richard B. Shepard, Ph.D.   |  Integrity
>  Credibility
> >  > Applied Ecosystem Services, Inc.|Innovation
> >  >  Voice: 503-667-4517  Fax:
> >  > 503-667-8863
> >  > ___
> >  > sqlite-users mailing list
> >  > sqlite-users@sqlite.org
> >  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >  >
> >  ___
> >  sqlite-users mailing list
> >  sqlite-users@sqlite.org
> >  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] More on ICU extension (windows)

[Robert Simpson]

I don't think the sqlite3.exe command-line tool converts its input strings
to utf8 before parsing them.  I think its because non-Windows platforms have
utf8 console sessions, and Windows consoles use the default Windows
codepage.

So when you're inserting from the console, you're inserting
codepage-specific text rather than utf8 text.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Friday, August 01, 2008 3:05 AM
To: General Discussion of SQLite Database
Subject: [sqlite] More on ICU extension (windows)

Graeme schrieb:
> I apparently successfully compiled the ICU extension with:
>   
Has anyone sucessfully compiled the ICU extension for Windows (XP)? If 
so, i would be very interested in the steps, thank you.

Also, isn´t it that the command line interpreter for windows (the one 
offered at the sqlite site) should have the ICU extension built-in?

If so, then i must say that it apparently hasn´t, as i cannot do any SQL 
statements in it whatsoever.

Any diacritic character like "ü" gets apparently garbled in the statements.


I am doing something like this

select * from cities where name like "münchen"; --> no result

whereas if i do

select * from cities where name like "m³nchen"; --> correct results

Note the weird "upper 3" character in the request, which apparently 
represents the Umlaut "ü".

Either the command line interpreter does not correctly handle diacritic 
characters, or my sqlite database was wrongly created (it was an 
automatic conversion from an Access MDB file thought).

Thanks for any help,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


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


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

[sqlite] Shared cache question

[Robert Simpson]

What should be the expected behavior of existing connections when
sqlite3_enable_shared_cache() is enabled, and then another database is
ATTACH'ed to the pre-cache-enabled connection?

 

And further complicate things by saying that the attached database is using
vtables and the original connection is not.

 

What I'm wondering is . could we enhance sqlite3_open_v2() to add a flag for
shared cache enabled, rather than have it be a global setting?  I'd like to
have some connections on a shared cache, and some not . and be able to
attach non-cache-enabled databases to existing connections and have fts3
work.

 

Robert

 

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

Re: [sqlite] Return a rowset from function?

[Robert Simpson]

No, but that would be wicked cool and bring stored procs a lot closer to
reality.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alexey Pechnikov
Sent: Friday, July 25, 2008 11:08 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Return a rowset from function?

Hello!

There is any way to create  'table functions', which are functions that
return 
a rowset and are used in place of a table to generate rows?


Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] User-defined collation UNIQUE INDEX

[Robert Simpson]

I just tried the same steps on a memorydb using the NOCASE collation
sequence, and it worked fine ...


C:\Src>sqlite3 :memory:
SQLite version 3.6.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers on
sqlite> create table foo(myvalue text collate nocase);
sqlite> create unique index fooidx on foo(myvalue);
sqlite>
sqlite> insert into foo(myvalue) values('abc');
sqlite> insert into foo(myvalue) values('abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('Abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique
sqlite> drop index fooidx;
sqlite> select * from foo;
myvalue
abc
sqlite> create unique index fooidx on foo(myvalue);
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:52 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined collation UNIQUE INDEX

No, assigning collation during index creation makes no difference.  I also
tried 
REINDEX with no luck.

I am using verison 3.5.9 on a winxp box (forgot to mention that).

csmith


Robert Simpson wrote:
> Does this work?
> 
> CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
> 
> Robert
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
> Sent: Sunday, July 20, 2008 9:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] User-defined collation UNIQUE INDEX
> 
> I am trying to add a very simple collation via load_extension.  The
> collation is 
> a case-insensitive wchar compare for windows (using _wcsicmp).  It is
> designed 
> for windows pathnames being stored in utf16.  All works fine until I try
to
> add 
> the final touch, a UNIQUE INDEX.
> 
> Question: How do I get a UNIQUE INDEX to follow the collation assigned to
a 
> column?
> 
> NOTE: in the below examples, I also tried assigning the collation during
> index 
> creation rather than table creation ... didn't help any.
> 
> -- collation named PATH was loaded via load_extension
> sqlite> create table test (str text collate path);
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> abc
> sqlite> select * from test where str = 'abC';
> abc
> sqlite> select * from test where str = 'aBC';
> abc
> 
> The above looks great.  Now add the unique index...
> 
> sqlite> create unique index myidx on test (str);
> sqlite> insert into test values ('abc');
> 
> The above is the first problem.  The unique index should of detected that
> the 
> value 'abc' already existed in a 'str' column.
> 
> sqlite> select * from test where str = 'abc';
> sqlite>
> 
> The next problem is that the above select does not find any matching
> records, as 
> it did prior to the unique index being added.  I thought it could be
because
> the 
> table is messed up due to duplicate column values?  So I did the below:
> 
> sqlite> delete from test;
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> sqlite>
> 
> Still no result.  I then removed the unique index:
> 
> sqlite> drop index myidx;
> sqlite> select * from test where str = 'ABC';
> abc
> sqlite>
> 
> Thanks,
> csmith
> 
> 

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


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

Re: [sqlite] User-defined collation UNIQUE INDEX

[Robert Simpson]

Does this work?

CREATE UNIQUE INDEX myidx ON test(str COLLATE path);

Robert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:05 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] User-defined collation UNIQUE INDEX

I am trying to add a very simple collation via load_extension.  The
collation is 
a case-insensitive wchar compare for windows (using _wcsicmp).  It is
designed 
for windows pathnames being stored in utf16.  All works fine until I try to
add 
the final touch, a UNIQUE INDEX.

Question: How do I get a UNIQUE INDEX to follow the collation assigned to a 
column?

NOTE: in the below examples, I also tried assigning the collation during
index 
creation rather than table creation ... didn't help any.

-- collation named PATH was loaded via load_extension
sqlite> create table test (str text collate path);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
abc
sqlite> select * from test where str = 'abC';
abc
sqlite> select * from test where str = 'aBC';
abc

The above looks great.  Now add the unique index...

sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');

The above is the first problem.  The unique index should of detected that
the 
value 'abc' already existed in a 'str' column.

sqlite> select * from test where str = 'abc';
sqlite>

The next problem is that the above select does not find any matching
records, as 
it did prior to the unique index being added.  I thought it could be because
the 
table is messed up due to duplicate column values?  So I did the below:

sqlite> delete from test;
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
sqlite>

Still no result.  I then removed the unique index:

sqlite> drop index myidx;
sqlite> select * from test where str = 'ABC';
abc
sqlite>

Thanks,
csmith

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


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

Re: [sqlite] ANN: SQLite .NET provider updated

[Robert Simpson]

Sure does! 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Harold Wood
Sent: Wednesday, July 16, 2008 7:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] ANN: SQLite .NET provider updated

very very cool!  now does it work with teh compact framework?

--- On Wed, 7/16/08, Robert Simpson <[EMAIL PROTECTED]> wrote:

From: Robert Simpson <[EMAIL PROTECTED]>
Subject: [sqlite] ANN: SQLite .NET provider updated
To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org>
Date: Wednesday, July 16, 2008, 8:22 PM

I don't normally announce releases here, but this one's got some great
stuff
in it.  Those of you using the SQLite ADO.NET provider will want to check
out.

Some highlights in the 52 release:
3.6.0 code merge
Table and View designers - you can now create and design tables and views,
indexes and foreign keys from the Visual Studio Server Explorer with a nice
interactive GUI.  This is still in beta, but it's looking really good.
Trigger designer is coming up soon.
Entity Framework support much improved from the 51 release.  Still in beta
while Visual Studio 2008 SP1 is in beta.

It's public domain, it's open source, and 3.5 years stable.  Why buy a
SQLite ADO.NET provider from someone else?

http://sqlite.phxsoftware.com

Robert



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


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

[sqlite] ANN: SQLite .NET provider updated

[Robert Simpson]

I don't normally announce releases here, but this one's got some great stuff
in it.  Those of you using the SQLite ADO.NET provider will want to check
out.

Some highlights in the 52 release:
3.6.0 code merge
Table and View designers - you can now create and design tables and views,
indexes and foreign keys from the Visual Studio Server Explorer with a nice
interactive GUI.  This is still in beta, but it's looking really good.
Trigger designer is coming up soon.
Entity Framework support much improved from the 51 release.  Still in beta
while Visual Studio 2008 SP1 is in beta.

It's public domain, it's open source, and 3.5 years stable.  Why buy a
SQLite ADO.NET provider from someone else?

http://sqlite.phxsoftware.com

Robert



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

Re: [sqlite] General help, a bit OT

[Robert Simpson]

Without causing a rehash of that long-debated topic, I'll say this:  As it
relates to SQLite, generally you will get better performance out of an
optimized C application talking to SQLite than you will out of .NET talking
to SQLite.

The difference can be insignificant to a user application however -- as
someone is probably not going to notice the difference between your C app
inserting 100,000 rows a second vs. .NET's 90,000 rows a second.

I did a simple insert/read/update test comparing the ADO.NET provider to raw
C++, results here:

http://sqlite.phxsoftware.com/forums/t/19.aspx


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of cstrader
Sent: Tuesday, July 15, 2008 5:36 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] General help, a bit OT

is it true that C is always (or even generally) faster than .net? 


- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:40 AM
Subject: Re: [sqlite] General help, a bit OT


>> -Original Message-
>> From: [EMAIL PROTECTED] 
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:28 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] General help, a bit OT
>> 
>> Oh, sorry, the example was just an example.  The calculations 
>> I need to make on on ColC cannot be done within SQLite.
>>
>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>> >
> 
> AFAIK, the current .NET provider for SQLITE supports custom functions
> implemented in .NET.
> Maybe this will work?
> 
> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;
> 
> You might also want to implement the function in C for speed
> improvements.
> 
> Greetings,
> Stefan.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Rigging up SQLite over LINQ (Entity Framework)

[Robert Simpson]

In case anyone stopped reading after the convoluted LINQ query, here's a
simplified version of what it's trying to do.  It's kindof confusing, but
that's what you get when a computer constructs a query I guess:

create table a (id integer primary key, bid integer);
create table b (id integer primary key, myval varchar);

select *, (select count(c) from (select 1 as c from b where b.id = a.bid))
from a;
SQL error: no such column: a.bid

Is there a way to reference an outer value from an inner sub-query like its
attempting to do here?


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Monday, June 30, 2008 9:10 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] Rigging up SQLite over LINQ (Entity Framework)

Just when I thought I was hitting the home stretch, I came across an
aggregate query test that I'm not sure how to solve.   LINQ generated the
following query programmatically:

-- Begin auto-generated query
SELECT 
1 AS [C1], 
[Project2].[ProductID] AS [ProductID], 
[Project2].[ProductName] AS [ProductName], 
[Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
[Project2].[UnitPrice] AS [UnitPrice], 
[Project2].[UnitsInStock] AS [UnitsInStock], 
[Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
[Project2].[ReorderLevel] AS [ReorderLevel], 
[Project2].[Discontinued] AS [Discontinued], 
[Project2].[CategoryID] AS [CategoryID], 
[Project2].[SupplierID] AS [SupplierID]
FROM ( SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued], 
(SELECT 
Count([Project1].[C1]) AS [A1]
FROM ( SELECT 
1 AS [C1]
FROM[Order Details] AS [Extent2]
LEFT OUTER JOIN [Orders] AS [Extent3] ON
[Extent2].[OrderID] = [Extent3].[OrderID]
LEFT OUTER JOIN [Customers] AS [Extent4] ON
[Extent3].[CustomerID] LIKE [Extent4].[CustomerID]
INNER JOIN [Suppliers] AS [Extent5] ON
[Extent4].[Country] LIKE [Extent5].[Country]
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])
)  AS [Project1]) AS [C1]
FROM [Products] AS [Extent1]
)  AS [Project2]
WHERE [Project2].[C1] > 2
-- End autogenerated query


The error SQLite throws is "no such column: Extent1.ProductID" and I believe
it's in the inner WHERE clause:
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])

I can of course supply a schema, but I think it may be irrelevant and have
more to do with the fact that it's got some inner subqueries referencing an
outer object?

Since the SQL is autogenerated, I have limited control over it.  The actual
LINQ query was:

  var query = from p in db.Products
  where p.Order_Details.Count(od => od.Orders.Customers.Country
== p.Suppliers.Country) > 2
  select p;

Aside from this little issue involving aggregates, SQLite is working nicely
with Microsoft's new Entity Framework.

Robert Simpson



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


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

[sqlite] Rigging up SQLite over LINQ (Entity Framework)

[Robert Simpson]

Just when I thought I was hitting the home stretch, I came across an
aggregate query test that I'm not sure how to solve.   LINQ generated the
following query programmatically:

-- Begin auto-generated query
SELECT 
1 AS [C1], 
[Project2].[ProductID] AS [ProductID], 
[Project2].[ProductName] AS [ProductName], 
[Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
[Project2].[UnitPrice] AS [UnitPrice], 
[Project2].[UnitsInStock] AS [UnitsInStock], 
[Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
[Project2].[ReorderLevel] AS [ReorderLevel], 
[Project2].[Discontinued] AS [Discontinued], 
[Project2].[CategoryID] AS [CategoryID], 
[Project2].[SupplierID] AS [SupplierID]
FROM ( SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued], 
(SELECT 
Count([Project1].[C1]) AS [A1]
FROM ( SELECT 
1 AS [C1]
FROM[Order Details] AS [Extent2]
LEFT OUTER JOIN [Orders] AS [Extent3] ON
[Extent2].[OrderID] = [Extent3].[OrderID]
LEFT OUTER JOIN [Customers] AS [Extent4] ON
[Extent3].[CustomerID] LIKE [Extent4].[CustomerID]
INNER JOIN [Suppliers] AS [Extent5] ON
[Extent4].[Country] LIKE [Extent5].[Country]
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])
)  AS [Project1]) AS [C1]
FROM [Products] AS [Extent1]
)  AS [Project2]
WHERE [Project2].[C1] > 2
-- End autogenerated query


The error SQLite throws is "no such column: Extent1.ProductID" and I believe
it's in the inner WHERE clause:
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])

I can of course supply a schema, but I think it may be irrelevant and have
more to do with the fact that it's got some inner subqueries referencing an
outer object?

Since the SQL is autogenerated, I have limited control over it.  The actual
LINQ query was:

  var query = from p in db.Products
  where p.Order_Details.Count(od => od.Orders.Customers.Country
== p.Suppliers.Country) > 2
  select p;

Aside from this little issue involving aggregates, SQLite is working nicely
with Microsoft's new Entity Framework.

Robert Simpson



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

Re: [sqlite] Table Adapter Update Question

[Robert Simpson]

I'm not exactly sure what they've built on top of my (free) provider, but it
looks like a good portion of their core codebase is my code.  It's a bummer
they decided to split with the free spirit of SQLite and charge for their
provider (based on my public domain code), but to each his own.

Robert
http://sqlite.phxsoftware.com


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Richard W. Kulp
Sent: Wednesday, June 25, 2008 2:15 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Table Adapter Update Question

I am trying to convert a VB 2005 program from a SQLExpress database to
SQLite 3. I have been successful up to the following line of code:
 
Me.Dbo_UsersTableAdapter.Update(Me.TSEPWin5UserSQLiteDB.dbo_Users)

It seems that the DboTableAdapter does not support the Update command.  What
is the preferred way to update a dataset created from a SQLite db? I am
using CoreLab's dotConnect SQLite data provider. Another consideration is
that my ultimate goal is to move from Visual Studio to MonoDevelop.

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


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

Re: [sqlite] CANTOPEN error on Windows systems running TortoiseSVN

[Robert Simpson]

Sounds like the new journal_mode pragma might be a solution for you.

http://www.sqlite.org/pragma.html#pragma_journal_mode



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Filip Navara
Sent: Wednesday, June 04, 2008 2:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CANTOPEN error on Windows systems running TortoiseSVN

On Wed, Jun 4, 2008 at 11:41 PM, Eric Minbiole
<[EMAIL PROTECTED]> wrote:
> It looks like you can configure TortoiseSVN to include / exclude
> specific paths during its searches.  Though I've not tried it, I would
> think you could simply exclude any paths that contain SQLite databases.
>  This thread had some good info:
>
>
http://www.nabble.com/Disable-TSVNCache.exe-to-minimize-disk-IO--to2385446.h
tml
>
> ~Eric

That would solve the specific problem with TortoiseSVN, but other
reports suggest that similar error happens also with Windows Desktop
Search and possibly some other applications. I really hesitate to
include workaround in our installer for specific applications. Thanks
for the hint, though.

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


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

Re: [sqlite] .net wrappers - mono vs. phxsoftware

[Robert Simpson]

1.  "Better" is a relative term.  Since the effort of porting changes from
my version to mono is non-trivial, I imagine that mono will be slower to
adapt to newer SQLite releases in favor of stabilizing the port.  So you
might get a more stable, less frequently updated version from Mono.
2.  Ties in with #1.  Traditionally I've updated more frequently than mono
and adopted newer SQLite builds faster.
3.  Marek Habersack ported the code around February of 2007.  I can't recall
exactly what version he ported.
4.  They have to use the stock sqlite library, so their changes were to call
the native sqlite api directly, and had to remove some of the features I
added to the core.  They also can't combine the native library and assembly
into a single module.
5.  Unsure of this.
6.  No, though I have been insanely busy lately and haven't been able to
devote as much time to SQLite as I have in the past.


You can browse the SVN sources for Mono.Data.SQLite here:
http://anonsvn.mono-project.com/viewcvs/trunk/mcs/class/Mono.Data.Sqlite/Mon
o.Data.Sqlite_2.0/?rev=90845#dirlist

That'll give you an idea how often its updated.

BTW:  I finally got around to posting an update today.
http://sqlite.phxsoftware.com

Robert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Tuesday, May 27, 2008 6:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] .net wrappers - mono vs. phxsoftware

Denis pointed another poster to the url below, which I was unaware of:

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

I'm currently using the phxsoftware ado.net 2.0 wrapper for sqlite by
Robert Simpson, which is excellent.  However, I am surprised to see that
the Mono project recently imported Simpson's code
(http://www.mono-project.com/SQLite).  This begs the following
questions:

1. Which project will be better maintained in the long run?
2. Which maintainer will continue to improve the project, and at what
frequency?
3. When did Mono merge in the phx code base?  (Simpson's latest build is
Dec 2007.)
4. Did Mono make any significant changes to the phx project?
5. Will the Mono project try to stay synchronized with phx?
6. Is Simpson passing the baton?

Does anyone have any insight into this?  Also, I can check this myself
later, but does anyone know off the top of their head how much test
coverage the phx code base has, if any?

RW

PS. Robert - if you are reading this list - THANK YOU.

--Tyco Electronics--
Ron Wilson, S/W Systems Engineer III
434.455.6453 tycoelectronics.com


Important:

This electronic mail message and any attached files contain information
intended for the exclusive use of the individual or entity to which it
is addressed and may contain information that is proprietary,
privileged, confidential and/or exempt from disclosure under applicable
law.  If you are not the intended recipient, you are hereby notified
that any viewing, copying, disclosure or distribution of this
information may be subject to legal restriction or sanction. Please
notify the sender, by electronic mail or telephone, of any unintended
recipients and delete the original message without making any copies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Robert Simpson]

Posted too soon ... change sqlite3_closeAndFreeMutex() to just
sqlite3_close();

My version of sqlite3_finalize() contains this small addition:
if (p->magic == VDBE_MAGIC_DEAD && p->db == NULL)
  {
sqlite3_free(p);
return SQLITE_OK;
  }

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Wednesday, May 14, 2008 7:58 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

I wrote this behavior into the SQLite.NET provider as well.  However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.

  int ret = sqlite3_close (db);

  if (ret == SQLITE_BUSY && db->pVdbe)
  {
while (db->pVdbe)
{
  // Make a copy of the first prepared statement
  Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
  Vdbe *po = db->pVdbe;

  if (!p) return SQLITE_NOMEM;

  CopyMemory(p, po, sizeof(Vdbe));

  // Put it on the chain so we can free it
  db->pVdbe = p;
  ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
  if (ret)
  {
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
  }
  else
  {
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
  }
}
ret = sqlite3_closeAndFreeMutex(db);
  }

  return ret;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
> 
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);

Funnily enough that is exactly what I provide in my Python wrapper for
SQLite!  Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.

Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed.  It can also
ensure the right thing happens when threads are being abused.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


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

Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

[Robert Simpson]

I wrote this behavior into the SQLite.NET provider as well.  However, I make
a temp copy of the open statements in the db->Vdbe and finalize the copies
so that subsequent calls to sqlite3_finalize() on the original pointer will
not fail, but be a no op.

  int ret = sqlite3_close (db);

  if (ret == SQLITE_BUSY && db->pVdbe)
  {
while (db->pVdbe)
{
  // Make a copy of the first prepared statement
  Vdbe *p = (Vdbe *)sqlite3_malloc(sizeof(Vdbe));
  Vdbe *po = db->pVdbe;

  if (!p) return SQLITE_NOMEM;

  CopyMemory(p, po, sizeof(Vdbe));

  // Put it on the chain so we can free it
  db->pVdbe = p;
  ret = sqlite3_finalize((sqlite3_stmt *)p); // This will also free the
copy's memory
  if (ret)
  {
// finalize failed -- so we must put back anything we munged
CopyMemory(po, p, sizeof(Vdbe));
db->pVdbe = po;
break;
  }
  else
  {
ZeroMemory(po, sizeof(Vdbe));
po->magic = VDBE_MAGIC_DEAD;
  }
}
ret = sqlite3_closeAndFreeMutex(db);
  }

  return ret;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, May 13, 2008 7:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed SQLite C/C++ interface behavior change.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Virgilio Alexandre Fornazin wrote:
> A good and new safe could be a sqlite3_close_v2() call prototyped like
> 
> int sqlite3_close_v2(sqlite3 * db, int closePendingStatements);

Funnily enough that is exactly what I provide in my Python wrapper for
SQLite!  Reference counting ensures that the sqlite3_db can't be freed
before all the statements are closed but the above method is a quick way
for the developer to proactively close everything.

Having a close_v2 is most likely the best solution since there won't be
semantics changes over what people have already developed.  It can also
ensure the right thing happens when threads are being abused.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIKlSymOOfHg372QQRAiEbAJ9mbFpensXkTXmJtI90vPfTqMNpswCgmh2/
2HwZvkW8FdDUzWId2mtE5fs=
=oawA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

RE: [sqlite] Is there a difference between NULL und zero byte length BLOB/TEXT?

[Robert Simpson]

> -Original Message-
> From: Lothar Scholz [mailto:[EMAIL PROTECTED] 
> Sent: Friday, January 18, 2008 3:50 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Is there a difference between NULL und zero 
> byte length BLOB/TEXT?
> 
> Hello,
> 
> The last question for today:
> Is there a difference between NULL und zero byte length BLOB/TEXT?
> 
> Need this information for my SQLite GUI Frontend.

In my mind, yes.  An equality test against two empty blobs will result in a
match.  A test of two NULL's will not -- nor will a test of a NULL vs. empty
blob.  So there is a difference between NULL and an empty blob.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] RE: Visual Studio (was Improving performance of SQLite)

[Robert Simpson]

I was a die-hard Amiga programmer years ago and swore never to put a PC on
my desk at home.  Unfortunately they used them at work, so I was forced to
learn it.

After a few years of being a broke enthusiast on the Amiga, I wrote a
business app on the PC in Visual C and VB3.  It was my first Windows app,
and I sold it for 6 figures to a Fortune 100 company.

I quit that day job, started my first software company, and the PC has been
on my desk ever since.

Although I did abandon VB before VB4 came out (in favor of C/C++) I have
been using Visual development tools ever since.  So you guys can knock it
all you like ... I and thousands of others have made quite a pile of money
spinning Microsoft-driven products :)

Robert

> -Original Message-
> From: James Steward [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 17, 2007 3:16 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Improving performance of SQLite. Anyone 
> heard ofDevic eSQL?
> 
> On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote:
> > A hundred or so Visual Basic programmers are cheaper to replace and
> > "maintain" than one good Delphi/C++ programmer. ;-)  That 
> is the reason
> > management likes "Visual ."  Been there, learned that.  Hire the
> > staff from the largest pool, not the most effective.  
> Besides it's damn
> > hard to be a prima donna, when your replacement is ready to jump off
> > that forklift and learn a cushy job.
> 
> Ouch.  Lucky Visual  is not a cross platform language.  Show the
> Visual  programmers a bit of Tcl/Tk and watch them wilt!
> 
> It looks like Xilinx wrote their entire ISE GUI in Tcl/Tk, and the
> backend apps are cross compiled.  I can run it on Linux as well as
> NoDose, and the GUI is identical.  Try that in Visual poop.
> 
> You might have cheap programmers today, but tomorrow they will be less
> useful.  Look out for Tux!
> 
> At every planning meeting I push open source, and cross platform
> solutions, because I know today the majority is still under Bill's
> spell, but the magic in Vista is fading...
> 
> 
> Ciao.
> James.
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Proposed sqlite3_initialize() interface

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 30, 2007 7:19 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Proposed sqlite3_initialize() interface
> 
> As currently implemented, SQLite3 requires no initialization.
> You just start calling SQLite3 interfaces and they work.  We
> can pull off this trick on Unix because pthread mutexes can
> be initialized statically at compile-time.
> 
>   static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
> 
> On win32, we have to initialize mutexes at run-time, but this
> can be done within a contrived mutex that we build off of
> a static integer using InterlockedIncrement().  And mutex
> initialization apparently never fails on win32, so we do not
> have to worry with reporting errors that occur during
> mutex initialization.
> 
> But there are other operating systems using SQLite that do
> not work this way.  They need a way to initialize mutexes
> (and possibly other objects such as malloc) prior to running
> any SQLite interface.  And the initialization needs to be able
> to fail and return an error code.
> 

Is there a reason this can't be checked/done in sqlit3_open() via an
InterlockedCompareExchange() operation on the static integer, and if the
mutexes don't exist and can't be created, you just return a different error
code?

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] how to get file handle from sqlite3 object?

[Robert Simpson]

> -Original Message-
> From: Cyrus Durgin [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 09, 2007 5:02 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] how to get file handle from sqlite3 object?
> 
> i'm wondering if there's a "standard" way to get an open file 
> handle from an
> sqlite3 pointer using the C API.  anyone know?
> 

There's no public way to get this, nor should there be.  The internal
implementation of the database should be kept separate from the logical API
to access it.  Such a function would muddy the water between implementation
and interface and hamper the ability to change the implementation without
changing the interface.

Not all filesystems would be able to return one, nor could it guarantee that
the database is in fit state for someone to fiddle with its internal handle.
Furthermore, it could not be guaranteed that once a caller obtained the
handle that the caller might then do something damaging to it or alter its
state.

Such a function definitely falls into the BAD IDEA category, IMO.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite.Net

[Robert Simpson]

The wrapper on phpguru hasn't been updated in almost 3 years and isn't
actively maintained or supported.  I'm sure it worked fine against an older
version of the SQLite engine, but there've been quite a few changes to the
engine since then.

Robert


> -Original Message-
> From: Michael Martin [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 8:29 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite.Net
> 
> Ok, but why I can't use this wrapper 
> http://www.phpguru.org/static/SQLite.NET.html ?
> 
> 
> -----Message d'origine-
> De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> Envoyé : mercredi 19 septembre 2007 17:16
> À : sqlite-users@sqlite.org
> Objet : RE: [sqlite] SQLite.Net
> 
> And for good reason ... Adapting the sqlite3 api to .NET is 
> not trivial. 
> 
> > -Original Message-
> > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, September 19, 2007 8:09 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLite.Net
> > 
> > I am using this http://www.phpguru.org/static/SQLite.NET.html
> > 
> > I have the "attempted to read or write protected memory" 
> > exception with this wrapper.
> > 
> > If I try to define my own wrapper with sqlite3.dll it crashes 
> > on select statement.
> > 
> > -Message d'origine-
> > De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> > Envoyé : mercredi 19 septembre 2007 17:00
> > À : sqlite-users@sqlite.org
> > Objet : RE: [sqlite] SQLite.Net
> > 
> > If you're using the Finisar library, it doesn't work with the 
> > later sqlite3
> > libraries.  If you're using the PHP one, it hasn't been 
> > updated in almost 3
> > years so it probably has issues as well.
> > 
> > Your best bet is to use the ADO.NET 2.0 provider at
> > http://sqlite.phxsoftware.com
> > 
> > Robert
> >  
> > 
> > > -Original Message-----
> > > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > > Sent: Wednesday, September 19, 2007 7:42 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: RE: [sqlite] SQLite.Net
> > > 
> > > SQLite.NET.2.0.1
> > > 
> > > -Message d'origine-
> > > De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> > > Envoyé : mercredi 19 septembre 2007 16:31
> > > À : sqlite-users@sqlite.org
> > > Objet : RE: [sqlite] SQLite.Net
> > > 
> > > What .NET wrapper are you using? 
> > > 
> > > > -Original Message-
> > > > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > > > Sent: Wednesday, September 19, 2007 7:13 AM
> > > > To: sqlite-users@sqlite.org
> > > > Subject: [sqlite] SQLite.Net
> > > > 
> > > > Hi all,
> > > > 
> > > >  
> > > > 
> > > > I try to use SQLite (sqlite3.dll)  in DotNet; this is my code :
> > > > 
> > > >  
> > > > 
> > > > SQLiteClient db= new SQLiteClient("test.db");
> > > > 
> > > > SQLiteResultSet results;
> > > > 
> > > > results = db.Execute("create table Table1(one 
> > > int primary
> > > > key, two varchar(10));");
> > > > 
> > > > results = db.Execute("insert into Table1
> > > > values(1,'hello');");
> > > > 
> > > > results = db.Execute("insert into Table1
> > > > values(2,'hello');");
> > > > 
> > > > results = db.Execute("select * from Table1");
> > > > 
> > > >  
> > > > 
> > > > The problem was on the select statement because I have a 
> > crash with
> > > > sqlite3.dll or a "attempted to read or write protected memory 
> > > > exception"
> > > > with sqlite.dll.
> > > > 
> > > >  
> > > > 
> > > > I don't understant why.
> > > > 
> > > >  
> > > > 
> > > > Could someone help me please
> > > > 
> > > >  
> > > > 
> > > > Thanks in advance
> > > > 
> > > >  
> > > > 
> > > >  
> > > > 
> > > >  
> > > > 
> > > >

RE: [sqlite] SQLite.Net

[Robert Simpson]

And for good reason ... Adapting the sqlite3 api to .NET is not trivial. 

> -Original Message-
> From: Michael Martin [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 8:09 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite.Net
> 
> I am using this http://www.phpguru.org/static/SQLite.NET.html
> 
> I have the "attempted to read or write protected memory" 
> exception with this wrapper.
> 
> If I try to define my own wrapper with sqlite3.dll it crashes 
> on select statement.
> 
> -Message d'origine-
> De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> Envoyé : mercredi 19 septembre 2007 17:00
> À : sqlite-users@sqlite.org
> Objet : RE: [sqlite] SQLite.Net
> 
> If you're using the Finisar library, it doesn't work with the 
> later sqlite3
> libraries.  If you're using the PHP one, it hasn't been 
> updated in almost 3
> years so it probably has issues as well.
> 
> Your best bet is to use the ADO.NET 2.0 provider at
> http://sqlite.phxsoftware.com
> 
> Robert
>  
> 
> > -Original Message-
> > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, September 19, 2007 7:42 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLite.Net
> > 
> > SQLite.NET.2.0.1
> > 
> > -Message d'origine-
> > De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> > Envoyé : mercredi 19 septembre 2007 16:31
> > À : sqlite-users@sqlite.org
> > Objet : RE: [sqlite] SQLite.Net
> > 
> > What .NET wrapper are you using? 
> > 
> > > -Original Message-
> > > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > > Sent: Wednesday, September 19, 2007 7:13 AM
> > > To: sqlite-users@sqlite.org
> > > Subject: [sqlite] SQLite.Net
> > > 
> > > Hi all,
> > > 
> > >  
> > > 
> > > I try to use SQLite (sqlite3.dll)  in DotNet; this is my code :
> > > 
> > >  
> > > 
> > > SQLiteClient db= new SQLiteClient("test.db");
> > > 
> > > SQLiteResultSet results;
> > > 
> > > results = db.Execute("create table Table1(one 
> > int primary
> > > key, two varchar(10));");
> > > 
> > > results = db.Execute("insert into Table1
> > > values(1,'hello');");
> > > 
> > > results = db.Execute("insert into Table1
> > > values(2,'hello');");
> > > 
> > > results = db.Execute("select * from Table1");
> > > 
> > >  
> > > 
> > > The problem was on the select statement because I have a 
> crash with
> > > sqlite3.dll or a "attempted to read or write protected memory 
> > > exception"
> > > with sqlite.dll.
> > > 
> > >  
> > > 
> > > I don't understant why.
> > > 
> > >  
> > > 
> > > Could someone help me please
> > > 
> > >  
> > > 
> > > Thanks in advance
> > > 
> > >  
> > > 
> > >  
> > > 
> > >  
> > > 
> > > 
> > 
> > 
> > 
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
> > 
> > 
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
> > 
> 
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite.Net

[Robert Simpson]

If you're using the Finisar library, it doesn't work with the later sqlite3
libraries.  If you're using the PHP one, it hasn't been updated in almost 3
years so it probably has issues as well.

Your best bet is to use the ADO.NET 2.0 provider at
http://sqlite.phxsoftware.com

Robert
 

> -Original Message-
> From: Michael Martin [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 7:42 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite.Net
> 
> SQLite.NET.2.0.1
> 
> -Message d'origine-
> De : Robert Simpson [mailto:[EMAIL PROTECTED] 
> Envoyé : mercredi 19 septembre 2007 16:31
> À : sqlite-users@sqlite.org
> Objet : RE: [sqlite] SQLite.Net
> 
> What .NET wrapper are you using? 
> 
> > -Original Message-
> > From: Michael Martin [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, September 19, 2007 7:13 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] SQLite.Net
> > 
> > Hi all,
> > 
> >  
> > 
> > I try to use SQLite (sqlite3.dll)  in DotNet; this is my code :
> > 
> >  
> > 
> > SQLiteClient db= new SQLiteClient("test.db");
> > 
> > SQLiteResultSet results;
> > 
> > results = db.Execute("create table Table1(one 
> int primary
> > key, two varchar(10));");
> > 
> > results = db.Execute("insert into Table1
> > values(1,'hello');");
> > 
> > results = db.Execute("insert into Table1
> > values(2,'hello');");
> > 
> > results = db.Execute("select * from Table1");
> > 
> >  
> > 
> > The problem was on the select statement because I have a crash with
> > sqlite3.dll or a "attempted to read or write protected memory 
> > exception"
> > with sqlite.dll.
> > 
> >  
> > 
> > I don't understant why.
> > 
> >  
> > 
> > Could someone help me please
> > 
> >  
> > 
> > Thanks in advance
> > 
> >  
> > 
> >  
> > 
> >  
> > 
> > 
> 
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite.Net

[Robert Simpson]

What .NET wrapper are you using? 

> -Original Message-
> From: Michael Martin [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 19, 2007 7:13 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite.Net
> 
> Hi all,
> 
>  
> 
> I try to use SQLite (sqlite3.dll)  in DotNet; this is my code :
> 
>  
> 
> SQLiteClient db= new SQLiteClient("test.db");
> 
> SQLiteResultSet results;
> 
> results = db.Execute("create table Table1(one int primary
> key, two varchar(10));");
> 
> results = db.Execute("insert into Table1
> values(1,'hello');");
> 
> results = db.Execute("insert into Table1
> values(2,'hello');");
> 
> results = db.Execute("select * from Table1");
> 
>  
> 
> The problem was on the select statement because I have a crash with
> sqlite3.dll or a "attempted to read or write protected memory 
> exception"
> with sqlite.dll.
> 
>  
> 
> I don't understant why.
> 
>  
> 
> Could someone help me please
> 
>  
> 
> Thanks in advance
> 
>  
> 
>  
> 
>  
> 
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Skype client using SQLite?

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 28, 2007 8:14 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Skype client using SQLite?
> 
> In reference to
> 
>http://www.sqlite.org/cvstrac/tktview?tn=2592
> 
> This is the first public indication we have had that
> Skype is using SQLite in their windows clients.  However,
> the person who wrote the ticket seems to be a bit confused.
> Can any able hackers in the SQLite community confirm that
> the Skype windows client is using SQLite?  It would be
> nice to add them to the page of high-profile users.
> 

Bundle.dat file also confirmed on Windows Skype, found it:
C:\Documents and Settings\\Application Data\Skype\\dyncontent\bundle.dat

In my case the tables are populated.  Example below.

sqlite> .schema
CREATE TABLE bupdate (uri text, type text, meta text, prio integer,id integer, b
ody blob, terms blob);
CREATE TABLE events (id intenger, event integer, event_time integer);
CREATE TABLE install (uri text, type text, meta text, prio integer,id integer, b
ody blob, terms blob);
CREATE TABLE itemkeys (id integer, keys text);
CREATE TABLE reported (id integer, exposed_cnt integer, exposed_time integer, ex
ec_cnt integer, close_cnt integer);
CREATE TABLE stats (id integer, exposed_cnt integer,exposed_time integer, exec_c
nt integer, close_cnt integer,last_expose_start integer, exposing_now integer);
CREATE INDEX bupdate_id on bupdate (id);
CREATE INDEX bupdate_uri on bupdate (uri);
CREATE INDEX events_id on events (id);
CREATE INDEX install_id on install (id);
CREATE INDEX install_uri on install (uri);
CREATE INDEX itemkeys_id on itemkeys (id);
CREATE INDEX reported_id on reported(id);
CREATE INDEX stats_id on stats (id);

sqlite> select * from install;
uri|type|meta|prio|id|body|terms
ui/banners/main/81|text/x-rich|information|1|81|How is your call quality? Please give us your feedba
ck|☺




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Proposed incompatible change to SQLite

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, August 09, 2007 8:38 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Proposed incompatible change to SQLite
> 
> We are considering a change to the SQLite API which is
> technically not backwards compatible.  On the other hand,
> it is difficult to imagine a situation where the change
> would cause anyone any serious hardship.  We suspect that
> most people will consider this change a vast improvement.
> 
> The proposed changes is to these APIs:
> 
> sqlite3_soft_heap_limit();
> sqlite3_enable_shared_cache();
> sqlite3_release_memory();
> 
> As currently implemented (and documented), these 
> routines only work across database connections in 
> the same thread.  We propose to modify this so 
> that these routines work across all database 
> connections in the same process.
> 
> If you think such a change will cause problems for
> you, please let me know.  Tnx.

+1 for this change



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] [VC++ 6] Error compiling VBified source

[Robert Simpson]

> -Original Message-
> From: Gilles Ganault [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 03, 2007 11:12 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] [VC++ 6] Error compiling VBified source
> 
> At 09:53 03/08/2007 -0700, Joe Wilson wrote:
> >This has come up before on the list. The instructions are wrong:
> 
> Sorry, should have thought someone had already tried.. and failed :-/
> 
> Browsing through the archives, it looks like the code might 
> not be 100% 
> reliable, so I'll probably look for another solution to have 
> an up-to-date 
> VB-compatible SQLite DLL.
> 

Download the System.Data.SQLite.DLL I maintain from
http://sqlite.phxsoftware.com

I've "wrapped" all the cdecl SQLite calls with corresponding stdcall
_interop calls and made minor adjustments to them for easier marshaling.
The library was designed to be used from .NET or straight C++, but you can
just as easily use it from VB6.  If using it from a non-dotnet language,
.NET runtimes are not required.  It doesn't statically link to them.

You could also download the source from there and compile just the primary
SQLite amalgamation with my modifications.  The interop.c file will have all
the declarations you need to write your imports in VB.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How to Speed of Inserts

[Robert Simpson]

> -Original Message-
> From: Stephen Sutherland [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 03, 2007 10:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] How to Speed of Inserts
> 
[snip]
>
>So it makes me a bit concerned that when I have to drop my 
> XML repository with 7,000 to 10,000 records it will take 
> forever at application start up ? 
>
>   Is there a way to execute all these inserts much faster 
> than I am currently doing ? 
>
>   I am currently simply using sqlite3_exec(db, "INSERT SQL 
> STATEMENT ", ... etc etc ) 
>   for every single insert statement. 
>
>   Is there away to maximize this speed dramatically to do 
> 10,000 inserts in less than 5 seconds. 
>


3 words  Transaction Transaction Transaction.  Issue a BEGIN before you
start inserting, and a COMMIT afterwards, and then come back and tell us how
many MILLISECONDS it took.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Begining SQLite

[Robert Simpson]

> -Original Message-
> From: Meftah Tayeb [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 24, 2007 3:04 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Begining SQLite
> 
> Hello
> 
> i create my SQLite Database, but it is blank
> ho to Create my Table, view, Query and procedure ?

There are too many SQLite management programs out there to count ... Go to
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

SQLite doesn't support stored procedures.  You'll need to learn ADO.NET
(numerous resources on the internet) in order to use my provider.  Basically
you open a connection with the SQLiteConnection object, create
SQLiteCommand's for queries and iterate over rows with SQLiteDataReader.
You can also use ADO.NET's disconnected model of DataTables and DataSets
with the SQLiteDataAdapter.

If you want to work with Typed Datasets and do databinding, here's an old
video demonstrating that feature in Visual Studio, using the Server
Explorer.  It requires that you've run the installer and installed Visual
Studio support.  Some of the GUI in the provider has changed, but you get
the general idea of how to work with the Server Explorer to create tables,
add some simple data, and then generate a typed dataset from it.

http://sqlite.phxsoftware.com/sqlite.wmv


Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Begining SQLite

[Robert Simpson]

> -Original Message-
> From: Meftah Tayeb [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 23, 2007 3:33 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Begining SQLite
> 
> Hello
> 
> Thank you for this SQLite ADO .Net Data provider
> is working very correctly
> but ho to Create my SQLite database ?
> thank you my frients

To answer your immediate question, just put the name of the database in the
"Data Source" parameter of the connection string.  If it doesn't exist,
it'll be created for you.  

For further questions, I encourage you to visit the SQLite.NET forums.  That
question and many more are discussed in the "How To" forum.

http://sqlite.phxsoftware.com/forums


Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Begining SQLite

[Robert Simpson]

> -Original Message-
> From: Meftah Tayeb [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 23, 2007 12:27 PM
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: [sqlite] Begining SQLite
> 
> hello
> 
> please help me:
> i am New to SQLITE
> i want to begin Using SQLite with my Enterprise Application 
> using Visual 
> Basic 6 and VB .Net (2005)
> 1. ho to Create my First SQLite Database ?
> 2. ho to Connect, Add, modifi, Remove and ... my SQLite database ?
> 3. if you have a SQLite com component accessible in Visual 
> Basic and .Net 
> Interop Service, please Send it to Me

Are you familiar with ADO.NET?  If so, go here and download this ADO.NET
provider:

http://sqlite.phxsoftware.com

It does not work with VB6, but it is a full-blown ADO.NET 2.0 provider for
VB.NET 2005.  If you know how to write code in ADO.NET, you'll immediately
know how to work with SQLite.  The help file will explain the language
syntax and extended features the provider has over and beyond the ADO.NET
framework.

Once its installed, it also integrates with the Visual Studio Server
Explorer.  You can create a connection, run queries, drag and drop typed
datasets, do databinding, and everything else from Visual Studio.

It is a single-file redistributable as well.  Just redistribute
System.Data.SQLite.dll with your application.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite Version 3.4.1

[Robert Simpson]

> -Original Message-
> From: Steinmaurer Thomas [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 23, 2007 1:12 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLite Version 3.4.1
> 
> Hi!
> 
> has anybody tried to compile SQLite for the ARM CPU architecture?
> 

Yup.

http://sqlite.phxsoftware.com



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] sqlite Text fields Vista/ 64 bit/core 2 duo machines

[Robert Simpson]

> -Original Message-
> From: James Forrester [mailto:[EMAIL PROTECTED] 
> Sent: Friday, July 20, 2007 6:37 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite Text fields Vista/ 64 bit/core 2 duo machines
> 
> This may be a problem specific to Vista alone, but I'm 
> running 64 bit and a
> core 2 dou machine. 
> 
> When I try to read a Text field with more than about 35 
> characters I get a:
> 
> "System.AccessViolationException: Attempted to read or write protected
> memory.  This is often an indication that other memory is corrupt."
> 
> entry.Description = reader.GetString(9);
> 
> Same code, same data on an XP pro-machine (single processor, 
> 32 bit) works
> perfectly.  On the 64 I'm compiling for x86.
> 
> Any help greatly appreciated.  As I've exhausted possible 
> combinations.
> 
> Important because we need to move our product which uses 
> sqlite to support
> our Vista customer base.  
> 
> Developing in VS 2005 C#. 

That would fall under my jurisdiction.  E-mail me at robert at
blackcastlesoft dot com, or post on the SQLite.NET forums at
http://sqlite.phxsoftware.com and I'll try and help figure out what's wrong.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Does Transaction object roll back automatically on exceptions?

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 16, 2007 9:14 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Does Transaction object roll back 
> automatically on exceptions?
> 
> "Ahmed Sulaiman" <[EMAIL PROTECTED]> wrote:
> > Hi all,
> > 
> > I have tried this question in the forum with no answer, so 
> I decide to
> > give it a try here :)
> > 
> > I am using the "Using" statements in C# to handle the 
> closing of SQLite
> > connection, transaction and command objects.  I have this setup in
> > different places in my code (Generic methods returning 
> SQLite objects)
> >   
> > using (IDbConnection conn = CreateConnection())
> >  {
> >conn.Open();
> >using (IDbTransaction transaction = conn.BeginTransaction())
> > {
> > Dosomething();
> > transaction.Commit();
> > }  
> >} 
> > 
> > My question, what happens if there was an exception inside the
> > transaction using block? I know that the transaction object would be
> > closed and disposed, but does the traction roll back 
> automatically by
> > the SQLite engine, or do I need to have special logic for 
> that? What is
> > the suggested best practices in this case?
> >  
> 
> In the equivalent code in TCL, the transaction would
> rollback automatically.  But I do not know anything
> about the C# bindings that you are using, so I do not
> know what will happen in your case.  Have you tried it
> to see?  What does the documentation that comes with
> your SQLite C# bindings say?  It is the C# bindings,
> not the SQLite core, that will determine this behavior.

Answered on the SQLite.NET forums.  The ADO.NET wrapper automatically roll
back as well.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Introducing... ManagedSQLite

[Robert Simpson]

> -Original Message-
> From: Yves Goergen [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, June 26, 2007 4:55 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Introducing... ManagedSQLite
> 
> On 26.06.2007 00:24 CE(S)T, WHITE, DANIEL wrote:
> > The main advantage of mine is that it is lightweight and easy to use
> > without using ADO.NET.
> 
> Okay, the other SQLite.NET DLL has 592 kB, that's not necessarily
> "lightweight". I'd like to be able to include the SQLite library into
> the main assembly so that I won't need a separate DLL anymore. I'm not
> sure though how good that works with each solution or whether it's a
> good idea for other reasons.
> 
> But I'm quite happy with it without using ADO.NET. In fact, I 
> don't like
> the idea of copying each and every record into memory and having it
> sorted and filtered there (which ADO.NET propagates) instead of having
> that done directly in the source database engine...

The nice thing about ADO.NET (other than all the bells and whistles built on
top of it) is that you can decide where you want to process things.  If
you're using a client/server model and want to process on the client-side,
you can use the disconnected model that behaves as you've described.  If
you'd rather communicate directly with the database and process per-row as
quickly as possible, you can use the DbCommand/DbDataReader model.  The
Command/DataReader model conforms very nicely with SQLite's
sqlite3_prepare/sqlite3_step model and is a relatively thin layer on top of
it.

The 592k desktop version of the library is built for performance using PGO
(profile guided optimizations) and includes both FTS1 and FTS2 modules (for
now) as well as the ADO.NET wrapper assembly.  While some may argue whether
or not 592k is "lightweight", it's definitely not in the heavyweight
category either.  On the desktop, 592k is really considered a pittance.

When built to minimize size on the Compact Framework w/o FTS1, the library
is only 476kb.  Considering the alternative databases available for the
Compact Framework, that's roughly 1/3rd the nearest competitor's size.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Trouble compiling with MSVC++ 6.0

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, June 25, 2007 2:39 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Trouble compiling with MSVC++ 6.0
> 
> Ticket #2457 reports difficulty building SQLite version 3.4.0
> using MSVC++ 6.0.  This appears to be a bug in MSVC++.  See the
> comments on the ticket for details:
> 
>http://www.sqlite.org/cvstrac/tktview?tn=2457
> 
> I do not own MSVC++ (and have no intention of acquiring a copy)
> so I am unable to reproduce.  On the other hand, Version 3.4.0
> has been out for a week and there have been no other reports
> of problems, so I am somewhat suspicious that this problem is
> specific to the specific installation.  
> 
> Can anybody shed any light on ticket #2457?  Can anybody else
> reproduce the problem or suggest a work-around?

Error in MSVC 6.0 -- the user who wrote the ticket hasn't installed the
latest service pack of VC6.

http://support.microsoft.com/kb/890892

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Introducing... ManagedSQLite

[Robert Simpson]

> -Original Message-
> From: WHITE, DANIEL [mailto:[EMAIL PROTECTED] 
> Sent: Saturday, June 23, 2007 7:29 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Introducing... ManagedSQLite
> 
> Howdy all!
> 
> I am just writing tonight to let you know that a project of mine has
> opened up to being open source -- ManagedSQLite.  It is a 
> light wrapper
> around SQLite 3.4.0 that was originally written by Rob Groves.  I have
> added support for Unicode to his wrapper, then added my Managed (.NET)
> port to the mix.  One DLL file unlike others out there.  Supports FTS1
> and FTS2 out of the box!!!
> 
> I am looking for help with this little project.  I think we could
> eventually make an entire ADO.NET wrapper with this thing.  Thanks.
> 
> http://code.google.com/p/managed-sqlite/


Actually I've already gone and written an ADO.NET 2.0 wrapper for SQLite
more than 2 years ago.  It's even a single-file binary :)

http://sqlite.phxsoftware.com

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 31, 2007 4:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
> 
> You have explained the problem, which is .NET not Sqlite.  You have
> apparently done a fine job marrying the two but it might be more
> logical
> to suggest that .NET be made more flexible.
> 
> As for flexibility, programs in C or Assembler are only inflexible at
> the level of the underlying machine or operating system.  They are a
> tool to use to build an environment.
> 
> Initially the typing rules in Sqlite appeared to be a nusisance but on
> deeper thought their utility became apparent and it was possible to use
> them to great advantage.  To bloat or impair that advantage just to
> match a particular concept like .NET would be a tragedy.
> 
> You might consider developing an SQL engine ideally adapted to .NET.

I don't really know how we got here :) I think SQLite is a fantastic engine,
fast, free and flexible -- I wouldn't have spent the last 2 years
maintaining an ADO.NET provider for it if I didn't love it.  I'm certainly
not going to throw the baby out with the bath water and try and roll my own
SQL engine -- it misses the point entirely.  I'm here to support SQLite.

I'm not complaining about SQLite's lack of typing in general.  Could I use a
little help in making SQLite fit more seamlessly into these typed
environments?  Sure, the more help I get the better!

I'd love it if I could issue a CAST([MyCol] AS HAIRYLLAMA) and have some way
to yank HAIRYLLAMA out of the statement's metadata so I could tell what
specific type that column was CAST to.

Heck, I'd love it even more if there was an additional parameter in the
user-def function callback to provide SQLite a string name of a type
returned from a function so we could have more descriptive typing in there
as well.

The problem isn't .NET.  The problem is that every other database on the
planet enforces type restrictions, and hence every generic database access
layer (ODBC, OLEDB, ADO, ADO.NET) is designed around that concept.  "Don't
use a wrapper" doesn't work for every circumstance, and I can't change the
ADO.NET spec -- though I do get around it where I can to support SQLite's
typelessness.

My opinion is: Since SQLite is typeless, then it should probably have a few
more functions dedicated to type description.  Preserving the destination
typename on a CAST as well as in a userdef function would go a long way
toward accomplishing that.  Or even better, some kind of extensible type
system that enabled us to have type-specific comparison callbacks.

However -- we're not there, and may never get there, so I'll continue to
hack.  That's what open source is for, afterall :)

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 3:56 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You are looking for a fit to one particular restrictive, proprietary
> environment.  Our approach has been to work with the spirit of Sqlite
> and to its strengths and to that end we designed out environment
> accordingly.  Sqlite's typing has become a major asset, not a
> difficulty.

All environments, proprietary or not, are restrictive in one way or another
- including C.  SQLite is flexible and adaptable, and capable of being
wedged into quite a few places -- which is what makes it a great little
engine.

For you that means captializing on SQLite's strengths and using its
typelessness as an asset.  For me, it means bringing SQLite to a mass of
.NET folks (Mono and MS) who would otherwise pass it by.  If that means
kludging a type system together to hide SQLite's typelessness, so be it.
I'd rather blunt the edge than throw the whole knife out.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 8:40 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You have just given an excellent explanation of why the wrapper
> approach
> is flawed.  Think about it.

Every approach not deliberately designed around a specific module's exposed
API is fundamentally flawed in that it is limited by the constraints of the
wrapper.  The answer is not "don't use a wrapper" though.

"Think about it"
In my case, I can only speak for .NET and Microsoft environments, so here
goes:

1.  The complexities of interop'ing with SQLite's API are enormous -- an
insurmountable feat for a novice programmer to get right.  As a matter of
fact, most would simply get it wrong, blame it on SQLite, and switch to a
database engine that works better with their chosen design environment.

2.  A mountain of custom code has to be written to interact with SQLite, all
to use an engine who's familiar SQL92 syntax is so tantalizingly close to
the other databases they've used that one can't help to wonder "why do I
have to go to all this trouble just to use the same SQL syntax as my other
database programs?"  A wrapper solves this problem.

3.  A user can learn one API and apply it to multiple databases, instead of
learning one database API and applying it to a single type of database.

4.  The user can write engine-agnostic code and concentrate on the SQL
variations rather than wondering if they get the API code right and called
things in the right sequence.

5.  The wrapper expands the influence and usage of SQLite significantly.

Lets face it ... everyone likes potato chips, but if you had to make them
yourself everytime you got a craving, you'd probably think twice.  On the
other hand, if someone already made them and all you had to do was pick them
off the shelf, you'd be much more inclined to eat them.

So yes, homemade potato chips do taste better than the ones you buy at the
store, provided you get good potatoes, prepare them, peel them with a proper
tool, and watch them in the fryer carefully to avoid burning them.  Of
course you stink up the house and make an enormous mess, but that's the
price you pay for good chips.

So yes, the wrapper approach is flawed, but most folks don't want to stink
up their house trying to roll their own access layer.

Robert
 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 6:18 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> Your comments endorse the approach we took which was to avoid the
> wrapper concept entirely with its inherent limitations  We use Sqlite
> as
> an embedded database in an application server rather than trying to
> integrate an API which wraps Sqlite.
> 
> In the case of the date we implement date arithmetic and comparison
> functions building upon the excellent date primitives in the Sqlite
> source.  However your case does not require any special functions as it
> is handled simply by the regular Sqlite date functions.
> 
> An example of the use of date arithmetic might be:
> 
> SELECT * FROM mytab WHERE days_overdue(due_date) > 90;
> 
> We implement the user functions either as native code or as Javascript.
>   The Javascript has the advantage that the text is stored in the
> database so the functions can be state-driven.

Ok, I grant you that was a bad example -- I was in a hurry when I posted it.
It also requires that datetimes are stored in the database as a string.  If
someone used a numeric type instead, you'd be forced into using a
user-defined function for a comparison against a literal string.  If all
users were nice and tidy and used parameterized queries, the world would be
a better place -- but try as you might to drill it into folks, they still
throw their literals into a query and defeat your nice inline type
adjusters.

I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into
the ADO.NET vNext (Entity Framework) recently, so a lot of my type
frustrations stem from that effort.

MS's Entity Framework uses CAST() liberally in its SQL construction, and
expects the returned values to match up to the type it was CAST() to --
which is currently impossible to do.  I can't cast to a datetime, guid,
int32, float, etc in SQLite and there's no way for me to tell what datatype
was mentioned in the CAST statement for a given returned column.  So when
I'm queried for the type of a column returned from one of these SELECT's,
there's no way for me to give back an accurate type.

I've hacked it up and done it, but its a bit ugly.  Fortunately users don't
need to see the underlying SQL generated from the Entity Framework :)

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 28, 2007 4:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> We actually do that with our Sqlite interfaces.  We use the declared
> type to specify the type and perform a conversion when necessary.  For
> example if the declared type of a column is DATE we know that we
> actually have a FLOAT so when we call a Javascript SQL function for
> example the Sqlite user function transforms the floating point number
> declared as a DATE to be a Javascript Date object.
> 
> A FLOAT with declared type DATE may be transformed into a date string
> in
> accordance with the rules of the chosen locale when being embedded in
> an
> HTML page.
> 
> Our applications use DECIMAL arithmetic for accuracy so when a column
> is
> declared as DECIMAL its actual type is TEXT but arithmetic rules are
> applied.  If Sqlite has decided to make it an INTEGER or a FLOAT the
> correct conversion is made with the declared precision and scale.

I use the declared type as well (where possible) in the SQLite .NET
provider.  Still, it'd be better if we could have an extensible type system
built into the engine itself so one could perform literal to column
comparisons and other SQL statements that are beyond the scope of a wrapper
to provide type adjustments for.

Your date example is a perfect example of a place where an extensible type
system would be ideal.  A SQL statement against a DATE field such as ...

  SELECT * FROM FOO WHERE [TheDate] < '2007/01/01'

...is pretty much impossible to fix in a wrapper -- but if we had an
extensible type system we could provide our own comparison func that takes
[TheDate]'s value and the literal value and figures out how to interpret and
compare the two values.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 28, 2007 9:11 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
> 
> 
> > SQLite does not have a dedicated DATE type.
> 
> I know that, but why it does't create appropriate column definition ?
> create table tab(col date);
> creates a table with "date" type.
> create table tab2 as select * from tab;
> also.
> This type does't do much, but it can be queried with
> sqlite3_column_decltype.
> This way I know what to do with text stored in the database.
> Currently the only way I see is to create table and then insert .. as
> select
> which seems to be weird in presence of a function that "changes the
> datatype".

sqlite3_column_decltype() returns the declared type of the column in the
table, not the name of the type declared in a particular row in a particular
column of a table.

I wonder how feasible it'd be to implement an extensible type system into
SQLite's core.  We have extensibility through user-defined functions and
collating sequences, why not user-defined types?

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still getting "Insertion failed because database isfull." errors

[Robert Simpson]

> -Original Message-
> From: Christian Schwarz [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 18, 2007 7:25 AM
> To: sqlite-users@sqlite.org
> Subject: AW: [sqlite] Still getting "Insertion failed because database
> isfull." errors
> 
> > the database residing on removable media.  When the system returns,
> the
> > "pointer" to the media is not guaranteed to work again.  In other
> words,
> 
> The file handle remains perfectly valid when the media has not been
> removed or changed. Besides, I've observed that sometimes the media is
> not accessible at all after an application was trying to use one of the
> open file handles while the media is being remounted. Maybe that's just
> a sign for a poorly written driver and thus device dependent...

It is in fact, device-dependent.  Some hardware manufacturers have more
durable remounting infrastructure that enables your file handles to survive
a resume from standby, and some don't.

I always recommend short-lived connections on a mobile device.  Get in and
get out quickly, and don't keep anything in memory or active longer than it
needs to be.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Leading zeros in strings

[Robert Simpson]

Issued from the 3.3.12 comand-line:

 

CREATE TABLE blah (ID INTEGER PRIMARY KEY, STUFF string NOT NULL);

INSERT INTO blah (STUFF) VALUES('00302');
SELECT * FROM blah;

1|302

 

Same query, slightly different table definition:

 

CREATE TABLE blah (ID INTEGER PRIMARY KEY, STUFF text NOT NULL);

INSERT INTO blah (STUFF) VALUES('00302');
SELECT * FROM blah;

1|00302

 

Should there be a difference between declaring a column as type 'text' and
type 'string' ?


Robert

 

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

> -Original Message-
> From: Robert Simpson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 8:25 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 

> __declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt
> *pstmt,
> int cursor, sqlite_int64 *prowid)

Oops I forgot to de-decorate this call ... just pretend.

And I also neglected to mention that the input statement must have already
been started with a previous call to sqlite3_step() -- as these functions
will return the rowid information for the row you've just stepped into.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
engine to help me retrieve key information.  My requirements were a little
more complex than just getting rowid, but here's what I did to get me that
far at least:

Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
  1.  For each table, fetch the internal cursor for that table in the
statement
  2.  For each cursor, fetch the rowid

That was the API modification part.  Caveats are that this information is
not always available.  So if its not, I just return a failure indicator.

The next part involved querying the schema of each table, looking up indexes
and finding the most logical primary key definitions for the table(s)
involved in the select, and compiling a side-by-side SELECT clause returning
the user's defined primary keys using a WHERE clause with the above fetched
rowid's.

So given these table definitions:
CREATE TABLE Foo (MyID INTEGER, BarID INTEGER, MyValue, CONSTRAINT pk
PRIMARY KEY(MyId, MyKey));
CREATE TABLE Bar (BarID INTEGER PRIMARY KEY, MyBarValue);

And this SELECT clause:
SELECT MyValue, MyBarValue FROM Foo INNER JOIN Bar ON Foo.BarID = Bar.BarID;

I could then construct a hidden side-by-side query, looking like:
SELECT MyID, BarID FROM Foo WHERE ROWID = ?
(I already had the rowid from the Bar table without a sub-query using the
API calls I made earlier)

All this is wrapped up inside the SQLiteDataReader when you specify a
KeyInfo flag to open the reader on the command.

As for the API extensions ...

The only reason I never posted it and wasn't sure of its suitability is that
I've only ever tested it the way I use it and dunno how stable it is for
general purpose usage.  Also, the initial function call to
sqlite_table_cursor requires the database index and root page number which
can only be obtained by previous fetches into the database schema.

Here's the code at any rate:

/*
[in] pstmt -- The statement to fetch a cursor on
[in] iDb   -- The database index (id from a previous call to
PRAMGA database_list)
[in] tableRootPage -- Root page of the table, (fetched from
sqlite_master table)

   For a given row-returning statement which may contain multiple cursors,
return the index of
   the cursor responsible for iterating the specified table.  You must
specify the root page of that
   table and the database it's in to get a match.

   RETURNS
   -1 on failure, or a 0-based index of the cursor for the given table
*/
int sqlite3_table_cursor(sqlite3_stmt *pstmt, int iDb, Pgno tableRootPage)
{
  Vdbe *p = (Vdbe *)pstmt;
  int n;

  for (n = 0; n < p->nCursor && p->apCsr[n] != NULL; n++)
  {
if (p->apCsr[n]->isTable == FALSE) continue;
if (p->apCsr[n]->iDb != iDb) continue;
if (p->apCsr[n]->pCursor->pgnoRoot == tableRootPage)
  return n;
  }
  return -1;
}

/*
[in] pstmt   -- A row-returning SQLite statement
[in] cursor  -- A cursor previously returned from sqlite3_table_cursor()
[out] prowid -- The rowid for the specified cursor

Fetches (if possible) a rowid for a given cursor in a row-returning
statement.

RETURNS
0 on success, or one of the SQLite error codes otherwise
*/
__declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
  Vdbe *p = (Vdbe *)pstmt;
  int rc = 0;
  Cursor *pC;

  if (cursor < 0 || cursor >= p->nCursor) return SQLITE_ERROR;
  if (p->apCsr[cursor] == NULL) return SQLITE_ERROR;
  pC = p->apCsr[cursor];

  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) return rc;

  if( pC->rowidIsValid )
  {
*prowid = pC->lastRowid;
  }
  else if(pC->pseudoTable )
  {
*prowid = keyToInt(pC->iKey);
  }
  else if(pC->nullRow || pC->pCursor==0)
  {
return SQLITE_ERROR;
  }
  else
  {
if (pC->pCursor == NULL) return SQLITE_ERROR;
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = keyToInt(*prowid);
  }
  return 0;
}





> -Original Message-
> From: Ralf Junker [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 4:23 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 
> 
> >> The SQLite library lacks certain API required to implement cell
> >> editing: Most notably, there is no functionality to retrieve
> >> the exact origin (table, row and column) of a cell, which must
> >> be known to store back the modified value.
> >
> >See:
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name
> 
> I know, and these functions were much appreciated when they entered
> SQLite. However, they do not cover the table row (RowID). Here is the
> discussion about what's still missing. The thread also points out a few
> inconsistencies in the implementation of the above functions, IIRC:
> 
>   

RE: [sqlite] sqlite / gac

[Robert Simpson]

http://sqlite.phxsoftware.com is the site where I develop and maintain the
SQLite ADO.NET 2.0 wrapper.  The project is 2 years old and is pretty
mature.  You'll find the forums pretty informative and I try and answer
every question there fairly quickly.

Robert Simpson


> -Original Message-
> From: Andrew [mailto:[EMAIL PROTECTED]
> Sent: Sunday, February 04, 2007 5:31 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite / gac
> 
> Hi Ian.
> 
> Thanks for the info.
> 
> P/Invoke is unsafe in the C# / common language infrastructure
> sense.  Anything unmanaged is unsafe.
> 
> I had been planning to use the System.Data.SQLite implementation from
> http://sourceforge.net/projects/sqlite-dotnet2 .  After I wrote the
> first email to this list, I was able to install it in the global
> assembly
> cache manually using "gacutil /i".  I still don't know whether that is
> all that is required.
> 
> Is System.Data.SQLite, then, a managed wrapper around the unmanaged
> part
> of SQLite?
> 
> Andrew
> 
> On Sun, Feb 04, 2007 at 08:16:50AM -0400, Ian Frosst wrote:
> > SQLite isn't managed code: it's unmanaged.  As such, unless you write
> the
> > p/invoke code yourself, I'd recommend finding a good SQLite .Net
> wrapper,
> > especially one of them that use ADO.Net.  You can then follow normal
> rules
> > for GAC registration with the wrapper.
> >
> ...
> > Again, SQLite's  DLL is unmanaged code, not managed, so it  can't go
> into
> > the GAC.
> >
> > I don't think using the p/invoke services in C# causes you to drop
> into
> > unsafe mode, but I'm not 100% sure of that.
> >
> > If you find yourself a wrapper that makes use of ADO.Net, you can
> find
> > numerous examples of it's use.
> >
> > With the right wrappers, it should work splendidly (I use it from C#
> through
> > a COM wrapper myself.)
> > Cheers,
> > Ian
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite in the wild

[Robert Simpson]

Just found out on my forums that the National Library of Medicine is using
SQLite.  They provide a free software program for HAZMAT first-responders to
use on their PDA's to get information on hazardous materials.

>From their website:
Mobile support, providing First Responders with critical information in the
palm of their hand. 
Comprehensive decision support, including assistance in identification of an
unknown substance and, once the substance is identified, providing guidance
on immediate actions necessary to save lives and protect the environment. 
Access to 400+ substances from NLM's Hazardous Substances Data Bank (HSDB)
which contains detailed information on over 4,700 critical hazardous
substances 
Rapid access to the most important information about a hazardous substance
by an intelligent synopsis engine and display called "Key Info" 
Intuitive, simple, and logical user interface developed by working with
experienced first responders

More info can be found here:
http://wiser.nlm.nih.gov/


Robert Simpson
Programmer at Large
http://sqlite.phxsoftware.com




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: does select reliably return insert order?

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 21, 2007 2:40 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: does select reliably return insert order?
> 
> 
> > If you use LIMIT/OFFSET without an
> > explicit ORDER BY, you are relying on an implementation detail.
> 
> Yes, but SQLite is an implementation. If it returns rows allways in the
> same
> order I don't see why it shouldn't be acknowledged - one mention in a
> documentation would make it a feature, and I think quite usefull one.

If the current implementation of SQLite returns rows always in the same
order and is documented as such, then future versions of SQLite will be
bound by that.  While that may seem like a Good Thing for now, it will
hamper future optimizations or changes to the pager, btree or other modules.
By leaving it undocumented and not committing to today's implementation, DRH
leaves himself open to change or optimize it later without breaking backward
compatibility.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] ETA for SQLite 3.3.9 ?

[Robert Simpson]

I've been too busy with the holidays to run any of the changes.  I won't be
able to do anything with the code until after the 1st.

Robert


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Saturday, December 23, 2006 12:40 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ETA for SQLite 3.3.9 ?
> 
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > it's been a while since 3.3.8.
> >
> 
> I was thinking the same thing earlier today.
> Perhaps sometime next week.
> 
> There have been a lot of changes lately that
> impact win95 and wince.  I sent out email asking
> people to test those changes but have not gotten
> back a lot of response yet.  I want to wait until
> people actually try those changes before I cut
> a new release.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Need a wince test

[Robert Simpson]

> -Original Message-
> From: Brodie Thiesfield [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 20, 2006 4:43 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need a wince test
> 
> Robert Simpson wrote:
> >> From: Brodie Thiesfield [mailto:[EMAIL PROTECTED]
> >> Robert, you are missing the point. Because of the way this is being
> >> defined, there is a need to check for _UNICODE. If you don't then a
> >> build with _UNICODE defined will fail. If it was implemented like
> the
> >> rest of the functions in os_win.c then it wouldn't be necessary.
> >
> > I can go either way on that.  There is no need to check for _UNICODE
> if you
> > change the defines ... to this:
>  >
>  > # ifdef _WIN32_WCE
>  > //snip
>  > # else
>  > #   define SQLITE_OPEN_LIBRARY(A)  LoadLibraryA(A) // <-- changed to
> A
>  > #   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
>  > # endif
> 
> That way you are ensuring that non-ASCII strings won't work on any
> platform other than WINCE. It's the worse solution so far. Why do you
> have such a problem accepting the _UNICODE define? I feel like I am
> arguing with a brick wall.

I don't have a problem with the _UNICODE define, I have a problem with the
differences in support between CE and the desktop.

> The following patch is a version of drh's that will compile and work on
> in the cases that you and I want (e.g. Unicode build). See my original
> reply to drh as to why it is better than his (although see below for
> why
> it is still bad).
> 
> # ifdef _UNICODE  // hey, look, we catch _WIN_WCE here too!
> static HANDLE loadLibraryUtf8(const char *z){
>WCHAR zWide[MAX_PATH];
>DWORD dwLen = MultiByteToWideChar(CP_UTF8,0,z,-1,zWide,MAX_PATH);
>if (dwLen == 0 || dwLen > MAX_PATH) return NULL;
>return LoadLibraryW(zWide);
> }
> #   define SQLITE_OPEN_LIBRARY(A)  loadLibraryUtf8(A)
> # else
> #   define SQLITE_OPEN_LIBRARY(A)  LoadLibraryA(A)
> # endif
> # define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
> 
> If _UNICODE is defined it works fine. When _UNICODE is not defined (the
> default) it has UTF-8/ANSI coding problems on *all* platforms (unlike
> os_win functions which have them only on Win9x).

CP_UTF8 doesn't work on most CE platforms and hence your proposed patch
doesn't work.

> This is why at a minimum it needs to be included in os_win.c and
> implemented like all other functions there. As per my original patch on
> the bug report. Can we just implement it there, export it from there
> and
> use it in loadext.c without touching the os.h header that drh seems so
> allergic to?
> 
[snip diagram] 
> 
> > Here's what I propose ...
>  > #ifdef _UNICODE
>  > #define OSSTR wchar_t
>  > #else
>  > #define OSSTR char
>  >
>  > OSSTR *utf8toOS(char *utf8)
>  > void utf8toOSFree(OSSTR *apiString)
> 
> Although it is nice since it is simple, it is worse than the current
> solution. Firstly, with the current method, only Windows 95 is broken.
> Additionally, the current solution has the nice property of using the
> unicode functions whenever available (i.e. when running on WinNT)
> regardless of build type.
> 
> Your proposal takes a step backwards to providing Unicode support only
> when compiled as a Unicode application. It is better to continue the
> current method of calling W functions where possible and falling back
> to
> A only when necessary, just process the string sent into the A function
> so that it is properly ACP/OEM as necessary.

I'm not sure what you're getting all worked up over.  How is "providing
unicode support only when compiled as a unicode application" a Bad Thing?

99.9% of all API calls that take a string have an A and W version defined.
The LoadLibrary() function maps to LoadLibraryW when _UNICODE is defined,
and LoadLibraryA() when _UNICODE is not defined.  What's so wrong with this
system?  Why is it so terrible to convert a utf8 string to MBCS and call an
A function when _UNICODE isn't defined, and to convert a utf8 string to
unicode and call the W function when _UNICODE *is* defined?

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Need a wince test

[Robert Simpson]

> -Original Message-
> From: Brodie Thiesfield [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 20, 2006 12:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need a wince test
> 
> Robert, does the patch I provided work as is on Windows CE or not?
> 
> Robert Simpson wrote:
> > There's some flaws in your arguments, Brodie ...
> >
> > 1.  There's no need to do this whole _UNICODE test, only the _WINCE
> test is
> > needed.  All versions of Windows that support unicode also support
> the ANSI
> > versions of the API calls -- the only Windows platform that doesn't
> have any
> > ANSI support is Windows CE.  A single one-character modification to
> DRH's
> > proposed patch is all that's needed for regular Windows desktop
> support.
> 
> Robert, you are missing the point. Because of the way this is being
> defined, there is a need to check for _UNICODE. If you don't then a
> build with _UNICODE defined will fail. If it was implemented like the
> rest of the functions in os_win.c then it wouldn't be necessary.

I can go either way on that.  There is no need to check for _UNICODE if you
change the defines from this:

# ifdef _WIN32_WCE
//snip
# else
#   define SQLITE_OPEN_LIBRARY(A)  LoadLibrary(A)
#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
# endif

To this:

# ifdef _WIN32_WCE
//snip
# else
#   define SQLITE_OPEN_LIBRARY(A)  LoadLibraryA(A) // <-- changed to A
#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
# endif

Here's what I propose ...

#ifdef _UNICODE
#define OSSTR wchar_t
#else
#define OSSTR char

OSSTR *utf8toOS(char *utf8)
{
  // for windows, allocate and return an OS string of the right encoding and
type
  // For *nix, return the utf8 string as-is
}
void utf8toOSFree(OSSTR *apiString)
{
  // For windows, free the apiString
  // For *nix, do nothing
}

Once those are declared in all the os's, then we can dispense with the
rigamarol of calling CreateFileW and CreateFileA, and LoadLibraryW and
LoadLibraryA, etc.  Anytime you make an OS-level call, you call these two
functions to allocate and free an appropriately-converted string.  On *nix
these will be noop functions.

There will still be one exception, but its minor.  GetProcAddress() in
Windows takes an ANSI string for the function name always.  However, since
you can't export a function with a name outside the normal ASCII range,
there's no need to convert the utf8 string to an OS-specific string.
 
Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Need a wince test

[Robert Simpson]

> -Original Message-
> From: Brodie Thiesfield [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 19, 2006 9:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need a wince test
> 
> There are a few problems with your patch.
> 
> +# ifdef _WIN32_WCE
> +static HANDLE loadLibraryUtf8(const char *z){
> +  WCHAR zWide[MAX_PATH];
> +  MultiByteToWideChar(CP_ACP,0,z,-1,zWide,MAX_PATH);
> +  return LoadLibrary(zWide);
> +}
> +#   define SQLITE_OPEN_LIBRARY(A)  loadLibraryUtf8(A)
> +#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddressA(A,B)
> +# else
> +#   define SQLITE_OPEN_LIBRARY(A)  LoadLibrary(A)
> +#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
> +# endif
> 
> The problem in question is not a Windows CE only one. It occurs with
> any
> client that builds in Unicode mode. Therefore you need to test for the
> _UNICODE define instead of _WINCE. Windows CE compilers will also set
> _UNICODE (I believe - Robert?).
> 
> CP_ACP is not UTF-8. Use either CP_UTF8 or your own UTF-8 conversion
> functions from the OS library. Note also that MultiByteToWideChar may
> fail or return ERROR_NO_UNICODE_TRANSLATION (1113L) for UTF-8
> conversions.
> 
> There is no GetProcAddressA. You need to use GetProcAddress.
> 
> The patch will need to be something like the following. Which I have
> tested and builds with no errors or warnings in _UNICODE mode. Still
> need someone to test it in WINCE to be sure.

There's some flaws in your arguments, Brodie ...

1.  There's no need to do this whole _UNICODE test, only the _WINCE test is
needed.  All versions of Windows that support unicode also support the ANSI
versions of the API calls -- the only Windows platform that doesn't have any
ANSI support is Windows CE.  A single one-character modification to DRH's
proposed patch is all that's needed for regular Windows desktop support.

2.  You're ignoring that the ANSI versions that already exist in loadext.c
are potentially passing a utf8 char * to LoadLibraryA() right now and have
been since loadext.c was created.  If you really want to be technical about
it, all the strings passed to LoadLibrary() and GetProcAddress() need to be
converted from utf8 to MBCS for non-CE platforms.

There are two possible solutions, and these are the same issues we've had in
os_win ...

1.  Bite the bullet and realize that Windows API calls are natively MBCS,
not utf8, and convert any char * from utf8 to mbcs before passing them to a
Windows API call.

2.  Just assume that certain functions for Windows are going to take an mbcs
string instead of a utf8 string, like sqlite3_open() and
sqlite3_load_extension()


DRH, your proposed patch almost works with _UNICODE defined.  Just change it
to this instead (my changes are marked with -->):

--- loadext.c   2006/09/22 23:38:21 1.14
+++ loadext.c   2006/12/20 03:37:35 1.15
@@ -223,8 +223,18 @@
 #if defined(_WIN32) || defined(WIN32) || defined(__MINGW32__) ||
defined(__BORLANDC__)
 # include 
 # define SQLITE_LIBRARY_TYPE HANDLE
-# define SQLITE_OPEN_LIBRARY(A)  LoadLibrary(A)
-# define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
+# ifdef _WIN32_WCE
+static HANDLE loadLibraryUtf8(const char *z){
+  WCHAR zWide[MAX_PATH];
+  MultiByteToWideChar(CP_ACP,0,z,-1,zWide,MAX_PATH);
+  return LoadLibrary(zWide);
+}
+#   define SQLITE_OPEN_LIBRARY(A)  loadLibraryUtf8(A)
--> +#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
+# else
--> +#   define SQLITE_OPEN_LIBRARY(A)  LoadLibraryA(A)
+#   define SQLITE_FIND_SYMBOL(A,B) GetProcAddress(A,B)
+# endif
 # define SQLITE_CLOSE_LIBRARY(A) FreeLibrary(A)
 #endif /* windows */
 
Unfortunately the larger issue still remains, that all ANSI api calls in
Windows expect MBCS strings and not utf8 strings.  Technically what you
should do is have two functions:

loadLibraryUtf16() for WINCE that converts the utf8 string to utf16/unicode
and calls LoadLibraryW()
loadLibraryUtf8() for the rest of Windows, which converts a utf8 string to
mbcs and calls LoadLibraryA()

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-