Re: [sqlite] how to write sql cmd for this situation:

[Igor Tandetnik]

YAN HONG YE  wrote:
> I have a 2 table:
> 1.  tb1:
> 
> aa bb cc   mdate
> 1   d1 2.5 2012-08-07
> 2   d2 3.3 2012-08-07
> 3   d1 6.4 2012-08-09
> 
> 2.  tb2:
> aa bb cc
> 1.  d1 5.3
> 2.  d2 7.6
> 
> mysql sql cmd is:
> 
> select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. 
> max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2
> where tb2.bb in (select bb from tb1 where bb group by bb  having count(bb)) ;
> 
> I wanna the result is:
> bb ccmmd mmc
> d1 5.3  23.9
> 
> the result 3.9 mean is 6.4-2.5  ,  mdate 2012-08-09 - 2012-08-07 =2, and 6.4 
> - 2.5=3.9

It's not quite clear what you want. See if something like this helps:

select tb2.bb, tb2.cc,
julianday(tbmax.mdate)-julianday(tbmin.mdate) mmd,
tbmax.cc - tbmin.cc mmc
from tb2, tb1 tbmin, tb1 tbmax
where tbmin.aa = (select aa from tb1 inner where inner.bb = tb2.bb order by 
mdate asc limit 1)
and tbmax.aa = (select aa from tb1 inner where inner.bb = tb2.bb order by 
mdate desc limit 1)
and tbmin.aa != tbmax.aa;

-- 
Igor Tandetnik

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

Re: [sqlite] Why can't SQLite drop columns?

[gregorinator]

On 10/8/12, Petite Abeille  wrote:
> How do they deal with constraints? triggers? indexes? others?
> If this was as straightforward as a 'create table bar as select a, b, c from
> foo; drop table foo; alter table bar rename to foo;', we would most likely
> not having this conversation :)

I'm sorry for not being more comprehensive in my answer.  Also, as I
pointed out, I can only speak for SQLiteStudio -- there are other
tools; you need to investigate for yourself.

When dropping a column from a table, SQLiteStudio preserves the
original table's primary key, foreign keys, null constraints,
defaults, check constraints, unique constraints, and indexes on
columns other than the one being deleted.  At _least_ that much.  I
confess I've never done an exhaustive exploration of what it preserves
-- I only look for the things I need preserved in my applications.  I
haven't come across anything I need that it doesn't preserve, but I
confess I haven't tried it with triggers or that really powerful SQL92
"others" functionality that you asked about. :)

Honestly, I wouldn't even have recommended SQLiteStudio or tools like
it if my experience was that all it did was "create table bar select
from foo", but I can see it would have made my earlier post more clear
if I had mentioned this from the start.  And, as I said, SQLiteStudio
isn't the only SQLite db manager out there; I suggest that you take
them each for a spin and come to your own conclusions.  But my point
was that there are third party tools that might be able to give you a
robust DROP COLUMN capability -- and maybe other functionality that
SQLite can't do itself -- without your having to reinvent the wheel.
Might.

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

Re: [sqlite] how to write sql cmd for this situation

[YAN HONG YE]

"Igor Tandetnik"  reply:

YAN HONG YE  wrote:
> mysql sql cmd is:
> 
> select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. 
> max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2
> where tb2.bb in (select bb from tb1 where bb group by bb  having count(bb)) ;

With all due respect, this query doesn't make any sense. Could you explain in 
your own words exactly what data do you want to calculate?
-- 
Igor Tandetnik

I have a 2 table:
1.  tb1:

aa bb cc   mdate
1   d1 2.5 2012-08-07
2   d2 3.3 2012-08-07
3   d1 6.4 2012-08-09

2.  tb2:
aa bb cc
1.  d1 5.3
2.  d2 7.6


I wanna the result is:
bb ccmmd mmc
d1 5.3  23.9

the result 3.9 mean is 6.4-2.5  ,  mdate 2012-08-09 - 2012-08-07 =2, and 6.4 - 
2.5=3.9

how to get the result by sqm cmd?
thanks.

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

Re: [sqlite] Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT:0x8007007E)

[Joe Mistachkin]

Robert Calco wrote:
>
> They are all running .NET 4.5, C# 2012, Debug, AnyCPU mode, and the unit
> tests look identically configured, both on the hard drive and logically in
> their respective solution trees. I used NuGet to install SQLite in both
> solutions, so they each have their own copy in their respective /packages
> directory.
> 

Are these running on the same machine?  Is the operating system 32-bit or
64-bit?  One thing to note is that AnyCPU is basically the same as Win32
(x86) in the System.Data.SQLite solution files.  I'm not sure how your
solution files are configured.

> 
> So, to the naked eye, they seem identical. However, one solutions' unit
> tests run absolutely fine, and the other one throws the error in the
> subject of this email. Both are pulling in the common data layer project
> which is the one that depends on SQLite. both have the following in the
> Debug\bin directory:
> 
> x64\ (folder with 64 bit version of SQLite.Interop.dll in it)
> x86\ (folder with 32 bit version of SQLite.Interop.dll in it)
> 
> ... rest of the binaries required
> 

Ok, the native library pre-loading feature should be in use then.

> 
> I tried configuring all projects in the problem solution to Debug, x86,
> and I even tried dropping the 32-bit version of the SQLite.Interop.dll
> into the bin directory of the unit tests... and STILL I get this error.
> 

Is the operating system 32-bit?  If not, you might want to try building
everything for x64.

> 
> I saw on post, a couple of google searches ago, that indicated perhaps the
> version of the MS runtime DLLs might be the issue. Is it possible that one
> of my projects has a "newer" version of System.Data.SQLite that was
> compiled against a "newer" version of the MS C++ runtime dlls, even though
> they have the same version (1.0.82.0)? (I have not upgraded to the latest
> MS C++ runtime DLLs in my 2012 project, for all kinds of reasons that have
> to do with some of our own C/C++ dependencies in native sub-projects.)
> 

If you are using the official NuGet package, the contained DLLs are all
compiled with the latest version of the corresponding Microsoft Visual C++
runtime libraries for the appropriate processor architecture.

> 
> How should I troubleshoot this?
> 

1. Verify if the operating system is 32-bit or 64-bit.

2. Verify if the application EXE is marked as 32-bit or 64-bit in the header
using CorFlags and/or ILDasm.

3. Run the "depends.exe" tool on the SQLite.Interop.dll and make sure it
does
not find any errors related to missing Visual C++ Runtime DLLs.

>
> And in general how does SQLite work in "AnyCPU" mode if it needs
> SQLite.Interop.dll to run, and there are precisely two mutually exclusive
> versions of it -- 32 and 64 bit?
>

The "AnyCPU" build platform really only applies to managed projects.  For
native projects in the System.Data.SQLite solution files, it simply maps to
Win32 (x86).

--
Joe Mistachkin

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

Re: [sqlite] Why can't SQLite drop columns?

[BareFeetWare]

You can do it, but you need to parse the existing table schema.

