Re: [sqlite] LIKE IN

[Simon Davies]

Hi,

On Fri, 22 Nov 2019 at 13:18, Hamish Allan  wrote:
>
> Hi,
>
> Is it possible to achieve the effect of combining the LIKE and IN operators?
>
> So for instance if I have tables:
>
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
>
> CREATE TABLE matches (match TEXT);
> INSERT INTO matches VALUES ('Alex');
> INSERT INTO matches VALUES ('Alexander');
> INSERT INTO matches VALUES ('Alexandra');
> INSERT INTO matches VALUES ('Rob');
> INSERT INTO matches VALUES ('Robin');
> INSERT INTO matches VALUES ('Robert');
>
> I can query as follows:
>
> SELECT * FROM names WHERE name IN (SELECT * FROM matches);
>
> But can I do something more like:
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex%*');
> INSERT INTO queries VALUES ('Rob%*');
>
> SELECT * FROM table WHERE name LIKE IN (SELECT * FROM queries);
>
> Thanks,
> Hamish

Will a JOIN not do what you want?

SELECT table.* from table t JOIN queries q ON t.name LIKE q.query;

(after cleaning up query, to 'Alex%' and 'Rob%')

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

Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[Simon Davies]

Hi Alex,

On Thu, 18 Jul 2019 at 17:02, Alexandre Billon  wrote:
>
> Hello,
>
> I have created a table.
>
> CREATE TABLE "sales" (
> "client"TEXT,
> "salesman"  TEXT,
> "revenue"   REAL,
> PRIMARY KEY("client","salesman")
> );
>
> I can run the query below mutliple times without any error :
>
> INSERT INTO sales ("client", "salesman", "revenue")
> VALUES ('C1', NULL, 10.0);
>
> Have I missed something in the CREATE instruction ?
> Is this a normal behaviour ?
>
> I have tried to read https://sqlite.org/lang_createtable.html#constraints and 
> https://www.sqlite.org/nulls.html but I don't really have found why I can 
> insert 2 records that have the same primary key.

You quote "https://sqlite.org/lang_createtable.html#constraints;,
which contains:
<<
Each row in a table with a primary key must have a unique combination
of values in its primary key columns. For the purposes of determining
the uniqueness of primary key values, NULL values are considered
distinct from all other values, including other NULLs.
>>

I.e the rows you are inserting do not have conflicting primary keys

The paragraph following the one quoted above is also relevant...

> Thanks in advance for your insights.
>
> Alex

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

Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

[Simon Davies]

On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera  wrote:
>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my 
> logic, which I am trying to see if I can make it work.  Please look at this 
> scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, 
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, 
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, 
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, 
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, 
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, 
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, 
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, 
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, 
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, 
> '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, 
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, 
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, 
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, 
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, 
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, 
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, 
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, 
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, 
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, 
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, 
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, 
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, 
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, 
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, 
> '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem.  But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, 
> '2019-03-01');
>
> and then do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing.  I know why, but what will save my logic is, to be able to fix 
> the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||
>
> in other words, NULL values instead.  This will fix my "logic". :-) and the 
> world will be at peace again. :-)  Is this even possible?  Thanks.

left join:

select
 a.*, b.* from t as a left join z as b on a.a = b.f
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;

> josé

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

Re: [sqlite] INSERTing from another table data

[Simon Davies]

On Tue, 19 Mar 2019 at 15:07, Tim Streater  wrote:
>
> My use case is a mixture of these. My need is to copy a row from a table in 
> one db (db1) to a table with identical schema in another db (db2). The 
> complication is that there is an id column, so the row needs to get a new id 
> in db2.
>
> At the minute I open db1, and do these steps (absid is the id column):
>
> attach database ':memory:' as mem
> create table mem.messages as select * from main.messages where 
> absid=
> update mem.messages set absid=null
> attach database 'db2' as dst
> insert into dst.messages select * from mem.messages
>
> which works nicely but has too many steps. I've not found a way to reduce the 
> step count.

absid is integer primary key, or a new id would not be generated in
the above scenario; so the following should work:

insert into dst.messages( notabsid_1, notabsid2,...) select
notabsid_1, notabsid_2,... from main.messages;

> --
> Cheers  --  Tim

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

Re: [sqlite] SQLite3 with C

[Simon Davies]

Hi Ricardo,

On 15 August 2018 at 06:26, Ricardo Lima  wrote:
> I built a straightforward program in C. It's just a STACK structure (first 
> in, last out) that takes input from the user and stores it into the stack. 
> After I close the program, all the data vanishes since I don't have a 
> database implemented into the program. Even so, the program runs fine. It 
> allocates memory according to the user's input, and I always validate every 
> allocation.
>
> In fact, my issue isn't concerning C code itself. It's the implementation of 
> SQLite into the C code. Every stack_node has a value (integer), date (char), 
> and info (char). The wallet structure is the "middle-man" between the 
> stack_node and the DB, and it has the same attributes plus an ID (integer 
> autoincremented). What I'm trying to do is to as soon as the user inputs data 
> (while the program is running) this data gets stored not only in the stack 
> but also in the DB. After the user is done inputting data, I usually use 
> pop_stack() to retrieve the data from the stack onto the screen, but since 
> the data is stored in the DB, it makes more sense just to extract all data 
> from the DB.
>
> I don't get any compilation errors and the SQLite source files/header files 
> are implemented correctly into the code. I know this because I'm using SQLite 
> Studio ( SQLite GUI) and I see all the attributes from the wallet DB, but 
> they are all empty.
> So, that's my issue. I'm doing something wrong that causes the program to not 
> store the data correctly into the DB.

in
if( push_stack( s, value, date, info ) ){
addData( db, node );
 }

node is always NULL, and addData checks for that, and does nothing...

> --- Is it a waste of memory and running time for the program to create a 
> stack, stack_node etc...to only after all that store the information in the 
> DB? Should I just skip all that and program just for the DB? If so, how would 
> I do that since I would eventually run into the same problem. I'm using the 
> stack because I might have more control over what I can do with the data like 
> adding the values, calculate average expenditure and so on.
>
> Thank you so much!

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

Re: [sqlite] not build with SQLITE_OMIT_VIRTUALTABLE

[Simon Davies]

On 3 May 2018 at 14:07, Michele Dionisio  wrote:
> Hi all,
>
> last sqlite 3230100 does not build with -DSQLITE_OMIT_VIRTUALTABLE and the
> error is:
>
> | sqlite3-sqlite3.o: In function `sqlite3RunParser':
> | sqlite3.c:(.text+0x6e644): undefined reference to `sqlite3VtabFinishParse'
> | sqlite3.c:(.text+0x6e654): undefined reference to `sqlite3VtabFinishParse'
> | sqlite3.c:(.text+0x6e674): undefined reference to `sqlite3VtabBeginParse'
> | sqlite3.c:(.text+0x6e680): undefined reference to `sqlite3VtabArgInit'
> | sqlite3.c:(.text+0x6e690): undefined reference to `sqlite3VtabArgExtend'
> | collect2: error: ld returned 1 exit status
> | make: *** [sqlite3] Error 1

If you are using the amalgamation, see last bullet in list in
"Building The Amalgamation"
(http://www.sqlite.org/howtocompile.html#amal)

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

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

[Simon Davies]

On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.)
 wrote:
> Hi All,
>
> We have a requirement where in offset of the primary key ID is needed as per 
> the sorted list.
.
.
.
> 1   AAA
> 3   BBB
> 5   CCC
> 4   WWW
> 2   ZZZ
>
> So position of ID 3 as per the sorted list of the NAME is 2.

sqlite> create table t( id integer primary key, data text );
sqlite> insert into t( data ) values('aaa'), ('zzz'), ('bbb'), ('www'), ('ccc');
sqlite>
sqlite> select count(*)+1 from t where data<(select data from t where id=3);
2

> Thanks and Regards
> Deepak

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

Re: [sqlite] SQL Syntax fault on UPDATE statement

[Simon Davies]

On 17 August 2016 at 09:39, R Smith  wrote:
>
>
> On 2016/08/17 9:05 AM, flo wrote:
>>
>> Hi everyone,
.
.
.
> Well, it is perfectly valid to give boolean operations as an expression.
> If I said " id = 3 AND 6 then the resulting value would be 2  (If you are
> unsure why that is you need to read up on Boolean logic, check google for
> it)

Boolean AND:
sqlite> select 3 and 6;
1

Bitwise and:
sqlite> select 3 & 6;
2

> I hope that makes it clear!
> Ryan

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

Re: [sqlite] Regarding redirecting SQL results and errors to a single output file.

[Simon Davies]

On 27 June 2016 at 10:28, Kaja Varunkumar  wrote:
> Hello,
>
> sqlite -echo test.sqlite < test44.sql > test44.out.
> When I am running above command in windows command prompt, succeed query
> redirected to test44.out and errors are displayed on the command prompt.
> Here I want all the succeed and failed queries in one file.

Not an sqlite issue:
Google  "windows redirect stdout and stderr"
-->  
http://stackoverflow.com/questions/1420965/redirect-stdout-and-stderr-to-a-single-file
.
.
.
>
> Thanks,
> varun

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

[sqlite] help with query

[Simon Davies]

On 6 April 2016 at 12:22, e-mail mgbg25171  
wrote:
> Here are my tables specified as... tbl_nm | col1, col2...
> std_nms | id, nm
> raw_nms | id, nm
> nm_pairs | id, std_nms_id, raw_nms_id
>
> I'm wondering how to supply a single std_nms.nm and get back a list of pairs
> i.e. std_nm.nm, raw_nms.nm
> that reflect each record in nm_pairs with a std_nms_id = std_nms.id

SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table std_nms( id integer, nm text );
sqlite> create table raw_nms( id integer, nm text );
sqlite>
sqlite> create table nm_pairs( id integer, std_nms_id integer,
raw_nms_id integer );
sqlite>
sqlite> insert into std_nms( id, nm ) values( 1, 'std1' ),( 2, 'std2'
),( 3, 'std3' );
sqlite> insert into raw_nms( id, nm ) values( 1, 'raw1' ),( 2, 'raw2'
),( 3, 'raw3' );
sqlite>
sqlite> insert into nm_pairs( id,  std_nms_id, raw_nms_id ) values( 1,
1, 1 ),( 2, 2, 2 ),( 3, 3, 3 ),( 4, 1, 3 );


sqlite> select sn.nm, rn.nm
   from std_nms sn
   inner join nm_pairs nmp on nmp.std_nms_id=sn.id
   inner join raw_nms rn on nmp.raw_nms_id=rn.id
   where sn.nm='std1';
std1|raw1
std1|raw3

Regards,
Simon

[sqlite] sqlite error 25 during bind

[Simon Davies]

