Re: [sqlite] Basic SQLite/EF6 question

[Graham Holden]

I've no experience of SQLite with .NET/EF6 but...

“Could not load file or assembly ‘System.Data.SQLite, Version=1.0.93.0, 
Culture=neutral, PublicKey Token=db937bc2d44ff139’ or one of its 
dependencies. An attempt was made to load a program with the incorrect 
format.”

in my experience indicates a .NET program running in the 64bit VM calling a 
32bit DLL.  Together with the "x86" of:

sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0

This suggests the core SQLite DLL is 32 bit.  Whether a true 64bit version is 
available,  or whether you have to mark your .NET program as 32bit, others will 
have to answer.

Graham


Sent from Samsung Galaxy Note

 Original message 
From: Steven Davisworth  
Date: 14/07/2014  01:21  (GMT+00:00) 
To: sqlite-users@sqlite.org 
Subject: Re: [sqlite] Basic SQLite/EF6 question 
 
Joe Mistachkin  writes:

> 
> 
> Steven Davisworth wrote:
> > 
> > I've just upgraded PC to 64bit (new PC) and installed VS2013. I've
> followed 
> > standard install instructions as outlined in web posts I've come across
> for 
> > EF6. I've used Syatem.Data.SQLite.EF6 1.0.93.0   Windows (.NET 
Framework 
> > 4.5.1) and seem to be getting the same sorts of errors listed above.
> > 
> 
> What are the specific errors?  I did not see them when I did the testing 
of
> this scenario.  Can you provide the System.Data.SQLite setup package logs?
> 
> The log files should be located in the %TEMP% directory on the machine.
> They
> will have names similar to "Setup Log 2012-12-18 #001.txt" and
> "Installer.exe.trace.tmp29.log".
> 
> >
> > All posts I've come across seem to have the same problem. A heap load 
of 
> > suggestions that don't work.
> >
> 
> I've seen some of those same posts as well.  However, it's important to 
note
> that people may not always report when they succeed at making it 
work...  :)
> 
> > 
> > I've tried the GACing thing as well (contrary to what some posts say) Is
> > this a common issue and will it be addressed soon? (Hope my question is
> not
> > redundant but as I said loads of advice out there but non seem to work)
> > 
> 
> In the official documentation for Entity Framework 6, it is unclear to me
> whether or not their providers need to be registered machine-wide.  
Clearly,
> the design-time components in Visual Studio need various kinds of
> machine-wide
> configuration, hence the confusion here (i.e. especially since the
> components
> are in completely different assemblies).
> 
> --
> Joe Mistachkin
> 
Hi Joe
Thanks for your response.
Installs
sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0
Start VS 2013 and open project
NuGet Package Manager - System.Data.SQLite.EF6 (x86/x64)
 included install - EF 6.1.0
   System.Data.SQLite.Core (x86/x64)
Rebuild done
Add New – Data – ADO.NET Entity Data Model
Choose Model Contents – EF Designer from database
New Connection – Data Provider is .NET Framework Data Provider for SQLite
Browse to Database – Test OK – Next

I get “Your project references the latest version of Entity Framework, 
however, an Entity Framework database provider compatible with this version 
could not be found for your data connection…”
“Next button is greyed out”

I’ve looked for setup logs but can’t seem to find any. (did C: search for 
Installer.exe.trace.*)

PS. When running the Test.exe from the “C:\Program Files (x86)
\System.Data.SQLite\2013\bin” the app loads but when I configure connection 
string to point to northwindEF.db I get 
“Could not load file or assembly ‘System.Data.SQLite, Version=1.0.93.0, 
Culture=neutral, PublicKey Token=db937bc2d44ff139’ or one of its 
dependencies. An attempt was made to load a program with the incorrect 
format.”

Help :-)...





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

Re: [sqlite] Basic SQLite/EF6 question

[Steven Davisworth]

Joe Mistachkin  writes:

> 
> 
> Steven Davisworth wrote:
> > 
> > I've just upgraded PC to 64bit (new PC) and installed VS2013. I've
> followed 
> > standard install instructions as outlined in web posts I've come across
> for 
> > EF6. I've used Syatem.Data.SQLite.EF6 1.0.93.0   Windows (.NET 
Framework 
> > 4.5.1) and seem to be getting the same sorts of errors listed above.
> > 
> 
> What are the specific errors?  I did not see them when I did the testing 
of
> this scenario.  Can you provide the System.Data.SQLite setup package logs?
> 
> The log files should be located in the %TEMP% directory on the machine.
> They
> will have names similar to "Setup Log 2012-12-18 #001.txt" and
> "Installer.exe.trace.tmp29.log".
> 
> >
> > All posts I've come across seem to have the same problem. A heap load 
of 
> > suggestions that don't work.
> >
> 
> I've seen some of those same posts as well.  However, it's important to 
note
> that people may not always report when they succeed at making it 
work...  :)
> 
> > 
> > I've tried the GACing thing as well (contrary to what some posts say) Is
> > this a common issue and will it be addressed soon? (Hope my question is
> not
> > redundant but as I said loads of advice out there but non seem to work)
> > 
> 
> In the official documentation for Entity Framework 6, it is unclear to me
> whether or not their providers need to be registered machine-wide.  
Clearly,
> the design-time components in Visual Studio need various kinds of
> machine-wide
> configuration, hence the confusion here (i.e. especially since the
> components
> are in completely different assemblies).
> 
> --
> Joe Mistachkin
> 
Hi Joe
Thanks for your response.
Installs
sqlite-netFx451-setup-bundle-x86-2013-1.0.93.0
Start VS 2013 and open project
NuGet Package Manager - System.Data.SQLite.EF6 (x86/x64)
 included install - EF 6.1.0
   System.Data.SQLite.Core (x86/x64)
Rebuild done
Add New – Data – ADO.NET Entity Data Model
Choose Model Contents – EF Designer from database
New Connection – Data Provider is .NET Framework Data Provider for SQLite
Browse to Database – Test OK – Next

I get “Your project references the latest version of Entity Framework, 
however, an Entity Framework database provider compatible with this version 
could not be found for your data connection…”
“Next button is greyed out”

I’ve looked for setup logs but can’t seem to find any. (did C: search for 
Installer.exe.trace.*)

PS. When running the Test.exe from the “C:\Program Files (x86)
\System.Data.SQLite\2013\bin” the app loads but when I configure connection 
string to point to northwindEF.db I get 
“Could not load file or assembly ‘System.Data.SQLite, Version=1.0.93.0, 
Culture=neutral, PublicKey Token=db937bc2d44ff139’ or one of its 
dependencies. An attempt was made to load a program with the incorrect 
format.”

Help :-)...





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

Re: [sqlite] Brief intro to SQLite in Python

[Keith Medcalf]

>A Python programmer was doing a lot of data processing in Python and
>wondered whether SQLite could speed it up:
>
>
>
>The article and the database needs of the programmer are rather simple,
>and the results are unremarkable (fast DBMS is fast).  But the examples
>of SQLite-via-Python code are clear and well written and may be useful
>for Python users who want to learn SQLite.  Especially for the triple-
>quoting needed for .execute().

Triple quoting is not required for .execute().  A Triple-Quote is a way of 
delimiting a string such that escape characters are not required to embed 
characters with semantic meaning within the string (ie, quote characters or 
line-ending characters). 




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

