Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Bernd Lehmkuhl]

Thank you so much for your reply. I sill surely look at the options you 
gave me.



I ask because I run into errors a lot, using the System.Data.SQLite 
libraries. It easily crashes, trhows exceptions and similar issues... I 
found out that it matters which target platform is selected...



So thanks once again.


Regards, Jordy



I dare claim that you're doing something wrong using System.Data.SQLite.
I've been using this library extensively over the past seven or eight 
years - starting when it wasn't yet maintained by the SQLite team - and 
never had any severe problems. Several gotchas for sure, but certainly 
not enough to find it unreliable.
That said I've only coded for windows desktop, so should you be 
struggling with Win CE or the kind, I apologize for making my first 
claim, as I can't say anything about the library's stability on these 
platforms.
As always - examples where your code crashes are a valuable resource to 
giving advice what might be done differently (if there is something done 
wrong).


Best regards, Bernd

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

Re: [sqlite] lint CLI command

[Bernd Lehmkuhl]

Am 14.08.2018 um 20:42 schrieb Bernd Lehmkuhl:

Good day altogether,

I suppose there might be a problem with reporting missing indexes on 
foreign key columns in conjunction with the without rowid clause:


C:\Users\Bernd>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t_parent (id text PRIMARY KEY NOT NULL);
sqlite> CREATE TABLE t_child_standard (id text PRIMARY KEY NOT NULL 
REFERENCES t_parent (id) ON DELETE CASCADE, somethingelse text);
sqlite> CREATE TABLE t_child_wr (id text PRIMARY KEY NOT NULL REFERENCES 
t_parent (id) ON DELETE CASCADE, somethingelse text) WITHOUT ROWID;

sqlite> .lint fkey-indexes
CREATE INDEX 't_child_wr_id' ON 't_child_wr'('id'); --> t_parent(id)
sqlite> .q

C:\Users\Bernd>

It looks like the primary key isn't realized as such when the without 
rowid clause is used.


Thanks, Bernd




Moreover, it'd be a bit more comfortable if the names of the index, 
table and column of the resulting sql weren't put in single quotation 
marks, but that's not so important.
But I consider it a bug - even though I know I have to be careful with 
this word - because in both cases the primary keys are recognized as such:

sqlite> pragma index_list(t_child_standard);
0|sqlite_autoindex_t_child_standard_1|1|pk|0
sqlite> pragma index_list(t_child_wr);
0|sqlite_autoindex_t_child_wr_1|1|pk|0
sqlite>

Oh - just saw this in the change logs:
3.22.0  
(10) Improvements to the command-line shell:
...
5. Enhance the ".lint fkey-indexes" command so that it works with 
WITHOUT ROWID tables.

...

Regression? Nope. Same output in 3.22.0 ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] lint CLI command

[Bernd Lehmkuhl]

Good day altogether,

I suppose there might be a problem with reporting missing indexes on 
foreign key columns in conjunction with the without rowid clause:


C:\Users\Bernd>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t_parent (id text PRIMARY KEY NOT NULL);
sqlite> CREATE TABLE t_child_standard (id text PRIMARY KEY NOT NULL 
REFERENCES t_parent (id) ON DELETE CASCADE, somethingelse text);
sqlite> CREATE TABLE t_child_wr (id text PRIMARY KEY NOT NULL REFERENCES 
t_parent (id) ON DELETE CASCADE, somethingelse text) WITHOUT ROWID;

sqlite> .lint fkey-indexes
CREATE INDEX 't_child_wr_id' ON 't_child_wr'('id'); --> t_parent(id)
sqlite> .q

C:\Users\Bernd>

It looks like the primary key isn't realized as such when the without 
rowid clause is used.


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

Re: [sqlite] Bug report: Wrong column name in a table in a certain case

[Bernd Lehmkuhl]

Most probably not a bug. I asked something similar a while ago. It's as 
easy as https://sqlite.org/faq.html#q28 .
As long as you don't explicitly assign an alias to a column name, sqlite 
is not guaranteed to return what you might expect.



Am 11.08.2017 um 22:52 schrieb Jürgen Palm:

Hi,

please have a look at the following sequence of statements executed on 
Windows 10 with sqlite3.exe, version 3.19.3 and 3.20.0:


CREATE TABLE test("column with space" TEXT);
CREATE TABLE test2 AS SELECT "column with space" FROM test;
CREATE TABLE test3 AS SELECT "column with space" FROM test GROUP BY 1;
CREATE TABLE test4 AS SELECT "column with space" AS "column with space" 
FROM test GROUP BY 1;