1. Get the existing schema for the table, along with associated triggers and 
indexes: select group_concat(SQL, x'0A' || ';' || x'0A') from SQLite_Master 
where tbl_name = 'My Table'.

2. Edit the create table schema to remove the column you no longer want.

3. Execute the following, with the edited schema inserted as shown:

begin immediate
;
pragma foreign_keys = NO
;
pragma triggers = NO
;
create temp table "Cache" as select * from "My Table"
;
drop table "My Table"
;

;
insert into "My Table" () select  
from temp.Cache
;
drop table temp.Cache
;
pragma foreign_keys = YES
;

4. If any of that generates an error, then issue a rollback. If it works fine, 
then execute commit.

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] Why can't SQLite drop columns?

[Darren Duncan]

Jay Kreibich wrote:

On Oct 8, 2012, at 3:36 PM, Darren Duncan  wrote:

Of course, if that is done, then in order to have predictable performance we'd also want 
to add some other statement one can invoke when they want to reclaim disk space later, 
which actually goes and rewrites the table, rather than this just happening 
"automatically" (though it could also be configured to happen automatically.


That would be VACUUM, which already rewrites the tables, and would have to be 
run to reclaim the space anyways.


Yes, that's what I meant, I didn't recall if SQLite already had such 
functionality (I knew of it in other DBMSs). -- Darren Duncan

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

Re: [sqlite] Why can't SQLite drop columns?

[Petite Abeille]

On Oct 8, 2012, at 10:47 PM, gregorinator  wrote:

>  (they do it by copying the table, but it's transparent to
> you). 

How do they deal with constraints? triggers? indexes? others?

If this was as straightforward as a 'create table bar as select a, b, c from 
foo; drop table foo; alter table bar rename to foo;', we would most likely not 
having this conversation :)



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

[sqlite] Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT:0x8007007E)

[Robert Calco]

