Re: [sqlite] Why do I only get one record?

[Igor Korot]

Hi,

On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera  wrote:
>
>
> Thanks, Keith.  Darn it!  GROUP BY and ORDER BY!  Got it, it's working now.  
> Thanks.
>
>
> 
> From: sqlite-users  on behalf 
> of Keith Medcalf 
> Sent: Wednesday, February 19, 2020 03:09 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Why do I only get one record?
>
>
> 1)  In the first two query's, why do you have a condition on the LHS table in 
> the LEFT JOIN conditions?
> 2)  In the last query, why do you have a condition on the RHS table of the 
> LEFT JOIN in the WHERE clause?
>
> These would seem to indicate that you are using a LEFT JOIN when you really 
> do not want a LEFT JOIN (maybe you are a LEFT lover?) ... it is a common 
> ailment.

I hate MS-ism. ;-)
Why not just use a simple WHERE a.id = b.id?

Less typing anyway...

Thank you.

>
> Also, you are only getting one row because you only asked for one row.  If 
> you request an aggregate and specify no GROUP BY then you can only ever get 
> one row as a result -- the one aggregate row.
>
> If you want more than one row you need to specify by what you want the 
> results grouped in the GROUP BY clause.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users  On
> >Behalf Of Jose Isaias Cabrera
> >Sent: Wednesday, 19 February, 2020 12:46
> >To: SQLite mailing list 
> >Subject: [sqlite] Why do I only get one record?
> >
> >
> >Greetings!
> >
> >Newbie here again... ;-)
> >
> >Please take a look at this query,
> >
> >sqlite> SELECT
> >   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
> >   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
> >   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
> >   ...> FROM Project_List AS a
> >   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
> >   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
> >WHERE ProjID = b.ProjID)
> >   ...> AND
> >   ...> a.InsertDate =
> >   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
> >a.ProjID)
> >   ...> WHERE a.ProjID IN
> >   ...> (
> >   ...> 'PR018284',
> >   ...> 'PR015544'
> >   ...> )
> >   ...> ORDER BY a.ProjID;
> >PR015544|2019-01-01|2020-01-01||
> >sqlite>
> >
> >Why do I only get one row?  Because if I do this other query,
> >
> >sqlite> SELECT
> >   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
> >   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
> >   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
> >   ...> FROM Project_List AS a
> >   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
> >   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
> >WHERE ProjID = b.ProjID)
> >   ...> AND
> >   ...> a.InsertDate =
> >   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
> >a.ProjID)
> >   ...> WHERE a.ProjID IN
> >   ...> (
> >   ...> 'PR018284'
> >   ...> )
> >   ...> ORDER BY a.ProjID;
> >PR018284|2020-01-01|2020-03-01||
> >sqlite>
> >
> >That project exists.  And if I do this other query,
> >sqlite> SELECT
> >   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
> >   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
> >   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
> >   ...> FROM Project_List AS a
> >   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
> >   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
> >WHERE ProjID = b.ProjID)
> >   ...> WHERE
> >   ...> a.InsertDate =
> >   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
> >a.ProjID)
> >   ...> AND a.ProjID = b.ProjID
> >   ...> ;
> >PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01-
> >31|76605061.443927|76125541.48|149733051.57
> >sqlite>
> >
> >I still only get one.  It looks like I am setting a limit, but that is
> >not true.  Any help would be greatly appreciated.  Thanks.
> >
> >josé
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable dot commands?

[Igor Korot]

Hi,

On Sun, Jan 12, 2020 at 7:44 PM Xingwei Lin  wrote:
>
> Hi,
>
> Is there any way can we disable the dot commands feature in sqlite?

Are you talking about the SQLite shell?
Why do you want to disable them? What is your specific scenario?

Thank you.

>
> --
> Best regards，
> Xingwei Lin
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Tue, Jan 7, 2020 at 2:01 AM Clemens Ladisch  wrote:
>
> Richard Hipp wrote:
> > On 1/5/20, Keith Medcalf  wrote:
> >> select * from a, b, c using (id); -- very strange result
> >
> > PostgreSQL and MySQL process the query as follows:
> >
> >SELECT * FROM a, (b JOIN c USING(id));
> >
> > SQLite processes the query like this:
> >
> >SELECT * FROM (a,b) JOIN c USING (id);
> >
> > I don't know which is correct.  Perhaps the result is undefined.
>
> Assuming the following query:
>
>   SELECT * FROM a, b JOIN c USING (id);
>
> SQL-92 says:
> |7.4  
> |
> | ::= FROM  [ {   reference> }... ]
> |
> |6.3  
> |
> | ::=
> |[ [ AS ] 
> |   [] ]
> | |  [ AS ] 
> |   []
> | | 
> |
> |7.5  
> |
> | ::=
> |   
> | | 
> | |   
> |
> | ::=
> |  CROSS JOIN 
> |
> | ::=
> |  [ NATURAL ] [  ] JOIN
> |[  ]
>
> It is not possible to have such a  inside a , so
> b and c must be joined first.
>
> SQLite actually parses the comma as a join:
>
>   SELECT * FROM a CROSS JOIN b JOIN c USING (id);
>
> If the query were written like this, joining a and b first would be
> correct.  (As far as I can see, the standard does not say how to handle
> ambiguous parts of the grammar, so it would also be allowed to produce
> "b JOIN c" first.)

That's why one should never use that "MS JOIN extension" and should
simply write:

SELECT ... FROM a,b,c WHERE a.x = b.x AND b.x = c.y AND...;

Thank you.

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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Sat, Jan 4, 2020 at 7:31 PM Amer Neely  wrote:
>
> Hello all,
> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
> and web-based environment for several years. So far I'm happy and
> impressed with SQLite, but I recently noticed some odd behaviour with
> one of my queries.
> Using the command-line in a shell (Mac High Sierra) I get a particular
> result from a query. The exact same query in a Perl script gives me a
> different result. To my mind it is a simple query, getting the 5 latest
> additions to my music library.
> Command-line:
> select artists.artist, artists.artistid, cds.title, cds.artistid,
> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
> cds using (artistid) group by artists.artistid order by cds.id desc
> limit 5;
> gives me the correct result. However, in a Perl script it gives me a
> different result. How is that possible? Could it be a Perl::DBI issue?
> Many thanks for anyone able to shed some light on this.

What is your version of Perl and the SQLite module?

Thank you.

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

Re: [sqlite] Bug Report

[Igor Korot]

Hi,

On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
> SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
> col1 DESC ) FROM table1 ;

Could you please provide the schema for table1?

Thank you.