SELECT * FROM sqlite_master;
table|test|test|2|CREATE TABLE test("column with space" TEXT)
table|test2|test2|3|CREATE TABLE test2("column with space" TEXT)
table|test3|test3|4|CREATE TABLE test3("""column with space""" TEXT)
table|test4|test4|5|CREATE TABLE test4("column with space" TEXT)

As you can see the table "test3" has extra double quotes around the 
column name "column with space". There should be no extra double quotes 
for this table and table "test3" should look like the tables "test2" and 
"test4".


Regards,
Jürgen
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] constraint failed message and no clue what went wrong

[Bernd Lehmkuhl]

> Dominique Devienne  hat am 23. Mai 2016 um 13:42 
> geschrieben:
> 
> 
> On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl  mailbox.org
> > wrote:
> >
> > > Dominique Devienne  hat am 23. Mai 2016 um 11:20
> > geschrieben:
> > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <
> > bernd.lehmkuhl at mailbox.org
> > > > [...] What might cause a "constraint failed" message following
> > this command: [...]
> > >
> > > Which version of SQLite? More recent ones tell you which constraint
> > failed,
> > > when they are named, which yours are (a good thing IMHO).
> >
> > Most recent one - 3.12.2. Unfortunately without any name.
> > [...]
> 
> 
> /*** t_geometrie_index ***/
> CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
>   id,
> [...]
> 
> sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
> >...> SELECT
> >...>   t.auto_id, [...]
> 
>...> FROM
> >...>   (
> >...> SELECT
> >...>   k.id, [...]
> >...> FROM
> >...>   t_geometrie_knoten k
> >...>
> >...> UNION ALL <<<<<<
> >...>
> >...> SELECT
> >...>   p.id, [...]
> >...> FROM
> >...>   t_geometrie_punkte p
> >...> GROUP BY
> >...>   p.id
> >...>   ) sub JOIN t_geometrie_typ t
> >...> ON   sub.id = t.id;
> > Error: constraint failed
> > sqlite> rollback;
> > sqlite> .quit
> >
> 
> OK, was worth a shot. I had a feeling it might be related to the RTREE
> vtables.
> 
> Never used RTREE myself, in SQLite, although I know what this is.
> Could it be your you "knoten" and "punkte" tables have values with the same
> IDs?

Gotcha! Thanks. Even though I claimed having checked that auto_id is unique in 
that query, you just proofed me wrong: 

SELECT 
  auto_id, 
  COUNT(*) 
FROM 
  ( 
  SELECT
t.auto_id,
sub.xmin,
sub.xmax,
sub.ymin,
sub.ymax
  FROM
(
  SELECT
  k.id,
  k.x AS xmin,
  k.x AS xmax,
  k.y AS ymin,
  k.y AS ymax
FROM
  t_geometrie_knoten k

UNION ALL

SELECT
  p.id,
  MIN(p.x),
  MAX(p.x),
  MIN(p.y),
  MAX(p.y)
FROM
  t_geometrie_punkte p
GROUP BY
  p.id
) sub JOIN t_geometrie_typ t
  ON   sub.id = t.id 
) 
GROUP BY 
  auto_id 
HAVING 
  COUNT(*) > 1 

gives me one auto_id with three occurrences. I should check my data more 
thoroughly, I guess.
Thanks for saving my day :-)

[sqlite] constraint failed message and no clue what went wrong

[Bernd Lehmkuhl]

> Dominique Devienne  hat am 23. Mai 2016 um 11:20 
> geschrieben:
> 
> 
> On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl  mailbox.org
> > wrote:
> 
> > [...] What might cause a "constraint failed" message following this
> > command: [...]
> >
> 
> Which version of SQLite? More recent ones tell you which constraint failed,
> when they are named, which yours are (a good thing IMHO).

Most recent one - 3.12.2. Unfortunately without any name.

Microsoft Windows [Version 10.0.10586]
(c) 2015 Microsoft Corporation. Alle Rechte vorbehalten.

