[sqlite] Better way to get range of dates

[Cecil Westerhof]

When getting data between a range of dates you can use:
WHERE  date >= DATE('now', '-7 days')
   AND date  < DATE('now')

or:
WHERE  date BETWEEN
DATE('now', '-7 days')
AND DATE('now', '-1 days')

Is there a preferred way? In a way I like the second better.

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

Re: [sqlite] sqlite on IBM z/OS Unix

[Keith Medcalf]

I am not familiar with the internals of z/OS ... YMMV.

My initial take would be that it would depend on whether the LE remains active 
(initialized) and maintains its memory allocations/file opens, etc, between 
invocations from your native z/OS assembly code.  That is to say is the 
sequence:

run z/OS assembly
call "C" LE
  initialize environment
  ... do stuff in environment
  tear down environment and release all its resources
carry on in z/OS assembly

or is it more like:

run z/OS assembly
call "C" LE
  initialize LE environment if it is not initialized otherwise use existing 
environment
  ... do stuff in environment
carry on in z/OS assembly
... eventually when process ends clean up the LE environment if it exists

If the latter then you should be able to open the database and keep a static 
pointer to the sqlite3 structure across "jumps" into the LE environment.  If 
the environment and all its resources are released (as in the former case) when 
you return back to native z/OS then obviously you cannot do that.  Which one it 
does is probably documented somewhere, however, I would suspect the latter case 
(initialize only once) for performance reasons, unless the LE environment is 
another "process" ...

---
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 David Jackson
>Sent: Wednesday, 29 August, 2018 16:26
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] sqlite on IBM z/OS Unix
>
>Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am
>now
>looking at a c program to make SQL calls to this.
>Starting with an Assembler routine that runs within z/OS (not Unix),
>which
>is not LE (Language Environment)enabled, we then call a c routine
>(numerous
>times) that is LE enabled. That is all working fine and making good
>SQL
>calls to sqlite. the c program then returns back to the upper
>assembler
>calling program. The problem is that the c routines is opening,
>issuing the
>SQL and closing on each invocation.
>
>Now this may be a dumb question, so apologies up front.
>Is there any way that the c program can open the sqlite db initially
>on the
>first call and keep it open after it returns back to the calling
>program
>until a final call at which point it will issue the sqlite3_close.
>
>Again - sorry if this was a dumb question.
>
>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] sqlite on IBM z/OS Unix

[Chris Brody]

It is not clear to me why your program closes the sqlite3 database at
the end of each C function invocation. AFAIK sqlite3 should not close
any database connection unless your program invokes sqlite3_close. If
something in the "Language Environment" closes and releases internal
resources at the end of each C function call then this would be an
issue with your z/OS environment.

Assuming that the "Language Environment" does not automatically
release resources, I can think of a the following alternative
approaches:

1: C code opens the database and stores the handle in a static variable.
2: C code opens the database and returns the pointer to the assembly
code; assembly code would then include the returned pointer value in
subsequent calls to the C code.
3a: C code opens the database, stores it in a structure on the heap,
and returns the pointer to the structure back to the assembly code
which is then used in subsequent calls to the C code
3b: C code opens the database, stores it in a structure on the heap,
maintains some kind of hash table or other key-value map, and returns
a numerical value to the assembly program which is then used in
subsequent calls to the C code

I suspect it should be pretty straightforward (easy) to search for
documentation on each of the approaches above. I think this could be
similar to interfacing between C and higher-level languages such as
Java, Python, C++, etc.

I would compare alternatives 2 and 3a to how FILE pointers work
between stdlib and C programs, also to how sqlite3 database pointers
work between sqlite3 library and application code in C..

I hope this is helpful to you and other readers on the list. Please do
not hesitate to ask if anything does not sound right or is not clear.

Chris

https://www.linkedin.com/in/chrisbrody/