Re: [sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[Simon Slavin]

On 13 Jul 2014, at 9:45pm, Clemens Ladisch  wrote:

> But these particular column names do not look as if they were anything
> but as bug:
> 
> sqlite> select "TestView"."id", "TestView"."data2" from TestView;
> TestViewTestView
> --  --
> 1   Miranda

I understand your problem with two identical names, but it's not a bug in 
SQLite.  SQLite does not make any promise about column names unless you use AS. 
 This includes having two columns with identical names.

It's also not 'wrong' with regard to the SQL specification.  SQL does not 
define any commands which retrieve the column names of a table, or the column 
names of the result of a SELECT, so it doesn't define anything about the names 
produced.

Also, a note about delimited identifiers.  SQLite supports them correctly.  
Almost nobody does these days, preferring to use only letters, digits and the 
underline character in identifiers.  It's worth noting, however, that 
implementation of delimited identifiers differs in different implementations of 
SQL.  For example, for the command

CREATE TABLE "TestTable" ...

some implementations of SQL consider the table name to be TestTable and will 
recognise future references to it without the quotes, and others consider the 
table name to be "TestTable" and recognise it only with the quotes.  Again, 
since SQL does not define any way to retrieve identifier names, neither 
behaviour can be considered buggy.

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

Re: [sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[RSmith]

On 2014/07/13 22:45, Clemens Ladisch wrote:

RSmith wrote:

On 2014/07/11 17:26, Bruce Cowan wrote:

When you select columns from a view, the (ADO.NET) DbDataReader that is
returned from the execute call does not contain sensible column names

There is no contract of which column names should be returned, no
"incorrect" headers and no guarantee, and no obligation from the
standard or any other requirement.

But these particular column names do not look as if they were anything
but as bug:

sqlite> select "TestView"."id", "TestView"."data2" from TestView;
TestViewTestView
--  --
1   Miranda


Hi Clemens,

I'm not sure what you mean. I think you are saying that the specific case feels like it is buggy and the SQL engine should rather 
return the second part of the column name as it will in some other circumstances?  If this is what you are saying, I can agree, it 
would be nice if it behaves the same.


There is an important difference between "would be nice if..." and "it IS a bug" though.  It is NOT a bug, it is not documented to 
behave differently, nor required to. Making it behave the same would be nice, but it can't be a requirement, and much more 
importantly, any system that you develop or use MUST necessarily NOT expect it to work that specific way for future-proofing or just 
in general compliance.


So if we want a ticket to ask an adjustment of this quirk, I don't see why not, 
would be nice - but the OP stated:

"This problem makes SQLite views completely unusable with ServiceStack.OrmLite 
version 4.0.23 or newer"


which sounds a lot like it is perceived to be an SQLite problem, but it is not an SQLite problem, it is an OrmLite v4.0.23 problem 
(which might be "helped" by the above fix, but should not ever be expected to work that way). In this regard I would urge the 
OrmLite people foremost to adjust their system to be standard and compliant, and as an aside maybe patch the SQLite quirk as a 
matter of luxury, not necessity.


I might be a bit over-jittery too, but many years finding problems in systems has made me allergic to the expectation of behaviour 
that is not explicitly documented, I almost prefer it to work wrong so it highlights the issue during the dev cycle already. I only 
ever use strict mode SQL in other engines, and others have been asking on this forum for a "Strict" mode in SQLite, which I think 
would be even more useful than fixing a quirk to comply to a non-required non-documented behaviour.


But I'll roll with whatever induces euphoria :)

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

[sqlite] Brief intro to SQLite in Python

[Simon Slavin]

A Python programmer was doing a lot of data processing in Python and wondered 
whether SQLite could speed it up:



The article and the database needs of the programmer are rather simple, and the 
results are unremarkable (fast DBMS is fast).  But the examples of 
SQLite-via-Python code are clear and well written and may be useful for Python 
users who want to learn SQLite.  Especially for the triple-quoting needed for 
.execute().

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

Re: [sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[Clemens Ladisch]

RSmith wrote:
> On 2014/07/11 17:26, Bruce Cowan wrote:
>> When you select columns from a view, the (ADO.NET) DbDataReader that is
>> returned from the execute call does not contain sensible column names
>
> There is no contract of which column names should be returned, no
> "incorrect" headers and no guarantee, and no obligation from the
> standard or any other requirement.

But these particular column names do not look as if they were anything
but as bug:

sqlite> select "TestView"."id", "TestView"."data2" from TestView;
TestViewTestView
--  --
1   Miranda


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

Re: [sqlite] capturing and testing a hot journal

[Simon Slavin]

On 12 Jul 2014, at 9:37am, Charles Parnot  wrote:

> - the journal file is actually not “hot” and I misunderstood the conditions 
> that make it hot

That one.  The files on disk aren't 'hot' (as I think you mean it) while you're 
in a transaction.

Your file system is not pushing journal changes at the file level.  It doesn't 
need to do that while a transaction is open. since while the transaction is 
open, the database is locked so nothing else can use it anyway, and if your app 
crashes the whole transaction will be ignored.

SQLite could be written to push transactions to the journal file on each 
change, but that would involve lots of writing to disk, so it would make SQLite 
slower, and for no gain.

> [snip] The test case I am generating is just for a simple edge case of our 
> Dropbox-based syncing


Yes, DropBox can be a problem for open SQLite databases.  As a file level 
duplication system which does not understand locks, there's no good way to make 
DropBox work with open SQLite databases, or as a mediator for concurrent 
multi-user changes to a database.  I had to explain to some users that a 
database change is not 'safe' until the database is closed.

One thing that's worth testing is to make sure that recovery after crashes 
always yields a database with either pre- or post-transaction data rather than 
something corrupt which can't be opened.  I don't know much about how DropBox 
works.  Could it perhaps end up with a database file from one computer but 
journal file from another ?

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

Re: [sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[Simon Slavin]

On 13 Jul 2014, at 2:34pm, RSmith  wrote:

> If you need the returned names to be exactly something specific, then you 
> need to use the "AS" directive.

You can even put the 'AS' into the VIEW:

sqlite> .headers ON
sqlite> .mode columns
sqlite> CREATE TABLE TestA (one TEXT, two TEXT);
sqlite> INSERT INTO TestA VALUES ('a','b');
sqlite> INSERT INTO TestA VALUES ('c','d');  
sqlite> SELECT * FROM TestA;
one two   
--  --
a   b 
c   d 
sqlite> CREATE VIEW TestB AS SELECT * FROM TestA;
sqlite> SELECT * FROM TestB;
one two   
--  --
a   b 
c   d 
sqlite> CREATE VIEW TestC AS SELECT one AS oneone, two AS twotwo FROM TestA;
sqlite> SELECT * FROM TestC;
oneone  twotwo
--  --
a   b 
c   d
sqlite> CREATE TABLE TestD AS SELECT * FROM TestC;
sqlite> SELECT * FROM TestD;
oneone  twotwo
--  --
a   b 
c   d 

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

Re: [sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[RSmith]

On 2014/07/11 17:26, Bruce Cowan wrote:

When you select columns from a view, the (ADO.NET) DbDataReader that is 
returned from the execute call does not contain sensible column names//

...//This problem makes SQLite views completely unusable with ServiceStack.OrmLite version 4.0.23 
or newer since it always uses the "view-or-table-name"."column-name" format for 
select statements.


There is no contract of which column names should be returned, no "incorrect" headers and no guarantee, and no obligation from the 
standard or any other requirement. If you need the returned names to be exactly something specific, then you need to use the "AS" 
directive. I find it hard to imagine that ServiceStack.OrmLite does not know this - but it is possible.  This question would 
probably get more useful replies on an OrmLite forum.


Anyone else here using OrmLite with some success?



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

Re: [sqlite] sqlite3_open() returns SQLITE_FULL

[RSmith]

Probably because the UAC is not allowing changes to files in the folder you are using. Maybe a protected folder, such as a 
sub-folder of \Program Files\ or \System\? Place the file in a My Documents\ or some other place where access is unrestricted.


If this works you know what the problem is, if you still need the file to work from a protected location, then you will have to 
assign rights to it or log in as admin.


On 2014/07/11 05:38, 徐刚辉 wrote:

OS: Windows XP
Disk not full.  When logged in as Administrator, sqlite3_open() succeeds. When 
logged in as another account, sqlite3_open() failed with error

SQLITE_FULL.
___
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] Hello, I would like to know the difference between sqlite2 and sqlite3!

[Kees Nuyt]

Oops, I should have linked to the official site, not my local copy.
Corrected below.

===

> Hello, I would like to know the difference
> between sqlite2 and sqlite3!?

sqlite2 is deprecated since 2004, not maintained since 2005 and should
only be used to convert legacy sqlite2 databases to sqlite3.

sqlite3 is current and actively maintained / optimized.

Differences (as perceived almost 10 years ago):
http://www.sqlite.org/version3.html

Release history:
http://www.sqlite.org/changes.html

Hope this helps.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Hello, I would like to know the difference between sqlite2 and sqlite3!

[Kees Nuyt]

On Sun, 13 Jul 2014 07:23:08 +0800, "ddy is super man"
 wrote:

> Hello, I would like to know the difference
> between sqlite2 and sqlite3!?

sqlite2 is deprecated since 2004, not maintained since 2005 and should
only be used to convert legacy sqlite2 databases to sqlite3.

sqlite3 is current and actively maintained / optimized.

Differences (as perceived almost 10 years ago):
http://knuyt.demon.nl/sqlite.org/version3.html

Release history:
http://knuyt.demon.nl/sqlite.org/changes.html

Hope this helps.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] capturing and testing a hot journal

[Richard Hipp]

On Sat, Jul 12, 2014 at 4:37 AM, Charles Parnot 
wrote:

> Hi all,
>
> For testing purposes of our application (a Mac app), I am generating what
> I thought would be a database with a “hot” journal using this approach (on
> an existing database):
>
> - open the database (and PRAGMA journal_mode = TRUNCATE;)
> - open a transaction: BEGIN IMMEDIATE TRANSACTION;
> - add some rows: INSERT etc…
> - **make a copy of the db and journal files** (while still hot?)
>

Normally you need to either (1) reduce the page cache size using "PRAGMA
cache_size=5" or else (2) do a VERY large transaction in order to get
SQLite to spill content to disk in order to get a hot journal using the
technique above.



> - close the transaction
>
> Then I open the copied database+journal (naming the files appropriately),
> again in TRUNCATE journal mode. As expected, the content of the database
> does not include the inserted rows. However, the journal file is not
> emptied, even after closing the database. Based on the documentation (
> http://www.sqlite.org/lockingv3.html#hot_journals), I would have expected
> the journal file to be emptied because it is “hot”.
>
> There are 2 options here:
>
> - the journal file is actually not “hot” and I misunderstood the
> conditions that make it hot
> - there is a bug in SQLite
>
> Obviously, I strongly suspect I am misunderstanding things, and don’t
> think it is an SQLite bug. Despite intensive Google-ing and more testing, I
> am not sure what makes the journal non-hot.
>
> Thanks for your help!
>
> Charles
>
>
> NB: You might be wondering why I am doing the above. I realize SQLite has
> already much more advanced tests for “hot” db+journals (running custom
> versions of filesystems to generate all kind of edge cases). The test case
> I am generating is just for a simple edge case of our Dropbox-based syncing
> (see: https://github.com/cparnot/PARStore and
> http://mjtsai.com/blog/2014/05/21/findings-1-0-and-parstore/). For a
> given database file, there is only one device that can write to it, all
> other devices being read-only (not in terms of filesystem, but
> sqlite-wise). But it is possible that Dropbox will copy a database and
> journal files that are not consistent with each other, which can create
> problems. For instance, maybe a read-only device could try to open the
> (still old) database with a new non-empty journal file and sqlite would
> empty that journal file, then Dropbox could in turn empty the journal file
> before the writer client had finished the transaction. I am not (yet) going
> to test for and try to protect against more complicated (and rarer) edge
> cases where the database is in the middle of writing a transaction (which I
> suspect will only happen in case of crashes, not because of Dropbox, in
> which case the recovery of the database by the read-only client would
> actually be beneficial).
>
> --
> Charles Parnot
> charles.par...@gmail.com
> http://app.net/cparnot
> twitter: @cparnot
>
> Your Lab Notebook, Reinvented.
> http://findingsapp.com
>
> ___
> 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] capturing and testing a hot journal

[Charles Parnot]

Hi all,

For testing purposes of our application (a Mac app), I am generating what I 
thought would be a database with a “hot” journal using this approach (on an 
existing database):

- open the database (and PRAGMA journal_mode = TRUNCATE;)
- open a transaction: BEGIN IMMEDIATE TRANSACTION;
- add some rows: INSERT etc…
- **make a copy of the db and journal files** (while still hot?)
- close the transaction

Then I open the copied database+journal (naming the files appropriately), again 
in TRUNCATE journal mode. As expected, the content of the database does not 
include the inserted rows. However, the journal file is not emptied, even after 
closing the database. Based on the documentation 
(http://www.sqlite.org/lockingv3.html#hot_journals), I would have expected the 
journal file to be emptied because it is “hot”.

There are 2 options here:

- the journal file is actually not “hot” and I misunderstood the conditions 
that make it hot
- there is a bug in SQLite

Obviously, I strongly suspect I am misunderstanding things, and don’t think it 
is an SQLite bug. Despite intensive Google-ing and more testing, I am not sure 
what makes the journal non-hot.

Thanks for your help!

Charles


NB: You might be wondering why I am doing the above. I realize SQLite has 
already much more advanced tests for “hot” db+journals (running custom versions 
of filesystems to generate all kind of edge cases). The test case I am 
generating is just for a simple edge case of our Dropbox-based syncing (see: 
https://github.com/cparnot/PARStore and 
http://mjtsai.com/blog/2014/05/21/findings-1-0-and-parstore/). For a given 
database file, there is only one device that can write to it, all other devices 
being read-only (not in terms of filesystem, but sqlite-wise). But it is 
possible that Dropbox will copy a database and journal files that are not 
consistent with each other, which can create problems. For instance, maybe a 
read-only device could try to open the (still old) database with a new 
non-empty journal file and sqlite would empty that journal file, then Dropbox 
could in turn empty the journal file before the writer client had finished the 
transaction. I am not (yet) going to test for and try to protect against more 
complicated (and rarer) edge cases where the database is in the middle of 
writing a transaction (which I suspect will only happen in case of crashes, not 
because of Dropbox, in which case the recovery of the database by the read-only 
client would actually be beneficial).

--
Charles Parnot
charles.par...@gmail.com
http://app.net/cparnot
twitter: @cparnot

Your Lab Notebook, Reinvented.
http://findingsapp.com

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

[sqlite] Selecting from view gives bad column information when view name is used in quotes in select statement

[Bruce Cowan]

When you select columns from a view, the (ADO.NET) DbDataReader that is 
returned from the execute call does not contain sensible column names when the 
select statement specifies "view-name"."column-name" or 
[view-name].[column-name]. Instead of column names, it just contains the view 
name for each column. This problem does not occur when selecting from a table, 
nor does it occur when the quotes are omitted. This problem is also visible 
with the command line sqlite3 program - it displays incorrect headers. This 
leads me to suspect that the problem is in SQLite core, not in the ADO.NET 
provider.
The following script demonstrates the issue in the sqlite3 command-line shell.

create table TestTable1 (id integer primary key autoincrement, data 
varchar(200) not null);
create table TestTable2 (id integer primary key autoincrement, data 
varchar(200) not null);
create view TestView as select t1.id, t1.data as data1, t2.data as data2 from 
TestTable1 t1 inner join TestTable2 t2 on t1.id = t2.id;
insert into TestTable1 (data) values ('Fred');
insert into TestTable2 (data) values ('Miranda');
.headers on
select id, data2 from TestView;
select TestView.id, TestView.data2 from TestView;
select "TestView"."id", "TestView"."data2" from TestView;
select [TestView].[id], [TestView].[data2] from TestView;
select id, data from TestTable1;
select TestTable1.id, TestTable1.data from TestTable1;
select "TestTable1"."id", "TestTable1"."data" from TestTable1;
select [TestTable1].[id], [TestTable1].[data] from TestTable1;

If you look at the headers output from the 8 select statements you will see 
that the headers contain only the view name for each column in the case of the 
3rd and 4th statements.

This problem makes SQLite views completely unusable with ServiceStack.OrmLite 
version 4.0.23 or newer since it always uses the 
"view-or-table-name"."column-name" format for select statements.

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

Re: [sqlite] trying to get things to work from the command line on windows 7 x64

[Jonathan Leslie]

Jan, 

Yes, thank you I see.  the download of the x64 mingw is failing behind my proxy 
here.  the installer wants to phone home for some repository tool.  is there 
anyway around that?

          OR

can I use the 32-bit sqlite?   





On Thursday, July 10, 2014 7:47 PM, Jonathan Leslie  
wrote:
 

>
>
>Jan, 
>
>
>Yes, thank you I see.  the download of the x64 mingw is failing behind my 
>proxy here.  the installer wants to phone home for some repository tool.  is 
>there anyway around that?
>
>
>          OR
>
>
>can I use the 32-bit sqlite?   
>
>
>
>
>
>
>
>On Thursday, July 10, 2014 5:14 PM, Jan Nijtmans  
>wrote:
> 
>
>>
>>
>>2014-07-10 22:17 GMT+02:00 Jonathan Leslie :
>>
>>> question 1) what am I doing wrong?
>>
>>Your compiler is 32-bit MinGW, but you unpacked the
>>64-bit dll in your current directory.
>>
>>Regards,
>>       Jan Nijtmans
>>___
>>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] Hello, I would like to know the difference between sqlite2 and sqlite3!

[ddy is super man]

Hello, I would like to know the difference between sqlite2 and sqlite3!‍
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_open() returns SQLITE_FULL

[徐刚辉]

OS: Windows XP
Disk not full.  When logged in as Administrator, sqlite3_open() succeeds. When 
logged in as another account, sqlite3_open() failed with error

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

Re: [sqlite] trying to get things to work from the command line on windows 7 x64

[Jonathan Leslie]

Jan, 

Yes, thank you I see.  the download of the x64 mingw is failing behind my proxy 
here.  the installer wants to phone home for some repository tool.  is there 
anyway around that?

          OR

can I use the 32-bit sqlite?   




On Thursday, July 10, 2014 5:14 PM, Jan Nijtmans  wrote:
 

>
>
>2014-07-10 22:17 GMT+02:00 Jonathan Leslie :
>
>> question 1) what am I doing wrong?
>
>Your compiler is 32-bit MinGW, but you unpacked the
>64-bit dll in your current directory.
>
>Regards,
>       Jan Nijtmans
>___
>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