D:\Programmierung\NAS\Diverse Echtdaten\Schortens\2016-03-31>sqlite3 
000-0-katalog-1-3730-17851-2016-03-31-08-44-03__postnas_grafik.db3
SQLite version 3.12.2 2016-04-18 17:30:31
Enter ".help" for usage hints.
sqlite> begin;
sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
   ...> SELECT
   ...>   t.auto_id,
   ...>   sub.xmin,
   ...>   sub.xmax,
   ...>   sub.ymin,
   ...>   sub.ymax
   ...> FROM
   ...>   (
   ...> SELECT
   ...>   k.id,
   ...>   k.x AS xmin,
   ...>   k.x AS xmax,
   ...>   k.y AS ymin,
   ...>   k.y AS ymax
   ...> FROM
   ...>   t_geometrie_knoten k
   ...>
   ...> UNION ALL
   ...>
   ...> SELECT
   ...>   p.id,
   ...>   MIN(p.x),
   ...>   MAX(p.x),
   ...>   MIN(p.y),
   ...>   MAX(p.y)
   ...> FROM
   ...>   t_geometrie_punkte p
   ...> GROUP BY
   ...>   p.id
   ...>   ) sub JOIN t_geometrie_typ t
   ...> ON   sub.id = t.id;
Error: constraint failed
sqlite> rollback;
sqlite> .quit

> 
> So using a newer version might help your troubleshooting. --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] constraint failed message and no clue what went wrong

[Bernd Lehmkuhl]

Dear list, 
having the following database schema: 

/*** t_geometrie_typ ***/
CREATE TABLE t_geometrie_typ(
  auto_id INTEGER PRIMARY KEY AUTOINCREMENT, -- automatically generated id as 
link to the r*Tree index
  id TEXT UNIQUE NOT NULL, 
  typ TEXT COLLATE NOCASE NOT NULL, 
  objektart TEXT NOT NULL, 
  crs TEXT, 
  qualitaetsangabenDatenerhebung TEXT);

CREATE INDEX i_geometrie_typ_crs 
ON t_geometrie_typ (crs, id);

CREATE INDEX i_geometrie_typ_objektart 
ON t_geometrie_typ (objektart, id); 

/*** t_geometrie_umring ***/
CREATE TABLE t_geometrie_umring(
  id TEXT, 
  umringnummer INTEGER NOT NULL, 
  typ TEXT NOT NULL COLLATE NOCASE, 
  CONSTRAINT fk_geometrie_umring_id FOREIGN KEY (id) 
  REFERENCES t_geometrie_typ (id) ON DELETE CASCADE, 
  CONSTRAINT c_geometrie_umring_typ CHECK (typ IN ('Exterior', 'Interior')), 
  CONSTRAINT u_geometrie_umring UNIQUE (id, umringnummer)); 

/*** t_geometrie_punkte ***/
CREATE TABLE t_geometrie_punkte(
  id TEXT, 
  x REAL NOT NULL, 
  y REAL NOT NULL, 
  bulge REAL NOT NULL, 
  umringnummer INTEGER, 
  laufendenummer INTEGER NOT NULL , 
  CONSTRAINT fk_geometrie_punkte_id FOREIGN KEY (id) 
  REFERENCES t_geometrie_typ (id) ON DELETE CASCADE, 
  CONSTRAINT u_geometrie_punkte UNIQUE (id, umringnummer, laufendenummer)); 

/*** t_geometrie_knoten ***/
CREATE TABLE t_geometrie_knoten(
  id TEXT NOT NULL, 
  x REAL NOT NULL, 
  y REAL NOT NULL, 
  CONSTRAINT pk_geometrie_knoten PRIMARY KEY (id, x, y), 
  CONSTRAINT fk_geometrie_knoten_id FOREIGN KEY (id) 
  REFERENCES t_geometrie_typ (id) ON DELETE CASCADE); 

/*** t_geometrie_index ***/
CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
  id, 
  xmin, 
  xmax, 
  ymin, 
  ymax); 

What might cause a "constraint failed" message following this command:

INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
SELECT 
  t.auto_id, 
  sub.xmin, 
  sub.xmax, 
  sub.ymin, 
  sub.ymax 
FROM 
  ( 
SELECT 
  k.id, 
  k.x AS xmin, 
  k.x AS xmax, 
  k.y AS ymin, 
  k.y AS ymax
FROM 
  t_geometrie_knoten k

UNION ALL 

SELECT 
  p.id, 
  MIN(p.x), 
  MAX(p.x), 
  MIN(p.y), 
  MAX(p.y) 
FROM 
  t_geometrie_punkte p 
GROUP BY 
  p.id
  ) sub JOIN t_geometrie_typ t 
ON   sub.id = t.id; 

