Re: [sqlite] I consider this a bug. Anyone else?

[Teg]

In windows, I believe you want to use the "function level linking"
option to get it to drop the most functions when they aren't used.
Particularly for the amalgamation, I think this will give you the
tightest link. By default, I believe the optimization only operates on
compilation units, meaning the amalgamation would always include
everything in the link stage.

http://msdn.microsoft.com/en-us/library/xsa71f43(v=vs.80).aspx

C



Tuesday, June 26, 2012, 8:52:58 PM, you wrote:


SS> On 27 Jun 2012, at 1:48am, Stephen Chrzanowski  wrote:

>> When you compile the amalgamation with your source code, doesn't the
>> compiler reject/not include/remove/doesn't consider the code that could
>> have been generated from the actual final build?  In other words, if you
>> just have one function being used out of 10,000 functions, the final binary
>> would only include the code for that single function and not the 10,000?

SS> Your compiler will probably be set to do optimization.  It will
SS> not include any functions which are not called.

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




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] I consider this a bug. Anyone else?

[Pavel Ivanov]

On Tue, Jun 26, 2012 at 8:48 PM, Stephen Chrzanowski
 wrote:
> All;
>
> When you compile the amalgamation with your source code, doesn't the
> compiler reject/not include/remove/doesn't consider the code that could
> have been generated from the actual final build?  In other words, if you
> just have one function being used out of 10,000 functions, the final binary
> would only include the code for that single function and not the 10,000?

Compilers definitely can't do that because they generate obj-files and
if functions are not used in one obj-file they can be used in another
one. Linkers probably could do that (although there are lots of
limitations to that) but AFAIK they only optimize out the whole
obj-files, not individual functions. That is unless you turn on Whole
Program Optimization which as you understand is not turned on by
default because usually it requires lots of computational resources.

Pavel


> OP;
>
> The big question is how much time you want to invest to meet your standards.
>
> Seeing that this is a Windows application, not something for a handheld
> with limited resources, if the above is true, and you "pride" yourself on
> making your binaries as small as possible, personally, I'd think the
> 40-80-160 hours investment time to figure out how to get rid of the pure
> "bloat" the DLL gives a plausible exercise into investigating this, even if
> its on the side.
>
> On top of that, I'm the kind of developer who, if I have to require
> assistance from an external source, I'll leave it external from my main
> binary, that way if something does change, like FF in your case, then my
> application just needs to update a single file, not the entire application.
>
> As much as I can appreciate that your program is under 400k (Which I remind
> you is still smaller than the capacity of a 5 1/4 inch floppy disk, single
> sided), no user on the planet is going to be concerned that your
> application "blew up" to 900kb (Which now fits on a double sided 5 1/4 inch
> floppy).  I don't like bloat as much as the next guy, but when you're
> talking applications the size of a meg, on what seems to be a windows
> platform, NO ONE is going to notice a performance hit between a 400kb
> application and a 900kb application. Not to mention bloatware, to me, is
> more about how fast the system responds.  If it takes more than 15 seconds
> to show your initial UI, or you see elements of your UI just "showing up"
> randomly, that would be bloat, even if its a 1 meg file.  If your
> application is in line with a "hello world" app, is gigabytes in size, but
> everything seems to respond nice and quickly, excellent application, not
> bloat, well worth the download/install time, as an end-user view.
>
> Bottom line, how important is [staying close to 400kb and removing external
> dependencies] vs [having two physical files, in which, one can be updated
> on the fly (Pending no application use)]?  In your testing, if the new DLL
> breaks, simply fix, and push out a new version.  Otherwise, just have your
> app download the DLL from you or the machine hosting SQLite.
>
> The other thing I just thought of, the fact that this library is available
> AT ALL for better-than-free makes me jump for joy and hit my head on the
> ceiling.  Its a little black box that "works".  That 500kb DLL is something
> I didn't have to write, something that I can review and update if I want.
> Its something I didn't have to invent, can easily plug into new
> applications I write, small footprint, etc, etc.  Personally, could be
> 10meg in size, and I'd STILL use it.
>
>
> On Tue, Jun 26, 2012 at 5:39 PM, E. Timothy Uy  wrote:
>
>> " 40-80 hours digging deep into how System.Data.SQLite.dll is built "
>>
>> Lol, I actually did this.
>>
>> On Tue, Jun 26, 2012 at 11:00 AM, Andrew Rondeau
>> wrote:
>>
>> > The answer of "just add sqlite.c to your project" is great when you're
>> > making something in C. The entire world does not use C, nor is C (or
>> > C++) always the best option for a particular project.
>> >
>> > Timothy Uy's offer actually makes a lot of sense if you're using
>> > something other then C or C++.
>> >
>> > For example, I just can't ship a .Net .exe that uses x-copy
>> > deployment, and runs on Linux/Mac (via Mono) unless I invest about
>> > 40-80 hours digging deep into how System.Data.SQLite.dll is built.
>> >
>> > On Tue, Jun 26, 2012 at 9:10 AM, Simon Slavin 
>> > wrote:
>> > >
>> > > On 26 Jun 2012, at 4:55pm, bi...@aol.com wrote:
>> > >
>> > >> Thank you everyone who took the time to comment on my Windows DLL
>> >  question.
>> > >> I'm also glad I'm not the only one who sees the problem with not
>>  having
>> > >> the version in the resource block. This really would have helped when
>> >  Chrome
>> > >> and Firefox updated to a new version of SQLite and all my code stopped
>> > >> working.
>> > >
>> > > This is the reason you will 

Re: [sqlite] I consider this a bug. Anyone else?

[Simon Slavin]

On 27 Jun 2012, at 1:48am, Stephen Chrzanowski  wrote:

> When you compile the amalgamation with your source code, doesn't the
> compiler reject/not include/remove/doesn't consider the code that could
> have been generated from the actual final build?  In other words, if you
> just have one function being used out of 10,000 functions, the final binary
> would only include the code for that single function and not the 10,000?

Your compiler will probably be set to do optimization.  It will not include any 
functions which are not called.

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

Re: [sqlite] I consider this a bug. Anyone else?

[Stephen Chrzanowski]

All;

When you compile the amalgamation with your source code, doesn't the
compiler reject/not include/remove/doesn't consider the code that could
have been generated from the actual final build?  In other words, if you
just have one function being used out of 10,000 functions, the final binary
would only include the code for that single function and not the 10,000?

OP;

The big question is how much time you want to invest to meet your standards.

Seeing that this is a Windows application, not something for a handheld
with limited resources, if the above is true, and you "pride" yourself on
making your binaries as small as possible, personally, I'd think the
40-80-160 hours investment time to figure out how to get rid of the pure
"bloat" the DLL gives a plausible exercise into investigating this, even if
its on the side.

On top of that, I'm the kind of developer who, if I have to require
assistance from an external source, I'll leave it external from my main
binary, that way if something does change, like FF in your case, then my
application just needs to update a single file, not the entire application.

As much as I can appreciate that your program is under 400k (Which I remind
you is still smaller than the capacity of a 5 1/4 inch floppy disk, single
sided), no user on the planet is going to be concerned that your
application "blew up" to 900kb (Which now fits on a double sided 5 1/4 inch
floppy).  I don't like bloat as much as the next guy, but when you're
talking applications the size of a meg, on what seems to be a windows
platform, NO ONE is going to notice a performance hit between a 400kb
application and a 900kb application. Not to mention bloatware, to me, is
more about how fast the system responds.  If it takes more than 15 seconds
to show your initial UI, or you see elements of your UI just "showing up"
randomly, that would be bloat, even if its a 1 meg file.  If your
application is in line with a "hello world" app, is gigabytes in size, but
everything seems to respond nice and quickly, excellent application, not
bloat, well worth the download/install time, as an end-user view.

Bottom line, how important is [staying close to 400kb and removing external
dependencies] vs [having two physical files, in which, one can be updated
on the fly (Pending no application use)]?  In your testing, if the new DLL
breaks, simply fix, and push out a new version.  Otherwise, just have your
app download the DLL from you or the machine hosting SQLite.

The other thing I just thought of, the fact that this library is available
AT ALL for better-than-free makes me jump for joy and hit my head on the
ceiling.  Its a little black box that "works".  That 500kb DLL is something
I didn't have to write, something that I can review and update if I want.
Its something I didn't have to invent, can easily plug into new
applications I write, small footprint, etc, etc.  Personally, could be
10meg in size, and I'd STILL use it.


On Tue, Jun 26, 2012 at 5:39 PM, E. Timothy Uy  wrote:

> " 40-80 hours digging deep into how System.Data.SQLite.dll is built "
>
> Lol, I actually did this.
>
> On Tue, Jun 26, 2012 at 11:00 AM, Andrew Rondeau
> wrote:
>
> > The answer of "just add sqlite.c to your project" is great when you're
> > making something in C. The entire world does not use C, nor is C (or
> > C++) always the best option for a particular project.
> >
> > Timothy Uy's offer actually makes a lot of sense if you're using
> > something other then C or C++.
> >
> > For example, I just can't ship a .Net .exe that uses x-copy
> > deployment, and runs on Linux/Mac (via Mono) unless I invest about
> > 40-80 hours digging deep into how System.Data.SQLite.dll is built.
> >
> > On Tue, Jun 26, 2012 at 9:10 AM, Simon Slavin 
> > wrote:
> > >
> > > On 26 Jun 2012, at 4:55pm, bi...@aol.com wrote:
> > >
> > >> Thank you everyone who took the time to comment on my Windows DLL
> >  question.
> > >> I'm also glad I'm not the only one who sees the problem with not
>  having
> > >> the version in the resource block. This really would have helped when
> >  Chrome
> > >> and Firefox updated to a new version of SQLite and all my code stopped
> > >> working.
> > >
> > > This is the reason you will see so many posts here telling you to build
> > SQLite into your application instead of using a DLL.  Then you are not
> > subject to the choices of any other person with code on your users'
> > computers.  SQLite purposely issued a compact and simple amalgamation
> > version of the source code especially to make this fast and simple.
> > >
> > > 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
> > 

[sqlite] Okapi BM25

[E. Timothy Uy]

Hi, I'm interested in integrating the Okapi BM25 ranking procedure into my
SQLite. Has anyone done this already?

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

[sqlite] convert CString for statement

[deltagam...@gmx.net]

Hello I have as input parameter

CString likeexp

but I think in the following statement  likeexp hast to be char []

rc = sqlite3_bind_text(stmt, 1, likeexp, strlen(likeexp), NULL);


How can i convert likeexp to fit the above statement ?

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

Re: [sqlite] I consider this a bug. Anyone else?

[E. Timothy Uy]

" 40-80 hours digging deep into how System.Data.SQLite.dll is built "

Lol, I actually did this.

On Tue, Jun 26, 2012 at 11:00 AM, Andrew Rondeau
wrote:

