Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Joe Mistachkin]

Andrew Arnott wrote:
>
> The exception raised when I send "VACUUM" as a sql statement is:
> SQLite.SQLiteException occurred
>   _HResult=-2146233088
>   _message=SQL logic error or missing database
>   HResult=-2146233088
>   Message=SQL logic error or missing database
>   Source=Dart.WinPhone8Lib
>   StackTrace:
>at SQLite.SQLiteCommand.ExecuteNonQuery()
>   InnerException:
> 
> Thanks for any help you can offer!
>

In the "SQLite.cs" file, there is the following:

#if NETFX_CORE
SQLite3.SetDirectory(/*temp directory type*/2,
Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);
#endif

Can you please try removing the surrounding "#if" from this code and try it
again?

--
Joe Mistachkin

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

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

I've created a trivial repro project, which you can download here:
http://1drv.ms/N2D4gP

The exception raised when I send "VACUUM" as a sql statement is:
SQLite.SQLiteException occurred
  _HResult=-2146233088
  _message=SQL logic error or missing database
  HResult=-2146233088
  Message=SQL logic error or missing database
  Source=Dart.WinPhone8Lib
  StackTrace:
   at SQLite.SQLiteCommand.ExecuteNonQuery()
  InnerException:

Thanks for any help you can offer!


--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre


On Mon, Mar 10, 2014 at 8:06 PM, Andrew Arnott wrote:

> I expect I could build a miniature repro if that would be useful to anyone.
> On Mar 10, 2014 8:05 PM, "Andrew Arnott"  wrote:
>
>> It raises an error, which the sqlite.cs file that everyone uses turns
>> into an exception.
>> On Mar 10, 2014 6:01 PM, "Simon Slavin"  wrote:
>>
>>>
>>> On 11 Mar 2014, at 12:56am, Andrew Arnott 
>>> wrote:
>>>
>>> > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1,
>>> the
>>> > VACUUM statement quit working. Any idea why?
>>>
>>> What does it do instead of work ?
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

I expect I could build a miniature repro if that would be useful to anyone.
On Mar 10, 2014 8:05 PM, "Andrew Arnott"  wrote:

> It raises an error, which the sqlite.cs file that everyone uses turns into
> an exception.
> On Mar 10, 2014 6:01 PM, "Simon Slavin"  wrote:
>
>>
>> On 11 Mar 2014, at 12:56am, Andrew Arnott  wrote:
>>
>> > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the
>> > VACUUM statement quit working. Any idea why?
>>
>> What does it do instead of work ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

It raises an error, which the sqlite.cs file that everyone uses turns into
an exception.
On Mar 10, 2014 6:01 PM, "Simon Slavin"  wrote:

>
> On 11 Mar 2014, at 12:56am, Andrew Arnott  wrote:
>
> > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the
> > VACUUM statement quit working. Any idea why?
>
> What does it do instead of work ?
>
> 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] 64bit DLL

[Bogdan Ureche]

In case you didn't find one by now, here's 3.8.3.1:

http://www.sqliteexpert.com/library/



On Mon, Mar 10, 2014 at 4:43 AM, RSmith  wrote:

>
> Have any of you kind folks a recent 3.8.3 (or newer) 64-bit DLL for
> SQLite3 perhaps?
>
>
>
> ___
> 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] Final preparations for the release of System.Data.SQLite v1.0.92.0 have begun...

[Joe Mistachkin]

If you have any issues with the current code, please report them via this
mailing
list (and/or by creating a ticket on "https://system.data.sqlite.org/;)
prior to
this coming Friday, March 14th.

Thanks.

--
Joe Mistachkin

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

Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Simon Slavin]

On 11 Mar 2014, at 12:56am, Andrew Arnott  wrote:

> When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the
> VACUUM statement quit working. Any idea why?

What does it do instead of work ?

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

[sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command

[Andrew Arnott]

When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the
VACUUM statement quit working. Any idea why?

I'd be quite happy to 'rollback' to 3.8.1, but I can't find a way to
download prior versions of the SDK.

Thanks.

--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

I built-in an extension which defines all the standard math functions as SQL 
functions.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of big stone
>Sent: Monday, 10 March, 2014 13:22
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
>"instr"
>
>Hello Keith,
>
>Thanks for the ".timer on" tip.
>
>I only succeed to get a "Error: not such function : sqrt" from default
>Sqlite.exe
>How did you get that "native" sqrt working ?
>
>
>regards,
>___
>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] In memory database - WAL