On Wed, Aug 29, 2018 at 6:26 PM David Jackson  wrote:
>
> Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> looking at a c program to make SQL calls to this.
> Starting with an Assembler routine that runs within z/OS (not Unix), which
> is not LE (Language Environment)enabled, we then call a c routine (numerous
> times) that is LE enabled. That is all working fine and making good SQL
> calls to sqlite. the c program then returns back to the upper assembler
> calling program. The problem is that the c routines is opening, issuing the
> SQL and closing on each invocation.
>
> Now this may be a dumb question, so apologies up front.
> Is there any way that the c program can open the sqlite db initially on the
> first call and keep it open after it returns back to the calling program
> until a final call at which point it will issue the sqlite3_close.
>
> Again - sorry if this was a dumb question.
>
> 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] sqlite on IBM z/OS Unix

[David Jackson]

Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
looking at a c program to make SQL calls to this.
Starting with an Assembler routine that runs within z/OS (not Unix), which
is not LE (Language Environment)enabled, we then call a c routine (numerous
times) that is LE enabled. That is all working fine and making good SQL
calls to sqlite. the c program then returns back to the upper assembler
calling program. The problem is that the c routines is opening, issuing the
SQL and closing on each invocation.

Now this may be a dumb question, so apologies up front.
Is there any way that the c program can open the sqlite db initially on the
first call and keep it open after it returns back to the calling program
until a final call at which point it will issue the sqlite3_close.

Again - sorry if this was a dumb question.

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

Re: [sqlite] Get data in one query

[Abroży Nieprzełoży]

Julian Day is defined in terms of Universal Time.
https://en.wikipedia.org/wiki/Julian_day


2018-08-29 22:33 GMT+02:00, David Raymond :
> Good to know. Is that actually documented anywhere? All I see is...
>
>
> https://www.sqlite.org/lang_datefunc.html
> "Format 11, the string 'now', is converted into the current date and time as
> obtained from the xCurrentTime method of the sqlite3_vfs object in use."
>
>
> https://www.sqlite.org/c3ref/vfs.html
> "The xCurrentTime() method returns a Julian Day Number for the current date
> and time as a floating point value. The xCurrentTimeInt64() method returns,
> as an integer, the Julian Day Number multiplied by 8640 (the number of
> milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64()
> method to get the current date and time if that method is available (if
> iVersion is 2 or greater and the function pointer is not NULL) and will fall
> back to xCurrentTime() if xCurrentTimeInt64() is unavailable."
>
>
> Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the
> default vfs object gives UTC, but this is an implementation detail and
> should not be relied upon"
>
> Is there any way to know what you're getting? Or to explicitly ask for one?
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Keith Medcalf
> Sent: Wednesday, August 29, 2018 3:45 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Get data in one query
>
>
> ... don't forget that Date('now') returns the UT1 date, not the local (as in
> Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date
> in accordance with the timezone where your computer thinks it is located and
> should always be accurate for 'now' but maybe not a few years in the past on
> Operating Systems that do not contain/use a full set of UT1 -> localtime
> (timezone) conversion rules (such as Windows).
>
> ---
> 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 Cecil Westerhof
>>Sent: Wednesday, 29 August, 2018 12:46
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Get data in one query
>>
>>2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
>>
>>> 2018-08-29 18:06 GMT+02:00 R Smith :
>>>

 SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
   FROM messages
  WHERE date = DATE('now')
)

>>>
>>> Works like a charm. Thanks.
>>>
>>> I made it even more useful:
>>> SELECT Total
>>> ,   Late
>>> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>> FROM(
>>> SELECT SUM(total) AS Total
>>> ,  SUM(late)  AS Late
>>> FROM  (
>>> SELECT 1  AS Total
>>> ,  (time NOT LIKE '%:00') AS Late
>>> FROM   messages
>>> WHERE  date = DATE('now')
>>> )
>>> )
>>>
>>
>>And even more useful:
>>SELECT date
>>,   Total
>>,   Late
>>,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>FROM(
>>SELECT date
>>,  SUM(total) AS Total
>>,  SUM(late)  AS Late
>>FROM  (
>>SELECT date
>>,   1  AS Total
>>,  (time NOT LIKE '%:00') AS Late
>>FROM   messages
>>WHERE  date >= DATE('now', '-7 days')
>>   AND date  < DATE('now')
>>)
>>GROUP BY date
>>)
>>ORDER BY date DESC
>>
>>--
>>Cecil Westerhof
>>___
>>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] Get data in one query

[David Raymond]

Good to know. Is that actually documented anywhere? All I see is...