On 6 April 2016 at 12:16, Kumar Suraj  wrote:
> Hi
. 
. 
. 
> Here is table definition and insert statement which is causing this
>
> #define CREATE_TABLE_DNINDEX "CREATE TABLE IF NOT EXISTS TBL (dn BLOB,
> pclassid INTEGER, pkey INTEGER, kindex INTEGER PRIMARY KEY ASC)"
>
> #define INSERT_DN "INSERT INTO TBL (dn,pclassid,pkey) VALUES (?,?,?);"
>
>  rv = sqlite3_bind_int(newStmt, 2 , aInClassId);
>
> if (rv != SQLITE_OK)
>
> {
>
> fprintf(stderr, "Error Insert : sqlite3_bind_int, Error code :
> %d\n", rv);
>
> sqlite3_finalize(newStmt);
>
> return rv;
>
> }

Are you calling sqlite3_prepare_v2 before your call to sqlite3_bind_int?

Regards,
Simon

[sqlite] Can't understand why I need this cast

[Simon Davies]

On 18 February 2016 at 15:29, Richard Hipp  wrote:
> On 2/18/16, Simon Davies  wrote:
>>
>
> The documentation says:  "An expression of the form "CAST(expr AS
> type)" has an affinity thta is the same as a column with a declared
> type of "type".".  Since the RHS of the <= operator now has affinity,
> that affinity is applied to the LHS operand as well, resulting in both
> operands being treated as integers for the purpose of the comparison.
> --
> D. Richard Hipp

I was aware that CAST produces affinity as quoted, but not that a
literal integer does not...

What is the affinity of a literal integer?

I have had a quick search through the documentation but did not find anything.

Regards,
Simon

[sqlite] Can't understand why I need this cast

[Simon Davies]

On 18 February 2016 at 12:34, Richard Hipp  wrote:
> On 2/18/16, Simon Davies  wrote:
>> sqlite> select '11' <= 11;
>> 0
>> sqlite> select '11' <= cast(11 as integer);
>> 1
>> sqlite> select '11' >= 11;
>> 1
>
> Sort order in SQLite is:  (1) NULLs, (2) Numeric values in numeric
> order, (3) Strings in the order of whatever collating sequence
> applies, and (4) BLOBs in lexicographical order.
>
> Hence, strings are always greater than numbers.  Always.
>
> If you want to compare a string against it's numeric equivalent, you
> need to cast the string to a number.
>
> --
> D. Richard Hipp

Ok on sort order, but why

SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> select '11' <= 11;
0
sqlite> select '11' <= cast(11 as integer);
1

Regards,
Simon

[sqlite] Can't understand why I need this cast

[Simon Davies]

sqlite> select '11' <= 11;
0
sqlite> select '11' <= cast(11 as integer);
1
sqlite> select '11' >= 11;
1

Certainly seems odd...

On 18 February 2016 at 12:20, Rob Willett  
wrote:
> Tim,
>
> We actually do store the timestamps as epoch seconds, but we wrote a quick
> piece of SQL to test something out and wanted to use the ISO date. Thats
> when the SQL failed and we couldn?t understand why. We hate it when we don?t
> understand why things don?t work the way we expect. Our OCD kicks in and
> annoys us :)
>
> We?ve investigated it further and it still makes no sense, though Quan Yong
> Zhai has helped. It appears that
>
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
>
> works BUT
>
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
>
> does not work.
>
> The only difference is the boolean operand.
>
> We know how to solve the problem, we?re puzzled though as our understanding
> is clearly wrong.
>
> Thanks for replying,
>
> Rob
>
> On 18 Feb 2016, at 12:14, Tim Streater wrote:
>
>> On 18 Feb 2016 at 10:20, Rob Willett  wrote:
>>
>>> I?m sure this is a really dumb question but I?m struggling to
>>> understand why the following SQL is needed for what should be a trivial
>>> SQL expression.
>>>
>>> I?ve minimised the example down to (hopefully) make it simpler.
>>>
>>> I have a table with an ISO date StartTime in it held as a string.
>>
>>
>> I'd be inclined to store your dates as seconds since the epoch. That way
>> arithmetic and comparisons become easy, and your SQL looks simpler. Convert
>> to a string for display. But perhaps your application prevents that for some
>> reason.
>>
>> --
>> Cheers  --  Tim
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Setting SQLITE_OMIT_FLOATING_POINT has surprising undocumented consequences

[Simon Davies]

On 14 January 2016 at 22:31, Warren Young  wrote:
> For no especially good reason, I decided to turn off all SQLite features I?m 
> not using now and which I have no plans to use in the future.
>
> My current DB doesn?t use any FP columns, so I rebuild SQLite with 
> SQLITE_OMIT_FLOATING_POINT and ran ran into a bunch of breakage:
>
> 1. The (double) cast on line 66942 of the current amalgamation in 
> valueFromExpr() produces a warning.  Most of that block probably should go 
> away if FP is disabled.

http://www.sqlite.org/compile.html#omitfeatures

SQLITE_OMIT_xxx options "may only be used when the library is built
from canonical source, not from the amalgamation"

Regards,
Simon

[sqlite] 'order by' doesn't work with 'group_concat()'

[Simon Davies]

On 5 January 2016 at 00:14, Yuri  wrote:
> Please consider this example:
. 
. 
. 
> The 'order by' clause doesn't work, because if it did the result would have
> been:
> 1|x,y
> 2|x,y
>
> sqlite3-3.9.2
>
> Yuri

See http://www.sqlite.org/lang_aggfunc.html last sentence

"
group_concat(X)
group_concat(X,Y)
The group_concat() function returns a string which is the
concatenation of all non-NULL values of X. If parameter Y is present
then it is used as the separator between instances of X. A comma (",")
is used as the separator if Y is omitted. The order of the
concatenated elements is arbitrary.
"

Regards,
Simon

[sqlite] How do i submit a bug?

[Simon Davies]

On 5 October 2015 at 10:48, Jacob  wrote:
> Hi John,
>
> I have tried to create an in memory database according to the
> documentation on this link
> https://www.sqlite.org/inmemorydb.html
>
> The documentation lists: (C code)
>
> rc = sqlite3_open("file:memdb1?mode=memory=shared", );
>
> It creates a file on disk this is not supposed to happen.
>
> I compiled with version 3.8.11 , i checked (see c-code below) that i
> used the correct include file and library