[Richard Hipp]

On Mon, Mar 10, 2014 at 6:25 PM, Sandu Buraga wrote:

> Hi,
>
> I am using WAL to read/write into a database from a single process with
> several threads. For performance reasons, along with the HDD database file,
> I am attaching an in memory db for storing temporary information. Each
> thread has it own handle. My question is: once attached the in memory db
> will be visible for all the handles?
>

No, not unless you want it to be.  See
http://www.sqlite.org/sharedcache.html#inmemsharedcache (section 6.0) for
instructions on how to make it shared.


-- 
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] In memory database - WAL

[Sandu Buraga]

Hi,

I am using WAL to read/write into a database from a single process with
several threads. For performance reasons, along with the HDD database file,
I am attaching an in memory db for storing temporary information. Each
thread has it own handle. My question is: once attached the in memory db
will be visible for all the handles?

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

Re: [sqlite] Fwd: Exception when querying a range index

[St. B.]

Hi,


On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy  wrote:

> On 03/10/2014 06:49 PM, St. B. wrote:
>
>> Hi,
>>
>> I posted this question last year, but did not get any feed back. Is this
>> something that can be handled on the ML, or should I ask in another place?
>>
>> Regards,
>>
>> -- Forwarded message --
>> From: St. B. 
>> Date: Sun, Jan 6, 2013 at 11:45 PM
>> Subject: Exception when querying a range index
>> To: sqlite-users@sqlite.org
>>
>>
>> Hi All,
>>
>> I have an application using sqlite to store coast lines (application runs
>> on windows and uses the .Net version of sqlite v1.0.83). I use range
>> indexes to look-up coast line within range of a given point.
>>
>> During run time I find that the following exception occurs.
>>
>> vtable constructor failed: MapLevel_Idx3
>>
>
> How is virtual table MapLevel_Idx3 created? Is it an rtree table?
>

The virtual table  is created with the following sql statement:

CREATE VIRTUAL TABLE MapLevel_Idx4 USING rtree(
   id,  -- Integer primary key
   minX, maxX,  -- Minimum and maximum X coordinate
   minY, maxY   -- Minimum and maximum Y coordinate
);

Yes it is an rtree table

>
> SQLite version?
>

I am using  system.data.SQLite v 1.0.84.0. I guess it includes sqlites in v
3.7.6.3 (but am not sure at all)

>
> Dan.
>
>
>
>
>
>
>> Occurence ratio is about 1 in 1 queries on the range index. Am I doing
>> something wrong, or is this supposed to be so? When the exception occurs,
>> I
>> just drop the resultset and assume that no data was to be found. Should I
>> restart the query?
>>
>> Regards,
>>
>> --
>>
>> S. Barthes
>> ___
>> 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] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Keith,

Thanks for the ".timer on" tip.

I only succeed to get a "Error: not such function : sqrt" from default
Sqlite.exe
How did you get that "native" sqrt working ?


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

Re: [sqlite] Fwd: Exception when querying a range index

[Dan Kennedy]

On 03/10/2014 06:49 PM, St. B. wrote:

Hi,

I posted this question last year, but did not get any feed back. Is this
something that can be handled on the ML, or should I ask in another place?

Regards,

-- Forwarded message --
From: St. B. 
Date: Sun, Jan 6, 2013 at 11:45 PM
Subject: Exception when querying a range index
To: sqlite-users@sqlite.org


Hi All,

I have an application using sqlite to store coast lines (application runs
on windows and uses the .Net version of sqlite v1.0.83). I use range
indexes to look-up coast line within range of a given point.

During run time I find that the following exception occurs.

vtable constructor failed: MapLevel_Idx3


How is virtual table MapLevel_Idx3 created? Is it an rtree table?

SQLite version?

Dan.







Occurence ratio is about 1 in 1 queries on the range index. Am I doing
something wrong, or is this supposed to be so? When the exception occurs, I
just drop the resultset and assume that no data was to be found. Should I
restart the query?

Regards,

--

S. Barthes
___
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] compile 3.8.4 error