https://www.sqlite.org/lang_datefunc.html
"Format 11, the string 'now', is converted into the current date and time as 
obtained from the xCurrentTime method of the sqlite3_vfs object in use."


https://www.sqlite.org/c3ref/vfs.html
"The xCurrentTime() method returns a Julian Day Number for the current date and 
time as a floating point value. The xCurrentTimeInt64() method returns, as an 
integer, the Julian Day Number multiplied by 8640 (the number of 
milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64() method 
to get the current date and time if that method is available (if iVersion is 2 
or greater and the function pointer is not NULL) and will fall back to 
xCurrentTime() if xCurrentTimeInt64() is unavailable."


Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the 
default vfs object gives UTC, but this is an implementation detail and should 
not be relied upon"

Is there any way to know what you're getting? Or to explicitly ask for one?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Wednesday, August 29, 2018 3:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Get data in one query


... don't forget that Date('now') returns the UT1 date, not the local (as in 
Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in 
accordance with the timezone where your computer thinks it is located and 
should always be accurate for 'now' but maybe not a few years in the past on 
Operating Systems that do not contain/use a full set of UT1 -> localtime 
(timezone) conversion rules (such as Windows).

---
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 Cecil Westerhof
>Sent: Wednesday, 29 August, 2018 12:46
>To: SQLite mailing list
>Subject: Re: [sqlite] Get data in one query
>
>2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
>
>> 2018-08-29 18:06 GMT+02:00 R Smith :
>>
>>>
>>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>>   FROM messages
>>>  WHERE date = DATE('now')
>>>)
>>>
>>
>> Works like a charm. Thanks.
>>
>> I made it even more useful:
>> SELECT Total
>> ,   Late
>> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>> FROM(
>> SELECT SUM(total) AS Total
>> ,  SUM(late)  AS Late
>> FROM  (
>> SELECT 1  AS Total
>> ,  (time NOT LIKE '%:00') AS Late
>> FROM   messages
>> WHERE  date = DATE('now')
>> )
>> )
>>
>
>And even more useful:
>SELECT date
>,   Total
>,   Late
>,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>FROM(
>SELECT date
>,  SUM(total) AS Total
>,  SUM(late)  AS Late
>FROM  (
>SELECT date
>,   1  AS Total
>,  (time NOT LIKE '%:00') AS Late
>FROM   messages
>WHERE  date >= DATE('now', '-7 days')
>   AND date  < DATE('now')
>)
>GROUP BY date
>)
>ORDER BY date DESC
>
>--
>Cecil Westerhof
>___
>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] Get data in one query

[Cecil Westerhof]

2018-08-29 21:44 GMT+02:00 Keith Medcalf :

>
> ... don't forget that Date('now') returns the UT1 date, not the local (as
> in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local
> date in accordance with the timezone where your computer thinks it is
> located and should always be accurate for 'now' but maybe not a few years
> in the past on Operating Systems that do not contain/use a full set of UT1
> -> localtime (timezone) conversion rules (such as Windows).
>

I know and I prefer it that way. No problems with date + time when the
clock is set back. That is why I do my statistics after two in the morning.

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

Re: [sqlite] Get data in one query

[Cecil Westerhof]

2018-08-29 21:26 GMT+02:00 Wout Mertens :

> By the way, why not store the time as epoch? Date and time in one...
>

Because I think it is better to have date and time as different (text)
fields.

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

Re: [sqlite] Get data in one query

[Keith Medcalf]

... don't forget that Date('now') returns the UT1 date, not the local (as in 
Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in 
accordance with the timezone where your computer thinks it is located and 
should always be accurate for 'now' but maybe not a few years in the past on 
Operating Systems that do not contain/use a full set of UT1 -> localtime 
(timezone) conversion rules (such as Windows).

