Re: [sqlite] Simple SQL question?

[Bart Smissaert]

Thanks, they both work indeed.
Nice work!

This bit looks strange to me:

SELECT GetAgeAtDate(dob, issue_date)
   FROM table_p

as issue_date is not in table_p.
Never realised that this could work and learned something there.

RBS






On Wed, Nov 15, 2017 at 10:12 PM, Keith Medcalf  wrote:

>
> Then try making the where clause explicitly qualified with the table names.
>
> UPDATE table_a
>SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
>FROM table_p
>   WHERE table_p.id = table_a.id);
>
> Actually, that would be correct.  You can use the query of the form
>
> UPDATE table_a
>SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
>FROM table_p
>   WHERE id = table_a.id);
>
> as unqualified duplicate named columns in the correlated subquery are
> preferentially assumed to refer to tables mentioned in the from clause of
> the correlated subquery.
>
> Duplicated column names that you wish to refer to the outer correlated
> table must be qualified.  Non-duplicate column names will be found where
> they exist, either in the inner or outer correlate.
>
> ---
> 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 Bart Smissaert
> >Sent: Wednesday, 15 November, 2017 15:05
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Simple SQL question?
> >
> >They end up in the wrong row.
> >
> >RBS
> >
> >On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf 
> >wrote:
> >
> >>
> >> That is not possible since there is only one column called
> >issue_date in
> >> all the tables mentioned in the query ...
> >>
> >>
> >> ---
> >> 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 Bart Smissaert
> >> >Sent: Wednesday, 15 November, 2017 14:55
> >> >To: SQLite mailing list
> >> >Subject: Re: [sqlite] Simple SQL question?
> >> >
> >> >That is getting close, but the calculated values end up with the
> >> >right ID,
> >> >but the wrong ISSUE_DATE.
> >> >Will if an order by can sort this out.
> >> >
> >> >RBS
> >> >
> >> >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond
> >> >
> >> >wrote:
> >> >
> >> >> Try...
> >> >>
> >> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
> >> >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM
> >> >>  TABLE_P AS P WHERE P.ID = ID);
> >> >>
> >> >>
> >> >> -Original Message-
> >> >> From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org]
> >> >> On Behalf Of Bart Smissaert
> >> >> Sent: Wednesday, November 15, 2017 4:17 PM
> >> >> To: General Discussion of SQLite Database
> >> >> Subject: [sqlite] Simple SQL question?
> >> >>
> >> >> Have 2 tables, TABLE_A and TABLE_P like this:
> >> >>
> >> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE
> >> >INTEGER)
> >> >>
> >> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
> >> >>
> >> >> ID is he common field.
> >> >>
> >> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it
> >will
> >> >hold the
> >> >> age of the person identified by ID, at the date of ISSUE_DATE.
> >> >> I do this with a UDF, taking 2 arguments, in this case DOB (date
> >of
> >> >birth)
> >> >> and ISSUE_DATE.
> >> >> The UDF is not relevant in this example and it could as well be
> >> >ISSUE_DATE
> >> >> - DOB.
> >> >>
> >> >> I tried this:
> >> >>
> >> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
> >> >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
> >> >> INNER JOIN TABLE_P P ON(A.ID = P.ID)
> >> >>
> >> >> But that will make the column AGE_AT_ISSUE have the same value
> >for
> >> >all
> >> >> rows, which is he first row of the select.
> >> >>
> >> >> The select by itself will give the right values.
> >> >>
> >> >> Any suggestions how this can be done with just one statement?
> >> >>
> >> >>
> >> >> RBS
> >> >> ___
> >> >> 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
> >> 

Re: [sqlite] Simple SQL question?

[Keith Medcalf]

Then try making the where clause explicitly qualified with the table names.

UPDATE table_a
   SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
   FROM table_p
  WHERE table_p.id = table_a.id);

Actually, that would be correct.  You can use the query of the form

UPDATE table_a
   SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
   FROM table_p
  WHERE id = table_a.id);

as unqualified duplicate named columns in the correlated subquery are 
preferentially assumed to refer to tables mentioned in the from clause of the 
correlated subquery.  