> The answer of "just add sqlite.c to your project" is great when you're
> making something in C. The entire world does not use C, nor is C (or
> C++) always the best option for a particular project.
>
> Timothy Uy's offer actually makes a lot of sense if you're using
> something other then C or C++.
>
> For example, I just can't ship a .Net .exe that uses x-copy
> deployment, and runs on Linux/Mac (via Mono) unless I invest about
> 40-80 hours digging deep into how System.Data.SQLite.dll is built.
>
> On Tue, Jun 26, 2012 at 9:10 AM, Simon Slavin 
> wrote:
> >
> > On 26 Jun 2012, at 4:55pm, bi...@aol.com wrote:
> >
> >> Thank you everyone who took the time to comment on my Windows DLL
>  question.
> >> I'm also glad I'm not the only one who sees the problem with not  having
> >> the version in the resource block. This really would have helped when
>  Chrome
> >> and Firefox updated to a new version of SQLite and all my code stopped
> >> working.
> >
> > This is the reason you will see so many posts here telling you to build
> SQLite into your application instead of using a DLL.  Then you are not
> subject to the choices of any other person with code on your users'
> computers.  SQLite purposely issued a compact and simple amalgamation
> version of the source code especially to make this fast and simple.
> >
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I consider this a bug. Anyone else?

[Andrew Rondeau]

While I appreciate the FAQ, it seems to assume that C# / .Net / Mono
are environments where one always makes a platform-specific build, or
one always builds ones dependancie. This is not the case, nor should
it always be the case.

On Tue, Jun 26, 2012 at 11:21 AM, Joe Mistachkin  wrote:
>
> Andrew Rondeau wrote:
>>
>> For example, I just can't ship a .Net .exe that uses x-copy
>> deployment, and runs on Linux/Mac (via Mono) unless I invest about
>> 40-80 hours digging deep into how System.Data.SQLite.dll is built.
>>
>
> Compiling System.Data.SQLite for Mono is fairly easy as long as you
> have the necessary tools (e.g. a Windows [virtual] machine with the
> .NET Framework installed):
>
> https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6
>
> --
> Joe Mistachkin
>
> ___
> 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] Selecting from a GROUP Select

[Igor Tandetnik]

On 6/26/2012 4:02 PM, Pavel Ivanov wrote:

On Tue, Jun 26, 2012 at 3:02 PM, Igor Tandetnik  wrote:

On 6/26/2012 1:19 PM, Peter Haworth wrote:


I still have the issue that, in order to be selected,  the rows in the
groups containing two entries must have a different value in a specific
column.



I'm not quite sure I understand the condition, but perhaps you are looking
for something like this:


SELECT * from TableA WHERE z in (
  SELECT z FROM TableA GROUP BY z
  HAVING count(*)=2 and min(otherColumn) != max(otherColumn)
);


Maybe this can be simplified?


SELECT * from TableA WHERE z in (
  SELECT z FROM TableA GROUP BY z
  HAVING count(distinct otherColumn)=2
);


The two requests are not equivalent. Yours would return groups 
containing two *or more* rows, as long as there are only two distinct 
values in otherColumn. But the OP specifically asked for two-row groups 
only.

--
Igor Tandetnik

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

Re: [sqlite] Selecting from a GROUP Select

[Pavel Ivanov]

On Tue, Jun 26, 2012 at 3:02 PM, Igor Tandetnik  wrote:
> On 6/26/2012 1:19 PM, Peter Haworth wrote:
>>
>> I still have the issue that, in order to be selected,  the rows in the
>> groups containing two entries must have a different value in a specific
>> column.
>
>
> I'm not quite sure I understand the condition, but perhaps you are looking
> for something like this:
>
>
> SELECT * from TableA WHERE z in (
>  SELECT z FROM TableA GROUP BY z
>  HAVING count(*)=2 and min(otherColumn) != max(otherColumn)
> );

Maybe this can be simplified?


SELECT * from TableA WHERE z in (
 SELECT z FROM TableA GROUP BY z
 HAVING count(distinct otherColumn)=2
);


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

Re: [sqlite] Selecting from a GROUP Select

[Igor Tandetnik]

On 6/26/2012 1:19 PM, Peter Haworth wrote:

I still have the issue that, in order to be selected,  the rows in the
groups containing two entries must have a different value in a specific
column.


I'm not quite sure I understand the condition, but perhaps you are 
looking for something like this:


SELECT * from TableA WHERE z in (
  SELECT z FROM TableA GROUP BY z
  HAVING count(*)=2 and min(otherColumn) != max(otherColumn)
);

--
Igor Tandetnik

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

Re: [sqlite] I consider this a bug. Anyone else?

[Joe Mistachkin]

Andrew Rondeau wrote:
> 
> For example, I just can't ship a .Net .exe that uses x-copy
> deployment, and runs on Linux/Mac (via Mono) unless I invest about
> 40-80 hours digging deep into how System.Data.SQLite.dll is built.
> 

Compiling System.Data.SQLite for Mono is fairly easy as long as you
have the necessary tools (e.g. a Windows [virtual] machine with the
.NET Framework installed):

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6

--
Joe Mistachkin

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

Re: [sqlite] Selecting from a GROUP Select

[Pavel Ivanov]

I'm sorry, I didn't understand what you are trying to say. Please elaborate.

> I still have the issue that, in order to be selected,  the rows in the
> groups containing two entries must have a different value in a specific
> column.

What column are you talking about here? And in what query?


Pavel


On Tue, Jun 26, 2012 at 1:19 PM, Peter Haworth  wrote:
> Thank you Pavel, that works great.
>
> I still have the issue that, in order to be selected,  the rows in the
> groups containing two entries must have a different value in a specific
> column.
>
>
> The rows returned by the SELECT can be uniquely identified so I'm guessing
> this will involve linking TableA to itself and checking the necessary
> columns for different values.  Does that sound a reasonable way to approach
> this?
>
> Pete
> lcSQL Software 
>
>
>
> On Sat, Jun 23, 2012 at 9:00 AM,  wrote:
>
>> Message: 5
>> Date: Fri, 22 Jun 2012 19:17:39 -0400
>> From: Pavel Ivanov 
>> To: General Discussion of SQLite Database 
>> Subject: Re: [sqlite] Selecting from a GROUP Select
>> Message-ID:
>>        > >
>> Content-Type: text/plain; charset=ISO-8859-1
>>
>> I guess you need something like
>>
>> SELECT * from TableA
>> WHERE z in (
>> SELECT z FROM TableA GROUP BY z HAVING count(*)=2
>> );
>>
>>
>> Pavel
>>
>>
>> On Fri, Jun 22, 2012 at 6:43 PM, Peter Haworth  wrote:
>> > I have a situation where I need to select entries based on the count of
>> the
>> > number of entries in a GROUP = 2, and also that the value of a specific
>> > column must be different for each row in the group. I then need to select
>> > all the individual rows from the qualifying groups.
>>
> ___
> 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] I consider this a bug. Anyone else?

[Andrew Rondeau]

The answer of "just add sqlite.c to your project" is great when you're
making something in C. The entire world does not use C, nor is C (or
C++) always the best option for a particular project.

Timothy Uy's offer actually makes a lot of sense if you're using
something other then C or C++.

For example, I just can't ship a .Net .exe that uses x-copy
deployment, and runs on Linux/Mac (via Mono) unless I invest about
40-80 hours digging deep into how System.Data.SQLite.dll is built.

On Tue, Jun 26, 2012 at 9:10 AM, Simon Slavin  wrote:
>
> On 26 Jun 2012, at 4:55pm, bi...@aol.com wrote:
>
>> Thank you everyone who took the time to comment on my Windows DLL  question.
>> I'm also glad I'm not the only one who sees the problem with not  having
>> the version in the resource block. This really would have helped when  Chrome
>> and Firefox updated to a new version of SQLite and all my code stopped
>> working.
>
> This is the reason you will see so many posts here telling you to build 
> SQLite into your application instead of using a DLL.  Then you are not 
> subject to the choices of any other person with code on your users' 
> computers.  SQLite purposely issued a compact and simple amalgamation version 
> of the source code especially to make this fast and simple.
>
> 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] I consider this a bug. Anyone else?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 26/06/12 08:55, bi...@aol.com wrote:
> That's a great suggestion but as I said, my main app is only 400 KB.
> I'd really like to keep it that way.

Well, it isn't 400kb if you depend on SQLite being somewhere else on the
system.  And I find it even stranger that you call this a security app yet
somehow depend on a random SQLite somewhere under the control of other apps.

> I pride myself in the size and performance of my app.

Did you know you can compile SQLite to exclude functionality?

  http://www.sqlite.org/compile.html#omitfeatures
  http://www.sqlite.org/footprint.html#relfootprint

What I do is have a .c file like this that does all the SQLite interaction:

  #define SQLITE_API static
  #include "sqlite3.c"

  ... my code to access sqlite ...

That allows the compiler to do a lot of inlining and making code smaller
plus more performant.

> I suspect I'm one of the few Windows security apps still using plain C
> and a little assembly code.

While you can take pride in that sort of thing as a programmer, the
reality is that users don't care what language you used, or how hard the
code was to write, how it behaves for others, how it works on platforms
they don't use etc.  What they do care about is if the resulting app works
for them and their priorities.

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

iEYEARECAAYFAk/p+I0ACgkQmOOfHg372QR9/wCeMutBjoaFyFjN/RD6BcdGIovm
qLUAnjoCN/z+TByIhefrvqspeqPbWmuV
=ROTY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I consider this a bug. Anyone else?

[Random Coder]

On Tue, Jun 26, 2012 at 8:55 AM,   wrote:
> I do realize and appreciate the public domain source is available. My best
> option is recompiling the DLL but since the project and make files aren't
> designed for Visual Studio it will require time that like everyone her I
> cherish.

To me the best option is to include the SQLite library in your project
statically.  It consists of two files, and adding them to your Visual
Studio project is very easy.  It solves the version issues you've run
into, and also allows you to tweak the #defines for SQLite's build to
target your particular app's needs.  If you can't or don't want to
include it statically, at least build your own version of the DLL,
it's very straight forward and you'll have higher confidence over the
feature set included.

While having a version number in the resource section of the DLL would
solve the "which version of sqlite3.dll is this that's been downloaded
from sqlite.org?" issue (of course, so would calling
sqlite3_libversion), it would not solve the "is this a random version
of sqlite3.dll that someone else built?"  And yes, I've seen all sorts
of fun versions of sqlite floating around.

> Creating a new build also requires me to follow an extensive test
> plan.  I do a complete test plan even if I make a simple text change. Having
> the  DLL out in the public being used by thousands beats any test plan.

So you do extensive testing if you change a text string in your
source, but don't test each and every version of
libraries you use?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting from a GROUP Select

[Peter Haworth]

Thank you Pavel, that works great.

I still have the issue that, in order to be selected,  the rows in the
groups containing two entries must have a different value in a specific
column.