>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 
> 20:19:45`
>
> Thanks
>
> Ming Jia
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in storing text values in numeric columns

[Igor Korot]

Hi,

On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner  wrote:
>
> The documentation for a column with NUMERIC affinity says
>
> > When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.
>
> But consider:
>
> sqlite> create table foo(bar numeric);
> sqlite> insert into foo values ('0012');
> sqlite> select bar, typeof(bar) from foo;bar typeof(bar)
> --  ---12  integer
>
>
> As you can see, the leading zeros in the original string are gone and it's
> been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?

What version of SQLite do you use?

Thank you.

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

Re: [sqlite] SQLite - macOS

[Igor Korot]

Hi,

On Mon, Sep 23, 2019 at 11:58 AM Pierre Clouthier
 wrote:
>
> Can anyone explain how to write UTF-8 in SQLite on the Mac?
>
> We use this statement:
>
> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")

You should probably use this inside

#ifdef _WINDOWS  #endif

Thank you.

>
> This works fine on Windows, but on macOS the data is not being stored
> correctly.
>
> The data to be written (passed to SQLite), is formatted in UTF-8.
>
> For example, 'é' is U+00E9, which in UTF-8 is 0xC3A9. However, in the
> macOS version of the database, it is converted to 0xEFBFBD, which is
> U+FFFD, which doesn't make sense.
>
> --
> Progeny Genealogy Inc. 902–681–3102
> Progeny helps you tell the Story of Your Family ™
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what's wrong with this trigger

[Igor Korot]

Hi,

On Mon, Sep 9, 2019 at 9:43 PM Doug  wrote:
>
> What works, please? I saw no answer.

There is an answer down below.

Thank you.

> Doug
>
> > -Original Message-
> > From: sqlite-users 
> > On Behalf Of Rael Bauer
> > Sent: Monday, September 09, 2019 7:01 PM
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] what's wrong with this trigger
> >
> > Thanks, that works.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to insert the BLOB in database?

[Igor Korot]

Hi, Richard,
Thank you for the reply.

On Sun, Jun 9, 2019 at 7:24 PM Richard Hipp  wrote:
>
> On 6/9/19, Igor Korot  wrote:
> >
> > Now I open this database in sqlite3 CLI binary and would like to insert some
> > png
> > file inside this BLOB field.
>
>
> INSERT INTO tab1(blob1) VALUES(readfile('some.png'));

I presume the file extension can be anything?
Looking at the documentation:

https://sqlite.org/cli.html

this looks to be the case.

Thank you.


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

[sqlite] How to insert the BLOB in database?

[Igor Korot]

Hi, ALL,
Let's say I have some database, where I have table called test.
This test table contains the field whose type is BLOB.

Now I open this database in sqlite3 CLI binary and would like to insert some png
file inside this BLOB field. And I am not talking about the file name
- the actual
content of the png.

Is there a way to do that from the CLI?

Thank you.
___
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

[Igor Korot]

Hi,

On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera  wrote:
>
>
> Thanks,  Simon.  Works like a charm...

Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...

Thank you.

>
>
> From: Simon Davies
> Sent: Friday, April 5, 2019 12:24 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not 
> satisfy all of the query
>
> 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 

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Igor Korot]

Hi

On Tue, Mar 12, 2019 at 5:03 PM Ted Goldblatt  wrote:
>
> On Tue, Mar 12, 2019 at 12:29 PM Simon Slavin  wrote:
>
> > I may have missed this already being discussed.
> >
> > Will you have access to a copy of the database as it was before corruption
> > testing ?  Can you use SQLite to see whether it is already corrupt ?  Or
> > can the test run on a brand new, freshly-created database ?  If neither of
> > those, your test won't be fair.
> >
>
> We are able to repro this problem, so clean databases aren't a problem.
>  At least the first time this was done in-house, it was on a brand new
> database (not sure about all the subsequent tests - at least some were on
> databases which had already been hit with a power failure).

So what is the exact steps you did to see the problem?

Thank you.

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

Re: [sqlite] Why am I not getting anything?

[Igor Korot]

Thank you.

On Sun, Feb 10, 2019 at 1:35 AM Shawn Wagner  wrote:
>
> SELECT * FROM pragma_foreign_key_list('temp1');
>
> The table name needs to be a string for the pragma_foreign_key_list virtual
> table. (It's not for the corresponding pragma statement.)
>
> On Sat, Feb 9, 2019, 11:31 PM Igor Korot 
> > Hi, ALL,
> >
> > [code]
> > sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
> > type|name|tbl_name|rootpage|sql
> > table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name
> > VARCHAR(200))
> > table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id
> > INTEGER, my
> > name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES
> > temp(id))
> > table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value
> > NUMERIC(15,
> >  2))
> > table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data
> > TEXT)
> > table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id
> > INTEGER,
> > myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
> > sqlite>
> > sqlite>
> > sqlite>
> > sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
> > Error: no such column: temp1
> > sqlite>
> > [/code]
> >
> > I should get an information about the foreign key that exists for the
> > table temp1,
> > but I have an error.
> >
> > And when I execute the PRAGMA directly, I do get the results:
> >
> > [code]
> > sqlite> PRAGMA foreign_key_list(temp1);
> > id|seq|table|from|to|on_update|on_delete|match
> > 0|0|temp|id|id|NO ACTION|NO ACTION|NONE
> > [/code]
> >
> > [code]
> > sqlite> .version
> > SQLite 3.24.0 2018-06-04 19:24:41
> > c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
> > d5221ac8fb199a87ca
> > zlib version 1.2.11
> > gcc-5.2.0
> > sqlite>
> > [/code]
> >
> > This is on Windows 8.1
> >
> > Am I doing something wrong?
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why am I not getting anything?

[Igor Korot]

Hi, ALL,

[code]
sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
type|name|tbl_name|rootpage|sql
table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name VARCHAR(200))
table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id INTEGER, my
name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES temp(id))
table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value NUMERIC(15,
 2))
table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data TEXT)
table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id INTEGER,
myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
sqlite>
sqlite>
sqlite>
sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
Error: no such column: temp1
sqlite>
[/code]

I should get an information about the foreign key that exists for the
table temp1,
but I have an error.

And when I execute the PRAGMA directly, I do get the results:

[code]
sqlite> PRAGMA foreign_key_list(temp1);
id|seq|table|from|to|on_update|on_delete|match
0|0|temp|id|id|NO ACTION|NO ACTION|NONE
[/code]

[code]
sqlite> .version
SQLite 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
d5221ac8fb199a87ca
zlib version 1.2.11
gcc-5.2.0
sqlite>
[/code]

This is on Windows 8.1

Am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Igor Korot]

Peter,

On Tue, Jan 29, 2019 at 7:53 PM Peter da Silva  wrote:
>
> On Tue, Jan 29, 2019, 7:46 PM Igor Korot 
> > You can install mySQL/MariaDB for free and use it for your needs.
> > I believe that if you pay to Oracle/MariaDB Foundation, it will be
> > just for support. (I may be wrong though).
> >
>
> Or better, PostgreSQL. We have a system that uses SQLite as an optional
> cache for PostgreSQL and takes advantage of the similarity of their SQL
> variants to get the best of both worlds. I modified the Pgtcl library to
> even get the variable binding for PostgreSQL to match SQLite syntax and
> semantics.

That is correct.
And with PG you will get extra security as a bonus, as I think it is
still considered
a very secure DBMS.

Thank you.

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

Re: [sqlite] SQLite slow when lots of tables

[Igor Korot]

Hi,

On Tue, Jan 29, 2019 at 7:06 PM Keith Medcalf  wrote:
>
> On Tuesday, 29 January, 2019 16:28, Wout Mertens wrote:
>
> >To: SQLite mailing list
> >Subject: Re: [sqlite] SQLite slow when lots of tables
> >
> > I always have to explain to people that there's no magic sauce that
> > "real databases" add versus SQLite.
>
> > SQLite uses the same techniques all databases use, and thanks to the
> > absence of a network later, you avoid a lot of latency, so it can
> > actually be faster.
>
> > (I do believe that SQLite optimizes for smaller disk footprint, so
> > that may be a tradeoff where other databases might gain speed)
>
> Well, there is a bit more to it than that.  The SQL interface and the 
> capabilities may be similar but paying $ for a client/server database 
> does get you something that you do not get with SQLite3:
>
>  - Someone else wrote the networking layer, so if you want one, you do not 
> have to do it yourself
>  - C/S databases are designed to handle REAMS (ie, thousands) of remote 
> clients doing simultaneous access, so the concurrency is much greater
>  - C/S databases are parallelized and multithreaded, meaning that the single 
> server process can use all the cores on your server simultaneously
>  - C/S databases may be parallelized so that a single query uses all 
> available CPU cores simultaneously
>  - C/S databases may be NUMA and/or SYSPLEX enabled so that a single database 
> can be scattered across multiple disparate machines yet queried as if there 
> was only one database on one machine
>  - C/S databases have more complicated data fetching methods (ie, they can 
> use such things as hash tables, intersection sorts, and all sorts of other 
> methods to fetch your data rather than just the nested loop retrieval 
> supported by SQLite)
>  - Some C/S databases may have extremely complex planners designed to take 
> maximum advantage of all the above things simultaneously (at a cost, of 
> course)
>
> Of course, other than the big bucks you will spend on the C/S database, you 
> will also have to spend big bucks to give it a big computer to run on.  That 
> means lots of fast I/O and gobs of RAM and CPU.  Of course, in the grand 
> scheme of things a 48 or 96 core x64 server with a terrabyte of RAM and a 
> couple hundred (or thousand) terrabytes of SSD is not really that expensive.
>
> So really, it depends on your needs and what you are willing to pay,  In many 
> cases for a single user or even a small number of concurrent users on a 
> single computer will likely achieve better performance (and cost efficiency) 
> by using SQLite3.
>
> On the other hand if the application is an line-of-business database for a 
> Fortune 5 multinational corporation, you will probably choose the C/S 
> database (particularly if you are running some  software like SAP 
> ...).

All this is not necessary true.
You can install mySQL/MariaDB for free and use it for your needs.
I believe that if you pay to Oracle/MariaDB Foundation, it will be
just for support. (I may be wrong though).

You can also install SAP ASE (former Sybase) and use it in production
with up to 4 connection for free.

I also think you can install Oracle on you box for free, but I'm not
sure about licenses for the big Oracle.

So, there are options even for the "Big DBMS" that are free.

Thank you.

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

Re: [sqlite] SQLite slow when lots of tables

[Igor Korot]

Hi,

On Mon, Jan 28, 2019 at 10:17 AM  wrote:
>
> Dear,
>
> I developed an application that need to create 1 table with thousand of rows 
> every time when a certain event occours.

Are you coming from the FoxBase/ForPro world?

Thank you.

>
> This works in a good way, but when the number of the tables become huge 
> (about 15000/2 tables) the first DataBase reading query, after Database 
> open, is very slow (about 4sec.) while next reading operations are faster.
>
> How can I speed up?
>
> Thanks.
>
>  Davide
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

[Igor Korot]

Dennis,
On Sat, Jan 19, 2019 at 9:31 PM Dennis Clarke  wrote:
>
>
> > And SPARC version is still available for download...
>
> Let us know when you get that running.

Install of x86 went very smooth.
And I was able to compile fairly recent SQLite with Oracle Studio 12.6
with just couple of warnings...

Which problem did you experience on SPARC?

Thank you.

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

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

[Igor Korot]

Hi,

On Sat, Jan 19, 2019 at 4:29 PM Dennis Clarke  wrote:
>
> On 1/19/19 4:47 PM, Andy Goth wrote:
> > Dennis Clarke wrote:
> >> On 2018-07-28 08:33, Andy Goth wrote:
> >>> SQLite 3.24.0 fails to build on Solaris 9 (a.k.a. Solaris 2.9)
> >
> >> It may be [worth] while to spin up a Solaris 9 zone on a Solaris 10 or
> >> Solaris 11 server for this purpose.
> >
> > I don't have access to any Solaris servers of any kind. And yet, I had the
> > requirement to produce a working binary for a computer I wasn't even
> > allowed to go visit. It was rough, but the task is done and sold off.
>
>  From the better late than never file eh?
>
> I have no idea how you managed to land that task but these days
> if someone asks me to do any work on a Solaris 8 or 9 server I
> simply say "no" and that is the end of it.  I have seen too many
> nights and days lost to cursing over old Solaris 8 sparc servers.
>
> I have a few Solaris 10 servers left in life and zero, none, nothing
> for Solaris 11 simply because Oracle made it impossible to run.

Well I don't know.
I successfully installed 11.4 on x86 architecture.

And SPARC version is still available for download...

Thank you.

>
> So well done and now do your self a favour and say good bye to it.
>
> Time to go look at Debian and FreeBSD on RISC-V if you want adventure!
>
> Dennis
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Solaris Studio 12.6 compilation failure

[Igor Korot]

"sqlite3.c", line 29907: warning: conversion to double is out of range
"sqlite3.c", line 52491: warning: statement not reached

Is it something to be worry?

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

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Igor Korot]

Or for the simplicity sake - just add the sqlite3.* files to the
project and compile everything.
But then you will NOT need to follow the instruction in my previous email.

As Keith said, you should choose the path and follow it - either use
the source code
or use precompiled library.

Thank you.

On Fri, Dec 21, 2018 at 8:03 PM Keith Medcalf  wrote:
>
>
> To compile the sqlite3.c file to sqlite3.dll you need to create a separate 
> project that will "compile" sqlite3.c (as a "C" file) with the dependency 
> sqlite3.h and tell VS to output a "DLL" / Dynamic Link Library rather than an 
> EXE file.  The ".lib" is the built from the symbols exported into this DLL.
>
> I presume that you can set these options in the VS gooey somewhere but where 
> I do not know since I only use the VS gooey under duress.
>
> You also somehow have to tell VS to "export" the sqlite3_api (perhaps by 
> defining the compiler symbol SQLITE_API=__declspec(dllexport) as one would do 
> with GCC) or by adding a dependency in the gooey to the sqlite3.def file 
> (which contains a list of the symbols that should be exported) and the .def 
> file is then also used to create the .lib file.
>
> If I were to build the sqlite3.dll using the command line compiler (CL), I 
> would define the symbol SQLITE_API=__declspec(dllexport) and compile to a DLL 
> (rather than an EXE).  The output of the compile step is the .DLL and the 
> .LIB file to go with it (plus perhaps a manifest that you have to attach back 
> to the DLL in some cases, though what those are are beyond me -- if there is 
> and output manifest I just plop in back into the DLL as a matter of course).
>
> Any "options" you want included when you compile the DLL are specified in the 
> input to the sqlite3.c compile step (as defines).
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of zydeholic
> >Sent: Friday, 21 December, 2018 18:29
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Need setup code for VC++ 2017 that will
> >ACTUALLY COMPILE
> >
> >So, and sorry if this is covering ground already covered, but if I
> >wanted to compile my own DLL and .lib file, I would do what exactly?
> >Just not tell it about the .lib dependencies?  And not put the lib
> >and dll files in my project directory?  Sorry for my denseness.  I'm
> >very knew to this route of doing things, old as that route may be.
> >
> >
> >  From: Keith Medcalf 
> > To: SQLite mailing list 
> > Sent: Friday, December 21, 2018 5:15 PM
> > Subject: Re: [sqlite] Need setup code for VC++ 2017 that will
> >ACTUALLY COMPILE
> >
> >
> >No problem.  You can of course omit the sqlite3.c file  and use the
> >sqlite3.lib/sqlite3.dll combination, but your application will then
> >be dependent on the version of sqlite3.dll that happens to be found
> >at runtime and the options that were used to compile it.  If you use
> >the sqlite3.c directly then your application will be statically
> >linked and not dependent on the external dll file.  Plus with the
> >sqlite3.c compiled statically you will be able to define exactly what
> >sqlite3 features you want to include whereas with the .lib/.dll you
> >will use whatever options happen to have been used to build that dll.
> >
> >
> >---
> >The fact that there's a Highway to Hell but only a Stairway to Heaven
> >says a lot about anticipated traffic volume.
> >
> >
> >>-Original Message-
> >>From: sqlite-users [mailto:sqlite-users-
> >>boun...@mailinglists.sqlite.org] On Behalf Of zydeholic
> >>Sent: Friday, 21 December, 2018 18:02
> >>To: SQLite mailing list
> >>Subject: Re: [sqlite] Need setup code for VC++ 2017 that will
> >>ACTUALLY COMPILE
> >>
> >>Holy Chewbacca,
> >>I removed the .c file from the project and I got an error free
> >>compile.  Yes, I knew my code would not do anything, except prove
> >>that I was able to talk through to something.  I'll try your code
> >>below.
> >>
> >>Thanks MUCHO.
> >>
> >>David
> >>
> >>  From: Keith Medcalf 
> >> To: SQLite mailing list 
> >> Sent: Friday, December 21, 2018 4:53 PM
> >> Subject: Re: [sqlite] Need setup code for VC++ 2017 that will
> >>ACTUALLY COMPILE
> >>
> >>
> >>First of all use EITHER the source (.c) OR the precomiled dynamic
> >>link library (.dll/.lib).  Not both.
> >>
> >>The .c file contains the code to the sqlite3 database engine.  The
> >>DLL contains this code compiled to a Dynamic Link Library.  The LIB
> >>file tells your application how to find the code in the dynamic link
> >>library.  If you use both, only god (and probably not ever her)
> >knows
> >>what will happen.
> >>
> >>Here is a C++ program that actually does something (yours does
> >>not).  It compiles and runs when using a real compiler on Winders
> >>(GCC).  It has no error checking.  But it does call "C" functions
> >>from "C++".  

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Igor Korot]

Hi,
It is in the Properties->Linker->General->Additional Library Directories.
Don't remove the things that is already there - just append you path to the end.

Thank you.

On Fri, Dec 21, 2018 at 7:18 PM zydeholic  wrote:
>
> Hi Igor,
> I've looked and I don't see those exact words.  If you can, it would be 
> helpful to know the route to get there, like
>
> Linker>>Input>>???
> Thanks for your time.
>
>   From: Igor Korot 
>  To: SQLite mailing list 
>  Sent: Friday, December 21, 2018 4:47 PM
>  Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY 
> COMPILE
>
> Hi,
>
> On Fri, Dec 21, 2018 at 6:26 PM zydeholic  wrote:
> >
> > My cpp code consists of this at the moment:
> > #include "sqlite3.h"
> > #include 
> > #include 
> > using namespace std;
> >
> > int main()
> > {
> >sqlite3 *db;
> > }
> >
> > I have added sqlite3.h to my header files.I have added sqlite3.c to my 
> > source files.I've moved these two files, plus sqlite3.dll and sqlite3.lib 
> > into the same directory as these other files:
> > C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3
> >
> > In Project, I've made the following settings:VC++ Directories>>Library 
> > Directories>>C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3;C/C++>>Additional
> >  Include Directories>>C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3;
> > C/C++>>Precompiled Headers>>Not using precompiled headers (all 
> > configurations, all platforms).
> > Linker>>Input>>Additional Dependencies>>sqlite3.lib
>
> You are almost there.
> Just like you added the "Include Directory", you now need to add
> "Linker Directory" or "Library Directory"
>
> Thank you.
>
>
>
> > I now get two errors when I compile:
> > SeverityCodeDescriptionProjectFileLineSuppression 
> > State
> > ErrorLNK2001unresolved external symbol _sqlite3_version
> > sqlite_try_3
> > C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj1
> > SeverityCodeDescriptionProjectFileLineSuppression 
> > State
> > ErrorLNK11201 unresolved externalssqlite_try_3
> > C:\Users\DSNoS\source\repos\sqlite_try_3\Debug\sqlite_try_3.exe1
> >
> >
> > At some point before I started filling in the directories and such in 
> > Project, my code:
> > sqlite3 *db;
> > was errored out (underlined in red).  Now they are not erroring out.  I 
> > just get the two errors above when I compile.
> >
> >  From: zydeholic 
> >  To: SQLite mailing list 
> >  Sent: Friday, December 21, 2018 3:46 PM
> >  Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY 
> > COMPILE
> >
> > Ok, to further define my goals, I am not looking to compile the files into 
> > a final EXE, unless that is the only way I can use it with my program.  I 
> > want to tap into the sqlite functionality from a C++ application I am 
> > writing.
> >
> > I suppose I need to use the DLL that I downloaded, but have never used an 
> > external library before, not where I had to tell the compiler how to talk 
> > to it.
> > So, SQLite, backend database.  I will do the front end stuff in my code.  
> > Hopefully this clarifies something.
> >
> >
> >  From: Larry Brasfield 
> >  To: "sqlite-users@mailinglists.sqlite.org" 
> > 
> >  Sent: Friday, December 21, 2018 1:02 PM
> >  Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY 
> > COMPILE
> >
> > Zydeholic wrote:
> > ➢ I compile and get one error: SeverityCodeDescriptionProject   
> >  FileLineSuppression State ErrorLNK2001unresolved external 
> > symbol _sqlite3_versionsqlite_try_3
> > C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj1
> >
> > That symbol is declared, and a definition for the object so named is coded, 
> > in the sqlite3.c amalgamation without the possibility of omission by the 
> > preprocessor.  So I find it exceedingly strange that your link operation is 
> > complaining of an unresolvable reference to that symbol in sqlite3.obj.  
> > For a C compilation, which you certainly should be using for that C source, 
> > the name should be undecorated, except for the leading underscore, just as 
> > it appears in the above-quoted error message.  This leads me to believe you 
> > are doing something too

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Igor Korot]

Hi,

On Fri, Dec 21, 2018 at 6:26 PM zydeholic  wrote:
>
> My cpp code consists of this at the moment:
> #include "sqlite3.h"
> #include 
> #include 
> using namespace std;
>
> int main()
> {
> sqlite3 *db;
> }
>
> I have added sqlite3.h to my header files.I have added sqlite3.c to my source 
> files.I've moved these two files, plus sqlite3.dll and sqlite3.lib into the 
> same directory as these other files:
> C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3
>
> In Project, I've made the following settings:VC++ Directories>>Library 
> Directories>>C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3;C/C++>>Additional
>  Include Directories>>C:\users\dsnos\source\repos\sqlite_try_3\sqlite_try_3;
> C/C++>>Precompiled Headers>>Not using precompiled headers (all 
> configurations, all platforms).
> Linker>>Input>>Additional Dependencies>>sqlite3.lib

You are almost there.
Just like you added the "Include Directory", you now need to add
"Linker Directory" or "Library Directory"

Thank you.



> I now get two errors when I compile:
> SeverityCodeDescriptionProjectFileLineSuppression 
> State
> ErrorLNK2001unresolved external symbol _sqlite3_version
> sqlite_try_3
> C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj1
> SeverityCodeDescriptionProjectFileLineSuppression 
> State
> ErrorLNK11201 unresolved externalssqlite_try_3
> C:\Users\DSNoS\source\repos\sqlite_try_3\Debug\sqlite_try_3.exe1
>
>
> At some point before I started filling in the directories and such in 
> Project, my code:
> sqlite3 *db;
> was errored out (underlined in red).  Now they are not erroring out.  I just 
> get the two errors above when I compile.
>
>   From: zydeholic 
>  To: SQLite mailing list 
>  Sent: Friday, December 21, 2018 3:46 PM
>  Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY 
> COMPILE
>
> Ok, to further define my goals, I am not looking to compile the files into a 
> final EXE, unless that is the only way I can use it with my program.  I want 
> to tap into the sqlite functionality from a C++ application I am writing.
>
> I suppose I need to use the DLL that I downloaded, but have never used an 
> external library before, not where I had to tell the compiler how to talk to 
> it.
> So, SQLite, backend database.  I will do the front end stuff in my code.  
> Hopefully this clarifies something.
>
>
>   From: Larry Brasfield 
>  To: "sqlite-users@mailinglists.sqlite.org" 
> 
>  Sent: Friday, December 21, 2018 1:02 PM
>  Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY 
> COMPILE
>
> Zydeholic wrote:
> ➢ I compile and get one error: SeverityCodeDescriptionProject
> FileLineSuppression State ErrorLNK2001unresolved external 
> symbol _sqlite3_versionsqlite_try_3
> C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj1
>
> That symbol is declared, and a definition for the object so named is coded, 
> in the sqlite3.c amalgamation without the possibility of omission by the 
> preprocessor.  So I find it exceedingly strange that your link operation is 
> complaining of an unresolvable reference to that symbol in sqlite3.obj.  For 
> a C compilation, which you certainly should be using for that C source, the 
> name should be undecorated, except for the leading underscore, just as it 
> appears in the above-quoted error message.  This leads me to believe you are 
> doing something too strange for anybody to guess with the information 
> provided so far.
>
> You may notice that this thread is misnamed for this latest difficulty, since 
> the code does actually compile.  If I had to name it accurately, it would be 
> called: [off topic] Need build instructions for my project which uses SQLite 
> in a development environment differing from the one actually supported by the 
> SQLite team.
>
> Some questions to ask yourself as you attempt to sort this out:
> 1. Am I compiling the .c sources as C language?
> 2. Have I modified the sources everybody assumes are as released by the 
> SQLite team?
> 3. What does insight does dumpbin.exe, (the VC command line tool for showing 
> compiled image content), provide into my link errors?
> 4. How does my sqlite3.obj differ from the one I get following step 19 at 
> https://www.sqlite.org/cli.html , and why?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Igor Korot]

Hi,
Unfortunately you didn't tell what did you try to compile, where did
you get the files you tried to compile from and
you didn't even supply the error message you received from the compilation.

Please follow-up and provide this and hopefully someone here will be
able to help.

Thank you.

On Thu, Dec 20, 2018 at 5:17 PM Donald Shepherd
 wrote:
>
> You'll need to provide more information, speaking as someone who compiles
> the amalgamation off the SQLite website with VC++ 2017 on Windows 10 with
> no issues.  SQLite is C code, not C++ code, but VC++ detects that based off
> the file extension and compiles it as such.
>
> Trying to use whatever "CPPSqlite3.cpp" is and adding C++ headers is not
> likely to get you anywhere unless you follow up with whoever created those
> files for assistance.
>
> Regards,
> Donald Shepherd.
>
> On Fri, 21 Dec 2018 at 09:41, zydeholic  wrote:
>
> > Hello folks,
> > I looked through the last few months of posts in the archive, and no
> > subject lines seemed to cover this.
> > I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried
> > code from a couple of websites.
> > NOTHING seems to compile all the way through.
> >
> > I'm using Visual Studio 2017 C++.  I'm on a WIndows 10 machine.  64bit,
> > but compiling 32 bit.
> > I tried Code::Blocks and got different, but equally incomplete compiles.
> >
> > I've included  in the includes.  I've downloaded sqlite3.c and .h.
> > Nothing seems to work.  Is there ANYWHERE that offers a step-by-step
> > solution to this that WILL COMPILE all the way through.
> > I'm sure this has been covered before, but I did not see a way to search
> > the entire archives.  Any help appreciated.
> > Thanks.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Igor Korot]

Hi,

On Tue, Nov 20, 2018 at 10:32 AM Dominique Devienne  wrote:
>
> On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin  wrote:
>
> > On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> > albert.banaszkiew...@tomtom.com> wrote:
> >
> > > ExecuteInTransaction(writeDb1, KCreateTable);
> >
> > I can't answer your question but the above line shows a misunderstanding
> > of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE,
> > DELETE.  Commands which modify the database schema fall outside the scope
> > of transactions and cannot be handled within the framework of COMMIT and
> > ROLLBACK.
> >
>
> Hmmm, DDL is transactional in SQLite, AFAIK... --DD

Unless you work with mySQL. ;-)

Thank you.

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

Re: [sqlite] Help!

[Igor Korot]

Hi,

On Sat, Oct 27, 2018 at 1:41 PM am...@juno.com  wrote:
>
> 10/27/18 Dear Good People: I have two issues which I have not been able to 
> solve. Hopefully at least one of you good people know how to do these. The 
> first is: how do I make a field wrap the text. In other words, rather than 
> having the text in a field keep going from right to left, when I hit the 
> right hand margin, how do I make the text automatically go to the next line. 
> The second is: how do I simultaneously nest two or more fields. For instance, 
> if I have four fields: name, department, clock number, department, how do I 
> nest so that I alphabetically, sort by the clock number, then last name 
> within each department, and ten clock number within in each name? As always, 
> if you have any questions, need more information, and/or need any 
> clarifications, ask away. Respectfully yours, Alex Stavis

On top of all other people told you - you most likely have an error in
you schema.
The department field appears twice in you table.

Thank you.

> 
> Gut Doctor Begs Americans - "Throw This Vegetable Now"
> food-frauds.com
> http://thirdpartyoffers.juno.com/TGL3131/5bd4b12c4eb0b312c7c70st03vuc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique Constraint Failed

[Igor Korot]

Hi,
On Fri, Sep 14, 2018 at 3:14 PM Andrew Stewart
 wrote:
>
> Hi all,
> I realize that this is the constraint that is failing.  The 
> data is very large, encrypted and at a customer's site - not easy to use an 
> external program to view or to transfer to my office.
>
> What I am wondering is if there are any limits on the Unique 
> table that is maintained or if any of the code that is used for this has been 
> changed in the last couple of years.

Can you try and reproduce the problem locally?
Just create a table and try to continuously insert some data in it.

It would also be nice if your software had some logging capabilities -
that way you can turn it on and see what data is coming in and what is
being stored in the DB/table.

Basically there should be a way to reproduce a problem to simplify the
debugging on both yours and the SQLite ends.

Thank you.

>
> Andrew Stewart
> Software Designer
>
> ARGUS CONTROLS
> 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
> t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
> www.arguscontrols.com
>
> Notice: This electronic transmission contains confidential information, 
> intended only for the person(s) named above. If you are not the intended 
> recipient, you are hereby notified that any disclosure, copying, 
> distribution, or any other use of this email is strictly prohibited. If you 
> have received this transmission by error, please notify us immediately by 
> return email and destroy the original transmission immediately and all copies 
> thereof.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Igor Korot]

Hi,


On Thu, Aug 2, 2018 at 1:44 PM, John R. Sowden
 wrote:
> another point that I did not make clear.  The accounting programs are not
> associated with the technical programs, different people, different security
> access.  The tech databases and programs are in portable computers that go
> out in the field, but not the accounting, etc.  There indexes would have to
> be updated when the computers are back at the office.

Then the solution by David applies.
You will have one database (centralized), which will be updated with
the changes to the local DBs
when they come back to the office.

Thank you.

>
> John
>
>
>
> On 08/02/2018 11:33 AM, Igor Korot wrote:
>>
>> Hi,
>>
>> On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
>>  wrote:
>>>
>>> I made a mistake.  I should have said table, not database.  My concern is
>>> if
>>> I have 4 databases each with tables associated with a particular use,
>>> like
>>> accounting, technical, etc., which may reside on different computers, how
>>> do
>>> I keep the index in each database file current.  I assume that I have an
>>> external database with the account number field, and its index that each
>>> database connects to to "refresh" its account number index from the
>>> external
>>> index.  Otherwise if the table with the accounting index is modified, the
>>> tech table and its index would have to communicate with the master in
>>> order
>>> to stay current.
>>
>> Why do you need 4 databases in the first place?
>> If you client is designed to access all 4 databases then all tables
>> should be in 1 DB file.
>>
>> Thank you.
>>
>>> I do this now because I have 1 account number index and the various
>>> foxpro
>>> databases (tables) all open that one index when each is used.
>>>
>>> John
>>>
>>>
>>> On 08/02/2018 10:31 AM, Simon Slavin wrote:
>>>>
>>>> On 2 Aug 2018, at 6:11pm, John R. Sowden 
>>>> wrote:
>>>>
>>>>> I do not want these databases to all reside in one sqlite file.  How do
>>>>> I
>>>>> index each database on this customer account number when each database
>>>>> and
>>>>> associated index are in separate files?  Is this what seems to be
>>>>> referred
>>>>> to as an external file?  I assume that I would have to reindex each
>>>>> database
>>>>> each time it is opened, since a record could have been edited, etc.
>>>>
>>>> You have been misinformed.  In SQLite,
>>>>
>>>> A) each table is stored one database file
>>>> B) each index indexes just one table
>>>> C) all indexes for a table are stored in the same file as that table.
>>>>
>>>> An index is updated when its table is updated.  You never need to
>>>> manually
>>>> reindex unless you changed the table structure or index structure.
>>>>
>>>> It is normal to keep all tables related to one application in one big
>>>> database file.  So, for example, if you run a library you would normally
>>>> keep tables and indexes for books, borrowers, and current loans all in
>>>> one
>>>> file.  And therefore all the indexes for those tables would be in that
>>>> file
>>>> too.  SQLite is designed to handle things this way, and does it very
>>>> efficiently.
>>>>
>>>> However, it is possible to keep different tables in different database
>>>> files.  So you might keep books (and all indexes on books) in one file,
>>>> and
>>>> borrowers and current loans (and all the indexes on those tables) in
>>>> another
>>>> file.
>>>>
>>>> Simon.
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Igor Korot]

Hi,

On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
 wrote:
> I made a mistake.  I should have said table, not database.  My concern is if
> I have 4 databases each with tables associated with a particular use, like
> accounting, technical, etc., which may reside on different computers, how do
> I keep the index in each database file current.  I assume that I have an
> external database with the account number field, and its index that each
> database connects to to "refresh" its account number index from the external
> index.  Otherwise if the table with the accounting index is modified, the
> tech table and its index would have to communicate with the master in order
> to stay current.

Why do you need 4 databases in the first place?
If you client is designed to access all 4 databases then all tables
should be in 1 DB file.

Thank you.

>
> I do this now because I have 1 account number index and the various foxpro
> databases (tables) all open that one index when each is used.
>
> John
>
>
> On 08/02/2018 10:31 AM, Simon Slavin wrote:
>>
>> On 2 Aug 2018, at 6:11pm, John R. Sowden 
>> wrote:
>>
>>> I do not want these databases to all reside in one sqlite file.  How do I
>>> index each database on this customer account number when each database and
>>> associated index are in separate files?  Is this what seems to be referred
>>> to as an external file?  I assume that I would have to reindex each database
>>> each time it is opened, since a record could have been edited, etc.
>>
>> You have been misinformed.  In SQLite,
>>
>> A) each table is stored one database file
>> B) each index indexes just one table
>> C) all indexes for a table are stored in the same file as that table.
>>
>> An index is updated when its table is updated.  You never need to manually
>> reindex unless you changed the table structure or index structure.
>>
>> It is normal to keep all tables related to one application in one big
>> database file.  So, for example, if you run a library you would normally
>> keep tables and indexes for books, borrowers, and current loans all in one
>> file.  And therefore all the indexes for those tables would be in that file
>> too.  SQLite is designed to handle things this way, and does it very
>> efficiently.
>>
>> However, it is possible to keep different tables in different database
>> files.  So you might keep books (and all indexes on books) in one file, and
>> borrowers and current loans (and all the indexes on those tables) in another
>> file.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, Simon,

On Thu, Jul 5, 2018 at 11:45 AM, Simon Slavin  wrote:
> On 5 Jul 2018, at 4:51pm, Igor Korot  wrote:
>
>> Is there a way to get which command was executed?
>> Or which table was added/changed/dropped?
>
> There is no reason for SQLite to record the information you want.  If a 
> connection you have no control over changes your schema you can't do anything 
> about it.

Well, I can. I just have to do another check thru sqlite_master.
But I was hoping for an easier solution...

>
>> Or the only way is to query sqlite_master? But there is no guarantee
>> that the last record in that table with the "'table' || 'view'" condition 
>> willbe that one that was just created/altered.
>
> Correct.  It could be any row in that table.  And they might have DROPped a 
> table just as easily as CREATing a new one.

They might.
But I guess it is the limitation of the embedded database - not
everything can be done in a simple manner. ;-)

>
>> 2. During the application run, someone started sqlite3, connects to
>> the database and creates a
>> brand new table.
>> 3. My application will need to pick up the newly created table and continue.
>
> Why are people creating new tables in a database someone else created ?  
> That's not a common thing to do.  Normally people add records to existing 
> tables.  There are many ways to monitor adding records to an existing table.

Not necessary other people. I might as well open the shell and
create/drop a table.

Thank you.

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

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi,

On Tue, Jun 19, 2018 at 1:56 PM, Richard Hipp  wrote:
> On 6/19/18, Igor Korot  wrote:
>> Hi, Wout,
>>
>> On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens 
>> wrote:
>>> you can query the table with
>>> https://www.sqlite.org/pragma.html#pragma_table_info
>>
>> Let me give you a scenario:
>>
>> 1. My application connects to the database and performs some
>> operations (using C API).
>> 2. During the application run, someone started sqlite3, connects to
>> the database and creates a
>> brand new table.
>> 3. My application will need to pick up the newly created table and continue.
>>
>> Is it easily possible?
>>
>> There is a sqlite3_*_hook() family of functions, but it looks like
>> they won't help with sqlite_master.
>>
>> Is there a different way?
>
> Poll the PRAGMA schema_version value and watch for changes.

Is there a way to get which command was executed?
Or which table was added/changed/dropped?

Or the only way is to query sqlite_master? But there is no guarantee
that the last record in that table with the "'table' || 'view'" condition will
be that one that was just created/altered.

Thank you.



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

Re: [sqlite] column types and constraints

[Igor Korot]

Hi, ALL,
I am not sure what are we talking about here and is discussed.
Just like on the paper the hard drive is storing the characters. It is
for us (humans, developers)
to interpret those characters as a TEXT, numeric value (be it INTEGER
or FLOAT/DOUBLE)
or some binary data.

As long as the client retrieves the exact set of characters that was
stored (and it is)
there is nothing to talk about. Because this is exactly what the
database is for - store
some data for the future use.

The strong typing in the DBMSes are there to simplify the life of the
people who creates
the library which is used by the actual developers to create client
application. Nothing more,
nothing less.
Just imagine if MS Access had the affinity paradigm instead of strong
data types. How hard would be
to write the VB for Access for MS devs.

So once again - it is exactly as on the paper - characters that should
be interpreted by people
(developers).

Thank you.

P.S.: I hope I'm right here and if not someone will surely correct me.


On Sat, Jun 30, 2018 at 3:57 PM, Keith Medcalf  wrote:
>
> In your case, yes.
>
> If you do not wish SQLite3 to "convert" to the requested storage type on 
> storage of a value, then do not specify a storage type (or specify a storage 
> type of BLOB).  Then whatever you request-to-store will be stored without 
> conversion.
>
> SQLite version 3.25.0 2018-06-21 23:53:54
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table x(x);
> sqlite> insert into x values ('3');
> sqlite> insert into x values ('3.0');
> sqlite> insert into x values (3);
> sqlite> insert into x values (3.0);
> sqlite> insert into x values (x'123456789084759301939875459381798754');
> sqlite> insert into x values (null);
> sqlite> select x, typeof(x) from x;
> 3|text
> 3.0|text
> 3|integer
> 3.0|real
>  4Vx��u� ��uE��y�T|blob
> |null
> sqlite>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>>Sent: Saturday, 30 June, 2018 14:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] column types and constraints
>>
>>> when in fact it was the third-party interface wrapper.
>>
>>The examples I provided were all taken from the current sqlite3.exe
>>cli with 3.24.0 library. It is not a third-party issue.
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, again,
This page: https://www.sqlite.org/threadsafe.html, says that the
default mode for SQLite is "Serialized".
It is also said that in this mode it is safe to use SQLite in multiple threads.

I am planning to do the polling in the secondary thread and SQLite was
compiled with the default parameters.
It means that I can safely pass the connection I made to the secondary
thread and update my table cache
and everything will work just fine.

Am I reading the docs correctly?

Thank you.


On Wed, Jun 20, 2018 at 9:17 PM, Igor Korot  wrote:
> David,
>
> On Wed, Jun 20, 2018 at 9:12 PM, David Empson  wrote:
>> Apart from the SQLITE_OK vs SQLITE_ROW/DONE check on the sqlite3_step() call 
>> mentioned already, you also have the third parameter to sqlite_prepare_v2() 
>> wrong: nByte = NULL will translate to nByte = 0 which is documented as “no 
>> prepared statement is generated”. Therefore stmt is not valid and 
>> sqlite3_step() returns SQLITE_MISUSE.
>
> That was it.
> I don't usually supply the hardcoded query to the sqlite3_prepare_v2()
> call, just a variable name.
>
> Thank you.
>
>>
>> Try -1 instead of NULL.
>>
>>> On 21/06/2018, at 12:44 PM, Igor Korot  wrote:
>>>
>>> Hi, guys,
>>> I put in this code:
>>>
>>>if( sqlite3_prepare_v2( m_db, "PRAGMA
>>> schema_version", NULL, , NULL ) == SQLITE_OK )
>>>{
>>>if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>>>{
>>>m_schema = sqlite3_column_int( stmt, 0 );
>>>pimpl->m_dbName = sqlite_pimpl->m_catalog;
>>>}
>>>else
>>>{
>>>}
>>>}
>>>else
>>>{
>>>}
>>>
>>> The call to sqlite3_step() failed - it returned 21.
>>>
>>> Anyone sees any issues?
>>>
>>> Thank you.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

David,

On Wed, Jun 20, 2018 at 9:12 PM, David Empson  wrote:
> Apart from the SQLITE_OK vs SQLITE_ROW/DONE check on the sqlite3_step() call 
> mentioned already, you also have the third parameter to sqlite_prepare_v2() 
> wrong: nByte = NULL will translate to nByte = 0 which is documented as “no 
> prepared statement is generated”. Therefore stmt is not valid and 
> sqlite3_step() returns SQLITE_MISUSE.

That was it.
I don't usually supply the hardcoded query to the sqlite3_prepare_v2()
call, just a variable name.

Thank you.

>
> Try -1 instead of NULL.
>
>> On 21/06/2018, at 12:44 PM, Igor Korot  wrote:
>>
>> Hi, guys,
>> I put in this code:
>>
>>if( sqlite3_prepare_v2( m_db, "PRAGMA
>> schema_version", NULL, , NULL ) == SQLITE_OK )
>>{
>>if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>>{
>>m_schema = sqlite3_column_int( stmt, 0 );
>>pimpl->m_dbName = sqlite_pimpl->m_catalog;
>>}
>>else
>>{
>>}
>>}
>>else
>>{
>>}
>>
>> The call to sqlite3_step() failed - it returned 21.
>>
>> Anyone sees any issues?
>>
>> Thank you.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Richard,

On Wed, Jun 20, 2018 at 8:17 PM, Richard Hipp  wrote:
> On 6/20/18, Igor Korot  wrote:
>> if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>
> sqlite3_step() returns SQLITE_ROW when it has data, not SQLITE_OK.

But SQLITE_ROW value is not 21 - its 101.

Thank you.

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

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, guys,
I put in this code:

if( sqlite3_prepare_v2( m_db, "PRAGMA
schema_version", NULL, , NULL ) == SQLITE_OK )
{
if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
{
m_schema = sqlite3_column_int( stmt, 0 );
pimpl->m_dbName = sqlite_pimpl->m_catalog;
}
else
{
}
}
else
{
}

The call to sqlite3_step() failed - it returned 21.

Anyone sees any issues?

Thank you.

On Wed, Jun 20, 2018 at 6:32 AM, Simon Slavin  wrote:
> On 20 Jun 2018, at 12:29pm, Simon Slavin  wrote:
>
>> On 20 Jun 2018, at 7:24am, Peter Johnson  wrote:
>>
>>> Is it possible to create a trigger on sqlite_master which calls a
>>> user-defined function AFTER INSERT?
>>
>> No.  sqlite_master is modified using internal methods, not using an INSERT 
>> command.  TRIGGERs on it won't work.
>
> Are you not able to modify the program which adds so that it uses an existing 
> table instead ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, guys,

On Tue, Jun 19, 2018 at 4:37 PM, Igor Korot  wrote:
> Hi, Ryan,
>
> On Tue, Jun 19, 2018 at 3:22 PM, R Smith  wrote:
>>
>> On 2018/06/19 8:26 PM, Igor Korot wrote:
>>>
>>>   Hi,
>>> Is there a C API which checks if the new table has been created?
>>
>>
>> We could break this down into a few separate questions:
>>
>> 1 - Is there a C API that can return SQL query answers?
>> YES there is.
>>
>> 2 - Can I ask this API in SQL if the Schema changed, since
>> altering/adding/deleting a table involves schema changes?
>> YES you can. [I see this pragma schema_version is already mentioned in other
>> replies]
>>
>> 3 - Once I know the schema changed, can I ask for a list of tables to
>> compare to my predefined/pre-loaded/cached list to know WHICH tables were
>> changed/added/deleted?
>> YES you can: [pragma table_info]
>>
>> 4 - Can I use all of the above to update my cached table list and refresh
>> interfaces as needed?
>> Well, technically that's up to your mad skillz as a programmer, but having
>> seen your posts many times before, I'm going to bet on: YES, you can.
>>
>> 5 - Is there a C-API that does all of the above in one go for me?
>> I'm afraid NOT.
>
> Thank you.
> This post summarizes it all.
>
> I'll follow thru when I get back home.

One more question:

I presume I should call PRAGMA schema_version right after connection
has been made,
cache the value returned and then create a secondary thread which will
call this query continuously.

Am I right?

Thank you.

>
>
>>
>>
>> Good luck!
>> Ryan
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, Ryan,

On Tue, Jun 19, 2018 at 3:22 PM, R Smith  wrote:
>
> On 2018/06/19 8:26 PM, Igor Korot wrote:
>>
>>   Hi,
>> Is there a C API which checks if the new table has been created?
>
>
> We could break this down into a few separate questions:
>
> 1 - Is there a C API that can return SQL query answers?
> YES there is.
>
> 2 - Can I ask this API in SQL if the Schema changed, since
> altering/adding/deleting a table involves schema changes?
> YES you can. [I see this pragma schema_version is already mentioned in other
> replies]
>
> 3 - Once I know the schema changed, can I ask for a list of tables to
> compare to my predefined/pre-loaded/cached list to know WHICH tables were
> changed/added/deleted?
> YES you can: [pragma table_info]
>
> 4 - Can I use all of the above to update my cached table list and refresh
> interfaces as needed?
> Well, technically that's up to your mad skillz as a programmer, but having
> seen your posts many times before, I'm going to bet on: YES, you can.
>
> 5 - Is there a C-API that does all of the above in one go for me?
> I'm afraid NOT.

Thank you.
This post summarizes it all.

I'll follow thru when I get back home.


>
>
> Good luck!
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Igor Korot]

Hi, Wout,

On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens  wrote:
> you can query the table with
> https://www.sqlite.org/pragma.html#pragma_table_info

Let me give you a scenario:

1. My application connects to the database and performs some
operations (using C API).
2. During the application run, someone started sqlite3, connects to
the database and creates a
brand new table.
3. My application will need to pick up the newly created table and continue.

Is it easily possible?

There is a sqlite3_*_hook() family of functions, but it looks like
they won't help with sqlite_master.

Is there a different way?

Thank you.

>
> On Tue, Jun 19, 2018, 8:26 PM Igor Korot  wrote:
>
>>  Hi,
>> Is there a C API which checks if the new table has been created?
>>
>> Thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check if the new table has been created

[Igor Korot]

 Hi,
Is there a C API which checks if the new table has been created?

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

Re: [sqlite] Reset the cursor

[Igor Korot]

Keith,
Thank you. After upgrading everything works as expected. It was just
an old library version.

And I won't even bother trying to track down the fixing commit. ;-)

And thanks to Olivier for a suggestion about the "SELECT.. " statement
instead of mprintf().

The issue is closed.


On Thu, Jun 7, 2018 at 9:39 PM, Keith Medcalf  wrote:
>
> Yes, 3.24.0 is officially released and the official release page on the 
> sqlite.org website has been updated.  I don't think I saw a release 
> announcement either though.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Thursday, 7 June, 2018 20:19
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>Hi, Keith,
>>
>>On Tue, Jun 5, 2018 at 5:09 PM, Keith Medcalf 
>>wrote:
>>>
>>> Most of them.  In particular those that return (as in SELECT) data
>>work either way.  Those that set things can only be used as a pragma.
>>>
>>> Note that the table name is passed differently (in the case of
>>pragma's expecting an identifier).
>>> It is an identifier in the case of a pragma statement, and a string
>>in the case of the table valued function.
>>>
>>> pragma foreign_key_list(identifier);
>>> select * from pragma_foreign_key_list(stringval);
>>>
>>> ie:
>>>
>>> pragma foreign_key_list("My Table");
>>> select * from foreign_key_list('My Table');
>>>
>>> or, if you do not have stoopid characters in identifiers:
>>>
>>> pragma foreign_key_list(MyTable);
>>> select * from foreign_key_list('MyTable');
>>>
>>>
>>> Both versions behave identically and reset/auto-reset properly for
>>me ...
>>
>>Here is the session with the SQLite version I'm currently developing:
>>
>>[code]
>>SQLite version 3.13.0 2016-05-18 10:57:30
>>Enter ".help" for usage hints.
>>sqlite> SELECT* FROM leagues;
>>1|Demo - Roto (Auction)|1|1|1|demo|260|0
>>2|Demo - Roto (Draft)|3|1|1|demo|260|0
>>3|Demo - Points (Auction)|1|2|1|demo|260|0
>>4|Demo - Points (Draft)|3|2|1|demo|260|0
>>sqlite> SELECT * FROM pragma_foreign_key_list(leagues);
>>Error: no such table: pragma_foreign_key_list
>>sqlite>
>>[/code]
>>
>>This version is a little behind (I believe something like 4 years
>>old).
>>Now, since I'm still developing I can upgrade that version, but when
>>I
>>went to the SQLite
>>download page I see the SQLite 3.24 version and I don't remember the
>>official release
>>announcement being sent out by Mr. Hipp.
>>Is today sources/shell tool is the released version and I just missed
>>the announcement?
>>
>>Thank you.
>>
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia
>>>>Sent: Tuesday, 5 June, 2018 15:35
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] Reset the cursor
>>>>
>>>>> Le 5 juin 2018 à 22:47, Igor Korot  a écrit :
>>>>>
>>>>> As a side note: is it the case for all PRAGMA's command - they
>>can
>>>>be
>>>>> rewritten this way?
>>>>
>>>>Full documentation for that is on page
>>>>https://www.sqlite.org/pragma.html, see the second title ("PRAGMA
>>>>functions").
>>>>
>>>>Citing in short: // PRAGMAs that return results and that have no
>>>>side-effects can be accessed from ordinary SELECT statements as
>>>>table-valued functions. For each participating PRAGMA, the
>>>>corresponding table-valued function has the same name as the PRAGMA
>>>>with a 7-character "pragma_" prefix. //
>>>>
>>>>--
>>>>Best Regards, Meilleures salutations, Met vriendelijke groeten,
>>>>Olivier Mascia
>>>>
>>>>
>>>>___
>>>>sqlite-users mailing list
>>>>sqlite-users@mailinglists.sqlite.org
>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>users
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>users
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Keith,

On Tue, Jun 5, 2018 at 5:09 PM, Keith Medcalf  wrote:
>
> Most of them.  In particular those that return (as in SELECT) data work 
> either way.  Those that set things can only be used as a pragma.
>
> Note that the table name is passed differently (in the case of pragma's 
> expecting an identifier).
> It is an identifier in the case of a pragma statement, and a string in the 
> case of the table valued function.
>
> pragma foreign_key_list(identifier);
> select * from pragma_foreign_key_list(stringval);
>
> ie:
>
> pragma foreign_key_list("My Table");
> select * from foreign_key_list('My Table');
>
> or, if you do not have stoopid characters in identifiers:
>
> pragma foreign_key_list(MyTable);
> select * from foreign_key_list('MyTable');
>
>
> Both versions behave identically and reset/auto-reset properly for me ...

Here is the session with the SQLite version I'm currently developing:

[code]
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> SELECT* FROM leagues;
1|Demo - Roto (Auction)|1|1|1|demo|260|0
2|Demo - Roto (Draft)|3|1|1|demo|260|0
3|Demo - Points (Auction)|1|2|1|demo|260|0
4|Demo - Points (Draft)|3|2|1|demo|260|0
sqlite> SELECT * FROM pragma_foreign_key_list(leagues);
Error: no such table: pragma_foreign_key_list
sqlite>
[/code]

This version is a little behind (I believe something like 4 years old).
Now, since I'm still developing I can upgrade that version, but when I
went to the SQLite
download page I see the SQLite 3.24 version and I don't remember the
official release
announcement being sent out by Mr. Hipp.
Is today sources/shell tool is the released version and I just missed
the announcement?

Thank you.

>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia
>>Sent: Tuesday, 5 June, 2018 15:35
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>> Le 5 juin 2018 à 22:47, Igor Korot  a écrit :
>>>
>>> As a side note: is it the case for all PRAGMA's command - they can
>>be
>>> rewritten this way?
>>
>>Full documentation for that is on page
>>https://www.sqlite.org/pragma.html, see the second title ("PRAGMA
>>functions").
>>
>>Citing in short: // PRAGMAs that return results and that have no
>>side-effects can be accessed from ordinary SELECT statements as
>>table-valued functions. For each participating PRAGMA, the
>>corresponding table-valued function has the same name as the PRAGMA
>>with a 7-character "pragma_" prefix. //
>>
>>--
>>Best Regards, Meilleures salutations, Met vriendelijke groeten,
>>Olivier Mascia
>>
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi,  Olivier,

On Tue, Jun 5, 2018 at 3:15 PM, Olivier Mascia  wrote:
>> Le 5 juin 2018 à 18:19, Igor Korot  a écrit :
>>
>> My query is:
>>
>> std::string query = "PRAGMA foreign_key_list( \"%w\" )";
>>
>> Then I'm doing this:
>>
>> char *y = sqlite3_mprintf( query.c_str(), tableName );
>> res = sqlite3_prepare_v2( m_db, y, -1, , 0 );
>>
>> and then the code follows.
>>
>> So are you saying that this PRAGMA is not using the SELECT internally?
>
> I haven't dig this discussion thread in details, but you could try:
>
> std::string query = "SELECT * FROM pragma_foreign_key_list(?)";
>
> You will then be able to bind the table name after prepare without using 
> printf, which is good protection against code injection depending from where 
> your tableName value comes from and will allow you to bind new table names 
> and re-run without preparing the statement again.

Thank you for the suggestion. I will try that.

As a side note: is it the case for all PRAGMA's command - they can be
rewritten this way?

Thank you.

>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Keith,

On Tue, Jun 5, 2018 at 11:04 AM, Keith Medcalf  wrote:
>
> Hmmm.  I replaced the :memory: database with an on-disk database and created 
> a table with nothing in it.  The first run returned SQLITE_DONE immediately 
> and the resets worked normally.  Are you using a virtual table or a 
> non-select statement?

No virtual table(s).

My query is:

std::string query = "PRAGMA foreign_key_list( \"%w\" )";

Then I'm doing this:

char *y = sqlite3_mprintf( query.c_str(), tableName );
res = sqlite3_prepare_v2( m_db, y, -1, , 0 );

and then the code follows.

So are you saying that this PRAGMA is not using the SELECT internally?

I'm trying to workaround this by checking if the first loop return any
records and skip second if it does not.

Thank you.

P.S.: Using C++ here, but the interface should be the same.

>
> SQLite version 3.24.0 2018-06-04 19:24:41
> Enter ".help" for usage hints.
> sqlite> delete from test;
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test (id integer primary key);
> COMMIT;
>
> Changed the C code to:
>
> sqlite3_open("test.db", );
> rc = sqlite3_prepare_v2(db, "select id from test;", -1, , 
> (void*));
>
> and running it gets:
>
>>test
>
> Loop 1, no reset, reset at 5
> !
> sqlite3_reset returns 0
>
> Loop 2, After Reset
> !
>
> Loop 3, No Reset, Got SQLITE_DONE
> !
> sqlite3_reset returns 0
>
> which is what I would expect ...
>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Tuesday, 5 June, 2018 08:54
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>Hi, Keith,
>>I'm sorry for that but I just realized what is the difference between
>>you program and mine.
>>
>>Can you modify the query to not return any rows and re-run your test
>>program?
>>Because when I run it first time the query doesn't return any rows
>>and
>>n the first iteration sqlite3_step()
>>gives SQLITE_DONE.
>>
>>But I guess the next time it runs it just fails for some in-known
>>reason.
>>If that's not it - I guess I will have to give you the test case for
>>it.
>>
>>Thank you and sorry for not thinking about this immediately.
>>
>>
>>On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot 
>>wrote:
>>> Hi, Keith,
>>>
>>> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf 
>>wrote:
>>>>
>>>> Perhaps.  In order for the sqlite3_errcode(db) to have any meaning
>>then the immediately preceding API call must have failed with an
>>error (that is, returned a result other than SQLITE_OK, SQLITE_ROW,
>>or SQLITE_DONE).  The sqlite3_errcode is *ONLY* updated when there is
>>an error (ie, the return code from an API call is not OK / ROW /
>>DONE).  Otherwise calling the sqlite3_errcode function will either
>>return the errorcode from the most recently called failing API call
>>or whatever garbage happens to be contained in that memory location.
>>>>
>>>> According to the documentation:
>>>>
>>>> "If the most recent sqlite3_* API call associated with database
>>connection D failed, then the sqlite3_errcode(D) interface returns
>>the numeric result code or extended result code for that API call. If
>>the most recent API call was successful, then the return value from
>>sqlite3_errcode() is undefined."
>>>>
>>>> where undefined means that the value returned has no meaning ...
>>>>
>>>> "If an interface fails with SQLITE_MISUSE, that means the
>>interface was invoked incorrectly by the application. In that case,
>>the error code and message may or may not be set."
>>>>
>>>> So, if and only if "rc = sqlite3_(...)" returns a code (rc)
>>which is *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is
>>the sqlite3_errcode meaningful with the caveat that if rc is
>>SQLITE_MISUSE (23) then mayhaps yes and mayhaps no be meaningful.  It
>>also only returns data for the last API call on a connection.
>>>>
>>>>
>>>> so the idea is that you get the return code of the API call ...
>>>> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW,
>>SQLITE_DONE]) should you even look at the sqlite3_errcode
>>>> if the origina

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Keith,
I'm sorry for that but I just realized what is the difference between
you program and mine.

Can you modify the query to not return any rows and re-run your test program?
Because when I run it first time the query doesn't return any rows and
n the first iteration sqlite3_step()
gives SQLITE_DONE.

But I guess the next time it runs it just fails for some in-known reason.
If that's not it - I guess I will have to give you the test case for it.

Thank you and sorry for not thinking about this immediately.


On Tue, Jun 5, 2018 at 6:38 AM, Igor Korot  wrote:
> Hi, Keith,
>
> On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf  wrote:
>>
>> Perhaps.  In order for the sqlite3_errcode(db) to have any meaning then the 
>> immediately preceding API call must have failed with an error (that is, 
>> returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE).  The 
>> sqlite3_errcode is *ONLY* updated when there is an error (ie, the return 
>> code from an API call is not OK / ROW / DONE).  Otherwise calling the 
>> sqlite3_errcode function will either return the errorcode from the most 
>> recently called failing API call or whatever garbage happens to be contained 
>> in that memory location.
>>
>> According to the documentation:
>>
>> "If the most recent sqlite3_* API call associated with database connection D 
>> failed, then the sqlite3_errcode(D) interface returns the numeric result 
>> code or extended result code for that API call. If the most recent API call 
>> was successful, then the return value from sqlite3_errcode() is undefined."
>>
>> where undefined means that the value returned has no meaning ...
>>
>> "If an interface fails with SQLITE_MISUSE, that means the interface was 
>> invoked incorrectly by the application. In that case, the error code and 
>> message may or may not be set."
>>
>> So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is 
>> *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the 
>> sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) 
>> then mayhaps yes and mayhaps no be meaningful.  It also only returns data 
>> for the last API call on a connection.
>>
>>
>> so the idea is that you get the return code of the API call ...
>> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, 
>> SQLITE_DONE]) should you even look at the sqlite3_errcode
>> if the original API return code was SQLITE_MISUSE then the result of 
>> sqlite3_errcode may mean something and may not
>
> Removing thay call made no difference.
> I am still getting 1 on the second iteration.
>
> Thank you.
>
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
>> lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>>Sent: Monday, 4 June, 2018 12:15
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] Reset the cursor
>>>
>>>Keith,
>>>
>>>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf 
>>>wrote:
>>>>
>>>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was
>>>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE
>>>error on the 3rd loop because the statement was not reset.
>>>
>>>One more thing:
>>>
>>>Is my assumption correct that sqlite3_errcode() returning 0, indicate
>>>there was no error?
>>>
>>>Thank you.
>>>
>>>>
>>>> ---
>>>> The fact that there's a Highway to Hell but only a Stairway to
>>>Heaven says a lot about anticipated traffic volume.
>>>>
>>>>
>>>>>-Original Message-
>>>>>From: sqlite-users [mailto:sqlite-users-
>>>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>>>>Sent: Monday, 4 June, 2018 11:50
>>>>>To: SQLite mailing list
>>>>>Subject: Re: [sqlite] Reset the cursor
>>>>>
>>>>>Keith,
>>>>>
>>>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf
>>>
>>>>>wrote:
>>>>>>
>>>>>> #include "sqlite3.h"
>>>>>> #include 
>>>>>>
>>>>>> void main(int argc, char** argv)
>>>>>> {
>>>>>> sqlite3* db = 0;
>>>>>> sqlite3_stm

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Keith,

On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf  wrote:
>
> Perhaps.  In order for the sqlite3_errcode(db) to have any meaning then the 
> immediately preceding API call must have failed with an error (that is, 
> returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE).  The 
> sqlite3_errcode is *ONLY* updated when there is an error (ie, the return code 
> from an API call is not OK / ROW / DONE).  Otherwise calling the 
> sqlite3_errcode function will either return the errorcode from the most 
> recently called failing API call or whatever garbage happens to be contained 
> in that memory location.
>
> According to the documentation:
>
> "If the most recent sqlite3_* API call associated with database connection D 
> failed, then the sqlite3_errcode(D) interface returns the numeric result code 
> or extended result code for that API call. If the most recent API call was 
> successful, then the return value from sqlite3_errcode() is undefined."
>
> where undefined means that the value returned has no meaning ...
>
> "If an interface fails with SQLITE_MISUSE, that means the interface was 
> invoked incorrectly by the application. In that case, the error code and 
> message may or may not be set."
>
> So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is 
> *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the 
> sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) 
> then mayhaps yes and mayhaps no be meaningful.  It also only returns data for 
> the last API call on a connection.
>
>
> so the idea is that you get the return code of the API call ...
> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, 
> SQLITE_DONE]) should you even look at the sqlite3_errcode
> if the original API return code was SQLITE_MISUSE then the result of 
> sqlite3_errcode may mean something and may not

Removing thay call made no difference.
I am still getting 1 on the second iteration.

Thank you.

>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Monday, 4 June, 2018 12:15
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>Keith,
>>
>>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf 
>>wrote:
>>>
>>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was
>>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE
>>error on the 3rd loop because the statement was not reset.
>>
>>One more thing:
>>
>>Is my assumption correct that sqlite3_errcode() returning 0, indicate
>>there was no error?
>>
>>Thank you.
>>
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>>>Sent: Monday, 4 June, 2018 11:50
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] Reset the cursor
>>>>
>>>>Keith,
>>>>
>>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf
>>
>>>>wrote:
>>>>>
>>>>> #include "sqlite3.h"
>>>>> #include 
>>>>>
>>>>> void main(int argc, char** argv)
>>>>> {
>>>>> sqlite3* db = 0;
>>>>> sqlite3_stmt* stmt = 0;
>>>>> char* rest = 0;
>>>>> int rc = 0;
>>>>> int value = 0;
>>>>> sqlite3_open(":memory:", );
>>>>> rc = sqlite3_prepare_v2(db, "select value from
>>generate_series
>>>>where start=1 and stop=10;", -1, , (void*));
>>>>> if (rc != SQLITE_OK)
>>>>> {
>>>>> printf("Error %d during prepare\n", rc);
>>>>> return;
>>>>> }
>>>>> printf("\nLoop 1, no reset, reset at 5\n");
>>>>> for (;;)
>>>>> {
>>>>> rc = sqlite3_step(stmt);
>>>>> if (rc == SQLITE_DONE | value == 5)
>>>>> {
>>>>> printf("!\n");
>>>>> rc = sqlite3_reset(stmt);
&

Re: [sqlite] Reset the cursor

[Igor Korot]

Keith,

On Mon, Jun 4, 2018 at 1:39 PM, Keith Medcalf  wrote:
>
> Perhaps.  In order for the sqlite3_errcode(db) to have any meaning then the 
> immediately preceding API call must have failed with an error (that is, 
> returned a result other than SQLITE_OK, SQLITE_ROW, or SQLITE_DONE).  The 
> sqlite3_errcode is *ONLY* updated when there is an error (ie, the return code 
> from an API call is not OK / ROW / DONE).  Otherwise calling the 
> sqlite3_errcode function will either return the errorcode from the most 
> recently called failing API call or whatever garbage happens to be contained 
> in that memory location.
>
> According to the documentation:
>
> "If the most recent sqlite3_* API call associated with database connection D 
> failed, then the sqlite3_errcode(D) interface returns the numeric result code 
> or extended result code for that API call. If the most recent API call was 
> successful, then the return value from sqlite3_errcode() is undefined."
>
> where undefined means that the value returned has no meaning ...
>
> "If an interface fails with SQLITE_MISUSE, that means the interface was 
> invoked incorrectly by the application. In that case, the error code and 
> message may or may not be set."
>
> So, if and only if "rc = sqlite3_(...)" returns a code (rc) which is 
> *NOT* SQLITE_OK (0), SQLITE_ROW (100), SQLITE_DONE (101) is the 
> sqlite3_errcode meaningful with the caveat that if rc is SQLITE_MISUSE (23) 
> then mayhaps yes and mayhaps no be meaningful.  It also only returns data for 
> the last API call on a connection.
>
>
> so the idea is that you get the return code of the API call ...
> if and only if this is a failure (not in [SQLITE_OK, SQLITE_ROW, 
> SQLITE_DONE]) should you even look at the sqlite3_errcode
> if the original API return code was SQLITE_MISUSE then the result of 
> sqlite3_errcode may mean something and may not

But then this is even more confusing.
As I said in the beginning:

The second cycle' sqlite3_step() returns 1, but immediately calling
sqlite3_errcode() returns 0.

I just don't understand how to interpret those results.

Can you?

Thank you.

>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Monday, 4 June, 2018 12:15
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>Keith,
>>
>>On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf 
>>wrote:
>>>
>>> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was
>>compiled with SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE
>>error on the 3rd loop because the statement was not reset.
>>
>>One more thing:
>>
>>Is my assumption correct that sqlite3_errcode() returning 0, indicate
>>there was no error?
>>
>>Thank you.
>>
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>>>Sent: Monday, 4 June, 2018 11:50
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] Reset the cursor
>>>>
>>>>Keith,
>>>>
>>>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf
>>
>>>>wrote:
>>>>>
>>>>> #include "sqlite3.h"
>>>>> #include 
>>>>>
>>>>> void main(int argc, char** argv)
>>>>> {
>>>>> sqlite3* db = 0;
>>>>> sqlite3_stmt* stmt = 0;
>>>>> char* rest = 0;
>>>>> int rc = 0;
>>>>> int value = 0;
>>>>> sqlite3_open(":memory:", );
>>>>> rc = sqlite3_prepare_v2(db, "select value from
>>generate_series
>>>>where start=1 and stop=10;", -1, , (void*));
>>>>> if (rc != SQLITE_OK)
>>>>> {
>>>>> printf("Error %d during prepare\n", rc);
>>>>> return;
>>>>> }
>>>>> printf("\nLoop 1, no reset, reset at 5\n");
>>>>> for (;;)
>>>>> {
>>>>> rc = sqlite3_step(stmt);
>>>>> if (rc == SQLITE_DONE | value == 5)

Re: [sqlite] Reset the cursor

[Igor Korot]

Keith,

On Mon, Jun 4, 2018 at 12:59 PM, Keith Medcalf  wrote:
>
> Yes, if it is 3.6.23.1 or earlier or the sqlite3 library was compiled with 
> SQLITE_OMIT_AUTORESET you will get an SQLITE_MISUSE error on the 3rd loop 
> because the statement was not reset.

One more thing:

Is my assumption correct that sqlite3_errcode() returning 0, indicate
there was no error?

Thank you.

>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Monday, 4 June, 2018 11:50
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>Keith,
>>
>>On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf 
>>wrote:
>>>
>>> #include "sqlite3.h"
>>> #include 
>>>
>>> void main(int argc, char** argv)
>>> {
>>> sqlite3* db = 0;
>>> sqlite3_stmt* stmt = 0;
>>> char* rest = 0;
>>> int rc = 0;
>>> int value = 0;
>>> sqlite3_open(":memory:", );
>>> rc = sqlite3_prepare_v2(db, "select value from generate_series
>>where start=1 and stop=10;", -1, , (void*));
>>> if (rc != SQLITE_OK)
>>> {
>>> printf("Error %d during prepare\n", rc);
>>> return;
>>> }
>>> printf("\nLoop 1, no reset, reset at 5\n");
>>> for (;;)
>>> {
>>> rc = sqlite3_step(stmt);
>>> if (rc == SQLITE_DONE | value == 5)
>>> {
>>> printf("!\n");
>>> rc = sqlite3_reset(stmt);
>>> printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> if (rc == SQLITE_ROW)
>>> {
>>> value = sqlite3_column_int(stmt, 0);
>>> printf("%d ", value);
>>> continue;
>>> }
>>> printf("Error during stepping %d\n", rc);
>>> rc = sqlite3_reset(stmt);
>>> printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> printf("\nLoop 2, After Reset\n");
>>> for (;;)
>>> {
>>> rc = sqlite3_step(stmt);
>>> if (rc == SQLITE_DONE)
>>> {
>>> printf("!\n");
>>> //rc = sqlite3_reset(stmt);
>>> //printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> if (rc == SQLITE_ROW)
>>> {
>>> value = sqlite3_column_int(stmt, 0);
>>> printf("%d ", value);
>>> continue;
>>> }
>>> printf("Error during stepping %d\n", rc);
>>> rc = sqlite3_reset(stmt);
>>> printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> printf("\nLoop 3, No Reset, Got SQLITE_DONE\n");
>>> for (;;)
>>> {
>>> rc = sqlite3_step(stmt);
>>> if (rc == SQLITE_DONE)
>>> {
>>> printf("!\n");
>>> rc = sqlite3_reset(stmt);
>>> printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> if (rc == SQLITE_ROW)
>>> {
>>> value = sqlite3_column_int(stmt, 0);
>>> printf("%d ", value);
>>> continue;
>>> }
>>> printf("Error during stepping %d\n", rc);
>>> rc = sqlite3_reset(stmt);
>>> printf("sqlite3_reset returns %d\n", rc);
>>> break;
>>> }
>>> }
>>>
>>> 2018-06-04 11:32:12 MinGW [D:\work]
>>>>test
>>>
>>> Loop 1, no reset, reset at 5
>>> 1 2 3 4 5 !
>>> sqlite3_reset returns 0
>>>
>>> Loop 2, After Reset
>>> 1 2 3 4 5 6 7 8 9 10 !
>>>
>>> Loop 3, No Reset, Got SQLITE_DONE
>>> 1 2 3 4 5 6 7 8 9 10 !
>>> sqlite3_reset returns 0
>>
>>I will try without this call tonight when I'm back from work and let
>>you know.
>

Re: [sqlite] Reset the cursor

[Igor Korot]

Keith,

On Mon, Jun 4, 2018 at 12:35 PM, Keith Medcalf  wrote:
>
> #include "sqlite3.h"
> #include 
>
> void main(int argc, char** argv)
> {
> sqlite3* db = 0;
> sqlite3_stmt* stmt = 0;
> char* rest = 0;
> int rc = 0;
> int value = 0;
> sqlite3_open(":memory:", );
> rc = sqlite3_prepare_v2(db, "select value from generate_series where 
> start=1 and stop=10;", -1, , (void*));
> if (rc != SQLITE_OK)
> {
> printf("Error %d during prepare\n", rc);
> return;
> }
> printf("\nLoop 1, no reset, reset at 5\n");
> for (;;)
> {
> rc = sqlite3_step(stmt);
> if (rc == SQLITE_DONE | value == 5)
> {
> printf("!\n");
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> if (rc == SQLITE_ROW)
> {
> value = sqlite3_column_int(stmt, 0);
> printf("%d ", value);
> continue;
> }
> printf("Error during stepping %d\n", rc);
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> printf("\nLoop 2, After Reset\n");
> for (;;)
> {
> rc = sqlite3_step(stmt);
> if (rc == SQLITE_DONE)
> {
> printf("!\n");
> //rc = sqlite3_reset(stmt);
> //printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> if (rc == SQLITE_ROW)
> {
> value = sqlite3_column_int(stmt, 0);
> printf("%d ", value);
> continue;
> }
> printf("Error during stepping %d\n", rc);
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> printf("\nLoop 3, No Reset, Got SQLITE_DONE\n");
> for (;;)
> {
> rc = sqlite3_step(stmt);
> if (rc == SQLITE_DONE)
> {
> printf("!\n");
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> if (rc == SQLITE_ROW)
> {
> value = sqlite3_column_int(stmt, 0);
> printf("%d ", value);
> continue;
> }
> printf("Error during stepping %d\n", rc);
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> }
>
> 2018-06-04 11:32:12 MinGW [D:\work]
>>test
>
> Loop 1, no reset, reset at 5
> 1 2 3 4 5 !
> sqlite3_reset returns 0
>
> Loop 2, After Reset
> 1 2 3 4 5 6 7 8 9 10 !
>
> Loop 3, No Reset, Got SQLITE_DONE
> 1 2 3 4 5 6 7 8 9 10 !
> sqlite3_reset returns 0

I will try without this call tonight when I'm back from work and let you know.

But if the system have an older version of SQLite this code will break right?

Thank you.

>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>>Sent: Monday, 4 June, 2018 11:25
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reset the cursor
>>
>>
>>Note also that you do not need to do an sqlite3_reset after
>>sqlite3_step returns SQLITE_DONE as reset is called automatically the
>>next time you call sqlite3_step.  You only need to call sqlite3_reset
>>if you want to reset the statement before all the rows have been
>>retrieved (this is documented somewhere, and I believe there is a
>>compile time #define to turn off the auto-reset).  Yes, it is
>>documented in the sqlite3_step documentation
>>
>>"For all versions of SQLite up to and including 3.6.23.1, a call to
>>sqlite3_reset() was required after sqlite3_step() returned anything
>>other than SQLITE_ROW before any subsequent invocation of
>>sqlite3_step(). Failure to reset the prepared statement using
>>sqlite3_reset() would result in an SQLITE_MISUSE return from
>>sqlite3_step(). But after version 3.6.23.1 (2010-03-26,
>>sqlite3_step() began calling sqlite3_reset() automatically in this
>>circumstance rather than returning SQLITE_MISUSE. This is not
>>considered a compatibility break because any application that ever
>>receives an SQLITE_MISUSE error is broken by definition. The
>>SQLITE_OMIT_AUTORESET compile-time option can be used to restore the
>>legacy behavior."
>>
>>Neither the automatic nor the manual sqlite3_reset reset any bindings
>>-- if you want to do this I believe you must call the
>>sqlite3_clear_bindings()
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to Heaven
>>says a lot about anticipated traffic volume.
>>
>>
>>>-Original Message-
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>>>Sent: Monday, 4 June, 2018 11:06
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] Reset the 

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Keith,

On Mon, Jun 4, 2018 at 11:45 AM, Keith Medcalf  wrote:
> Works just fine for me ...
>
>
> #include "sqlite3.h"
> #include 
>
> void main(int argc, char** argv)
> {
> sqlite3* db = 0;
> sqlite3_stmt* stmt = 0;
> char* rest = 0;
> int rc = 0;
> int value = 0;
> sqlite3_open(":memory:", );
> rc = sqlite3_prepare_v2(db, "select value from generate_series where 
> start=1 and stop=10;", -1, , (void*));
> if (rc != SQLITE_OK)
> {
> printf("Error %d during prepare\n", rc);
> return;
> }
> printf("\nLoop 1, no reset\n");
> for (;;)
> {
> rc = sqlite3_step(stmt);
> if (rc == SQLITE_DONE)
> {
> printf("!\n");
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> if (rc == SQLITE_ROW)
> {
> value = sqlite3_column_int(stmt, 0);
> printf("%d ", value);
> continue;
> }
> printf("Error during stepping %d\n", rc);
> break;
> }
> printf("\nLoop 2, after reset\n");
> for (;;)
> {
> rc = sqlite3_step(stmt);
> if (rc == SQLITE_DONE)
> {
> printf("!\n");
> rc = sqlite3_reset(stmt);
> printf("sqlite3_reset returns %d\n", rc);
> break;
> }
> if (rc == SQLITE_ROW)
> {
> value = sqlite3_column_int(stmt, 0);
> printf("%d ", value);
> continue;
> }
> printf("Error during stepping %d\n", rc);
> break;
> }
> }
>
> gcc test.c -L. -lsqlite3 -o test.exe

Currently running w/MSVC 2010 under Win 8.1.

I also presume you are testing under the latest SQLite source?

Thank you.

>
> 2018-06-04 10:41:10 MinGW [D:\work]
>>test
>
> Loop 1, no reset
> 1 2 3 4 5 6 7 8 9 10 !
> sqlite3_reset returns 0
>
> Loop 2, after reset
> 1 2 3 4 5 6 7 8 9 10 !
> sqlite3_reset returns 0
>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
>>Sent: Sunday, 3 June, 2018 22:32
>>To: Discussion of SQLite Database; General Discussion of SQLite
>>Database
>>Subject: [sqlite] Reset the cursor
>>
>>Hi, All,
>>After executing the following:
>>
>>int res = sqlite3_prepare_v2( ... stmt );
>>while( ; ; )
>>{
>>res = sqlite3_step( stmt );
>>if( res == SQLITE_ROW )
>>{
>>// process the record
>>}
>>else if( res == SQLITE_DONE )
>>break;
>>else
>>{
>>// error procressing
>>}
>>}
>>
>>Now I'd like the cursor in the recordset of the "stmt" to go to the
>>record 1
>>so I can process those records again.
>>
>>I thought that this will be a job of sqlite_reset(), but when I
>>called
>>it and started re-processing the recordset I got SQLITE_DONE on the
>>very first iteration.
>>
>>So, how do I reset the cursor to the first record?
>>
>>Thank you.
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

x,

On Mon, Jun 4, 2018 at 10:54 AM, x  wrote:
> If the first loop exits with res3 == SQLITE_DONE then !result will be true 
> and the second loop should process exactly the same (assuming underlying data 
> is unchanged). I can’t see why the code below wouldn’t work although I’m 
> confused by the fact you say that sqlite3_step(stmt3)  returns SQLITE_DONE 
> immediately after the sqlite3_reset(stmt3) but later say it’s returning 1 
> (SQLITE_ERROR).

Yes, first loop exits with the SQLITE_DONE.
The call to sqlite3_reset() return 0 (success).

But the very first call to sqlite3_step() returns 1 (error). Then the
code goes to execute error handling branch where it calls
sqlite3_errcode().
This function returns 0 - which I think means no error is encountered
during the previous SQLite call.

I can try to get an external error code or the error message though
using the appropriate function.

Thank you.

>
>
> int result = 0,  res3 = SQLITE_OK;
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
> // initial processing
> }
> else if( res3 == SQLITE_DONE )
> break;
> else
> {
> // error handling
> result = 1;
> }
> }
> if( !result )
> {
> res3 = sqlite3_reset( stmt3 );
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
>// actual processing
> }
> else if( res3 == SQLITE_DONE )
>break;
> else
> {
>// error handling
> }
> }
> }
>
> Not sure where this code belongs
>
> if( res3 != SQLITE_DONE )
> break;
> }
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

x,

On Mon, Jun 4, 2018 at 9:42 AM, x  wrote:
> int result = 0,  res3 = SQLITE_OK;
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
>
>
> As I said in previous post a successful sqlite3_step doesn’t return SQLITE_OK 
> so
>
> res3 == SQLITE_ROW is never true.

But SQLITE_OK != SQLITE_ROW.

Thank you.

>
>
>
>
>
> ____
> From: sqlite-users  on behalf 
> of Igor Korot 
> Sent: Monday, June 4, 2018 3:33:54 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Reset the cursor
>
> Hi, Igor,
>
> On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik  wrote:
>> On 6/4/2018 12:31 AM, Igor Korot wrote:
>>>
>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record
>>> 1
>>> so I can process those records again.
>>>
>>> I thought that this will be a job of sqlite_reset(), but when I called
>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>> very first iteration.
>>
>>
>> sqlite_reset definitely works. The problem must be somewhere in the code you
>> haven't shown. Can you reproduce in a small complete example?
>
> Following the exact code taken from y source.
> Can you spot an error?
>
> [code]
> int result = 0,  res3 = SQLITE_OK;
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
> // initial processing
> }
> else if( res3 == SQLITE_DONE )
> break;
> else
> {
> // error handling
> result = 1;
> }
> }
> if( !result )
> {
> res3 = sqlite3_reset( stmt3 );
> for( ; ; )
> {
> res3 = sqlite3_step( stmt3 );
> if( res3 == SQLITE_ROW )
> {
> // actual processing
> }
> else if( res3 == SQLITE_DONE )
> break;
> else
> {
> // error handling
> }
> }
> }
> if( res3 != SQLITE_DONE )
> break;
> }[/code]
>
> Thank you.
>
>> --
>> Igor Tandetnik
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

x,

On Mon, Jun 4, 2018 at 9:12 AM, x  wrote:
> Sorry, just looked that up. 1 is SQLITE_ERROR. 100 is SQLITE_ROW
>
>
>
> But note that a successful sqlite3_step does not return SQLITE_OK (0). Are 
> you maybe converting the result to Boolean?

Please see the code I posted in reply to Igor's post.

Thank you.

>
>
>
> 
> From: sqlite-users  on behalf 
> of Igor Korot 
> Sent: Monday, June 4, 2018 1:52:05 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Reset the cursor
>
> Hi,
>
> On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot  wrote:
>> Hi, Clemens et al,
>>
>> On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch  wrote:
>>> Igor Korot wrote:
>>>> res = sqlite3_step( stmt );
>>>>
>>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 
>>>> 1
>>>> so I can process those records again.
>>>
>>> Strictly speaking, it is not possible to go back in the _same_ cursor.
>>> You'd have to execute the query again (by calling sqlite3_reset() and
>>> sqlite3_step()), and if you're not in a transaction, the data might
>>> have been modified between these two calls.
>>>
>>>> I thought that this will be a job of sqlite_reset(), but when I called
>>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>>> very first iteration.
>>>
>>> In theory, executing the same query on the same data should work again.
>>> Did you accidentally call sqlite3_clear_bindings()?
>>
>> No, I didn't clear anything.
>> I just call sqlite3_reset() and sqlite3_step() and receive an error.
>
> In addition:
> As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error).
> However calling sqlite3_errcode() right after returns 0 (which is
> success, right)
>
> Thank you.
>
>>
>> Thank you.
>>
>>>
>>>
>>> Regards,
>>> Clemens
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Igor,

On Mon, Jun 4, 2018 at 7:55 AM, Igor Tandetnik  wrote:
> On 6/4/2018 12:31 AM, Igor Korot wrote:
>>
>> Now I'd like the cursor in the recordset of the "stmt" to go to the record
>> 1
>> so I can process those records again.
>>
>> I thought that this will be a job of sqlite_reset(), but when I called
>> it and started re-processing the recordset I got SQLITE_DONE on the
>> very first iteration.
>
>
> sqlite_reset definitely works. The problem must be somewhere in the code you
> haven't shown. Can you reproduce in a small complete example?

Following the exact code taken from y source.
Can you spot an error?

[code]
int result = 0,  res3 = SQLITE_OK;
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
// initial processing
}
else if( res3 == SQLITE_DONE )
break;
else
{
// error handling
result = 1;
}
}
if( !result )
{
res3 = sqlite3_reset( stmt3 );
for( ; ; )
{
res3 = sqlite3_step( stmt3 );
if( res3 == SQLITE_ROW )
{
// actual processing
}
else if( res3 == SQLITE_DONE )
break;
else
{
// error handling
}
}
}
if( res3 != SQLITE_DONE )
break;
}[/code]

Thank you.

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

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi,

On Mon, Jun 4, 2018 at 7:54 AM, Igor Korot  wrote:
> Hi, Clemens et al,
>
> On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch  wrote:
>> Igor Korot wrote:
>>> res = sqlite3_step( stmt );
>>>
>>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
>>> so I can process those records again.
>>
>> Strictly speaking, it is not possible to go back in the _same_ cursor.
>> You'd have to execute the query again (by calling sqlite3_reset() and
>> sqlite3_step()), and if you're not in a transaction, the data might
>> have been modified between these two calls.
>>
>>> I thought that this will be a job of sqlite_reset(), but when I called
>>> it and started re-processing the recordset I got SQLITE_DONE on the
>>> very first iteration.
>>
>> In theory, executing the same query on the same data should work again.
>> Did you accidentally call sqlite3_clear_bindings()?
>
> No, I didn't clear anything.
> I just call sqlite3_reset() and sqlite3_step() and receive an error.

In addition:
As I said calling sqlite3_step() after sqlite3_reset() returns 1 (error).
However calling sqlite3_errcode() right after returns 0 (which is
success, right)

Thank you.

>
> Thank you.
>
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset the cursor

[Igor Korot]

Hi, Clemens et al,

On Mon, Jun 4, 2018 at 1:23 AM, Clemens Ladisch  wrote:
> Igor Korot wrote:
>> res = sqlite3_step( stmt );
>>
>> Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
>> so I can process those records again.
>
> Strictly speaking, it is not possible to go back in the _same_ cursor.
> You'd have to execute the query again (by calling sqlite3_reset() and
> sqlite3_step()), and if you're not in a transaction, the data might
> have been modified between these two calls.
>
>> I thought that this will be a job of sqlite_reset(), but when I called
>> it and started re-processing the recordset I got SQLITE_DONE on the
>> very first iteration.
>
> In theory, executing the same query on the same data should work again.
> Did you accidentally call sqlite3_clear_bindings()?

No, I didn't clear anything.
I just call sqlite3_reset() and sqlite3_step() and receive an error.

Thank you.

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

[sqlite] Reset the cursor

[Igor Korot]

Hi, All,
After executing the following:

int res = sqlite3_prepare_v2( ... stmt );
while( ; ; )
{
res = sqlite3_step( stmt );
if( res == SQLITE_ROW )
{
// process the record
}
else if( res == SQLITE_DONE )
break;
else
{
// error procressing
}
}

Now I'd like the cursor in the recordset of the "stmt" to go to the record 1
so I can process those records again.

I thought that this will be a job of sqlite_reset(), but when I called
it and started re-processing the recordset I got SQLITE_DONE on the
very first iteration.

So, how do I reset the cursor to the first record?

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

Re: [sqlite] ALTER TABLE

[Igor Korot]

Hi, Charles,

On Tue, May 22, 2018 at 2:30 PM, Charles Leifer  wrote:
> As a workaround, you can always rename the existing table, create the new
> table with desired attributes, and do a INSERT INTO ... SELECT FROM
> old_table. Then you can safely drop the old table.

But the table_name will be different.
Also the data in the old table might be referencing some other table.
So this process is not really very
straightforward...

Thank you.

>
> On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz  wrote:
>
>> > ALTER TABLE ADD COLUMN has existed for a long time.
>>
>> Yes, sorry, I mixed things up.
>>
>> The order of importance is imho:
>> 1. RENAME COLUMN (shouldn't be too hard)
>> 2. DROP COLUMN (should be a bit more comlicated but feasible)
>> 3. MODIFY COLUMN
>>
>> > What kind of MODIFY COLUMN changes do you have in mind?
>>
>> I understand this can be difficult as there are many possible operations
>> that might be incompatible with the data already stored in that column.
>> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE CASCADE
>> action of a foreign key column. Also adding/removing a foreign key would be
>> useful.
>>
>> Kind regards,
>> Thomas
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unknown type name 'sqlite_int64'

[Igor Korot]

Hi,

On Mon, Apr 30, 2018 at 7:31 PM, jungle Boogie <jungleboog...@gmail.com> wrote:
> On 5:20PM, Mon, Apr 30, 2018 Igor Korot <ikoro...@gmail.com> wrote:
>>
>> Hi,
>>
>> On Mon, Apr 30, 2018 at 6:30 PM, jungle Boogie <jungleboog...@gmail.com>
> wrote:
>> > Hi All,
>> >
>> > What's happening here?
>> >
>> > $ cc --version
>> > OpenBSD clang version 6.0.0 (tags/RELEASE_600/final) (based on LLVM
> 6.0.0)
>> > Target: aarch64-unknown-openbsd6.3
>> > Thread model: posix
>> >
>> > ARM64 bit on a pine64-lts device running:
>> > OpenBSD 6.3-current (GENERIC.MP) #91: Mon Apr 30 01:29:06 MDT 2018
>> >
>> >
>> >
>> > sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o
>>
>> What is the full exact command you are using to compile?
>> Do you have sqlite3.h in the same directory as sqlite3.c?
>>
>> Thank you.
>
> Doing a ./configure without any options and simply a make. This is with the
> download.tar.gz file, and the make takes place in in that directory.

Why did you need configure?

Just do make.

Thank you.

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

Re: [sqlite] unknown type name 'sqlite_int64'

[Igor Korot]

Hi,

On Mon, Apr 30, 2018 at 6:30 PM, jungle Boogie  wrote:
> Hi All,
>
> What's happening here?
>
> $ cc --version
> OpenBSD clang version 6.0.0 (tags/RELEASE_600/final) (based on LLVM 6.0.0)
> Target: aarch64-unknown-openbsd6.3
> Thread model: posix
>
> ARM64 bit on a pine64-lts device running:
> OpenBSD 6.3-current (GENERIC.MP) #91: Mon Apr 30 01:29:06 MDT 2018
>
>
>
> sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o

What is the full exact command you are using to compile?
Do you have sqlite3.h in the same directory as sqlite3.c?

Thank you.

> sqlite3.c:2086:9: error: unknown type name 'sqlite_int64'
> typedef sqlite_int64 i64;  /* 8-byte signed integer */
> ^
> sqlite3.c:2087:9: error: unknown type name 'sqlite_uint64'
> typedef sqlite_uint64 u64; /* 8-byte unsigned integer */
> ^
> sqlite3.c:2524:3: error: unknown type name 'sqlite3_vfs'
>   sqlite3_vfs *pVfs,   /* VFS to use with this b-tree */
>   ^
> sqlite3.c:2526:3: error: unknown type name 'sqlite3'
>   sqlite3 *db, /* Associated database connection */
>   ^
> sqlite3.c:2756:3: error: unknown type name 'sqlite3_int64'
>   sqlite3_int64 nKey; /* Size of pKey for indexes.  PRIMARY KEY for tabs 
> */
>   ^
> sqlite3.c:2758:3: error: unknown type name 'sqlite3_value'
>   sqlite3_value *aMem;/* First of nMem value in the unpacked pKey */
>   ^
> sqlite3.c:2819:44: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE   void sqlite3BtreeEnterAll(sqlite3*);
>^
> sqlite3.c:2834:44: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE   void sqlite3BtreeLeaveAll(sqlite3*);
>^
> sqlite3.c:2913:5: error: unknown type name 'sqlite3_context'
> sqlite3_context *pCtx; /* Used when p4type is P4_FUNCCTX */
> ^
> sqlite3.c:3296:44: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
>^
> sqlite3.c:3313:16: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE sqlite3 *sqlite3VdbeDb(Vdbe*);
>^
> sqlite3.c:3318:16: error: must use 'struct' tag to refer to type 
> 'sqlite3_value'
> SQLITE_PRIVATE sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
>^
>struct
> sqlite3.c:3337:39: error: unknown type name 'sqlite3_context'
> SQLITE_PRIVATE int sqlite3NotPureFunc(sqlite3_context*);
>   ^
> sqlite3.c:3527:3: error: unknown type name 'sqlite3_vfs'
>   sqlite3_vfs*,
>   ^
> sqlite3.c:3535:53: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE int sqlite3PagerClose(Pager *pPager, sqlite3*);
> ^
> sqlite3.c:3547:55: warning: type specifier missing, defaults to 'int'
> [-Wimplicit-int]
> SQLITE_PRIVATE void sqlite3PagerSetMmapLimit(Pager *, sqlite3_int64);
>   ^
> sqlite3.c:3555:16: error: unknown type name 'sqlite3_backup'
> SQLITE_PRIVATE sqlite3_backup **sqlite3PagerBackupPtr(Pager*);
>^
> sqlite3.c:3587:60: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE   int sqlite3PagerCheckpoint(Pager *pPager, sqlite3*,
> int, int*, int*);
>^
> sqlite3.c:3591:58: error: unknown type name 'sqlite3'
> SQLITE_PRIVATE   int sqlite3PagerCloseWal(Pager *pPager, sqlite3*);
>  ^
> sqlite3.c:3616:16: error: unknown type name 'sqlite3_vfs'
> SQLITE_PRIVATE sqlite3_vfs *sqlite3PagerVfs(Pager*);
>^
> fatal error: too many errors emitted, stopping now [-ferror-limit=]
> 1 warning and 20 errors generated.
>
> --
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String Too Long

[Igor Korot]

Hi,

On Thu, Mar 1, 2018 at 1:20 PM, Matías Badin  wrote:
> Hi all;
> I am trying to insert a big string and i have the message: Request too long.
>
> I set my parameter as "text" but i still have this problem.
>
> Do you know if i can use another type?

BLOB?

Thank you.

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

Re: [sqlite] Please remove my id from mailing list/subscription.

[Igor Korot]

Hi,

On Thu, Feb 8, 2018 at 10:00 AM, Vasanth  wrote:
> Please remove my id from mailing list/subscription.

Why not do it yourself?
Did you try to go to the link shown at the end of this email or any
other for that matter?

Thank you.

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

Re: [sqlite] Vetting SQLite

[Igor Korot]

Hi,


On Mon, Feb 5, 2018 at 11:41 AM, Simon Slavin  wrote:
> On 5 Feb 2018, at 5:21pm, Drago, William @ CSG - NARDA-MITEQ 
>  wrote:
>
>> I've been using/loving SQLite for years, but the use of open source software 
>> is highly discouraged where I work, and now I have to prove to our IT dept. 
>> that SQLite is reliable and secure. The reliable part is easy because there 
>> is enough information on the SQLite website about testing, but what about 
>> security? How can I convince the auditors that SQLite is not stealing 
>> corporate secrets and spreading viruses?

Out of curiosity - does your company do the security scans quarterly
to make sure that the system (whatever is used) and the software you
guys provide are free for all security vulnerabilities?
As an example - here we do the scans quarterly, than check all
findings against RHSA (we use Red Hat Enterprise) and then fix them.
And then do quarterly security releases for the OS and software.

I'm sure Windows have the same Security Vulnerabilities DB where you
can check what should be fixed by the update, which will be done
automatically anyway.
And if you have a source code scanner(s) - you are in luck as you can
just check the code and fix it.

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

Re: [sqlite] sqlite3_column_decltype and max and min

[Igor Korot]

Hi,

On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly  wrote:
>
>
> On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G 
> :
>
>> You can
>> download and/or compile your own up-to-date SQLite that is twice as
>> fast and has all the latest features.
>
> It may be more convenient to use the [Homebrew package 
> manager](https://brew.sh/), which tracks SQLite pretty closely.  In order to 
> avoid interfering with the Apple-supplied installation, Homebrew deliberately 
> neglects to link the executable to _/usr/local/bin_, so some care is needed 
> when invoking SQLite in order to run the intended version.
>
> Here's what I have on my laptop, running Sierra.
>
> dhcp-162(niall)12: brew install sqlite
> Updating Homebrew...
> Warning: sqlite 3.21.0 is already installed
> dhcp-162(niall)13:
> dhcp-162(niall)13: which sqlite3
> /usr/bin/sqlite3
> dhcp-162(niall)14: /usr/bin/sqlite3
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> ^D
> dhcp-162(niall)15: /usr/local/Cellar/sqlite/3.21.0/bin/sqlite3
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> dhcp-162(niall)16:
>
> I hope this helps.

Why?
Just include sqlite3.c in your project and recompile.

Thank you.

>
> Best regards,
> Niall O'Reilly
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi, Keith,

On Mon, Dec 11, 2017 at 12:07 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> After stripping out comments and so forth of course ...

Could you please clarify that statement?
There is a patch from Cezary linked in this thread. I guess he is
successfully using it in his own application.

Thank you.

>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of petern
>>Sent: Sunday, 10 December, 2017 22:37
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Retrieving constraint name
>>
>>Igor/Cezary,
>>
>>It is remarkable how 'struct Parse' already contains the constraint
>>name as
>>Cezary pointed out.
>>->  Token constraintName;/* Name of the constraint currently being
>>parsed */
>>But is not included in the 'struct FKey' linked list node that is
>>reeled in
>>to produce columns in the PRAGMA report.
>>For the official release, presumably, test cases would have to be
>>added in
>>addition to simply hooking it up as suggested.
>>
>>In the meantime, parsing wouldn't be difficult even with primitive
>>built-in
>>SQL string functions.  Consider how the constraint name must occur
>>within
>>the comma delimited part of the well formed CREATE TABLE statement.
>>When
>>obtained from sqlite_master, the statement is guaranteed to be well
>>formed.  Simply examine each comma delimited candidate part.  If
>>present,
>>the first word between keyword CONSTRAINT and keyword REFERENCES is
>>the
>>constraint name.
>>
>>Peter
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot <ikoro...@gmail.com>
>>wrote:
>>
>>> Hi,
>>>
>>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
>><c...@poczta.onet.pl>
>>> wrote:
>>> > Hello,
>>> >
>>> > On 2017-12-11 01:04, Igor Korot wrote:
>>> >
>>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
>><c...@poczta.onet.pl>
>>> >> wrote:
>>> >
>>> >
>>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>>> >
>>> >
>>> >>>> The CREATE TABLE statement supports the following syntax:
>>> >>>>
>>> >>>> CREATE TABLE( , CONSTRAINT  FOREIGN
>>> >>>> KEY() REFERENCES (ref_column_list>);
>>> >
>>> >
>>> >>>> [...] If not - does this mean that the only way to get the
>>name is to
>>> >>>> parse the
>>> >>>> sql
>>> >>>> from sqlite_master? Or there is a better way?
>>> >
>>> >
>>> >>> The answer is ``not''. Constraint names are ignored and
>>disappearing
>>> >>> without
>>> >>> a trace except for ``CHECK'' constraint (the name is used to
>>build an
>>> >>> error
>>> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the
>>sole
>>> place
>>> >>> which contains an indirect info about ``FOREIGN KEY''
>>constraint's
>>> name.
>>> >
>>> >
>>> >> Thank you for confirming.
>>> >
>>> > You are welcome. BTW, SQLite parses SQL every time it creates a
>>table
>>> > (by a SQL command or after an opening of BTree file) -- I believe
>>there
>>> > is no better way. You do not need to parse SQL on your own (it is
>>hard,
>>> > if not impossible, to establish a link between a name and a
>>particular
>>> > constraint). All you need is to append ``char *'' field to
>>``struct
>>> > FKey'' and to inject a function
>>``build.c:sqlite3CreateForeignKey()'':
>>> > ``pParse->constraintName'' will contain the constraint's name
>>(note
>>> > that the name is not dequoted -- you will have to dequote it;
>>look at
>>> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of
>>a
>>> > constraint's name is done). This will allow you to build your own
>>map of
>>> > ``FOREIGN KEY'' names. For example, if you want to expand
>>``PRAGMA
>>> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
>>> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>>>
>>> Thank you, but I need to keep the official SQLite code.
>>>
>>>
>>> >
>>> >
>>> > -- best regards
>>> >
>>> > Cezary H. Noweta
>>> > ___
>>> > sqlite-users mailing list
>>> > sqlite-users@mailinglists.sqlite.org
>>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>users
>>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi, Cezary et al,

On Mon, Dec 11, 2017 at 5:48 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original
> output of ``PRAGMA foreign_key_list'' will fail.

It would be nice if the extra column for the fk name will appear last in the
output. I understand that that's how most DBMSs are work, but  that
way the backward
compatibility can be preserved.

Then maybe it could be considered to be included in the main tree by
simply applying the patch.

Thank you.

>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[Igor Korot]

Hi,

On Tue, Jan 2, 2018 at 3:34 PM,   wrote:
> Hi:
>
> Not only it can, but that is probably the use in the 99.00% of C++ 
> applications that uses SQLite.

That number should probably be 99.999(9)%... ;-)

Thank you.

>
> The only caveat is that you can get some warnings depending on the compiler 
> you use.
>
> Cheers
>
> --
> Adolfo J. Millan
>
>>
>>  Mensaje original 
>> De: eli 
>> Para:  sqlite-users@mailinglists.sqlite.org
>> Fecha:  Sat, 30 Dec 2017 13:35:35 +0200
>> Asunto:  [sqlite] C++ compiler
>>
>> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++ project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.
>
> Cheers,
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[Igor Korot]

Hi,

On Sat, Dec 30, 2017 at 5:35 AM, eli  wrote:
> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++ project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.

Which OS/compiler are you trying?
What is the exact error message you are receiving?
Did you modify t code in any way?

Thank you.

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

Re: [sqlite] Move to Github!!?

[Igor Korot]

Hi,

On Wed, Dec 27, 2017 at 12:00 PM, Warren Young  wrote:
> On Dec 27, 2017, at 7:49 AM, Nelson, Erik - 2 
>  wrote:
>>
>>> Also when you're forced to use a third party ticket system, fossil i 
>>> missing one of its big advantages.
>>
>> I'm no Fossil expert, but it does seem to have a ticketing system.  Is there 
>> something in particular functionality missing?
>
> I think he’s saying that being forced to use a third-party ticketing system 
> is a problem in itself, and it means you lose out on some of the features you 
> get by integrating the bug tracker tightly with the VCS as Fossil and GitHub 
> do: cross-linking checkin comments and bug IDs, etc.
>
> Some people like running a separate bug tracker to get more features, but of 
> course you can do that with Fossil as well.
>
> Third-party bug trackers often have built-in ways to integrate with Git — 
> network effects again — whereas to integrate Random Bug Tracker III with 
> Fossil, you’d probably have to do a lot of local scripting.
>
> One thing about evaluating Fossil’s ticket tracker: it’s quite configurable.  
> Don’t judge it solely on how it ships out of the box.  It may be easy to 
> adjust it to fix any mismatches between your expectations and the default 
> configuration.

I'm curious - is there a way for a Git{Hub}/Fossil to set up a
synchronized ticketing system?
If I have a trac and my project is on Git{Hub} - how hard wll it be to
synchronize between a trac tickets and Git{Hub} PR?

Moreover - it looks like this should be pushed further and further
away, because there is so much in-needed stuff to implement

Thank you.

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

Re: [sqlite] Retrieving constraint name

[Igor Korot]

On Mon, Dec 11, 2017 at 4:48 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original
> output of ``PRAGMA foreign_key_list'' will fail.

Hopefully this patch can be included in the release...

Thank you.

>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 11:36 PM, petern <peter.nichvolo...@gmail.com> wrote:
> Igor/Cezary,
>
> It is remarkable how 'struct Parse' already contains the constraint name as
> Cezary pointed out.
> ->  Token constraintName;/* Name of the constraint currently being parsed */
> But is not included in the 'struct FKey' linked list node that is reeled in
> to produce columns in the PRAGMA report.
> For the official release, presumably, test cases would have to be added in
> addition to simply hooking it up as suggested.

Now I'm curious if there is a plan to include the foreign key name in that
PRAGMA output. It looks like everything is in place for that.

Mr. Hipp?

>
> In the meantime, parsing wouldn't be difficult even with primitive built-in
> SQL string functions.  Consider how the constraint name must occur within
> the comma delimited part of the well formed CREATE TABLE statement.  When
> obtained from sqlite_master, the statement is guaranteed to be well
> formed.  Simply examine each comma delimited candidate part.  If present,
> the first word between keyword CONSTRAINT and keyword REFERENCES is the
> constraint name.

Yes, I may try to do that in the meantime.

Thank you.

>
> Peter
>
>
>
>
>
>
>
>
>
> On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta <c...@poczta.onet.pl>
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <c...@poczta.onet.pl>
>> >> wrote:
>> >
>> >
>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>> >
>> >
>> >>>> The CREATE TABLE statement supports the following syntax:
>> >>>>
>> >>>> CREATE TABLE( , CONSTRAINT  FOREIGN
>> >>>> KEY() REFERENCES (ref_column_list>);
>> >
>> >
>> >>>> [...] If not - does this mean that the only way to get the name is to
>> >>>> parse the
>> >>>> sql
>> >>>> from sqlite_master? Or there is a better way?
>> >
>> >
>> >>> The answer is ``not''. Constraint names are ignored and disappearing
>> >>> without
>> >>> a trace except for ``CHECK'' constraint (the name is used to build an
>> >>> error
>> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole
>> place
>> >>> which contains an indirect info about ``FOREIGN KEY'' constraint's
>> name.
>> >
>> >
>> >> Thank you for confirming.
>> >
>> > You are welcome. BTW, SQLite parses SQL every time it creates a table
>> > (by a SQL command or after an opening of BTree file) -- I believe there
>> > is no better way. You do not need to parse SQL on your own (it is hard,
>> > if not impossible, to establish a link between a name and a particular
>> > constraint). All you need is to append ``char *'' field to ``struct
>> > FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
>> > ``pParse->constraintName'' will contain the constraint's name (note
>> > that the name is not dequoted -- you will have to dequote it; look at
>> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
>> > constraint's name is done). This will allow you to build your own map of
>> > ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
>> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
>> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>>
>> Thank you, but I need to keep the official SQLite code.
>>
>>
>> >
>> >
>> > -- best regards
>> >
>> > Cezary H. Noweta
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote:
> Hello,
>
> On 2017-12-11 01:04, Igor Korot wrote:
>
>> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <c...@poczta.onet.pl>
>> wrote:
>
>
>>> On 2017-12-10 07:21, Igor Korot wrote:
>
>
>>>> The CREATE TABLE statement supports the following syntax:
>>>>
>>>> CREATE TABLE( , CONSTRAINT  FOREIGN
>>>> KEY() REFERENCES (ref_column_list>);
>
>
>>>> [...] If not - does this mean that the only way to get the name is to
>>>> parse the
>>>> sql
>>>> from sqlite_master? Or there is a better way?
>
>
>>> The answer is ``not''. Constraint names are ignored and disappearing
>>> without
>>> a trace except for ``CHECK'' constraint (the name is used to build an
>>> error
>>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place
>>> which contains an indirect info about ``FOREIGN KEY'' constraint's name.
>
>
>> Thank you for confirming.
>
> You are welcome. BTW, SQLite parses SQL every time it creates a table
> (by a SQL command or after an opening of BTree file) -- I believe there
> is no better way. You do not need to parse SQL on your own (it is hard,
> if not impossible, to establish a link between a name and a particular
> constraint). All you need is to append ``char *'' field to ``struct
> FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
> ``pParse->constraintName'' will contain the constraint's name (note
> that the name is not dequoted -- you will have to dequote it; look at
> ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
> constraint's name is done). This will allow you to build your own map of
> ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
> foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
> PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.

Thank you, but I need to keep the official SQLite code.


>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote:
> Hello,
>
> On 2017-12-10 07:21, Igor Korot wrote:
>>
>> The CREATE TABLE statement supports the following syntax:
>>
>> CREATE TABLE( , CONSTRAINT  FOREIGN
>> KEY() REFERENCES (ref_column_list>);
>>
>> However, the statement "PRAGME foreign_key_list;" does not list the
>> foreign key name ("fk_name" in the statement above).
>>
>> Does the info for the aforementioned PRAGMA stored somewhere?
>> If yes - does it include the key name and it just not printed with the
>> PRAGMA?
>> If not - does this mean that the only way to get the name is to parse the
>> sql
>> from sqlite_master? Or there is a better way?
>
>
> The answer is ``not''. Constraint names are ignored and disappearing without
> a trace except for ``CHECK'' constraint (the name is used to build an error
> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place
> which contains an indirect info about ``FOREIGN KEY'' constraint's name.

Thank you for confirming.

>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Retrieving constraint name

[Igor Korot]

Hi,
The CREATE TABLE statement supports the following syntax:

CREATE TABLE( , CONSTRAINT  FOREIGN
KEY() REFERENCES (ref_column_list>);

However, the statement "PRAGME foreign_key_list;" does not list the
foreign key name ("fk_name" in the statement above).

Does the info for the aforementioned PRAGMA stored somewhere?
If yes - does it include the key name and it just not printed with the PRAGMA?
If not - does this mean that the only way to get the name is to parse the sql
from sqlite_master? Or there is a better way?

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

Re: [sqlite] Why Unicode is difficult

[Igor Korot]

Stephen,

On Mon, Dec 4, 2017 at 1:01 PM, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> ... as in how 1 != "1"?

No.
1000 vs 1,000 vs 1.000 vs 1,000.00 vs whatever.

>
> On Mon, Dec 4, 2017 at 11:07 AM, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi,
>>
>> On Mon, Dec 4, 2017 at 7:42 AM, Jay Kreibich <j...@kreibi.ch> wrote:
>> >
>> >
>> > Next, we can talk about how dates and times are simple and
>> straight-forward.
>>
>> And then the number representation...
>>
>> Thank you.
>>
>> >
>> >  -j
>> >
>> >
>> >
>> >> On Dec 4, 2017, at 7:08 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>> >>
>> >> Every so often someone asks on this list for Unicode to be handled
>> properly.  I did it myself.  Then other people have to explain how hard
>> this is.  So here’s an article which, after introductory material,
>> discusses the hard questions in Unicode:
>> >>
>> >> <https://norasandler.com/2017/11/02/Around-the-with-Unicode.html>
>> >>
>> >> Are two strings the same?
>> >> How long is a string?
>> >> How do you sort things in alphabetical order?
>> >>
>> >> The first and third questions are requirements for implementing COLLATE
>> in SQLite.  And the fact that the second question is a difficult one
>> emphasises that one shouldn’t take Unicode as simple.
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why Unicode is difficult

[Igor Korot]

Hi,

On Mon, Dec 4, 2017 at 7:42 AM, Jay Kreibich  wrote:
>
>
> Next, we can talk about how dates and times are simple and straight-forward.

And then the number representation...

Thank you.

>
>  -j
>
>
>
>> On Dec 4, 2017, at 7:08 AM, Simon Slavin  wrote:
>>
>> Every so often someone asks on this list for Unicode to be handled properly. 
>>  I did it myself.  Then other people have to explain how hard this is.  So 
>> here’s an article which, after introductory material, discusses the hard 
>> questions in Unicode:
>>
>> 
>>
>> Are two strings the same?
>> How long is a string?
>> How do you sort things in alphabetical order?
>>
>> The first and third questions are requirements for implementing COLLATE in 
>> SQLite.  And the fact that the second question is a difficult one emphasises 
>> that one shouldn’t take Unicode as simple.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Hi,

On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev <devshan...@gmail.com> wrote:
> On 22 November 2017 at 17:08, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Hi, Shane,
>>
>>
>> What I don't understand is why do you need to do that?
>>
>
> Imagine I have a GUI element with a drop down list of fruit. The source of
> the list is my fruit table and it may have many entries. It might more
> convenient to list the popular fruit near the top. In that case the
> fruit.sort_order could represent relative popularity of the fruit entries.
>
> Database idea is to store the data and then retrieve them in any way
>> you want at any given time.
>>
>> So all you need to know that there is a record inserted into the table
>> "fruit" on the schema
>> "garden".
>> Then when the time comes by you can retrieve the records with the
>> "ORDER BY" clause.
>> Whether you will sort the data by alphabet - fruit_name" or by number
>> increment - "Sort_order"
>> doesn't really matter.
>> Inserting the record is an implementation detail which shouldn't
>> bother you at all.
>>
>
> Actually, it interests me. If I knew insertions and updates in the fruit
> table were mostly for unpopular fruits, then  Peter Nichvolodov's trigger
> solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
> might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL
> solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html)
> might more be efficient. However, querying may be slower.

Then have a popularity column in the table and update it with every single hit
using trigger.
Then do the query to fill out you list with "ORDER BY popularity".

Once again - how the records are inserted is implementation detail which
shouldn't be of the concern in any situations.

Thank you.

P.S.: Basically you are trying to create a problem where there is no problem
and a nice and simple solution.

>
>
>> Unless you can sow us that the time required to retrieve the
>> sorting data will SIGNIICANTLY
>> differ in both cases.
>>
>> I am ready to hear arguments against this approach. ;-)
>>
>> Thank you.
>>
>> >
>> >
>> > On 22 November 2017 at 00:11, Igor Korot <ikoro...@gmail.com> wrote:
>> >
>> >> Simon,
>> >>
>> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org>
>> >> wrote:
>> >> >
>> >> >
>> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote:
>> >> >
>> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> wrote:
>> >> >>>
>> >> >>> That assumes you are not starting from an integer part (like 4000)
>> and
>> >> hitting the exact same relative insert spot every time, which /can/
>> happen,
>> >> but is hugely unlikely.
>> >> >>
>> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> >> say you sort rows by date. You’ve already got some entries from 2015 in
>> >> your database, and some from 2017. Someone now inserts 60 entries from
>> >> 2016, and to be ‘helpful’, they insert them in chronological order.
>> Wham,
>> >> this immediately hits that case.
>> >> >
>> >> > Yes, if you use this method, you do need to renumber them every so
>> >> often.  You assess this when you’re working out (before + after) / 2,
>> and
>> >> you do it using something like the double-UPDATE command someone came up
>> >> with earlier.
>> >> >
>> >> > But that just brings us back to the question of why OP wants to store
>> ID
>> >> numbers which might change.
>> >>
>> >> Homework exercise?
>> >> Stupid requirements?
>> >>
>> >> Thank you.
>> >>
>> >> >
>> >> > Simon.
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@mailinglists.sqlite.org
>> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Igor Korot]

Hi,

On Wed, Nov 22, 2017 at 10:53 AM, jungle Boogie <jungleboog...@gmail.com> wrote:
> On 22 November 2017 at 07:56, Igor Korot <ikoro...@gmail.com> wrote:
>> Hi,
>> Postgres very recently switched to PGLister for their ML
>>
>> This software switch tries to do exactly that - it tries to stay
>> complaint with all this DMARC stuff.
>>
>> Here is the announcement that was posted on their wiki page:
>> https://wiki.postgresql.org/wiki/PGLister_Announce.
>>
>
> Not a bad consideration. Where's the documentation on the software,
> though? Is it open source/free?

I don't know. I just subscribed to the PG ML general and got that notification.
If there is an interest I can try to ask or probably Mr Hipp can do that imself
and give more info.

One thing that I don't like is that the nice footer I see in the SQLite MLas:

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

will be gone. But this is the only downside as it stands right now
from my perspective as a
user.

Thank you.

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Hi, Shane,

On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev <devshan...@gmail.com> wrote:
> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not. Obviously,
> this would be bad idea if the ID column was referenced by other column /
> table. In that case, I would have created a different table such as
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column. I
> still have to consider how to manage changes to values in the sort column.
> Apparently there is no single SQL statement which can insert a record in to
> any arbitrary sort position. Even if I use the stepped approach (fruit.sort
> = 100, 200, 300 ...) or define sort as real unique, I will still need to
> determine if it is necessary to reset the gaps between sort column values.
> Peter Nichvolodov's trigger solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
> is elegant, but might be slow if the table had many entries.

What I don't understand is why do you need to do that?
Database idea is to store the data and then retrieve them in any way
you want at any given time.

So all you need to know that there is a record inserted into the table
"fruit" on the schema
"garden".
Then when the time comes by you can retrieve the records with the
"ORDER BY" clause.
Whether you will sort the data by alphabet - fruit_name" or by number
increment - "Sort_order"
doesn't really matter.
Inserting the record is an implementation detail which shouldn't
bother you at all.

Unless you can sow us that the time required to retrieve the
sorting data will SIGNIICANTLY
differ in both cases.

I am ready to hear arguments against this approach. ;-)

Thank you.

>
>
> On 22 November 2017 at 00:11, Igor Korot <ikoro...@gmail.com> wrote:
>
>> Simon,
>>
>> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org>
>> wrote:
>> >
>> >
>> > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote:
>> >
>> >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> wrote:
>> >>>
>> >>> That assumes you are not starting from an integer part (like 4000) and
>> hitting the exact same relative insert spot every time, which /can/ happen,
>> but is hugely unlikely.
>> >>
>> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> say you sort rows by date. You’ve already got some entries from 2015 in
>> your database, and some from 2017. Someone now inserts 60 entries from
>> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
>> this immediately hits that case.
>> >
>> > Yes, if you use this method, you do need to renumber them every so
>> often.  You assess this when you’re working out (before + after) / 2, and
>> you do it using something like the double-UPDATE command someone came up
>> with earlier.
>> >
>> > But that just brings us back to the question of why OP wants to store ID
>> numbers which might change.
>>
>> Homework exercise?
>> Stupid requirements?
>>
>> Thank you.
>>
>> >
>> > Simon.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Igor Korot]

Hi,
Postgres very recently switched to PGLister for their ML

This software switch tries to do exactly that - it tries to stay
complaint with all this DMARC stuff.

Here is the announcement that was posted on their wiki page:
https://wiki.postgresql.org/wiki/PGLister_Announce.

Since SQLite follows PostgreSQL maybe we should have something similar?


Just my $0.02.

On Wed, Nov 22, 2017 at 9:44 AM, Drago, William @ CSG - NARDA-MITEQ
 wrote:
>> There is nothing wrong with email - but there is an awful lot wrong with 
>> gnail
>> and Google's ideas on how email is done.  (Not to mention Yahoo, but it
>> seems that MS have the sense to leave the underpinnings of hotmail as they
>> were.)
>>
>> To put it simply - friends don't let friends use gmail.
>>
>> Cheers,
>> GaryB-)
>
> Short of running my own server, what do you recommend? After losing my 
> lifelong email provider (Suffolk.lib.ny.us) and trying a few others, I 
> eventually settled for gmail, which I use with Thunderbird. I don't have any 
> significant issues with that setup.
>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
>
> CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use 
> of the intended recipient and may contain material that is proprietary, 
> confidential, privileged or otherwise legally protected or restricted under 
> applicable government laws. Any review, disclosure, distributing or other use 
> without expressed permission of the sender is strictly prohibited. If you are 
> not the intended recipient, please contact the sender and delete all copies 
> without reading, printing, or saving.
>
> Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of 
> all @L-3Com.com email addresses. To ensure delivery of your messages to this 
> recipient, please update your records to use william.dr...@l3t.com.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Simon,

On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin  wrote:
>
>
> On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>
>>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>>>
>>> That assumes you are not starting from an integer part (like 4000) and 
>>> hitting the exact same relative insert spot every time, which /can/ happen, 
>>> but is hugely unlikely.
>>
>> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
>> sort rows by date. You’ve already got some entries from 2015 in your 
>> database, and some from 2017. Someone now inserts 60 entries from 2016, and 
>> to be ‘helpful’, they insert them in chronological order. Wham, this 
>> immediately hits that case.
>
> Yes, if you use this method, you do need to renumber them every so often.  
> You assess this when you’re working out (before + after) / 2, and you do it 
> using something like the double-UPDATE command someone came up with earlier.
>
> But that just brings us back to the question of why OP wants to store ID 
> numbers which might change.

Homework exercise?
Stupid requirements?

Thank you.

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Hi,

On Mon, Nov 20, 2017 at 10:12 AM, R Smith  wrote:
> This question pops up from time to time.
>
> I will show a correct query script to achieve this below, but I want to
> emphasize what others have said: Data in an RDBMS has no intrinsic order,
> it's all SETs, and if you artificially bestow order to the data itself (as
> opposed to the eventual output) then you are doing something that's very bad
> in database design.
>
> To be specific, if the fruit in your DB needs ORDER as a property, best is
> to add a column called  SortOrder or FruitOrder or the like. In this column
> you can then assign the values automatically in steps of 10 or 100, so you
> end up with a table like:
> id  |  fruit  | SortOrder
> 1  |  Apple  |  100
> 2  |  Pear  |  200
> 3  |  Kiwi  |  300  etc...
>
>
> Then inserting:
> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>
> is simply trivial. (The 150 can be computed from splitting the difference
> between the precedent and decedent). Non-Integer is best.
>
> Eventually though, you might need to do maintenance and reset the gaps or
> such.
>
> Anyway, enough preaching - this query script will fix your Situation in
> SQLite very fast:
>
> UPDATE fruit SET id = -id-1 WHERE id >= 2;
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (2,'Banana');
>
>
> Another way:
>
> UPDATE fruit SET id = -(id * 100);
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (150,'Banana');
>
>
> The reason why this is bad? Mostly a primary Key serves as a lookup for
> other tables linking to a very specific record. Imagine your query that
> added fruit to recipes where needed has the fruit's primary keys shuffled,
> the next day will see some really weird recipes when Banana ends up where
> Pear was intended.  Next you'll want to insert Watermelon...  :)

For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE.

But yes - this problem is very weird. Unless its some kind of
educational/home work

Thank you.

>
> Cheers,
> Ryan
>
>
> On 2017/11/19 10:37 PM, Shane Dev wrote:
>>
>> Let's say I have a table of fruit -
>>
>> sqlite> .sch fruit
>> CREATE TABLE fruit(id integer primary key, name text);
>>
>> with some entries -
>>
>> sqlite> select * from fruit;
>> id|name
>> 1|apple
>> 2|pear
>> 3|kiwi
>>
>> Is there an easy way to insert 'banana' between apple and pear while still
>> maintaining a consistent order of the ID field?
>>
>> desired result -
>>
>> sqlite> select * from fruit;
>> 1|apple
>> 2|banana
>> 3|pear
>> 4|kiwi
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to develop a GUI front-end

[Igor Korot]

Take a look at wx{Phoenix, Python}.
It is much simpler, written in python, supports all its versions, and
there demos and samples on its website - www.wxpython.org

Thank you.

On Wed, Nov 15, 2017 at 2:32 PM, Peter Da Silva
 wrote:
> Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, 
> assume UNIX file paths and stuff) any Tk app should work just fine on 
> Windows. You may need to tweak the fonts, eg:
>
> if { $tcl_platform(platform) eq "windows" } { set font {Arial} } else { 
> set font {Helvetica} }
>
> Have a look at the examples at https://wiki.tcl.tk/4149
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to develop a GUI front-end

[Igor Korot]

Hi,
Did you look at wxPython (or wxPhoenix)?
It was recenty saw a new release..
Thank you.

On Tue, Nov 14, 2017 at 3:39 PM, Peter Da Silva
 wrote:
> On 11/14/17, 3:33 PM, "sqlite-users on behalf of Balaji Ramanathan" 
>  balaji.ramanat...@gmail.com> wrote:
>> I am leaning towards tcl/tk given the endorsement from both Peter and DRH. 
>> So, I downloaded tcl/tk version 8.6.7 from magicsplat 
>> (http://www.magicsplat.com/tcl-installer/index.html), and then ran 
>> tclsh.exe.  In the resulting command window, I typed "sqlite3 db1 > my sqlite database" as instructed on this page on the sqlite website: 
>> https://sqlite.org/tclsqlite.html .  And all I got was "invalid command name 
>> sqlite3".  So, I am stuck.  What did I do wrong?
>
> % package require sqlite3
> 3.20.1
> % sqlite3 db1 :memory:
> %
>
>> BTW, the www.tcl-lang.org website has been down for at least the past 3 days 
>> it looks like.  Not sure how to interpret the reliability of a technology 
>> whose home website is down for days at a time.
>
> Try https://www.tcl.tk/ ?
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA foreign_key_list

[Igor Korot]

 Hi,
I am curious - how hard will it be to add the constraint name to the
result of this view?

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

Re: [sqlite] sqlite_master structure

[Igor Korot]

Simon,


On Wed, Nov 1, 2017 at 10:44 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 2 Nov 2017, at 3:37am, Igor Korot <ikoro...@gmail.com> wrote:
>
>> I see that sqlite_master have 2 fields: name and tbl_name. It looks
>> like they have
>> the same value in my case.
>>
>> Is there a scenario when those 2 are different?
>
> sqlite_master includes rows for things that aren’t tables.  For example, 
> indexes.

So if I want table, it is cleaner to use tbl_name, correct?
Provided I have "WHERE type = 'table'" in the query...

Thank you.

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

[sqlite] sqlite_master structure

[Igor Korot]

Hi, ALL,
I see that sqlite_master have 2 fields: name and tbl_name. It looks
like they have
the same value in my case.

Is there a scenario when those 2 are different?
And what should I check for the "table name"?

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

Re: [sqlite] very sqlite3 noobie error

[Igor Korot]

Hi, John,

On Sun, Oct 22, 2017 at 9:59 PM, John R. Sowden
 wrote:
> Since I am trying to learn sqlite3 (unlearning foxpro) I find that python is
> the simpleist language, wfich allows me to focus on sqlite, I amtrying the
> =guide just sent to the list.
>
> I am getting a syntax error on line 5.  below is the program copied by hand
> from the guide:

What is the exact error message you received?
Please copy and paste it here in reply?

Also, what python version do you have?

Thank you.

>
> --
> #/!python
> # sqlite3_test, a test of python and sqlite
>
> import sqlite3
>
> connection = sqlite3(':memory:')
>
> # create a table
> connection.execute('create table events(ts, msg)')
>
> # insert values
> connection.execute('insert into events values(?,?),
>[(1, 'foo'), (2,'bar'), (3,'baz') ] )
>
> # print inserted rows
> for row in connection.execute('select * from events'):
> print(row)
> ---
>
> can anyone tell me where i am missing something?
>
> John
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[Igor Korot]

Hi,



On Oct 21, 2017 5:18 AM, "csanyipal"  wrote:

I try to follow advices and modify my database so it is now like this:
*CREATE TABLE "student" (
  "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,
  "studentname" TEXT NOT NULL,
  "teachinglang" VARCHAR(2) NOT NULL,
  "grade" TINYINT,
  "classname" VARCHAR(1) NOT NULL,
  "formmaster" TEXT NOT NULL
);

CREATE TABLE "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" TINYINT,
  "quarter" TINYINT,
  "workpiecenamehu" TEXT NOT NULL,
  "workpiecenamesr" TEXT NOT NULL
);

CREATE TABLE "uniqueworkpiece" (
  "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" TINYINT,
  "cause" TEXT NOT NULL,
  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
);


You can as well drop the PK on the table above and live with just 2 FK
constraints. If you really want a PK on this table you can create a dummy
field and make it a primary key.

Other than that you should be good.

Thank you.


CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
("workpiece_list")*

What do you think, did I achieve what I want?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

Hi Keith,

On Oct 6, 2017 12:59 PM, "Keith Medcalf"  wrote:

The return code will tell you the cause of the failure  Love them.  Check
them.  Every time.


I'm checking them.
But if there is a ROLLBACK failure I will tell the user and when the app
will close sqlite3_close() will be called.
I guess I will just throw an error anyway and exit.

Thank you.

 Original message From: Simon Slavin <
slav...@bigfraud.org> Date:2017-10-05  09:50  (GMT-07:00)
To: SQLite mailing list 
Subject: Re: [sqlite] How to handle such situation 

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

Re: [sqlite] How to handle such situation

[Igor Korot]

Hi Simon et al,
So I shouldn't card if _close() fail either way?
Just give an error and quit?

Thank you.


On Oct 5, 2017 2:01 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:



On 5 Oct 2017, at 6:42pm, Igor Korot <ikoro...@gmail.com> wrote:

> My question here is about ROLLBACK failure vs sqlite3_close() failure.
> Like I said when the app closes I'm calling the latter and if it failed I
> again display an error and just quit.
>
> But if the former fails calling _close() does not make much sense, right?

If you get an unexpected failure on _execute() or on _step() then I don’t
think you need to worry about calling _close().  Just quit.

The question of what _close() would actually do after an error probably
depends on what the error was.  But since you can’t do anything if _close()
fails I refuse to worry about it.

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

Re: [sqlite] How to handle such situation

[Igor Korot]

Simon
My question here is about ROLLBACK failure vs sqlite3_close() failure.
Like I said when the app closes I'm calling the latter and if it failed I
again display an error and just quit.

But if the former fails calling _close() does not make much sense, right?

Thank you.

On Oct 5, 2017 11:50 AM, "Simon Slavin" <slav...@bigfraud.org> wrote:

>
>
> On 5 Oct 2017, at 4:35pm, Igor Korot <ikoro...@gmail.com> wrote:
>
> > You mean like have some kind of flag and display an error on disconnect
> > only if not set?
>
> If ROLLBACK fails, it’s probably because of a hardware failure or your
> database being corrupt.  You can’t do anything else useful with the
> database connection since it has produced an unrecoverable error.
>
> Display an error message to your user and quit immediately.  That way you
> don’t risk corrupting your dataset by doing more operations which assume
> that that ROLLBACK succeeded.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

Jens,
You mean like have some kind of flag and display an error on disconnect
only if not set?

Thank you.


On Oct 5, 2017 11:32 AM, "Jens Alfke" <j...@mooseyard.com> wrote:

>
>
> > On Oct 4, 2017, at 7:16 PM, Igor Korot <ikoro...@gmail.com> wrote:
> >
> > And if the ROLLBACK fails?
>
> Then AFAIK, something’s really wrong; treat it as a fatal error, probably.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

Hi,

On Wed, Oct 4, 2017 at 6:19 PM, J. King <jk...@jkingweb.ca> wrote:
> See also:
> <http://sqlite.org/lang_conflict.html>

This is good to know but I don't think it is applicable in this case
as I explicitly said
that this is inside transaction which fails.

Thank you.

>
> On October 4, 2017 6:15:55 PM EDT, Jens Alfke <j...@mooseyard.com> wrote:
>>
>>
>>> On Oct 4, 2017, at 2:20 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>>
>>> If I start transaction, all queries were successful, but issuing
>>"COMMIT" fails.
>>> On such failure I am going to present an error, but then what happens
>>> if I go and try to close the connection?
>>
>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>transaction, then continue with failure handling. Otherwise yes, the
>>transaction remains open and will cause trouble when you try to close
>>the connection.
>>
>>—Jens
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to handle such situation

[Igor Korot]

Hi, Jens,

On Wed, Oct 4, 2017 at 6:15 PM, Jens Alfke <j...@mooseyard.com> wrote:
>
>
>> On Oct 4, 2017, at 2:20 PM, Igor Korot <ikoro...@gmail.com> wrote:
>>
>> If I start transaction, all queries were successful, but issuing "COMMIT" 
>> fails.
>> On such failure I am going to present an error, but then what happens
>> if I go and try to close the connection?
>
> If COMMIT fails, you should execute a ROLLBACK to abort the transaction, then 
> continue with failure handling. Otherwise yes, the transaction remains open 
> and will cause trouble when you try to close the connection.

And if the ROLLBACK fails?
Because it is possible for ROLLBACK to fail. Not just because COMMIT fails.

Thank you.

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

[sqlite] How to handle such situation

[Igor Korot]

Hi, list,
Have a following question.

I am writing an application in which I will be using transactions. At
the end of the application I will close the connection.
The application will verify every single call to SQLite for an error.

If I start transaction, all queries were successful, but issuing "COMMIT" fails.
On such failure I am going to present an error, but then what happens
if I go and try to close the connection?

I think that the call to sqlite3_close() will fail since there is an
open transaction, right? But then there is no error at all.
Unless I misunderstands the nature of the connection close failure.

So, how do I differentiate such situation from a legitimate
sqlite3_close() failure?

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

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

[Igor Korot]

Simon,

On Fri, Sep 29, 2017 at 4:38 PM, Simon Slavin  wrote:
>
>
> On 29 Sep 2017, at 9:06pm, Denis V. Razumovsky  wrote:
>
>> What can be wrong for _any_ of the compilers if you will define
>> SQLITE_4_BYTE_ALIGNED_MALLOC as 0 in sqlite3.h? It's so simple. I think
>> it should only get better for all platforms and compilers )
>
> If SQLITE_4_BYTE_ALIGNED_MALLOC always means 0 under all circumstances, why 
> did someone bother to make it a named option ?  Why doesn’t the code just 
> assume zero ?
>
> Under Windows malloc() returns a 4-byte aligned pointer, i.e. that the option 
> has to be set to 1.  The same occurs in many embedded processors, which have 
> far less memory than you’re used to on a multi-purpose desktop computer.  
> It’s not simple.  And it doesn’t work on all platforms.
>
> You are still proceeding as if your own development platform and target CPU 
> is the only one SQLite has to work for.

But then why not give it some default value ("0" maybe") and default
it to "1" only if needed during configure?

Thank you.

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Igor Korot]

Not if you use connection-per-thread model.

On Thu, Sep 28, 2017 at 3:07 PM, Simon Slavin  wrote:
>
>
> On 28 Sep 2017, at 8:01pm, pisymbol .  wrote:
>
>> So you can still have issues with thread 1 issuing a "BEGIN" and then
>> thread 2 issuing another "BEGIN" before thread 1 finalizes the transaction
>> causing failure. Correct?
>
> Don’t know.  Hope someone else does.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Igor Korot]

Hi, Simon,

On Thu, Sep 28, 2017 at 11:55 AM, Simon Slavin  wrote:
>
>
> On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:
>
>> Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
>> mutex, then both could start a transaction simultaneously, one will win the
>> other will wait, [snip]
>
> By "serialised" the documentation means that no two SQL commands will be 
> allowed to take place at the same time.  But SQL can’t separate your two 
> different threads into two different sources of commands.  As far as its 
> concerned, anything which uses your single connection is part fo the same 
> command-stream.  So if one part of your program executes BEGIN then 
> everything from any source from then onwards is part of the same transaction 
> until some part of your program executes "COMMIT".
>
> If you want your program to execute two different transactions at the same 
> time (and handle the bookkeeping necessary to make one wait for the other to 
> complete) you must use two different connections.  And if you have three 
> independent parts of your program which may all depend on transaction 
> isolation, you need three connections
>
>> I can't find where a single thread
>> of execution could issue "BEGIN" twice in my code though I did find that I
>> was calling sqlite3_lastrow_insert() twice in a row by accident but I don't
>> know how that could cause the above.)
>
> Do you use _prepare(), _step(), _finalize() for a multi-row SELECT anywhere ? 
>  What probably happened is that one thread executed _step() for a multi-row 
> SELECT.  That means it is in the middle of a SELECT command, so it wouldn’t 
> have released its lock on the database.Only when a _step() command has 
> reached the end of the rows and returned SQLITE_DONE (or an error) is the SQL 
> command over, at which point SQLite can end the transaction.

But the lock will be released on _finalize(), right?

Thank you.

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

Re: [sqlite] CREATE FOREIGN KEY support

[Igor Korot]

Hi, Clemens,

On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Igor Korot wrote:
>> 3. DROP TABLE ;
>>
>> On step 3 all ttriggers and indexes will be dropped as well, right?
>
> Yes.
>
>> 4. CREATE TABLE (, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * 
>> FROM temp;
>>
>> what would be the best way to read [triggers and indexes] and apply to the 
>> step 4?
>
> Run .schema in the command-line shell, and search where the table name is 
> mentioned.
>
> Alternatively, if you're feeling adventurous, you can use PRAGMA 
> writable_schema
> to modify the table definition in place: 
> https://stackoverflow.com/a/42970982/11654

It would be nice if that backdoor was implemented as "ALTER TABLE..." one.

But that aside I am trying to run this from the C interface.
I guess I can just try to implement it in my program. Just curious -
why I should feel
adventurous?

Thank you.

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