Duplicated column names that you wish to refer to the outer correlated table 
must be qualified.  Non-duplicate column names will be found where they exist, 
either in the inner or outer correlate.

---
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 Bart Smissaert
>Sent: Wednesday, 15 November, 2017 15:05
>To: SQLite mailing list
>Subject: Re: [sqlite] Simple SQL question?
>
>They end up in the wrong row.
>
>RBS
>
>On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf 
>wrote:
>
>>
>> That is not possible since there is only one column called
>issue_date in
>> all the tables mentioned in the query ...
>>
>>
>> ---
>> 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 Bart Smissaert
>> >Sent: Wednesday, 15 November, 2017 14:55
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] Simple SQL question?
>> >
>> >That is getting close, but the calculated values end up with the
>> >right ID,
>> >but the wrong ISSUE_DATE.
>> >Will if an order by can sort this out.
>> >
>> >RBS
>> >
>> >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond
>> >
>> >wrote:
>> >
>> >> Try...
>> >>
>> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
>> >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM
>> >>  TABLE_P AS P WHERE P.ID = ID);
>> >>
>> >>
>> >> -Original Message-
>> >> From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org]
>> >> On Behalf Of Bart Smissaert
>> >> Sent: Wednesday, November 15, 2017 4:17 PM
>> >> To: General Discussion of SQLite Database
>> >> Subject: [sqlite] Simple SQL question?
>> >>
>> >> Have 2 tables, TABLE_A and TABLE_P like this:
>> >>
>> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE
>> >INTEGER)
>> >>
>> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>> >>
>> >> ID is he common field.
>> >>
>> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it
>will
>> >hold the
>> >> age of the person identified by ID, at the date of ISSUE_DATE.
>> >> I do this with a UDF, taking 2 arguments, in this case DOB (date
>of
>> >birth)
>> >> and ISSUE_DATE.
>> >> The UDF is not relevant in this example and it could as well be
>> >ISSUE_DATE
>> >> - DOB.
>> >>
>> >> I tried this:
>> >>
>> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
>> >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
>> >> INNER JOIN TABLE_P P ON(A.ID = P.ID)
>> >>
>> >> But that will make the column AGE_AT_ISSUE have the same value
>for
>> >all
>> >> rows, which is he first row of the select.
>> >>
>> >> The select by itself will give the right values.
>> >>
>> >> Any suggestions how this can be done with just one statement?
>> >>
>> >>
>> >> RBS
>> >> ___
>> >> 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] Running sums and averages

[Dennis Clarke]

  Memory is cheap and most servers have plenty.



  Processors are fast and most servers have multiple with many cores.



  Select the entire table of columns you need into memory.
  Write a little code.



No it won't scale very well into millions of rows but I could easily run
a test and I will bet many mnay many dollars that processing the sums in
memory is orders of magnitude faster than SQL.


You shouldn't even need to read the entire table (or view) into
memory: just read row-by-row, and for each field, keep a running
total and the count of non-NULL values. From these you can calculate
your total and both types of average.

Graham


yes .. that is even better !

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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

They end up in the wrong row.

RBS

On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf  wrote:

>
> That is not possible since there is only one column called issue_date in
> all the tables mentioned in the query ...
>
>
> ---
> 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 Bart Smissaert
> >Sent: Wednesday, 15 November, 2017 14:55
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Simple SQL question?
> >
> >That is getting close, but the calculated values end up with the
> >right ID,
> >but the wrong ISSUE_DATE.
> >Will if an order by can sort this out.
> >
> >RBS
> >
> >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond
> >
> >wrote:
> >
> >> Try...
> >>
> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
> >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM
> >>  TABLE_P AS P WHERE P.ID = ID);
> >>
> >>
> >> -Original Message-
> >> From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org]
> >> On Behalf Of Bart Smissaert
> >> Sent: Wednesday, November 15, 2017 4:17 PM
> >> To: General Discussion of SQLite Database
> >> Subject: [sqlite] Simple SQL question?
> >>
> >> Have 2 tables, TABLE_A and TABLE_P like this:
> >>
> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE
> >INTEGER)
> >>
> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
> >>
> >> ID is he common field.
> >>
> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will
> >hold the
> >> age of the person identified by ID, at the date of ISSUE_DATE.
> >> I do this with a UDF, taking 2 arguments, in this case DOB (date of
> >birth)
> >> and ISSUE_DATE.
> >> The UDF is not relevant in this example and it could as well be
> >ISSUE_DATE
> >> - DOB.
> >>
> >> I tried this:
> >>
> >> UPDATE TABLE_A SET AGE_AT_ISSUE =
> >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
> >> INNER JOIN TABLE_P P ON(A.ID = P.ID)
> >>
> >> But that will make the column AGE_AT_ISSUE have the same value for
> >all
> >> rows, which is he first row of the select.
> >>
> >> The select by itself will give the right values.
> >>
> >> Any suggestions how this can be done with just one statement?
> >>
> >>
> >> RBS
> >> ___
> >> 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] Simple SQL question?