I don't see any dependencies on the r*Tree table t_geometrie_index. 
Executing just the SELECT part of the query I checked that all xmin's are less 
or equal to the xmax's, the same holds true for the y-values. There are no 
double auto_id's. 
So what might cause the error message "constraint failed"?

Re: [sqlite] struggling with a query

[Bernd Lehmkuhl]

Am 08.02.2014 11:03, schrieb Stephan Beal:


i have table containing a mapping of logic dataset versions and filenames
contained in that dataset version:

CREATE TABLE v(vid,name);
INSERT INTO "v" VALUES(1,'foo');
INSERT INTO "v" VALUES(1,'bar');
INSERT INTO "v" VALUES(2,'bar');
INSERT INTO "v" VALUES(2,'baz');

i am trying like mad to, but can't seem formulate a query with 2 version
number inputs (1 and 2 in this case) and creates a result set with these
columns:

- name. must include all names across both versions
- status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
v1.

So the above data set should produce:

foo, -1
bar, 0
baz, 1



Should work as well:

SELECT
  name,
  CASE
WHEN minvid = maxvid AND minvid = 1
THEN -1
WHEN minvid = maxvid AND minvid = 2
THEN 1
ELSE 0
  END vid
FROM
  (
SELECT
  name,
  MIN(vid) AS minvid,
  MAX(vid) AS maxvid
FROM
  v
GROUP BY
  name
  )

Bernd

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

> On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl <be...@web.de> wrote:
> 
>> Am 15.07.2013 22:26, schrieb Simon Slavin:
>>>
>>> The following two statements do different things.
>>>
>>> INSERT INTO myTable VALUES (01)
>>> INSERT INTO myTable VALUES ('01')
>>>
>>> Can you tell what's being done in your setup ? Is there a way using your 
>>> API that you can stress that the value you're binding or inserting is text, 
>>> not a number ?
>>
>> As I use a parameterized query, I'm pretty certain that it should be '01' - 
>> the second case. Stepping through the code in VS Debugger also shows that 
>> DbType of that parameter is String and Value is '01'.
> 
> If the column in the table really is defined as TEXT, and the INSERT commands 
> do have apostrophes around the values, then SQLite3 should not be losing that 
> zero.
> 
> Can you open the database in some other tool (e.g. the sqlite3 command-line 
> tool, available from the SQLite site) and see what the table schema says ? 
> Your commands should be something like
> 
> sqlite3 myDatabaseFile
> .schema
> .quit
> 
> If you have lots of tables you can do ".schema mytable" instead of just 
> ".schema".
> 
> If you want to experiment you can manually type in an INSERT yourself, then 
> do a SELECT and see whether the zeros were preserved.
> 
> Simon.

Hi Simon, 

The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String, 
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String, 
IST_KATEGORIE_ESK_BETRIEBSSTOF String);

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select schluessel, typeof(schluessel)
   ...> from t_vwg_abfallverzeichnis
   ...> where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172', 
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite>

I *expected* to gain TEXT affinity through the use of System.Data.SQLite and 
it's strongly typed types (doppelt gemoppelt?), but apparently this is not 
totally effective. Maybe Joe Mistachkin can say something about that?

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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

Am 15.07.2013 22:30, schrieb Gerry Snyder:

On 7/15/2013 1:18 PM, Bernd wrote:

 I'm reading that text out of an Oracle-DB into a SQLite table
which has the affected column defined as 'String' - which maps to TEXT
in native SQLite


No. Look at section 2.1 of http://sqlite.org/datatype3.html   Only CHAR,
CLOB, or TEXT cause the column to have TEXT affinity.

HTH,

Gerry Snyder



If I understand correctly, this is right for the C API or rather SQL 
queries without any interpreting layer in between, but the .NET wrapper 
explicitly maps type String to affinity TEXT in SQLiteConvert.cs.


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

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

[Bernd Lehmkuhl]

Am 15.07.2013 22:26, schrieb Simon Slavin:


On 15 Jul 2013, at 9:18pm, Bernd  wrote:


I know that SQLite is inherently type-less, but I'm using System.Data.SQLite 
which tries it very best to force that into the common ADO.NET schema.
I'm having troubles with some text that has leading zeros, like '01', '02' and 
so on. I'm reading that text out of an Oracle-DB into a SQLite table which has 
the affected column defined as 'String' - which maps to TEXT in native SQLite - 
via a parameterized insert query and a parameter DbType of 'String' as well. 
Nonetheless the leading zero gets stripped off the text. Any ideas how I could 
preserve those leading zeros?