The rows returned by the SELECT can be uniquely identified so I'm guessing
this will involve linking TableA to itself and checking the necessary
columns for different values.  Does that sound a reasonable way to approach
this?

Pete
lcSQL Software 



On Sat, Jun 23, 2012 at 9:00 AM,  wrote:

> Message: 5
> Date: Fri, 22 Jun 2012 19:17:39 -0400
> From: Pavel Ivanov 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Selecting from a GROUP Select
> Message-ID:
> >
> Content-Type: text/plain; charset=ISO-8859-1
>
> I guess you need something like
>
> SELECT * from TableA
> WHERE z in (
> SELECT z FROM TableA GROUP BY z HAVING count(*)=2
> );
>
>
> Pavel
>
>
> On Fri, Jun 22, 2012 at 6:43 PM, Peter Haworth  wrote:
> > I have a situation where I need to select entries based on the count of
> the
> > number of entries in a GROUP = 2, and also that the value of a specific
> > column must be different for each row in the group. I then need to select
> > all the individual rows from the qualifying groups.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Pavel Ivanov]

> Is it possible to check even if a db file exists and if necessary create it
> from within c++ ?
> So I would open the db file  sqlite3_open("ah.db", );   only if it really
> exists ..

Why do you need to create file from C++? If file doesn't exist SQLite
will automatically create it for you after sqlite3_open().

Pavel


On Tue, Jun 26, 2012 at 12:27 PM, deltagam...@gmx.net
 wrote:
> Am 26.06.2012 18:00, schrieb Pavel Ivanov:
>
>> On Tue, Jun 26, 2012 at 11:50 AM, deltagam...@gmx.net
>>  wrote:
>>>
>>> Am 26.06.2012 17:08, schrieb Pavel Ivanov:
>>>
 You are leaking stmt statement (re-preparing without finaliznig) and
 your call to sqlite3_close returns SQLITE_ERROR because of that, but
 you don't even check that so you are leaking database connections as
 well.

 Pavel


 On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
  wrote:
>
> Am 26.06.2012 16:49, schrieb Richard Hipp:
>
>> On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
>> wrote:
>>
>>> I have a c++ GUI application from where the db is read and the
>>> content
>>> is
>>> displayed in a Clistbox.
>>> Then I try to delete some rows from the sqlite3-db from the console.
>>> After rereading from within the GUI the deleted rows are still there.
>>>
>>> How is this possible ?
>>>
>> The GUI is holding a read transaction open.  Hence it sees a
>> consistent
>> snapshot of the database from the moment in time when the transaction
>> was
>> started.  Subsequent writes to the database are ignored by the GUI
>> until
>> it
>> closes its current transaction and starts a new one.
>>
>>
>>
>>>
> First, I would like to thank all for the great support, a special thanks to
> Pavel Ivanov and Richard Hipp
>
> I think with
> ==
>
>    char create_sql[] = "CREATE TABLE if not exists eventlog ("
>        "id INTEGER PRIMARY KEY,"
>        "eventdate DATETIME default current_timestamp,"
>        "eventtype TEXT,"
>        "counter INTEGER"
>        ")";
> rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
> =
> it is a convenient way to check if a table exists.
>
> Is it possible to check even if a db file exists and if necessary create it
> from within c++ ?
> So I would open the db file  sqlite3_open("ah.db", );   only if it really
> exists ..
>
>
>
> ___
> 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] access from 2 different programms to same sqlite3-db

[Simon Slavin]

On 26 Jun 2012, at 5:27pm, deltagam...@gmx.net wrote:

> Is it possible to check even if a db file exists and if necessary create it 
> from within c++ ?
> So I would open the db file  sqlite3_open("ah.db", );   only if it really 
> exists ..

Look at the flags available to the _open_v2() function:



I think you want SQLITE_OPEN_READONLY .

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 18:00, schrieb Pavel Ivanov:

On Tue, Jun 26, 2012 at 11:50 AM, deltagam...@gmx.net
 wrote:

Am 26.06.2012 17:08, schrieb Pavel Ivanov:


You are leaking stmt statement (re-preparing without finaliznig) and
your call to sqlite3_close returns SQLITE_ERROR because of that, but
you don't even check that so you are leaking database connections as
well.

Pavel


On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
 wrote:

Am 26.06.2012 16:49, schrieb Richard Hipp:


On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
wrote:


I have a c++ GUI application from where the db is read and the content
is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction
was
started.  Subsequent writes to the database are ignored by the GUI until
it
closes its current transaction and starts a new one.





First, I would like to thank all for the great support, a special thanks 
to Pavel Ivanov and Richard Hipp


I think with
==
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";
rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
=
it is a convenient way to check if a table exists.

Is it possible to check even if a db file exists and if necessary create 
it from within c++ ?
So I would open the db file  sqlite3_open("ah.db", );   only if it 
really exists ..



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

Re: [sqlite] How to know the memory usage of an in-memory database.

[Stephan Beal]

On Tue, Jun 26, 2012 at 4:46 AM, Bo Peng  wrote:

> >> In any case, you set the cache size in pages by executing "PRAGMA
> cache_size=" after opening the database connection.
>
> I am using "PRAGMA cache_size=-300" to set the cache to 3G, but
> the process is still slow-going using 23M of RAM, despite the fact
>

The problem might not be with sqlite, but with your system's allocator or
any number of other factors. Running it through a profiler is the only way
to know for sure. i use "callgrind":

valgrind --tool=callgrind --callgrind-out-file='callgrind.out' ./myapp

and then feed callgrind.out to kcachegrind or similar to get enough
detailed statistics to keep you busy for days.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I consider this a bug. Anyone else?

[Simon Slavin]

On 26 Jun 2012, at 4:55pm, bi...@aol.com wrote:

> Thank you everyone who took the time to comment on my Windows DLL  question.
> I'm also glad I'm not the only one who sees the problem with not  having 
> the version in the resource block. This really would have helped when  Chrome 
> and Firefox updated to a new version of SQLite and all my code stopped  
> working.

This is the reason you will see so many posts here telling you to build SQLite 
into your application instead of using a DLL.  Then you are not subject to the 
choices of any other person with code on your users' computers.  SQLite 
purposely issued a compact and simple amalgamation version of the source code 
especially to make this fast and simple.

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Pavel Ivanov]

On Tue, Jun 26, 2012 at 11:50 AM, deltagam...@gmx.net
 wrote:
> Am 26.06.2012 17:08, schrieb Pavel Ivanov:
>
>> You are leaking stmt statement (re-preparing without finaliznig) and
>> your call to sqlite3_close returns SQLITE_ERROR because of that, but
>> you don't even check that so you are leaking database connections as
>> well.
>>
>> Pavel
>>
>>
>> On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
>>  wrote:
>>>
>>> Am 26.06.2012 16:49, schrieb Richard Hipp:
>>>
 On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
 wrote:

> I have a c++ GUI application from where the db is read and the content
> is
> displayed in a Clistbox.
> Then I try to delete some rows from the sqlite3-db from the console.
> After rereading from within the GUI the deleted rows are still there.
>
> How is this possible ?
>
 The GUI is holding a read transaction open.  Hence it sees a consistent
 snapshot of the database from the moment in time when the transaction
 was
 started.  Subsequent writes to the database are ignored by the GUI until
 it
 closes its current transaction and starts a new one.



>>>
> 
> void InitialReadEventsData()
>
> {
>
>    // Remove all events from array
>    m_arrEvents.RemoveAll();
>
>    // write  events
>    Event newEvent;
>
> // sqlite3 reading ///
>
>        int rc, id, total_events;
>        char *sql, *sqltotal;
>        char *evdate, *evtype;
>        int evctr;
>
>        int the_event_ctr = 0;
>
>        CString datetime;
>        CString datepart;
>        CString timepart;
>
>
>        sqlite3 *db;
>        sqlite3_stmt *stmt;
>
>        sqlite3_open("ah.db", );
> /*
>
>    // check if table eventlog exists
>    char create_sql[] = "CREATE TABLE if not exists eventlog ("
>        "id INTEGER PRIMARY KEY,"
>        "eventdate DATETIME default current_timestamp,"
>        "eventtype TEXT,"
>        "counter INTEGER"
>        ")";
>
>    rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
>
>
> */
>
>
>    // select count(*) from eventlog
>    sqltotal = "Select count(*) from eventlog";
>    rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
>    rc = sqlite3_step(stmt);
>    total_events = sqlite3_column_int(stmt, 0 );
>
>    std::cout << total_events << std::endl;
>
>
>
>    // select * from eventlog
>    sql = "Select id, eventdate, eventtype, counter FROM eventlog";
>    sqlite3_prepare(db, sql, strlen(sql), , NULL);
>
>    rc = sqlite3_step(stmt);
>
> while(rc == SQLITE_ROW) {
>    id = sqlite3_column_int(stmt, 0 );
>    //cid = sqlite3_column_int(stmt, 1 );
>    evdate = (char*)sqlite3_column_text(stmt, 1 );
>    evtype = (char*)sqlite3_column_text(stmt, 2 );
>    evctr = sqlite3_column_int(stmt, 3 );
>
>    datetime = evdate;
>
>    datepart = datetime.Mid(0,10);
>    timepart = datetime.Mid(11,5);
>
>    std::cout << datepart << "\t" << timepart << std::endl;
>
>    newEvent.m_nEvent = the_event_ctr;
>    newEvent.m_strLastEventDate = datepart ;
>    newEvent.m_strEventTime = timepart;
>    newEvent.m_strEventType = evtype;
>    newEvent.m_nCount = evctr;
>    WriteEvent(newEvent, the_event_ctr);
>
>
>    rc = sqlite3_step(stmt);
>
>    // increment eventcounter
>    the_event_ctr++;
>
> } // while
>
> rc = sqlite3_reset(stmt);
>
> rc = sqlite3_finalize(stmt);
> rc = sqlite3_close(db);
>
> // sqlite3 reading ///
>
> }
>
> =
>
> What am I missing now ? There is a rc = sqlite3_reset(stmt);  but the rc =
> sqlite3_close(db);  still returns error_code 5
> The sqlite3_exec is now comment. Do I have to "reset " and finalize this
> part normally too ? How is this done ?


When you prepare "select * from eventlog" statement you do not re-use
sqlite3_stmt object, you create a new one losing pointer to the old
statement. So you have to call sqlite3_finalize(stmt) before calling
sqlite3_prepare() at this point. And you don't have to call
sqlite3_reset() if you'll call sqlite3_finlaize() right after that,
just use sqlite3_finalize().


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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 11:50 AM, deltagam...@gmx.net
wrote:

> Am 26.06.2012 17:08, schrieb Pavel Ivanov:   // select count(*) from
> eventlog
> sqltotal = "Select count(*) from eventlog";
>rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
>rc = sqlite3_step(stmt);
>total_events = sqlite3_column_int(stmt, 0 );
>
>std::cout << total_events << std::endl;
>
>// select * from eventlog
>sql = "Select id, eventdate, eventtype, counter FROM eventlog";
>sqlite3_prepare(db, sql, strlen(sql), , NULL);
>