[petern]

It is often helpful to study the syntax diagrams to see what is possible
and intended by the language:

https://sqlite.org/lang_update.html

Take a look at the WHERE clause.  The WHERE clause determines which rows
are UPDATEd.  One weakness in the documentation (although it may otherwise
generally be known about SQL)  is that one may fully qualify column names
with table dot  prefix.  In this case, you can precisely constrain
TABLE_A.ID in the WHERE clause of the UPDATE according to your
requirements.

[FYI: there may be a typo in the suggested answer I saw posted earlier with
a WHERE clause.  If you want to affect  rows of the UPDATEd table, the
WHERE clause has to be at the scope of the UPDATE statement, not a
subquery.]



On Wed, Nov 15, 2017 at 1:16 PM, Bart Smissaert 
wrote:

> Have 2 tables, TABLE_A and TABLE_P like this:
>
> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)
>
> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>
> ID is he common field.
>
> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
> age of the person identified by ID, at the date of ISSUE_DATE.
> I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
> and ISSUE_DATE.
> The UDF is not relevant in this example and it could as well be ISSUE_DATE
> - DOB.
>
> I tried this:
>
> UPDATE TABLE_A SET AGE_AT_ISSUE =
> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
> INNER JOIN TABLE_P P ON(A.ID = P.ID)
>
> But that will make the column AGE_AT_ISSUE have the same value for all
> rows, which is he first row of the select.
>
> The select by itself will give the right values.
>
> Any suggestions how this can be done with just one statement?
>
>
> RBS
> ___
> 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] Simple SQL question?

[Keith Medcalf]

That is not possible since there is only one column called issue_date in all 
the tables mentioned in the query ...


---
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 Bart Smissaert
>Sent: Wednesday, 15 November, 2017 14:55
>To: SQLite mailing list
>Subject: Re: [sqlite] Simple SQL question?
>
>That is getting close, but the calculated values end up with the
>right ID,
>but the wrong ISSUE_DATE.
>Will if an order by can sort this out.
>
>RBS
>
>On Wed, Nov 15, 2017 at 9:33 PM, David Raymond
>
>wrote:
>
>> Try...
>>
>> UPDATE TABLE_A SET AGE_AT_ISSUE =
>> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM
>>  TABLE_P AS P WHERE P.ID = ID);
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org]
>> On Behalf Of Bart Smissaert
>> Sent: Wednesday, November 15, 2017 4:17 PM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] Simple SQL question?
>>
>> Have 2 tables, TABLE_A and TABLE_P like this:
>>
>> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE
>INTEGER)
>>
>> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>>
>> ID is he common field.
>>
>> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will
>hold the
>> age of the person identified by ID, at the date of ISSUE_DATE.
>> I do this with a UDF, taking 2 arguments, in this case DOB (date of
>birth)
>> and ISSUE_DATE.
>> The UDF is not relevant in this example and it could as well be
>ISSUE_DATE
>> - DOB.
>>
>> I tried this:
>>
>> UPDATE TABLE_A SET AGE_AT_ISSUE =
>> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
>> INNER JOIN TABLE_P P ON(A.ID = P.ID)
>>
>> But that will make the column AGE_AT_ISSUE have the same value for
>all
>> rows, which is he first row of the select.
>>
>> The select by itself will give the right values.
>>
>> Any suggestions how this can be done with just one statement?
>>
>>
>> RBS
>> ___
>> 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] Best way to develop a GUI front-end