---
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 Cecil Westerhof
>Sent: Wednesday, 29 August, 2018 12:46
>To: SQLite mailing list
>Subject: Re: [sqlite] Get data in one query
>
>2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
>
>> 2018-08-29 18:06 GMT+02:00 R Smith :
>>
>>>
>>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>>   FROM messages
>>>  WHERE date = DATE('now')
>>>)
>>>
>>
>> Works like a charm. Thanks.
>>
>> I made it even more useful:
>> SELECT Total
>> ,   Late
>> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>> FROM(
>> SELECT SUM(total) AS Total
>> ,  SUM(late)  AS Late
>> FROM  (
>> SELECT 1  AS Total
>> ,  (time NOT LIKE '%:00') AS Late
>> FROM   messages
>> WHERE  date = DATE('now')
>> )
>> )
>>
>
>And even more useful:
>SELECT date
>,   Total
>,   Late
>,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>FROM(
>SELECT date
>,  SUM(total) AS Total
>,  SUM(late)  AS Late
>FROM  (
>SELECT date
>,   1  AS Total
>,  (time NOT LIKE '%:00') AS Late
>FROM   messages
>WHERE  date >= DATE('now', '-7 days')
>   AND date  < DATE('now')
>)
>GROUP BY date
>)
>ORDER BY date DESC
>
>--
>Cecil Westerhof
>___
>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] Get data in one query

[Wout Mertens]

By the way, why not store the time as epoch? Date and time in one...

On Wed, Aug 29, 2018, 8:46 PM Cecil Westerhof 
wrote:

> 2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
>
> > 2018-08-29 18:06 GMT+02:00 R Smith :
> >
> >>
> >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
> >>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
> >>   FROM messages
> >>  WHERE date = DATE('now')
> >>)
> >>
> >
> > Works like a charm. Thanks.
> >
> > I made it even more useful:
> > SELECT Total
> > ,   Late
> > ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> > FROM(
> > SELECT SUM(total) AS Total
> > ,  SUM(late)  AS Late
> > FROM  (
> > SELECT 1  AS Total
> > ,  (time NOT LIKE '%:00') AS Late
> > FROM   messages
> > WHERE  date = DATE('now')
> > )
> > )
> >
>
> And even more useful:
> SELECT date
> ,   Total
> ,   Late
> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM(
> SELECT date
> ,  SUM(total) AS Total
> ,  SUM(late)  AS Late
> FROM  (
> SELECT date
> ,   1  AS Total
> ,  (time NOT LIKE '%:00') AS Late
> FROM   messages
> WHERE  date >= DATE('now', '-7 days')
>AND date  < DATE('now')
> )
> GROUP BY date
> )
> ORDER BY date DESC
>
> --
> Cecil Westerhof
> ___
> 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] Get data in one query

[Cecil Westerhof]

2018-08-29 18:58 GMT+02:00 Cecil Westerhof :

> 2018-08-29 18:06 GMT+02:00 R Smith :
>
>>
>> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>>   FROM messages
>>  WHERE date = DATE('now')
>>)
>>
>
> Works like a charm. Thanks.
>
> I made it even more useful:
> SELECT Total
> ,   Late
> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM(
> SELECT SUM(total) AS Total
> ,  SUM(late)  AS Late
> FROM  (
> SELECT 1  AS Total
> ,  (time NOT LIKE '%:00') AS Late
> FROM   messages
> WHERE  date = DATE('now')
> )
> )
>

And even more useful:
SELECT date
,   Total
,   Late
,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM(
SELECT date
,  SUM(total) AS Total
,  SUM(late)  AS Late
FROM  (
SELECT date
,   1  AS Total
,  (time NOT LIKE '%:00') AS Late
FROM   messages
WHERE  date >= DATE('now', '-7 days')
   AND date  < DATE('now')
)
GROUP BY date
)
ORDER BY date DESC

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

Re: [sqlite] Pragma Use for TCL scripts

[Tilsley, Jerry M.]

The data was encrypted by a third party softward, which I assume does have that 
since it works with their application.  I know it was possible to extract that 
data directly from the encrypted databases as long as you provided the 
encryption key as the community for this software talked about it.  Come to 
find out I was making a really stupid coding error.  I think I have this 
working now.

Jerry

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, August 29, 2018 12:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Pragma Use for TCL scripts


ATTENTION: This email originated from outside of the organization. Do not open 
attachments or click on links unless you recognize the sender and know the 
content is safe.

--
On 8/29/18, Tilsley, Jerry M.  wrote:
>
> Trying to access some encrypted databases via TCL interface and need
> to provide the key.  Can someone provide an good example of this?
> I've found some things on google, but not having much success.
>