Error in the previous line.

You have created a prepared statement and stepped it, but you never
finalized it.  Then you overwrote the pointer to the original prepared
statement with a pointer to the new prepared statement, thus leaking the
original prepared statement.



>
>rc = sqlite3_step(stmt);
>
> while(rc == SQLITE_ROW) {
>id = sqlite3_column_int(stmt, 0 );
>//cid = sqlite3_column_int(stmt, 1 );
>evdate = (char*)sqlite3_column_text(**stmt, 1 );
>evtype = (char*)sqlite3_column_text(**stmt, 2 );
>evctr = sqlite3_column_int(stmt, 3 );
>
>datetime = evdate;
>
>datepart = datetime.Mid(0,10);
>timepart = datetime.Mid(11,5);
>
>std::cout << datepart << "\t" << timepart << std::endl;
>
>newEvent.m_nEvent = the_event_ctr;
>newEvent.m_strLastEventDate = datepart ;
>newEvent.m_strEventTime = timepart;
>newEvent.m_strEventType = evtype;
>newEvent.m_nCount = evctr;
>WriteEvent(newEvent, the_event_ctr);
>
>
>rc = sqlite3_step(stmt);
>
>// increment eventcounter
>the_event_ctr++;
>
> } // while
>
> rc = sqlite3_reset(stmt);
>
> rc = sqlite3_finalize(stmt);
> rc = sqlite3_close(db);
>
> // sqlite3 reading //**/
>
> }
>
> ==**==**
> =
>
> What am I missing now ? There is a rc = sqlite3_reset(stmt);  but the rc =
> sqlite3_close(db);  still returns error_code 5
> The sqlite3_exec is now comment. Do I have to "reset " and finalize this
> part normally too ? How is this done ?
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[OBones]

Jay A. Kreibich wrote:

On Tue, Jun 26, 2012 at 03:56:33PM +0200, OBones scratched on the wall:

Hello all,

As I'm experimenting with Virtual Tables, I have had to implement
xBestIndex and xFilter.
The documentation says that idxNum and idxStr are of no importance
to the SQLite core and that it's our responsibility to make sure
xFilter and xBestIndex agree on the meaning.
This is fine by me but I'd like to suggest to change the type of
idxStr from "char *" to "void *" to make it even clearer to everyone
that this parameter is not used in any specific way inside SQLite
core.

   99% of the modules that use idxStr pass a string.  It is usually an
   SQL statement used by xFilter() that has parameter names mapped to
   the parameters defined by xBestIndex().  This is the primary use of
   this API.

   I see you're point, but the variable name is idx"Str", and that's how
   most people use it.  I don't think redefining it as a void* implies
   any more or less ownership by SQLite than a char*.  The docs are
   clear, the code is clear, and that's how the vast majority of users
   use it.  Forcing them to cast back and forth for the exception to the
   rule seems like a bit of waste.

-j
Thanks for the clarification, I assumed it was the case and was just 
making a suggestion.


Regards
Olivier


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

Re: [sqlite] I consider this a bug. Anyone else?

[BillP]

Thank you everyone who took the time to comment on my Windows DLL  question.
I'm also glad I'm not the only one who sees the problem with not  having 
the version in the resource block. This really would have helped when  Chrome 
and Firefox updated to a new version of SQLite and all my code stopped  
working.

I have considered just finding the code I need, stripping it out  and 
compiling it as a static part of my code. That's a great suggestion but as I  
said, my main app is only 400 KB. I'd really like to keep it that way. I pride  
myself in the size and performance of my app. I suspect I'm one of the few  
Windows security apps still using plain C and a little assembly code.
 
I do realize and appreciate the public domain source is available. My best  
option is recompiling the DLL but since the project and make files aren't  
designed for Visual Studio it will require time that like everyone her I  
cherish. Creating a new build also requires me to follow an extensive test 
plan.  I do a complete test plan even if I make a simple text change. Having 
the  DLL out in the public being used by thousands beats any test plan. 
Luckily,  SQLite isn't a major part of my app so I guess I'll try this if the 
consortium  doesn't consider our suggestions.

Thank you to everyone who  participated.
Bill Pytlovany


_BillP Studios_ (http://www.billp.com/)   | This computer protected by  
WinPatrol |
_http://www.winpatrol.com_ (http://www.winpatrol.com/) _  
http://billpstudios.blogspot.com/_ (http://billpstudios.blogspot.com/) 

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 17:08, schrieb Pavel Ivanov:

You are leaking stmt statement (re-preparing without finaliznig) and
your call to sqlite3_close returns SQLITE_ERROR because of that, but
you don't even check that so you are leaking database connections as
well.

Pavel


On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
 wrote:

Am 26.06.2012 16:49, schrieb Richard Hipp:


On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
wrote:


I have a c++ GUI application from where the db is read and the content is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until
it
closes its current transaction and starts a new one.







void InitialReadEventsData()
{

// Remove all events from array
m_arrEvents.RemoveAll();

// write  events
Event newEvent;

// sqlite3 reading ///
int rc, id, total_events;
char *sql, *sqltotal;
char *evdate, *evtype;
int evctr;

int the_event_ctr = 0;

CString datetime;
CString datepart;
CString timepart;


sqlite3 *db;
sqlite3_stmt *stmt;

sqlite3_open("ah.db", );
/*
// check if table eventlog exists
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);


*/

// select count(*) from eventlog
sqltotal = "Select count(*) from eventlog";
rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
rc = sqlite3_step(stmt);
total_events = sqlite3_column_int(stmt, 0 );

std::cout << total_events << std::endl;


// select * from eventlog
sql = "Select id, eventdate, eventtype, counter FROM eventlog";
sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
//cid = sqlite3_column_int(stmt, 1 );
evdate = (char*)sqlite3_column_text(stmt, 1 );
evtype = (char*)sqlite3_column_text(stmt, 2 );
evctr = sqlite3_column_int(stmt, 3 );

datetime = evdate;

datepart = datetime.Mid(0,10);
timepart = datetime.Mid(11,5);

std::cout << datepart << "\t" << timepart << std::endl;

newEvent.m_nEvent = the_event_ctr;
newEvent.m_strLastEventDate = datepart ;
newEvent.m_strEventTime = timepart;
newEvent.m_strEventType = evtype;
newEvent.m_nCount = evctr;
WriteEvent(newEvent, the_event_ctr);

rc = sqlite3_step(stmt);

// increment eventcounter
the_event_ctr++;

} // while

rc = sqlite3_reset(stmt);

rc = sqlite3_finalize(stmt);
rc = sqlite3_close(db);

// sqlite3 reading ///

}

=

What am I missing now ? There is a rc = sqlite3_reset(stmt);  but the rc 
= sqlite3_close(db);  still returns error_code 5
The sqlite3_exec is now comment. Do I have to "reset " and finalize this 
part normally too ? How is this done ?


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

Re: [sqlite] Virtual tables: rename, disconnect and reconnect

[OBones]

Jay A. Kreibich wrote:

On Tue, Jun 26, 2012 at 03:47:07PM +0200, OBones scratched on the wall:

I would have thought that since the disconnect was given a valid
pVTab, this one could have been given back to xConnect when
reconnecting the database after its rename.

   Except SQLite doesn't store the data in that structure.  sqlite3_vtab
   is a "live" structure, in the sense that it only exists in-memory in
   an active database process.  The whole idea of xCreate()/xConnect()
   is to re-create this data structure, every time, from the original
   virtual table declaration.  The whole point of
   xDestroy()/xDisconnect() is to clean up, destroy, and free the
   sqlite3_vtab structure.  It only exists between a pair of those calls.
I understand that SQLite doesn't store the data, but I was thinking that 
it stored the pointer to it during the "rename" internal procedure. 
Hence my question about that.



   Regardless, any possible saving of the state is up to the application.

Which is fine by me.


Is this behavior expected? I'm fine with my current solution, but I
was expecting xConnect to get a non zero *pPVTab most, if not all
the time.

   This is exactly how the API is designed.
Fair enough, I'm in discovery mode here, with some habits from my 
experience but no hard expectations.



   You seem to be fighting the API, which makes me think there might be
   a slight disconnect between how you expect the API to work and how
   the API was designed to be used.
I'm definitely not fighting, merely discovering and learning along the 
way. And because there were not so many discussions on virtual tables in 
the archive, I thought that answers to my questions would help others in 
the future.


Thanks for the pointers, I already read the documentation page and will 
seek for the book.


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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 11:15 AM, Simon Slavin  wrote:

>
> On 26 Jun 2012, at 4:11pm, "Black, Michael (IS)" 
> wrote:
>
> > So...transaction is started at sqlite3_step and stays open until
> sqlite3_reset or sqlite3_finalize, right?
> >
> > sqlite3_close would do what if you didn't finalize?  Just a memory leak
> or worse?  Or would sqlite3_close return an error if there's an open
> transaction?
>
> 
>
> "Applications must finalize all prepared statements and close all BLOB
> handles associated with the sqlite3 object prior to attempting to close the
> object. If sqlite3_close() is called on a database connection that still
> has outstanding prepared statements or BLOB handles, then it returns
> SQLITE_BUSY."
>
>
> on the other hand ...
>
> "If sqlite3_close() is invoked while a transaction is open, the
> transaction is automatically rolled back."
>
> It is not an error to close the file while you're in the middle of a
> transaction.  It just means you didn't commit the transaction.
>

The second statement above refers to an explicit transaction created using
the BEGIN statement.  It does not follow that implied transactions created
by running queries are automatically cancelled when you do
sqlite3_close().  To cancel the implied transaction, you have to
sqlite3_reset() or sqlite3_finalize() the prepared statement that created
the transaction in the first place.


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



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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 11:11 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> So...transaction is started at sqlite3_step and stays open until
> sqlite3_reset or sqlite3_finalize, right?
>
> sqlite3_close would do what if you didn't finalize?  Just a memory leak or
> worse?  Or would sqlite3_close return an error if there's an open
> transaction?
>

The first prepared statement is never reset or finalized.  It is merely
stepped once, and then the pointer to the prepared statement is overwritten
by a different pointer, thus leaking the perpared statement.

Because the prepared statement remains open, the sqlite3_close() call
fails.  The database connection remains open, with a half-run statement
holding open a read transaction.