I have an interesting (read: frustrating) situation in which I have two
.NET (C#) solutions which share some common projects. One of this
encapsulates our data access layer, and that is using System.Data.SQLite
version 1.0.82.0.

They are all running .NET 4.5, C# 2012, Debug, AnyCPU mode, and the unit
tests look identically configured, both on the hard drive and logically in
their respective solution trees. I used NuGet to install SQLite in both
solutions, so they each have their own copy in their respective /packages
directory.

So, to the naked eye, they seem identical. However, one solutions' unit
tests run absolutely fine, and the other one throws the error in the
subject of this email. Both are pulling in the common data layer project
which is the one that depends on SQLite. both have the following in the
Debug\bin directory:

x64\ (folder with 64 bit version of SQLite.Interop.dll in it)
x86\ (folder with 32 bit version of SQLite.Interop.dll in it)

... rest of the binaries required

I tried configuring all projects in the problem solution to Debug, x86, and
I even tried dropping the 32-bit version of the SQLite.Interop.dll into the
bin directory of the unit tests... and STILL I get this error.

I saw on post, a couple of google searches ago, that indicated perhaps the
version of the MS runtime DLLs might be the issue. Is it possible that one
of my projects has a "newer" version of System.Data.SQLite that was
compiled against a "newer" version of the MS C++ runtime dlls, even though
they have the same version (1.0.82.0)? (I have not upgraded to the latest
MS C++ runtime DLLs in my 2012 project, for all kinds of reasons that have
to do with some of our own C/C++ dependencies in native sub-projects.)

How should I troubleshoot this?

And in general how does SQLite work in "AnyCPU" mode if it needs
SQLite.Interop.dll to run, and there are precisely two mutually exclusive
versions of it -- 32 and 64 bit?

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

Re: [sqlite] Why can't SQLite drop columns?

[gregorinator]

Some freeware SQLite tools, such as SQLiteStudio, allow you to drop
columns (they do it by copying the table, but it's transparent to
you).  If you don't need to drop the column programmatically, as part
of a bigger automated process, it works and you don't have to code
anything.

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

Re: [sqlite] Why can't SQLite drop columns?

[Petite Abeille]

On Oct 8, 2012, at 10:36 PM, Darren Duncan  wrote:

> Petite Abeille wrote:
>> On Oct 8, 2012, at 10:12 PM, Richard Hipp  wrote:
>>> ALTER TABLE DROP COLUMN requires writing every row of the table.
>> Could be a 'logical delete' instead. In other words, a dictionary operation 
>> instead of a data one.
>> http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete
> 
> I don't think you want to follow the syntax of that Oracle example, the 
> "ALTER TABLE foo SET UNUSED (bar, baz)", especially since that is reversible 
> and it could mess up other issues such as effectively invalidating 
> constraints.

Yes, I would not suggest using the Oracle syntax either. This was more for 
illustration purpose.

> *All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* 
> a "logical delete", no matter if column data is physically removed or just 
> made inaccessible.

"In theory there is no difference between theory and practice. In practice 
there is."
-- Yogi Berra


> SQLite can add the DROP COLUMN syntax but just implement it so that tables 
> don't have to be rewritten, but just that the then-superfluous table data is 
> ignored, and so you still get O(1).

Precisely.

> Of course, if that is done, then in order to have predictable performance 
> we'd also want to add some other statement one can invoke when they want to 
> reclaim disk space later, which actually goes and rewrites the table, rather 
> than this just happening "automatically" (though it could also be configured 
> to happen automatically).

vacuum?


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

Re: [sqlite] Why can't SQLite drop columns?

[Yves Goergen]

On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote:
> The only ALTER TABLE functions that SQLite supports are those that can be
> accomplished without having to rewrite the entire table.

Oh, and what about the VACUUM command? That certainly needs to recreate
just about everything, and yet it's here. (Thankfully!)

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't SQLite drop columns?

[Yves Goergen]

On 08.10.2012 22:12 CE(S)T, Richard Hipp wrote:
> ALTER TABLE DROP COLUMN requires writing every row of the table.

Actually, I wouldn't care what the engine needs to do to get the job
done. If it's documented that a certain command requires more time, it
would be perfectly fine. SELECT queries also don't always return in a
constant time, each of them requires different work to be done.

So, to update my question: Why doesn't SQLite support DDL commands that
internally need recreating the table or may not return in O(1) time?

Of course, when the engine recreates the table, I'd expect it to
maintain all foreign key references, if possible. That's probably even
more work from the outside as a normal database user. (You know, the
engine can trust the things it does itself (at least I hope so), but it
doesn't necessarily trust the things a user does.)

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't SQLite drop columns?

[Petite Abeille]

On Oct 8, 2012, at 10:12 PM, Richard Hipp  wrote:

>  the only ALTER TABLE functions supported are those that can be
> done in O(1) time.

Out of curiosity, does 'add column not null default' modify the data or just 
the table definition?

For example:

create table foo( x, y );
insert into foo values( 1, null );
alter table foo add column z not null default 'z' ;
select * from foo;
> x|y|z
> 1||z


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

Re: [sqlite] Why can't SQLite drop columns?

[Jay Kreibich]

On Oct 8, 2012, at 3:36 PM, Darren Duncan  wrote:

> Petite Abeille wrote:
>> On Oct 8, 2012, at 10:12 PM, Richard Hipp  wrote:
>>> ALTER TABLE DROP COLUMN requires writing every row of the table.
>> Could be a 'logical delete' instead. In other words, a dictionary operation 
>> instead of a data one.
>> http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete
> 
> I don't think you want to follow the syntax of that Oracle example, the 
> "ALTER TABLE foo SET UNUSED (bar, baz)", especially since that is reversible 
> and it could mess up other issues such as effectively invalidating 
> constraints.
> 
> *All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* 
> a "logical delete", no matter if column data is physically removed or just 
> made inaccessible.
> 
> SQLite can add the DROP COLUMN syntax but just implement it so that tables 
> don't have to be rewritten, but just that the then-superfluous table data is 
> ignored, and so you still get O(1).
> 
> Of course, if that is done, then in order to have predictable performance 
> we'd also want to add some other statement one can invoke when they want to 
> reclaim disk space later, which actually goes and rewrites the table, rather 
> than this just happening "automatically" (though it could also be configured 
> to happen automatically.

That would be VACUUM, which already rewrites the tables, and would have to be 
run to reclaim the space anyways.

  -j




> -- Darren Duncan
> ___
> 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] Why can't SQLite drop columns?

[Darren Duncan]

Petite Abeille wrote:

On Oct 8, 2012, at 10:12 PM, Richard Hipp  wrote:


ALTER TABLE DROP COLUMN requires writing every row of the table.


Could be a 'logical delete' instead. In other words, a dictionary operation 
instead of a data one.

http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete


I don't think you want to follow the syntax of that Oracle example, the "ALTER 
TABLE foo SET UNUSED (bar, baz)", especially since that is reversible and it 
could mess up other issues such as effectively invalidating constraints.


*All* SQL actions are /supposed/ to be logical, meaning that DROP COLUMN *is* a 
"logical delete", no matter if column data is physically removed or just made 
inaccessible.


SQLite can add the DROP COLUMN syntax but just implement it so that tables don't 
have to be rewritten, but just that the then-superfluous table data is ignored, 
and so you still get O(1).


Of course, if that is done, then in order to have predictable performance we'd 
also want to add some other statement one can invoke when they want to reclaim 
disk space later, which actually goes and rewrites the table, rather than this 
just happening "automatically" (though it could also be configured to happen 
automatically).


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

Re: [sqlite] Why can't SQLite drop columns?

[Petite Abeille]

On Oct 8, 2012, at 10:12 PM, Richard Hipp  wrote:

> ALTER TABLE DROP COLUMN requires writing every row of the table.

Could be a 'logical delete' instead. In other words, a dictionary operation 
instead of a data one.

http://www.oracle-base.com/articles/8i/dropping-columns.php#LogicalDelete

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

Re: [sqlite] Unofficial poll

[Yves Goergen]

On 23.09.2012 12:37 CE(S)T, Baruch Burstein wrote:
> Has anyone ever actually taken advantage of this feature? In what case?

No. And I guess I wouldn't need it. If I need that in other systems, I
simply use the most generic type, varchar/text or blob. This way, at
least it's obvious that text can appear in a column. I don't see any use
in letting the data store accept arbitrary data types.

After all, some code must read and understand the data again! And code
is usually very restricted in what it can do with data. Some values must
be added/multiplied/..., others are concatenated. Each algorithm needs
certain data types, even if the programming language doesn't enforce
them. While you could always use a different data type at your will, you
have to live with the consequences of doing so: bugs and crashes.
Sometimes even unnoticed.

And if the data will not be processed by code, but simply stored for a
user to read it, a simple text column will always do.

I code C# and PHP a lot. C# has a mostly static type system and it has
the great benefit that the compiler can check the entire application
before you need to execute every line of it. PHP doesn't have that
advantage and I regularly produce and find bugs due to that. But also in
PHP, I carefully select the appropriate type (i.e. don't rely on
auto-conversion too much) and rely on it being preserved. That allows me
to add my own type-checking and find bugs a lot faster.

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why can't SQLite drop columns?

[Richard Hipp]

On Mon, Oct 8, 2012 at 3:57 PM, Yves Goergen wrote:

>
> Why does SQLite still not support dropping columns
> through SQL after all these years?
>

The only ALTER TABLE functions that SQLite supports are those that can be
accomplished without having to rewrite the entire table.  Or, to put it
another way, the only ALTER TABLE functions supported are those that can be
done in O(1) time.

ALTER TABLE DROP COLUMN requires writing every row of the table.

-- 
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] Why can't SQLite drop columns?

[Yves Goergen]

Hello,

I know that SQLite doesn't support ALTER TABLE DROP COLUMN, unlike
probably every other SQL database system. But every time I come across
this, I feel the pain of having to write huge amounts of code to
automatically remove single columns in a table. When doing that in code,
it's usually working non-interactively on some generic table schema and
cannot use hard-coded column names. So I really have to collect all
relevant data including foreign keys and all column attributes and then
generate the right SQL code to copy everything right except the dropped
column.

I very much believe that it would save a lot of developers' resources if
SQLite supported that directly. After all, the DBMS has all the data it
needs in its readily readable data structures. It would possibly be less
work for SQLite than for anybody using it.

So I am asking: Why does SQLite still not support dropping columns
through SQL after all these years? Do the SQLite developers have strong
arguments against it, and which? Are there technical limitations (I
can't believe that)? Is there some kind of religion behind it?

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

[Petite Abeille]

On Oct 8, 2012, at 3:44 PM, Clemens Ladisch  wrote:

> Because it's not called SQLFat.

It's called SQLite because it's self-contained and embeddable, not because it 
sports subpar features.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - Sqlite3 and Visual Studio 10

[Arbol One]

yap, that's what it was, thanks!

... work out your salvation with fear and trembling
Eat Kosher

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: Monday, October 08, 2012 10:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] C++ - Sqlite3 and Visual Studio 10

Arbol One wrote:
> error LNK2019: unresolved external symbol _sqlite3_prepare_v2

It appears you forgot to include sqlite3.c in your application.


Regards,
Clemens


> This e-mail is for the sole use of the intended recipient and may 
> contain confidential or privileged information. Unauthorized use of 
> its contents is prohibited. If you have received this e-mail in error, 
> please notify sender immediately via return e-mail and then delete the
original e-mail.

This e-mail contains public information intended for any subscriber of this
mailing list and for anybody else who bothers to read it; it will be copied,
disclosed and distributed to the public.  If you think you are not the
intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in those
e-mails.  Additionally, confidentiality notices in those e-mails will incur
legal processing fees of $42 per line; you have agreed to this by reading
this confidentiality notice.
___
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] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Good point.  Also worth a look.

> From: kevin.m.ben...@gmail.com
> Date: Mon, 8 Oct 2012 12:42:09 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> On Mon, Oct 8, 2012 at 12:32 PM, Matthew Dumbleton wrote:
> 
> >
> > Thanks Kevin.
> > I will see if I can find some sort of registry hack or something to
> > disable this and see if it makes any difference.
> >
> 
> Actually, you might investigate how to access the RTLog Manager to examine
> the log(s) and see what it might reveal ( after all, it appears as if it
> might be for debugging situations just like your experiencing ;-)
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] Seemingly random Access violation errors (resent)