Do you have a license for the SQLite Encryption Extension?

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=GFqIbjwKw0kVii5O6Uc6PJGkobyxvuBP74j45UJ5HPs=ha77g7SAS2KmEM091Ua4AXpMUoWz2o-ZTnvLfOF5sUg=L6Mx35ZaWYvszhyVyZBSnVHTsTgWZ2Mh3QSX0dXCHUM=6FBrfOC48sQ_da65NIKNx-eG9X79K7GJND_dpYdURWQ=



Disclaimer
Confidentiality Notice: This e-mail communication and any attachments may 
contain confidential and privileged information for the use of the designated 
recipients named above. If you are not the intended recipient, you are hereby 
notified that you have received this communication in error and that any 
review, disclosure, dissemination, distribution, or copying of it or its 
contents is prohibited. If you have received this communication in error, 
please notify the SCR Helpdesk immediately at (606) 783-6565, and delete this 
email from your computer. SCR will never request via a link within an email any 
HIPAA-type information regarding patients. If such a message is received, 
please immediately call the SCR Privacy Officer at (606) 783-6597.


SCR Mission Statement: “The mission of St. Claire Regional Medical Center is to 
proclaim God’s goodness through a healing ministry to the people of Eastern 
Kentucky.”
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get data in one query

[Cecil Westerhof]

2018-08-29 18:06 GMT+02:00 R Smith :

>
> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
>   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
>   FROM messages
>  WHERE date = DATE('now')
>)
>

Works like a charm. Thanks.

I made it even more useful:
SELECT Total
,   Late
,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
FROM(
SELECT SUM(total) AS Total
,  SUM(late)  AS Late
FROM  (
SELECT 1  AS Total
,  (time NOT LIKE '%:00') AS Late
FROM   messages
WHERE  date = DATE('now')
)
)



> On 2018/08/29 5:56 PM, Cecil Westerhof wrote:
>
>> I have a table messages in which something is put every minute. The total
>> messages that are added today I can get with:
>>  SELECT COUNT(*) AS Total
>>  FROM   messages
>>  WHERE  date = DATE('now')
>>
>> And the number of messages that where entered today, but not at the start
>> of a minute I can get with:
>>  SELECT COUNT(*) AS Late
>>  FROM   messages
>>  WHERE  date = DATE('now')
>> AND time NOT LIKE '%:00'
>>
>> Is there a way to get this information in one query?
>>
>
-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pragma Use for TCL scripts

[Richard Hipp]

On 8/29/18, Tilsley, Jerry M.  wrote:
>
> Trying to access some encrypted databases via TCL interface and need to
> provide the key.  Can someone provide an good example of this?  I've found
> some things on google, but not having much success.
>

Do you have a license for the SQLite Encryption Extension?

-- 
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] Get data in one query

[R Smith]

SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
  FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
          FROM messages
         WHERE date = DATE('now')
   )



On 2018/08/29 5:56 PM, Cecil Westerhof wrote:

I have a table messages in which something is put every minute. The total
messages that are added today I can get with:
 SELECT COUNT(*) AS Total
 FROM   messages
 WHERE  date = DATE('now')

And the number of messages that where entered today, but not at the start
of a minute I can get with:
 SELECT COUNT(*) AS Late
 FROM   messages
 WHERE  date = DATE('now')
AND time NOT LIKE '%:00'

Is there a way to get this information in one query?



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

Re: [sqlite] Get data in one query

[Simon Slavin]

On 29 Aug 2018, at 4:59pm, Simon Slavin  wrote:

> You can use this structure:
> 
> SELECT (first SELECT), (second SELECT)

Better still in your case, you can use

SELECT (first SELECT) as Total, (second SELECT) as Late

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

[sqlite] Pragma Use for TCL scripts

[Tilsley, Jerry M.]

All,

Trying to access some encrypted databases via TCL interface and need to provide 
the key.  Can someone provide an good example of this?  I've found some things 
on google, but not having much success.

Thanks,

Jerry Tilsley