>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Tuesday, June 26, 2012 9:54 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] access from 2 different programms to same
> sqlite3-db
>
> On Tue, Jun 26, 2012 at 10:51 AM, Black, Michael (IS) <
> michael.bla...@ngc.com> wrote:
>
> > Does that mean the "prepare" is wrapped inside a transaction?
> >
> >
> >
> > So you must finalize and re-prepare?
> >
>
> No.  It just means you need to run sqlite3_reset() on your prepared
> statements when you are finished with them, so that they will release the
> transaction they are holding.
>
>
>
>
> >
> >
> >
> > Michael D. Black
> >
> > Senior Scientist
> >
> > Advanced Analytics Directorate
> >
> > Advanced GEOINT Solutions Operating Unit
> >
> > Northrop Grumman Information Systems
> >
> > 
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of Richard Hipp [d...@sqlite.org]
> > Sent: Tuesday, June 26, 2012 9:49 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] access from 2 different programms to same
> > sqlite3-db
> >
> > On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
> > wrote:
> >
> > > I have a c++ GUI application from where the db is read and the content
> is
> > > displayed in a Clistbox.
> > > Then I try to delete some rows from the sqlite3-db from the console.
> > > After rereading from within the GUI the deleted rows are still there.
> > >
> > > How is this possible ?
> > >
> >
> > The GUI is holding a read transaction open.  Hence it sees a consistent
> > snapshot of the database from the moment in time when the transaction was
> > started.  Subsequent writes to the database are ignored by the GUI until
> it
> > closes its current transaction and starts a new one.
> >
> >
> > >
> > > __**_
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<<
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3C>
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users%3C>
> >
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.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
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.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
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 11:08 AM, Pavel Ivanov  wrote:

> You are leaking stmt statement (re-preparing without finaliznig) and
> your call to sqlite3_close returns SQLITE_ERROR because of that, but
> you don't even check that so you are leaking database connections as
> well.
>

Good eye, Pavel!  Nicely done.


>
> Pavel
>
>
> On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
>  wrote:
> > Am 26.06.2012 16:49, schrieb Richard Hipp:
> >
> >> On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
> >> wrote:
> >>
> >>> I have a c++ GUI application from where the db is read and the content
> is
> >>> displayed in a Clistbox.
> >>> Then I try to delete some rows from the sqlite3-db from the console.
> >>> After rereading from within the GUI the deleted rows are still there.
> >>>
> >>> How is this possible ?
> >>>
> >> The GUI is holding a read transaction open.  Hence it sees a consistent
> >> snapshot of the database from the moment in time when the transaction
> was
> >> started.  Subsequent writes to the database are ignored by the GUI until
> >> it
> >> closes its current transaction and starts a new one.
> >>
> >>
> >>
> >
> > How can I close the transaction , and later open a new one ?
> > BTW, transaction is still open although i use a sqlite3_close(db) ?
> >
> > Here is the code for reading from the db. By changing within the GUI from
> > tab viewevents to another tab and back again to tab viewevents, the db is
> > read again and should display all changes to the db which appeared during
> > that time.
> >
> > ==
> >
> > int ReadViewEventsFormDBData()
> > {
> >int nRetCode = ERROR_SUCCESS;
> >
> >// Remove all events from array
> >m_arrEvents.RemoveAll();
> >
> >// write  events
> >Event newEvent;
> >
> >int rc, id, total_events;
> >char *sql, *sqltotal;
> >char *evdate, *evtype;
> >int evctr;
> >
> >int the_event_ctr = 0;
> >
> >CString datetime;
> >CString datepart;
> >CString timepart;
> >
> >sqlite3 *db;
> >sqlite3_stmt *stmt;
> >
> >sqlite3_open("ah.db", );
> >
> >// check if table eventlog exists
> >char create_sql[] = "CREATE TABLE if not exists eventlog ("
> >"id INTEGER PRIMARY KEY,"
> >"eventdate DATETIME default current_timestamp,"
> >"eventtype TEXT,"
> >"counter INTEGER"
> >")";
> >
> >rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
> >
> >// select count(*) from eventlog
> >sqltotal = "Select count(*) from eventlog";
> >rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
> >rc = sqlite3_step(stmt);
> >total_events = sqlite3_column_int(stmt, 0 );
> >
> >// select * from eventlog
> >sql = "Select id, eventdate, eventtype, counter FROM eventlog";
> >sqlite3_prepare(db, sql, strlen(sql), , NULL);
> >
> >
> >rc = sqlite3_step(stmt);
> >
> >while(rc == SQLITE_ROW) {
> >id = sqlite3_column_int(stmt, 0 );
> >//cid = sqlite3_column_int(stmt, 1 );
> >evdate = (char*)sqlite3_column_text(stmt, 1 );
> >evtype = (char*)sqlite3_column_text(stmt, 2 );
> >evctr = sqlite3_column_int(stmt, 3 );
> >
> >datetime = evdate;
> >
> >datepart = datetime.Mid(0,10);
> >timepart = datetime.Mid(11,5);
> >
> >std::cout << datepart << "\t" << timepart << std::endl;
> >
> >newEvent.m_nEvent = the_event_ctr;
> >newEvent.m_strLastEventDate = datepart ;
> >newEvent.m_strEventTime = timepart;
> >newEvent.m_strEventType = evtype;
> >newEvent.m_nCount = evctr;
> >
> >// add the new element to array
> >m_arrEvents.Add(newEvent);
> >
> >rc = sqlite3_step(stmt);
> >
> >// increment eventcounter
> >the_event_ctr++;
> >
> >} // while
> >
> >sqlite3_finalize(stmt);
> >sqlite3_close(db);
> >nRetCode = rc;
> >
> >return nRetCode;
> >
> > } // ReadViewEventsFormDBData
> >
> > =
> >
> >
> > ___
> > 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
>



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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Simon Slavin]

On 26 Jun 2012, at 4:11pm, "Black, Michael (IS)"  wrote:

> So...transaction is started at sqlite3_step and stays open until 
> sqlite3_reset or sqlite3_finalize, right?
> 
> sqlite3_close would do what if you didn't finalize?  Just a memory leak or 
> worse?  Or would sqlite3_close return an error if there's an open transaction?



"Applications must finalize all prepared statements and close all BLOB handles 
associated with the sqlite3 object prior to attempting to close the object. If 
sqlite3_close() is called on a database connection that still has outstanding 
prepared statements or BLOB handles, then it returns SQLITE_BUSY."


on the other hand ...

"If sqlite3_close() is invoked while a transaction is open, the transaction is 
automatically rolled back."

It is not an error to close the file while you're in the middle of a 
transaction.  It just means you didn't commit the transaction.

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Black, Michael (IS)]

So...transaction is started at sqlite3_step and stays open until sqlite3_reset 
or sqlite3_finalize, right?

sqlite3_close would do what if you didn't finalize?  Just a memory leak or 
worse?  Or would sqlite3_close return an error if there's an open transaction?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, June 26, 2012 9:54 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] access from 2 different programms to same sqlite3-db

On Tue, Jun 26, 2012 at 10:51 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> Does that mean the "prepare" is wrapped inside a transaction?
>
>
>
> So you must finalize and re-prepare?
>

No.  It just means you need to run sqlite3_reset() on your prepared
statements when you are finished with them, so that they will release the
transaction they are holding.




>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Tuesday, June 26, 2012 9:49 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] access from 2 different programms to same
> sqlite3-db
>
> On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
> wrote:
>
> > I have a c++ GUI application from where the db is read and the content is
> > displayed in a Clistbox.
> > Then I try to delete some rows from the sqlite3-db from the console.
> > After rereading from within the GUI the deleted rows are still there.
> >
> > How is this possible ?
> >
>
> The GUI is holding a read transaction open.  Hence it sees a consistent
> snapshot of the database from the moment in time when the transaction was
> started.  Subsequent writes to the database are ignored by the GUI until it
> closes its current transaction and starts a new one.
>
>
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.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
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Pavel Ivanov]

You are leaking stmt statement (re-preparing without finaliznig) and
your call to sqlite3_close returns SQLITE_ERROR because of that, but
you don't even check that so you are leaking database connections as
well.

Pavel


On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
 wrote:
> Am 26.06.2012 16:49, schrieb Richard Hipp:
>
>> On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
>> wrote:
>>
>>> I have a c++ GUI application from where the db is read and the content is
>>> displayed in a Clistbox.
>>> Then I try to delete some rows from the sqlite3-db from the console.
>>> After rereading from within the GUI the deleted rows are still there.
>>>
>>> How is this possible ?
>>>
>> The GUI is holding a read transaction open.  Hence it sees a consistent
>> snapshot of the database from the moment in time when the transaction was
>> started.  Subsequent writes to the database are ignored by the GUI until
>> it
>> closes its current transaction and starts a new one.
>>
>>
>>
>
> How can I close the transaction , and later open a new one ?
> BTW, transaction is still open although i use a sqlite3_close(db) ?
>
> Here is the code for reading from the db. By changing within the GUI from
> tab viewevents to another tab and back again to tab viewevents, the db is
> read again and should display all changes to the db which appeared during
> that time.
>
> ==
>
> int ReadViewEventsFormDBData()
> {
>    int nRetCode = ERROR_SUCCESS;
>
>    // Remove all events from array
>    m_arrEvents.RemoveAll();
>
>    // write  events
>    Event newEvent;
>
>    int rc, id, total_events;
>    char *sql, *sqltotal;
>    char *evdate, *evtype;
>    int evctr;
>
>    int the_event_ctr = 0;
>
>    CString datetime;
>    CString datepart;
>    CString timepart;
>
>    sqlite3 *db;
>    sqlite3_stmt *stmt;
>
>    sqlite3_open("ah.db", );
>
>    // check if table eventlog exists
>    char create_sql[] = "CREATE TABLE if not exists eventlog ("
>        "id INTEGER PRIMARY KEY,"
>        "eventdate DATETIME default current_timestamp,"
>        "eventtype TEXT,"
>        "counter INTEGER"
>        ")";
>
>    rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
>
>    // select count(*) from eventlog
>    sqltotal = "Select count(*) from eventlog";
>    rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
>    rc = sqlite3_step(stmt);
>    total_events = sqlite3_column_int(stmt, 0 );
>
>    // select * from eventlog
>    sql = "Select id, eventdate, eventtype, counter FROM eventlog";
>    sqlite3_prepare(db, sql, strlen(sql), , NULL);
>
>
>    rc = sqlite3_step(stmt);
>
>    while(rc == SQLITE_ROW) {
>        id = sqlite3_column_int(stmt, 0 );
>        //cid = sqlite3_column_int(stmt, 1 );
>        evdate = (char*)sqlite3_column_text(stmt, 1 );
>        evtype = (char*)sqlite3_column_text(stmt, 2 );
>        evctr = sqlite3_column_int(stmt, 3 );
>
>        datetime = evdate;
>
>        datepart = datetime.Mid(0,10);
>        timepart = datetime.Mid(11,5);
>
>        std::cout << datepart << "\t" << timepart << std::endl;
>
>        newEvent.m_nEvent = the_event_ctr;
>        newEvent.m_strLastEventDate = datepart ;
>        newEvent.m_strEventTime = timepart;
>        newEvent.m_strEventType = evtype;
>        newEvent.m_nCount = evctr;
>
>        // add the new element to array
>        m_arrEvents.Add(newEvent);
>
>        rc = sqlite3_step(stmt);
>
>        // increment eventcounter
>        the_event_ctr++;
>
>    } // while
>
>    sqlite3_finalize(stmt);
>    sqlite3_close(db);
>    nRetCode = rc;
>
>    return nRetCode;
>
> } // ReadViewEventsFormDBData
>
> =
>
>
> ___
> 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] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 11:01 AM, deltagam...@gmx.net
wrote:

> Am 26.06.2012 16:49, schrieb Richard Hipp:
>
>  On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
>> wrote:
>>
>>  I have a c++ GUI application from where the db is read and the content is
>>> displayed in a Clistbox.
>>> Then I try to delete some rows from the sqlite3-db from the console.
>>> After rereading from within the GUI the deleted rows are still there.
>>>
>>> How is this possible ?
>>>
>>>  The GUI is holding a read transaction open.  Hence it sees a consistent
>> snapshot of the database from the moment in time when the transaction was
>> started.  Subsequent writes to the database are ignored by the GUI until
>> it
>> closes its current transaction and starts a new one.
>>
>>
>>
>>
> How can I close the transaction , and later open a new one ?
> BTW, transaction is still open although i use a sqlite3_close(db) ?
>
> Here is the code for reading from the db. By changing within the GUI from
> tab viewevents to another tab and back again to tab viewevents, the db is
> read again and should display all changes to the db which appeared during
> that time.
>

Are you sure the db is being read again when you change tabs?  Have you set
a breakpoint to verify this?

And are you sure the database has changed?

And are you certain that your command-line and your GUI are using the same
database?


>
> ==**==**==
>
> int ReadViewEventsFormDBData()
> {
>int nRetCode = ERROR_SUCCESS;
>
>// Remove all events from array
>m_arrEvents.RemoveAll();
>
>// write  events
>Event newEvent;
>
>int rc, id, total_events;
>char *sql, *sqltotal;
>char *evdate, *evtype;
>int evctr;
>
>int the_event_ctr = 0;
>
>CString datetime;
>CString datepart;
>CString timepart;
>
>sqlite3 *db;
>sqlite3_stmt *stmt;
>
>sqlite3_open("ah.db", );
>
>// check if table eventlog exists
>char create_sql[] = "CREATE TABLE if not exists eventlog ("
>"id INTEGER PRIMARY KEY,"
>"eventdate DATETIME default current_timestamp,"
>"eventtype TEXT,"
>"counter INTEGER"
>")";
>
>rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
>
>// select count(*) from eventlog
>sqltotal = "Select count(*) from eventlog";
>rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
>rc = sqlite3_step(stmt);
>total_events = sqlite3_column_int(stmt, 0 );
>
>// select * from eventlog
>sql = "Select id, eventdate, eventtype, counter FROM eventlog";
>sqlite3_prepare(db, sql, strlen(sql), , NULL);
>
>
>rc = sqlite3_step(stmt);
>
>while(rc == SQLITE_ROW) {
>id = sqlite3_column_int(stmt, 0 );
>//cid = sqlite3_column_int(stmt, 1 );
>evdate = (char*)sqlite3_column_text(**stmt, 1 );
>evtype = (char*)sqlite3_column_text(**stmt, 2 );
>evctr = sqlite3_column_int(stmt, 3 );
>
>datetime = evdate;
>
>datepart = datetime.Mid(0,10);
>timepart = datetime.Mid(11,5);
>
>std::cout << datepart << "\t" << timepart << std::endl;
>
>newEvent.m_nEvent = the_event_ctr;
>newEvent.m_strLastEventDate = datepart ;
>newEvent.m_strEventTime = timepart;
>newEvent.m_strEventType = evtype;
>newEvent.m_nCount = evctr;
>
>// add the new element to array
>m_arrEvents.Add(newEvent);
>
>rc = sqlite3_step(stmt);
>
>// increment eventcounter
>the_event_ctr++;
>
>} // while
>
>sqlite3_finalize(stmt);
>sqlite3_close(db);
>nRetCode = rc;
>
>return nRetCode;
>
> } // ReadViewEventsFormDBData
>
> ==**==**
> =
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

Am 26.06.2012 16:49, schrieb Richard Hipp:

On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
wrote:


I have a c++ GUI application from where the db is read and the content is
displayed in a Clistbox.
Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?


The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until it
closes its current transaction and starts a new one.





How can I close the transaction , and later open a new one ?
BTW, transaction is still open although i use a sqlite3_close(db) ?

Here is the code for reading from the db. By changing within the GUI 
from tab viewevents to another tab and back again to tab viewevents, the 
db is read again and should display all changes to the db which appeared 
during that time.


==

int ReadViewEventsFormDBData()
{
int nRetCode = ERROR_SUCCESS;

// Remove all events from array
m_arrEvents.RemoveAll();

// write  events
Event newEvent;

int rc, id, total_events;
char *sql, *sqltotal;
char *evdate, *evtype;
int evctr;

int the_event_ctr = 0;

CString datetime;
CString datepart;
CString timepart;

sqlite3 *db;
sqlite3_stmt *stmt;

sqlite3_open("ah.db", );

// check if table eventlog exists
char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);

// select count(*) from eventlog
sqltotal = "Select count(*) from eventlog";
rc = sqlite3_prepare(db, sqltotal, strlen(sqltotal), , NULL);
rc = sqlite3_step(stmt);
total_events = sqlite3_column_int(stmt, 0 );

// select * from eventlog
sql = "Select id, eventdate, eventtype, counter FROM eventlog";
sqlite3_prepare(db, sql, strlen(sql), , NULL);


rc = sqlite3_step(stmt);

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
//cid = sqlite3_column_int(stmt, 1 );
evdate = (char*)sqlite3_column_text(stmt, 1 );
evtype = (char*)sqlite3_column_text(stmt, 2 );
evctr = sqlite3_column_int(stmt, 3 );

datetime = evdate;

datepart = datetime.Mid(0,10);
timepart = datetime.Mid(11,5);

std::cout << datepart << "\t" << timepart << std::endl;

newEvent.m_nEvent = the_event_ctr;
newEvent.m_strLastEventDate = datepart ;
newEvent.m_strEventTime = timepart;
newEvent.m_strEventType = evtype;
newEvent.m_nCount = evctr;

// add the new element to array
m_arrEvents.Add(newEvent);

rc = sqlite3_step(stmt);

// increment eventcounter
the_event_ctr++;

} // while

sqlite3_finalize(stmt);
sqlite3_close(db);
nRetCode = rc;

return nRetCode;

} // ReadViewEventsFormDBData

=

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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 10:51 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> Does that mean the "prepare" is wrapped inside a transaction?
>
>
>
> So you must finalize and re-prepare?
>

No.  It just means you need to run sqlite3_reset() on your prepared
statements when you are finished with them, so that they will release the
transaction they are holding.




>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Richard Hipp [d...@sqlite.org]
> Sent: Tuesday, June 26, 2012 9:49 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] access from 2 different programms to same
> sqlite3-db
>
> On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
> wrote:
>
> > I have a c++ GUI application from where the db is read and the content is
> > displayed in a Clistbox.
> > Then I try to delete some rows from the sqlite3-db from the console.
> > After rereading from within the GUI the deleted rows are still there.
> >
> > How is this possible ?
> >
>
> The GUI is holding a read transaction open.  Hence it sees a consistent
> snapshot of the database from the moment in time when the transaction was
> started.  Subsequent writes to the database are ignored by the GUI until it
> closes its current transaction and starts a new one.
>
>
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.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
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Black, Michael (IS)]

Does that mean the "prepare" is wrapped inside a transaction?



So you must finalize and re-prepare?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, June 26, 2012 9:49 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] access from 2 different programms to same sqlite3-db

On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
wrote:

> I have a c++ GUI application from where the db is read and the content is
> displayed in a Clistbox.
> Then I try to delete some rows from the sqlite3-db from the console.
> After rereading from within the GUI the deleted rows are still there.
>
> How is this possible ?
>

The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until it
closes its current transaction and starts a new one.


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



--
D. Richard Hipp
d...@sqlite.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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] access from 2 different programms to same sqlite3-db

[Richard Hipp]

On Tue, Jun 26, 2012 at 10:46 AM, deltagam...@gmx.net
wrote:

> I have a c++ GUI application from where the db is read and the content is
> displayed in a Clistbox.
> Then I try to delete some rows from the sqlite3-db from the console.
> After rereading from within the GUI the deleted rows are still there.
>
> How is this possible ?
>

The GUI is holding a read transaction open.  Hence it sees a consistent
snapshot of the database from the moment in time when the transaction was
started.  Subsequent writes to the database are ignored by the GUI until it
closes its current transaction and starts a new one.


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



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

Re: [sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[Jay A. Kreibich]

On Tue, Jun 26, 2012 at 03:56:33PM +0200, OBones scratched on the wall:
> Hello all,
> 
> As I'm experimenting with Virtual Tables, I have had to implement
> xBestIndex and xFilter.
> The documentation says that idxNum and idxStr are of no importance
> to the SQLite core and that it's our responsibility to make sure
> xFilter and xBestIndex agree on the meaning.

> This is fine by me but I'd like to suggest to change the type of
> idxStr from "char *" to "void *" to make it even clearer to everyone
> that this parameter is not used in any specific way inside SQLite
> core.

  99% of the modules that use idxStr pass a string.  It is usually an
  SQL statement used by xFilter() that has parameter names mapped to
  the parameters defined by xBestIndex().  This is the primary use of
  this API.

  I see you're point, but the variable name is idx"Str", and that's how
  most people use it.  I don't think redefining it as a void* implies
  any more or less ownership by SQLite than a char*.  The docs are
  clear, the code is clear, and that's how the vast majority of users
  use it.  Forcing them to cast back and forth for the exception to the
  rule seems like a bit of waste.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] access from 2 different programms to same sqlite3-db

[deltagam...@gmx.net]

I have a c++ GUI application from where the db is read and the content 
is displayed in a Clistbox.

Then I try to delete some rows from the sqlite3-db from the console.
After rereading from within the GUI the deleted rows are still there.

How is this possible ?

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

Re: [sqlite] Virtual tables: rename, disconnect and reconnect

[Jay A. Kreibich]

On Tue, Jun 26, 2012 at 03:47:07PM +0200, OBones scratched on the wall:
> Hello all,
> 
> I'm experimenting with virtual tables and I must say that it's a
> very nice feature to have, it saves me from inserting millions of
> records in a database when I can simply map the source file in
> memory and read it directly.
> However, I have a small issue when renaming a database. What is
> happening is the following:
> 
> xDisconnect
> xRename
> xConnect