[Simon Slavin]

On 15 Nov 2017, at 8:16pm, Balaji Ramanathan  
wrote:

> Thank you very much for all your suggestions.  For now, I am going to start
> with a windows forms application in vb.net or forms in OpenOffice. 

You are locking yourself into the Windows system.  By all means use this 
solution as a prototype but if you ever find yourself saying "I’m now doing 
serious programming." do what you can to escape Windows, Office, and Visual-*.  
Otherwise you will continue to be a niche programmer with a very small niche, 
subject to panics every time your users upgrade their versions of Windows and 
Office.

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

Re: [sqlite] Simple SQL question?

[Bart Smissaert]

That is getting close, but the calculated values end up with the right ID,
but the wrong ISSUE_DATE.
Will if an order by can sort this out.

RBS

On Wed, Nov 15, 2017 at 9:33 PM, David Raymond 
wrote:

> Try...
>
> UPDATE TABLE_A SET AGE_AT_ISSUE =
> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM
>  TABLE_P AS P WHERE P.ID = ID);
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Bart Smissaert
> Sent: Wednesday, November 15, 2017 4:17 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Simple SQL question?
>
> Have 2 tables, TABLE_A and TABLE_P like this:
>
> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)
>
> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>
> ID is he common field.
>
> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
> age of the person identified by ID, at the date of ISSUE_DATE.
> I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
> and ISSUE_DATE.
> The UDF is not relevant in this example and it could as well be ISSUE_DATE
> - DOB.
>
> I tried this:
>
> UPDATE TABLE_A SET AGE_AT_ISSUE =
> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
> INNER JOIN TABLE_P P ON(A.ID = P.ID)
>
> But that will make the column AGE_AT_ISSUE have the same value for all
> rows, which is he first row of the select.
>
> The select by itself will give the right values.
>
> Any suggestions how this can be done with just one statement?
>
>
> RBS
> ___
> 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] Simple SQL question?

[Keith Medcalf]

UPDATE table_a
   SET issue_date = (SELECT GetAgeAtDate(dob, issue_date)
   FROM table_p
  WHERE table_p.id = id);

---
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 Bart Smissaert
>Sent: Wednesday, 15 November, 2017 14:17
>To: General Discussion of SQLite Database
>Subject: [sqlite] Simple SQL question?
>
>Have 2 tables, TABLE_A and TABLE_P like this:
>
>CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)
>
>CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>
>ID is he common field.
>
>Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will
>hold the
>age of the person identified by ID, at the date of ISSUE_DATE.
>I do this with a UDF, taking 2 arguments, in this case DOB (date of
>birth)
>and ISSUE_DATE.
>The UDF is not relevant in this example and it could as well be
>ISSUE_DATE
>- DOB.
>
>I tried this:
>
>UPDATE TABLE_A SET AGE_AT_ISSUE =
>(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
>INNER JOIN TABLE_P P ON(A.ID = P.ID)
>
>But that will make the column AGE_AT_ISSUE have the same value for
>all
>rows, which is he first row of the select.
>
>The select by itself will give the right values.
>
>Any suggestions how this can be done with just one statement?
>
>
>RBS
>___
>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] Simple SQL question?

[David Raymond]

Try...

UPDATE TABLE_A SET AGE_AT_ISSUE =
(SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM 
 TABLE_P AS P WHERE P.ID = ID);


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Wednesday, November 15, 2017 4:17 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Simple SQL question?

Have 2 tables, TABLE_A and TABLE_P like this:

CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)

CREATE TABLE_P(ID INTEGER, DOB INTEGER)

ID is he common field.

Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
age of the person identified by ID, at the date of ISSUE_DATE.
I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
and ISSUE_DATE.
The UDF is not relevant in this example and it could as well be ISSUE_DATE
- DOB.

I tried this:

UPDATE TABLE_A SET AGE_AT_ISSUE =
(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
INNER JOIN TABLE_P P ON(A.ID = P.ID)

But that will make the column AGE_AT_ISSUE have the same value for all
rows, which is he first row of the select.

The select by itself will give the right values.

Any suggestions how this can be done with just one statement?


RBS
___
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] Simple SQL question?