The following two statements do different things.

INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')

Can you tell what's being done in your setup ?  Is there a way using your API 
that you can stress that the value you're binding or inserting is text, not a 
number ?

Simon.


As I use a parameterized query, I'm pretty certain that it should be 
'01' - the second case. Stepping through the code in VS Debugger also 
shows that DbType of that parameter is String and Value is '01'.


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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Bernd Lehmkuhl]

This is not correct. It's up definitely up to date:

https://system.data.sqlite.org/downloads/1.0.86.0/sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.86.0.zip

Available at:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

"This binary package contains all the binaries for the PocketPC version 
of the System.Data.SQLite 1.0.86.0 (3.7.17) package. The .NET Compact 
Framework 3.5 is required."




Am 31.05.2013 13:28, schrieb Noel Frankinet:

and it's already on year behind the official c sqlite. I wouldn't go that
route


On 31 May 2013 13:26, Noel Frankinet  wrote:


oh, yes everything in c# then, let's see the performance on an arm cpu !


On 31 May 2013 13:23, Noel Frankinet  wrote:


ah, sorry, I will never understand why everybody wants to add a virtual
machine on an already feeble machine. Then you will have to deal wil a
extra layer of complexity ( you need a assembly that will translate from
managed code to unmanaged). You will probably have to compile it yourself,
since its probably an arm CPU. Good luck !


On 31 May 2013 13:16, Ali Dirik  wrote:


Thanks for answers.

I use c# (VS2008)
I am looking for c# sample source code.


2013/5/31 Noel Frankinet 


I've used C++, do you want to use anything else. It's of course easier

in

C++ since sqlite is in C. You link statically, nothing to install.


On 31 May 2013 12:21,  wrote:


This is the first shot from google
http://sqlite-wince.sourceforge.net/index.html

If you'd like to use sqlitecin your app, I do not think it will be an
issue for any playform once you have the proper C compiler. You may

try

to

statically compiling your application.

On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:


Hello Noel Frankinet,

Thank you for your answer.
I using a hand-held terminal (
http://www.ute.com/products_info.php?pc1=1=3=0=703)

with

windows

mobile operating system.
We use this device for counting.
There is already an application that works with SQLCE (Microsoft

SQL

Compact Edition)
I want to use SQLite instead of SQL CE.
I found a sample source code on the Internet (






http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application

)
I've tried it, but did not succeed source code.
Do you have any idea about this?

Best Regards
Ali Dirik


2013/5/31 Ali Dirik 


Dear Friends,

I want to develop a application that runs Windows CE or Mobile

devices.

I want to use the Sqlite database.
May I find same sample source codes?

Best Regards
Ali D
i
rik




--
İyi Çalışmalar
Ali DİRİK
___
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





--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
İyi Çalışmalar
Ali DİRİK
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
Noël Frankinet
Strategis sprl
0478/90.92.54





--
Noël Frankinet
Strategis sprl
0478/90.92.54







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

Re: [sqlite] System.Data.SQLite version for SQLite 3.7.16

[Bernd Lehmkuhl]

Am 25.03.2013 18:37, schrieb Nicolas Rivera:

Hi,

The latest version of System.Data.SQLite in the download page is
1.0.84.0, which appears to have been done for SQLite version 3.7.15.2.
Is that correct?

If so, is there a plan to update System.Data.SQLite with the latest
SQLite version?



https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki


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

Re: [sqlite] [SQLite.ADO.Net] Upgrading XP to SQLite version?

[Bernd Lehmkuhl]

Am 27.02.2013 15:28, schrieb Gilles Ganault:

On Tue, 26 Feb 2013 19:45:27 -0500, Kevin Benson
 wrote:

Right, but while the first DLL will be found since it's now part of
the project (Project > Add Reference), the wiki doesn't say that this
doesn't take care of the other DLL.



http://www.mail-archive.com/sqlite-users@sqlite.org/msg73039.html


Good to know, although creating an .\x86 or .\x64 doesn't seem to add
anything as compared to simply putting the two DLLs in the
application's directory.


Speaking of which, why is the no-bundle version (two DLLs) to be
prefered to the bundle version (single DLL)? It solves this issue.


I still didn't find any info on the web about what the difference is.

The wiki says: "All the "bundle" packages contain the
"System.Data.SQLite.dll" mixed-mode assembly. These packages should
only be used in cases where the assembly binary must be deployed to
the Global Assembly Cache for some reason (e.g. to support some legacy
application on customer machines). "

Anyone knows more?

Thank you.


I asked Joe Mistachkin a while back the same. Here's what he replied:

Am 21.09.2012 00:35, schrieb Joe Mistachkin:

Bernd wrote:


Could you elaborate on why I should avoid doing as I do at the moment?



The mixed-mode assembly contains native code for one platform as well as
the managed code.  This prevents the same application from being easily
deployed on both x86 and x64.

I suppose if you only ever deal with one processor architecture on all the
target machines, it's just fine to use the bundled packages.

Also, mixed-mode assemblies are not supported by Mono or the .NET Compact
Framework.

--
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] MIN() for a timedelta?