[Kevin Benson]

On Mon, Oct 8, 2012 at 12:32 PM, Matthew Dumbleton wrote:

>
> Thanks Kevin.
> I will see if I can find some sort of registry hack or something to
> disable this and see if it makes any difference.
>

Actually, you might investigate how to access the RTLog Manager to examine
the log(s) and see what it might reveal ( after all, it appears as if it
might be for debugging situations just like your experiencing ;-)
--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Thanks Kevin.
I will see if I can find some sort of registry hack or something to disable 
this and see if it makes any difference.

> From: kevin.m.ben...@gmail.com
> Date: Mon, 8 Oct 2012 12:14:58 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> On Mon, Oct 8, 2012 at 11:57 AM, Matthew Dumbleton wrote:
> 
> > Joe,
> >
> > > It's worked on every device that I've used in the past.
> >
> > I hope you see the funny side when I say, this doesn't fill me with a
> > great amount of confidence as according to your previous messages you don't
> > have a device to test on, only emulators. :)
> >
> >
> > > I have no idea what the "rtlogshimeng" DLL is. I tried searching for
> > > references to it on the web to no avail. It might be totally harmless;
> > > however, it seems to be implicated by the debug output.
> >
> > I have no doubt that the dll is harmless as even with a complete reflash
> > of the device it exists. My guess would be that it is a Motorola
> > device-specific library since we cannot find any reference to it and given
> > your comment above (despite my tongue in cheek response.)
> > Still the fact that the problem occurs between another threads
> > sqlite3_finalize() calls and that calling GC.Collect before making a
> > database connection seems to hide the issue doesn't suggest that SQLite is
> > completely exonerated.
> >
> > > Does everything work properly when only a single thread is used?The
> > answer is, no if the only thread doing the database accessing is not the
> > foreground (UI) thread. If you mean, there is literally just a UI thread
> > making database connections in amongst user activity, then (I need to do
> > more testing but) certainly the issue becomes a lot less easy to recreate
> > (i.e. in the small amount of time I've had to test it has not reoccured in
> > those conditions). Unfortunately the app I had in mind requires more than a
> > single thread even if only one thread can access the database at once.
> >
> > Please do not interpret my disappointment as criticism, it is more of a
> > compliment to SQLite that I am so frustrated that I will not be able to
> > take advantage of it.
> >
> > Thank you again for all your help, it was and is greatly appreciated. At
> > least now I know.
> >
> 
> Having seen your mention of Motorola confirms my web search bringing up
> Motorola's Debug Tool known as RT Logs (Retail Tools - system driver
> logs).My guess is that the "rtlogshimeng" DLL reference is related to their
> tool:
> 
> http://goo.gl/sF4hU
> 
> 
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] Seemingly random Access violation errors (resent)

[Kevin Benson]

On Mon, Oct 8, 2012 at 11:57 AM, Matthew Dumbleton wrote:

> Joe,
>
> > It's worked on every device that I've used in the past.
>
> I hope you see the funny side when I say, this doesn't fill me with a
> great amount of confidence as according to your previous messages you don't
> have a device to test on, only emulators. :)
>
>
> > I have no idea what the "rtlogshimeng" DLL is. I tried searching for
> > references to it on the web to no avail. It might be totally harmless;
> > however, it seems to be implicated by the debug output.
>
> I have no doubt that the dll is harmless as even with a complete reflash
> of the device it exists. My guess would be that it is a Motorola
> device-specific library since we cannot find any reference to it and given
> your comment above (despite my tongue in cheek response.)
> Still the fact that the problem occurs between another threads
> sqlite3_finalize() calls and that calling GC.Collect before making a
> database connection seems to hide the issue doesn't suggest that SQLite is
> completely exonerated.
>
> > Does everything work properly when only a single thread is used?The
> answer is, no if the only thread doing the database accessing is not the
> foreground (UI) thread. If you mean, there is literally just a UI thread
> making database connections in amongst user activity, then (I need to do
> more testing but) certainly the issue becomes a lot less easy to recreate
> (i.e. in the small amount of time I've had to test it has not reoccured in
> those conditions). Unfortunately the app I had in mind requires more than a
> single thread even if only one thread can access the database at once.
>
> Please do not interpret my disappointment as criticism, it is more of a
> compliment to SQLite that I am so frustrated that I will not be able to
> take advantage of it.
>
> Thank you again for all your help, it was and is greatly appreciated. At
> least now I know.
>

Having seen your mention of Motorola confirms my web search bringing up
Motorola's Debug Tool known as RT Logs (Retail Tools - system driver
logs).My guess is that the "rtlogshimeng" DLL reference is related to their
tool:

http://goo.gl/sF4hU


--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seemingly random Access violation errors (resent)

[Black, Michael (IS)]

Can you check the difference in stack size between the emulator and the real 
device?

The emulator could well have a larger default stack size and you're getting 
stack overflow on the real device.


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 Matthew Dumbleton [msd...@hotmail.com]
Sent: Monday, October 08, 2012 11:10 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Seemingly random Access violation errors (resent)

Simon,

Apologies if it seems like I'm picking on SQLite.  As previously stated I am 
just disappointed that I don't seem to be able to use it in a simple c# app.  
I'm sure it works great on other platforms/devices etc.  just wanted to get in 
on the act. No insult to c, c++ developers and/or any and all contributors to 
sqlite intended.

I can send you the simple c# app I wrote to recreate the problem if you like 
but from what Joe is saying this is just a device/platform issue I will have to 
accept.  Fair enough.


> From: slav...@bigfraud.org
> Date: Mon, 8 Oct 2012 16:54:00 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
>
>
> On 8 Oct 2012, at 3:54pm, Matthew Dumbleton  wrote:
>
> > So does this mean therefore SQLite will not currently work on a compact 
> > framework device? (Or at least not on mine.)
>
> SQLite is distributed as C source code.  It's the .c and .h files you find 
> when you download the amalgamation from
>
> 
>
> You're meant to compile these files into your project and call the C routines 
> directly.  If your preferred programming language can call C routines, it can 
> use the SQLite API.
>
> What you're trying to use is a DLL.  You can by all means complain that a DLL 
> doesn't work, but the DLL is not SQLite, it's someone trying to be helpful 
> and package SQLite into the DLLs some people seem to want, to save them 
> compiling their own.
>
> From reading this thread so far, it seems that you have a bug in your 
> application but it's possible that you've found a bug in a .NET wrapper for 
> SQLite.  Neither of these are SQLite.
>
> 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] Handled images in database: URL or blob ?

[Keith Medcalf]

> > Images are handled in the app: photo of products, photos of customers, etc.
> > Each client can have thousands.
> > Advise you manage images as blob in the database? or have only the URL of
> > images in the database (so the image files on the hard disk)?

> > The first solution seems simpler, more secure. But is it slows down
> > significantly the database? Especially if the database is encrypted?