[Bart Smissaert]

I like to just update that table as this AGE_AT_ISSUE column will be used
often in various statements.
There must be a simple way to do this, but just can't figure it out.

RBS


On Wed, Nov 15, 2017 at 9:25 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Wouldn’t you create a view instead, and not bother calculating
> age_at_issue until necessary since it’s derived completely from two other
> columns?
>
> On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> bart.smissa...@gmail.com> wrote:
>
> Have 2 tables, TABLE_A and TABLE_P like this:
>
> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)
>
> CREATE TABLE_P(ID INTEGER, DOB INTEGER)
>
> ID is he common field.
>
> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will
> hold the
> age of the person identified by ID, at the date of ISSUE_DATE.
> I do this with a UDF, taking 2 arguments, in this case DOB (date of
> birth)
> and ISSUE_DATE.
> The UDF is not relevant in this example and it could as well be
> ISSUE_DATE
> - DOB.
>
> I tried this:
>
> UPDATE TABLE_A SET AGE_AT_ISSUE =
> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
> INNER JOIN TABLE_P P ON(A.ID = P.ID)
>
> But that will make the column AGE_AT_ISSUE have the same value for all
> rows, which is he first row of the select.
>
> The select by itself will give the right values.
>
> Any suggestions how this can be done with just one statement?
>
>
> RBS
> ___
> 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] Simple SQL question?

[Peter Da Silva]

Wouldn’t you create a view instead, and not bother calculating age_at_issue 
until necessary since it’s derived completely from two other columns?

On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" 
 wrote:

Have 2 tables, TABLE_A and TABLE_P like this:

CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)

CREATE TABLE_P(ID INTEGER, DOB INTEGER)

ID is he common field.

Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
age of the person identified by ID, at the date of ISSUE_DATE.
I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
and ISSUE_DATE.
The UDF is not relevant in this example and it could as well be ISSUE_DATE
- DOB.

I tried this:

UPDATE TABLE_A SET AGE_AT_ISSUE =
(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
INNER JOIN TABLE_P P ON(A.ID = P.ID)

But that will make the column AGE_AT_ISSUE have the same value for all
rows, which is he first row of the select.

The select by itself will give the right values.

Any suggestions how this can be done with just one statement?


RBS
___
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] Simple SQL question?

[Bart Smissaert]

Have 2 tables, TABLE_A and TABLE_P like this:

CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER)

CREATE TABLE_P(ID INTEGER, DOB INTEGER)

ID is he common field.

Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the
age of the person identified by ID, at the date of ISSUE_DATE.
I do this with a UDF, taking 2 arguments, in this case DOB (date of birth)
and ISSUE_DATE.
The UDF is not relevant in this example and it could as well be ISSUE_DATE
- DOB.

I tried this:

UPDATE TABLE_A SET AGE_AT_ISSUE =
(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A
INNER JOIN TABLE_P P ON(A.ID = P.ID)

But that will make the column AGE_AT_ISSUE have the same value for all
rows, which is he first row of the select.

The select by itself will give the right values.

Any suggestions how this can be done with just one statement?


RBS
___
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

[Richard Hipp]

On 11/15/17, 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.

A majority of the SQLite source code and also the Fossil SCM source
code (those parts written by me, which is the majority in both cases)
was composed in a cross-platform text editor that is written in
Tcl/Tk, based on the text widget.  The same TclTk source file runs on
linux, mac, and windows.  I've used that same editor for two decades,
now and then updating it for this or that new feature that I find
useful.

SQLite began life as a Tcl extension that only later escaped into the
wild.  That's why most of the test cases for SQLite are written in
Tcl, why "tclsh" is required to build from canonical sources, why the
Tcl language bindings are built in, why SQLite accept parameters that
look like Tcl language variables, and why SQLite uses flexible typing
and does not require explicit data types on columns.

There is a new book out on Tcl by Ashok Nadkarni.  See
http://wiki.tcl.tk/48868 for details.  I have not read it myself, but
people I trust tell me that it is good.
-- 
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

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

[Peter Da Silva]

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

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

[Balaji Ramanathan]

Thank you very much for all your suggestions.  For now, I am going to start
with a windows forms application in vb.net or forms in OpenOffice.  Tcl/Tk
is a steeper learning curve, and if someone can point me to some good
resources that will walk a beginner through the development of a windows
GUI on that platform, I would appreciate it.  Thank you again.

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

[sqlite] segfault in load-extension but not in app

[sub sk79]

Hi,

I am getting seg fault when same code is run as loadable-extension but not
when run as shared lib linked to app.
Thanks in advance for all help.

The C++ code is compiled to
1. shared library and linked to app
2. loadable-extension and loaded from sqlite command line using '.load'.

The C++ code is exactly same in both cases (except extension init section)

Sqlite version is 3.21.0. Ubuntu 16.04 LTS 64-bit in virtualbox VM on
windows 7 host. GCC 6.1.0

I do not get any issues when the code is run as app.
The code runs fine as loadable-extension as well except, it SEG faults
during cleanup:
Here are two snippets of the app's C++ code where I have observed this:
==Snippet 1
  //Drop all views created in sqlite
  std::vector  view_vector;
  view_vector.push_back("MYDATA");

std::string dropView;
  for (std::vector ::iterator
iter=view_vector.begin(); iter != view_vector.end(); ++iter)
  {
LOG_DEBUG << "Dropping View: " << (*iter) << endl;
dropView=("DROP VIEW IF EXISTS "+(*iter));
if (SQLITE_OK != sqlite3_exec(m_pDb, dropView.c_str(),
NULL, NULL, NULL))
{
 LOG_ERROR << "Exec failed for Drop View " << (*iter)
<< ". Error Code= " << sqlite3_errcode(m_pDb)<< " Error Message= "<<
sqlite3_errmsg(m_pDb) << " Line=" << __LINE__ << endl;
};
  };
==Snippet 2
  //Generic Finalize loop -- requires Sqlite version 3.5+
//NOTE: Since we are finalizing it is correct to pass 0 as second
arg all the time.
  sqlite3_stmt *pStmt;
  while( (pStmt = sqlite3_next_stmt(m_pDb, 0))!=0 )
  {
LOG_TRACE << "Finalizing SQL: " << sqlite3_sql(pStmt) <,
run_list_atexit=run_list_atexit@entry=true)
at exit.c:82
#20 0x77626045 in __GI_exit (status=) at exit.c:104
#21 0x7760c837 in __libc_start_main (main=0x404550 , argc=4,
argv=0x7fffdb58, init=, fini=,
rtld_fini=, stack_end=0x7fffdb48) at
../csu/libc-start.c:325
#22 0x004052b9 in _start ()
(gdb)

==

Sometimes I also get SEG fault at:
===
(gdb) where
#0  malloc_consolidate (av=av@entry=0x779b0b20 ) at
malloc.c:4167
#1  0x7766c678 in _int_free (av=0x779b0b20 ,
p=, have_lock=0) at malloc.c:4075
#2  0x7767053c in __GI___libc_free (mem=) at
malloc.c:2968
#3  0x00417074 in sqlite3_free ()
#4  0x00421572 in sqlite3VdbeClearObject ()
#5  0x00421954 in sqlite3VdbeDelete ()
#6  0x00454b74 in sqlite3VdbeFinalize ()
#7  0x00454bde in sqlite3_finalize ()
#8  0x76889a2a in WRAPPER1::~WRAPPER1 (this=0x7121e0,
__in_chrg=) at src/wrapper1_defn.cpp:181
#9  0x76887de5 in WRAPPER1::removeInstance () at
src/wrapper1_defn.cpp:81
#10 0x76886e71 in ss_world::removeInstance (this=0x76daa8e0
) at src/load_extension.cpp:196
#11 0x76884e80 in ss_world::~ss_world (this=0x76daa8e0
, __in_chrg=) at src/load_extension.cpp:30
#12 0x77625ff8 in __run_exit_handlers (status=0,
listp=0x779b05f8 <__exit_funcs>,
run_list_atexit=run_list_atexit@entry=true)
at exit.c:82
#13 0x77626045 in __GI_exit (status=) at exit.c:104
#14 0x7760c837 in __libc_start_main (main=0x404550 , argc=4,
argv=0x7fffdb58, init=, fini=,
rtld_fini=, stack_end=0x7fffdb48) at
../csu/libc-start.c:325
#15 0x004052b9 in _start ()
(gdb)