> Those calls are quite expected and fine by me.
> However, what surprises me is that xConnect is given a zero ppVTab
> parameter (that is, *ppVTab == 0)

  The whole point of both the xCreate() and xConnect() functions is to
  allocate, define, and return the sqlite3_vtab structure.  It must be
  allocated by the xCreate()/xConnect() function because it is standard
  practice to over-allocate the data structure so that the virtual table
  can append additional data fields to the end of the sqlite3_vtab
  structure.  Since SQLite has no idea how much extra memory you might
  want, it is up to the functions to allocate this structure and pass
  it back to SQLite via the ppVTab parameter.

  This exact same pattern can be seen in xOpen(), where the virtual
  table is tasked with allocating, init'ing, and returning the
  sqlite3_vtab_cursor structure.  In xClose(), the virtual table must
  clean-up and deallocate the same structure.  Again, it is standard
  practice for virtual tables to over-allocate this data structure, so
  the actual memory allocation needs to be done by the virtual table
  code, not by SQLite.

> I would have thought that since the disconnect was given a valid
> pVTab, this one could have been given back to xConnect when
> reconnecting the database after its rename.

  Except SQLite doesn't store the data in that structure.  sqlite3_vtab
  is a "live" structure, in the sense that it only exists in-memory in
  an active database process.  The whole idea of xCreate()/xConnect()
  is to re-create this data structure, every time, from the original
  virtual table declaration.  The whole point of
  xDestroy()/xDisconnect() is to clean up, destroy, and free the
  sqlite3_vtab structure.  It only exists between a pair of those calls.

  If you're trying to keep track of some type of connection
  information or external file-name or something, it is best that such
  information is part of the CREATE VIRTUAL TABLE statement, as this
  data will always be available.

  In theory, your application could save the partial state of any
  custom fields in xDisconnect() and then read them back in xConnect().
  Any application that uses "shadow tables" (such as the built-in FTS
  and R-Tree modules) essentially does this, as there is state saved
  into the database the virtual table uses to provide its service.
  These types of virtual tables have different xCreate()/xConnect()
  functions and different xDestroy()/xDisconnect() functions, as one
  set of functions needs to create/drop the shadow tables, while the
  other set only needs to verify that they're there.  Regardless, any
  possible saving of the state is up to the application.

> Is this behavior expected? I'm fine with my current solution, but I
> was expecting xConnect to get a non zero *pPVTab most, if not all
> the time.

  This is exactly how the API is designed.

  You seem to be fighting the API, which makes me think there might be
  a slight disconnect between how you expect the API to work and how
  the API was designed to be used.  I would recommend you have a good
  read through all the docs on the SQLite website about virtual tables,
  especially this:   http://www.sqlite.org/vtab.html

  The book "Using SQLite" (O'Reilly) also has an entire chapter on
  virtual tables and how to use them.  It happens to be the longest
  chapter in the book, and walks through two full examples.  One of the
  examples is using a virtual table to map web-server logs to an SQLite
  table without importing the data.  From what you said, that sounds
  somewhat similar to your problem.

  http://shop.oreilly.com/product/9780596521196.do


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables: xSync without xBegin

[OBones]

Hello all,

I'm pushing my experiment with virtual tables a bit further by trying 
out the transaction functions.
As a result, I gave values for the xBegin, xSync, xCommit and xRollback 
members of my sqlite3_module structure.

Then after having registered the module, I sent those two statements:

CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
INSERT INTO SomeTable VALUES (50);

via appropriate calls to sqlite3_exec
However, this fails quite badly because the xSync function is called 
outside any transaction that would have been started by a call to xBegin.
Basically, xBegin is never called in my simple test, despite the 
documentation saying that this should not happen.


What have I done wrong here?

Any suggestion is most welcome

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

[sqlite] Virtual Tables: retrieving the ppArg given in xFindFunction

[OBones]

Hello all,

As I'm moving forward in my usage of virtual tables, I'm using the 
xFindFunction method to overload some functions with my own implementation.
That works just fine, but I stumbled upon a hurdle that was not obvious 
to solve at first glance.
Inside the xFindFunction, you can give a value to *ppArg that the 
documentation says is user data. But there are no indication as to how 
to retrieve that user data from within the function which pointer is set 
to *pxFunc
I initially expected that this function would receive it as a parameter 
much like xCreate and xConnect, but there is no such parameter to that 
function.
Turns out that one can get the value back by calling 
sqlite3_user_data(pContext) and that works quite nice.


Considering that it's unlikely that the signature for pxFunc would be 
changed, could at least the documentation be updated so that it 
indicates how to retrieve the value with sqlite3_user_data?


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

Re: [sqlite] Detecting when a table is created

[Jay A. Kreibich]

On Tue, Jun 26, 2012 at 02:52:43PM +0200, Ralf Junker scratched on the wall:
> On 26.06.2012 14:33, Vivien Malerba wrote:
> 
> > The code is some SQL entered by the user, I have no control over it. There
> > is effectively the possibility to parse the SQL entered, detect the CREATE
> > table statement and act accordingly, but I would like to see if SQLite has
> > some kind of builtin feature I could use first.
> 
> Two options, both not intended for it, but could work well:
> 
> - http://sqlite.org/c3ref/set_authorizer.html
> - http://sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog


  You might also be able to define an ON INSERT trigger on sqlite_master
  that calls an application defined function.  If you can do it, it will
  likely require enabling writable schemas, which is somewhat dangerous
  for anything that allows user-interaction.  Even then, it might not
  work... I have no idea if things are "inserted" into sqlite_master in
  the standard way.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[OBones]

Hello all,

As I'm experimenting with Virtual Tables, I have had to implement 
xBestIndex and xFilter.
The documentation says that idxNum and idxStr are of no importance to 
the SQLite core and that it's our responsibility to make sure xFilter 
and xBestIndex agree on the meaning.
This is fine by me but I'd like to suggest to change the type of idxStr 
from "char *" to "void *" to make it even clearer to everyone that this 
parameter is not used in any specific way inside SQLite core.
Maybe this was already requested in the past and denied because the 
interface of virtual tables cannot change, but I could not find any 
trace of such thing.


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

[sqlite] Virtual tables: rename, disconnect and reconnect

[OBones]

Hello all,

I'm experimenting with virtual tables and I must say that it's a very 
nice feature to have, it saves me from inserting millions of records in 
a database when I can simply map the source file in memory and read it 
directly.
However, I have a small issue when renaming a database. What is 
happening is the following:


xDisconnect
xRename
xConnect

Those calls are quite expected and fine by me.
However, what surprises me is that xConnect is given a zero ppVTab 
parameter (that is, *ppVTab == 0)
I would have thought that since the disconnect was given a valid pVTab, 
this one could have been given back to xConnect when reconnecting the 
database after its rename.
As it turns out, I use pAux to point to my own structure, so I was able 
to create a list of "dangling" databases to pick from when connecting.
That is to say, when xDisconnect is called, the database is put in that 
list of "dangling" databases and using the name in xConnect, its value 
is retrieved at that time.
I know that the documentation says that xConnect and xCreate can be the 
same, and that most implementation that I have seen are actually the 
same, but when they are not, the current situation makes it difficult 
(at first sight) not to call again lengthy code when *ppVTab is zero.


Is this behavior expected? I'm fine with my current solution, but I was 
expecting xConnect to get a non zero *pPVTab most, if not all the time.


Thanks in advance for any clarification on this subject.

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 15:31, Black, Michael (IS)  wrote:

> If you have sqlite3.c in your project you can just add your own hook.
>
>
>
> Take a look at  sqlite3TwoPartName if you want to see if before it's
> created.
>
>
>
> Or look at sqlite3StartTable (end of the function) if you want to know
> after it's created.
>
>
>
> That will catch a VIEW creation too.
>

Yes, I had had this idea, but I would like to resort to modifying SQLite's
source code if there is no other solution as I want my program to work with
the standard packaged SQLite (for Linux and ease of update reasons).

Thanks a lot anyway for your suggestion,

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 15:00, kyan  wrote:

> > Is there any possibility to be notified when a table is created (when a
> > "CREATE TABLE XXX" is executed)?
>
> You could try installing a profile callback using sqlite3_profile()
> (see http://www.sqlite.org/c3ref/profile.html). Of course this
> callback will be called for any kind of SQL or DDL statement that gets
> executed in your database so you will have to distinguish CREATE TABLE
> statements by parsing the SQL command text in the profiler callback's
> second argument.
>

This is similar to what I'm doing at the moment (parsing the SQL before
execution).
Thanks for the tip anyway!

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 14:55, Simon Slavin  wrote:

>
> On 26 Jun 2012, at 1:33pm, Vivien Malerba  wrote:
>
> > The code is some SQL entered by the user, I have no control over it.
> There
> > is effectively the possibility to parse the SQL entered, detect the
> CREATE
> > table statement and act accordingly, but I would like to see if SQLite
> has
> > some kind of builtin feature I could use first.
>
> Could you poll 'PRAGMA schema_version' ?
>
> 
>
> It changes when something creates or drops a table or index.  Of course,
> it won't tell you the actual CREATE command.  For that you could look at
> the table called "sqlite_master".
>

I need a notification _before_ the command is executed, so this won't do.


>
> > Also, for example if the user executes a query as "CREATE TABLE xxx AS
> > SELECT ..." then it's much more difficult to know how to define the XXX
> > table as I would have to parse the SELECT query part as well. It's doable
> > of course but can become a bit complicated.
>
> I think this will be your problem.  Even if you could set up notifications
> for changes, trying to convert what they did to what you want to do instead
> looks complicated.


Still, it seems it's the easiest way to go (as a matter of fact this is
already partially done for the soft I work on which is Libgda).

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

Re: [sqlite] Detecting when a table is created

[Black, Michael (IS)]

If you have sqlite3.c in your project you can just add your own hook.



Take a look at  sqlite3TwoPartName if you want to see if before it's created.



Or look at sqlite3StartTable (end of the function) if you want to know after 
it's created.



That will catch a VIEW creation too.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Vivien Malerba [vmale...@gmail.com]
Sent: Tuesday, June 26, 2012 7:33 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Detecting when a table is created

On 26 June 2012 14:21, Igor Tandetnik  wrote:

> Vivien Malerba  wrote:
> > Is there any possibility to be notified when a table is created (when a
> > "CREATE TABLE XXX" is executed)?
>
> How is it executed? Isn't it your program that executes the statement? How
> come you don't know when that happens?
>

The code is some SQL entered by the user, I have no control over it. There
is effectively the possibility to parse the SQL entered, detect the CREATE
table statement and act accordingly, but I would like to see if SQLite has
some kind of builtin feature I could use first.

Also, for example if the user executes a query as "CREATE TABLE xxx AS
SELECT ..." then it's much more difficult to know how to define the XXX
table as I would have to parse the SELECT query part as well. It's doable
of course but can become a bit complicated.

Regards,

Vivien
___
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] Detecting when a table is created

[kyan]

> Is there any possibility to be notified when a table is created (when a
> "CREATE TABLE XXX" is executed)?