[Richard Hipp]

On Mon, Mar 10, 2014 at 1:06 PM, Michele Pradella <
michele.prade...@selea.com> wrote:

> Compiling on Windows 32bit VS2010
> Line 73595
> static const int iLn = __LINE__+4;
> give an error: "error C2099: initializer is not a constant"
>

This is a case of VS2010 not conforming to the C89 standard.  Disable "Edit
and Continue" to work around the problem.

-- 
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] compile 3.8.4 error

[Michele Pradella]

Compiling on Windows 32bit VS2010
Line 73595
static const int iLn = __LINE__+4;
give an error: "error C2099: initializer is not a constant"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak in online backup api

[Richard Hipp]

On Mon, Mar 10, 2014 at 10:06 AM, techi eth  wrote:

> Any hint will help me to identify root cause of leak.
>

Statically link against SQLite.  Compile with -g.  Use the
--leak-check=full option to valgrind.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak in online backup api

[techi eth]

Any hint will help me to identify root cause of leak.

Thanks


On Mon, Mar 10, 2014 at 2:58 PM, techi eth  wrote:

> Hi,
>
> I come across memory leak with sqlite3 online backup api (Leak detected by
> valgrind tool). I have used reference code from below link for test.
> (Online backup for running database)
>
> http://www.sqlite.org/backup.html
>
> Please find below memory leak snapshot for reference.
>
> ==6084== 78,688 (880 direct, 77,808 indirect) bytes in 1 blocks are
> definitely lost in loss record 52 of 52
> ==6084==at 0x4C2B6CD: malloc (in
> /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==6084==by 0x571C73E: ??? (in
> /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
> ==6084==by 0x56FA238: ??? (in
> /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
> ==6084==by 0x5701B17: ??? (in
> /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
> ==6084==by 0x5701B44: ??? (in
> /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
> ==6084==by 0x576FB91: ??? (in
> /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
> ==6084==by 0x4E36001: db_sqlite3_backup (db_sqlite3.c:2291)
> ==6084==by 0x4026A7:  Test_backup (db_sqlite3_test.c:994)
> ==6084==by 0x40283D: main (db_sqlite3_test.c:1170)
> ==6084==
> ==6084== LEAK SUMMARY:
> ==6084==definitely lost: 880 bytes in 1 blocks
>
> Thanks
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How synchronize two databases

[Simon Slavin]

On 10 Mar 2014, at 12:58pm, Adam Devita  wrote:

> Are the databases designed to allow you to perform such an operation easily?
> 
> On the databases I do it with, I have designed in enough extra data in the
> db and logic in my code to handle sorting out what to do (which record to
> use) if both databases have different data with the same primary key.

That's one of the problems.  Another is what happens if these two commands get 
executed on different copies of the databases:

INSERT INTO customers (name, city) VALUES ("Smith Vacuum Depositing", "Newport")

UPDATE customers SET city = "Fountain City" WHERE city = "Newport"

Without knowing the intention behind the UPDATE (rather than just a dry record 
of SQL commands) there's no way to tell what city should be recorded for Smith. 
 Sometimes a city is renamed.  Sometimes it's a change that just happens to 
effect all data already entered.

There are many other problems like this.  Put them all together and you know 
why synchronization of SQL databases is not a solved problem.

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

Re: [sqlite] How synchronize two databases

[Adam Devita]

Are the databases designed to allow you to perform such an operation easily?

On the databases I do it with, I have designed in enough extra data in the
db and logic in my code to handle sorting out what to do (which record to
use) if both databases have different data with the same primary key.

regards,
Adam DeVita




On Mon, Mar 10, 2014 at 8:15 AM, Simon Slavin  wrote:

>
> On 10 Mar 2014, at 12:58pm, Muhammad Bashir Al-Noimi 
> wrote:
>
> > May I get some help from you guys?
>
> If you're asking how to synchronise two SQL databases which may have had
> different commands executed on them, then you should know that this is an
> unsolved problem which involves many difficult questions. The reason nobody
> is giving you an answer is that nobody in the whole world has a good
> solution which works in all cases.
>
> One way involves logging all the commands executed on the databases, and
> playing back these commands on an unaltered copy of the database.  And even
> this can lead to undesired results.
>
> 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] Making string changes in a table

[Tim Streater]

On 09 Mar 2014 at 22:48, Igor Tandetnik  wrote: 

> On 3/9/2014 6:37 PM, Tim Streater wrote:
>> Dammit, I looked up and down for 'strlen' and passed over 'length'! I had
>> been thinking about:
>>
>>update mytable set path='/path/from/' || substr(path, length('/path/to/')
>> + 1)
>>where path like '/path/to/%';
>>
>> that way I anchor to the start of the path.
>
> Be careful about '/path/to/' itself containing percent signs or underscores.

Thanks for the reminder, Igor.



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

Re: [sqlite] How synchronize two databases

[Simon Slavin]

On 10 Mar 2014, at 12:58pm, Muhammad Bashir Al-Noimi  wrote:

> May I get some help from you guys?

If you're asking how to synchronise two SQL databases which may have had 
different commands executed on them, then you should know that this is an 
unsolved problem which involves many difficult questions. The reason nobody is 
giving you an answer is that nobody in the whole world has a good solution 
which works in all cases.

One way involves logging all the commands executed on the databases, and 
playing back these commands on an unaltered copy of the database.  And even 
this can lead to undesired results.

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

Re: [sqlite] How synchronize two databases

[Dominique Devienne]

On Mon, Mar 10, 2014 at 1:58 PM, Muhammad Bashir Al-Noimi
 wrote:
> On 03/07/2014 08:28 PM, Muhammad Bashir Al-Noimi wrote:
>> I've two identical sqlite databases from structure side. I want to make a
>> synchronization for the data but I need to keep some tables away from the
>> synchronization process.
>>
>> Is there any library can do that OR I've to do it by myself?
>>
>> P.S.
>>
>>  * I'm using C++/Wt
>>  * I read about symmetricds <.../> but I'm not
>>sure what if works for the above request
>>
>>
> May I get some help from you guys?

Probably not, because your message is flagged as "phishing scams" by
at least Google, perhaps because of the URL you included. FWIW. --DD

PS: Also, you may read up on
http://www.catb.org/esr/faqs/smart-questions.html, and perhaps get
(better) answers from a better question.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table and view select difference in C api sqlite3_column_name result

[Simon Slavin]

On 10 Mar 2014, at 11:57am, Stephan Beal  wrote:

> The remaining questions:
> 
> - How are view selects handled differently than table selects?
> - If so, then what is the reasoning behind it?
> 
> This is not explained, but it's quite possibly that way so that the
> implementation has some leeway in how it works.

In other words, the strings returned are just whatever the developers found 
handy when they wrote the code.  They're not defined by the SQL standard, and 
they're undocumented, so nobody cares what they are.

To make it even clearer, they may work differently in different versions of 
SQLite.  In other words, even if you get your program working perfectly with 
the column names returned by one version of SQLite, a later update may change 
what the column names are, and the developers won't consider this a bug.

So if you want column names to be something useful, use AS in your command.

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

Re: [sqlite] How synchronize two databases

[Muhammad Bashir Al-Noimi]

On 03/07/2014 08:28 PM, Muhammad Bashir Al-Noimi wrote:


 Howdy,

I've two identical sqlite databases from structure side. I want to 
make a synchronization for the data but I need to keep some tables 
away from the synchronization process.


Is there any library can do that OR I've to do it by myself?

P.S.

 * I'm using C++/Wt
 * I read about symmetricds  but I'm not
   sure what if works for the above request



May I get some help from you guys?

--
Best Regards,
Muhammad Bashir Al-Noimi

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

Re: [sqlite] table and view select difference in C api sqlite3_column_name result

[Stephan Beal]

On Mon, Mar 10, 2014 at 12:52 PM, Dariusz Wawer wrote:

> yes, that is what is written in the documentation at
> https://www.sqlite.org/c3ref/column_name.html and it is what I have read
> first when trying to find an answer. It provides a solution, but it does
> not answer the question.


It answers 2 of the 4:

- Where does the difference come from?
- Is this intentional?

The docs make clear that it IS intentional and that any result is
unspecified if you do not provide AS. If you don't provide an AS, the
implementation is free to return "abc" for every single column name.

The remaining questions:

- How are view selects handled differently than table selects?
- If so, then what is the reasoning behind it?

This is not explained, but it's quite possibly that way so that the
implementation has some leeway in how it works.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table and view select difference in C api sqlite3_column_name result

[Dariusz Wawer]

W dniu 2014-03-10 12:27, Stephan Beal pisze:

On Mon, Mar 10, 2014 at 11:53 AM, Dariusz Wawer wrote:


When I obtain the column name through sqlite3_column_name() I get
different results for query on view (ALIAS.COLUMN) than for query on table
(COLUMN). The alias is included in column name for view query but not in
table query, even though it is explicitely specified.

Where does the difference come from? How are view selects handled
differently than table selects? Is this intentional? If so, then what is
the reasoning behind it?



The column name returned is ALWAYS UNDEFINED unless you use an "AS" clause
to name it. If you do not use an "AS" clause then it is perfectly legal for
sqlite3 to return anything it wants for the column names.


yes, that is what is written in the documentation at 
https://www.sqlite.org/c3ref/column_name.html and it is what I have read 
first when trying to find an answer. It provides a solution, but it does 
not answer the question.



Pozdrawiam,
Dariusz Wawer

--
+--- http://www.cc.com.pl/ --- +
       CC Otwarte Systemy Komputerowe Sp. z o.o. - |
  CCC  CCC   - CheckPoint, Juniper, SafeNet, Vasco Partner |
  CC   CC  - Internet, Intranet, WWW, Bezpieczne sieci |
  CCC  CCC   - TEL,FAX (+48) 22 646-68-73, 606-37-80 - |
       - Rakowiecka 36, Warszawa, 02-532 PL -- |
+--+
Spółka zarejestrowana w Wydz.XIII Gospodarczym Krajowego  Rejestru |
Sądowego, nr KRS 023570, zarząd: Grzegorz Blinowski  -  Prezes |
Zarządu, Tomasz Ramsza - Członek Zarządu; Kapitał zakł.:10 PLN |
+- +
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Exception when querying a range index

[St. B.]

Hi,

I posted this question last year, but did not get any feed back. Is this
something that can be handled on the ML, or should I ask in another place?

Regards,

-- Forwarded message --
From: St. B. 
Date: Sun, Jan 6, 2013 at 11:45 PM
Subject: Exception when querying a range index
To: sqlite-users@sqlite.org


Hi All,

I have an application using sqlite to store coast lines (application runs
on windows and uses the .Net version of sqlite v1.0.83). I use range
indexes to look-up coast line within range of a given point.

During run time I find that the following exception occurs.

vtable constructor failed: MapLevel_Idx3

Occurence ratio is about 1 in 1 queries on the range index. Am I doing
something wrong, or is this supposed to be so? When the exception occurs, I
just drop the resultset and assume that no data was to be found. Should I
restart the query?

Regards,

--

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

Re: [sqlite] table and view select difference in C api sqlite3_column_name result

[Stephan Beal]

On Mon, Mar 10, 2014 at 11:53 AM, Dariusz Wawer wrote:

> When I obtain the column name through sqlite3_column_name() I get
> different results for query on view (ALIAS.COLUMN) than for query on table
> (COLUMN). The alias is included in column name for view query but not in
> table query, even though it is explicitely specified.
>
> Where does the difference come from? How are view selects handled
> differently than table selects? Is this intentional? If so, then what is
> the reasoning behind it?
>

The column name returned is ALWAYS UNDEFINED unless you use an "AS" clause
to name it. If you do not use an "AS" clause then it is perfectly legal for
sqlite3 to return anything it wants for the column names.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] table and view select difference in C api sqlite3_column_name result

[Dariusz Wawer]

I am confused about the difference of handling table and view selects by 
sqlite.. I have the following view query:


SELECT
   TLP.name, TLP.jar_file, TLP.log_file, ...
FROM
   tasks_list_page TLP

and a table query:

 SELECT
   TSK.name, TSK.jar_file, TSK.log_file, ...
FROM
   task TSK

When I obtain the column name through sqlite3_column_name() I get 
different results for query on view (ALIAS.COLUMN) than for query on 
table (COLUMN). The alias is included in column name for view query but 
not in table query, even though it is explicitely specified.


Where does the difference come from? How are view selects handled 
differently than table selects? Is this intentional? If so, then what is 
the reasoning behind it?



Pozdrawiam,
Dariusz Wawer

--
+--- http://www.cc.com.pl/ --- +
       CC Otwarte Systemy Komputerowe Sp. z o.o. - |
  CCC  CCC   - CheckPoint, Juniper, SafeNet, Vasco Partner |
  CC   CC  - Internet, Intranet, WWW, Bezpieczne sieci |
  CCC  CCC   - TEL,FAX (+48) 22 646-68-73, 606-37-80 - |
       - Rakowiecka 36, Warszawa, 02-532 PL -- |
+--+
Spółka zarejestrowana w Wydz.XIII Gospodarczym Krajowego  Rejestru |
Sądowego, nr KRS 023570, zarząd: Grzegorz Blinowski  -  Prezes |
Zarządu, Tomasz Ramsza - Członek Zarządu; Kapitał zakł.:10 PLN |
+- +
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64bit DLL

[RSmith]

Have any of you kind folks a recent 3.8.3 (or newer) 64-bit DLL for SQLite3 
perhaps?



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

[sqlite] Memory leak in online backup api

[techi eth]

Hi,

I come across memory leak with sqlite3 online backup api (Leak detected by
valgrind tool). I have used reference code from below link for test.
(Online backup for running database)

http://www.sqlite.org/backup.html

Please find below memory leak snapshot for reference.

==6084== 78,688 (880 direct, 77,808 indirect) bytes in 1 blocks are
definitely lost in loss record 52 of 52
==6084==at 0x4C2B6CD: malloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==6084==by 0x571C73E: ??? (in
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
==6084==by 0x56FA238: ??? (in
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
==6084==by 0x5701B17: ??? (in
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
==6084==by 0x5701B44: ??? (in
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
==6084==by 0x576FB91: ??? (in
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6)
==6084==by 0x4E36001: db_sqlite3_backup (db_sqlite3.c:2291)
==6084==by 0x4026A7:  Test_backup (db_sqlite3_test.c:994)
==6084==by 0x40283D: main (db_sqlite3_test.c:1170)
==6084==
==6084== LEAK SUMMARY:
==6084==definitely lost: 880 bytes in 1 blocks

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

[sqlite] Observations about CTE optimization

[Max Vlasov]

Many CTE queries are just some mini-algorithms with iteration and only
last row is required. I just wondered whether it's easy to do this
without "order by ... " of the outer query (also mentioned in my reply
about CTE sqrt). There's a solution, but the good news is that
probably one rarely needs such optimization because of effectiveness
of sqlite in-memory B-tree sort. Anyway, the results are below.

A synthetic test of a query interating until 1,000,000

with recursive
  testval(depth, val) as
   (
  select 1, 0
  UNION ALL
  select depth + 1, val + 1 from testval where depth <= 100
   )
select val from testval order by depth desc limit 1

Time:
  4 sec. 20 ms
Explain query plan
"SCAN TABLE testval"
"COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)"
"SCAN SUBQUERY 1"
"USE TEMP B-TREE FOR ORDER BY"


We add an endmark and where condition moves to the select query so we
get rid of "order by" completely

with recursive
  testval(endmark, depth, val) as
   (
  select 0, 1, 0
  UNION ALL
  select (case when depth < 100 then 0 else 1 end), depth + 1,
val + 1 from testval where endmark=0
   )
select val from testval where endmark=1

Time:
  2 sec 900 ms.
Explain query plan:
"SCAN TABLE testval"
"COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)"
"SCAN SUBQUERY 1"

For the modified CTE sqrt from the other post the difference for
100,000 queries was 27 seconds vs 21 seconds. Not so much, but
sometimes it might make sense to do such optimization

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

Re: [sqlite] How synchronize two databases

[Klaas V]

Muhammad wrote:
| ... two identical sqlite databases from structure side. I want to make a 
synchronization for the data ...
| Is there any library can do that OR I've to do it by myself?

With an Apple you can do it within SQLite itself. 

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

To do it yourself use ',mp insert' and change the resulting SQL-code as needed 
with a suitable editor like Notepad++

Cordiali saluti | Kind regards | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO / ICT-guru / SystemDeveloper-Analyst 
kla...@innocentisart.eu http://innocentisart.eu/klaasv/indexw.html



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