> Using a web service is different to asking the same question about an app
> which runs on one computer.  If your web server would serve the images
> straight from files on disk, I would leave the images as individual files.
> Web server software is optimised to serve many different files, caching them
> intelligently.  It will do a good job of working out how to serve many
> different files in an efficient manner.

> If you keep the image data inside the database you have to write some code
> which will extract the data and present it as an 'image/jpeg' file or
> whatever it is.  This isn't too difficult but it's one more thing to go
> wrong, the server won't be able to cache those returns, and doing the
> processing (especially for an encrypted database) will require more CPU than
> just serving the images from disk.  If secrecy of the images is an issue make
> sure you are serving over HTTPS not HTTP.

The OP will also have to generate "proper" metadata and cache control when 
processing a GET for the image, and also be able to respond to HEAD requests to 
check for client-side and intermediate cache validation.  On top of the server 
overhead incurred because the underlying data is dynamic therefore having to 
call into the app code and do a database dip on every HEAD and GET request, not 
coding the metadata and cache control correctly will result in mucho duplicated 
traffic to repeatedly tranfer the same unchanged image, and performance will be 
terrible.

In other words, the application will have to duplicate the sematics of a 
filestore by manually accounting for all the processing that is already coded 
into the web server to handle static files.

I'd just store the images in files on disk and syncronize the filesystem 
contents with the URLs stored in the database.  Why go to all the complication 
of duplicating huge swaths of code already present in the web server and then 
debugging that code.  Unless of course you have written web servers before and 
know all the nuances that you have to deal with to correctly respond to dynamic 
requests for (mostly) static content.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Simon,

Apologies if it seems like I'm picking on SQLite.  As previously stated I am 
just disappointed that I don't seem to be able to use it in a simple c# app.  
I'm sure it works great on other platforms/devices etc.  just wanted to get in 
on the act. No insult to c, c++ developers and/or any and all contributors to 
sqlite intended.

I can send you the simple c# app I wrote to recreate the problem if you like 
but from what Joe is saying this is just a device/platform issue I will have to 
accept.  Fair enough.


> From: slav...@bigfraud.org
> Date: Mon, 8 Oct 2012 16:54:00 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> 
> On 8 Oct 2012, at 3:54pm, Matthew Dumbleton  wrote:
> 
> > So does this mean therefore SQLite will not currently work on a compact 
> > framework device? (Or at least not on mine.)
> 
> SQLite is distributed as C source code.  It's the .c and .h files you find 
> when you download the amalgamation from
> 
> 
> 
> You're meant to compile these files into your project and call the C routines 
> directly.  If your preferred programming language can call C routines, it can 
> use the SQLite API.
> 
> What you're trying to use is a DLL.  You can by all means complain that a DLL 
> doesn't work, but the DLL is not SQLite, it's someone trying to be helpful 
> and package SQLite into the DLLs some people seem to want, to save them 
> compiling their own.
> 
> From reading this thread so far, it seems that you have a bug in your 
> application but it's possible that you've found a bug in a .NET wrapper for 
> SQLite.  Neither of these are SQLite.
> 
> 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] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Yes.  Motorola ES400.  Do have any advice/experience with these? (Apart from 
'don't use them' :)  )

> From: kevin.m.ben...@gmail.com
> Date: Mon, 8 Oct 2012 11:55:57 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> On Mon, Oct 8, 2012 at 10:54 AM, Matthew Dumbleton wrote:
> 
> > So does this mean therefore SQLite will not currently work on a compact
> > framework device? (Or at least not on mine.)
> > There's nothing else installed or running apart from the test app I sent
> > you using SQLite and the OS itself. That dll is protected inside the
> > windows directory on the device so I cannot even
> > try removing/renaming it.
> > Since the device isn't crashing on it's own it's presumably some sort of
> > inadvertant call being made as part of the application running, which
> > disappears when SQLite is not referenced.
> > And since Microsoft aren't likely to release any updates to the compact
> > framework I'm not sure where to go with this.
> >
> > > From: sql...@mistachkin.com
> > > To: sqlite-users@sqlite.org
> > > Date: Mon, 8 Oct 2012 06:00:50 -0700
> > > Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> > >
> > >
> > > Matthew Dumbleton wrote:
> > > >
> > > > Sorry about that. Sent you the attachments properly a while ago and
> > have
> > > > also just sent you a .txt file with the last output from the debugger
> > > > before the access violation crash thanks to your new version.
> > > >
> > >
> > > The start of the trouble seems to be here:
> > >
> > > Data Abort: Thread=8aeec800 Proc=81a374c0 'SQLiteDatabaseEngineTest.exe'
> > > AKY=00010001 PC=7b38a890(netcfagl3_5.dll+0xd890)
> > > RA=7b38a7d0(netcfagl3_5.dll+0xd7d0) BVA=1100 FSR=0005
> > > #$# Exception 0xC005 
> > > #$# Thread procedure: rtlogshimeng.dll!0x000744C8 (in dll)
> > > #$# PC: netcfagl3_5.dll!0xD890 (in dll)
> > > #$# Return address: netcfagl3_5.dll!0xD7D0 (in dll)
> > > #$# Call stack top
> > >
> > > This exception appears to be coming from the .NET Compact Framework
> > itself
> > > on a thread calling into the rtlogshimeng DLL (I'm not sure what this DLL
> > > is for).
> > >
> > > Later on in the debugger output, I see:
> > >
> > > *** ASSERTION FAILED in ../core/sqlite3.c(15799):
> > > pInt[nReserve/sizeof(int)]==(int)0xE4676B53
> > >
> > > This type of assertion failure indicates generalized heap corruption.
> > > After this, nothing in the heap can truly be trusted.
> > >
> > > --
> > > Joe Mistachkin
> 
> 
> Are you having this problem with a Motorola device?
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Joe,

> It's worked on every device that I've used in the past.

I hope you see the funny side when I say, this doesn't fill me with a great 
amount of confidence as according to your previous messages you don't have a 
device to test on, only emulators. :)


> I have no idea what the "rtlogshimeng" DLL is.  I tried searching for
> references to it on the web to no avail.  It might be totally harmless;
> however, it seems to be implicated by the debug output.

I have no doubt that the dll is harmless as even with a complete reflash of the 
device it exists.  My guess would be that it is a Motorola device-specific 
library since we cannot find any reference to it and given your comment above 
(despite my tongue in cheek response.)
Still the fact that the problem occurs between another threads 
sqlite3_finalize() calls and that calling GC.Collect before making a database 
connection seems to hide the issue doesn't suggest that SQLite is completely 
exonerated.

> Does everything work properly when only a single thread is used?The answer 
> is, no if the only thread doing the database accessing is not the foreground 
> (UI) thread.  If you mean, there is literally just a UI thread making 
> database connections in amongst user activity, then (I need to do more 
> testing but) certainly the issue becomes a lot less easy to recreate (i.e. in 
> the small amount of time I've had to test it has not reoccured in those 
> conditions).  Unfortunately the app I had in mind requires more than a single 
> thread even if only one thread can access the database at once.