Was the library compiled with "SQLITE_USE_URI=1"?
(See "Backwards Compatibility" in https://www.sqlite.org/uri.html)

Regards,
Simon

[sqlite] order by not working in combination with random()

[Simon Davies]

On 17 August 2015 at 21:50, Simon Slavin  wrote:
>
> On 17 Aug 2015, at 9:46pm, Jeffrey Mattox  wrote:
>
>> Could the random() be made part of an expression (that doesn't change the 
>> result) to fool the optimizer into only doing the random() once, like this:
>>
>> SELECT ( random() * col_thats_always_one ) AS x  FROM table  ORDER BY x
>
> Use a sub-select:
>
> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC LIMIT 20

So:

SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table myTable( id integer );
sqlite>  insert into myTable values(1),(2),(3),(4),(5);
sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC;
-6629212185178073901
-5293473521544706766
2649466971390864878
-6185422953036640443
1855956853707028764
sqlite>

> Alternatively I think you could use WITH (CTE format) but I would like 
> someone more familiar with its syntax to figure it out.
>
> Simon.

Regards,
Simon

[sqlite] How to enable shared cache mode?

[Simon Davies]

On 13 August 2015 at 19:17, Joe Mistachkin  wrote:
>
> Aaron Hudon wrote:
>>
>> I've looked through the source, and see there is a method in
>> UnsafeNativeMethods.cs that exposes this internally to the
>> assembly, but nothing that's public.
>
> Currently, it's only used by the test suite.  In the future, being
> able to enable shared cache mode for use with System.Data.SQLite is
> something that is being considered.
>
> --
> Joe Mistachkin

Shared cache can be specified in URI filenames; do they not work with
System.Data.SQLite?
http://www.sqlite.org/c3ref/open.html#urifilenamesinsqlite3open

Regards,
Simon

[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?

[Simon Davies]

On 2 July 2015 at 15:09, Kathleen Alexander  wrote:
> Hi,
>
> I apologize if this is an incorrect forum for this question, but I am
> pretty new to SQLite and have been unable to resolve this issue through
> other searches. Feel free to direct me to a more appropriate forum.
>
> Essentially, I have written an application in C++ that interfaces (reads
> and writes) with a SQLite database, and I am getting lots of 'database is
> locked' errors. Right now, I am trying to establish whether those errors
> are due to my improper use of SQLite itself, or if the real problem is that
> SQLite is not a good fit for my application.
>
> My application runs on Linux (ubuntu 13.10), and is driven by a bash script
> that spawns many (~60 on a 64 core workstation) instances of a serial, C++
> program, each of which opens its own connection to the database and
> performs reads and writes.

http://www.sqlite.org/whentouse.html#dbcklst item 2 may be relevant

Regards,
Simon

[sqlite] confusing error msgs

[Simon Davies]

On 12 June 2015 at 06:42, Hick Gunter  wrote:
> You are creating each table in a separate file; a foreign key may only 
> reference a table in the same file.
>
> Your type declarations are faulty in that you are omitting an opening 
> parenthesis in a DECIMAL 4,3) declaration.
>
> SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my contain 
> arbitrary length strings; types only define affinities, the same fields could 
> be used to store integers, reals or BLOBS for that matter.
>
> SQLIte also does not implement fixed point numeric fields. DECIMAL (6,2) 
> defines a field of numeric affinity, i.e. if you provide a value that 
> contains a decimal point, it will be stored as a REAL.
>

And table constraints (foreign key in timeslip table) must follow the
column declarations
Regards,
Simon

[sqlite] Regarding testing

[Simon Davies]

On 27 April 2015 at 12:54, Sairam Gaddam  wrote:
> How SQLite is tested and can I get those test cases?

4th entry under "Overview Documents" on http://www.sqlite.org/docs.html

[sqlite] Problems with pragma journal_mode

[Simon Davies]

On 16 April 2015 at 10:10, Janke, Julian  wrote:
> Hi everyone,
>
> I'm relatively new to SQLite and currently I'm experimenting a little bit 
> with the SQLite database on an embedded system.
> I want to change the journal mode of a database to WAL. So I did the 
> following:
>
> rc = sqlite3_open(dbPath, );
> rc = sqlite3_exec(db, "PRAGMA journal_mode=WAL;", testCallbackPrint, 0, 
> );
> [?]
> sqlite3_close(db);
>
> Unfortunately, after these lines, the journal mode is not changed. The 
> sqlite3_exec function returns SQLITE_OK (0),
> testCallbackPrint is never called and the error message is empty.
> Also reading the value of journal mode with ?PRAGMA journal_mode;? leads to 
> the same result (rc=0, callback noch called and error message is empty).

What version of SQLite?

...
>
> Thanks.

Regards,
Smon

[sqlite] Select query becomes distinct on where matches unique with null value

[Simon Davies]

On 11 April 2015 at 07:07, Mike Gladysch  wrote:
> Hi,
. 
> Data:
> 1;1;null;test;null;null
> 2;2;null;test;null;null
> 3;3;null;test;null;null
> 4;4;something;test;null;null
>
> Select col1, col2, col3, col4, col5, col6
> From table
> Where col3 is null and col4 ='test'
>
> Delivers different results:
>
> 3.8.4.3: 3 rows (expected, ok)
> 3.8.7.2: 1 row (wrong)
> 3.8.9: 1 row (wrong)
>
> Tested with Windows shell binaries.
>
> Mike

So I tried this:

SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table t( c1 integer primary key, c2 integer unique not null,
  c3 integer unique, c4 integer, c5
integer, c6 integer );
sqlite>  insert into t values( 1,1,null,'test',null,null);
sqlite>  insert into t values( 2,2,null,'test',null,null);
sqlite>  insert into t values( 3,3,null,'test',null,null);
sqlite>  insert into t values( 4,4,'something','test',null,null);
sqlite> select c1,c2,c3,c4,c5,c6 from t where c3 is null and c4='test';
1|1||test||
2|2||test||
3|3||test||
sqlite>

Am I doing anything different from you?

Regards,
Simon

[sqlite] Sqlite3 Bug Found

[Simon Davies]

On 6 March 2015 at 08:38, Carabas, Costin  wrote:
> Hello,
>
> I was working on version 3.8.8.2 of sqlite3 and I found a bug that is trace 
> related. It can be reproduced in 3 steps:
>
> 1.   ".t log" - opens log for tracing
>
> 2.   ".t" - no argument. Closes the file descriptor that was previously 
> opened (Problem)

No - it reports the expected usage - see below

> 3.   ".d" - dumps into the file that was previously closed. = > 
> Segmentation Fault
>
> Where:
> .t = .trace
> .d = .debug

I think you mean .dump here

> Best Regards,
> Costin

Works for me:

C:\sqlite>sqlite3_3.8.8.2 new.db
SQLite version 3.8.8.2 2015-01-30 14:30:45
Enter ".help" for usage hints.
sqlite> .t log
sqlite> .t
Usage: .trace FILE|off
sqlite> .d
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .q


On 19 March 2015 at 12:52, Carabas, Costin  wrote:
> This bug reproduces to the latest Sqlite release (3.8.8.3).
>
> The log from the bug:
> sqlite> .t file
> sqlite> .t
> Usage: .trace FILE|off
> sqlite> .d
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> Segmentation fault
>
> I also found another bug with the same root cause:
> sqlite> .t file
> sqlite> .t
> Usage: .trace FILE|off
> sqlite> .t
> *** Error in `.libs/lt-sqlite3': double free or corruption (top): 
> 0x024ab340 ***
> Aborted

Still works for me:

C:\sqlite>sqlite3_3.8.8.3 new.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite>
sqlite> .t log
sqlite> .t
Usage: .trace FILE|off
sqlite> .t
Usage: .trace FILE|off
sqlite>
sqlite> .q

These executables were Win32 downloaded from
https://www.sqlite.org/download.html:
sqlite-shell-win32-x86-3080802.zip
sqlite-shell-win32-x86-3080803.zip

How are you getting your executables?

Regards,
Simon

[sqlite] SQLITE_OPEN_EXCLUSIVE

[Simon Davies]

On 15 March 2015 at 12:54, Felipe Gasper  wrote:
> This seems to support the idea that O_EXCL is SQLITE_OPEN_EXCLUSIVE..?

"The SQLITE_OPEN_EXCLUSIVE flag is always used in conjunction with the
SQLITE_OPEN_CREATE flag, which are both directly analogous to the
O_EXCL and O_CREAT flags of the POSIX open() API. The
SQLITE_OPEN_EXCLUSIVE flag, when paired with the SQLITE_OPEN_CREATE,
is used to indicate that file should always be created, and that it is
an error if it already exists. It is not used to indicate the file
should be opened for exclusive access."

I think that is pretty explicit.

Regards,
Simon

[sqlite] SQLITE_OPEN_EXCLUSIVE

[Simon Davies]

On 15 March 2015 at 06:06, Felipe Gasper  wrote:
> Hi all,
>
> Does the SQLITE_OPEN_EXCLUSIVE flag exist as analogous to O_EXCL in
> C open()? i.e., is this how I can say, ?open a database, but only if it?s a
> new database??

https://www.sqlite.org/capi3ref.html#sqlite3vfsxopen

> Thank you!
>
> -Felipe Gasper
> Houston, TX

Regards,
Simon

[sqlite] fix rowid's of fts table

[Simon Davies]

On 12 March 2015 at 20:20, Rael Bauer  wrote:
>
> Hi,
>
. 
. 
. 
> Now I have restructured the normal table (i.e. delete column) using the
> algorithm mentioned here a couple of times (insert into normal select
> (columns) from normal_OLD, etc..)
>
> This "resets" the rowid's of the normal table.
> E.g. if the table started with rowid's like:
> 1
> 2
> 6
> 7
> 10
>
> after restructuring they will be:
> 1
> 2
> 3
> 4
> 5
>
> So the fts table is now out of sync with the normal table. Do i have to
> completely re-populate the fts table or is there a simpler way to
> "reset" the fts rowids?

Propagate the rowids from the old table when populating the new table:

INSERT INTO nTable( rowid, data1, ... ) SELECT rowid, data1, ... FROM oTable;

>
> (If I tried to just reset the rowid's on the "main" fts table I imagine
> this would not work, since there are the aux fts tables as well..)
>
> Thanks
> Rael

Regards,
Simon

[sqlite] Multi-table index ersatz?

[Simon Davies]

On 3 March 2015 at 11:10, Eric Grange  wrote:
>
> Hi,
>
> I have problem where I need a "multi-table index" ersatz, or maybe a better
> data structure :-)
>
> The problem is as follow:
>
>- Table A : some fields plus fields A1 & A2
>- Table B : some fields plus fields B1 & B2
>
> Both tables have several dozen millions of rows, and both are accessed
> independently of each others by some queries, their current structure has
> no performance issues for those queries.
>
> However I have a new query which is like
>
> select ...some fields of A & B...
> from A join B on A.A2 = B.B2
> where A.A1 = ?1
> order by B.B1
> limit 100
>
> Without the limit, there can be tens of thousandths resulting rows, without
> the A1 condition, there can be millions of resulting rows.
>
> With indexes on A & B, the performance of the above is not very good, as
> indexing A1 is not enough, and indexing B1 is not enough either, so no
> query plan is satisfying.

Have you tried indexing on A2?

. 
. 
. 
> Is there a better way that would not involve duplicating the data?
>
> Eric

Regards,
Simon

[sqlite] outer join/order by bug?

[Simon Davies]

On 24 February 2015 at 15:20, Simon Davies  
wrote:
> On 24 February 2015 at 15:02, Grisha Vinevich  wrote:
>> There seems to be some problem with left outer join in Windows version of 
>> sqlite3.
>> I try to use the following (simplified) schema:
>>

. 
. 
. 
>> When I run this on SQLFiddle<http://www.sqlfiddle.com/#!7/763a5/1>, It 
>> returns (correctly) 2 records.
>> But when I run this on Windows (using sqlite3.exe command-line utility, for 
>> example), no records are returned. Note that if I remove "order by" clause 
>> or one of the joins or even dummy condition inside the first join, the query 
>> works OK.
>> The same happens on Windows Phone using sqlite.net.
>> Any suggestions would be appreciated...
>> Thanks in advance,
>> Grisha.
>
> Perhaps you could advise what version you are using.
>
> When I try:
>
> Microsoft Windows [Version 6.1.7601]
> Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
>
> C:\>sqlite3
> SQLite version 3.7.15.1 2012-12-19 20:39:10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> sqlite> CREATE TABLE "A" ( "Name" text);
> sqlite> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
> sqlite> INSERT INTO "Items" VALUES('Item1','Parent');
> sqlite> INSERT INTO "Items" VALUES('Item2','Parent');
> sqlite> CREATE TABLE "B" ( "Name" text );
> sqlite>
> sqlite> select Items.ItemName
>...> from Items
>...> left outer join A on (A.Name =
> Items.ItemName and Items.ItemName = 'dummy')
>...> left outer join B on (B.Name = Items.ItemName)
>...> where Items.Name = 'Parent';
> Item1
> Item2
> sqlite>
>
> I get the same result with v 3.8.8.2 from the download page
>
> Regards,
> Simon

Sorry - missed the order by clause.

Confirm your observation using 3.8.8.2, but get your expected result
using 3.7.15.1

Regards,
Simon

[sqlite] outer join/order by bug?

[Simon Davies]

On 24 February 2015 at 15:02, Grisha Vinevich  wrote:
> There seems to be some problem with left outer join in Windows version of 
> sqlite3.
> I try to use the following (simplified) schema:
>
> CREATE TABLE "A" ( "Name" text);
> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
> INSERT INTO "Items" VALUES('Item1','Parent');
> INSERT INTO "Items" VALUES('Item2','Parent');
> CREATE TABLE "B" ( "Name" text );
>
> Now I try to run the following query (again, simplified version of my real 
> query):
>
> select Items.ItemName
> from Items
> left outer join A on (A.Name = Items.ItemName and 
> Items.ItemName = 'dummy')
> left outer join B on (B.Name = Items.ItemName)
> where Items.Name = 'Parent'
> order by Items.ItemName;
> When I run this on SQLFiddle, It 
> returns (correctly) 2 records.
> But when I run this on Windows (using sqlite3.exe command-line utility, for 
> example), no records are returned. Note that if I remove "order by" clause or 
> one of the joins or even dummy condition inside the first join, the query 
> works OK.
> The same happens on Windows Phone using sqlite.net.
> Any suggestions would be appreciated...
> Thanks in advance,
> Grisha.

Perhaps you could advise what version you are using.

When I try:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\>sqlite3
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> CREATE TABLE "A" ( "Name" text);
sqlite> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
sqlite> INSERT INTO "Items" VALUES('Item1','Parent');
sqlite> INSERT INTO "Items" VALUES('Item2','Parent');
sqlite> CREATE TABLE "B" ( "Name" text );
sqlite>
sqlite> select Items.ItemName
   ...> from Items
   ...> left outer join A on (A.Name =
Items.ItemName and Items.ItemName = 'dummy')
   ...> left outer join B on (B.Name = Items.ItemName)
   ...> where Items.Name = 'Parent';
Item1
Item2
sqlite>

I get the same result with v 3.8.8.2 from the download page

Regards,
Simon

Re: [sqlite] Possible to get table size (in bytes)?

[Simon Davies]

On 4 February 2015 at 18:26, Rael Bauer  wrote:
> Hi,
>
> Is it possible to get the information of how many bytes a table is taking up
> in the database?

For Windows, see http://www.sqlite.org/download.html#a10
There are other links for other operating systems

> Thanks
> Rael

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

Re: [sqlite] Update Statements using Sub query

[Simon Davies]

On 20 January 2015 at 13:09, MikeSnow  wrote:
> I was wondering if anyone could help
> I am trying to use Excel to create update statements based on certain
> criteria.
> For example...this works
> UPDATE CDR_Adjusted SET "DateTime"=DATE||" "||TIME;
> (This is what I need, a simple Concat
> 2013-10-11 7:59
> But when I try to do this
>
> UPDATE CDR_Adjusted SET "DateTime" =(
>SELECT [Column Name]
>  FROM Providers_Import
> WHERE Provider = 'abc'
>   AND
>   Version = '2013-2014'
>   AND
>   [Column Name] = 'Date'
>) || Time ;"

The subquery can never return any value other than 'Date', so your
update boils down to

UPDATE CDR_Adjusted SET "DateTime" ='Date' || Time;"

>
> I get this
> Date || Time
>
> Not the update, I would expect above
> (In the table Providers_Input, the value in the [Column Name] is 'Date')

You have not provided any information on sqlite version, or much about
table schemas, but if I attempt something that looks similar (to me) I
get:

SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table p( column_name text, provider text, version text );
sqlite>
sqlite> insert into p values( 'ttt', 'xxx', 'blah' );
sqlite> insert into p values( 'Date', 'abc', '2013-2014' );
sqlite> insert into p values( 't2', 'abc', '2013-2014' );
sqlite>
sqlite> select column_name from p where provider='abc' and
version='2013-2014' and column_name='Date';
Date
sqlite>
sqlite> create table a( datetime text, time text );
sqlite> insert into a values( '', 'time1' );
sqlite> insert into a values( '', 'time2' );
sqlite> select * from a;
|time1
|time2
sqlite> update a set datetime=(select column_name from p where
provider='abc' and version='2013-2014' and column_name='Date') ||
Time;
sqlite> select * from a;
Datetime1|time1
Datetime2|time2

I.e. the result that I would expect...

>
> Thanks is advance

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

Re: [sqlite] Is this date supposed to be less than or more than the other?

[Simon Davies]

On 3 January 2015 at 00:12, J Decker  wrote:
> Okay...
> https://www.sqlite.org/lang_datefunc.html
>
> https://www.sqlite.org/datatype3.html /* lists DateTime as a distinct type
> */

Could you point out where exactly

>
> I understand it's kept as a string... and there's no internal functions for
> this... but wasn't there a discussion to add hex and octal etc support for
> number conversions?  1.2Meg of stuff and you can't have a few 30 line
> functions to convert to gregorian calander day/second of day for
> calculation(julian day and second tick); amazing.

You have already quoted https://www.sqlite.org/lang_datefunc.htm

>
> --
> Since on the datefunc page
>
> Formats 2 through 10 may be optionally followed by a timezone indicator of
> the form "*[+-]HH:MM*" or just "*Z*". The date and time functions use UTC
> or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero
> "HH:MM" suffix is subtracted from the indicated date and time in order to
> compute zulu time. For example, all of the following time strings are
> equivalent:
>
> 2013-10-07 08:23:19.120
> 2013-10-07T08:23:19.120Z
> 2013-10-07 04:23:19.120-04:00
> 2456572.84952685
>
> Defines equivalency... I would have assumed that inequalities could also be
> done.

There is no datetime type - the value is stored as string, real or
integer. The datetime functions will convert strings, reals or
integers to a datetime value, which can be compared; for equivalence
or inequality.

>
>
> And since 'formats supported' are specified, one would assume that datetime
> columns with supported strings would work with at least =, >= <=, <, >, !=

There is no datetime type; compare the columns using the datetime functions.
I.e datetime( col1) < datetime( col2 )...

If you don't use the datetime functions, then columns containing
datetime STRINGS will be compared using the text collation specified.

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

Re: [sqlite] Using binding in sqlite insert statements

[Simon Davies]

On 9 December 2014 at 10:56, Prakash Premkumar  wrote:
> Hi,
>
> I'm trying to use the sqlite_bind function calls to pass values to the
> insert queries in sql.
>
> Here's the code I'm trying to execute: http://pastebin.com/kMvAHsrk
>
> I get an error after the sqlite3_step() function call :
> The error message is "Unknown error".

Check expected return value:
http://www.sqlite.org/c3ref/step.html

>
> Can you kindly help me fix this ?
>
> Thanks
> Prakash

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Davies]

On 10 November 2014 16:03, Don V Nielsen  wrote:
> Isn't this the result of the results cache?  The two queries are identical.
>

The query plan changes...

.
.
.
sqlite>  explain query plan select count(*) from v wherez = 0 and
   ...>   (   y between 100 and 1001000
   ...>   or  y between 200 and 2001000
   ...>   or  y between 300 and 3001000
   ...>   or  y between 400 and 4001000);
0|0|0|SEARCH TABLE v USING COVERING INDEX w (z=?)
sqlite>
sqlite>
sqlite> analyze;
sqlite>
sqlite>
sqlite>  explain query plan select count(*) from v wherez = 0 and
   ...>   (   y between 100 and 1001000
   ...>   or  y between 200 and 2001000
   ...>   or  y between 300 and 3001000
   ...>   or  y between 400 and 4001000);
0|0|0|SEARCH TABLE v USING COVERING INDEX w (z=? AND y>? AND y? AND y? AND y? AND y

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

Re: [sqlite] sqlite3.8.7 crashes when creating an index with WAL mode disabled

[Simon Davies]

On 4 November 2014 04:17, RP McMurphy  wrote:
> Consider the following build cmd line:
>
> gcc -o sqlite3.exe -DSQLITE_OMIT_WAL sqlite3.c shell.c
>
> The build succeeds when using MinGW on Windows.
>
> Now start the built exe with a memory database:
>
> sqlite3 :memory:
>
> And execute the following query:
>
> create table v(y);
> with recursive cnt(x) as (select 1 union all select x+1 from cnt 
> limit 10)
> insert into v select * from cnt;
> create index w on v(y);
>
> And watch as it crashes when creating the index.

>From https://www.sqlite.org/compile.html:

Important Note: The SQLITE_OMIT_* options do not work with the
amalgamation or with pre-packaged C code files. SQLITE_OMIT_*
compile-time options only work correctly when SQLite is built from
canonical source files.

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

Re: [sqlite] Inserting a row with all defaults set in table

[Simon Davies]

On 25 October 2014 14:49, Stephen Chrzanowski  wrote:
> I've got a table that has defaults set for all fields
>
> CREATE TABLE [tEvents] (
>   [EventID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>   [Airline] CHAR DEFAULT '',
>   [TicketID] INTEGER DEFAULT 0,
>   [Resolved] BOOL DEFAULT 0);
>
>
> Seems to me it'd be a bit redundant to do an "insert into tEvents
> (TicketID) values (0)" when the default is already set (And who says I may
> not want to change the default later to -1 for whatever reason?).
>
> The question is, how would I insert a blank row and rely on the defaults
> I've got in the schema?  "insert into tEvents () values ()" fails, as does
> removing the first pair of brackets, as well as removing all brackets.

INSERT INTO tEvents( EventId ) VALUES( null );

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

Re: [sqlite] how to join 3 tables from a sqlite database?

[Simon Davies]

On 25 March 2014 14:09, Christian Dallinger
 wrote:
> Hello, I want to join these 3 tables from my database:
>
> http://pastebin.com/f8FP0G0C

something like

select Macadress, timestamp, result, user, '', '', '', '' from checklog
union
select MacAdress, timestamp, '', '', '', error1, error2, errortext from error;
union
select MacAdress, timestamp, '', '', text, '', '', '' from service
order by timestamp

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

Re: [sqlite] How to write a query

[Simon Davies]

On 5 March 2014 10:24, Igor Korot  wrote:
> Hi, Simon,
>
.
.
.
>>
>> Possibly:
>> select v1, v2 from ( select 1 as ordr, f1 as v1, f2 as v2 from t union
>> all select 2, f3, f4 from t ) order by ordr, v1;
>>
>
> With UNION I will have 2 DB hits, correct?
> Meaning I execute the part on the left side of the UNION and then execute
> the right side of the UNION
> and then add the results together. Do I understand correctly?

I'm not sure. It is a single SQL statement

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

Re: [sqlite] How to write a query

[Simon Davies]

On 5 March 2014 10:04, Igor Korot  wrote:
> Hi, ALL,
> Let's say I have a table with following data:
>
> field1field2field3   field4
> 12  3 4
> 5   6   7 8
>
> How do I write a query which will produce the output as:
>
> 1 2
> 5 6
> 3 4
> 7 8
>
> Is it possible to write a single query which will produce the output like
> this?
> And not just in SQLite

Possibly:
select v1, v2 from ( select 1 as ordr, f1 as v1, f2 as v2 from t union
all select 2, f3, f4 from t ) order by ordr, v1;

>
> Thank you.

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

Re: [sqlite] Select with dates

[Simon Davies]

On 12 September 2013 06:34, jwzumwalt  wrote:
> I have not used the date function in select statements before.
> I have valid entries for the current month, what am I doing wrong?
>
> SELECT * FROM "entry" WHERE
> bankdate > date('now','end of month','-1 month')
> AND bankdate < date('now','start of month','+1 month')
>

I see no "end of month" modifier in http://www.sqlite.org/lang_datefunc.html
Why not
SELECT * FROM "entry" WHERE
   bankdate >= date('now','start of month')
  AND bankdate < date('now','start of month','+1 month')

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

Re: [sqlite] How to use aux_data effectively?

[Simon Davies]

On 6 September 2013 16:38, Pepijn Van Eeckhoudt  wrote:
> Hi,
>
> In the extension I'm developing (https://bitbucket.org/luciad/libgpkg)
> I'm currently adding support for queries like:
> select Distance(
>   GeomFromText('Point(13.457 3)'),
>   geometry
> ) from table;
>
> GeomFromText takes a string and outputs a geometry blob
> Distance takes two geometry blobs and returns a double
>
> In order to speed up the distance function I was wondering if I could
> use aux_data to cache the parsed version of the first parameter since
> this remains the same for every call. So far I haven't been able to get
> this to work though, aux_data is always NULL, no matter what I try.
>
> My hunch is that this is because the first parameter is the result of a
> function call which could in theory return different values for each row
> even if the input parameters are constant. Is that correct?
>
> Are there any other ways to kind of memoize the GeomFromText function
> (or the parameters to distance) besides aux_data?

select Distance( constGeom, geometry ) from table, (select
GeomFromText('Point(13.457 3)') as constGeom );

>
> Thanks,
>
> Pepijn
>

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

Re: [sqlite] v3.8 .import misbehaves

[Simon Davies]

On 28 August 2013 14:16,   wrote:
> OK, now copy the data line several times, and you'll see there are errors
> for several lines, unrelated to the final CRLF (which I removed this next
> sample).
>
> -- data --
> "Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> ---
>
> C:\temp>sqlite3.exe
> SQLite version 3.8.0 2013-08-26 04:50:08
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .sep ,
> sqlite> .import data tab
> data:3: unescaped " character
> data:4: unescaped " character
> data:5: unescaped " character
> data:6: unescaped " character
> data:7: unescaped " character
> data:2: expected 8 columns but found 43 - extras ignored
> sqlite>
>

Agreed.
The CR character seems not to be handled properly. Change CRLF to just
LF, and the import succeeds. Change CRLF to CR and same failure as
above.

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

Re: [sqlite] v3.8 .import misbehaves

[Simon Davies]

On 28 August 2013 13:51,   wrote:
> I did.  I just download the precompiled binaries for Windows, and this is
> what I see (for that sample data file):
>
> C:\temp>sqlite3.exe
> SQLite version 3.8.0 2013-08-26 04:50:08
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .sep ,
> sqlite> .import data tab
> data:2: unterminated "-quoted field
> sqlite>
>
>


I can confirm the behaviour with
C:\>xd -c data.txt
 0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
70: 2C 22 22 0D 0A   | ,""..
C:> sqlite3
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite>
sqlite> .import data.txt tab
data.txt:2: unterminated "-quoted field

But change file to:

C:\Joinerysoft\Software\sqlite\3_8_0>xd -c data.txt
 0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
70: 2C 22 22 | ,""

(remove trailing newline) and the import works with no errors.

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

Re: [sqlite] Number of Colum!!!

[Simon Davies]

On 13 August 2013 15:11, techi eth  wrote:
> Is their any limit on number of Colum in SQLite3 table?
>
> Cheers -
>
> Techi


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

Re: [sqlite] Query on Update or Selective Insert

[Simon Davies]

On 7 August 2013 15:25, Simon Slavin  wrote:
>
> On 7 Aug 2013, at 9:50am, techi eth  wrote:
>
>> I am trying to update 100 variables at a time & due to some of the reason 99
>> th or any mid index variable cause update to fail.
>>
>> In that case this query has to execute it again for the entire variable or
>> only for failed variable.
>
> If the whole of the UPDATE cannot be completed, none of the UPDATE is done.
>
> Simon.

If a conflict arises, none of the row is updated.
But see http://www.sqlite.org/lang_conflict.html. An update with
ignore conflict resolution can update rows even if some rows cause
conflict; those rows that cause conflict will be skipped.

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

Re: [sqlite] Query optimization: Checking for existence before performing action

[Simon Davies]

On 29 July 2013 12:57, Navaneeth.K.N  wrote:
> Hello,
>
> I am trying to optimize the SQL calls that my application makes. I
> have a scenario where words are inserted into a table. Now each word
> will have a column called "confidence". There is a unique primary key
> on "word".
>
> When inserting a word, first I check if the words exists by performing
> a "select" query. If it exists, I fire an update query to increment
> the confidence for that word.  If word is not available, I fire an
> insert query to insert the word.
>
> In both the cases, I can't skip doing two queries. One for checking
> existence and second for updating or creating. I am wondering is there
> an easy way to solve this by just doing one query? I have tried
> "insert or replace", but I can't use that as it changes the rowid's.
>
> Any help would be great!

Something like:

SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table words( id text unique, confidence integer default 0 );
sqlite> create trigger words_before_insert before insert on words
   ...> begin update words set confidence=confidence+1 where id=new.id;
   ...> end;
sqlite>
sqlite> select * from words;
sqlite>
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'bill' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'joe' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite> insert or ignore into words( id ) values( 'fred' );
sqlite>
sqlite> select * from words;
fred|4
bill|0
joe|1
sqlite>

>
> --
> Thanks
> Navaneeth

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

Re: [sqlite] Access Permission on Table

[Simon Davies]

On 22 July 2013 10:02, techi eth  wrote:
> Is their any statement I can build using SQLite3, which will provide me
> access permission over table.
>
> Let us say I have 3 tables (table 1, table 2, table 3). Here I am looking
> Read permission for one->many but Write can be done by creator of table
> only. If other will try to access then they couldn't be able to do & error
> will be thrown.

Perhaps http://www.sqlite.org/c3ref/set_authorizer.html helps...

>
> Cheers-
>
> Techi

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

Re: [sqlite] How to select from a temp table with same name as a main table.

[Simon Davies]

On 17 June 2013 23:19, dochsm  wrote:
> I have a table in the main database called 'students' and a two page block of
> sql that processes it, referring to it as simply 'students'.
>
> Experimenting with a different approach, I created a temp table, also called
> 'students' that contained a subset of the main.students. (I intended to
> modify the sql, replacing 'students' with 'temp.students' but have not yet
> done so)
>
> However, running my original sql now, before editing it, it appears to be
> using both the table 'students' and the table temp.students as it is now
> repeating some rows.
>
> Can sombody please explain what might be happening?
> If no database prefix is given, doesn't SQLite default to the 'main'
> database or does it somehow use a combination of all the tables with the
> same name regardless of database?

Does the following output give a clue?

SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t( data text );
sqlite> create temp table t( data text );
sqlite> insert into t values( 'main' );
sqlite> insert into temp.t values( 'temp' );
sqlite>
sqlite>
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
sqlite> select * from temp.t;
main
temp
sqlite> insert into main.t values( 'really main' );
sqlite> select * from t;
main
temp
sqlite> select * from main.t;
really main
sqlite> select * from temp.t;
main
temp
sqlite>

It looks like the temp db is searched first for a match of table name...

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

Re: [sqlite] getting transactions working with prepare, step & finallise

[Simon Davies]

On 12 June 2013 16:50, e-mail mgbg25171  wrote:
> Tbank you in anticipation for any forthcoming advice
> I include my program below in Powerbasic (not disimilar to C)
> and wonder what I need to do in order to "wrap"
> multiple statements (which use prepare,step & finalise) as transactions?
>
.
.
.
.
>
> FUNCTION exec_sql( _
>hDB AS DWORD, _
>hD AS DWORD, _
>BYVAL pzSql AS ASCIZ PTR, _
>BYVAL pzErr_msg AS ASCIZ PTR _
>) AS LONG
>
>LOCAL lresult   AS LONG
>LOCAL pzTailAS ASCIZ PTR
>LOCAL pzPreparedAS ASCIZ PTR
>LOCAL pzStmtAS ASCIZ PTR
>LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
> column name strings
>LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
> column values
>LOCAL ncols AS LONG   ' the column count
>LOCAL nErmsgAS LONG
>LOCAL szErmsg   AS ASCIZ * 512
>LOCAL nRetryAS LONG
>LOCAL s AS STRING
>LOCAL i, l  AS LONG
>
>lresult = %SQLITE_OK
>
>IF @pzSql = "" THEN
>   FUNCTION = %SQLITE_OK
>   EXIT FUNCTION ' called with null SQL statement
>END IF
>
>lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )

Your exec_sql function is passed multiple sql statements in a single
string, but you are only ever preparing the first statement.

Reread http://www.sqlite.org/c3ref/prepare.html with particular focus on pzTail

.
.
.

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

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Simon Davies]

On 13 May 2013 16:52, Simon Slavin  wrote:
>
> On 13 May 2013, at 3:54pm, Григорий Григоренко  wrote:
>
>> sample database is:
>>
>> PRAGMA FOREIGN_KEYS=1;
>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name);
>> INSERT INTO cat VALUES (1, 'Alice');
>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id));
>> INSERT INTO owner VALUES(1);
>>
>> This script fails to drop tables with  'foreign key constraint failed':
>>
>> SAVEPOINT edit;
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> RELEASE edit;
>> PRAGMA FOREIGN_KEYS=1;
>
> You are DROPping your tables in the wrong order.  The 'owner' table refers to 
> the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' table 
> which refers to a table which doesn't exist.  Swap the order of the DROPs and 
> your database will not risk that kind of disaster.
>
>> This script works OK:
>>
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> PRAGMA FOREIGN_KEYS=1;
>
> Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
> you're going to do the RELEASE.  If you left out the second DROP, then issued 
> a RELEASE, the database would be corrupt, so SQLite issues the error message 
> to warn you about it.  The version without the SAVEPOINT never has to worry 
> about you doing that.
>
> Simon.

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

"...foreign key constraint enforcement may only be enabled or disabled
when there is no pending BEGIN or SAVEPOINT. "

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

Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)

[Simon Davies]

On 17 April 2013 14:39, J Trahair  wrote:
> Hi everyone
>
> Can my application lock the database for its own exclusive use at the 
> beginning of a series of INSERTs UPDATEs and SELECTs, then unlock it again 
> afterwards? I've read about the five locking states (unlocked, shared, etc.) 
> but I assume the dll handles all that at INSERT or UPDATE time. I'm looking 
> for a way to ensure other users aren't allowed to write to the database 
> during this quarter-second period.

http://www.sqlite.org/lang_transaction.html
BEGIN EXCLUSIVE

>
> Thanks in advance.
>
> Jonathan Trahair

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

Re: [sqlite] FreeBSD and SQLITE_ENABLE_LOCKING_STYLE

[Simon Davies]

On 11 April 2013 09:03, Baptiste Daroussin  wrote:
> Ok so I was not going in the right direction :)
>
> So how it is possible to corrupt a database on nfsv4? Does that ring a bell
> to someone?

In case you have not already seen it:
http://www.sqlite.org/lockingv3.html#how_to_corrupt

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

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

[Simon Davies]

On 19 March 2013 15:08, rol...@logikalsolutions.com
 wrote:
> version 3.7.9 2011-11-01 00:52:41
> KUbuntu 12.04 LTS 32-bit
>
> ex1.sql
>
> CREATE TABLE table_ex1(
> Groupid INTEGER PRIMARY KEY,
> GroupName TEXT
> );
> .separator '|'
> .import ex1.csv table_ex1
> .quit
>
> ex1.csv
> 1|NONE
> 2|TWO
> 3|THREE
> 4|FOUR
> 5|FIVE
>
> sqlite> .read ex1.sql
> Error: datatype mismatch
> sqlite> .quit
>
> ex2.sql
>
> CREATE TABLE table_ex1(
> Groupid INT PRIMARY KEY,
> GroupName TEXT
> );
> .separator '|'
> .import ex1.csv table_ex1
> .quit
>
>
> sqlite> drop table table_ex1;
> sqlite> .read ex2.sql
> sqlite> .quit
>
>
> Makes absolutely no sense.  INT and INTEGER should be exactly the same.  For
> this limited set of data both should have worked.

INT and INTEGER are not exactly the same:
http://www.sqlite.org/lang_createtable.html#rowid

In version 3.7.15.1 your example does not produce the "datatype mismatch" error.

>
> Roland Hughes, President

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

Re: [sqlite] insert / replace into joined tables.

[Simon Davies]

On 1 March 2013 13:29, chas cartmel  wrote:
> Hi guys/gals.
>
> First post to this group.
>
> I have a problem which can be solved in Access/ SQL Server but not seemingly
> in SQLite. I am trying to update a value in a table from another identical
> table where that value has been updated elsewhere. Do not want to delete
> insert and the original entry may have custom information.
>
> The working MS Access SQL :
>
> UPDATE aircraft INNER JOIN datatemp ON aircraft.ModeS = datatemp.newModeS
> SET aircraft.Registration = [datatemp.newregistration]
>
> WHERE (((aircraft.Registration)=".NO-REG") AND
> ((datatemp.newRegistration)<>".NO-REG" And (datatemp.newRegistration) Is Not
> Null));
>
> Works in access, but fails in SQLite as inner joins on updates not allowed.

Is

UPDATE aircraft SET registration =
( SELECT newRegistration FROM datatemp WHERE newModeS=ModeS LIMIT 1 )
WHERE   registration='.NO-REG' AND
   ModeS IN
  ( SELECT newModeS FROM datatemp
WHERE newRegistration IS NOT NULL AND
 newRegistration <> '.NO-REG' );

what you want?

.
.
.

>
> Thanks
> Charlie
>

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

Re: [sqlite] got selected items ok but can't update using them

[Simon Davies]

On 20 February 2013 11:36, e-mail mgbg25171  wrote:
> After a bit of a struggle I've got the fields I want but I'm failing to use
> them in an update operation.
> This
>
> SELECT i,o,n FROM
> (
> SELECT alias_id i,
> (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> (SELECT itm FROM std_itms where ID = std_id) n
> FROM
> (SELECT std_id, alias_id FROM alias_itms)
> )
>
> works fine and gives me
> i, o, n  where i, o, n equals id, old, new
> 
> 0, turnover, sales
> 1, cost of sales, cogs
>
> now I want to say
> update stmnts set itm = n where id = i
> but I'm getting errors

It would help to be clear what the errors are.

>
> Table fields
> stmnts__id, orig_itm, itm
> alias_itmsid, std_id, alias_id
> std_itms_id, itm
>
> Any help much appreciated

Maybe:
update stmnts set itm=(select itm from std_itms s, alias_itms a on
s.id=a.std_id where a.alias_id=stmnts.id);

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

Re: [sqlite] about delete problem

[Simon Davies]

On 16 January 2013 08:16, YAN HONG YE  wrote:
> create table mytable( ID integer primary key autoincrement, name nvarchar(32) 
> );
> insert into mytable(name) values("aa1");
> insert into mytable(name) values("aa2");
> insert into mytable(name) values("aa3");
> insert into mytable(name) values("aa4");
> insert into mytable(name) values("aa5");
> insert into mytable(name) values("aa6");
> delete from mytable where id>3;
> insert into mytable(name) values("aa7");
> insert into mytable(name) values("aa8");
> select * from mytable;
> 1|aa1
> 2|aa2
> 3|aa3
> 7|aa7
> 8|aa8
>
> I wish after I  delete any rows from mytable, and then when I insert into any 
> row into mytable, The Id should follow the last exists in mytable.
> in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
> then when I add any column into mytable, the ID should from 3 to
> the next column, how to do this? maybe have any rubbish in the database when 
> delete from mytable, how to clean that rubbish?

Try:

create table mytable( ID integer primary key, name nvarchar(32) );

and read up on "integer primary key" and "autoincrement" on the SQLite web-site

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

Re: [sqlite] special aggregate feature vs query optimization

[Simon Davies]

On 10 January 2013 17:04, Eduardo Morras  wrote:
> On Thu, 10 Jan 2013 14:53:52 +0100
> E.Pasma  wrote:
>
>> Hello,
>>
>> A query of the form: "SELECT max(x), y FROM table" returns the value
>> of y on the same row that contains the maximum x value.
>
> True
>
>> I just want to point to a construction where one would expect this to
>> work however it does not. I tried a query that returns only the value
>> of y and intuitively wrote:
>>
>> select y from (select max(x), y from t);
>>
>
> Select max(x), y from t will return 2 colums and n rows, the first column 
> with the same value, the maximum of x in table t, the second column all t.y 
> values. Something like this:
>
> max(x) |   y
> -
> 500|  5
> 500|  3
> 500|  9
> 500|  2
> 500|  31
> 500|  1
> 500|  86
> 500|  64

Not what I see...

>
> From this result table, you are doing select y from (result table) and 
> getting only the y values as you expected
>
>   y
> -
>   5
>   3
>   9
>   2
>   31
>   1
>   86
>   64
>
>> This however no longer returns the value of y corresponding to the
>> maximum x.
>
> For me it works, it shows all y from t. Perhaps i have misunderstood something

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( x integer, y integer );
sqlite>
sqlite>
sqlite> insert into t values( 1, 2 );
sqlite> insert into t values( 4, 3 );
sqlite> insert into t values( 10, 5 );
sqlite> insert into t values( 2, 6 );
sqlite>
sqlite> select max(x),y from t;
10|5
sqlite> select x,y from (select max(x) x, y from t);
10|5
sqlite> select y from (select max(x) x, y from t);
6

This does not look right...

>
>>
>> It looks a consequence of query optimization. The query satisfies all
>> conditions for "Query Flattening" in http://www.sqlite.org/optoverview.html
>>   . The max(x) column is then eliminated.
>>
>> Hope this is useful to know for who is using the feature.
>>
>> EPasma
>>
>
> ---   ---
> Eduardo Morras 

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

Re: [sqlite] Result of multi-command _exec() that errors ?

[Simon Davies]

On 4 December 2012 11:02, Simon Slavin  wrote:
> (Yes, I know I should be preparing and binding for security reasons, but that 
> doesn't work here.)
>
> What is the result when using _exec() on multiple statements when one of the 
> statements errors ?  I read this page:
>
> 
>
> and I can't figure out whether execution continues to following statements 
> after one of them errors or not.  Assume, for the sake of argument, that I'm 
> not using any callbacks or hooks.

3rd paragraph:
"If an error occurs while evaluating the SQL statements passed into
sqlite3_exec(), then execution of the current statement stops and
subsequent statements are skipped."

>
> Simon.


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

Re: [sqlite] What is the easiest way of changing the field type?

[Simon Davies]

On 3 December 2012 19:29, Igor Korot  wrote:
> Simon,
>
> On Mon, Dec 3, 2012 at 11:03 AM, Simon Slavin  wrote:
>>
>> On 3 Dec 2012, at 6:53pm, Igor Korot  wrote:
>>
>
> Here is the session:
>
> sqlite> CREATE TABLE test(id1 integer, id2 double);
> sqlite> INSERT INTO test( 1, 1.0 );
> Error: near "1": syntax error
> sqlite> INSERT INTO test VALUES( 1, 1.0 );
> sqlite> INSERT INTO test VALUES( 2, 2.0 );
> sqlite> INSERT INTO test VALUES( 3, 3.0 );
> sqlite> .timer ON
> sqlite> SELECT * FROM test;
> 1|1.0
> 2|2.0
> 3|3.0
> CPU Time: user 0.00 sys 0.00
> .output testdump.log
> .dump test
> sqlite> SELECT * FROM test;
> CPU Time: user 0.00 sys 0.00
> sqlite> DROP TABLE test;
> CPU Time: user 0.00 sys 0.00
> sqlite> .read testdump.log
>
> Error: incomplete SQL: 1|1.0
> 2|2.0
> 3|3.0
> sqlite> SELECT * FROM test;
> CPU Time: user 0.00 sys 0.00
> sqlite>
>
> I didn't get errors when I did that with my table. Other than that its all
> the same.
>
>
> Thank you.
>
>>

After .dump test, add .output stdout, and try again.

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

Re: [sqlite] What is wrong with this update SQL

[Simon Davies]

On 22 November 2012 11:20, Staffan Tylen  wrote:
> I run this (sql.txt):
>
> create table table1 (age_band);
> insert into table1 values(31);
> UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
> when age_band BETWEEN 11 AND 20  then ' 11 - 20' when age_band BETWEEN
> 21 AND 30  then ' 21 - 30' when age_band BETWEEN 31 AND 40  then ' 31
> - 40' when age_band BETWEEN 41 AND 50  then ' 41 - 50' when age_band
> BETWEEN 51 AND 60  then ' 51 - 60' when age_band BETWEEN 61 AND 70
> then ' 61 - 70' when age_band BETWEEN 71 AND 80  then ' 71 - 80' when
> age_band BETWEEN 81 AND 90  then ' 81 - 90' when age_band BETWEEN 91
> AND 100  then ' 91 - 100' when age_band > 100 then ' over 100' else
> age_band end);
> select * from table1;
>
> and get this:
>
> SQLite version 3.7.15 2012-11-09 21:40:02
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read sql.txt
> age_band
> --
>  31
> - 40
>
> To sure what might be causing the line break, it only seems to happen for
> this age band.

The literal selected contains the line break

sqlite> select '31
   ...> - 40';
31
- 40

sqlite> select '31 - 40';
31 - 40
sqlite>


> Staffan
>


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

Re: [sqlite] how to use julianday function

[Simon Davies]

On 9 November 2012 02:42, YAN HONG YE  wrote:
> sqlite> select 
> fmn,DATE('NOW'),supplierDate,julianday('now'),julianday(supplierDate),julianday('supplierDate')
>  from t93c_adl where supplierDate>julianday('now') limit 5 ;
> 854954|2012-11-09|12/01/2012|2456240.61186149||
> 854952|2012-11-09|03/01/2013|2456240.61186149||
> 635604|2012-11-09|05/27/2011|2456240.61186149||
> 635605|2012-11-09|03/16/2012|2456240.61186149||
> 635607|2012-11-09|09/15/2012|2456240.61186149||

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

 ISO-8601 format for date strings

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

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 7

[Simon Davies]

On 9 November 2012 01:40, YAN HONG YE  wrote:
> select date( supplierDate, '+2 days' ) ;
> use this cmd couldn't solve the problem;
> will have nothing on this column:
>
> sqlite> select fmn,'84 day'+supplierDate from t93c_adl where fmn like 
> '85493%';
> 854939|95
> 854938|95
> 854937|95
> sqlite> select fmn,date('84 day'+supplierDate) from t93c_adl where fmn like 
> '854
> 93%';
> 854939|-4712-02-27
> 854938|-4712-02-27
> 854937|-4712-02-27
> sqlite> select fmn,date(supplierDate) from t93c_adl where fmn like '85493%';
> 854939|
> 854938|
> 854937|
> sqlite> select fmn,supplierDate from t93c_adl where fmn like '85493%';
> 854939|11/15/2012
> 854938|11/30/2012
> 854937|11/30/2012

The dates you are trying to process are not ISO-8601 standard
The link provided earlier to the date and time functions indicate that
the textual date/time must conform to (a subset of)  ISO-8601

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

Re: [sqlite] couldn't use is null function

[Simon Davies]

On 9 November 2012 01:49, YAN HONG YE  wrote:
> sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE 
> IMPLANTATIONgrading i
> s not null and fmn like '85495%';
> 854954|R
> 854952|  
> //-here is 
> NULL, but I don't know why it shows in the not null result

What you are claiming is null is an empty string; they are not the same thing.

> 854951|R
> 854953|R
> 854950|R
> 854959|G
> 854957|
> 854955|R
> 854956|R
> 854958|

http://en.wikipedia.org/wiki/Null_(SQL)

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

Re: [sqlite] finding duplicate records i.e. records with same values across 4 colums...

[Simon Davies]

On 8 November 2012 11:23, e-mail mgbg25171  wrote:
> I'm using SQLIte.
> All columns can have duplicate values but it would be helpful to report
> on those rows which are identical across all columns.
> More specifically I'm looking for matching itm values where the first 3
> cols ALSO match but am not sure of the sqlite select query to do this.
>
> epic, yr, statement, itm
> ==
> mcro, 2002, income, revs
> mcro, 2002, income, cogs
> mcro, 2002, income, sg
> mcro, 2002, income, cogs
> mcro, 2003, balance, gdwil
> etc
>
> in the example I'm looking for a query that would return
> mcro, 2002, income, cogs
> mcro, 2002, income, cogs

select * from t natural join ( select epic, yr, statement, itm from t
group by epic, yr, statement, itm having count(*) > 1 ) t1;

>
> any help much appreciated
>
> BTW no field can be unique in the createtable statement


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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[Simon Davies]

On 7 November 2012 20:36,  <stahl...@dbs.uni-hannover.de> wrote:
> Quoting Simon Davies <simon.james.dav...@gmail.com>:
>
.
.
.
>
>> I think this is the documented behaviour:
>> http://www.sqlite.org/datatype3.html
>>
>> tab1.id has integer affinity, and '42' is coerced to integer
>> tab2.id has none affinity, and '42' is not coerced
>
>
> Hmm... I see what you mean:
> Point 3 under 2.1 states that "if no type is specified then the column has
> affinity NONE."
>
> However, I find a foreign-key-clause counting as "no type specified" is at
> least a bit irritating. After all the type could be inferred from the
> reference. :-/
>
> Also, ironically, the documentation claims that "The dynamic type system of
> SQLite is backwards compatible with the more common static type systems of
> other database engines...".
>
> I just checked the example with the Oracle and PostgreSQL instances I have
> at hand here:
> Oracle does what I think is correct and returns a row in both cases.
> PostgreSQL does not allow the "id REFERENCES" construction at all and
> requires a data type even for foreign keys.
>
> So in this case SQLite is incompatible with two major DBMSes. :-)

Which from what you have said, are also incompatible with each other!

>
> Anyway, thanks for your help, Simon!
>
> kind regards,
>
> Christian
>

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

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[Simon Davies]

On 7 November 2012 16:41,   wrote:
> Hi!
>
> I have encountered inconsistent behavior regarding indirectly defined
> columns.
>
> In the following example:
>
> CREATE TABLE main ( id INTEGER PRIMARY KEY );
> CREATE TABLE tab1 ( id INTEGER REFERENCES main, str VARCHAR(10) );
> CREATE TABLE tab2 ( id REFERENCES main, str VARCHAR(10) );
>
> Table 'tab2' defines column 'id' indirectly by referring to the primary key
> of table 'main'.
> This is accepted by SQLite, and usually tables 'tab1' and 'tab2' behave the
> same (as they should).
>
> But assuming these rows:
>
> INSERT INTO tab1 VALUES ( 42, 'foo' );
> INSERT INTO tab2 VALUES ( 42, 'foo' );
>
> The following two queries return different results:
>
> SELECT * FROM tab1 WHERE id = '42'; -- returns 1 row
> SELECT * FROM tab2 WHERE id = '42'; -- returns no rows
>
> I understand that the coercion from string '42' to number 42 plays a role
> here.
> (Maybe the data type of column 'id' in table 'tab2' cannot be inferred.)
> But I do expect both queries to give the same result (be it 1 row, no rows
> or even an error for trying to use string '42' as a number).
>
> Best regards,
> Christian
>

I think this is the documented behaviour:
http://www.sqlite.org/datatype3.html

tab1.id has integer affinity, and '42' is coerced to integer
tab2.id has none affinity, and '42' is not coerced

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

Re: [sqlite] FW: about date question

[Simon Davies]

On 7 November 2012 09:42, YAN HONG YE  wrote:
> the table had a column: SupplierDate Date
> I wanna add 84 days :
> SELECT SupplierDate+84 as date1  FROM ADL;
> BUT result have noting
> when use:
> select date(supplierdate+'2 day') from t93c_adl limit 3;
> the result is:
> -4713-12-08
> -4713-11-29
> -4713-12-01
>

select date( supplierDate, '+2 days' )

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

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

Re: [sqlite] couldn't use is null function

[Simon Davies]

On 7 November 2012 08:27, YAN HONG YE  wrote:
> SELECT * FROM ADL WHERE Project_grading is null;
> can't select anything.

sqlite> create table ADL( id integer primary key, data text,
Project_grading integer );
sqlite> insert into ADL( data, Project_grading ) values( '1', 1 );
sqlite> insert into ADL( data, Project_grading ) values( '2', 2 );
sqlite> insert into ADL( data ) values( '3' );
sqlite> insert into ADL( data, Project_grading ) values( '4', 4 );
sqlite> insert into ADL( data ) values( '5' );
sqlite> insert into ADL( data, Project_grading ) values( '6', 6 );
sqlite>
sqlite> select * from ADL;
1|1|1
2|2|2
3|3|
4|4|4
5|5|
6|6|6
sqlite>
sqlite> select * from ADL where Project_grading is null;
3|3|
5|5|

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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Simon Davies]

On 2 November 2012 13:08, Григорий Григоренко  wrote:
>
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
>
>>
> Please read
>>
>>
> 
>>
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
> functions that modify or format date values.
>
> It's about having useful shortcut for getting current moment that doesn't 
> have (string) parameters and so can be easily remembered and typed.
>
> Compare:
> MS SQL: CURRENT_TIMESTAMP
> PostgreSQL: now()
> Oracle: sysdate
>
> To:
> Sqlite: strftime('%s','now')

datetime() will give current date and time
likewise date(), time() etc

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

Re: [sqlite] primary key with bulk insert (UNION SELECT)

[Simon Davies]

On 11 October 2012 15:07, Alan Frankel  wrote:
> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
>
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> VARCHAR(25), distance REAL);
> sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as 
> distance UNION SELECT 'Procyon', 500;
> Error: table CelestialObject has 3 columns but 2 values were supplied
>
> If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
> AUTOINCREMENT") when I create the table, the error is the same. Can anyone 
> tell me whether there's a way to use a bulk insert without specifying an id 
> for each row?

insert into CelestialObject( name, distance ) select 'Betelguese' as
name, 200 as distance UNION SELECT 'Procyon', 500;

>
> Thanks,
> Alan
>

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

Re: [sqlite] Size

[Simon Davies]

On 4 October 2012 14:06, Alami Omar  wrote:
> Hello, does SQLite format files have any size(fie size of the sqlite file)
> info on the header ? Thank you.

http://www.sqlite.org/fileformat.html#filesize

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

Re: [sqlite] export html error

[Simon Davies]

On 21 September 2012 10:35, Kevin Benson  wrote:
> On Fri, Sep 21, 2012 at 4:30 AM, YAN HONG YE  wrote:
>
>> C:\t9>sqlite3 -html -header t9_engine.db "select id,partnumber,'img
>> src=
>> t'||pic||'height=120px',pcs from engine where id>7 " >> n.html
>>
>
> C:\t9>sqlite3 -html -header t9_engine.db "select id,partnumber,'img
> src='||pic||'height=120px',pcs from engine where id>7 " >>
> n.html
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N

Or
C:\t9>sqlite3 -html -header t9_engine.db "select
id,partnumber,'imgsrc='||pic||'height=120px',pcs
from engine where id>7 " >>n.html

to fix the specific error message...

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

Re: [sqlite] Foreign Key

[Simon Davies]

On 7 September 2012 21:28, Dennis Klein
 wrote:
> Hallo,
>
> I am Using the latest Version of the precompiled shell Application from the 
> Download section, and the C/C++ Sources in Version 3.7.11
>
> Now i created my tables, using Primary Key and Foreign Key Constraints. Then 
> I tested it, and inserted test data, with a foreign key which doesn't exist 
> as primary key in the referenced table. This worked. I also added valid data, 
> and tested the "ON UPDATE" and "ON DELETE" constraints. So if i delete a 
> entryset in the referenced table, the foreign key isn't set null.
>
> Is there a bug?

Did you do
pragma foreign_keys=on;
?

http://www.sqlite.org/foreignkeys.html#fk_enable

They are off by default.

>
> Bye Dennis Klein

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

Re: [sqlite] query function release problem

[Simon Davies]

On 2 August 2012 08:28, YAN HONG YE  wrote:
.
.
.
> when I use command
> " jii select ccc"
> it shows a problem:
>
> "jii.exe has encountered a problem and needs to close.  We are sorry for the 
> inconvenience."
>
> I couldn't resolve it.
>
> void query_table(sqlite3 *l_db)
> {
> char **result;
> char buffer2[324]="0";
> int row,column;
> char *sql;
>
> char *error;
> int i;
>
> sprintf(sql,"select * from  myprivate;");

Perhaps you intended sql to have been malloc'd here...

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

Re: [sqlite] C++ - Finalizing my SQLite interface

[Simon Davies]

On 27 July 2012 15:22, Arbol One  wrote:
> Before calling the destructor, I would like to make sure that all the
> sqlite3_stmt have been 'finalized', is there a function in SQLite that that
> can help me do this, or should I just use 'NULL'?

The documentation is there to help you...
http://www.sqlite.org/c3ref/next_stmt.html
http://www.sqlite.org/c3ref/finalize.html

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

Re: [sqlite] how many rows deleted

[Simon Davies]

On 12 July 2012 00:21, deltagam...@gmx.net  wrote:
> Can I retrieve how many rows  are affected from a delete statement like
> std::string delete_stmt("DELETE FROM mylog WHERE
> strftime('%Y-%m-%d',thedate) BETWEEN ? AND ? ");
>

http://www.sqlite.org/c3ref/changes.html

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

Re: [sqlite] cann't work

[Simon Davies]

On 11 July 2012 10:00, YAN HONG YE  wrote:
> two same structure tables, when use this sql:
> insert into table2  values(select * from table1 where filename like "%55");
>
> but show error: sqlite error 1 - near "select": syntax error

insert into table2 select * from table1 where filename like "%55";

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

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

Re: [sqlite] How to use variables in statements ?

[Simon Davies]

On 13 June 2012 10:40, deltagam...@gmx.net  wrote:
> Im using c++ and like to write from within c++ into sqlite3 db.
> I saw some tutorials on the sqlite pages, but the
> statements have always been defined in advance and they have been constant.
>
> How to add now new data to sqlite if you dont know in advance the data ?
> I think the statement has to be build somehow with variables or binding
> (maybe better because of sql injection ? ).
>
>
> I would appreciate it if someone has some example.

http://sqlite.1065341.n5.nabble.com/Simple-Sample-Code-Linked-td43646.html

>
>
> Thx in advance.
>

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

Re: [sqlite] Query by License -Consulta por Licencia

[Simon Davies]

2012/5/16 Guillermo Soland :
> Mr. Representative of SQLite:
>
>
> Hello, my name is Guillermo G. Soland, I live in the city of Villa
> Constitución, Santa Fe, Argentina, I graduated as a Computer Systems Analyst,
> but now I do not practice my profession for profit because my job is totally
> alien to what I studied , he wanted to see if I am allowed to include a file
> SQLite database in an application that I developed, and if so if I'm allowed
> to include with the application to apply for registration under any license,
> either GPL or Copyright (preferably the latter).

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

> Motivates this reason that I like to develop applications (as amateur) to
> users or public organizations, usually non profit with the sale of software
> (do not sell, are free), but I would ever write a history that I have I
> performed.
> From already thank you very much.
>
> Signed:  Guillermo G. Soland

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

Re: [sqlite] Having in prepared statement with parameters

[Simon Davies]

On 9 May 2012 14:17, Pavel Ivanov  wrote:
>> As the last three statements are exactly the same (in meaning).
>> Can someone explain what's happening?
>
> I think you've got trapped by double value storage imprecision.
> Comparing double values for exact equality is pretty dangerous - two
> numbers can differ in one last bit, be not equal and be displayed
> identically when printed in decimal form.
>
>
> Pavel
>

I rather think that it is an affinity issue:

http://www.sqlite.org/datatype3.html#affinity
and
http://www.sqlite.org/datatype3.html#comparisons

Consider:

sqlite> select 123.456='123.456';
0
sqlite> select cast( 123.456 as real ) = '123.456';
1
sqlite> select 123.456 = 0+'123.456';
1

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

Re: [sqlite] ROLLBACK TO savepoint cancels earlier RELEASEs

[Simon Davies]

On 9 May 2012 10:33,   wrote:
> Hi All,
>
> 1: SAVEPOINT 1
> 2: insert A
> 3: RELEASE SAVEPOINT 1
> 4: SAVEPOINT 2
> 5: insert B
> 6: ROLLBACK TO SAVEPOINT 2
>
> ROLLBACK in line 6 cancels also line 2 in addition to line 5.
>
> I want to cancel just line 5.
>
> What am I doing wrong ?
>
> br,
>
> Esko

On Win7 32 bit:

SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table t( id integer primary key, data text );
sqlite> savepoint a;
sqlite> insert into t( data ) values( '1' );
sqlite> release savepoint a;
sqlite> savepoint b;
sqlite> insert into t( data ) values( '2' );
sqlite> rollback to savepoint b;
sqlite> select * from t;
1|1
sqlite>
sqlite> commit;
sqlite> select * from t;
1|1
sqlite>

I see no problem. Perhaps you can expand on the environment/version in
which you are trying this

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

Re: [sqlite] SQlite exclusive table lock

[Simon Davies]

On 3 May 2012 20:29, Harnek Manj  wrote:
> Hi Simon,
>
> Yes I have multiple Threads which are accessing the database. Currently if I 
> am doing a write operation the whole database file is locked, I want the 
> locking applied only to the table in operation.
>
> So currently from .net code I just say BeginTransaction(ReadCommitted) and do 
> a write operation this locks the complete database file and the write 
> operations are really long. So currently I want to solve the
> locking issue by bringing them down to table locks.

http://www.sqlite.org/whentouse.html
look at last paragraph - SQLite locks the whole database file.

>
> You mentioned "BEGIN EXCLUSIVE TRANSACTION", but I don't see where I can set, 
> that I want to start a transaction with exclusive lock, when I call 
> BeginTransaction on the connection.
>
> Thanks
> Harnek
>

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

Re: [sqlite] Is it possible to preclude the latest sqlite 3.7.11 Windows warning message?

[Simon Davies]

On 23 April 2012 13:04, Frank Chang  wrote:
>
>
>       Good morning, We are trying to compile  the latest SQLITE 3.7.11 
> release but we keep getting the Windows Visual Studio 8 warning message:  
> warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 
> 'AreFileApisANSI' is not static, identity not guaranteed showm below.
>       Is it possible to preclude and understand the meaning of this warning 
> message? Thank you.
>
> e:\users\frank\dqt_memorymap\sqlite\sqlite3.c(32329) : warning C4232: 
> nonstandard extension used : 'pCurrent' : address of dllimport 
> 'AreFileApisANSI' is not static, identity not guaranteed

A quick google shows http://msdn.microsoft.com/en-us/library/9a1sy630.aspx

Would using /Ze instead of /Za do what you want?

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

Re: [sqlite] help

[Simon Davies]

2012/4/23 Ковригина Ольга :
> Hello,support!
> Tell me how to resize columns in a table?
> I do like this:
> ALTER TABLE t1 MODIFY (c1 TYPE VARCHAR(2500));
> or
> ALTER TABLE t1 ALTER (c1 TYPE VARCHAR(2500));
> But it does not work.
>

The column does not have a size:
http://www.sqlite.org/datatype3.html

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

Re: [sqlite] sqlite3_transaction can't work

[Simon Davies]

On 5 April 2012 09:44, YAN HONG YE  wrote:
> sqlite3_transaction trans(db); can't work in c, I don't know why?

Give us a clue: what are you expecting, and what actually happens...

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

Re: [sqlite] SQLITE Binding Issue when using [] brackets

[Simon Davies]

On 1 April 2012 21:23, ap  wrote:
>
> http://sqlite.org/c3ref/bind_blob.html
>
> Brackets are recommended for attribute names in some cases, it seems the
> binding for :VVV does not permit :[VVV]. Is this the case?

Square brackets are a quoting mechanism for identifiers supported for
compatibility with MS Access and SQL Server.
(http://www.sqlite.org/lang_keywords.html)

>
> I prefer the dictionary binding if possible. Normally no issues, however
> when I introduce brackets there may be an issue. Is this the appropriate
> syntax?  ([c1],[c2]) VALUES ( :[c1], :[c2] )

I don't think so. The column names are c1 and c2 - the square brackets
are a quoting mechanism, not part of the name.
Try  ([c1],[c2]) VALUES ( :c1, :c2 )

>
 recdct
> {'[c1]': 1, '[c2]': 2}
>
 cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :[c2]
 )""",recdct)
> Traceback (most recent call last):
>  File "", line 1, in 
>    cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :
> [c2] )""",recdct)
> OperationalError: unrecognized token: ":"

>

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

Re: [sqlite] put jpg into sqlite database

[Simon Davies]

On 30 March 2012 10:39, YAN HONG YE  wrote:
> this following code is my import girl.jpg to my sqlite database, but I don't 
> know why JPG didn't up to the bin.db.
>
> #include 
> #include
> #include
> #include"sqlite3.h"
>
> #pragma comment(lib, "sqlite3.lib")
>
> sqlite3 * db;
>
> sqlite3_stmt *stat;
> int main(int argc, char **argv)
> {
> char *zErrMsg=0;
> FILE *fp=NULL;
> long filesize=0;
> char* ffile=NULL;
> char* buf=NULL;
> sqlite3_open("Bin.db",);
> if(db==NULL)
> { return 0;}
> fp=fopen("girl.jpg","rb");
> if(fp!=NULL)
> {fseek(fp,0,SEEK_END);
> filesize=ftell(fp);
> fseek(fp,0,SEEK_SET);
> ffile=(char*)malloc(filesize);
>
> size_t sz=fread(ffile,sizeof(char),filesize,fp);
> printf("%s",ffile);
> printf("%d",filesize);
> fclose(fp);
> }
>
> sqlite3_exec(db,"create table image (filename varchar(128) unique,img 
> blob);",0,0,);
>
> sqlite3_prepare(db,"insert into image values  ('girl.jpg',?)",-1,,0);
>
> sqlite3_bind_blob(stat,1,ffile,filesize,NULL);
>
> sqlite3_step(stat);
> free(ffile);
>
> sqlite3_finalize(stat);
>
> sqlite3_close(db);

It is worthwhile checking for errors from the api calls...

>
>    return 0;
> }

How are you determining that the blob is not in the database?

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

Re: [sqlite] how to add time in a new column

[Simon Davies]

On 29 March 2012 12:36, Black, Michael (IS)  wrote:
> You'll need to export the table and data.  Change the SQL to what you want. 
> Then import again.
>
> Does the shell have ability to name the columns on the insert  statements 
> from the .dump to make this easier?  I don' t see anythinig offhand that 
> seems to do that.

Or use ALTER TABLE RENAME TO... and INSERT INTO...

viz

create table t( id integer, data text );
insert into t values( 1, '1' );

alter table t rename to old_t;

create table t( id integer, data text, "time" text default current_time );

insert into t( id, data ) select id, data from old_t;

>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>

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

Re: [sqlite] .headers error

[Simon Davies]

On 27 March 2012 06:03, YAN HONG YE  wrote:
> C:\sqlite\lib>sqlite3 -html foods.db "select * from dzh;" >mm.html "-headers 
> on"
>
> sqlite3: Error: too many options: "-headers on"
> Use -help for a list of options.
> how to write this command?

sqlite3 -html -header foods.db "select * from dzh;" >mm.html

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

Re: [sqlite] version 3.7.3 on linux, commands do not respond

[Simon Davies]

2012/3/22 Conxita Marín :
> It has to be something more complexof course, I tried the same database in
> Debian Lenny + Sqlite3 3.5.9 and it works perfectly:
>
> conxita@my_other_linux# sqlite3 backup_bd
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> .tables
> android_metadata  dbversion  prefs 

Compare from your original post and above:

conxita@mylinux$: sqlite3 backup.bd
SQLite version 3.7.3

conxita@my_other_linux# sqlite3 backup_bd
SQLite version 3.5.9

We can't tell if they are really the same underlying data, but the
names are different.
Do you change the db name when moving between machines?

>
> Any other ideas?
>
> Conxita
>

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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Simon Davies]

On 19 March 2012 13:05, Pavel Ivanov  wrote:
>> According to the documentation on SELECT statements 
>> http://www.sqlite.org/lang_select.html
>> It seems possible to write join chains as A join (B join C). (using a '(' 
>> join-source ')' single-source )
> ...
>> It seems that parsing is ok (no syntax error) but sources in the sub join 
>> can't be used outside the parenthesis.
>>
>> Could you confirm this is a bug ? or did I miss something ?
>
> So there's no problem in documentation and you can indeed write join
> chains using parenthesis. Also there's no bug in SQLite because
> (according to documentation) join-source with parenthesis is
> considered a single-source and as a consequence you can't link to some
> details of this single-source outside of parenthesis. Maybe MS SQL
> Server and Oracle provide an extension to do that, but SQLite doesn't.
>
> So we can't confirm a bug and you didn't miss anything. You can file a
> bug with the application generating your original query.
>
>
> Pavel

Maybe what the application should be doing:

SELECT subjoin.OrderID
FROM Customers
INNER JOIN
(Orders
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS subjoin
ON Customers.CustomerID = subjoin.CustomerID
WHERE 'ALFKI' = Customers.CustomerID

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

Re: [sqlite] The number of parameters passed to user functions

[Simon Davies]

On 13 March 2012 10:59, Max Vlasov  wrote:
> Hi,
>
> Is there a way to know the number of parameters passed for user function
> with undefined argument count (nArg = -1 in the Sqlite3_create_function
> call).
>
> I could not find a function specific to this task and thought that I at
> least can check sqlite3_values array members for zero for my implementation
> of xFunc. But the problem is that for complex queries the undefined members
> sometimes become not-null

argc gives num of args

as in

static void sjdTest(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv )
{
sqlite3_result_int( context, argc );
}

.
.
.
> The version of sqlite 3.7.10
>
> So is there reliable way to find the number of parameters of such functions?
>
> Thanks
>
> Max

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

Re: [sqlite] Unique qualifier

[Simon Davies]

On 12 March 2012 15:49, Bryce Lembke  wrote:
> I cannot seem to get the UNIQUE constraint to work in sqlite.
>
> The following is the trace from my command line.
>
> Note that if I remove the UNIQUE constraint, it works fine.
>
> C:\>sqlite myDB.db
>
> SQLite version 3.6.17
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT
> UNIQUE);
>
> SQL error: SQL logic error or missing database
>
> sqlite> CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT);
>
> sqlite>
>
> The examples I have seen are using this syntax, what am I doing wrong?
>
> Bryce
>

I have just tried your table create statement in versions 3.3.14 and
3.7.10 on Win7; in both cases no error

SQLite version 3.3.14
Enter ".help" for instructions
sqlite> CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE);
sqlite>

Where does your shell come from? What environment?

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

Re: [sqlite] import txt file to sqlite

[Simon Davies]

On 12 March 2012 08:35, YAN HONG YE  wrote:
> I wanna import a text file to sqlite db, this follow code is right?
>        char *zErrMsg = 0;
>        sqlite3 *db;
>
>        rc=sqlite3_open("foods.db",);
>        sqlite3_exec(db, "CREATE TABLE contact (fliename varchar(128) UNIQUE, 
> fzip blob, ntest int, ntest1 int);", 0, 0, );
>        sqlite3_exec(db, " .separator ',';", 0, 0, );
>        sqlite3_exec(db, " .import e:/contact.txt contact", 0, 0, );

No. Sqlite3 dot commands are not executed by the sqlite3 library, but by shell.c

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

>
> thanks!

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

Re: [sqlite] Efficient random sampling in a large table using builtin functions.

[Simon Davies]

On 8 March 2012 14:37, Black, Michael (IS) <michael.bla...@ngc.com> wrote:
> Glad to know thatcould that possibly be mentioned in the random() notes 
> on the core functions?  Thought that is (apparently) a C function and not SQL 
> accessible?

Core SQL random() and randomblob() functions use sqlite3_randomness(),
according to the page quoted.

>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Simon Davies [simon.james.dav...@gmail.com]
> Sent: Thursday, March 08, 2012 8:33 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Efficient random sampling in a large table using 
> builtin functions.
>
> On 8 March 2012 14:20, Black, Michael (IS) <michael.bla...@ngc.com> wrote:
>> You don't say what language you are working in.  IN C++ I would just declare 
>> a "set" and put random row numbers in it until I had enough.  Then use that 
>> set to build the SQL.
>>
>> SQLite's random() doesn't have a seed function so you don't really get very 
>> random numbers from run-to-run and have no good way of controlling it that I 
>> can find in the docs.  You want to use your language's random function if 
>> you want anything close to real randomness.
>
> http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG
>
>>
>> Hopefully your language has a similar data structure you can use.
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
> ___
> 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] Efficient random sampling in a large table using builtin functions.

[Simon Davies]

On 8 March 2012 14:20, Black, Michael (IS)  wrote:
> You don't say what language you are working in.  IN C++ I would just declare 
> a "set" and put random row numbers in it until I had enough.  Then use that 
> set to build the SQL.
>
> SQLite's random() doesn't have a seed function so you don't really get very 
> random numbers from run-to-run and have no good way of controlling it that I 
> can find in the docs.  You want to use your language's random function if you 
> want anything close to real randomness.

http://www.sqlite.org/c3ref/randomness.html claims high-quality PRNG

>
> Hopefully your language has a similar data structure you can use.
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Simon Davies]

On 2 March 2012 16:23, Duquette, William H (318K)
 wrote:
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov

SQLite version 3.6.11
Enter ".help" for instructions
sqlite> create table t1( id integer primary key, data text );
sqlite> create table t2( id integer primary key, data text );
sqlite>
sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
as d2 from t1 join t2 on t1.id=t2.id;
sqlite>
sqlite>
sqlite> explain query plan select data from t1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
sqlite> explain query plan select d1 from v1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
1|1|TABLE t2 USING PRIMARY KEY
sqlite>

It seems not for v 3.6.11

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

Re: [sqlite] read each line in sqlite database

[Simon Davies]

On 1 March 2012 18:43, Don V Nielsen  wrote:
> I think Simon's solution is in error, and Igor's is correct.  In Simon's
> case, Slevel will be set to 1 if price1 is greater than 30.

It will be set to 2, as required by OP.

> However, the
> original c function would set Slevel to 2 because price1 is greater than 12
> and it is greater than 30.  Two increments of i are executed in that
> scenario.  Igor's accumulating the boolean results gets you where you want
> to be; it's just a little cryptic.
>
> The original function was less than explicit.  I'm assuming some kind of
> weighted value is being generated.  My solution is probably the most wordy
> and least efficient performance wise, it is explicit in function.  It also
> allows you to set the weight of each price1/price2 level.
>
> UPDATE bb SET Slevel =
> (
>  (Case when price1>12 then 1
>        when price1>20 then 2
>        else 0
>   End) +
>  (Case when price2>20 then 1
>        when price2>30 then 2
>        when price2>80 then 3
>        else 0
>   End)
> );
>

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