Disclaimer
Confidentiality Notice: This e-mail communication and any attachments may 
contain confidential and privileged information for the use of the designated 
recipients named above. If you are not the intended recipient, you are hereby 
notified that you have received this communication in error and that any 
review, disclosure, dissemination, distribution, or copying of it or its 
contents is prohibited. If you have received this communication in error, 
please notify the SCR Helpdesk immediately at (606) 783-6565, and delete this 
email from your computer. SCR will never request via a link within an email any 
HIPAA-type information regarding patients. If such a message is received, 
please immediately call the SCR Privacy Officer at (606) 783-6597.


SCR Mission Statement: "The mission of St. Claire Regional Medical Center is to 
proclaim God's goodness through a healing ministry to the people of Eastern 
Kentucky."
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get data in one query

[Simon Slavin]

On 29 Aug 2018, at 4:56pm, Cecil Westerhof  wrote:

> Is there a way to get this information in one query?

You can use this structure:

SELECT (first SELECT), (second SELECT)

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

[sqlite] Get data in one query

[Cecil Westerhof]

I have a table messages in which something is put every minute. The total
messages that are added today I can get with:
SELECT COUNT(*) AS Total
FROM   messages
WHERE  date = DATE('now')

And the number of messages that where entered today, but not at the start
of a minute I can get with:
SELECT COUNT(*) AS Late
FROM   messages
WHERE  date = DATE('now')
   AND time NOT LIKE '%:00'

Is there a way to get this information in one query?

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

[sqlite] SQLite with branching

[Simon Slavin]

I have no connection with the following project.



Described poorly on the web site so here's my own description:

This is an extension of SQLite which allows branched versions, each new branch 
creating one dataset which existed before the new branch and a new dataset, 
initially a copy of the old dataset at some historical point, which can be 
further modified.  Both the old and new branches can be further branched.

LiteTree is implemented storing the SQLite db pages on LMDB making it more than 
twice as fast as normal SQLite on Linux and MacOSX, and also runs on Windows.

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

Re: [sqlite] SQLite name/identifier length limit?

[Dominique Devienne]

On Wed, Aug 29, 2018 at 12:09 PM Richard Hipp  wrote:

> On 8/29/18, Dominique Devienne  wrote:
> > https://www.sqlite.org/limits.html doesn't say explicitly.
> > Unlimited? SQLITE_MAX_LENGTH ?
> >
> > PostgreSQL is limited to 63 chars.
> > Oracle is limited to 30 and 128 chars (latter > 12.2).
> > Other DBs? (just curious). --DD
>
> There is no arbitrary limit, other than the ability of a signed 32-bit
> integer to measure the number of bytes.  But you are limited to
> SQLITE_MAX_SQL_LENGTH bytes for an SQL statement, and since an
> identifier needs to fit inside an SQL statement, I suppose that makes
> the identifier length limit SQLITE_MAX_SQL_LENGTH (default: 1,000,000).
>

Minus the minimum required CREATE TABLE ... (a), so 17 chars :)
Thanks Richard. Wanted to confirm I wasn't missing anything here. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite name/identifier length limit?

[Richard Hipp]

On 8/29/18, Dominique Devienne  wrote:
> https://www.sqlite.org/limits.html doesn't say explicitly.
> Unlimited? SQLITE_MAX_LENGTH ?
>
> PostgreSQL is limited to 63 chars.
> Oracle is limited to 30 and 128 chars (latter > 12.2).
> Other DBs? (just curious). --DD

There is no arbitrary limit, other than the ability of a signed 32-bit
integer to measure the number of bytes.  But you are limited to
SQLITE_MAX_SQL_LENGTH bytes for an SQL statement, and since an
identifier needs to fit inside an SQL statement, I suppose that makes
the identifier length limit SQLITE_MAX_SQL_LENGTH (default:
1,000,000).

-- 
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] SQLite name/identifier length limit?

[Dominique Devienne]

https://www.sqlite.org/limits.html doesn't say explicitly.
Unlimited? SQLITE_MAX_LENGTH ?

PostgreSQL is limited to 63 chars.
Oracle is limited to 30 and 128 chars (latter > 12.2).
Other DBs? (just curious). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shared memory cache files on disk?

[Dirkjan Ochtman]

On Wed, Aug 29, 2018 at 1:11 AM Keith Medcalf  wrote:

> SQLITE_USE_URI
>
> If this is not defined then URI's are not parsed.
>
> https://www.sqlite.org/uri.html
>

Thank you, that helps a lot!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users