Please do not interpret my disappointment as criticism, it is more of a 
compliment to SQLite that I am so frustrated that I will not be able to take 
advantage of it.

Thank you again for all your help, it was and is greatly appreciated.  At least 
now I know.


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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Kevin Benson]

On Mon, Oct 8, 2012 at 10:54 AM, Matthew Dumbleton wrote:

> So does this mean therefore SQLite will not currently work on a compact
> framework device? (Or at least not on mine.)
> There's nothing else installed or running apart from the test app I sent
> you using SQLite and the OS itself. That dll is protected inside the
> windows directory on the device so I cannot even
> try removing/renaming it.
> Since the device isn't crashing on it's own it's presumably some sort of
> inadvertant call being made as part of the application running, which
> disappears when SQLite is not referenced.
> And since Microsoft aren't likely to release any updates to the compact
> framework I'm not sure where to go with this.
>
> > From: sql...@mistachkin.com
> > To: sqlite-users@sqlite.org
> > Date: Mon, 8 Oct 2012 06:00:50 -0700
> > Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> >
> >
> > Matthew Dumbleton wrote:
> > >
> > > Sorry about that. Sent you the attachments properly a while ago and
> have
> > > also just sent you a .txt file with the last output from the debugger
> > > before the access violation crash thanks to your new version.
> > >
> >
> > The start of the trouble seems to be here:
> >
> > Data Abort: Thread=8aeec800 Proc=81a374c0 'SQLiteDatabaseEngineTest.exe'
> > AKY=00010001 PC=7b38a890(netcfagl3_5.dll+0xd890)
> > RA=7b38a7d0(netcfagl3_5.dll+0xd7d0) BVA=1100 FSR=0005
> > #$# Exception 0xC005 
> > #$# Thread procedure: rtlogshimeng.dll!0x000744C8 (in dll)
> > #$# PC: netcfagl3_5.dll!0xD890 (in dll)
> > #$# Return address: netcfagl3_5.dll!0xD7D0 (in dll)
> > #$# Call stack top
> >
> > This exception appears to be coming from the .NET Compact Framework
> itself
> > on a thread calling into the rtlogshimeng DLL (I'm not sure what this DLL
> > is for).
> >
> > Later on in the debugger output, I see:
> >
> > *** ASSERTION FAILED in ../core/sqlite3.c(15799):
> > pInt[nReserve/sizeof(int)]==(int)0xE4676B53
> >
> > This type of assertion failure indicates generalized heap corruption.
> > After this, nothing in the heap can truly be trusted.
> >
> > --
> > Joe Mistachkin


Are you having this problem with a Motorola device?
--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seemingly random Access violation errors (resent)

[Simon Slavin]

On 8 Oct 2012, at 3:54pm, Matthew Dumbleton  wrote:

> So does this mean therefore SQLite will not currently work on a compact 
> framework device? (Or at least not on mine.)

SQLite is distributed as C source code.  It's the .c and .h files you find when 
you download the amalgamation from



You're meant to compile these files into your project and call the C routines 
directly.  If your preferred programming language can call C routines, it can 
use the SQLite API.

What you're trying to use is a DLL.  You can by all means complain that a DLL 
doesn't work, but the DLL is not SQLite, it's someone trying to be helpful and 
package SQLite into the DLLs some people seem to want, to save them compiling 
their own.

>From reading this thread so far, it seems that you have a bug in your 
>application but it's possible that you've found a bug in a .NET wrapper for 
>SQLite.  Neither of these are SQLite.

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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Joe Mistachkin]

Matthew Dumbleton wrote:
>
> So does this mean therefore SQLite will not currently work on a
> compact framework device? (Or at least not on mine.)
> 

It's worked on every device that I've used in the past.

>
> That dll is protected inside the windows directory on the device so I
> cannot even try removing/renaming it.
>

I have no idea what the "rtlogshimeng" DLL is.  I tried searching for
references to it on the web to no avail.  It might be totally harmless;
however, it seems to be implicated by the debug output.

>
> Since the device isn't crashing on it's own it's presumably some sort of
> inadvertant call being made as part of the application running, which
> disappears when SQLite is not referenced.  And since Microsoft aren't
> likely to release any updates to the compact framework I'm not sure where
> to go with this.
>

Does everything work properly when only a single thread is used?

--
Joe Mistachkin

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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

So does this mean therefore SQLite will not currently work on a compact 
framework device? (Or at least not on mine.)
There's nothing else installed or running apart from the test app I sent you 
using SQLite and the OS itself.  That dll is protected inside the windows 
directory on the device so I cannot even
try removing/renaming it.
Since the device isn't crashing on it's own it's presumably some sort of 
inadvertant call being made as part of the application running, which 
disappears when SQLite is not referenced.
And since Microsoft aren't likely to release any updates to the compact 
framework I'm not sure where to go with this.

> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Mon, 8 Oct 2012 06:00:50 -0700
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> 
> Matthew Dumbleton wrote:
> > 
> > Sorry about that.  Sent you the attachments properly a while ago and have
> > also just sent you a .txt file with the last output from the debugger
> > before the access violation crash thanks to your new version.
> > 
> 
> The start of the trouble seems to be here:
> 
> Data Abort: Thread=8aeec800 Proc=81a374c0 'SQLiteDatabaseEngineTest.exe'
> AKY=00010001 PC=7b38a890(netcfagl3_5.dll+0xd890)
> RA=7b38a7d0(netcfagl3_5.dll+0xd7d0) BVA=1100 FSR=0005
> #$# Exception 0xC005 
> #$# Thread procedure: rtlogshimeng.dll!0x000744C8 (in dll)
> #$# PC: netcfagl3_5.dll!0xD890 (in dll)
> #$# Return address: netcfagl3_5.dll!0xD7D0 (in dll)
> #$# Call stack top
> 
> This exception appears to be coming from the .NET Compact Framework itself
> on a thread calling into the rtlogshimeng DLL (I'm not sure what this DLL
> is for).
> 
> Later on in the debugger output, I see:
> 
>   *** ASSERTION FAILED in ../core/sqlite3.c(15799):
>   pInt[nReserve/sizeof(int)]==(int)0xE4676B53
> 
> This type of assertion failure indicates generalized heap corruption.
> After this, nothing in the heap can truly be trusted.
> 
> --
> 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] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

[Simon Slavin]

On 7 Oct 2012, at 10:32am, Marcel Wesołowski  wrote:

> In SQLite when strings are concatenated (with ||) and one of them is null,
> the result will be null.
> This is not a required behaviour (not intuitive)

Actually, it is required behaviour, and I find it intuitive given what NULL 
means.  SQL92 6.13 General Rules 2(a)

"If either S1 or S2 is the null value, then the result of the
   is the null value."

Generally, finding NULL unexpectedly is a big problem and you almost never find 
a serious program showing a NULL value as ''.  Normally you want them to be a 
great big 'VALUE MISSING !1!!' kind of thing to show that something is 
wrong.  That's what 'ifnull()' is for.

> and most users tend to add
> *ifnull(X, '')* to get proper results.
> This slows down the whole operation, when we have lots of records in the
> database.