You could try installing a profile callback using sqlite3_profile()
(see http://www.sqlite.org/c3ref/profile.html). Of course this
callback will be called for any kind of SQL or DDL statement that gets
executed in your database so you will have to distinguish CREATE TABLE
statements by parsing the SQL command text in the profiler callback's
second argument.

HTH

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

Re: [sqlite] Detecting when a table is created

[Simon Slavin]

On 26 Jun 2012, at 1:33pm, Vivien Malerba  wrote:

> The code is some SQL entered by the user, I have no control over it. There
> is effectively the possibility to parse the SQL entered, detect the CREATE
> table statement and act accordingly, but I would like to see if SQLite has
> some kind of builtin feature I could use first.

Could you poll 'PRAGMA schema_version' ?



It changes when something creates or drops a table or index.  Of course, it 
won't tell you the actual CREATE command.  For that you could look at the table 
called "sqlite_master".

> Also, for example if the user executes a query as "CREATE TABLE xxx AS
> SELECT ..." then it's much more difficult to know how to define the XXX
> table as I would have to parse the SELECT query part as well. It's doable
> of course but can become a bit complicated.

I think this will be your problem.  Even if you could set up notifications for 
changes, trying to convert what they did to what you want to do instead looks 
complicated.

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

Re: [sqlite] Detecting when a table is created

[Ralf Junker]

On 26.06.2012 14:33, Vivien Malerba wrote:

> The code is some SQL entered by the user, I have no control over it. There
> is effectively the possibility to parse the SQL entered, detect the CREATE
> table statement and act accordingly, but I would like to see if SQLite has
> some kind of builtin feature I could use first.

Two options, both not intended for it, but could work well:

- http://sqlite.org/c3ref/set_authorizer.html

- http://sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog

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

Re: [sqlite] Detecting when a table is created

[Vivien Malerba]

On 26 June 2012 14:21, Igor Tandetnik  wrote:

> Vivien Malerba  wrote:
> > Is there any possibility to be notified when a table is created (when a
> > "CREATE TABLE XXX" is executed)?
>
> How is it executed? Isn't it your program that executes the statement? How
> come you don't know when that happens?
>

The code is some SQL entered by the user, I have no control over it. There
is effectively the possibility to parse the SQL entered, detect the CREATE
table statement and act accordingly, but I would like to see if SQLite has
some kind of builtin feature I could use first.

Also, for example if the user executes a query as "CREATE TABLE xxx AS
SELECT ..." then it's much more difficult to know how to define the XXX
table as I would have to parse the SELECT query part as well. It's doable
of course but can become a bit complicated.

Regards,

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

Re: [sqlite] Detecting when a table is created

[Igor Tandetnik]

Vivien Malerba  wrote:
> Is there any possibility to be notified when a table is created (when a
> "CREATE TABLE XXX" is executed)?

How is it executed? Isn't it your program that executes the statement? How come 
you don't know when that happens?
-- 
Igor Tandetnik

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

[sqlite] Detecting when a table is created

[Vivien Malerba]

Hi!

Is there any possibility to be notified when a table is created (when a
"CREATE TABLE XXX" is executed)?

The context here is that I have an SQLite connection with only virtual
tables and when the user manually creates a table, the table is created
either in memory or in the temporary file (depending on how the SQLite
connection was opened in the first place), whereas I would like to create a
virtual table instead.

Thanks for any answer!

Regards,

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

Re: [sqlite] How to retrieve number of records

[Simon Slavin]

On 26 Jun 2012, at 1:03pm, "Igor Tandetnik"  wrote:

> Simon Slavin  wrote:
>> Use sqlite3_result_int(stmt, 0) to get the value of the first (only) column 
>> of the row.
> 
> Make it sqlite3_column_int. sqlite3_result_* is used inside custom functions, 
> to set the return value of the function call.

Argh.  Thanks Igor.

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

Re: [sqlite] How to retrieve number of records

[Igor Tandetnik]

Simon Slavin  wrote:
> Use sqlite3_result_int(stmt, 0) to get the value of the first (only) column 
> of the row.

Make it sqlite3_column_int. sqlite3_result_* is used inside custom functions, 
to set the return value of the function call.
-- 
Igor Tandetnik

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

[sqlite] Track SQLite memory usage from inside Java code

[mira]

Hi,

I am experiencing memory issues when inserting a large number of records
into my database.  I use a prepared statement wrapped in a transaction where
I process (insert) 10K records before I clear the batch 
using the PreparedStatement.clearBatch() method;

I would like to insert code to track SQL's memory usage.  I found a solution
for C++ but I use Java.  

Any help would be greatly appreciated!
Mira


--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Track-SQLite-memory-usage-from-inside-Java-code-tp62756.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

Re: [sqlite] How to retrieve number of records

[Simon Slavin]

On 26 Jun 2012, at 11:28am, "deltagam...@gmx.net"  wrote:

> I want to retrieve the number of records in a table,
> but dont know how to get access to the result of  " select count(*) from 
> tablename "

That statement, like any other SELECT command, will return a set of rows and 
columns with the result in.  In this case, it will return just one row, 
containing just one column, which has the number you asked for in.

Use sqlite3_step(stmt) to get the row.

Use sqlite3_result_int(stmt, 0) to get the value of the first (only) column of 
the row.

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

[sqlite] How to retrieve number of records

[deltagam...@gmx.net]

I want to retrieve the number of records in a table,
but dont know how to get access to the result of  " select count(*) from 
tablename "





#include "db_functions.h"

//using namespace std;


void select_count( ) {

int rc, i, ncols, id;
char *sql;
char *evdate, *evtype;
int evctr;

sqlite3 *db;
sqlite3_stmt *stmt;



sql = "Select count(*) from eventlog";
sqlite3_open("ah.db", );

sqlite3_prepare(db, sql, strlen(sql), , NULL);


char create_sql[] = "CREATE TABLE if not exists eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);


//while(rc == SQLITE_ROW) {



rc = sqlite3_step(stmt);


//} // while


sqlite3_finalize(stmt);
sqlite3_close(db);
}







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

Re: [sqlite] I consider this a bug. Anyone else?

[E. Timothy Uy]

Bill, Andrew, PA Newsgroups et al.,

Would you be willing to invest in a subscription for a compiled, signed,
non-Debug, Windows-deployed SQLite DLL with resource information? We would
consider compiling and hosting the files for you. In addition, we could
offer improved stemming (multiple languages) as well as encrypted and/or
compressed databases.

Respectfully,
Tim

On Tue, Jun 26, 2012 at 12:10 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 25/06/12 23:38, Andrew Rondeau wrote:
> > Because my time is valuable;
>
> I'm still baffled why you think adding one source file to your project is
> a huge undertaking, but somehow getting a "community" going, debating how
> you want things, fiddling around, testing, waiting for releases to
> packaged differently etc is not.
>
> > For example, if I make a Mac application, I can target the SQLites
> > shipped with Mac. Problem solved, my valuable time can go to something
> > else.
>
> Except the version of SQLite differs between MacOS releases so you have to
> code and test against different revisions of the SQLite library.  Or you
> include one file statically into your app and it is the only one you have
> to test against.  And it can be the same version as you use on other
> platforms lessening the development and testing even more.
>
> Of course you don't have to listen to the several people who have
> repeatedly been telling you that what you wanted is the most time
> consuming and most brittle way of doing things.  However you will note
> that so far there have been zero people clamouring to form some sort of
> community for what you stated.  Good luck!
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk/pYE8ACgkQmOOfHg372QTEOwCg4IU49+Y/emng7oJY6Qu8vOka
> BiUAoLt1o0uZW/tjxWah0jyi0MkgfFer
> =idVg
> -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] I consider this a bug. Anyone else?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/06/12 23:38, Andrew Rondeau wrote:
> Because my time is valuable;

I'm still baffled why you think adding one source file to your project is
a huge undertaking, but somehow getting a "community" going, debating how
you want things, fiddling around, testing, waiting for releases to
packaged differently etc is not.

> For example, if I make a Mac application, I can target the SQLites
> shipped with Mac. Problem solved, my valuable time can go to something
> else.

Except the version of SQLite differs between MacOS releases so you have to
code and test against different revisions of the SQLite library.  Or you
include one file statically into your app and it is the only one you have
to test against.  And it can be the same version as you use on other
platforms lessening the development and testing even more.

Of course you don't have to listen to the several people who have
repeatedly been telling you that what you wanted is the most time
consuming and most brittle way of doing things.  However you will note
that so far there have been zero people clamouring to form some sort of
community for what you stated.  Good luck!

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

iEYEARECAAYFAk/pYE8ACgkQmOOfHg372QTEOwCg4IU49+Y/emng7oJY6Qu8vOka
BiUAoLt1o0uZW/tjxWah0jyi0MkgfFer
=idVg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I consider this a bug. Anyone else?

[Mohd Radzi Ibrahim]

On Tuesday, June 26, 2012, Andrew Rondeau wrote:

> Because my time is valuable; and my peers' time is valuable as well.
>
> For example, if I make a Mac application, I can target the SQLites shipped
> with Mac. Problem solved, my valuable time can go to something else.
>
>
Since I don't want any outside library or DLLs interfere with my app, I
would rather have my sqlite library linked statically into my app. I just
need to have sqlite3.c and sqlite3.h in my project code.




> On Mon, Jun 25, 2012 at 12:35 PM, Roger Binns 
> >
> wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 25/06/12 11:21, Andrew Rondeau wrote:
> > > IMO, I've been somewhat dissapointed with sqlite's deployment scenario
> > > on Windows. Perhaps this is an area that needs a bit more community
> > > involvement?
> >
> > You really are looking this the wrong way.  SQLite's source is as close
> to
> > public domain as you can get, and the amalgamation means there is only
> one
> > source file to deal with.
> >
> > You can do whatever you want - you don't need anyone else's permission or
> > cooperation.  Why do you want to gate your usage through other people
> with
> > their own priorities and availability?
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAk/ovYEACgkQmOOfHg372QQrOACffzSBY6ZTR6/M6QrN4yxresMc
> > encAoLOLW8GQs7tVMHCur9jxt0uBCIra
> > =IV37
> > -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] I consider this a bug. Anyone else?

[Andrew Rondeau]

Because my time is valuable; and my peers' time is valuable as well.

For example, if I make a Mac application, I can target the SQLites shipped
with Mac. Problem solved, my valuable time can go to something else.

On Mon, Jun 25, 2012 at 12:35 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 25/06/12 11:21, Andrew Rondeau wrote:
> > IMO, I've been somewhat dissapointed with sqlite's deployment scenario
> > on Windows. Perhaps this is an area that needs a bit more community
> > involvement?
>
> You really are looking this the wrong way.  SQLite's source is as close to
> public domain as you can get, and the amalgamation means there is only one
> source file to deal with.
>
> You can do whatever you want - you don't need anyone else's permission or
> cooperation.  Why do you want to gate your usage through other people with
> their own priorities and availability?
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk/ovYEACgkQmOOfHg372QQrOACffzSBY6ZTR6/M6QrN4yxresMc
> encAoLOLW8GQs7tVMHCur9jxt0uBCIra
> =IV37
> -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