[Bernd Lehmkuhl]

Am 26.07.2012 23:32, schrieb C M:

I have string representations of a Python timedelta stored in an
SQLite database of the form H:MM:SS:ss (the last is microseconds).
  Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'10:01:23:041000'

I want to select the shortest duration (the smallest timedelta) using
the SQLite MIN(), like so:

SELECT MIN(duration) FROM Durations

The problem is, in Python, the string representation of the timedelta
is not left zero padded, so '9:00:00.00' (nine hours) is selected
by MIN() as greater than '10:01:23:041000' (ten hours and change).
This is not right in terms of time, as 9 hours is smaller than 10
hours.

I could zero pad these strings myself, so that '9:00:00.00'
becomes '09:00:00.00', but that would break other uses of these
values in my code and was wondering if there were a way in SQlite to
"see" these values as timedeltas.  I tried this:

SELECT MIN(TIME(duration) FROM Durations

but that returns nothing.

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


This seems to work:

C:\Users\Bernd>sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (duration);
sqlite>
sqlite> insert into t values ('9:34:15:00');
sqlite> insert into t values ('19:22:44:003500');
sqlite> insert into t values ('6:17:29:12');
sqlite> insert into t values ('22:18:00:937500');
sqlite> insert into t values ('0:02:11:00');
sqlite>
sqlite> select min(duration) from t;
0:02:11:00
sqlite> select max(duration) from t;
9:34:15:00
sqlite> select min(case when length(duration) = 15  then duration else 
'0' || du

ration end) from t;
00:02:11:00
sqlite> select max(case when length(duration) = 15  then duration else 
'0' || du

ration end) from t;
22:18:00:937500
sqlite> .q

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

Re: [sqlite] System.Data.SQLite version 1.0.80.0 released

[Bernd Lehmkuhl]

Here is a quick example that copies a small database from memory to disk:

using System.Data.SQLite;

namespace BackupAPI
{
   class Program
   {
 public static void BackupAndGetData()
 {
   using (SQLiteConnection source = new SQLiteConnection(
   "Data Source=:memory:"))
   {
 source.Open();

 using (SQLiteCommand command = new SQLiteCommand())
 {
   command.CommandText =
 "CREATE TABLE t1(x TEXT); " +
 "INSERT INTO t1 (x) VALUES('123456789');";

   command.Connection = source;
   command.ExecuteNonQuery();
 }

 using (SQLiteConnection destination = new SQLiteConnection(
 "Data Source=test.db"))
 {
   destination.Open();

   source.BackupDatabase(destination, "main", "main",
   -1, null, 0);
 }
   }
 }

 static void Main(string[] args)
 {
   BackupAndGetData();
 }
   }
}

--
Joe Mistachkin



Okay, that's easy. I didn't realize that it's a method of the Connection 
object. Thanks for providing the snippet.

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

Re: [sqlite] Multi-column unique constraint in SQLite

[Bernd Lehmkuhl]

Am 11.09.2011 13:42, schrieb liviodl:


Hi guys,

I'm trying to create a multi-column unique constraint in SQLite, but I don't
have success. In table "players", I've created the following index:

 CREATE UNIQUE INDEX "players_unique" ON "players" ("id" ASC, "skill"
ASC, "stagione" ASC, "settimana" ASC)

When I issue the following commands, I see that a single row is create and
then updated, even if I was hoping to obtain two different rows:

 INSERT OR REPLACE INTO "players"
(id,skill,e,f,s,r,a,g,p,w,c,stagione,settimana,v) VALUES
("100","187","1","1","1","1","50","0","1","1","1","20","1","2011-09-05");

 INSERT OR REPLACE INTO "players"
(id,skill,e,f,s,r,a,g,p,w,c,stagione,settimana,v) VALUES
("100","187","1","1","1","1","50","0","1","1","1","20","2","2011-09-05");

The two INSERT rows are identical except the value under "settimana". I
expected to not have a conflict, so that an INSERT should be performed for
both the commands, but at the end I have only one row with settimana=2.

Do you know why?

Thanks in advance,

Livio


I think that the 'OR REPLACE' clause refers to the primary key, which 
hasn't necessarily to do with a unique index defined elsewhere. So if 
you have a primary key set on id, that would be the result.

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

Re: [sqlite] [newbie/VB.Net + SQLite] Reliable file hashing?

[Bernd Lehmkuhl]

Am 05.03.2010 15:34, schrieb Gilles Ganault:

> In the following code, a record is added everytime, although this file
> is already in the SQLite database (I checked by opening it with a
> stand-alone application after running the program once):
>
> http://pastebin.ca/1823757
>
> The problem occurs around line 63.
>
> I'm using TEXT to hold the hash column: Could it be that, for some
> reason, this data isn't reliably saved or read, which would explain
> why a new record is INSERTed every time, even though this item is
> already in the database?
>
> Thank you for any hint.
>

Hi Gilles,

I think the problem is the query, whether there is already an entry in 
the database:

SQLcommand.CommandText = "SELECT id,name,hash FROM files WHERE hash='@hash'"

The parameter probably shouldn't be put in quotes as you did. This way 
the query looks for the literal '@hash' which doesn't get replaced by 
the SQLite-ADO.NET provider and which of course doesn't exist in the db.

Hope that helps, Bernd

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

Re: [sqlite] Vaccum with VB,NET

[Bernd Lehmkuhl]

Am 24.12.2009 02:16, schrieb Ernany:
> Hello guys,
>
> How i run sqllite with VB2005 , NE|T. I need tio run  *"Vacuum"*.
>
> Thanks a lot
>
> Ernany
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

there is a sqlite .NET framework provider available on 
http://sqlite.phxsoftware.com/
So it's just something like that:

using (SQLiteCommand command = m_connection.CreateCommand())
{
 command.CommandText = "vacuum;";
 command.ExecuteNonQuery();
}

merry Christmas altogether, Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with update trigger

[Bernd Lehmkuhl]

hello list,

my problem is rather related to sql in general than to sqlite but as I'm 
using sqlite as my db and there are so many authorities of sql in this 
list I thought of asking my question here. I have the following schema:

CREATE TABLE T_Linien(
 handle TEXT
   , von TEXT
   , nach TEXT
   , objektartengruppe TEXT
   , UNIQUE (von, nach, objektartengruppe));

with two triggers:

CREATE TRIGGER tr_sort_linien_insert BEFORE INSERT ON T_Linien
   WHEN NEW.von > NEW.nach
 BEGIN
   INSERT INTO T_Linien (von, nach, objektartengruppe) VALUES
 (NEW.nach, NEW.von, NEW.objektartengruppe);
   SELECT RAISE(IGNORE);
 END;

CREATE TRIGGER tr_sort_linien_update BEFORE UPDATE ON T_Linien
   WHEN NEW.von > NEW.nach
 BEGIN
   UPDATE T_Linien
 SET handle= NEW.handle
   WHERE
   (
 von   = NEW.nach
   AND   nach  = NEW.von
   AND   objektartengruppe = NEW.objektartengruppe
   );
   SELECT RAISE(IGNORE);
 END;

My goal is that 'von' always contains values that are smaller than 
'nach'. The following sql inserts the values correctly in that it 
exchanges 'von' and 'nach'.

INSERT INTO T_Linien (von, nach, objektartengruppe) VALUES (
 'c'
   , 'b'
   , 'xx');

SELECT * FROM T_Linien;

handle  von nachobjektartengruppe
b   c   xx

Unfortunately the update doesn't quite work as I expected it to do:

UPDATE T_Linien
   SET handle = '0815'
WHERE von = 'c'
AND   nach = 'b'
AND   objektartengruppe = 'xx';

SELECT * FROM T_Linien;

handle  von nachobjektartengruppe
b   c   xx

Isn't NEW.von 'c' and NEW.nach 'b' and shouldn't in that case exchange 
the update trigger the two values?
Any help is really appreciated, thanks in advance, Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users