Re: [sqlite] sqlite Issue

2014-11-14 Thread Stephen Chrzanowski
If you're using SQLite3.exe (or equivalent CLI - Command Line Interface)
then by default the database id written to memory, not to the disk.  Doing
something like [ sqlite3.exe test.db3] will create a test.db3 file once you
do an actual transaction like creating a table.  I THINK even doing a
select will make the file as well.  If you're writing code in some
language, as Simon suggested, ensure that you're not writing to [ :memory:
] or to a directory that the app has create and write permissions.

On Fri, Nov 14, 2014 at 5:40 AM, ARVIND KUMAR 
wrote:

> Hi,
>
> I am new for SQLite. I am trying to create database. But its not creating.
> I have attached the screenshot. Please find and do needful.
>
> Thanks & Regards
> Arvind
>
> ___
> 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] [PATCH][really] add missing autoconf feature detection

2014-11-14 Thread Yuriy Kaminskiy
Yuriy Kaminskiy wrote:
> Use of some function/features protected by #ifdefs, but lacks autoconf magic 
> to
> automatically enable them when possible. Of course, they can be manually
> enabled, but it is not very likely. And unused code tends to bitrot.

Oops, last time patch attachment with mime-type text/* worked :-(
Inlining instead:

===

Subject: autodetect and automatically use pread/pwrite

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.14/configure.ac
===
--- sqlite3-3.7.14.orig/configure.ac
+++ sqlite3-3.7.14/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64])

 #
 # By default, we use the amalgamation (this may be changed below...)
@@ -284,6 +284,18 @@ if test "$SQLITE_THREADSAFE" = "1"; then
 fi

 ##
+# Auto-detect pread/pread64
+#
+AC_ARG_ENABLE(pread, AC_HELP_STRING([--disable-pread], [Disable pread(2) use]))
+if test x$enable_pread != xno; then
+   if test $ac_cv_func_pread64,$ac_cv_func_pwrite64 = yes,yes; then
+   AC_DEFINE(USE_PREAD64, 1, [Define if you want to use pread64 
function])
+   elif test $ac_cv_func_pread,$ac_cv_func_pwrite = yes,yes; then
+   AC_DEFINE(USE_PREAD, 1, [Define if you want to use pread 
function])
+   fi
+fi
+
+##
 # Do we want to allow a connection created in one thread to be used
 # in another thread.  This does not work on many Linux systems (ex: RedHat 9)
 # due to bugs in the threading implementations.  This is thus off by default.
===

Subject: autodetect strchrnul

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.8.7.1/configure.ac
===
--- sqlite3-3.8.7.1.orig/configure.ac
+++ sqlite3-3.8.7.1/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul])

 #
 # By default, we use the amalgamation (this may be changed below...)
===

Subject: autodetect posix_fallocate

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.8.7.1/configure.ac
===
--- sqlite3-3.8.7.1.orig/configure.ac
+++ sqlite3-3.8.7.1/configure.ac
@@ -127,7 +127,7 @@ AC_CHECK_HEADERS([sys/types.h stdlib.h s
 #
 # Figure out whether or not we have these functions
 #
-AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul])
+AC_CHECK_FUNCS([usleep fdatasync localtime_r gmtime_r localtime_s utime
malloc_usable_size pread pwrite pread64 pwrite64 strchrnul posix_fallocate])

 #
 # By default, we use the amalgamation (this may be changed below...)

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


[sqlite] [PATCH] add missing autoconf feature detection

2014-11-14 Thread Yuriy Kaminskiy
Use of some function/features protected by #ifdefs, but lacks autoconf magic to
automatically enable them when possible. Of course, they can be manually
enabled, but it is not very likely. And unused code tends to bitrot.


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


Re: [sqlite] Recursive CTE on joined table

2014-11-14 Thread Stephan Beal
On Fri, Nov 14, 2014 at 6:16 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> I can simplify the SQL by creating a view andthen use the ciew in my
> recursive cte, but for various reasons I would rather not do this.
>
> Is it possible to use a recursive cte that refers to a cte?
>

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

You can combine several CTEs into one big SELECT, which seems to be what
you're asking?

Search that page for "mandelbrot" for an extreme example.

-- 
- 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] Recursive CTE on joined table

2014-11-14 Thread Paul Sanderson
I have a recursive cte on a joined table and altough it works fine the
SQL is a little complex and teh join makes it a little difficult to
follow.

I can simplify the SQL by creating a view andthen use the ciew in my
recursive cte, but for various reasons I would rather not do this.

Is it possible to use a recursive cte that refers to a cte?

Thanks




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sliteonline or sqlite in js

2014-11-14 Thread Stephan Beal
On Fri, Nov 14, 2014 at 4:31 PM, Clemens Ladisch  wrote:

> I understand the desire to avoid storing data on the web server, but it
> would
>

It doesn't - you can load local files.

-- 
- 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] BUGREPORT: LIMIT in a subquery has effect on the entire select

2014-11-14 Thread Richard Hipp
The ticket describing the cause of this problem is
https://www.sqlite.org/src/tktview/db872294979b

The problem should be fixed in 3.8.7.2 (due early next week) and in the
next main release 3.8.8, and later today on trunk and on the branch-3.8.7
branch.

On Fri, Nov 14, 2014 at 9:45 AM, Igor Stassiy  wrote:

> this was found in a larger query during testing (the number of ids was
> larger in the IN statement,
> however the query was the same)
>
> the bug was found with version 3.8.7.1 on:
> Linux 3.13.0-39-generic #66-Ubuntu x86_64 GNU/Linux
>
> on 3.8.2 the error did not occur on Ubuntu, but occured on
> Darwin Kernel Version 13.0.0 Thu Sep 19 22:22:27 PDT 2013 x86_64
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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


Re: [sqlite] sliteonline or sqlite in js

2014-11-14 Thread Clemens Ladisch
Dan Kennedy wrote:
> Perhaps not as functional as a native app (so far), but looking really good!
> And you can't beat the deployment.

Well, it does not replace SQL Fiddle.

I understand the desire to avoid storing data on the web server, but it would
be nice if the initial schema/query could be specified with a URL paramter.


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


Re: [sqlite] querying with BLOB in WHERE clause, possible?

2014-11-14 Thread Clemens Ladisch
bjdodo wrote:
> Sorry for resurrecting an old thread. I got the where clause working for
> queries with byte array arguments based on this discussion. I cannot find
> the way to use byte arrays in where clauses for update and delete
> statements.

In the Android database API, execSQL() is the only function where the
parameters are not String[] but Object[]:

  byte[] blob = ...;
  db.execSQL("DELETE FROM t WHERE my_blob = ?", new Object[]{ blob });


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


Re: [sqlite] Null rowid when using CTEs

2014-11-14 Thread Dan Kennedy

On 11/14/2014 08:33 AM, RP McMurphy wrote:

When using CTEs the rowid is null. The following returns "--NULL--"

.null --NULL--
with c(x) as (select 1)
select rowid from c;


I guess that's expected. The same behaviour as for reading the rowid of 
a view or sub-select. It should probably be documented though.


Dan.





RP
___
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] querying with BLOB in WHERE clause, possible?

2014-11-14 Thread Kees Nuyt
On Fri, 14 Nov 2014 04:59:58 -0700 (MST), bjdodo 
wrote:

>Hi
>
>Sorry for resurrecting an old thread. I got the where clause working for
>queries with byte array arguments based on this discussion. I cannot find
>the way to use byte arrays in where clauses for update and delete
>statements. I know it is terrible to use byte arrays as query arguments, I
>need this because of some 3rd party library problems. I also know that I can
>query for the row and do the update using some other column in the where
>clause I just find that "hacky". So if there is a way to do this trick for
>deletes and updates please let me know.


$ sqlite3 test.sqlite
SQLite version 3.8.8 2014-11-13 14:30:56
Enter ".help" for usage hints.
sqlite> CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL, bl BLOB);
sqlite> INSERT INTO T VALUES (1,x'313131');
sqlite> INSERT INTO T VALUES (2,x'323232');
sqlite> UPDATE T SET bl=x'33' WHERE bl=x'323232';
sqlite> SELECT * FROM t;
1|111
2|333
sqlite> DELETE FROM t WHERE bl=x'313131';
sqlite> SELECT * FROM t;
2|333
sqlite>

>Thank you,
>Jozsef

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] BUGREPORT: LIMIT in a subquery has effect on the entire select

2014-11-14 Thread Igor Stassiy
this was found in a larger query during testing (the number of ids was
larger in the IN statement,
however the query was the same)

the bug was found with version 3.8.7.1 on:
Linux 3.13.0-39-generic #66-Ubuntu x86_64 GNU/Linux

on 3.8.2 the error did not occur on Ubuntu, but occured on
Darwin Kernel Version 13.0.0 Thu Sep 19 22:22:27 PDT 2013 x86_64
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] BUGREPORT: LIMIT in a subquery has effect on the entire select

2014-11-14 Thread Richard Hipp
Thanks for the succinct script for reproducing the problem.  We are working
on it now.

Just curious:  Did you find this problem when a larger and more complex
query failed in a real application?

On Thu, Nov 13, 2014 at 9:05 AM, Igor Stassiy  wrote:

> CREATE TABLE A(id UNSIGNED INT, tag NVARCHAR(256), value NVARCHAR(256));
> CREATE TABLE B(tag NVARCHAR(256), hash NVARCHAR(256));
>
> INSERT INTO A VALUES(1, "color", "red");
> INSERT INTO A VALUES(1, "size", "big");
>
> INSERT INTO A VALUES(2, "color", "green");
> INSERT INTO A VALUES(2, "size", "small");
>
> INSERT INTO B VALUES("color", "color");
> INSERT INTO B VALUES("size", "size");
>
> SELECT A.id,B.tag,value FROM A,B WHERE A.id IN (SELECT DISTINCT id FROM A
> WHERE id IN (1, 2) limit 2) AND B.hash=A.tag ORDER BY A.id ASC;
>
> SQLite version 3.8.2 2013-12-06 14:53:30
> 1|color|red
> 1|size|big
> 2|color|green
> 2|size|small
>
> SQLite version 3.8.7.1 2014-10-29 13:59:56
> 1|color|red
> 1|size|big
>
> the correct output is in the version 3.8.2
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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


Re: [sqlite] sqlite Issue

2014-11-14 Thread Simon Slavin

On 14 Nov 2014, at 10:40am, ARVIND KUMAR  wrote:

> I am new for SQLite. I am trying to create database. But its not creating.
> I have attached the screenshot. Please find and do needful.

You cannot post screenshots to this mailing list.

Most problems with creating a new database are because you failed to specify an 
appropriate folder/directory for the database file.  Please make sure you have 
write access to the folder you specified.

If you are writing your own program which calls the SQLite API, and it is not 
creating the database, the function will return a value which is not SQLITE_OK. 
 Please tell us what value it is returning.

If you are using the SQLite Shell Tool to create your database, and it is not 
working, please post the error message it generates.

If you are using some other program besides the SQLite Shell Tool, then that 
program is written by a third party, not the SQLite team and the SQLite team 
can't do anything about your problem.  However, if you tell us what program 
you're using and what error message it shows we /might/ be able to advise you 
on what to try next.

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


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-14 Thread Simon Slavin

On 14 Nov 2014, at 3:42am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> I'm not aware of
>> any usable libraries which actually support 23,10 outside the world
>> of physics.
> 
> http://www.mpfr.org/#free-sw
> 
> I'm sure you're aware of such things.

Hey, you're right.  I was thinking about 128-bit stuff.  I forgot about 
arbitrary-precision libraries.  Thanks for the correction.

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


Re: [sqlite] querying with BLOB in WHERE clause, possible?

2014-11-14 Thread bjdodo
Hi

Sorry for resurrecting an old thread. I got the where clause working for
queries with byte array arguments based on this discussion. I cannot find
the way to use byte arrays in where clauses for update and delete
statements. I know it is terrible to use byte arrays as query arguments, I
need this because of some 3rd party library problems. I also know that I can
query for the row and do the update using some other column in the where
clause I just find that "hacky". So if there is a way to do this trick for
deletes and updates please let me know.

Thank you,
Jozsef





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/querying-with-BLOB-in-WHERE-clause-possible-tp44550p79209.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] BUGREPORT: LIMIT in a subquery has effect on the entire select

2014-11-14 Thread Igor Stassiy
CREATE TABLE A(id UNSIGNED INT, tag NVARCHAR(256), value NVARCHAR(256));
CREATE TABLE B(tag NVARCHAR(256), hash NVARCHAR(256));

INSERT INTO A VALUES(1, "color", "red");
INSERT INTO A VALUES(1, "size", "big");

INSERT INTO A VALUES(2, "color", "green");
INSERT INTO A VALUES(2, "size", "small");

INSERT INTO B VALUES("color", "color");
INSERT INTO B VALUES("size", "size");

SELECT A.id,B.tag,value FROM A,B WHERE A.id IN (SELECT DISTINCT id FROM A
WHERE id IN (1, 2) limit 2) AND B.hash=A.tag ORDER BY A.id ASC;

SQLite version 3.8.2 2013-12-06 14:53:30
1|color|red
1|size|big
2|color|green
2|size|small

SQLite version 3.8.7.1 2014-10-29 13:59:56
1|color|red
1|size|big

the correct output is in the version 3.8.2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] sqlite Issue

2014-11-14 Thread ARVIND KUMAR
Hi,

I am new for SQLite. I am trying to create database. But its not creating.
I have attached the screenshot. Please find and do needful.

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


[sqlite] Null rowid when using CTEs

2014-11-14 Thread RP McMurphy
When using CTEs the rowid is null. The following returns "--NULL--"

.null --NULL--
with c(x) as (select 1)
select rowid from c;

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


Re: [sqlite] sliteonline or sqlite in js

2014-11-14 Thread Dan Kennedy

On 11/14/2014 03:37 PM, Noel Frankinet wrote:

Impressive !


It is that. Perhaps not as functional as a native app (so far), but 
looking really good! And you can't beat the deployment.


Dan.





On 12 November 2014 12:08, Kirill  wrote:


Good day,

Full line manager to work with sqlite directly
from the browser on any platform:
http://sqliteonline.com/

___
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] sliteonline or sqlite in js

2014-11-14 Thread Noel Frankinet
Impressive !

On 12 November 2014 12:08, Kirill  wrote:

> Good day,
>
> Full line manager to work with sqlite directly
> from the browser on any platform:
> http://sqliteonline.com/
>
> ___
> 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


Re: [sqlite] [SQLite] Support for 23,10 Precision number format

2014-11-14 Thread Dominique Devienne
On Thu, Nov 13, 2014 at 6:53 PM, RSmith  wrote:

> By the way, my Oracle friends should intersect here if need be, but I
> believe the oracle method of /decimal(n,m)/ is simply a representation
> directive and constraint, there is no native datatype that actually stores
> or communicates such a value. Oracle stores it internally in a very
> specific arrangement of bytes and you need to still interpret it in your
> software.
>

Not so. OCI has native support for Number client side via OCINumber and
associated functions to convert to native C types, and can do 128-bit
integer arithmetic for example even when the C/C++ native types cannot. And
it's a value type, not an opaque type, so you can decode the internal
well-known byte rep if you want to even. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users