In SQL there is a huge difference between a NULL and a zero-length string and 
your data should respect that difference.  If you want a value to act as 
zero-length strings, then you will find it far more convenient to actually 
store the zero-length string and not a NULL.  That way you can do your concats 
and other operations without having to worry about NULLs everywhere.  Makes 
both your data storage and your code smaller and more efficient.

> Would it be hard to add both: compile time option (in C source) and pragma
> function to make concat yeld different results?
> In MS database there is *CONCAT_NULL_YIELDS_NULL* setting which does exacly
> what it states.
> Why there's no such functionality in SQLite?
> 
> Moreover there also should be function called *concat(...)* which would
> take multiple arguments instead of using '||' operator.

SQLite is 'lite'.  It's very small and contains only the bare minimum of 
functions, all of which make sense and conform to the SQL standard (as best as 
practical).  If you keep adding functions only a minority of people want you 
end up with something which isn't 'lite' any more.

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

Re: [sqlite] C++ - Sqlite3 and Visual Studio 10

[Clemens Ladisch]

Arbol One wrote:
> error LNK2019: unresolved external symbol _sqlite3_prepare_v2

It appears you forgot to include sqlite3.c in your application.


Regards,
Clemens


> This e-mail is for the sole use of the intended recipient and may contain
> confidential or privileged information. Unauthorized use of its contents is
> prohibited. If you have received this e-mail in error, please notify sender
> immediately via return e-mail and then delete the original e-mail.

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

[Jay A. Kreibich]

On Mon, Oct 08, 2012 at 03:44:01PM +0200, Clemens Ladisch scratched on the wall:
> Marcel Weso?owski wrote:

> > Why there's no such functionality in SQLite?
> 
> Because it's not called SQLFat.

  More to the point, it would be pretty trivial to write your own
  extension that includes a concat() function.  That way you get the
  function you want and the behavior you want.

   -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] C++ - Sqlite3 and Visual Studio 10

[Arbol One]

When trying to port my application from Code::Blocks(windows version) to
Visual Studio 10, I am getting some error, similar to the one below:
1>my_sqlite3.obj : error LNK2019: unresolved external symbol
_sqlite3_prepare_v2 referenced in function "public: bool __thiscall
mySQLite3::notExist(class Glib::ustring const &)"
(?notExist@mySQLite3@jme@@QAE_NABVustring@Glib@@@Z)

I was hoping someone here might have gone through the same experience and
might be able to help resolve this issue.

TIA

This e-mail is for the sole use of the intended recipient and may contain
confidential or privileged information. Unauthorized use of its contents is
prohibited. If you have received this e-mail in error, please notify sender
immediately via return e-mail and then delete the original e-mail.



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

Re: [sqlite] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

[Clemens Ladisch]

Marcel Wesołowski wrote:
> In SQLite when strings are concatenated (with ||) and one of them is null,
> the result will be null.
> This is not a required behaviour

It *is* required by all SQL standards.

> (not intuitive)

It's intuitive for everybody with experience with SQL.
That is how _all_ operations on NULL values behave.

> and most users tend to add *ifnull(X, '')* to get proper results.
> This slows down the whole operation, when we have lots of records in the
> database.

Then why don't they store empty strings in the first place?

> Would it be hard to add both: compile time option (in C source) and pragma
> function to make concat yeld different results?

It would be hard to document the interactions between all these settings,
and to handle all the complaints when it isn't set as one would expect.

> In MS database there is *CONCAT_NULL_YIELDS_NULL* setting which does exacly
> what it states.

 says:
| *Important*
| In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always
| be ON and any applications that explicitly set the option to OFF will
| generate an error.  Avoid using this feature in new development work,
| and plan to modify applications that currently use this feature.

> Why there's no such functionality in SQLite?

Because it's not called SQLFat.


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

[sqlite] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

[Marcel Wesołowski]

Hello,

I have a feature request.

In SQLite when strings are concatenated (with ||) and one of them is null,
the result will be null.
This is not a required behaviour (not intuitive) and most users tend to add
*ifnull(X, '')* to get proper results.
This slows down the whole operation, when we have lots of records in the
database.

Would it be hard to add both: compile time option (in C source) and pragma
function to make concat yeld different results?
In MS database there is *CONCAT_NULL_YIELDS_NULL* setting which does exacly
what it states.
Why there's no such functionality in SQLite?

Moreover there also should be function called *concat(...)* which would
take multiple arguments instead of using '||' operator.

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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Joe Mistachkin]

Matthew Dumbleton wrote:
> 
> Sorry about that.  Sent you the attachments properly a while ago and have
> also just sent you a .txt file with the last output from the debugger
> before the access violation crash thanks to your new version.
> 

The start of the trouble seems to be here:

Data Abort: Thread=8aeec800 Proc=81a374c0 'SQLiteDatabaseEngineTest.exe'
AKY=00010001 PC=7b38a890(netcfagl3_5.dll+0xd890)
RA=7b38a7d0(netcfagl3_5.dll+0xd7d0) BVA=1100 FSR=0005
#$# Exception 0xC005 
#$# Thread procedure: rtlogshimeng.dll!0x000744C8 (in dll)
#$# PC: netcfagl3_5.dll!0xD890 (in dll)
#$# Return address: netcfagl3_5.dll!0xD7D0 (in dll)
#$# Call stack top

This exception appears to be coming from the .NET Compact Framework itself
on a thread calling into the rtlogshimeng DLL (I'm not sure what this DLL
is for).

Later on in the debugger output, I see:

*** ASSERTION FAILED in ../core/sqlite3.c(15799):
pInt[nReserve/sizeof(int)]==(int)0xE4676B53

This type of assertion failure indicates generalized heap corruption.
After this, nothing in the heap can truly be trusted.

--
Joe Mistachkin

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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Matthew Dumbleton]

Sorry about that.  Sent you the attachments properly a while ago and have also 
just sent you a .txt file with the last output from the debugger before the 
access violation crash thanks to your new version.

> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Mon, 8 Oct 2012 04:38:37 -0700
> Subject: Re: [sqlite] Seemingly random Access violation errors (resent)
> 
> 
> Matthew Dumbleton wrote:
> > 
> > Have attached the latest .kdmp and pdbs since you last email/source
> > changes.  Hopefully the dump makes more sense to you than it does to
> > me. Builds were debug builds as requested.
> > 
> 
> There were no attachments to your message that I saw.  Also, were you
> able to capture the extra diagnostic output in the debugger?  It should
> be visible in the Debug Output Window.  In order to see it, you may need
> to attach to the target process on the device using the native debugging.
> 
> P.S. Apparently, my SMTP server is now being blocked by Hotmail and I am
> unable to send you direct mail.
> 
> --
> 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] Handled images in database: URL or blob ?

[Simon Slavin]

On 8 Oct 2012, at 11:25am, Paxdo Presse  wrote:

> Hello,
> 
> The context:
> - Web application
> - Billing application
> - Sqlite with WAL mode
> - The database is shared among many clients.
> 
> Images are handled in the app: photo of products, photos of customers, etc.
> Each client can have thousands.
> Advise you manage images as blob in the database? or have only the URL of 
> images in the database (so the image files on the hard disk)?
> 
> The first solution seems simpler, more secure. But is it slows down 
> significantly the database? Especially if the database is encrypted?

Using a web service is different to asking the same question about an app which 
runs on one computer.  If your web server would serve the images straight from 
files on disk, I would leave the images as individual files.  Web server 
software is optimised to serve many different files, caching them 
intelligently.  It will do a good job of working out how to serve many 
different files in an efficient manner.

If you keep the image data inside the database you have to write some code 
which will extract the data and present it as an 'image/jpeg' file or whatever 
it is.  This isn't too difficult but it's one more thing to go wrong, the 
server won't be able to cache those returns, and doing the processing 
(especially for an encrypted database) will require more CPU than just serving 
the images from disk.  If secrecy of the images is an issue make sure you are 
serving over HTTPS not HTTP.

So I would keep just the URLs in the database.  There is nothing impossible 
about the other solution, but I think it will be less efficient.

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

Re: [sqlite] Handled images in database: URL or blob ?

[Clemens Ladisch]

Paxdo Presse wrote:
> Indeed, I thought as a second database for images. This may be a good 
> solution. But there are two flaws:
>
> - With WAL mode, transactions involving multiple databases are no longer 
> atomic, whole.

But files are not atomic at all.  Do you actually need this for images?

> - The transaction will take more time (the database will be occupied longer) 
> than the solution with image files.

Images files have no transaction.  So you could do image manipulations in
a separate transaction, and it would still be safer than files.  :)


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

Re: [sqlite] Seemingly random Access violation errors (resent)

[Joe Mistachkin]

Matthew Dumbleton wrote:
> 
> Have attached the latest .kdmp and pdbs since you last email/source
> changes.  Hopefully the dump makes more sense to you than it does to
> me. Builds were debug builds as requested.
> 

There were no attachments to your message that I saw.  Also, were you
able to capture the extra diagnostic output in the debugger?  It should
be visible in the Debug Output Window.  In order to see it, you may need
to attach to the target process on the device using the native debugging.

P.S. Apparently, my SMTP server is now being blocked by Hotmail and I am
unable to send you direct mail.

--
Joe Mistachkin

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

Re: [sqlite] Handled images in database: URL or blob ?

[Paxdo Presse]

Thank you Clemens!

Indeed, I thought as a second database for images. This may be a good solution. 
But there are two flaws:

- With WAL mode, transactions involving multiple databases are no longer 
atomic, whole.

- The transaction will take more time (the database will be occupied longer) 
than the solution with image files.


> If you store the images in a table that is in a separate database file
> that just gets ATTACHed to the main database, the two page caches will
> not affect each other.


for sure?

thank you

olivier



Le 8 oct. 2012 à 13:16, Clemens Ladisch  a écrit :

> Paxdo Presse wrote:
>> The context:
>> - Web application
>> - Billing application
>> - Sqlite with WAL mode
>> - The database is shared among many clients.
>> 
>> Images are handled in the app: photo of products, photos of customers, etc.
>> Each client can have thousands.
>> Advise you manage images as blob in the database? or have only the URL
>> of images in the database (so the image files on the hard disk)?
>> 
>> The first solution seems simpler
> 
> But how do you get the images out of the database?  Assuming that the
> images are to be displayed on some web pages, if you have the images
> in the file system where they are directly accessible by the web server,
> you can generate pages containing images like this:
> 
>  Look at this cool image: 
> 
> But if the images are stored _only_ in the database, you would need
> some separate script to retrieve them:
> 
>  Look at this cool image: 
> 
> If the images are mostly read-only (so that you don't actually need the
> database management functions for them), the second solutions seems
> simpler.  So far.
> 
>> more secure.
> 
> If clients are not allowed to see other clients' images, then you need
> that script anyway to do access checks.
> 
>> But is it slows down significantly the database?
> 
> Too much image data certainly will blow out the page cache.
> 
> If you store the images in a table that is in a separate database file
> that just gets ATTACHed to the main database, the two page caches will
> not affect each other.
> 
>> Especially if the database is encrypted?
> 
> An encrypted database will not be slower than encrypted files.
> 
> If the images do not need to be encrypted, you can use a separate
> database file that isn't encrypted.
> 
> 
> Regards,
> Clemens
> ___
> 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] Handled images in database: URL or blob ?

[Clemens Ladisch]

Paxdo Presse wrote:
> The context:
> - Web application
> - Billing application
> - Sqlite with WAL mode
> - The database is shared among many clients.
>
> Images are handled in the app: photo of products, photos of customers, etc.
> Each client can have thousands.
> Advise you manage images as blob in the database? or have only the URL
> of images in the database (so the image files on the hard disk)?
>
> The first solution seems simpler

But how do you get the images out of the database?  Assuming that the
images are to be displayed on some web pages, if you have the images
in the file system where they are directly accessible by the web server,
you can generate pages containing images like this:

  Look at this cool image: 

But if the images are stored _only_ in the database, you would need
some separate script to retrieve them:

  Look at this cool image: 

If the images are mostly read-only (so that you don't actually need the
database management functions for them), the second solutions seems
simpler.  So far.

> more secure.

If clients are not allowed to see other clients' images, then you need
that script anyway to do access checks.

> But is it slows down significantly the database?

Too much image data certainly will blow out the page cache.

If you store the images in a table that is in a separate database file
that just gets ATTACHed to the main database, the two page caches will
not affect each other.

> Especially if the database is encrypted?

An encrypted database will not be slower than encrypted files.

If the images do not need to be encrypted, you can use a separate
database file that isn't encrypted.


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

Re: [sqlite] Seemingly random Access Violation errors

[Matthew Dumbleton]

Joe,

Have done as you said and reproduced the issue.  Have sent you direct the 
relevant pdbs and kdmp from the latest crash.


> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Mon, 8 Oct 2012 02:24:08 -0700
> Subject: Re: [sqlite] Seemingly random Access Violation errors
> 
> 
> I've just added some extra diagnostics to the debug build of the native
> interop assembly ("SQLite.Interop.dll"), could you update your local
> sources, compile everything in the debug build configuration, and then
> try to reproduce the problem?
> 
> --
> 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

[sqlite] Handled images in database: URL or blob ?

[Paxdo Presse]

Hello,

The context:
- Web application
- Billing application
- Sqlite with WAL mode
- The database is shared among many clients.

Images are handled in the app: photo of products, photos of customers, etc.
Each client can have thousands.
Advise you manage images as blob in the database? or have only the URL of 
images in the database (so the image files on the hard disk)?

The first solution seems simpler, more secure. But is it slows down 
significantly the database? Especially if the database is encrypted?

thank you for your advice,
olviier
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Seemingly random Access Violation errors

[Joe Mistachkin]

I've just added some extra diagnostics to the debug build of the native
interop assembly ("SQLite.Interop.dll"), could you update your local
sources, compile everything in the debug build configuration, and then
try to reproduce the problem?

--
Joe Mistachkin

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