===

I suspect memory corruption so I ran valgrind. It reports some possible
lost blocks during extension loading but none 

Re: [sqlite] 1TB limit on encrypted database

[Richard Hipp]

On 11/15/17, Andrew Stewart  wrote:
> Thanks.  This has worked.

Temporarily.  You cannot increase the max_page_count above 2147483646,
so if your database continues to grow, you are going to need to look
into increasing the page size from 1024 to something larger like 8192.

-- 
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

Re: [sqlite] 1TB limit on encrypted database

[Andrew Stewart]

Thanks.  This has worked.

Andrew Stewart
Argus Control Systems Ltd.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, November 14, 2017 12:34 PM
To: SQLite mailing list 
Subject: Re: [sqlite] 1TB limit on encrypted database

On 11/14/17, Andrew Stewart  wrote:
> Richard,
> If I am trying to change the max_page_count use the SEE.EXE
> program, how do I get the value to be updated in the database.  Every
> time I try changing this and then exit see.exe, the value has not
> changed.  Do I need the updated encryption extension first?

The PRAGMA only changes the one database connection in which it runs.

To make the setting persistent, recompile with
-DSQLITE_MAX_PAGE_COUNT=20

--
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
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

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

[Peter Da Silva]

On 11/14/17, 10:32 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
> Initially I was interested in tcl/tk, and still am, but I'm not sure about 
> the tcl/tk packaging that would be necessary to make use on multiple 
> computers.

You can bundle a whole Tcl application in a single executable called a 
“starkit” along with all the necessary packages and libraries exposed 
internally as a virtual file system.

> Also, I don't know how network database connections would work out, would it 
> be the same as web based stuff or more complicated, etc.

For sqlite you don’t generally make network database connections, it uses a 
library model. For other databases there’s a number of conventional script 
interfaces. DBI is popular for cross-database use: 
https://core.tcl.tk/jenglish/gutter/packages/dbi.html and I’m currently 
maintainer for the original PostgreSQL-Tcl interface 
https://wiki.tcl-lang.org/13016 ...

___
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

[Peter Da Silva]

I contacted the Tcl core team and this is the response from Steve Landers:

> tcl-lang.org was a temporary measure a few years ago when the .tk DNS went 
> missing.  It wasn’t advertised but I guess it is now.

> I’ve fixed it

The official site is still at tcl.tk.

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

[sqlite] Why there is duplicated sha1 functions in sqlite3 sources ?

[Domingo Alvarez Duarte]

Hello !

I'm making a build of sqlite3 that includes sha1 and shathree and other 
extensions, then made some changes to makefiles (Makefile.in, main.mk) 
then when trying to run the tests I got an error "multiple definition of 
'SHA1Transform'" when trying to link dbhash.c .


Why is sha1 duplicated on dbhash.c instead of reusing the one in 
ext/misc/sha1.c ?


Also I'm still waiting to know why sha1.c sqlite_extension return a hex 
string but shathree return a binary string ?


It seems that some cleanup can be done here !

Cheers !

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

[sqlite] LSM1 vtable extension portability

[Dominique Devienne]

I'm reading https://www.sqlite.org/src/artifact/529255dc70428900, and
stumbled on:

** For FLOAT values, the content is the IEEE754 floating point value in
** native byte-order.  This means that FLOAT values will be corrupted when
** database file is moved between big-endian and little-endian machines.

And my immediate reaction is why? SQLite DBs are portable themselves.
Why make the decision to break that portability when using the LSM1
extension?

I strongly suspect SQLite4, which also uses LSM, uses a portable encoding
for row data (the contrary would be extremely surprising), so why go for
non-portability on that one datatype, instead of 1) encoding the endianness
in the type,
or 2) forcing a given endianness, or 3) using varints to encode the
mantissa and exponent,
therefore providing the platform portability?

Thanks, --DD

PS: typo: varaible-length

PPS: Is the SQLite4 varint encoding (
https://sqlite.org/src4/doc/trunk/www/varint.wiki) different from the
SQLite3 one?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users